The 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 more details.
The Hammer and Saw Mindset
When you 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
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
One of 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/Linux, Oracle uses separate processes to run all the background
tasks (PMON, SMON, LGWR, etc). Also, each database connection uses
yet another UNIX/Linux process. On Windows, though, all of these
processes are implemented as threads inside one single process
called oracle.exe. The 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
oracle.exe. There will be one oracle.exe process for each instance
you have running on the server. Items such as the Listener, OEM, etc
will still have separate processes.
One of 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 services. The �net start� command at the command
prompt will display the services. This, as you will see later, can
be used in scripting.
The 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:
Oradim �new �sid ORCL �intpwd <pwd>
�maxusers 4 startmode auto �pfile
An example of deleting the Instance for ORCL is:
Oradim �delete �sid ORCL
An example of a shutdown and startup. These can be used in scripts,
such as for a cold backup.
Oradim �shutdown �sid ORCL �usrpwd /
-shuttype inst, srvc �shutmode i
Oradim �startup �sid ORCL
The ORA_DBA group
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 = sign):
This needs to be done prior to running SQLPlus, doing an
C:\>sqlplus �/ as sysdba�
In 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 are for.
HKEY_LOCAL_MACHINE\Software\Oracle - is the home registry key.
- ORA_sid_PFILE �
the path to your pfile
- ORA_sid_AUTOSTART � tells whether the sid should start when
the service does. Generally this should be TRUE
- ORA_sid_SHUTDOWN � tells whether to shutdown when the
service stops. Again, this 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_LANG � nls language setting for the server
32Bit vs 64Bit Windows
The difference between 32bit and 64bit Windows is obviously
address space. The big thing to look out for here is that 32bit
Windows limits you to 2GB for the SGA and ALL the other Oracle
support jobs AND user jobs for an Oracle Instance. (Remember the
Threaded Model discussed earlier).
If you 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!
If you 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. It does this by �borrowing� 1GB from
the OS memory.
In 32 bit Windows, the maximum address space is 4GB � the default
is 2GB for Windows Process and 2GB for the OS. The /3GB switch
simply tells Windows to reallocate 1GB of the OS memory and use it
for the Windows User Process.
The /3GB flag must be set in the
Windows boot.ini file to utilize this feature.
You can also use Oracle AWE (Address Windowing Extensions) to get
even more memory. Information on this can be found in Metalink Note
Get the Complete
Oracle Tuning Details
The landmark book
Tuning: The Definitive Reference Second Edition" has been
updated with over 1,150 pages of expert performance tuning tips.
It's packed with scripts and tools to hypercharge Oracle 11g
performance and you can
buy it for 40% off directly from the publisher.
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA
1996 -2017 by Burleson. All rights reserved.
Oracle® is the registered trademark of Oracle
Corporation. SQL Server® is the registered trademark of Microsoft
Many of the designations used by computer vendors to distinguish their
products are claimed as Trademarks