RMAN backup restore large files Oracle Database 12C release 1 (12.1)

There are two important improvements in making big datafile backups and restores in Oracle 12C release 1

  • multisection backup of image copies
  • multisection backup of incremental backups for level higher than 0

Multisection option was introduced in 11G and possible only for:

  • multisection backup of backup sets
  • multisection backup of full backup
  • multisection backup of incremental backups for level 0

Requirements and restrictions

  • COMPATIBLE must be set to 12.0 or higher
  • Not for control files or spfiles

Multisection image copies

SECTION SIZE can be specified for image copies in RMAN to divide large file into subsections that can be backed up in parallel across multiple channels.

RMAN> backup as copy datafile 6 section size 400m;

Example

In Oracle Database 11g the command raises RMAN exception

RMAN> backup as copy datafile 5 section size 500m;

RMAN-00571: =========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============
RMAN-00571: =========================================================
RMAN-03002: failure of backup command at 11/28/2014 21:13:00
RMAN-06580: the SECBYTES option cannot be used with AS COPY

In Oracle Database 12C image copy of large file with parallel level 4 works smoothly.

set ORACLE_SID=ORA12C

d:\app\oracle\product\12.1.0\dbhome_1\BIN>rman target /
Recovery Manager: Release 12.1.0.2.0 - Production 
on Fri Nov 28 21:03:48 2014
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  
All rights reserved.
connected to target database: ORA12C (DBID=241888208)

RMAN> configure device type disk parallelism 4;

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN> report schema;

Report of database schema for database with db_unique_name ORA12C

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    800      SYSTEM               YES     D:\APP\ORACLE\ORADATA\ORA12C\SYSTEM01.DBF
2    250      PDB$SEED:SYSTEM      NO      D:\APP\ORACLE\ORADATA\ORA12C\PDBSEED\SYSTEM01.DBF
...
8    610      PORA12C1:SYSAUX      NO      D:\APP\ORACLE\ORADATA\ORA12C\PORA12C1\SYSAUX01.DBF
9    4637     PORA12C1:USERS       NO      D:\APP\ORACLE\ORADATA\ORA12C\PORA12C1\SAMPLE_SCHEMA_USERS01.DBF
10   1260     PORA12C1:EXAMPLE     NO      D:\APP\ORACLE\ORADATA\ORA12C\PORA12C1\EXAMPLE01.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    197      TEMP                 32767       D:\APP\ORACLE\ORADATA\ORA12C\TEMP01.DBF
2    100      PDB$SEED:TEMP        32767       D:\APP\ORACLE\ORADATA\ORA12C\PDBSEED\PDBSEED_TEMP012014-11-20_12-43-47-AM.DBF
3    197      PORA12C1:TEMP        32767       D:\APP\ORACLE\ORADATA\ORA12C\PORA12C1\PORA12C1_TEMP012014-11-20_12-51-04-AM.DBF

RMAN>

RMAN> backup as copy datafile 9 section size 500m;

Starting backup at 28-NOV-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=302 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=363 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=419 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=123 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=D:\APP\ORACLE\ORADATA\ORA12C\PORA12C1\SAMPLE_SCHEMA_USERS01.DBF
backing up blocks 1 through 64000
channel ORA_DISK_2: starting datafile copy
input datafile file number=00009 name=D:\APP\ORACLE\ORADATA\ORA12C\PORA12C1\SAMPLE_SCHEMA_USERS01.DBF
backing up blocks 64001 through 128000
channel ORA_DISK_3: starting datafile copy
input datafile file number=00009 name=D:\APP\ORACLE\ORADATA\ORA12C\PORA12C1\SAMPLE_SCHEMA_USERS01.DBF
...
backing up blocks 512001 through 576000
output file name=D:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\DATA_D-ORA12C_I-241888208_TS-USERS_FNO-9_0MPOP7E9 tag=TAG20141128T210409
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_2: starting datafile copy
input datafile file number=00009 name=D:\APP\ORACLE\ORADATA\ORA12C\PORA12C1\SAMPLE_SCHEMA_USERS01.DBF
backing up blocks 576001 through 593600
...
Finished backup at 28-NOV-14

Starting Control File and SPFILE Autobackup at 28-NOV-14
piece handle=D:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\C-241888208-20141128-04 comment=NONE
Finished Control File and SPFILE Autobackup at 28-NOV-14

RMAN>

Multisection incremental backups

SECTION SIZE can be specified for incremental backups with any level higher in RMAN to divide large file into subsections that can be backed up in parallel across multiple channels. In previous release 11G it worked only for level 0.

RMAN> backup incremental level 1 section size 100m datafile 1;

Example

In previous release 11g for level higher than 1 section size is ignored and backup is executed with one channel.

RMAN> report schema;
Report of database schema for database with db_unique_name ORA11G

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    760      SYSTEM               ***     D:\APP\ORACLE\ORADATA\ORA11G\SYSTEM01.DBF
2    640      SYSAUX               ***     D:\APP\ORACLE\ORADATA\ORA11G\SYSAUX01.DBF
3    100      UNDOTBS1             ***     D:\APP\ORACLE\ORADATA\ORA11G\UNDOTBS01.DBF
4    5        USERS                ***     D:\APP\ORACLE\ORADATA\ORA11G\USERS01.DBF
5    346      EXAMPLE              ***     D:\APP\ORACLE\ORADATA\ORA11G\EXAMPLE01.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    29       TEMP                 32767       D:\APP\ORACLE\ORADATA\ORA11G\TEMP01.DBF

RMAN> backup incremental level 1 section size 100m datafile 2;

Starting backup at 28-NOV-14
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=D:\APP\ORACLE\ORADATA\ORA11G\SYSAUX01.DBF
channel ORA_DISK_1: starting piece 1 at 28-NOV-14
channel ORA_DISK_1: finished piece 1 at 28-NOV-14
piece handle=D:\APP\ORACLE\FAST_RECOVERY_AREA\ORA11G\BACKUPSET\2014_11_28\O1_MF_NNND1_TAG20141128T222411_B7KSZVVD_.BKP tag=TAG20141128T222411 comment=
NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 28-NOV-14

In Oracle Database 12C incremental backup for level higher than 0 doesn’t ignore section size.

RMAN> report schema;

Report of database schema for database with db_unique_name ORA12C

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    800      SYSTEM               YES     D:\APP\ORACLE\ORADATA\ORA12C\SYSTEM01.DBF
...
9    4637     PORA12C1:USERS       NO      D:\APP\ORACLE\ORADATA\ORA12C\PORA12C1\SAMPLE_SCHEMA_USERS01.DBF
10   1260     PORA12C1:EXAMPLE     NO      D:\APP\ORACLE\ORADATA\ORA12C\PORA12C1\EXAMPLE01.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    197      TEMP                 32767       D:\APP\ORACLE\ORADATA\ORA12C\TEMP01.DBF
2    100      PDB$SEED:TEMP        32767       D:\APP\ORACLE\ORADATA\ORA12C\PDBSEED\PDBSEED_TEMP012014-11-20_12-43-47-AM.DBF
3    197      PORA12C1:TEMP        32767       D:\APP\ORACLE\ORADATA\ORA12C\PORA12C1\PORA12C1_TEMP012014-11-20_12-51-04-AM.DBF

RMAN> backup incremental level 1 section size 250m datafile 1;

Starting backup at 28-NOV-14
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\ORACLE\ORADATA\ORA12C\SYSTEM01.DBF
backing up blocks 1 through 32000
channel ORA_DISK_1: starting piece 1 at 28-NOV-14
channel ORA_DISK_2: starting incremental level 1 datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\ORACLE\ORADATA\ORA12C\SYSTEM01.DBF
backing up blocks 32001 through 64000
channel ORA_DISK_2: starting piece 2 at 28-NOV-14
channel ORA_DISK_3: starting incremental level 1 datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\ORACLE\ORADATA\ORA12C\SYSTEM01.DBF
backing up blocks 64001 through 96000
channel ORA_DISK_3: starting piece 3 at 28-NOV-14
channel ORA_DISK_4: starting incremental level 1 datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\ORACLE\ORADATA\ORA12C\SYSTEM01.DBF
backing up blocks 96001 through 102400
channel ORA_DISK_4: starting piece 4 at 28-NOV-14
channel ORA_DISK_1: finished piece 1 at 28-NOV-14
...
Finished backup at 28-NOV-14

Starting Control File and SPFILE Autobackup at 28-NOV-14
piece handle=D:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\C-241888208-20141128-08 comment=NONE
Finished Control File and SPFILE Autobackup at 28-NOV-14

Have a fun 🙂

Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *