Edit (07,2013): I recommend using FRA for automatic deletion of archivelogs on a physical standby database.
Let's see an example of configuring automatic maintenance of the archived logs on a standby database:
1. Enable the fast recovery area on the standby database by setting the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parameters:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=500G;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/data/FRA';If we're using ASM, we can specify a disk group as DB_RECOVERY_FILE_DEST.
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+FRA';2. Set the LOG_ARCHIVE_DEST_1 parameter as follows so that the archived logfiles will be created at the DB_RECOVERY_FILE_DEST parameter:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_ FILE_DEST';3. Set the RMAN archived log deletion policy as follows. With this setting, the applied archived logs will be automatically deleted when there is a space constraint in FRA, depending on DB_RECOVERY_FILE_DEST_SIZE. If the archived logs are not applied, they will not be deleted.
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
The shell script below, can be used to automate the deletion of applied archivelogs on unix standby servers. In this case archivelogs on the primary database are automatically deleted by rman after the backup operation. This shell script is being used in crontab of the standby machine to automate applied arhivelog deletion. It finds the last applied log using alertlog file, then deletes archivelogs smaller then the "last archivelog number - 10" in the archive directory.
#take the log number to be applied
LogNo=`tail -30000 /oracle/app/oracle/product/10.2.0/admin/orcl/bdump/alert* | grep "Media Recovery Log" | cut -d " " -f 4 | cut -d "_" -f 5 | tail -1 `
echo "Oracle applied LogNo is $LogNo"
echo "new backlog log No: $SecLogNo"
#delete small numbers from this in arch dir
for i in `ls *.arc`
Newi=`echo $i | cut -d "_" -f 4`
if [ $Newi -lt $SecLogNo ] ; then
echo "$i to be deleted..."
This script is special for my enviroment, it should be modified for any other environments. Archive log directory and name format effects the field numbers in the cut commands. If you need help for modifying the script for your env. please write me the "Media Recovery Log" lines in the alert log file.
The important think here is the idea.
(Thanks to my friend Selcuk Karaca who owns the idea and the script)