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%