RMAN on Multitenant DB – Awareness of the Backup Optimization Behavior

Recovery Manager (RMAN) is one of the most popular Oracle databases components with unique Backup/Recovery features. It is fully integrated with the Multitenant Architecture allowing to implement Manage Many-Databases-as-One strategy.

RMAN permits to customize and save several database parameters used during the backup and recovery operations. Such parameters define for example the backup retention policy, the default device type,  how many archivelog copy should be stored, if the backup-sets should be compressed and/or encrypted and so on…

 

Below an example of RMAN setup with the highlight of the parameter CONFIGURE BACKUP OPTIMIZATION ON discussed on the next sections.

RMAN> show all;

RMAN configuration parameters for database with db_unique_name CEFUPRD are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 8 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/BACKUP/Databases/CEFUPRD/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/BACKUP/Databases/CEFUPRD/%d_%T_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE RMAN OUTPUT TO KEEP FOR 10 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/fast_recovery_area/rcocefuprd/cefuprd/snapcf_cefuprd.f';

RMAN>

 

 

Effects of RMAN Backup Optimization ON/OFF

In a Multitenant environment is more important than ever to understand the effects of the parameter CONFIGURE BACKUP OPTIMIZATION which can be set to ON or OFF.

Behavion when set ON

If RMAN determines that a file is identical and it has been backed up, then it is a candidate to be skipped. RMAN must do further checking to determine whether to skip the file, however, because both the retention policy and the backup duplexing feature are factors in the algorithm that determines whether RMAN has sufficient backups on the specified device type. (Definition from Oracle Backup Recovery User’s Guide).

Behavion when set OFF

The RMAN backup always includes all files no matter if they are identical and already backed up within the backup retention window.

 

 

What happens by migrating from Non-CDB to PDB?

Assuming that we have just migrated a non-CDB database to PDB and our pluggable database has 4 tablespaces all open read/write.  The container uses the same RMAN setup included on the top of this page, with CONFIGURE BACKUP OPTIMIZATION ON.

Dispite having a FULL database backup every night, only 1 backup every 8 days will be complete and consistent, because the RMAN backup optimization algorithm will detect the SEED PDB datafiles unchanged and it will skip those files. Therefore if we restore the CDB using the backup-sets generated by one FULL database backup, with no access to the rest of backup-sets inside the retention window, there are great probabilities that the restore will fail.

 

Extract of the CDB backup log which shows that the PDB$SEED datafiles have been skipped because already backed up 1 time during the last 8 days.

RMAN> BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL = 0 DATABASE PLUS ARCHIVELOG NOT BACKED UP 1 TIMES; 

Starting backup at May 15 2018 00:35:07
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 instance=clgbprd1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=168 instance=clgbprd1 device type=DISK
skipping archived logs of thread 1 from sequence 39516 to 39931; already backed up
skipping archived logs of thread 2 from sequence 34457 to 34749; already backed up
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=2 sequence=34774 RECID=148645 STAMP=976088413
input archived log thread=2 sequence=34775 RECID=148649 STAMP=976088467
input archived log thread=1 sequence=39944 RECID=148655 STAMP=976088552
input archived log thread=2 sequence=34776 RECID=148651 STAMP=976088509
input archived log thread=2 sequence=34777 RECID=148653 STAMP=976088551
input archived log thread=2 sequence=34778 RECID=148657 STAMP=976088700
input archived log thread=1 sequence=39945 RECID=148662 STAMP=976088937
input archived log thread=2 sequence=34779 RECID=148659 STAMP=976088838

...
Starting backup at May 15 2018 00:50:02
using channel ORA_DISK_1
using channel ORA_DISK_2
skipping datafile 2; already backed up 1 time(s)
skipping datafile 4; already backed up 1 time(s)
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
...

 

Using only the backup-sets above to restore the CDB means that Oracle has to recreate the two skipped datafiles (number 2 and 4) applying the archived logs generated during the initial CDB provisioning.

To note that the full backup starts including archived log from the following sequence:

  • For the Thread 1 – Sequence  39944
  • For the Thread 2 – Sequence  34774

But when Oracle initiates the Media Recovery, it complains because the archived log Thread 1 – Sequence 1 is unavailable:

RMAN> run {
allocate auxiliary channel dsk1 type disk ;
2> allocate auxiliary channel dsk2 type disk ;
allocate auxiliary channel dsk3 type disk ;
allocate auxiliary channel dsk4 type disk ;
3> allocate auxiliary channel dsk5 type disk ;
4> allocate auxiliary channel dsk6 type disk ;
5> duplicate database to 'CEFUAUX' noopen backup location '/BACKUP/Databases/CEFUPRD/backup_20180515_only' nofilenamecheck;
}6> 7> 8> 9>

allocated channel: dsk1
channel dsk1: SID=322 device type=DISK

allocated channel: dsk2
channel dsk2: SID=471 device type=DISK

allocated channel: dsk3
channel dsk3: SID=9 device type=DISK

allocated channel: dsk4
channel dsk4: SID=166 device type=DISK

allocated channel: dsk5
channel dsk5: SID=323 device type=DISK

allocated channel: dsk6
channel dsk6: SID=478 device type=DISK

Starting Duplicate Db at May 15 2018 09:29:15

....

contents of Memory Script:
{
 set until scn 2372623043;
 recover
 clone database
 delete archivelog
 ;
}
executing Memory Script

executing command: SET until clause

Starting recover at May 15 2018 11:24:39

starting media recovery

unable to find archived log
archived log thread=1 sequence=1
Oracle instance started

 

I hope this example helped to understand that while migrating from non-CDB to Multitenant, many Administration tasks should be carefully reviewed due to major architecture changes.

 


 

Advertisements

RMAN Backup Recovery Test

#######################################################
##                RMAN Setup, Backup and Recovery Test                     ##
#######################################################

–Basic tips for Disk & Tape Backup/Recovery strategy.

--To be able to efficiently perform incremental backup check if "Change Block Tracking" is enable:
 SELECT * FROM V$BLOCK_CHANGE_TRACKING;

–If it doesn’t exist create it:
alter database enable block change tracking using file ‘+FRA1’;

###########################
# RMAN Format Description
###########################

%a Current database activation id
%A Zero-filled activation ID
%c The copy number of the backup piece within a set of duplexed backup pieces.bMaximum value is 256
%d Database name
%D Current day of the month from the Gregorian calendar in format DD
%e Archived log sequence number
%f Absolute file number
%F Combines the DBID, day, month, year, and sequence into a unique and repeatable generated name
%h Archived redo log thread number
%I DBID
%M Month in the Gregorian calendar in the format MM
%n Database name, padded on the right with x characters to a total length of eight characters
%N Tablespace name. Only valid when backing up datafiles as image copies.
%p Piece number within the backup set. This value starts at 1 for each backup set and is incremented by 1
 for each backup piece created. If a PROXY is specified, the %p variable must be included in the FORMAT
 string either explicitly or implicitly within %U.
%r Resetlogs ID
%s Backup set number. This number is a counter in the control file that is incremented for each backup set.
 The counter value starts at 1 and is unique for the lifetime of the control file. If you restore a backup
 control file, then duplicate values can result. CREATE CONTROLFILE initializes the counter at 1.
%S Zero-filled sequence number
%t Backup set time stamp, a 4-byte value derived as the number of seconds elapsed since a fixed reference time.
 The combination of %s and %t can be used to form a unique name for the backup set.
%T Year, month, and day in the Gregorian calendar in the format: YYYYMMDD
%u An 8-character name constituted by compressed representations of the backup set or image copy number and the
 time the backup set or image copy was created
%U A system-generated unique filename (default). %U is different for image copies and backup pieces.
 For a backup piece, %U is a shorthend for %u_%p_%c and guarantees uniqueness in generated backup filenames.
 For an image copy of a datafile, %U means the following: data-D-%d_id-%I_TS-%N_FNO-%f_%u

%Y Year in this format: YYYY

%% Percent (%) character. For example, %%Y translates to the string %Y

################################################################

#####################
## RMAN STEUP
#####################

connect target /
 connect catalog  usr_catalog/xxxxxx@RMAN_Catalog.emilianofusaglia.net
REGISTER DATABASE;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
 CONFIGURE BACKUP OPTIMIZATION ON;
 CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
 CONFIGURE CONTROLFILE AUTOBACKUP ON;
 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+FRA1/%F';
 CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
 CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' RATE 200M PARMS 'ENV=(NB_ORA_POLICY=ora_monthly)' FORMAT 'backup_%d_%T_%U';
 CONFIGURE CHANNEL DEVICE TYPE DISK RATE 200M FORMAT '+BACKUP/%d_%T_%U';
 CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
 CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FRA1/snapcf_dbrm07.cf';
--Full Database backup
 BACKUP INCREMENTAL LEVEL = 0 DATABASE PLUS ARCHIVELOG NOT BACKED UP 1 TIMES;
--Incremental Database backup
 BACKUP INCREMENTAL LEVEL = 1 DATABASE PLUS ARCHIVELOG NOT BACKED UP 1 TIMES;
--RMAN Catalog Maintenance and Backup Validation
 SHOW ALL;
 run{
 ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;
 allocate channel tape1 type 'SBT_TAPE' PARMS 'ENV=(NB_ORA_CLIENT=lxrm01.emilianofusaglia.net)';
 crosscheck backup completed before "sysdate-30";
 delete noprompt expired backup;
 delete noprompt obsolete;
 }
--Rman basic Backup Report
 SHOW all;
 list backup summary;

################################################################
##                                        RESTORE TESTS
################################################################

–Restore Datafile from TAPE

SQL> startup mount
 ORACLE instance started.
Total System Global Area 4277059584 bytes
 Fixed Size                  2154936 bytes
 Variable Size            2499812936 bytes
 Database Buffers         1728053248 bytes
 Redo Buffers               47038464 bytes
 Database mounted.
 SQL>
 SQL>
 SQL> select * from v$recover_file;
FILE# ONLINE  ONLINE_  ERROR                       CHANGE#    TIME
 ---------- ------- -------  -------------------------- ----------  --------
 9 ONLINE  ONLINE   FILE NOT FOUND                     0
RMAN> connect target /
connected to target database: DBRM07 (DBID=3653795552, not open)
RMAN> connect catalog  usr_catalog/xxxxxx@RMAN_Catalog.emilianofusaglia.net
connected to recovery catalog database
RMAN> restore datafile 9;
Starting restore at 07.04.11
 starting full resync of recovery catalog
 full resync complete
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=1393 instance=DBRM071 device type=DISK
 allocated channel: ORA_DISK_2
 channel ORA_DISK_2: SID=1422 instance=DBRM071 device type=DISK
 allocated channel: ORA_DISK_3
 channel ORA_DISK_3: SID=1451 instance=DBRM071 device type=DISK
 allocated channel: ORA_DISK_4
 channel ORA_DISK_4: SID=1480 instance=DBRM071 device type=DISK
 allocated channel: ORA_DISK_5
 channel ORA_DISK_5: SID=1509 instance=DBRM071 device type=DISK
 allocated channel: ORA_SBT_TAPE_1
 channel ORA_SBT_TAPE_1: SID=1538 instance=DBRM071 device type=SBT_TAPE
 channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 6.5 (2010120208)
channel ORA_SBT_TAPE_1: starting datafile backup set restore
 channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
 channel ORA_SBT_TAPE_1: restoring datafile 00009 to +DATA1/dbtr07/datafile/indxtbs.562.717369571
 channel ORA_SBT_TAPE_1: reading from backup piece 13ld31mb_1_2
 channel ORA_SBT_TAPE_1: piece handle=13ld30mb_1_2 tag=TAG20110406T121258
 channel ORA_SBT_TAPE_1: restored backup piece 1
 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:04:16
 Finished restore at 07.04.11
 starting full resync of recovery catalog
 full resync complete
RMAN> recover datafile 9;
Starting recover at 07.04.11
 using channel ORA_DISK_1
 using channel ORA_DISK_2
 using channel ORA_DISK_3
 using channel ORA_DISK_4
 using channel ORA_DISK_5
 using channel ORA_SBT_TAPE_1
 channel ORA_SBT_TAPE_1: starting incremental datafile backup set restore
 channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
 destination for restore of datafile 00009: +DATA1/dbtr07/datafile/indxtbs.562.717369571
 channel ORA_SBT_TAPE_1: reading from backup piece 1eld30qo_1_2
 channel ORA_SBT_TAPE_1: piece handle=1eld30qo_1_2 tag=TAG20110406T121520
 channel ORA_SBT_TAPE_1: restored backup piece 1
 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:04:25
starting media recovery
archived log for thread 1 with sequence 162 is already on disk as file +FRA1/dbtr07/archivelog/2011_04_06/thread_1_seq_162.16655.718385527
 archived log for thread 1 with sequence 163 is already on disk as file +FRA1/dbtr07/archivelog/2011_04_06/thread_1_seq_163.8452.718385527
 archived log for thread 2 with sequence 141 is already on disk as file +FRA1/dbtr07/archivelog/2011_04_06/thread_2_seq_141.4677.718385531
 archived log for thread 3 with sequence 132 is already on disk as file +FRA1/dbtr07/archivelog/2011_04_06/thread_3_seq_132.5466.718385525
 archived log for thread 3 with sequence 133 is already on disk as file +FRA1/dbtr07/archivelog/2011_04_06/thread_3_seq_133.1479.718385525
 channel ORA_SBT_TAPE_1: starting archived log restore to default destination
 channel ORA_SBT_TAPE_1: restoring archived log
 archived log thread=3 sequence=131
 channel ORA_SBT_TAPE_1: reading from backup piece 1hld30r8_1_2
 channel ORA_SBT_TAPE_1: piece handle=1hld30r8_1_2 tag=TAG20100507T121535
 channel ORA_SBT_TAPE_1: restored backup piece 1
 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:02:15
 archived log file name=+FRA1/dbtr07/archivelog/2011_04_06/thread_3_seq_131.16592.718387087 thread=3 sequence=131
 channel ORA_SBT_TAPE_1: starting archived log restore to default destination
 channel ORA_SBT_TAPE_1: restoring archived log
 archived log thread=1 sequence=161
 channel ORA_SBT_TAPE_1: reading from backup piece 1ild30r8_1_2
 channel ORA_SBT_TAPE_1: piece handle=1ild30r8_1_2 tag=TAG20100507T121535
 channel ORA_SBT_TAPE_1: restored backup piece 1
 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:58
 archived log file name=+FRA1/dbtr07/archivelog/2011_04_06/thread_1_seq_161.13854.718387211 thread=1 sequence=161
 channel ORA_SBT_TAPE_1: starting archived log restore to default destination
 channel ORA_SBT_TAPE_1: restoring archived log
 archived log thread=2 sequence=140
 channel ORA_SBT_TAPE_1: reading from backup piece 1jld30r8_1_2
 channel ORA_SBT_TAPE_1: piece handle=1jld30r8_1_2 tag=TAG20100507T121535
 channel ORA_SBT_TAPE_1: restored backup piece 1
 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:55
 archived log file name=+FRA1/dbtr07/archivelog/2011_04_06/thread_2_seq_140.1819.718387319 thread=2 sequence=140
 channel default: deleting archived log(s)
 archived log file name=+FRA1/dbtr07/archivelog/2011_04_06/thread_1_seq_161.13854.718387211 RECID=824 STAMP=718387211
 channel default: deleting archived log(s)
 archived log file name=+FRA1/dbtr07/archivelog/2011_04_06/thread_2_seq_140.1819.718387319 RECID=825 STAMP=718387319
 channel default: deleting archived log(s)
 archived log file name=+FRA1/dbtr07/archivelog/2011_04_06/thread_3_seq_131.16592.718387087 RECID=823 STAMP=718387086
 media recovery complete, elapsed time: 00:00:02
 Finished recover at 07.04.11