Home » RDBMS Server » Backup & Recovery » sp2-0640 error while doing coldbackup in crontab.
sp2-0640 error while doing coldbackup in crontab. [message #74445] Fri, 10 December 2004 11:54 Go to next message
Natraj
Messages: 53
Registered: March 2003
Member
Hi ,

I when i am doing cold backup using cron job, i get the error .sp2:0640 not connected in my .lst file. in the log file i created its connecting to the databnase. itshows error as below

02:00.01 1 Files found in: /u01/app/oracle/admin/natraj/cold_bkup_nat
All files removed from /u01/app/oracle/admin/natraj/cold_bkup_nat
02:00.02 Backup commands generated
02:00.02 Shutdown immediate issued for instance raj 02:00.09 Instance raj shutdown
02:00.10 Database backed up to:
         0 Database files
          0 Log files
          0 Copies of control file
          0 .
          0 Password file
        1 Total files backed up
02:00.10 Startup issued for instance raj
02:00.16 Instance raj  started
but says 0 files copied then shuts down the database and s. my tnsping is working. database is up and running.i dont want to stop and start the listener. how do i resolve this?

Report message to a moderator

Re: sp2-0640 error while doing coldbackup in crontab. [message #74446 is a reply to message #74445] Fri, 10 December 2004 23:50 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Unless you post your script, we would have no idea what is happening inside the script.

Report message to a moderator

Re: sp2-0640 error while doing coldbackup in crontab. [message #74452 is a reply to message #74446] Mon, 13 December 2004 05:19 Go to previous messageGo to next message
Natraj
Messages: 53
Registered: March 2003
Member
Hi mahesh,
the script is the same for other databases. it is working for them they r being backed up properly. i guess this is also related with tns..or listener...though tnsping is working.any ideas ?
natraj

Report message to a moderator

Re: sp2-0640 error while doing coldbackup in crontab. [message #74475 is a reply to message #74452] Fri, 17 December 2004 10:52 Go to previous messageGo to next message
Natraj
Messages: 53
Registered: March 2003
Member
Requirements:
# cold_bkup_gen.sql
# .pwget.sh
# .pwtab
#
############################################################################
#
# Set up environment variables:
export ORACLE_BASE=/u01/app/oracle

# Accept input as oracle version and set oracle home accordingly
if [[ $# -gt 1 ]]
then
export ORACLE_HOME=$ORACLE_BASE/product/$2
else
export ORACLE_HOME=$ORACLE_BASE/product/8.1.7
fi
#export ORACLE_HOME=$ORACLE_BASE/product/$2
export LOG_DIR=$ORACLE_BASE/logs
export SCRIPT_NAME=cold_bkup.sh
export SCRIPT_DIR=${ORACLE_BASE}/scripts
export TEMP_DIR=${ORACLE_BASE}/tmp
export ORATAB_DIR=/var/opt/oracle

# Accept input name of database to backup
if [[ $# -gt 0 ]]
then SID=$1
else
echo "ERROR: $SCRIPT_NAME - No instance name specified."
exit 1
fi

# Remove previous output log
export LOG_FILE=${LOG_DIR}/cold_bkup_${SID}.log
if [[ -f $LOG_FILE ]]
then rm $LOG_FILE
fi

# Check if this database is listed in the oratab file
if [[ `egrep -v '^#' $ORATAB_DIR/oratab |awk -F: '{print $1}'|grep $SID|wc -l` -eq 0 ]]
then
echo "ERROR: $SCRIPT_NAME - $SID is not listed in $ORATAB_DIR/oratab" >> $LOG_FILE
exit 1
fi

# Is this instance up? exit if it is not -- cannot create dynamic list of files
if [[ ` ps -fu oracle|grep "ora_[[a-z]]*_$SID"|wc -l ` -eq 0 ]]
then
echo "Warning: $SCRIPT_NAME - Instance $SID is not running. Unable to perform cold backup." >> $LOG_FILE
exit 2
fi

# Set up environment variables:
export ORACLE_SID=$SID
export COLD_BKUP_1=$ORACLE_BASE/admin/$SID/cold_bkup_1

# delete backup pieces from last run.
if [[ -f $COLD_BKUP_1/* ]]
then
TTL_CNT=`ls -l $COLD_BKUP_1/* |wc -l`
TIME_STAMP=`date '+%H:%M.%S'`
echo $TIME_STAMP $TTL_CNT Files found in: $COLD_BKUP_1 >> $LOG_FILE
rm $COLD_BKUP_1/*
echo "All files removed from $COLD_BKUP_1" >> $LOG_FILE
else
TIME_STAMP=`date '+%H:%M.%S'`
echo $TIME_STAMP No files found in: $COLD_BKUP_1 >> $LOG_FILE
fi

# Get traj password
PW=$($ORACLE_BASE/scripts/.pwget.sh $SID traj); export PW;
if [[ $PW = "ERROR" ]]
then
echo "$SCRIPT_NAME Error: Password retrieval - .pwget.sh." >> $LOG_FILE
exit 1
fi

# Generate commands to copy database
COLD_BKUP_CPY=$TEMP_DIR/cold_bkup_cpy_${SID}.lst
export COLD_BKUP_CPY

if [[ -f $COLD_BKUP_CPY ]]
then rm $COLD_BKUP_CPY
fi
$ORACLE_HOME/bin/sqlplus -S /nolog <<EOF
connect dbcron/$PW @$SID
set echo on;
@$SCRIPT_DIR/cold_bkup_gen $SID;
set echo off;

exit;
EOF

if [[ -f $COLD_BKUP_CPY ]]
then
TIME_STAMP=`date '+%H:%M.%S'`
echo $TIME_STAMP Backup commands generated >> $LOG_FILE
else
echo "No backup commands generated " >> $LOG_FILE
exit 2
fi

# Shutdown the instance
TIME_STAMP=`date '+%H:%M.%S'`
echo $TIME_STAMP Shutdown immediate issued for instance $SID >> $LOG_FILE
$ORACLE_HOME/bin/sqlplus -S /nolog <<EOF
connect / as sysdba
shutdown immediate
exit;
EOF

TIME_STAMP=`date '+%H:%M.%S'`
echo $TIME_STAMP Instance $SID shutdown >> $LOG_FILE

# Run commands to backup the database
chmod 740 $COLD_BKUP_CPY
. $COLD_BKUP_CPY

# Following are added to check the Zip files for zero size and email if any found
export LOCAL=`uname -n |tr '[[a-z]]' '[[A-Z]]'`
export LOGFILE=${LOG_DIR}/cold_bkup_chk_zerosize_${SID}.log
export MAILLIST_COLDBKUP_ERROR=/u01/app/oracle/admin/local_email_addr/maillist_coldbkup_error.dat
MSEND_ZEROSIZE_EMAIL=0

echo "" > $LOGFILE
ls -l $COLD_BKUP_1/*.gz | while read line
do
MM=`echo $line | awk '{print $5}'`
if [[ $MM -le 0 ]]
then
MSEND_ZEROSIZE_EMAIL=1
echo "Check Size: $line" >> $LOGFILE
fi
done
if [[ $MSEND_ZEROSIZE_EMAIL = 1 ]]
then
MSEND_ZEROSIZE_EMAIL=0
/usr/ucb/mail -s "Check! $LOCAL: $SID: ZIP files have 0 size" `cat $MAILLIST_COLDBKUP_ERROR` < $LOGFILE
fi
# End of zero-size (zipFile) checking

# Report completion counts
DBFILE_CNT=`ls -l $COLD_BKUP_1/*dbf*|wc -l`
LOGFILE_CNT=`ls -l $COLD_BKUP_1/*log*|wc -l`
CTL_CNT=`ls -l $COLD_BKUP_1/control*|wc -l`
INITORA_CNT=`ls -l $COLD_BKUP_1/init*.ora|wc -l`
INITORA_FULL_NAME=`ls $COLD_BKUP_1/init*.ora`
INITORA_BASE_NAME=`basename $INITORA_FULL_NAME`
PWFILE_CNT=`ls -l $COLD_BKUP_1/orapw*|wc -l`
TTL_CNT=`ls -l $COLD_BKUP_1/* |wc -l`

TIME_STAMP=`date '+%H:%M.%S'`
echo $TIME_STAMP Database backed up to: $COLD_BACKUP >> $LOG_FILE
echo " " $DBFILE_CNT Database files >> $LOG_FILE
echo " " $LOGFILE_CNT Log files >> $LOG_FILE
echo " " $CTL_CNT Copies of control file >> $LOG_FILE
echo " " $INITORA_CNT $INITORA_BASE_NAME >> $LOG_FILE
echo " " $PWFILE_CNT Password file >> $LOG_FILE
echo " " $TTL_CNT Total files backed up >> $LOG_FILE

# Restart the instance
TIME_STAMP=`date '+%H:%M.%S'`
echo $TIME_STAMP Startup issued for instance $SID >> $LOG_FILE

# Startup the instance
$ORACLE_HOME/bin/sqlplus -S /nolog <<EOF
connect / as sysdba
startup
exit;
EOF

TIME_STAMP=`date '+%H:%M.%S'`
echo $TIME_STAMP Instance $SID started >> $LOG_FILE
#

Report message to a moderator

Re: sp2-0640 error while doing coldbackup in crontab. [message #74491 is a reply to message #74475] Mon, 20 December 2004 03:54 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Issue is with your listener.
If you cannot afford to restart, just
Try To reload the listener

bash-2.03$ lsnrctl reload

LSNRCTL for Solaris: Version 9.2.0.4.0 - Production on 20-DEC-2004 08:54:28

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mutation)(PORT=1521)))
The command completed successfully


First, check your sqlnet.ora.
If you are not using any configurations as mentioned,
COMMENT of all the entries and try again.

Also in the serverside Listener.ora try interchanging
HOST=hostname
with
HOST=IP.Number
vice versa
and you can always test your configuration, with
lsnrctl reload ( instead of bouncing the listener).

Report message to a moderator

Re: sp2-0640 error while doing coldbackup in crontab. [message #74505 is a reply to message #74491] Tue, 21 December 2004 06:50 Go to previous messageGo to next message
Natraj
Messages: 53
Registered: March 2003
Member
Hi,
i havent reloaded the listener yet, however my server has 9.2.0.44 and 9.2.0.1, the 9.2.0.4 cold backup runs,whereas 9.2.0.1 fails. again in the env parameters, for 9.2.0 db, my tns_admin is configured to take 9.2.0.4, tnsping works correctly. do i need to look into any other parameter in the environmental settings of 9.2.0 database?why do you think the lisntener needs to be reloaded?

Report message to a moderator

Re: sp2-0640 error while doing coldbackup in crontab. [message #74507 is a reply to message #74505] Tue, 21 December 2004 07:14 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Nope.
I beleive, i didnt make it clear.

In your previous posting, you have said.
>>but says 0 files copied then shuts down the database and s. my tnsping is working. database is up and
>>running.i dont want to stop and start the listener. how do i resolve this?

i have said
>>If you cannot afford to restart, just
>>Try To reload the listener

listener RELOAD is just another option to make the changes in listener.ora effective ( without bouncing the listener).

Please try the other things mentioned in previous post.
if there are multiple versions of databases, check whther your script is handling them properly.

>>.. u said u dont go back and see t.. so i am pasting
>>what i wanted to ask

again, what i meant is ,
Only IF you are responding to my post, i will get an email notice. Thus i get to know there is some activitiy.
If you respond to yourself ( as you did in one of your previous posts) i get no notice at all.
I never manually check all the postings.

Report message to a moderator

Re: sp2-0640 error while doing coldbackup in crontab. [message #74520 is a reply to message #74507] Fri, 24 December 2004 04:53 Go to previous messageGo to next message
Natraj
Messages: 53
Registered: March 2003
Member
Mahesh,
the listener is all fine.Actually when i run this cold backup script using cronjobsie when i schedule it, i get the error sp2-0640, however if i run it using nohup commenad.or for taht matter execute it immediately it goes through. i think trouble is with enironmental settings.. how does cronjob differ from nohup in this unique manner?
Thanks Natraj

Report message to a moderator

Re: sp2-0640 error while doing coldbackup in crontab. [message #74521 is a reply to message #74520] Fri, 24 December 2004 09:06 Go to previous message
Natraj
Messages: 53
Registered: March 2003
Member
i got it amahesh, error was my oracle_home was 9.2.0... however in 9.2.0/network admin. i didnt have entry in tnsnames.ora.i had rntry of db in 9.2.0.4 's tnsnames.ora... once i added that it went thru clearly via cronjobs. need to research more on nohup..:0 unix is really interestng..
Thanks Natraj

Report message to a moderator

Previous Topic: database recovery
Next Topic: RMAN error while cloning hot databaseupto a particular time.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Feb 01 22:34:37 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.