Retention Policy

[dbrs4me]

I am new to Oracle administration and have an issue with our current backup. From what I see, our retention policy is set to 7 days. The backup runs and as part of the backup it pulls all the small archivelogs into fewer larger archivelogs. Given the retention policy, within a week I should be able to delete the old archivelogs that should be showup as expired. What is strange is that their status appears to be stuck at Available. Does anyone have an idea as to why that might be? The rest of the backup gets deleted accordingly, the incrementat as well as the full backup, but the log files stay put requiring me to connect to the system and manually delete about once a month. Any insight into this would be helpful. Thanks

 

[karluk]

Could you clarify what is happening? Is it the backed up archive logs that aren't getting deleted or the originals in the archive log directory? The backed up archive logs should be deleted when no longer needed based on your retention period, but I don't think the original archive logs are handled the same way.

Take a look at the script I used to do my level 0 rman backups. The first command backs up the database. The second command backs up all archive logs that haven't previously been backed up, deleting the originals from the archive log directory. Finally, I run a "delete obsolete" command, which deletes all the database and archive log backups that are no longer needed.

backup incremental level 0 device type disk tag '%TAG' database;
backup device type disk tag '%TAG' archivelog all not backed up delete all input;
allocate channel for maintenance type disk;
delete noprompt obsolete device type disk;
release channel;

Please compare this with your rman backup script. If you haven't included the "delete all input" clause in your archive log backup statement, it wouldn't be surprising that you need to go in periodically and clean up your archive log directory. You can do this with an rman command similar to the following.

delete archivelog all backed up 1 times to device type disk;

But you should not simply go into your archive log directory and start deleting files. That would cause a conflict between what rman thinks is available for recovery and what is actually present on your database server.

 

[dbrs4me]

What I am trying to delete is the backed up archive logs, not the originals. The originals are being deleted just fine.

As part of the backup the originals are combined into 4GB chunks. During the backup there is a crosscheck that is running and all of the 4GB archive logs always comeback as Available. The database backup files are being deleted in a timely fashion, but not the archive. I have to delete them manually or we will run out of disk space.

This only answers your first paragraph. I am going to go through the rest carefully and answer any other questions from there. Thank you for the input so far!

 

[dbrs4me]

Here is the code we use for creating the backupset. I believe the delete archivelog command is actually just deleting all the small ones older than 7 days, that part seems to work

backup as compressed backupset device type disk
   tag '$AL_TAG' archivelog from time "${FROMTIME}" ;
 #
 backup as compressed backupset device type disk
   tag '$AL_TAG' archivelog until time "(sysdate - 7)" ;
 delete archivelog until time "(sysdate - 7)" backed up 1 times to device type disk ;
 #
 list backup completed after "(sysdate - 1.5)" ;

 

[karluk]

Are you absolutely certain that you are doing regular level 0 backups? It almost looks as if rman is retaining old archive log backups because it thinks it needs them to forward recover from a very old level 0.

I suggest you log into rman and identify the newest level 0 backup that is more than seven days old. Rman has to retain that level 0 and the associated logs to forward recover. That's the way it guarantees the ability to recover anytime within that last seven days. The entry for the level 0 will look something like the following, with a "0" in the "LV" column.

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
...
5030947 B  0  A DISK        22-MAY-10       1       1       NO         LEVEL0_BACKUP_OR10_052210080004
5030948 B  0  A DISK        22-MAY-10       1       1       NO         LEVEL0_BACKUP_OR10_052210080004
...

Also, please provide the rman script that you use for your level 0 backups.

 

[dbrs4me]

I ran the command in RMAN that you recommended. I got quite a few lines returned so here is the pertinent stuff:

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
6824    B  0  A DISK        29-MAY-2010     2       1       YES        FULL_100529
6825    B  0  A DISK        29-MAY-2010     1       1       YES        FULL_100529
6826    B  0  A DISK        29-MAY-2010     1       1       YES        FULL_100529

 

[dbrs4me]

Here is our script:

proddb1 [NOTSET]> more /oracle/bin/rman_bkup2fbra.ksh
#!/bin/ksh
# File:    rman_bkup2fbra.ksh
# Purpose: Daily backup to Flashback Recovery Area
# Created: 2007-04-13 RnB - John Bening

P_SID=${1:-NOTSET}          # SID to backup
P_MODE=${2:-NOTSET}         # backup type: FULL | INC_D | INC_C
P_RETENTION=${3:-NOTSET}    # recovery window in days

PROG=rman_bkup2fbra

if [[ "$P_SID" = NOTSET ]]; then
  echo "\nUsage:"
  echo "    rman_bkup2fbra.ksh <SID> <FULL|INC_D|INC_C> <RETENTION_DAYS>"
  echo ""
  exit 1
fi

. /oracle/bin/oradbenv $P_SID

DB_TAG=${P_MODE}_`date +%y%m%d`
AL_TAG=ARC_`date +%y%m%d`
LC_SID=`echo $ORACLE_SID | tr "[:upper:]" "[:lower:]"`     # lowercase SID

SID_ORABACK=`/oracle/bin/get_fbra_uniq.ksh`
#SID_ORABACK=/${LC_SID}/orafbra/${P_SID}
#SID_ORABACK=/u01/oraback/${ORACLE_SID}
mkdir -p $SID_ORABACK/etc >/dev/null 2>&1

FROMTIME="to_date('`date +%Y%m%d_%H%M%S`','yyyymmdd_hh24miss')"

echo "#"
echo "P_SID=$P_SID"
echo "P_MODE=$P_MODE"
echo "P_RETENTION=$P_RETENTION"
echo "DB_TAG=$DB_TAG"
echo "AL_TAB=$AL_TAG"
echo "FROMTIME=$FROMTIME"
echo "#"

#   Switch to a temp working directory
TMP_DIR=/var/tmp/$PROG.$$
mkdir $TMP_DIR
cd    $TMP_DIR

cp -p $ORACLE_HOME/dbs/orapw${ORACLE_SID}    $SID_ORABACK/etc
cp -p $ORACLE_HOME/dbs/init${ORACLE_SID}.ora $SID_ORABACK/etc

cat >sql1.sql <<EOF
   connect / as sysdba

 alter database backup controlfile to trace as '$SID_ORABACK/etc/control.trc' re
use resetlogs;
 create pfile='$SID_ORABACK/etc/spfile${ORACLE_SID}.txt' from spfile ;

   column name format A25
   column value format A50
   column parameter format A25
   set pagesize 10000
   set echo on
   REM
   prompt hostname    = `hostname`
   prompt date        = `date`
   prompt ORACLE_HOME = $ORACLE_HOME
   prompt ORACLE_SID  = $ORACLE_SID
   show sga
   select * from v\$version ;
   select * from v\$instance ;
   select * from v\$database ;
   select * from dba_tablespaces order by 1 ;
   select * from dba_data_files order by 1 ;
   select * from dba_temp_files order by 1 ;
   select * from v\$logfile order by member ;
   select * from v\$log order by 1 ;
   select * from v\$nls_parameters order by 1 ;
   select name, value from V\$PARAMETER where isdefault = 'FALSE' order by 1 ;
   select name, value from V\$PARAMETER order by 1 ;
   exit
EOF

sqlplus /nolog @sql1.sql >$SID_ORABACK/etc/misc1.txt


cat >rman1.rcv <<EOF
 # File: rman1.rcv
 #
 set echo on
 #
 # RMAN persistent settings
 configure controlfile autobackup off ;
 configure retention policy to recovery window of $P_RETENTION days ;
 configure device type disk backup type to compressed backupset ;
 configure channel device type disk maxpiecesize 4000 M;
 configure snapshot controlfile name to '$SID_ORABACK/etc/control.bak';
 #
 show all ;
 #
 allocate channel for maintenance type disk ;
 crosscheck backup ;
 crosscheck copy ;
 delete noprompt obsolete ;
 delete noprompt expired backup ;
 delete noprompt expired copy ;
 release channel ;
 #
 sql 'alter system archive log current' ;
EOF

case $P_MODE in
  FULL)  echo "backup incremental level 0 cumulative"  >>rman1.rcv  ;;
  INC_D) echo "backup incremental level 1"             >>rman1.rcv  ;;
  INC_C) echo "backup incremental level 1 cumulative"  >>rman1.rcv  ;;
esac

cat >>rman1.rcv <<EOF
 as compressed backupset device type disk
 tag '$DB_TAG' database spfile current controlfile for standby ;
 #tag '$DB_TAG' tablespace tools ;
 #
 allocate channel for maintenance type disk ;
 delete noprompt obsolete device type disk ;
 release channel ;
 #
 sql 'alter system archive log current' ;
 #
 # The End
EOF

cp rman1.rcv $SID_ORABACK/etc/rman1.rcv

echo "##########################################################"
echo "#  rman1.rcv"
echo "##########################################################"
cat rman1.rcv
echo "##########################################################"
echo "#"

echo "+rman target=/ nocatalog @rman1.rcv"
rman target=/ nocatalog @rman1.rcv
RMAN_EC=$?

if [[ $RMAN_EC = 0 ]] ; then
  echo "# RMAN exit code was 0"
else
  echo "*ERROR: non-zero RMAN exit code: $RMAN_EC"
fi


cat >rman2.rcv <<EOF
 # File: rman2.rcv
 #
 # set echo on
 #
 sql 'alter system archive log current' ;
 #
 # configure controlfile autobackup on ;
 # show all
 #
 backup as compressed backupset device type disk
   tag '$AL_TAG' archivelog from time "${FROMTIME}" ;
 #
 backup as compressed backupset device type disk
   tag '$AL_TAG' archivelog until time "(sysdate - 7)" ;
 delete archivelog until time "(sysdate - 7)" backed up 1 times to device type disk ;
 #
 list backup completed after "(sysdate - 1.5)" ;
 #
 # backup as compressed backupset current controlfile for standby tag 'STANDBY' ;
 #
 # The End
EOF

cp rman2.rcv $SID_ORABACK/etc/rman2.rcv


echo "##########################################################"
echo "#  rman2.rcv"
echo "##########################################################"
cat rman2.rcv
echo "##########################################################"
echo "#"

export NLS_DATE_FORMAT=YYYYMMDD_HH24MISS
echo "+rman target=/ nocatalog @rman2.rcv"
rman target=/ nocatalog @rman2.rcv
RMAN_EC=$?

if [[ $RMAN_EC = 0 ]] ; then
  echo "# RMAN exit code was 0"
else
  echo "*ERROR: non-zero RMAN exit code: $RMAN_EC"
fi


#  cleanup
cd /var/tmp
rm -rf $TMP_DIR >/dev/null 2>&1

# The End