But, I Don’t do
Windows…Oracle on Windows for UNIX Folks and Windows Newbies
intent of this paper is to introduce the ‘new to Windows’ reader
to some ideas and concepts related to running Oracle on Windows,
along with some tips and tricks.It is by no means in depth , but touches on a variety of
points that the reader can then delve into more detail as
needed. I strongly suggest you review the
Oracle Database Platform
Guide for Microsoft Windows for your release of Oracle for
The hammer and Saw Mindset
need a hammer, use a hammer, and when you need a saw, use a saw.Meaning, sometimes it is more cost-effective for a small
application to just stand up the database on a small Windows
server than use an Enterprise UNIX server.Or, Management just tells you that’s how it is.
Furthermore, Oracle on Windows is in reality a very good choice
for a number of reasons, even for large databases.Either way, you, as the DBA, are now responsible for it.Well, cheer up, because Oracle actually runs quite well
on Windows, especially in the 64-bit form.We will show you some tips on how to make things work.
Windows Actually has VMS Roots
Just as a
brief aside, we can trace Windows NT back to what is my favorite
O/S…VMS.VMS in its
day was used by Banks, Hospitals, and many large businesses.It had a reputation for extreme reliability. The author
of VMS and several others went to work for Microsoft, and
developed NT using a lot of what they learned at Digital.There are many similarities between the two.I won’t get into too much detail but suffice to say if
you know VMS, Windows NT/Server 200x really isn’t as much of a
shock as it is to someone who is coming over from UNIX.Add a letter to VMS and you get…WNT.
The Threaded Model on Windows
the biggest things to get used to with Oracle on Windows is the
change from a process-based server to a thread-based server. On
UNIX, Oracle uses separate processes to run all the background
tasks (PMON, SMON, LGWR, etc).Also, each database connection uses yet another UNIX
process. On Windows,
though, all of these processes areimplemented as threads
inside one single process called
threads all run inside this one Windows process – the database
support threads as well as the threads for all the user
connections. All you see
in the Task Manager is
There will be one
oracle.exe process for each instance you have running on the
as the Listener, OEM, etc will still have separate processes.
the other things to get used to with Oracle on Windows is the
use of Windows Services.Each Oracle Instance will have a Windows Service
associated with it., typically named something like
OracleServiceSID.You start and stop the service to start the instance, but
once the service is running, you can use SQLPlus to
startup/shutdown the database at the command prompt.Keep in mind that you cannot start the instance from
SQLPlus – the Service must be running first.That can be done using the Services screen, net start, or
with the ORADIM utility.Items such as the Listener
will also have separate
start’ command at the command prompt will display the services.This, as you will see later, can be used in scripting.
ORADIM utility only exists on Windows.It is the command-prompt tool that creates, deletes,
stops, and starts instances.If you have UNIX scripts that create databases, you can
still use them (modified of course), but you will need to run
ORADIM first to actually create the Instance.This command is what actually creates the Windows Service
that runs the Instance.
An example of creating a new Instance for ORCL is:
You will need to be a member of the ORA_DBA group to do things
like connect “/ as sysdba”.No surprise here, as that is also the case in UNIX. BUT –
in Windows, if you are running scheduled tasks/batch jobs (such
as exports, backups, etc) the user you are running as also needs
to be a member of the ORA_DBA group on the local machine.
The trick here is even though you are logged in and you are a
member of ORA_DBA, when you submit a batch job using ‘at’, the
job runs as SYSTEM by default.So, your job will fail if you are trying to do things
like “connect / as sysdba”, since SYSTEM won’t be in the ORA_DBA
group.You can use
the Windows Task Scheduler to submit the job, and assign a
username that has ORA_DBA.
In Windows, especially on servers with multiple databases, you
must set the environment, just as you do in UNIX.
From a command prompt, the syntax is (no spaces between the =
needs to be done prior to running SQLPlus, doing an
“/ as sysdba”
Windows, there is a place called the Registry (like the SYSGEN
Parameter file on VMS) that stores values that are used by the
OS and various software packages, and that includes Oracle.The command at the Windows ‘run’ prompt is ‘regedit’. I
won’t get into Registry Editing here, since that is a full topic
in itself, but below are some of the main entries, and what they
-is the home registry key.
ORA_sid_PFILE – the path to your pfile
ORA_sid_AUTOSTART – tells whether the sid should start when the
Generally this should be TRUE
ORA_sid_SHUTDOWN – tells whether to shutdown when the service
should be TRUE.
ORA_sid_SHUTDOWN_TYPE – How to shutdown by default when the
service stops. IMMEDIATE works here.
ORACLE_HOME – path to Oracle Home
– nls language setting for the server
32Bit vs 64Bit Windows
difference between 32bit and 64bit Windows is obviously address
thing to look out for here is that
Windows limits you to 2GB for the SGA and ALL the other Oracle
support jobs AND user jobs for an OracleInstance.
(Remember the Threaded Model discussed earlier).
are unaware of this on a 32-bit Oracle Windows Server and you
attempt to increase the SGA beyond 2GB, you will get ORA-27102
out of memory errors when you try to restart the database.I learned this the hard way, then did some reading!
add the /3GB switch, you can get more.This is due to a Windows feature called 4GB RAM Tuning
(4GT). This feature allows Windows applications to directly
access up to 3GB of memory as opposed to the standard 2GB.
The/3GB flag must be set in the Windows
boot.ini file to utilize
also use Oracle AWE (Address Windowing Extensions) to get even
Information on this can be found in Metalink Note 225349.1.
OFA on Windows
Windows, you can still use OFA for your file layout.The idea is the same as UNIX or anyplace else.Here is the 10g Release 1 layout.
of OFAon Windows -
Source: Oracle Database
Platform Guide 10g Release 1 for Windows
C:\oracle--First logical drive
\bin--Subtree for Oracle binaries
\network--Subtree for Oracle Net
\admin--Subtree for database administration files
\prod--Subtree for prod database administration files
commands that do the same things in Windows from the command
prompt as in UNIX, they’re just different (of course).This, as we will see, allows us to do some scripting as
well as work at the command prompt.Below is a simple table showing some common UNIX commands
and their Windows counterparts.
cdcd (plus if changing drives, type the drive letter first)
at, Task Scheduler
move a file
psTask Manager, tasklist
Some Commands are actually the same….
be happy to see that there are some things that work the same
way as in UNIX….
A quick word about FTP….
client piece on Windows (outbound) from your server will work
from the command prompt.However, if you want to FTP
to your Windows
Server, you will need to install Microsoft IIS (Internet
Information Services), and specify the FTP Service.The FTP Service does not get installed by default in the
Windows Server 2003 IIS Installation.
Batch Jobs with the ‘at’ command
command allows you to submit batch jobs from the command line,
using the syntax:
you want a logfile, use cmd.exe /c, and use the > to direct the
output.The path to
the script and the output need to be in the same set of quotes.
cmd.exe /c "c:\scripts\export.bat > c:\scripts\export.log"
Two Handy Utilities
– a public domain program to send emails. What it does is
forward the email to your email spooler/server.
This is very simple to
use; just download the utility, place blat.exe in the
c:\windows\system32 folder, and point it to your email server.Once you’ve done that, you can use the blat command to
send emails from scripts.The website is www.blat.net.
– found in the Windows 2000 Resource Kit,
allows you to run a
batch job every xx seconds. You
can also schedule recurring tasks in the Task Manager in Windows
2000 and above, but it’s a bit tricky.You need to schedule the task, open the Properties after,
choose the schedule tab, click the advanced button, and check
of rerunning a script every 2 minutes using soon.exe:
several things to look for when installing and patching on
in the documentation, and some are not.Some tips and suggestions follow.
-Stop all Oracle Services First
like the OracleServiceSID, Oracle Listener, and any other Oracle
service needs to be stopped, or there will be open DLLs that
cannot be replaced by the Installer.
-Stop the Windows ‘Distributed Transaction Coordinator Service’
This Windows service Coordinates transactions that span multiple
resource managers, such as databases, message queues, and file
systems, and as such may have Oracle files/dlls open.
-Stop any Antivirus Software or backup Agents.
to both improve performance of the install/upgrade as well as to
keep the Antivirus or Backup Software from locking up any files/dlls.
-If you get errors specifying open DLLs, you can find them…
If the Oracle installer reports a specific DLL being open, you
can find out what has that DLL open using the TASKLIST utility.
Tasklist /m filename.dll
will return all the processes that have that DLL open.
You can then kill them using the
taskkill utility if
-The ORACLE_HOME environment variable
ORACLE_HOME environment variable will not get reset properly by
an upgrade. More likely, it will be blank, since the Oracle
Installer clears it out by default.This can cause TNS Protocol Adapter errors when trying to
start the Listener, ODBC datasources to fail, as well as other
problems.So, it is
a good practice to check this following any upgrades.Oracle recommends you not set ORACLE_HOME at all….
-The PATH environment variable
last Oracle Product installed will be the first thing in the
change this with the Environment Tab in Oracle Universal
-Use LSNRCTL to create the Listener Service
first install or upgrade to a new release, you will notice there
is no OracleListener Windows Service.The first time you run LSNRCTL from the command prompt,
provided you have a good listener.ora file, it will create it
-Open DLLs – when all else fails….
If you cannot locate
what is locking a dll, then set all the Oracle Services to
‘manual’ and reboot the server.
Some Useful Routines
following are some items that can make life easier, especially
if you are not using OEM Console. (Meaning, this is somewhat
‘old school’ but it works…)
search the alert log for ORA- errors.
script searches the alert log and emails the DBA when an error
is found.Once you
resolve the error you can edit the alert log and change the
string ORA- to something else (I use ORA.) to stop the emails.It makes use of both soon.exe and the blat utility.This concept can be used for searching all kinds of log
files for a specific string.
::checks for ORA
errors in alert log and emails them off
-t email@example.com -s “ORCL ORA error"
clear out a directory, keeping a certain number of days worth of
script uses the windows forfiles utility to remove files from
the archivelog directory, keeping 7 days worth.Note that the parameters and values have no spaces.For example the –p parameter is for D:\oracle –m is *.*,
and –d is 7.
:: Clear out Archivelog files using forfiles utility
forfiles -pd:\oracle\oradata\orcl\archives -m*.* -d-7 -c"cmd /c
script uses the windows
net start and findstr
commands to check to see if a Windows Service is running.This is handy for more things than just Oracle.It can be used for Application Services, for example.
be sure DB Service is running
service, so drop thru to here and send mail
-t firstname.lastname@example.org -s “ORCL DB Service not running “
Kill a stuck Windows Service/process without rebooting.
series of commands is very helpful.
Sometimes you will try
to stop a Windows Service, and it will hang in a ‘stopping’
state, or it will hang in a ‘starting’ state when starting.The sc queryex command will allow you tofind the process
id of the Service, and then you can use taskkill to kill it.This avoids having to reboot the server to get the
the pid, using scqueryx
(STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
This gives you the PID, so you can kill the process
* the /F flag means 'force'
- shows all
running processes, similar to
- shows all processes and open DLLs
filename.dll - shows what process has a particular DLL
in the Windows 2000 Resource Kit allows a batch file to sleep
for n seconds.
can use the ping utility:
sleep for 20 seconds:
ping -n 20
So there you have it…
Hopefully you have learned
a few things here that will allow you to better manage your new
Windows Oracle server….
Oracle® is the registered trademark of Oracle
Corporation. SQL Server® is the registered trademark of Microsoft
Corporation. Many of the designations used by computer vendors to distinguish their
products are claimed as Trademarks