Looking for something? Try here..

Thursday, June 30, 2022

RMAN Merged incremental backups

 We are changing our backup solutions vendor in our company and the new vendor utilizes the RMAN merged incremental backup. So let's see what is this and what are the advantages of this RMAN capability. 

Merged Incremental creates a level 0 image copy backup of all of your database's datafiles in a disk location. All datafiles are backed up using the same tag name. Block Change Tracking can be used for the incremental level 1 backups for Fast Incremental Backups


Let's see a quick demo on how to perform a Merged Incremental backup
run {
allocate channel c1 device type disk format '/media/sf_Oracle/RMAN-training/%U';
recover copy of database with tag 'Demo_merged_l0';
backup incremental level 1 copies=1 for recover of copy with tag 'Demo_merged_l0' database;
}
This is the default strategy which OEM uses. Let's say we are scheduling backup every day at 12 pm, then the copy of datafiles will be 24 hrs behind. 

First day: Run 1
[oracle@linux-8 RMAN-training]$ rman target sys/oracle@odb1

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jun 30 01:10:09 2022
Version 19.10.0.0.0

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

connected to target database: CDB19:ODB1 (DBID=223390358)

RMAN> run {
allocate channel c1 device type disk format '/media/sf_Oracle/RMAN-training/%U';
recover copy of database with tag 'Demo_merged_l0';
backup incremental level 1 copies=1 for recover of copy with tag 'Demo_merged_l0' database;
}2> 3> 4> 5>

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=414 device type=DISK

Starting recover at 30-JUN-22
no copy of datafile 16 found to recover
no copy of datafile 17 found to recover
no copy of datafile 18 found to recover
no copy of datafile 19 found to recover
Finished recover at 30-JUN-22

Starting backup at 30-JUN-22
no parent backup or copy of datafile 18 found
no parent backup or copy of datafile 16 found
no parent backup or copy of datafile 17 found
no parent backup or copy of datafile 19 found
channel c1: starting datafile copy
input datafile file number=00018 name=+DATA/ODB1/DATAFILE/undotbs1.273.1084998671
output file name=/media/sf_Oracle/RMAN-training/data_D-CDB19_I-3846007102_TS-UNDOTBS1_FNO-18_kr11ag49 tag=DEMO_MERGED_L0 RECID=95 STAMP=1108689035
channel c1: datafile copy complete, elapsed time: 00:00:03
channel c1: starting datafile copy
input datafile file number=00016 name=+DATA/ODB1/DATAFILE/system.272.1084998669
output file name=/media/sf_Oracle/RMAN-training/data_D-CDB19_I-3846007102_TS-SYSTEM_FNO-16_ks11ag4c tag=DEMO_MERGED_L0 RECID=96 STAMP=1108689038
channel c1: datafile copy complete, elapsed time: 00:00:03
channel c1: starting datafile copy
input datafile file number=00017 name=+DATA/ODB1/DATAFILE/sysaux.274.1084998671
output file name=/media/sf_Oracle/RMAN-training/data_D-CDB19_I-3846007102_TS-SYSAUX_FNO-17_kt11ag4f tag=DEMO_MERGED_L0 RECID=97 STAMP=1108689040
channel c1: datafile copy complete, elapsed time: 00:00:01
channel c1: starting datafile copy
input datafile file number=00019 name=+DATA/CDB19/CD7591F2458BB5B4E053B538A8C08E48/DATAFILE/users.275.1108164853
output file name=/media/sf_Oracle/RMAN-training/data_D-CDB19_I-3846007102_TS-USERS_FNO-19_ku11ag4g tag=DEMO_MERGED_L0 RECID=98 STAMP=1108689040
channel c1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-JUN-22
released channel: c1

RMAN> exit


Recovery Manager complete.
[oracle@linux-8 RMAN-training]$ ls -lrt
...
-rwxrwx---. 1 root vboxsf 1447043072 Jun 30 01:10 data_D-CDB19_I-3846007102_TS-UNDOTBS1_FNO-18_kr11ag49
-rwxrwx---. 1 root vboxsf 1258299392 Jun 30 01:10 data_D-CDB19_I-3846007102_TS-SYSTEM_FNO-16_ks11ag4c
-rwxrwx---. 1 root vboxsf  650125312 Jun 30 01:10 data_D-CDB19_I-3846007102_TS-SYSAUX_FNO-17_kt11ag4f
-rwxrwx---. 1 root vboxsf   41951232 Jun 30 01:10 data_D-CDB19_I-3846007102_TS-USERS_FNO-19_ku11ag4g
We can see we get "no copy of database # found to recover" as this is the first run and we don't have any copy made before to recover. 
Also the backup command throws "no parent backup or copy of datafile # found" since we are triggering a incremental level 1 backup without a prior level 0 backup. In this case, Oracle will create a fresh level 0 copy of the datafiles. This can be identified using "channel c1: starting datafile copy" without the mention of incremental level 1 which we will see in the next run. 

Second day: Run 2

RMAN> run {
allocate channel c1 device type disk format '/media/sf_Oracle/RMAN-training/%U';
recover copy of database with tag 'Demo_merged_l0';
backup incremental level 1 copies=1 for recover of copy with tag 'Demo_merged_l0' database;
}2> 3> 4> 5>

allocated channel: c1
channel c1: SID=414 device type=DISK

Starting recover at 30-JUN-22
no copy of datafile 16 found to recover
no copy of datafile 17 found to recover
no copy of datafile 18 found to recover
no copy of datafile 19 found to recover
Finished recover at 30-JUN-22

Starting backup at 30-JUN-22
channel c1: starting compressed incremental level 1 datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00018 name=+DATA/ODB1/DATAFILE/undotbs1.273.1084998671
input datafile file number=00016 name=+DATA/ODB1/DATAFILE/system.272.1084998669
input datafile file number=00017 name=+DATA/ODB1/DATAFILE/sysaux.274.1084998671
input datafile file number=00019 name=+DATA/CDB19/CD7591F2458BB5B4E053B538A8C08E48/DATAFILE/users.275.1108164853
channel c1: starting piece 1 at 30-JUN-22
channel c1: finished piece 1 at 30-JUN-22
piece handle=/media/sf_Oracle/RMAN-training/kv11agra_671_1_1 tag=DEMO_MERGED_L0 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 30-JUN-22
released channel: c1

RMAN> exit
[oracle@linux-8 RMAN-training]$ ls -lrt
...
-rwxrwx---. 1 root vboxsf 1447043072 Jun 30 01:10 data_D-CDB19_I-3846007102_TS-UNDOTBS1_FNO-18_kr11ag49
-rwxrwx---. 1 root vboxsf 1258299392 Jun 30 01:10 data_D-CDB19_I-3846007102_TS-SYSTEM_FNO-16_ks11ag4c
-rwxrwx---. 1 root vboxsf  650125312 Jun 30 01:10 data_D-CDB19_I-3846007102_TS-SYSAUX_FNO-17_kt11ag4f
-rwxrwx---. 1 root vboxsf   41951232 Jun 30 01:10 data_D-CDB19_I-3846007102_TS-USERS_FNO-19_ku11ag4g
-rwxrwx---. 1 root vboxsf    2482176 Jun 30 01:22 kv11agra_671_1_1
We can still see "no copy of database # found to recover" as we don't have a previous level 1 backup. Remember the run 1 created a level 0 backup. Now, second created a incremental level 1 backup which you can see from "starting compressed incremental level 1 datafile backup set" line and also from the files list kv11agra_671_1_1

Third day: Run 3 and above
RMAN> run {
allocate channel c1 device type disk format '/media/sf_Oracle/RMAN-training/%U';
recover copy of database with tag 'Demo_merged_l0';
backup incremental level 1 copies=1 for recover of copy with tag 'Demo_merged_l0' database;
}2> 3> 4> 5>

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=414 device type=DISK

Starting recover at 30-JUN-22
channel c1: starting incremental datafile backup set restore
channel c1: specifying datafile copies to recover
recovering datafile copy file number=00016 name=/media/sf_Oracle/RMAN-training/data_D-CDB19_I-3846007102_TS-SYSTEM_FNO-16_ks11ag4c
recovering datafile copy file number=00017 name=/media/sf_Oracle/RMAN-training/data_D-CDB19_I-3846007102_TS-SYSAUX_FNO-17_kt11ag4f
recovering datafile copy file number=00018 name=/media/sf_Oracle/RMAN-training/data_D-CDB19_I-3846007102_TS-UNDOTBS1_FNO-18_kr11ag49
recovering datafile copy file number=00019 name=/media/sf_Oracle/RMAN-training/data_D-CDB19_I-3846007102_TS-USERS_FNO-19_ku11ag4g
channel c1: reading from backup piece /media/sf_Oracle/RMAN-training/kv11agra_671_1_1
channel c1: piece handle=/media/sf_Oracle/RMAN-training/kv11agra_671_1_1 tag=DEMO_MERGED_L0
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
Finished recover at 30-JUN-22

Starting backup at 30-JUN-22
channel c1: starting compressed incremental level 1 datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00018 name=+DATA/ODB1/DATAFILE/undotbs1.273.1084998671
input datafile file number=00016 name=+DATA/ODB1/DATAFILE/system.272.1084998669
input datafile file number=00017 name=+DATA/ODB1/DATAFILE/sysaux.274.1084998671
input datafile file number=00019 name=+DATA/CDB19/CD7591F2458BB5B4E053B538A8C08E48/DATAFILE/users.275.1108164853
channel c1: starting piece 1 at 30-JUN-22
channel c1: finished piece 1 at 30-JUN-22
piece handle=/media/sf_Oracle/RMAN-training/l011ahbn_672_1_1 tag=DEMO_MERGED_L0 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 30-JUN-22
released channel: c1

RMAN> exit
[oracle@linux-8 RMAN-training]$ ls -lrt
...
-rwxrwx---. 1 root vboxsf    2482176 Jun 30 01:22 kv11agra_671_1_1
-rwxrwx---. 1 root vboxsf   41951232 Jun 30 01:31 data_D-CDB19_I-3846007102_TS-USERS_FNO-19_ku11ag4g
-rwxrwx---. 1 root vboxsf  650125312 Jun 30 01:31 data_D-CDB19_I-3846007102_TS-SYSAUX_FNO-17_kt11ag4f
-rwxrwx---. 1 root vboxsf 1447043072 Jun 30 01:31 data_D-CDB19_I-3846007102_TS-UNDOTBS1_FNO-18_kr11ag49
-rwxrwx---. 1 root vboxsf 1258299392 Jun 30 01:31 data_D-CDB19_I-3846007102_TS-SYSTEM_FNO-16_ks11ag4c
-rwxrwx---. 1 root vboxsf    2580480 Jun 30 01:31 l011ahbn_672_1_1
Now we can see the previous level 1 backup has been applied on the datafile copies and also an incr level 1 copy has been taken. 
You can see the copy is been recovered the next day and hence the copy will always be 24 hrs behind.

We can change the strategy to make the copies current by flipping the backup and recover commands like below. 
run {
allocate channel c1 device type disk format '/media/sf_Oracle/RMAN-training/%U';
backup incremental level 1 copies=1 for recover of copy with tag 'Demo_merged_l0' database;
recover copy of database with tag 'Demo_merged_l0';
}
Coming to retention...

The above strategy will work for us if we need to restore the backup on the same day or from the period the datafile copy is made. Now, if we need to have a retention for a longer period say 30 days, then we can lag the recover by 30 days by using the below command
RECOVER COPY OF DATABASE WITH TAG 'Demo_merged_l0' UNTIL TIME "SYSDATE-31" FROM TAG 'Demo_merged_l0';
Block change tracking can also be used to take the incremental backups. In order to track 30 days of incr level 1, the hidden parameter _bct_bitmaps_per_file should be increased from its default value of 8 (8 days) to 30. 

Advantages: 

For VLDBs, this solution will provide quicker backups as only the 1st day we will take the full backup and then it will be continuous ever incremental backup only.

References: 

Happy Backups...!!!
Posted by at
Labels: , , , ,

4 comments:

Subscribe to: Post Comments (Atom)