1 Reply Latest reply: Nov 3, 2008 6:54 PM by ebrian RSS

    Batch script to test if Oracle instance is up on windows 2003

      I create the batch job from command line to test the oracle 10g, if the instance is up return oracle_sid is ready, this is running on windows 2003, test.bat
      @echo off
      echo sqlplus DBAUSER/PASSWORD@%ORACLE_SID% > 1.sql
      echo set echo off feed off pages 0 >> 1.sql
      echo whenever sqlerror exit sql.sqlcode >> 1.sql
      echo select * from global_name; >> 1.sql
      echo exit; >> 1.sql

      sqlplus -s /nolog @1.sql >NUL:
      IF {%ERRORLEVEL%} == {0} (
      echo %ORACLE_SID% is ready
      echo %ORACLE_SID% is not ready.

      echo End of script

      But it seems always display both lines oracle_sid is ready and not ready, do you know how to fix it?
        • 1. Re: Batch script to test if Oracle instance is up on windows 2003
          This may satisfy your needs for Windows. You will have to modify the settings for your environment. Obviously, this is not an exhaustive test, but the process should provide a cursory status of your database.
          @echo off
          set oracle_home=E:\oracle\product\10.2
          set path=E:\oracle\product\10.2\bin
          set oracle_sid=orcl10
          echo conn / as sysdba
          echo set head off feed off pages 0
          echo select status from v$instance;
          ) | sqlplus -s /nolog > %TEMP%\status.log
          for /f "tokens=3" %%i in ('%SystemRoot%\system32\find /c "OPEN" %TEMP%\status.log') do (
          if %%i == 1 (
          echo Database is OPEN
          ) ELSE (
          echo Databse does NOT appear to be OPEN
          for /f "tokens=4"  %%i in ('%SystemRoot%\system32\sc query oracleserviceorcl10 ^| %SystemRoot%\system32\find "STATE"') do echo %%i