Oracle on Windows – Things to Know Plus a Few Tricks Part 3

By Mark Sorger

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. You can also omit the ‘soon.exe’ execution and submit with the Windows Task Scheduler instead of ‘at’.

::
:: 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. Again one can remove the ‘soon.exe’ code and use the Windows Task Scheduler.

::
:: 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 sc queryx command
   
   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…

Hopefully you have learned a few things here that will allow you to better manage your Windows Oracle server….