Oracle on Windows � Things to Know Plus a Few Tricks Part 1

By Mark Sorger

Overview

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 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

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.

Windows Services

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.

ORADIM Utility

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 initSID.ora

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.

Setting ORACLE_SID

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):

C:\>set oracle_sid=SID

This needs to be done prior to running SQLPlus, doing an export/import, etc.

Example:

C:\>set oracle_sid=orcl

C:\>sqlplus �/ as sysdba�

The Registry

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 225349.1.