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: 

• RMAN: Merged Incremental Backup Strategies (Doc ID 745798.1)
• Incrementally updating backups
• Image courtesy: https://www.rubrik.com

Happy Backups...!!!

Script to gather details while using Datapump for DB migration

 Here is a script that can be used to gather source database details when we are attempting to upgrade from one version of DB to another or migrate Oracle DB from one server to another. 

The purpose is to gather as much details so that the target DB can be prepared in such a way we don't have much work post import of the required schemas. 

/* -----------------------------------------------------------------------------------------
Script name 	: export_details.sql
Usage			: sqlplus / as sysdba @export_details.sql

Instructions	: 
Replace all SCHEMA1 and SCHEMA2 with the schemas you are intended to export 
and save the file as export_details.sql, use as many users needed

Important Note	: 
Once details gathered, export NLS_LANG as specified below to set env variable
and start export by using the options mentioned in the end of this file

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 - Check what is the NLS_LANG needed as per app

This script will create 3 o/p files, 1 html and 2 sql files.
------------------------------------------------------------------------------------------ */

set echo on pages 999 long 90000
col OWNER for a30
col USERNAME for a30
col PROFILE for a30
col GRANTED_ROLE for a30
col GRANTEE for a30
col PROPERTY_NAME for a40
col PROPERTY_VALUE for a40
col DISPLAY_VALUE for a50
col DEFAULT_VALUE for a50
set markup html on spool on pre off
spool source_details.html

Prompt Parameter details:
------ ------------------
select name, DISPLAY_VALUE from v$parameter where name in ('service_names', 'compatible','sga_max_size','sga_target','log_buffer','db_cache_size','pga_aggregate_target', 'pga_aggregate_limit','cpu_count','session_cached_cursors','open_cursors','processes') order by name;

Prompt User details:
------ -------------
select count(*) "Total Schemas" from dba_users;
select sum(bytes)/1024/1024/1024 Total_DB_used_Size_In_GB from dba_segments;
select sum(bytes)/1024/1024/1024 Total_DB_allocated_Size_In_GB from dba_data_files;
SELECT owner,sum(bytes)/1024/1024/1024 Size_In_GB from dba_segments WHERE owner IN ('SCHEMA1','SCHEMA2') group by owner order by owner;

Prompt Info about Schemas:
------ -------------------
select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE from dba_users where USERNAME in ('SCHEMA1','SCHEMA2') order by 1;

set markup html on spool on pre on
Prompt Profile details for user:
------ -------------------------
SELECT dbms_metadata.get_ddl('PROFILE', p.profile) || ';' from dba_profiles p where profile not in ('DEFAULT') and resource_name='COMPOSITE_LIMIT';

set markup html on spool on pre off
Prompt Tablespace Size for particular schema:
------ --------------------------------------
select owner, TABLESPACE_NAME, sum(BYTES)/1024/1024/1024 Size_In_GB from dba_segments where OWNER in ('SCHEMA1','SCHEMA2') group by owner,TABLESPACE_NAME order by owner,TABLESPACE_NAME;

set markup html on spool on pre on
Prompt DDL for Schema, Non default Roles and Tablespace:
------ -------------------------------------------------
SELECT dbms_metadata.get_ddl('USER',u.username) || ';' as USERS from dba_users u where username in ('SCHEMA1','SCHEMA2');
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) || ';' as Tablespaces from dba_tablespaces tb where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS','TEMP');
select dbms_metadata.get_ddl('ROLE', r.role) || ';' as Roles from dba_roles r where ORACLE_MAINTAINED<>'Y';

set markup html on spool on pre off
Prompt Temp tablespace size:
------ ---------------------
select tablespace_name, sum(BYTES)/1024/1024/1024 from dba_temp_files group by tablespace_name;

Prompt Objects counts:
------ ---------------
select owner, object_type, status, count(*) from dba_objects where owner in ('SCHEMA1','SCHEMA2') group by object_type,owner, status order by 1,2,3;

Prompt Roles:
------ ------
select GRANTEE, GRANTED_ROLE, ADMIN_OPTION from dba_role_privs where grantee in ('SCHEMA1','SCHEMA2') order by 1,2;
select GRANTEE, PRIVILEGE, ADMIN_OPTION from dba_sys_privs where grantee in ('SCHEMA1','SCHEMA2') order by 1,2;


Prompt System privileges having a property value = 1
Prompt Use specific DBMS packages such as DBMS_RESOURCE_MANAGER_PRIVS to provide grants to these objects. 
------ --------------------------------------------------------------------------------------------------
select p.grantee, m.name from system_privilege_map m, dba_sys_privs p where m.name=p.privilege and m.property=1 and p.grantee in ('SCHEMA1','SCHEMA2') order by p.grantee, m.name;

Prompt DB Time Zone Details:
------ ---------------------
select sysdate,systimestamp,current_timestamp,sessiontimezone,dbtimezone from dual;

Prompt DB Characterset:
------ ----------------
select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

spool off

Prompt Generating grants script:
------ -------------------------
set markup html off
set lines 200 pages 0 head off feed off
spool source_all_privs.sql

select 'grant ' || privilege ||' on '|| owner || '.' || table_name ||' to '||grantee||';' from dba_tab_privs where grantee in ('SCHEMA1','SCHEMA2') and table_name NOT like 'BIN%';
spool off

spool source_all_synonyms.sql

SELECT 'CREATE OR REPLACE PUBLIC SYNONYM '||SYNONYM_NAME||' FOR '||TABLE_OWNER||'.'||TABLE_NAME||';' FROM ALL_SYNONYMS WHERE OWNER='PUBLIC' AND TABLE_OWNER IN ('SCHEMA1','SCHEMA2');

spool off

exit

Once the details are gathered, we can use the generated html file to prepare the target DB like creating required tablespaces, roles, etc. 

Once the import is completed in the target DB, both the sql scripts can be run against target DB to make sure all the grants are provided and public synonyms are created. 

Let me know in comments on whether we can further add any details which might be needed for collecting details on source DB. 

Also check, Datapump & system privileges with property value = 1 for details on this part. 

Happy Datapumping...!!!