But, I Don’t do Windows…Oracle on Windows for UNIX Folks and Windows Newbies

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

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.

OFA on Windows

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

 Example of OFA  on Windows - Source: Oracle Database Platform Guide 10g Release 1 for Windows

C:\oracle             --First logical drive

    \ora10            --Oracle home

      \bin            --Subtree for Oracle binaries

      \network        --Subtree for Oracle Net

      \...

    \admin            --Subtree for database administration files

      \prod           --Subtree for prod database administration files

        \adhoc        --Ad hoc SQL scripts

        \adump        --Audit files

        \bdump        --Background process trace files

        \cdump        --Core dump files

        \create       --Database creation files

        \exp          --Database export files

        \pfile        --Initialization parameter file

        \udump        --User SQL trace files

F:\oracle             --Second logical drive (two physical drives, striped)

    \oradata          --Subtree for Oracle Database files

      \prod           --Subtree for prod database files

        redo01.log    --Redo log file group one, member one

        redo02.log    --Redo log file group two, member one

        redo03.log    --Redo log file group three, member one

G:\oracle             --Third logical drive (RAID level 5 configuration)

    \oradata          --Subtree for Oracle Database files

      \prod           --Subtree for prod database files

        control01.ctl --Control file 1

        indx01.dbf    --Index tablespace datafile

        rbs01.dbf     --Rollback tablespace datafile

        system01.dbf  --System tablespace datafile

        temp01.dbf    --Temporary tablespace datafile

        users01.dbf   --Users tablespace datafile

H:\oracle             --Fourth logical drive

    \oradata          --Subtree for Oracle Database files

      \prod           --Subtree for prod database files

        control02.ctl --Control file 2

Windows to UNIX Commands

There are 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.

UNIX              WINDOWS

cat                    type, copy

cd                    cd (plus if changing drives, type the drive letter first)

                        e.g. C:>D:

                D:>cd D:\test

cp                    copy, xcopy

cron                 at, Task Scheduler

• 

ftp                    ftp

• 

grep                 find, findstr

• 

ls                      dir

• 

man                 help

• 

mkdir               mkdir

more                more

mv                   rename  - to rename,   move  -  actually move a file

netstat              netstat

nslookup          nslookup

ping                 ping

ps                     Task Manager, tasklist

pwd                 cd

rm                    del

rmdir                rmdir

telnet                telnet

traceroute         tracert

who                 net session

Some Commands are actually the same….

You will be happy to see that there are some things that work the same way as in UNIX….

•          ping

•          netstat

•          ftp

•          more

•          mkdir

•          nslookup

•          Pipes  

Example::   dir | findstr “<DIR>”

A quick word about FTP….

The 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

The ‘at’ command allows you to submit batch jobs from the command line, using the syntax:

at hh:mm /every:d  <path_to_batch_file>

Example:

at 20:00 /every:M,T,W,Th,F c:\adminscripts\exports\export.bat

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

Example:

at 20:00 /every:M,T cmd.exe /c "c:\scripts\export.bat > c:\scripts\export.log"

Two Handy Utilities

Blat.exe – 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.

Example of initial setup (only done once):

Blat –install my_smtp.edu Userid_on_email_server

Using blat to send an email:

Syntax: Blat <filename> -t <destination emailaddress> -s <subject>

blat D:\oracle\oradata\orcl\export\exp.log -t admin@email.com -s "Exp complete"

Soon.exe – 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 recurring event.

Example of rerunning a script every 2 minutes using soon.exe:

c:\adminscripts\soon\soon.exe 0120 c:\adminscripts\uptime\check_DBSvc.cmd

Tips for Doing Oracle Installs/Patches

There are several things to look for when installing and patching on Windows.  Some are in the documentation, and some are not.  Some tips and suggestions follow.

-Stop all Oracle Services First

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

This is 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 need be.

-The ORACLE_HOME environment variable

The 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

The last Oracle Product installed will be the first thing in the PATH.  You can change this with the Environment Tab in Oracle Universal Installer.

-Use LSNRCTL to create the Listener Service

When you 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 for you.

-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

The 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…)

Script to search the alert log for ORA- errors.

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

:: 

::  checkdb.bat

::  checks for ORA errors in alert log and emails them off

::

::  rerun every 60 minutes/3600 seconds

::

c:\adminscripts\soon\soon.exe 3600 c:\adminscripts\checkdb\checkdb.bat

::

::  look for ORA- in alertlog

::

@findstr "ORA-" c:\oracle\admin\orcl\bdump\alert_orcl.log && call :send_mail

goto :eof

::

::  Subroutine to send mail

::

:send_mail

del /Q  c:\adminscripts\checkdb\error.txt

findstr "ORA-" c:\oracle\admin\orcl\bdump\alert_orcl.log > c:\adminscripts\checkdb\error.txt

Blat  c:\adminscripts\checkdb\error.txt -t userid@email.xxx -s “ORCL ORA error"

:eof

exit

Script to clear out a directory, keeping a certain number of days worth of files.

This 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 dir @FILE"

forfiles -pd:\oracle\oradata\orcl\archives -m*.* -d-7 -c"cmd /c del/q @FILE"

exit

Script to check to see if a service is running.

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

::

::  check_DBSvc.cmd  -  checks to be sure DB Service is running

::

::  rerun every 2 minutes/120 seconds

::

c:\adminscripts\soon\soon.exe 0120 c:\adminscripts\uptime\check_DBSvc.cmd

::

::  Check the time and exit if 10pm, 11pm,

::

del /Q c:\adminscripts\uptime\time.txt

@echo %time:~0,2% > c:\adminscripts\uptime\time.txt

findstr /C:"22" c:\adminscripts\uptime\time.txt && goto :eof

findstr /C:"23" c:\adminscripts\uptime\time.txt && goto :eof

::

::  Look for DB service from NET START. If we find it exit, else send mail

::

del /Q c:\adminscripts\uptime\services.txt

net start > c:\adminscripts\uptime\services.txt

findstr /C:"OracleServiceORCL" c:\adminscripts\uptime\services.txt && goto :eof

::

::  didn't find service, so drop thru to here and send mail

::

:send_mail

Blat  c:\adminscripts\uptime\services.txt -t userid@email.com -s “ORCL DB Service not running “

::

::  The End

:eof

Exit

How to Kill a stuck Windows Service/process without rebooting.

This 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 service ‘unstuck’

1.)           Find the pid, using scqueryx

C:\>  sc queryex OracleServiceORCL

SERVICE_NAME: OracleServiceORCL

           TYPE               : 10  WIN32_OWN_PROCESS

                STATE              : 4  RUNNING

                                     (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)

            WIN32_EXIT_CODE    : 0  (0x0)

           SERVICE_EXIT_CODE  : 0  (0x0)

                CHECKPOINT         : 0x0

                     WAIT_HINT          : 0x0

               PID                : 357

                     FLAGS              :

2.)           This gives you the PID, so you can kill the process

C:>  taskkill /PID 357 /F

* the /F flag means 'force'

The tasklist utility

·         tasklist  - shows all running processes, similar to ps -ef

·         tasklist /m - shows all processes and open DLLs

·         tasklist /m filename.dll - shows what process has a particular DLL open

How to ‘sleep’

Sleep.exe in the Windows 2000 Resource Kit allows a batch file to sleep for n seconds.

Or, you can use the ping utility:

ping -n seconds+1 127.0.0.1>nul

To sleep for 20 seconds:

ping -n 20 127.0.0.1>nul

So there you have it…