Re: How to tell if Oracle is ready?

In article <5cn7vh$r0i_at_boursy.news.erols.com>, Scott Mattes <smattes_at_erols.com> writes
>On the computer we run our app on, Oracle is down for a certain time
>each night. The only thing is that we can not be sure exactly when
>this will be (once a human policy is 'set in concrete' people tend to
>forget that it is subject to change). We also have jobs that get
>scheduled to run over night and I need to be sure that they don't run
>until Oracle is up and ready. I also need to get them run as soon as
>possible so that the users have their output waiting for them in the
>morning.
>
>What should I be keying on to determine that the instance is ready to
>work?
>

I wrote a little Pro*C program which attempts to connect to the database and returns 0 if OK and a number of different exit codes for other types of errors (including shutdown in progress).

It traps the oracle error code and accepts 0 and 1017 as OK, everything else as failure. 1017 is invalid username/password. If you get this it means that you must have connected to the database to do the validation so it must be available.

I wrote a couple of shell scripts to go round it which do various things like wait a certain time for the database to come down or come up.

For example (one of) my backup script looks something like this.

db_shut immediate & # shutdown database in background wait_db_down 900 # wait 15 minutes for the database to shutdown if (($?!=0))
 echo SHutdown failed
 exit 1
fi
backup_db cold
db_start immediate & # start up in background wait_db_up 900 # wait 15 minutes for database to shutdown. if (($?!=0)) then
  echo Startup failed
   exit 2
fi

wait_db_up/down wait for the specified timeout value for the database to be in the desired state. If it starts/stops before the timeout they exit with a 0, if they timeout they exit with a 1.

-- 
Jim Smith