Copying Archived Logfile from ASM to a file system

Following OTN tread http://forums.oracle.com/forums/thread.jspa?threadID=2154801&tstart=0 about copying archived logfiles from ASM to a UNIX filesystem, I am posting a script we used in the past.

Nowadays we use RMAN to get the files from ASM directly into a TSM diskpool, but back then we used another method for putting the archived logfiles on backup, which resulted in the need for this script:

# Script to copy archived logfiles from ASM to a file system on UNIX

# In this case all archived logfiles found in v$archived_log are copied to a staging mount point /opt/asmprd/ar01

# The username/password used to access the database need to be set before use of this script.

# For sake of security I removed these lines from this script

#

# The script needs to be run as owner of the ASM instance

#

#

export ORACLE_SID=WCSPRD

export FSFULL=FALSE

export ORACLE_BASE=/opt/$LOGNAME/ora

export CRS_HOME=/opt/crsprd/ora/11.1.0

export ORA_CRS_HOME=$CRS_HOME

export ASM_HOME=/opt/asmprd/ora/11.1.0

export ORA_ASM_HOME=$ASM_HOME

export ORACLE_HOME=$ORACLE_BASE/11.1.0

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$CRS_HOME/lib:$ORACLE_HOME/lib32:$CRS_HOME/lib32

export LIBPATH=$LD_LIBRARY_PATH

export PATH=$ORACLE_HOME/bin:$CRS_HOME/bin:$PATH

export TNS_ADMIN=/opt/oraadmin/network/admin

export ORACLE_SID=+ASM

export HOST=`hostname`

export LOG_FILE=/var/adm/log/StageArchives_${HOST}.log

export A_FILE_COPIED=FALSE

#

# Optionally turn Debug on to see what the script does

export Debug=false

#

# Functions

#

Log() {

echo "`date +%Y/%m/%d` (`date +%H:%M:%S`) : $1" | tee -a $LOG_FILE

} #End Log

#

#

Continue()

{

echo "Continue....? [n]\c"; read ans

case $ans in

y|Y) echo "\n\nContinuing...."

break;;

*) exit 0

break;;

esac

unset ans

}

#

#

GetArchiveList()

{

echo "set termout off heading off pages 9999 lines 70 feedback off" > /tmp/getArchives.$$.sql

echo "column name format A70;" >> /tmp/getArchives.$$.sql

echo "spool /tmp/archivelist.lst;" >> /tmp/getArchives.$$.sql

echo "select trim(name) from v\$archived_log where deleted='NO' and archived='YES';" >> /tmp/getArchives.$$.sql

echo "quit;" >> /tmp/getArchives.$$.sql

$ORACLE_HOME/bin/sqlplus -S ${WCSPRD_SYS}@WCSPRD as sysdba @/tmp/getArchives.$$.sql

if [ "$Debug" = "true" ]

then

cat /tmp/getArchives.$$.sql

Continue

fi

rm -f /tmp/getArchives.$$.sql

}

StoreArchivesInFS()

{

ArchName=$1

ArchShortName=`basename $1`

ArchDest=/opt/asmprd/ar01/${ArchShortName}

AvailFS=`df -gv /opt/asmprd/ar01 | grep -v Free| awk '{print $5}' | cut -d'%' -f1`

Log "Archiving $ArchDest"

if [ "$Debug" = "true" ]

then

echo "AvailFS = $AvailFS"

echo "Commando = $ASM_HOME/bin/asmcmd cp $ArchName $ArchDest"

Continue

fi

if [ $AvailFS -lt 90 ]

then

echo "$ASM_HOME/bin/asmcmd cp $ArchName $ArchDest"

$ASM_HOME/bin/asmcmd cp $ArchName $ArchDest >/dev/null 2>&1

if [ -f "$ArchDest" ]

then

export A_FILE_COPIED=TRUE

$ASM_HOME/bin/asmcmd rm $ArchName >/dev/null 2>&1

Log "$ArchName has been deleted from ASM."

else

echo "ArchDest=$ArchDest"

ls -l $ArchDest

fi

else

export FSFULL=TRUE

fi

}

RmanCrosscheck()

{

# Crosscheck and clean repository

#Log "Crosscheck and clean repository"

$ORACLE_HOME/bin/rman target ${WCSPRD_SYS}@WCSPRD rcvcat ${TSMORA_TSMCAT}@tsmora cmdfile StageArchives.cmd

d

}

# Main Program

PROG_PID=$$

RUNNING=`ps -ef| grep StageArchives.sh | grep -v ${PROG_PID} | grep -v grep | awk '{print $2}'`

if [ -n "$RUNNING" ]

then

Log "Old Running session(s) found with PID(s): $RUNNING "

ps -ef| grep ${RUNNING} | grep -v grep >> $LOG_FILE

for tpid in $RUNNING

{

kill $tpid

}

fi

GetArchiveList

cat /tmp/archivelist.lst | while read ARCHFILE

do

if [ ! "$ARCHFILE" = "" ]

then

StoreArchivesInFS $ARCHFILE

if [ "$FSFULL" = "TRUE" ]

then

#

# Insert some action here to clean up the destination file system

fi

fi

done

#

#

#

#

# Use Rman to update the recovery catalog

RmanCrosscheck

unset FSFULL ARCHFILE

#rm -f /tmp/archivelist.lst

The script contains a call to 'rman' using the command file 'StageArchives.cmd'

This file looks like:

allocate channel for maintenance type disk;

crosscheck archivelog all;

delete noprompt expired archivelog all;

quit;

Please note that I am not able to give any support to anyone who wants to use this script.

You are allowed to copy and use it, but for the remainder you're on your own.

Success!