Donald K. Burleson Oracle Tips

Windows Utilities for Monitoring Oracle

The Oracle Counters for Windows Performance Monitor package is not installed by default. In order to install it when Oracle is installed, select the custom install option. This option can also be installed later via the Oracle installer. Select both custom installation and the Oracle for Windows Performance option and that will install this package.

Once Oracle Counters for Windows Performance Monitor has been installed, perform one more piece of the setup. The Oracle performance counters are set up to monitor one Oracle instance. Information about this instance must be configured in the registry.  The command to configure this is:

• The Oracle Buffer Cache. This is where the cache miss ratio can be seen.
• Shared Pool Stats. This collection includes the data dictionary cache and the library cache. 
• Log Buffer. Provides information on log space requests.  
• Database Data Files. This object provides physical read and write per second counters.  
• DBWR stats. Provides information on the DB Writer processes.  
• Miscellaneous. Other statistics include dynamic space management, free lists and dynamic sorts.

• Processor: %Processor Time. This gives a quick look at how busy the system is. 
• Physical Disk: Avg. Disk sec/Read, Avg. Disk sec/Write. This provides an overview of how well the I/O subsystem is doing.

Checking Windows Services for Oracle

1. Check Task Manager for the ORACLE.EXE process.  If it is present, then the service started.
2. Check the Alert Log for the database.  If the problem is with the database, there will probably be an entry in the alert log.  If the problem is not with the database, there will not be any entry in the alert log.
3. Check the oradim.log in the $ORACLE_HOME/database directory for errors.  Check the date on the log file as versions before 9i did not date/time stamp the entries.

If the database starts, the problem is in the service.

To check the Windows service:

1. Open the registry with Regedit.  Always back up the registry before making changes. Navigate to the key entry in the Windows registry:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_oracle_home_name 

2. There will be a key called ORA_SID_AUTOSTART.  SID is the database SID. This key should be set to TRUE.  If not, the server starts but not the database.  There is also an ORA_SID_SHUTDOWN which should be TRUE so that if the server is shut down, the service will shutdown the database.

3. In order to set the database to manual start mode, set ORA_SID_AUTOSTART to FALSE.  The service will start but not the database. 

Test the Windows Oracle Service

If the ORA_SID_AUTOSTART setting was the problem, change it to TRUE and then test the service by stopping and then restarting the service to see if the database automatically starts.  If it does, then reboot the Windows server to verify that the database will start automatically.  Sometimes the service will work, only to fail again after a reboot.  If the service fails after rebooting, recreate the service.  The ORADIM utility is used to recreate the service.

• SQLPATH - If the choice is to keep all scripts in one place, SQLPATH is what needs to be used to centrally manage and store the scripts. The default location is ORACLE_HOME\dbs. Generally, this value is changed by the user as it is bad practice to store user-created SQL in a software installation directory.

• ORA_SID _AUTOSTART - In Windows, the service can run without the database instance, but the instance can not run without the service.

• Upon a reboot or startup, have the database(s) automatically start up - the service is set to start automatically, which in turn enables the instance to be started, which in turn means the database is open for business. With this parameter set, also perform shutdown and startup simply by stopping and starting the service.

• ORA_SID_SHUTDOWN - This is similar to the autostart parameter.  If set to true, the database is shutdown when the service is stopped.

• ORA_SID_SHUTDOWNTYPE - One of three values can be set for this parameter: "a" for abort, "n" for normal, or "i" for immediate.  Immediate is the default, and recommended, value.  Either the entire word (abort, normal, immediate), or just the first letter may be used.

• ORA_SID_SHUTDOWN_TIMEOUT - To be on the safe side of things, i.e. ensure that there is a clean shutdown if not using the abort option, a time (in seconds) can be set for the service to wait before stopping.

Copyright © 1996 -  2011 by Burleson Enterprises. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.