Here's a 19c example with a Standby Database
I first set it to "APPLIED ON ALL STANDBY" meaning that an ArchiveLog can be deleted only if the Primary has confirmed that the ArchiveLog (i.e. all the Redo in that ArchiveLog) has been applied on every Standby database configured for this Primary.
I then attempt to use the DELETE command to delete all recent ArchiveLogs.
oracle19c>rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jul 31 11:45:40 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLCDB (DBID=2778483057)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCLCDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # 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 RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_ORCLCDB.f'; # default
RMAN> configure archivelog deletion policy to applied on all standby;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters are successfully stored
RMAN>
RMAN> sql 'alter system switch logfile';
sql statement: alter system switch logfile
RMAN> sql 'alter system switch logfile';
sql statement: alter system switch logfile
RMAN> list archivelog all completed after "sysdate-1/24";
List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
126 1 41 A 13-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_41_1036108814.dbf
128 1 42 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_42_1036108814.dbf
130 1 43 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_43_1036108814.dbf
131 1 44 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_44_1036108814.dbf
RMAN>
RMAN> delete archivelog all;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 device type=DISK
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_43_1036108814.dbf thread=1 sequence=43
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_44_1036108814.dbf thread=1 sequence=44
List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
118 1 37 A 12-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_37_1036108814.dbf
119 1 38 A 13-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_38_1036108814.dbf
120 1 39 A 13-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_39_1036108814.dbf
125 1 40 A 13-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_40_1036108814.dbf
126 1 41 A 13-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_41_1036108814.dbf
128 1 42 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_42_1036108814.dbf
Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_37_1036108814.dbf RECID=118 STAMP=1045695910
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_38_1036108814.dbf RECID=119 STAMP=1045696247
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_39_1036108814.dbf RECID=120 STAMP=1045696315
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_40_1036108814.dbf RECID=125 STAMP=1045696378
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_41_1036108814.dbf RECID=126 STAMP=1047209331
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_42_1036108814.dbf RECID=128 STAMP=1047209610
Deleted 6 objects
RMAN>
RMAN> list archivelog all completed after "sysdate-1/24";
List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
130 1 43 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_43_1036108814.dbf
131 1 44 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_44_1036108814.dbf
RMAN>
RMAN refused to delete ArchiveLogs 43 and 44 that were recently generated because they haven't been applied to the Standby database(s).
Once the Standby confirms that it has received and applied all the Redo in 43 and 44 :
2020-07-31T11:51:53.314269+08:00 PR00 (PID:3718): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_43_1036108814.dbf 2020-07-31T11:51:53.676981+08:00 PR00 (PID:3718): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_44_1036108814.dbf PR00 (PID:3718): Media Recovery Waiting for T-1.S-45 (in transit) 2020-07-31T11:51:53.868134+08:00
The Primary can now Delete these ArchiveLogs (even though they haven't been backed up).
RMAN> delete archivelog all completed after "sysdate-1/24";
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
130 1 43 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_43_1036108814.dbf
131 1 44 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_44_1036108814.dbf
Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_43_1036108814.dbf RECID=130 STAMP=1047210443
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_44_1036108814.dbf RECID=131 STAMP=1047210452
Deleted 2 objects
RMAN> list archivelog all;
specification does not match any archived log in the repository
RMAN>
If you want to prevent RMAN from Deleting ArchiveLogs that have not been backed up, you can either add the "backed up 'n' times to disk' clause to the DELETE command :
RMAN> sql 'alter system switch logfile';
sql statement: alter system switch logfile
RMAN> sql 'alter system switch logfile';
sql statement: alter system switch logfile
RMAN> list archivelog all;
List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
134 1 45 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_45_1036108814.dbf
136 1 46 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_46_1036108814.dbf
RMAN> delete archivelog all completed after "sysdate-1/24" backed up 1 times to disk;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 device type=DISK
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_45_1036108814.dbf thread=1 sequence=45
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_46_1036108814.dbf thread=1 sequence=46
RMAN>
OR use a combination Archive Log Deletion Policy :
RMAN> configure archivelog deletion policy to applied on all standby backed up 1 times to disk; old RMAN configuration parameters: CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; new RMAN configuration parameters: CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK; new RMAN configuration parameters are successfully stored RMAN> RMAN> sql 'alter system switch logfile'; sql statement: alter system switch logfile RMAN> sql 'alter system switch logfile'; sql statement: alter system switch logfile RMAN> delete archivelog all completed after "sysdate-1/24"; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=275 device type=DISK RMAN-08138: warning: archived log not deleted - must create more backups archived log file name=/opt/oracle/archivelog/ORCLCDB/1_45_1036108814.dbf thread=1 sequence=45 RMAN-08138: warning: archived log not deleted - must create more backups archived log file name=/opt/oracle/archivelog/ORCLCDB/1_46_1036108814.dbf thread=1 sequence=46 RMAN-08120: warning: archived log not deleted, not yet applied by standby archived log file name=/opt/oracle/archivelog/ORCLCDB/1_47_1036108814.dbf thread=1 sequence=47 RMAN-08120: warning: archived log not deleted, not yet applied by standby archived log file name=/opt/oracle/archivelog/ORCLCDB/1_48_1036108814.dbf thread=1 sequence=48 RMAN>
After I have confirmed that the Standby has applied the recent ArchiveLogs
PR00 (PID:4569): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_47_1036108814.dbf 2020-07-31T12:04:40.251269+08:00 PR00 (PID:4569): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_48_1036108814.dbf PR00 (PID:4569): Media Recovery Waiting for T-1.S-49 (in transit) 2020-07-31T12:04:40.609327+08:00 Recovery of Online Redo Log: Thread 1 Group 4 Seq 49 Reading mem 0 Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
I try the DELETE again on the Primary
RMAN> delete archivelog all completed after "sysdate-1/24";
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 device type=DISK
RMAN-08138: warning: archived log not deleted - must create more backups
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_45_1036108814.dbf thread=1 sequence=45
RMAN-08138: warning: archived log not deleted - must create more backups
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_46_1036108814.dbf thread=1 sequence=46
RMAN-08138: warning: archived log not deleted - must create more backups
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_47_1036108814.dbf thread=1 sequence=47
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_48_1036108814.dbf thread=1 sequence=48
RMAN>
RMAN> backup as compressed backupset archivelog all;
Starting backup at 31-JUL-20
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=45 RECID=134 STAMP=1047210945
input archived log thread=1 sequence=46 RECID=136 STAMP=1047210952
input archived log thread=1 sequence=47 RECID=138 STAMP=1047211344
input archived log thread=1 sequence=48 RECID=139 STAMP=1047211353
input archived log thread=1 sequence=49 RECID=142 STAMP=1047211650
channel ORA_DISK_1: starting piece 1 at 31-JUL-20
channel ORA_DISK_1: finished piece 1 at 31-JUL-20
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_07_31/o1_mf_annnn_TAG20200731T120730_hl7682or_.bkp tag=TAG20200731T120730 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 31-JUL-20
Starting Control File and SPFILE Autobackup at 31-JUL-20
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2020_07_31/o1_mf_s_1047211651_hl768490_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 31-JUL-20
RMAN> delete archivelog all completed after "sysdate-1/24";
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 device type=DISK
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_48_1036108814.dbf thread=1 sequence=48
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_49_1036108814.dbf thread=1 sequence=49
List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
134 1 45 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_45_1036108814.dbf
136 1 46 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_46_1036108814.dbf
138 1 47 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_47_1036108814.dbf
Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_45_1036108814.dbf RECID=134 STAMP=1047210945
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_46_1036108814.dbf RECID=136 STAMP=1047210952
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_47_1036108814.dbf RECID=138 STAMP=1047211344
Deleted 3 objects
RMAN>
At first, I cannot delete any of the ArchiveLogs. Then, after I backup 45 to 49, I am able to delete 45 to 47 but not 48 and 49 as they have not yet been applied to the Standby (although they have been backed up locally on the Primary server)
Thus the Archive Log Deletion Policy protects from accidental deletion with the DELETE ARCHIVELOG command.
HOWEVER, the DELETE OBSOLETE command ignores this Policy.
Notes :
1. Archive Log Deletion Policy has been available since 10g. However, if you are on 10g or 11g, see Oracle Support Document ID 728053.1
2. Instead of the "APPLIED ON [ALL] STANDBY" you can also use "SHIPPED TO [ALL] STANDBY" subclause. This is useful if you have Standby databases that are [deliberately] lagging the Primary -- .e.g a Standby that does the Apply 4 hours after the Redo is generated on the Primary.
2 comments:
wonderful narrative
Thanks Hemant.
I use one of your advice
delete archivelog all completed before "SYSDATE - 0.1" backed up 1 times to disk;
after
backup incremental level 1 database plus archivelog;
done on physical standby
Post a Comment