Re: cannot spool the mounted standby database through cron job shell script. Any other suggestions?
Date: Mon, 27 Jun 2011 16:27:31 -0400
Message-ID: <BANLkTimCNdkuRN=gUMyyU7b9TtJcVZd04Q_at_mail.gmail.com>
Hi All,
changing sqlplus -s /nolog << EOF to "$ORACLE_HOME"/bin/sqlplus -s /nolog << EOF did the trick. Many Thanks for all your help. I am very happy to be part of this list. I was making sure that i set my environment right but never thought that the complete path of using sqlplus would do the trick. I will include the complete path of sqlplus from now on. Good learning.
On Mon, Jun 27, 2011 at 4:21 PM, Guillermo Alan Bort <cicciuxdba_at_gmail.com>wrote:
> Just to see what is happening, please do the following:
>
> 1: modify the cron entry to
> 40 15 * * * /app/oracle/sm/test.sh invest1 > > <full_path>/<name>.log 2>&1
>
> 2: add set -x at the top of the script (right after #!/bin/bash), this will
> set debug mode.
>
> then post all the logs.
>
> cheers
>
>
>
>
> Alan.-
>
>
>
> On Mon, Jun 27, 2011 at 4:46 PM, sundar mahadevan <
> sundarmahadevan82_at_gmail.com> wrote:
>
>> Hi All,
>> Thanks for the response. Here is the complete script. I did set the
>> oracle_sid and oracle_home. There are no error messages. The spool file does
>> not get created. If i run it from command prompt, i am able to get the spool
>> file created. I tested this on 11gse1(linux) as well as 10gr2 EE(AIX). Also
>> i tested this on a open database and even it does not create the file. So
>> this has nothing to do with the status of the database. Can someone try it
>> out for me and let me know if you are facing the same issue. I have all
>> permissions to write/create files on the directory.
>>
>> This is how my cronjob looks: 40 15 * * * /app/oracle/sm/test.sh invest1
>>
>> #!/bin/bash
>> #sqplus will return last archive log applied result to the screen if
>> manually run
>>
>> SCRIPT_DIR=/u01/app/oracle/admin/escenic/standby
>> FULL_LOG_NAME=${SCRIPT_DIR}/test.log
>>
>> export TARGET_SID="$1"
>> export TARGET_SID=`echo ${TARGET_SID} | sed -e 's/ //g'`
>> export TARGET_SID=`cat /etc/oratab | grep -i "${TARGET_SID}:" | grep -v
>> "^#" | cut -d : -f 1`
>> export ORACLE_SID=${TARGET_SID}
>> export ORACLE_HOME=`cat /etc/oratab | grep ${TARGET_SID}: | grep -v "^#" |
>> cut -d : -f 2`
>>
>> echo "OS->${ORACLE_SID} OH->${ORACLE_HOME}" >> ${FULL_LOG_NAME}
>> cd ${SCRIPT_DIR}
>> echo "PWD"`pwd` >> ${FULL_LOG_NAME}
>> echo "" >> ${FULL_LOG_NAME}
>>
>> sqlplus -s /nolog << EOF
>> set head off
>> connect / as sysdba
>> spool abcd.log
>> select max(sequence#) max_seq from v\$log_history;
>> spool off
>> quit
>> EOF
>>
>>
>> cat test.log
>> OS->escenic OH->/u01/app/oracle/11gse1
>> PWD/u01/app/oracle/admin/escenic/standby
>>
>>
>> On Mon, Jun 27, 2011 at 2:48 PM, Bill Zakrzewski <bill_at_intactus.com>wrote:
>>
>>> What is the exact error you are getting?
>>>
>>> On Jun 27, 2011, at 2:10 PM, sundar mahadevan wrote:
>>>
>>> > Hi All,
>>> > Greetings. I have a standby in mount state. I would like to query the
>>> latest archive log applied on standby (in mount state) to apply the archive
>>> logs from primary with a cron job shell script. When i query "select
>>> max(sequence#) from v\$log_history;" from a shell script it works. But if i
>>> set it up as cron job as below, it does not work. I cannot assign the output
>>> to a shell variable since the database is not open and it would crap out.
>>> Any other suggestions? And the reason for this behaviour? Thanks a lot in
>>> advance.
>>> >
>>> > test.sh
>>> > #!/bin/bash
>>> > sqlplus -s /nolog << EOF
>>> > set head off
>>> > connect / as sysdba
>>> > spool last_applied_on_standby.log
>>> > select max(sequence#) from v\$log_history;
>>> > spool off
>>> > quit
>>> > EOF
>>>
>>>
>>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jun 27 2011 - 15:27:31 CDT