Recover standby database in a srcipt

[maddave]

Can anyone shed some light on this problem. Firstly, we have a cron job which transfers all our redo logs from a primary database server to a standby database server. Both running Oracl9i on Red Hat Linux Ent 4.

I'm trying to write a script to do a recover on the standby databse some time in the evening after all the redo logs have been transferred.

The folloing command is what the cron script does:

sqlplus -s "/as sysdba" @recover.sql

The recover.sql script then contains the following command:

alter database recover managed standby database disconnect;

When I run this command manually from a sqlplus prompt I get
"database altered" returned and the recover can be seen to be running by looking at the managed_standby view. However, when running the script, I receive the following error after the alter command has been issued:
ERROR at line 1:
ORA-01O34: Oracle not available.

The database had been started by "startup nomount" and then "alter database mount standby database" command had been issued before running the script. If after running the script and logging into sqlplus manually and issuing the same command as above, the command works fine.

Any idea?

 

[LKBrwnDBA]

Maybe you need a "space" between the '/' and the 'as sysdba'

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[maddave]

Thanks for the reply, but its not that. Still gives same error.

 

[hoinz]

Have you set environment variables in your cron script,
like ORACLE_SID and ORACLE_HOME?

 

[maddave]

Sorry yes forgot to mention, all the env settings are set correctly. I even echo'ed ORACLE_SID and ORACLE_HOME to a file to make sure they have been set and all is as expected.

 

[Scunningham99]

Have you tried to select name from v$database from within the script, replacing alter database just to see that you can get on to the database.

you could try the following instead of calling the @recover.sql from within your main script:-

$ORACLE_HOME/bin/sqlplus "/as sysdba" << EOF
recover managed standby database disconnect from session;
exit
EOF



Sy UK

 

[maddave]

Sorry, I should really put all the info down shouldn't I!

I have tried both, selecting the name from v$database and it returns the same error oracle not avaialable.

I tried the EOF way as well but again, the same thing happened.

Here is the full script the sqlplus -s command is commented out as I was trying the EOF below it.

#!/usr/bin/ksh
# setup Oracle environment
ORACLE_BASE=/oracle9/app/oracle ; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/ ; export ORACLE_HOME
ORACLE_SID=SPCC ; export ORACLE_SID
ORACLE_TERM=xterm ; export ORACLE_TERM

# Not mandatory
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/obackup/lib
export LD_LIBRARY_PATH
ORA_NLS32=$ORACLE_HOME/ocommon/nls/admin/data ; export ORA_NLS32
ORACLE_DOC=$ORACLE_BASE/doc
ORACLE_PATH=.:$ORACLE_HOME/bin:$ORACLE_HOME/obackup/bin:/opt/bin:/usr/bin
export ORACLE_PATH
TMPDIR=/oracle9/tmpdir ; export TMPDIR
WORKDIR=/oracle9/vartmp ; export WORKDIR
OBK_HOME=$ORACLE_HOME/obackup ; export OBK_HOME
EBU_HOME=$ORACLE_HOME/obackup ; export EBU_HOME
DEF_INSTALL=TRUE ; export DEF_INSTALL
PATH=/usr/local/bin:$ORACLE_PATH:$EBU_HOME/bin:$ORACLE_LOCAL_BIN:$PATH

#Log
LOG=$HOME/scripts/redo_log
export LOG
echo "Starting recovery of SPCC database `date`" > $LOG

#Firstly check that all the files have updated by checking the .done file has been transferred. If not dont do the update
ls -a $HOME/scripts/.done ; DONE=$?
if [[ $DONE -ne 0 ]]
then
echo "Files not transferred fully check cron script on Star database" >>
$LOG
exit
fi

#Do the recovery
cd $HOME/scripts
#sqlplus -s "/as sysdba" @recover.sql; SQL_RETURN_CODE=$?
#if [[ $SQL_RETURN_CODE -ne 0 ]]
# then
# echo "SQL Statement error" >> $LOG
# echo "Exiting Program" >> $LOG
# exit
#fi

sqlplus << EOF
connect / as sysdba
select name from v$database;
alter database recover managed standby database disconnect;
spool off
EOF

echo "Redo logs applied to database." >> $LOG

#remove the .done file ready for the next ftp from Star
#rm -f $HOME/scripts/.done


And the recover.sql file just contains
alter database recover managed standby database disconnect;

 

[dbtoo2001]

use /nolog instead of -s

 

[LKBrwnDBA]

I suspect you are getting some other error before the "Oracle not available" and maybe it's: "Not connected"

Issue the sqlplus command without the '-s' to see if maybe the "/ as sysdba" is not connecting.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[maddave]

Well Im a litte confused now. It must be some env setting Im missing. I thought I would try the above suggestion and up until now have been running the script on the server itself, but whilst at my PC I thought I would login via Absolute Telnet and run the script so I could copy and paste the errors I receive. And it worked as expected! The database name was shown and the recover process started and sat there waiting for the final redo log file.

But going back to the server and running it in a shell it gives the same error! I haven't actually tried running this script via cron itself but might give it a go as I wonder if my user settings etc could be causing problems?!

 

[hoinz]

Based on LKBrwnDBA's idea, one more question:
Are you using the same Linux user both for your cron script, and for your successful manual call of sqlplus?
Usually root, like most users, cannot 'connect / as sysdba'.

 

[maddave]

Yep, the user is oracle9 which is in only the DBA group and is the one I login aswell as the one who runs the cron script.

Ive just tried the script in cron and it failed with same errors.

Im gonig to strip out all the ENV variables and just have ORACLE_HOME, ORACLE_SID and ORACLE_HOME and see if this makes any difference.

 

[maddave]

Found it!

Found that I had LD_ASSUME_KERNEL=2.4.19 set in my users profile, but this wasn't being exported with the script for some reason.

All working now as expected!

 

[maddave]

Oh and thanks for all the replies.