Managing Oracle on Windows: Where’s my Oratab?

Posted in: Technical Track

If you manage Oracle on Windows, you probably have wondered why it is so difficult to work out which Oracle instances are running and which ORACLE_HOMEs they use. On Unix or Linux, this is a very simple task.  Oracle services and their ORACLE_HOMEs are listed in the oratab file, located in /etc/ on most platforms and in /var/opt/oracle/ on Solaris.  To find what is running, we would usually use the ‘ps’ command and pipe it through grep to find and run PMON processes.

On Windows, it just isn’t this easy. Each Oracle instance runs in a single monolithic oracle.exe process. Nothing about the process indicates the name of the instance. When we want to find all of the configured Oracle services, we can use the ‘sc’ command, and pipe the results through find (I have added emphasis to the ASM and database instances):

C:\> sc query state= all | find "SERVICE_NAME" | find "Oracle"
SERVICE_NAME: Oracle Object Service
SERVICE_NAME: OracleASMService+ASM1
SERVICE_NAME: OracleClusterVolumeService
SERVICE_NAME: OracleCRService
SERVICE_NAME: OracleCSService
SERVICE_NAME: OracleDBConsoleorcl1
SERVICE_NAME: OracleEVMService
SERVICE_NAME: OracleJobSchedulerORCL1
SERVICE_NAME: OracleOraAsm11g_homeTNSListener
SERVICE_NAME: OracleProcessManager
SERVICE_NAME: OracleServiceORCL1
SERVICE_NAME: OracleVssWriterORCL1

For any one of these services, you can get the current state with ‘sc query’ and the path of the ORACLE_HOME it is using with ‘sc qc’.

C:\> sc query OracleServiceORCL1

SERVICE_NAME: OracleServiceORCL1
        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

C:\> sc qc OracleServiceORCL1

SERVICE_NAME: OracleServiceORCL1
        TYPE               : 10  WIN32_OWN_PROCESS
        START_TYPE         : 3   DEMAND_START
        ERROR_CONTROL      : 1   NORMAL
        BINARY_PATH_NAME   : c:\oracle\product\11.2.0\db\bin\ORACLE.EXE ORCL1
        LOAD_ORDER_GROUP   :
        TAG                : 0
        DISPLAY_NAME       : OracleServiceORCL1
        DEPENDENCIES       :
        SERVICE_START_NAME : LocalSystem

As you can see, the ORACLE_HOME and SID are visible on the line labeled ‘BINARY_PATH_NAME’. Once you have this information, you can set your environment accordingly. It might even be worth your time to write a simple script to do this for you. Maybe you could call it ‘oraenv’!

C:\> set ORACLE_SID=ORCL1
C:\> set ORACLE_HOME=c:\oracle\product\11.2.0\db
C:\> set PATH=%ORACLE_HOME%\bin;%PATH%

Interested in working with Jeremiah? Schedule a tech call.

About the Author

Jeremiah Wilton has over eighteen years of Oracle database administration and systems architecture experience. As Amazon.com’s first database administrator, he helped lead Amazon.com’s database group from the pre-IPO period through the company’s years of exponential growth. He now works for Pythian, a leader in remote database administration for Oracle, Oracle Applications, SQL Server and MySQL. Jeremiah also teaches the Oracle Certificate Program at the University of Washington. At Oracle OpenWorld in 2001, Oracle Education honored Jeremiah as one of the first eight Oracle Certified Masters in the world. Jeremiah is a member of the OakTable, and co-author of the Oak Table’s Oracle Insights. He has presented at numerous conferences and user groups, including Oracle OpenWorld, Collaborate! and UKOUG, and is the author of a variety of technical whitepapers and articles.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *