Let us learn Oracle Database Administration skills: How to perform oracle RDBMS backup to multiple location in disk

Monday, September 25, 2023

How to perform oracle RDBMS backup to multiple location in disk

Situation:

In your project, you may be in a situation where

a. DB size is in several TBs.

b. Backup mountpath allocated isnt in proportion to the db size, rpo, rto requirement

i. due to os & filesystem format limitation (linux <7 has 16TB limitation for ext* FS)

In such situation, we may have to distribute the backups to multiple locations using rman.

Solution Tried:

1a. Channel configuration to send identified (d1, d2 etc..) to the corresponding mountpath

CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/backup1/rman_bkp/ch1_%U';

CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/backup2/rman_bkp/ch5_%U';

1b. Channel config through run

ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '/backup1/rman_bkp/%U';

ALLOCATE CHANNEL d2 DEVICE TYPE DISK FORMAT '/backup2/rman_bkp/%U';

Test outcome: Worked as expected

2. Configure the multiple disk location in the channel config like below

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/oraarch/ORA19C/backup/bkp%U', '/u01/app/oracle/admin/ORA19C/bkpspace2/bkp%U';

Test outcome: Didn’t work

Steps for solution 1a)

1) Preserve the rman setting using

show all;

Actual output:

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name ORA19C 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;

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 '/u01/app/oracle/product/19.0.0/db_1/dbs/snapcf_ORA19C.f'; # default

RMAN>

2) Perform the db backup using the below setting.. Ensure the # of disk parallelism and channel 1,2 format are aligned. If you used 5 in place of 2, it wont work for 2nd channel.

CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/oraarch/ORA19C/backup/rman_%d_%U';

CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/u01/app/oracle/admin/ORA19C/bkpspace2/rman_%d_%U';

Actual output:

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

old RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/oraarch/ORA19C/backup/rman_%d_%U';

new RMAN configuration parameters:

CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/oraarch/ORA19C/backup/rman_%d_%U';

new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/u01/app/oracle/admin/ORA19C/bkpspace2/rman_%d_%U';

new RMAN configuration parameters:

CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/u01/app/oracle/admin/ORA19C/bkpspace2/rman_%d_%U';

new RMAN configuration parameters are successfully stored

RMAN>

3) Verify the rman backup settings using

show all;

Actual output:

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORA19C 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 2 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/oraarch/ORA19C/backup/rman_%d_%U';

CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/u01/app/oracle/admin/ORA19C/bkpspace2/rman_%d_%U';

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 '/u01/app/oracle/product/19.0.0/db_1/dbs/snapcf_ORA19C.f'; # default

RMAN>

4) Perform the backup using the below command...

backup as compressed backupset section size 32G filesperset 1 database;

RMAN> backup as compressed backupset section size 32G filesperset 1 database;

The command has no syntax errors

RMAN> exit

Recovery Manager complete.

[oracle@vcentos79-oracle-ha1 backup]$

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Sep 25 13:06:17 2023

Version 19.3.0.0.0

connected to target database: ORA19C (DBID=1186647419)

RMAN> backup as compressed backupset section size 32G filesperset 1 database;

Starting backup at 25-SEP-23

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=70 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=57 device type=DISK

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/oradata/ORA19C/system01.dbf

channel ORA_DISK_2: specifying datafile(s) in backup set

input datafile file number=00012 name=/oradata/ORA19C/opdb1/users01.dbf

channel ORA_DISK_2: starting piece 1 at 25-SEP-23

channel ORA_DISK_1: finished piece 1 at 25-SEP-23

piece handle=/oraarch/ORA19C/backup/rman_ORA19C_0j278mkk_1_1 tag=TAG20230925T130624 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04

channel ORA_DISK_2: finished piece 1 at 25-SEP-23

piece handle=/u01/app/oracle/admin/ORA19C/bkpspace2/rman_ORA19C_0m278mkn_1_1 tag=TAG20230925T130624 comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01

Finished backup at 25-SEP-23

Starting Control File and SPFILE Autobackup at 25-SEP-23

piece handle=/oraarch/ORA19C/autobackup/2023_09_25/o1_mf_s_1148476056_lk2y08m1_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 25-SEP-23

RMAN>

5) Verify the backup:

RMAN> list backup of database summary;

using target database control file instead of recovery catalog

List of Backups

===============

Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

------- -- -- - ----------- --------------- ------- ------- ---------- ---

12 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624

13 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624

14 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624

15 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624

16 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624

17 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624

18 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624

19 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624

20 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624

21 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624

22 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624

RMAN>

Location:

[oracle@vcentos79-oracle-ha1 backup]$ ls -altr /oraarch/ORA19C/backup/ /u01/app/oracle/admin/ORA19C/bkpspace2

/oraarch/ORA19C/backup/:

total 630832

drwxr-x---. 5 oracle oinstall 56 Sep 25 08:58 ..

-rw-r-----. 1 oracle oinstall 239403008 Sep 25 13:07 rman_ORA19C_0c278mih_1_1

-rw-r-----. 1 oracle oinstall 62070784 Sep 25 13:07 rman_ORA19C_0h278mk2_1_1

-rw-r-----. 1 oracle oinstall 21512192 Sep 25 13:07 rman_ORA19C_0j278mkk_1_1

...

/u01/app/oracle/admin/ORA19C/bkpspace2:

total 419716

drwxr-x---. 7 oracle oinstall 81 Sep 25 08:53 ..

-rw-r-----. 1 oracle oinstall 66502656 Sep 25 13:06 rman_ORA19C_0d278mih_1_1

-rw-r-----. 1 oracle oinstall 1425408 Sep 25 13:06 rman_ORA19C_0e278mja_1_1

-rw-r-----. 1 oracle oinstall 40804352 Sep 25 13:07 rman_ORA19C_0f278mjb_1_1

-rw-r-----. 1 oracle oinstall 40738816 Sep 25 13:07 rman_ORA19C_0g278mjq_1_1

-rw-r-----. 1 oracle oinstall 61997056 Sep 25 13:07 rman_ORA19C_0i278mk5_1_1

-rw-r-----. 1 oracle oinstall 1097728 Sep 25 13:07 rman_ORA19C_0k278mkk_1_1

-rw-r-----. 1 oracle oinstall 1245184 Sep 25 13:07 rman_ORA19C_0l278mkm_1_1

-rw-r-----. 1 oracle oinstall 1073152 Sep 25 13:07 rman_ORA19C_0m278mkn_1_1

[oracle@vcentos79-oracle-ha1 backup]$ du -sh /u01/app/oracle/admin/ORA19C/bkpspace2 /oraarch/ORA19C/backup/

410M /u01/app/oracle/admin/ORA19C/bkpspace2

617M /oraarch/ORA19C/backup/

[oracle@vcentos79-oracle-ha1 backup]$

>> More or less uniform size distribution.

Steps for solution 1b)

1) Run the backup without any setting change using the below command:

run

{

ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '/oraarch/ORA19C/backup/rman_test_%d_%U';

ALLOCATE CHANNEL d2 DEVICE TYPE DISK FORMAT '/u01/app/oracle/admin/ORA19C/bkpspace2/rman_test_%d_%U';

backup as compressed backupset section size 32G filesperset 1 database;

}

No setting changes in rman..

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORA19C 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 2 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/oraarch/ORA19C/backup/rman_%d_%U';

CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/u01/app/oracle/admin/ORA19C/bkpspace2/rman_%d_%U';

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 '/u01/app/oracle/product/19.0.0/db_1/dbs/snapcf_ORA19C.f'; # default

RMAN> run

{

ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '/oraarch/ORA19C/backup/rman_test_%d_%U';

ALLOCATE CHANNEL d2 DEVICE TYPE DISK FORMAT '/u01/app/oracle/admin/ORA19C/bkpspace2/rman_test_%d_%U';

2> 3> 4> backup as compressed backupset section size 32G filesperset 1 database;

}5> 6>

allocated channel: d1

channel d1: SID=86 device type=DISK

allocated channel: d2

channel d2: SID=33 device type=DISK

Starting backup at 25-SEP-23

channel d1: starting compressed full datafile backup set

channel d1: specifying datafile(s) in backup set

input datafile file number=00001 name=/oradata/ORA19C/system01.dbf

channel d1: starting piece 1 at 25-SEP-23

channel d2: starting compressed full datafile backup set

channel d2: specifying datafile(s) in backup set

input datafile file number=00003 name=/oradata/ORA19C/sysaux01.dbf

piece handle=/u01/app/oracle/admin/ORA19C/bkpspace2/rman_test_ORA19C_11278mva_1_1 tag=TAG20230925T131159 comment=NONE

channel d2: backup set complete, elapsed time: 00:00:01

channel d2: starting compressed full datafile backup set

channel d2: specifying datafile(s) in backup set

input datafile file number=00012 name=/oradata/ORA19C/opdb1/users01.dbf

channel d2: starting piece 1 at 25-SEP-23

channel d1: finished piece 1 at 25-SEP-23

piece handle=/oraarch/ORA19C/backup/rman_test_ORA19C_0v278mv8_1_1 tag=TAG20230925T131159 comment=NONE

channel d1: backup set complete, elapsed time: 00:00:04

channel d2: finished piece 1 at 25-SEP-23

piece handle=/u01/app/oracle/admin/ORA19C/bkpspace2/rman_test_ORA19C_12278mvb_1_1 tag=TAG20230925T131159 comment=NONE

channel d2: backup set complete, elapsed time: 00:00:01

Finished backup at 25-SEP-23

Starting Control File and SPFILE Autobackup at 25-SEP-23

piece handle=/oraarch/ORA19C/autobackup/2023_09_25/o1_mf_s_1148476396_lk2ybwlm_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 25-SEP-23

released channel: d1

released channel: d2

2) Verify the backup

RMAN> list backup of database summary;

List of Backups

===============

Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

------- -- -- - ----------- --------------- ------- ------- ---------- ---

12 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624 << 1st

13 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624

14 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624

15 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624

16 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624

17 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624

18 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624

19 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624

20 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624

21 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624

22 B F A DISK 25-SEP-23 1 1 YES TAG20230925T130624

24 B F A DISK 25-SEP-23 1 1 YES TAG20230925T131159 << 2nd

25 B F A DISK 25-SEP-23 1 1 YES TAG20230925T131159

26 B F A DISK 25-SEP-23 1 1 YES TAG20230925T131159

27 B F A DISK 25-SEP-23 1 1 YES TAG20230925T131159

28 B F A DISK 25-SEP-23 1 1 YES TAG20230925T131159

29 B F A DISK 25-SEP-23 1 1 YES TAG20230925T131159

30 B F A DISK 25-SEP-23 1 1 YES TAG20230925T131159

31 B F A DISK 25-SEP-23 1 1 YES TAG20230925T131159

32 B F A DISK 25-SEP-23 1 1 YES TAG20230925T131159

33 B F A DISK 25-SEP-23 1 1 YES TAG20230925T131159

34 B F A DISK 25-SEP-23 1 1 YES TAG20230925T131159

RMAN>

Both used different format in the mountpath:

[oracle@vcentos79-oracle-ha1 backup]$ ls -altr /oraarch/ORA19C/backup/ /u01/app/oracle/admin/ORA19C/bkpspace2

/oraarch/ORA19C/backup/:

total 630832

drwxr-x---. 5 oracle oinstall 56 Sep 25 08:58 ..

-rw-r-----. 1 oracle oinstall 239403008 Sep 25 13:07 rman_ORA19C_0c278mih_1_1

-rw-r-----. 1 oracle oinstall 62070784 Sep 25 13:07 rman_ORA19C_0h278mk2_1_1

-rw-r-----. 1 oracle oinstall 21512192 Sep 25 13:07 rman_ORA19C_0j278mkk_1_1 <<backup 1

-rw-r-----. 1 oracle oinstall 239403008 Sep 25 13:12 rman_test_ORA19C_0o278msv_1_1 <<backup 2

-rw-r-----. 1 oracle oinstall 62070784 Sep 25 13:13 rman_test_ORA19C_0t278muo_1_1

drwxr-xr-x. 2 oracle oinstall 213 Sep 25 13:13 .

-rw-r-----. 1 oracle oinstall 21512192 Sep 25 13:13 rman_test_ORA19C_0v278mv8_1_1