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%

Source: https://www.pythian.com/blog/wheres-my-oratab/