Oracle archivelog retention policy


Jephe Wu - http://linuxtechres.blogspot.com

 Objective: understanding Oracle archivelog retention policy
Environment: Oracle Linux 6.3 64bit, Oracle database 11gR2 Enterprise Edition.


 case 1: use flush recovery area for archivelog and archivelog deletion

[oracle@oratest archivelog]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 22 08:43:55 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     206
Next log sequence to archive   208
Current log sequence       208

 SQL> alter system archive log current;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@oratest archivelog]$ find /home/oracle/app/oracle/flash_recovery_area/CMDB/archivelog/ -type f
/home/oracle/app/oracle/flash_recovery_area/CMDB/archivelog/2012_11_22/o1_mf_1_208_8btltmgl_.arc
[oracle@oratest archivelog]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Nov 22 08:45:10 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CMDB (DBID=1120341162)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name CMDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_cmdb.f'; # default

Note: before rman backup, try to delete archivelog all, failed, as it will check deletion policy, need to be backed up once.

RMAN> delete archivelog all;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/home/oracle/app/oracle/flash_recovery_area/CMDB/archivelog/2012_11_22/o1_mf_1_208_8btltmgl_.arc thread=1 sequence=208

RMAN> delete noprompt archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/home/oracle/app/oracle/flash_recovery_area/CMDB/archivelog/2012_11_22/o1_mf_1_208_8btltmgl_.arc thread=1 sequence=208

 RMAN> delete noprompt force archivelog all;  (force will override deletion policy checking)

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
List of Archived Log Copies for database with db_unique_name CMDB
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
2       1    208     A 22-NOV-12
        Name: /home/oracle/app/oracle/flash_recovery_area/CMDB/archivelog/2012_11_22/o1_mf_1_208_8btltmgl_.arc

deleted archived log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/CMDB/archivelog/2012_11_22/o1_mf_1_208_8btltmgl_.arc RECID=2 STAMP=800009091
Deleted 1 objects


RMAN>


 case 2: use user-specified area for archivelog instead of flush recovery area

SQL> alter system set log_archive_dest_1='location=/home/oracle/archivelog';

System altered.

SQL> archive log list;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       /home/oracle/archivelog
Oldest online log sequence     207
Next log sequence to archive   209
Current log sequence       209

 SQL> alter system archive log current;

System altered.

SQL> !ls -l /home/oracle/archivelog
total 156
-rw-r----- 1 oracle oinstall 153600 Nov 22 08:51 1_209_796487725.dbf

RMAN> show archivelog deletion policy;

RMAN configuration parameters for database with db_unique_name CMDB are:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;

RMAN> delete archivelog all;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/home/oracle/archivelog/1_209_796487725.dbf thread=1 sequence=209

RMAN> delete archivelog sequence 209;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/home/oracle/archivelog/1_209_796487725.dbf thread=1 sequence=209

RMAN> delete archivelog all;        

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/home/oracle/archivelog/1_209_796487725.dbf thread=1 sequence=209

RMAN> delete force archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
List of Archived Log Copies for database with db_unique_name CMDB

=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
3       1    209     A 22-NOV-12
        Name: /home/oracle/archivelog/1_209_796487725.dbf


Do you really want to delete the above objects (enter YES or NO)? y
deleted archived log
archived log file name=/home/oracle/archivelog/1_209_796487725.dbf RECID=3 STAMP=800009468
Deleted 1 objects

Summary: no matter archivelog sitting in flash recovery area or other location, it will always check archive log deletion policy, but force option override it.


case 3: restore archivelog from rman backup
========================
3.1 use rman to backup database plus archivelog, it will first archive log current, then at the end of backup, it will archive log current again, 
so it will generate logfile sequence 211 and 212, before rman, there was only 210.


[oracle@oratest archivelog]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 22 08:54:39 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 SQL> alter system archive log current;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@oratest archivelog]$ find .
.
./1_210_796487725.dbf

[oracle@oratest archivelog]$ ls
1_210_796487725.dbf

[oracle@oratest archivelog]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Nov 22 08:55:08 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CMDB (DBID=1120341162)

RMAN> backup database plus archivelog;


Starting backup at 22-NOV-12
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=210 RECID=4 STAMP=800009690
input archived log thread=1 sequence=211 RECID=5 STAMP=800009718
channel ORA_DISK_1: starting piece 1 at 22-NOV-12
channel ORA_DISK_1: finished piece 1 at 22-NOV-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/CMDB/backupset/2012_11_22/o1_mf_annnn_TAG20121122T085519_8btmg7dq_.bkp tag=TAG20121122T085519 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-NOV-12

Starting backup at 22-NOV-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/cmdb/sysaux01.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/cmdb/system01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/cmdb/undotbs01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/cmdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 22-NOV-12
channel ORA_DISK_1: finished piece 1 at 22-NOV-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/CMDB/backupset/2012_11_22/o1_mf_nnndf_TAG20121122T085520_8btmg92p_.bkp tag=TAG20121122T085520 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 22-NOV-12
channel ORA_DISK_1: finished piece 1 at 22-NOV-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/CMDB/backupset/2012_11_22/o1_mf_ncsnf_TAG20121122T085520_8btmjyt0_.bkp tag=TAG20121122T085520 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-NOV-12

Starting backup at 22-NOV-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=212 RECID=6 STAMP=800009808
channel ORA_DISK_1: starting piece 1 at 22-NOV-12
channel ORA_DISK_1: finished piece 1 at 22-NOV-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/CMDB/backupset/2012_11_22/o1_mf_annnn_TAG20121122T085648_8btmk08t_.bkp tag=TAG20121122T085648 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-NOV-12

 3.1.1 how does rman backup database works?

a. when issuing 'backup database plus archivelog', it will firstly run 'alter system archive log curent' to generate one additional archivelog first.
in this case, it generated log seq no 213.

Thu Nov 22 08:56:47 2012
ALTER SYSTEM ARCHIVE LOG
Thu Nov 22 08:56:48 2012
Thread 1 cannot allocate new log, sequence 213
Private strand flush not complete
  Current log# 2 seq# 212 mem# 0: /home/oracle/app/oracle/oradata/cmdb/redo02.log
Thread 1 advanced to log sequence 213 (LGWR switch)
  Current log# 3 seq# 213 mem# 0: /home/oracle/app/oracle/oradata/cmdb/redo03.log
Archived Log entry 6 added for thread 1 sequence 212 ID 0x42c754aa dest 1:
Thu Nov 22 09:31:00 2012
ALTER SYSTEM ARCHIVE LOG
Thu Nov 22 09:31:00 2012
Thread 1 cannot allocate new log, sequence 214
Private strand flush not complete
  Current log# 3 seq# 213 mem# 0: /home/oracle/app/oracle/oradata/cmdb/redo03.log
Thread 1 advanced to log sequence 214 (LGWR switch)
  Current log# 1 seq# 214 mem# 0: /home/oracle/app/oracle/oradata/cmdb/redo01.log
Archived Log entry 10 added for thread 1 sequence 213 ID 0x42c754aa dest 1:

---------
after done rman backup, it run 'alter systme archive log current' again to generate seq 214.
------------

Thu Nov 22 09:32:29 2012
ALTER SYSTEM ARCHIVE LOG
Thu Nov 22 09:32:29 2012
Thread 1 cannot allocate new log, sequence 215
Private strand flush not complete
  Current log# 1 seq# 214 mem# 0: /home/oracle/app/oracle/oradata/cmdb/redo01.log
Thread 1 advanced to log sequence 215 (LGWR switch)
  Current log# 2 seq# 215 mem# 0: /home/oracle/app/oracle/oradata/cmdb/redo02.log
Archived Log entry 11 added for thread 1 sequence 214 ID 0x42c754aa dest 1:


[oracle@oratest archivelog]$ ls -lart
total 5132
drwx------ 30 oracle oinstall    4096 Nov 22 08:35 ..
-rw-r-----  1 oracle oinstall 5131264 Nov 22 09:31 1_213_796487725.dbf
-rw-r-----  1 oracle oinstall   90624 Nov 22 09:32 1_214_796487725.dbf
drwxr-xr-x  2 oracle oinstall    4096 Nov 22 09:32 .



 When comes to restore, seq 213 is not required.
===========================

RMAN> restore database preview;

Starting restore at 22-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK



List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    1.27G      DISK        00:01:22     22-NOV-12    
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20121122T093102
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/CMDB/backupset/2012_11_22/o1_mf_nnndf_TAG20121122T093102_8btok67l_.bkp
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 6476692    22-NOV-12 /home/oracle/app/oracle/oradata/cmdb/system01.dbf
  2       Full 6476692    22-NOV-12 /home/oracle/app/oracle/oradata/cmdb/sysaux01.dbf
  3       Full 6476692    22-NOV-12 /home/oracle/app/oracle/oradata/cmdb/undotbs01.dbf
  4       Full 6476692    22-NOV-12 /home/oracle/app/oracle/oradata/cmdb/users01.dbf
using channel ORA_DISK_1

List of Archived Log Copies for database with db_unique_name CMDB
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
11      1    214     A 22-NOV-12
        Name: /home/oracle/archivelog/1_214_796487725.dbf

12      1    215     A 22-NOV-12
        Name: /home/oracle/archivelog/1_215_796487725.dbf

Media recovery start SCN is 6476683
Recovery must be done beyond SCN 6476692 to clear datafile fuzziness
Finished restore at 22-NOV-12


3.2 restore archivelog from rman backup
==============================================

 RMAN> run {
2> set archivelog destination to '/tmp';
3> restore archivelog from logseq=210 until logseq=212; 
4> }

executing command: SET ARCHIVELOG DESTINATION
using target database control file instead of recovery catalog

Starting restore at 22-NOV-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK

archived log for thread 1 with sequence 210 is already on disk as file /home/oracle/archivelog/1_210_796487725.dbf
archived log for thread 1 with sequence 211 is already on disk as file /home/oracle/archivelog/1_211_796487725.dbf
archived log for thread 1 with sequence 212 is already on disk as file /home/oracle/archivelog/1_212_796487725.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 22-NOV-12


Recovery Manager complete.
[oracle@oratest archivelog]$ ls
1_210_796487725.dbf  1_211_796487725.dbf  1_212_796487725.dbf
[oracle@oratest archivelog]$ rm -f *
[oracle@oratest archivelog]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Nov 22 09:00:30 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CMDB (DBID=1120341162)

RMAN> run { set archivelog destination to '/tmp'; restore archivelog from logseq=210 until logseq=212; }

executing command: SET ARCHIVELOG DESTINATION
using target database control file instead of recovery catalog

Starting restore at 22-NOV-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK

channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/tmp
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=210
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=211
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/CMDB/backupset/2012_11_22/o1_mf_annnn_TAG20121122T085519_8btmg7dq_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/CMDB/backupset/2012_11_22/o1_mf_annnn_TAG20121122T085519_8btmg7dq_.bkp tag=TAG20121122T085519
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/tmp
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=212
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/CMDB/backupset/2012_11_22/o1_mf_annnn_TAG20121122T085648_8btmk08t_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/CMDB/backupset/2012_11_22/o1_mf_annnn_TAG20121122T085648_8btmk08t_.bkp tag=TAG20121122T085648
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 22-NOV-12


[oracle@oratest tmp]$ ls -l /tmp/*.dbf
-rw-r----- 1 oracle oinstall 1017856 Nov 22 09:00 /tmp/1_210_796487725.dbf
-rw-r----- 1 oracle oinstall   10752 Nov 22 09:00 /tmp/1_211_796487725.dbf
-rw-r----- 1 oracle oinstall   45056 Nov 22 09:00 /tmp/1_212_796487725.dbf


3.3 preview restore database, it requires archivelog 212.
RMAN> restore database preview;

Starting restore at 22-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    1.27G      DISK        00:01:22     22-NOV-12    
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20121122T085520
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/CMDB/backupset/2012_11_22/o1_mf_nnndf_TAG20121122T085520_8btmg92p_.bkp
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 6472970    22-NOV-12 /home/oracle/app/oracle/oradata/cmdb/system01.dbf
  2       Full 6472970    22-NOV-12 /home/oracle/app/oracle/oradata/cmdb/sysaux01.dbf
  3       Full 6472970    22-NOV-12 /home/oracle/app/oracle/oradata/cmdb/undotbs01.dbf
  4       Full 6472970    22-NOV-12 /home/oracle/app/oracle/oradata/cmdb/users01.dbf
using channel ORA_DISK_1

List of Archived Log Copies for database with db_unique_name CMDB
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
9       1    212     A 22-NOV-12
        Name: /tmp/1_212_796487725.dbf

Media recovery start SCN is 6472970
Recovery must be done beyond SCN 6472970 to clear datafile fuzziness
Finished restore at 22-NOV-12


References:

1. http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmconfb.htm

 Commands
1.  list archivelog that backed up 2 times.
list archivelog all backed up 2 times to device type disk;

at 11/22/2012 10:12:00 PM

Labels: , ,