Yesterday I encountered a problem with a RMAN backup. There were no backups of archivelogs, only datafile backups and archivelogs. So I investigated the issue and found the following backup script:
backup database archivelog all delete input; delete noprompt obsolete;
Looks good at the first glance but what it does is not what I wanted it to do. The “redundancy” is configured to be 1. So what happens is this, first the database is being backued up and right after it the archivelogs are being backed up to. But only those archivelogs that existed at the time the backup command started. No archivelogs that were created during the database backup are being backed up. So the archivelog backups become obsolete immediately and are deleted by the following “delete obsolete” command.
The thing is, the script should look like this:
backup database plus archivelog delete input; delete noprompt obsolete;
Minor change with a major impact.
Let’s see what happens in both cases
Case 1
Check what sequences are in the online logs and generate some more:
[oracle@oel6u4 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 13 10:45:04 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt ions SYS@db12cb> select group#, sequence#, status from v$log; GROUP# SEQUENCE# STATUS ---------- ---------- --------------- 1 16 CURRENT 2 14 ACTIVE 3 15 ACTIVE SYS@db12cb> alter system switch logfile; System altered. SYS@db12cb> alter system switch logfile; System altered. SYS@db12cb> alter system switch logfile; System altered. SYS@db12cb> alter system checkpoint; System altered. SYS@db12cb> select group#, sequence#, status from v$log; GROUP# SEQUENCE# STATUS ---------- ---------- --------------- 1 19 CURRENT 2 17 INACTIVE 3 18 INACTIVE
At that point we have archivelogs until sequnce 18. Now we start the RMAN backup:
[oracle@oel6u4 ~]$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Wed Sep 13 10:48:07 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: DB12C (DBID=1357631873) RMAN> backup database archivelog all delete input; Starting backup at 13.09.2017 10:48:16 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=276 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00005 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_example_d3lq5h14_.dbf input datafile file number=00001 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_system_d3lq3d22_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_sysaux_d3lq4lpd_.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_marco_d3qvwcwt_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_undotbs1_d3lq5ctw_.dbf input datafile file number=00006 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_users_d3lq6w0q_.dbf channel ORA_DISK_1: starting piece 1 at 13.09.2017 10:48:17 channel ORA_DISK_1: finished piece 1 at 13.09.2017 10:51:02 piece handle=/u01/app/oracle/fra/DB12CB/backupset/2017_09_13/o1_mf_nnndf_TAG20170913T104816_dvkw6kls_.bkp tag=TAG20170913T104816 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:02:45 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=14 RECID=43 STAMP=954585900 input archived log thread=1 sequence=15 RECID=44 STAMP=954585901 input archived log thread=1 sequence=16 RECID=46 STAMP=954585995 input archived log thread=1 sequence=17 RECID=48 STAMP=954585996 input archived log thread=1 sequence=18 RECID=50 STAMP=954585998 channel ORA_DISK_1: starting piece 1 at 13.09.2017 10:51:02 channel ORA_DISK_1: finished piece 1 at 13.09.2017 10:51:05 piece handle=/u01/app/oracle/fra/DB12CB/backupset/2017_09_13/o1_mf_annnn_TAG20170913T104816_dvkwcpnp_.bkp tag=TAG20170913T104816 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 channel ORA_DISK_1: deleting archived log(s) archived log file name=/u01/app/oracle/fra/DB12CB/archivelog/2017_09_13/o1_mf_1_14_dvkw0cm1_.arc RECID=43 STAMP=954585900 archived log file name=/u01/app/oracle/fra/DB12CB/archivelog/2017_09_13/o1_mf_1_15_dvkw0f1w_.arc RECID=44 STAMP=954585901 archived log file name=/u01/app/oracle/fra/DB12CB/archivelog/2017_09_13/o1_mf_1_16_dvkw3c38_.arc RECID=46 STAMP=954585995 archived log file name=/u01/app/oracle/fra/DB12CB/archivelog/2017_09_13/o1_mf_1_17_dvkw3d1f_.arc RECID=48 STAMP=954585996 archived log file name=/u01/app/oracle/fra/DB12CB/archivelog/2017_09_13/o1_mf_1_18_dvkw3g00_.arc RECID=50 STAMP=954585998 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 13.09.2017 10:51:09 channel ORA_DISK_1: finished piece 1 at 13.09.2017 10:51:10 piece handle=/u01/app/oracle/fra/DB12CB/backupset/2017_09_13/o1_mf_ncsnf_TAG20170913T104816_dvkwcx24_.bkp tag=TAG20170913T104816 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 13.09.2017 10:51:10
We see, that all archivelogs until and including sequence 18 are being backed up. No sign of sequence 19 or any sequence beyond that.
Case 2
Again, we check the sequences in the online logs. There is no change to what we had ad the beginning of Case-1-Backup. That’s why I create some more archivelogs.
[oracle@oel6u4 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 13 12:36:06 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SYS@db12cb> select group#, sequence#, status from v$log; GROUP# SEQUENCE# STATUS ---------- ---------- --------------- 1 19 CURRENT 2 17 INACTIVE 3 18 INACTIVE SYS@db12cb> alter system switch logfile; System altered. SYS@db12cb> alter system switch logfile; System altered. SYS@db12cb> alter system checkpoint; System altered. SYS@db12cb> select group#, sequence#, status from v$log; GROUP# SEQUENCE# STATUS ---------- ---------- --------------- 1 19 INACTIVE 2 20 INACTIVE 3 21 CURRENT
Now we start the backup with the slightly modified command:
[oracle@oel6u4 ~]$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Wed Sep 13 12:36:41 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: DB12C (DBID=1357631873) RMAN> backup database plus archivelog delete input; Starting backup at 13.09.2017 12:36:49 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=264 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=19 RECID=52 STAMP=954592574 input archived log thread=1 sequence=20 RECID=54 STAMP=954592575 input archived log thread=1 sequence=21 RECID=56 STAMP=954592610 channel ORA_DISK_1: starting piece 1 at 13.09.2017 12:36:50 channel ORA_DISK_1: finished piece 1 at 13.09.2017 12:36:51 piece handle=/u01/app/oracle/fra/DB12CB/backupset/2017_09_13/o1_mf_annnn_TAG20170913T123650_dvl2l2o8_.bkp tag=TAG20170913T123650 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: deleting archived log(s) archived log file name=/u01/app/oracle/fra/DB12CB/archivelog/2017_09_13/o1_mf_1_19_dvl2jx41_.arc RECID=52 STAMP=954592574 archived log file name=/u01/app/oracle/fra/DB12CB/archivelog/2017_09_13/o1_mf_1_20_dvl2jzhj_.arc RECID=54 STAMP=954592575 archived log file name=/u01/app/oracle/fra/DB12CB/archivelog/2017_09_13/o1_mf_1_21_dvl2l23m_.arc RECID=56 STAMP=954592610 Finished backup at 13.09.2017 12:36:51 Starting backup at 13.09.2017 12:36:51 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=00005 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_example_d3lq5h14_.dbf input datafile file number=00001 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_system_d3lq3d22_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_sysaux_d3lq4lpd_.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_marco_d3qvwcwt_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_undotbs1_d3lq5ctw_.dbf input datafile file number=00006 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_users_d3lq6w0q_.dbf channel ORA_DISK_1: starting piece 1 at 13.09.2017 12:36:52 channel ORA_DISK_1: finished piece 1 at 13.09.2017 12:39:07 piece handle=/u01/app/oracle/fra/DB12CB/backupset/2017_09_13/o1_mf_nnndf_TAG20170913T123651_dvl2l435_.bkp tag=TAG20170913T123651 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:02:15 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 13.09.2017 12:39:08 channel ORA_DISK_1: finished piece 1 at 13.09.2017 12:39:09 piece handle=/u01/app/oracle/fra/DB12CB/backupset/2017_09_13/o1_mf_ncsnf_TAG20170913T123651_dvl2pd9n_.bkp tag=TAG20170913T123651 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 13.09.2017 12:39:09 Starting backup at 13.09.2017 12:39:09 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=22 RECID=58 STAMP=954592749 channel ORA_DISK_1: starting piece 1 at 13.09.2017 12:39:09 channel ORA_DISK_1: finished piece 1 at 13.09.2017 12:39:10 piece handle=/u01/app/oracle/fra/DB12CB/backupset/2017_09_13/o1_mf_annnn_TAG20170913T123909_dvl2pfxh_.bkp tag=TAG20170913T123909 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: deleting archived log(s) archived log file name=/u01/app/oracle/fra/DB12CB/archivelog/2017_09_13/o1_mf_1_22_dvl2pflz_.arc RECID=58 STAMP=954592749 Finished backup at 13.09.2017 12:39:10
Looks different now. We have a logswitch at the very beginning of the backup. Then all the archivelogs that exists at that point are being backed up. Then there is the database backup. And finally another logswitch is done and all the logs that were created during the database backup are being backed up too.
That is, what I really want.
Conclusion
The effect of the “plus archivelog” syntax is completely different from the combined “backup” command for different file types. So be very careful which option you use and most important: check your backup strategy by doing test restores on a regular basis.