RE: Need oracle Script

You can use/modify following script to find out which DB's are not accessible and which are. Checking the presence of the background processes does not necessarily mean that the databases are accessible. One needs to login to find that out. The script attempts to connect to each database via SQL*Net (assumption is that the Listener process is up and running, if it is not, the databases are not accessible) using a false id/password and then checking for specific errors to determine the up/down status of the database. If any database did not start at reboot, chances are, it won't still start until the problem is corrected. So, I do not see any point in trying to start the failed database without investigating the reason for failure. Good place to start is its alert log. Another such script that does more checking of the database status can be found at Steve Adams' site http://www.ixora.com.au

Hope this helps....
- Kirti

#!/usr/bin/ksh
#
DBNAMES=`cat /etc/oratab | egrep -v '^#|^$' | awk -F: '{print $1}'`

> /tmp/$$downdb.lst
> /tmp/$$updb.lst

for DB in $DBNAMES
do
sqlplus -s << EOF > /tmp/$$.1
whenever sqlerror exit
aaa/aaa@$DB
exit;
EOF egrep 'ORA-121|ORA-01034' /tmp/$$.1 > /dev/null if [[ $? = 0 ]]
then

   echo "Database '$DB' is _NOT_ accessible" >> /tmp/$$downdb.lst else

   grep 'ORA-01017' /tmp/$$.1 > /dev/null    if [[ $? = 0 ]]
   then
     echo "Database '$DB' UP and Accessible" >> /tmp/$$updb.lst    else
    echo "Database '$DB' is _NOT_ accessible" >> /tmp/$$downdb.lst    fi
fi
done
cat /tmp/$$downdb.lst | mailx -s "Down Databases " your_email_address_at_your.domain
cat /tmp/$$updb.lst | mailx -s "Up & Running Databases" your_email_address_at_your.domain

rm /tmp/$$*

#--------- End of File --------------

    -----Original Message-----
> From: puja rahul [SMTP:puja-r_at_mailcity.com]
> Sent: Friday, June 16, 2000 8:56 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Need oracle Script
>
> Hi,
>
> I'm aware of the dbstart and dbshut scripts but I was wondering if there
> is any script within oracle to see if all the databases mentioned in the
> /etc/oratab file are up and running - and if for some reason the database
> is not up - bring it up and online.
> Instead for wrting a script taking the output of ps -ef to check ,which
> will be little complicated I was trying to figure out if someone has a
> script in place doing the
> same job.
>
> I would highly appreciate the help.
>
> THanks in advance.
>
> cheers.
> --
>
> On Fri, 16 Jun 2000 05:39:47
> Chuck Hamilton wrote:
> >
> >You already have it. Check out $ORACLE_HOME/bin/dbstart.
> >
> >
> >puja rahul
> >wrote:
> >
> >Hi ,
> >
> >I'm a unix admin and not at all a DBA.So I need some help in ORACLE.
> >I have 12 instances of ORACLE running on my production box -\
> >( DIGITAL UNIX 4.0D is the OS on the box )
> >For some reasons at times after the night backup few data bases are not
> coming up-
> >I want to have a script that can run every morning after the backup is
> complete to check the status of all the databases and if Any of the them
> is not running do a clean startup of the databases which were not running
> and send a mail saying that all datbases are up and running.
Received on Fri Jun 16 2000 - 22:29:09 CDT