Recovery Manager (RMAN) enhancements in Oracle Database 12c

Oracle Community

Recovery Manager (RMAN) enhancements in Oracle Database 12c

Oracle finally announced the much anticipated first ever cloud database – Oracle Database 12c. Since then, there have been so many articles, papers , presentation by several Oracle professions/experts in the Oracle community. This paper will be focused on some of the Oracle database 12c new features, yet very useful, introduced in the area of Recovery Manager (RMAN).

As part of this article, the new features outlined below will be covered thoroughly with some working examples:

  • Table or partition point-in-time recovery in RMAN
  • SQL interface support
  • Backup, recovery enhancements for Very large data files
  • Restore/Recover data files over the network
  • Active Database Duplication additions & improvements

Table or partition point-in-time recovery in RMAN

Pre 12c, there was no direct method available to recovery a particular table or a table partition using the existing RMAN physical backups. The table should be recovered either using the existing logical backup that has the required table or restore/recover the complete database on the same or new host, extract the required table and import the it in the source database.

Recovery of one or more tables or partitions through an RMAN becomes a reality in 12c. I must say, it was quite a long pending ask from the Oracle community and finally it’s there in 12c. With the new RMAN 'RECOVER TABLE' command in 12c, DBA can perform point-in-time recovery of one or more tables or table partitions. The supported options are, UNTIL TIME, UNTIL SCN and UNTIL SEQUENCE RMAN uses the similar technique of tablespace recovery to perform a table or table partition recovery operations. During the course of recovery, RMAN does the following sequence of action:

  • Configure a temporary auxiliary database with the SYSTEM level tablespace and with the required tablespace on the local database server
  • Restore and then recover the tablespace that holds the table or table partition in the context
  • Export the table/partition that is being recovered using the data pumps
  • Optionally import and rename the table/partition in the source database
  • Destroys the auxiliary database upon completion

 The following example covers the instructions to recovery EMP table exist in the SCOTT schema that is either dropped or truncated unintentionally at around 2PM on 9th July 2013:
 

RMAN> RECOVER TABLE SCOTT.EMP

UNTIL TIME "TO_CHAR('07/09/2013 04:00:00','mm/dd/yyyy hh24:mi:ss')"

AUXILIARY DESTINATION '/u01/tmp/recemp'

DATAPUMP DESTINATION '/u01/DPUMP'

DUMP FILE 'recoveremp.dmp'

NOTABLEIMPORT

REMAP TABLE 'SCOTT'.'EMP':'EMP2';
 

The preceding example does the following:

  • Recover SCOTT.EMP table until Jul-09-2013 4PM
  • Auxiliary database data files will be placed under /u01/tmp/recemp location
  • The dump file recoveryemp.dmp will be stored in /u01/DPUMP location
  • The NOTABLEIMPORT will prevent importing the into the source database
  • REMAP TABLE provides an option to rename the table while importing

In order to perform point-in-time recovery for a table or partition, the following criteria must be met:

  • The target database must be in READ WRITE mode and the archive log mode must be turned on
  • You must have previous RMAN backups covering the tables/partition in the context
  • The COMPATIBLE database initialization parameter must be 11.1.0 or higher

Table or partition level recovery is a very useful option in the event of when the table/partition is dropped, truncated unintentionally or corrupted.

SQL interface support

Unlike the previous releases which needs SQL prefix to execute SQL statements through an RMAN, in 12c, you can execute most of the SQL statements and PL/SQL procedures straight in RMAN without mentioning the keyword SQL. For example, through RMAN, you can DESCRIBE command to list table structure, can add a datafile to an existing tablespace etc.

The following examples demonstrate adding a datafile to an existing tablespace, how to list table structure and how to use SELECT statement :

RMAN> ALTER TABLESPACE data ADD DATAFILE size 100;

RMAN> DESCE dual;

RMAN> SELECT sysdate FROM dual;
 

This feature gives the flexibility to execute the SQL statements freely in an RMAN prompt.

Backup/recovery improvements for VLDB

Multi-sectional backup functionality was introduced in 11g to break up a large sized data file into multiple sections with the specified size to improve backup and restore operation of large size data files. The functionality extended further in 12c to supports image copies and incremental backups along with the full database backupsets. The added functionality requires COMPATIBLE database initialization parameter to be set to 12.0 or higher.

The below example explains the syntax to use SECTION SIZE clause at various levels:

        RMAN> backup tablespace data SECTION SIZE 1g;
       RMAN> backup as copy SECTION SIZE 1g database;
       RMAN> backup increment level 1 SECTION SIZE 500M datafile 10;
 

You can query the MULTI_SECTION column in V$BACKUP_SET dynamic view to determine whether the feature is in use or not.

Restore/Recover data files over the network

The new RMAN FROM SERVICE clause in 12c let you copy the data files between primary and standby databases connecting through the network. This feature provides the flexibility to restore full database, specific data fileSleep, controlfile, spfile etc from and to primary and standby databases. This feature will be very useful to fill-in the huge gap between primary and standby databases. In fact, this will simplify the roll-forward procedure required to in-sync the standby with the primary database by taking appropriate actions automatically.

Additionally, you can easily restore and recover a data file from standby to primary in the event primary loss a data file. The following examples simulate the following scenario:

  • How to restore/recover a data file from standby to primary

    RMAN> CONNECT TARGET "rman_user@primary_tns AS SYSBACKUP";
    RMAN> RESTORE DATAFILE 9 FROM SERVICE standby_tns;
  • How to use the roll-forward to in-sync standby with primary

    RMAN> CONNECT TARGET "rman_user@standby_tns AS SYSBACKUP";
    RMAN> RECOVER DATABASE FROM SERVICE primary_tns USING COMPRESSED BACKUP;

In the first example, data file 9 will be restored from standby database to primary database.

In the second example, standby database roll-forward procedure will be kicked off by taking the incremental backup starting from the current SCN of standby on the primary database, these backup sets will be then moved/copied to the standby site over the network and then the backups will be applied automatically to in-sync the standby database.

When data files are copied across the sites through the network, you can also apply the encryption to encrypt the backups, use the SECTION SIZE split a big data files into pieces and also compression.

ACTIVE DUPLICATE DATABASE additions and improvements

The ACTIVE DUPLICATE DATABASE option in 11g clone the source database by coping the full image of the data files to the auxiliary instance location through the network. This procedure doesn’t not required any pre backup existence to clone the database.

However, in Oracle 12c, when ACTIVE DUPLICATE DATABASE is initiated, instead of sending the full image copy of the data files, RMAN first takes the backup of data files into backupsets, and these backups are transmit to the auxiliary location and will be restored/recovered subsequently. This functionality will surely reduce the overall impact on the source instance and also help fastening the cloning procedure. You have the flexibility to use backup compression, encryption option with this feature.

         RMAN> DUPLICATE TARGET DATABASE to mydb
                 FROM ACTIVE DATABASE
                 SECTION SIZE 500M
                 USING COMPRESSED BACKUPSETS;
 

In the previous releases, the cloned database will be opened automatically upon the process completion. The additional NOOPEN clause in 12c, will prevent opening database immediately after the cloning process completion. Therefore, the database will remain in MOUNT state after the procedure and you will have to manually open the database.

This will be a good option to use in the following scenario:

  • When the cloned database required additional settings, like, flash back or incremental backup settings.
  • To avoid any startup problems
  • To adjust the initialization parameters
  • If this database is used for upgrade purpose, you can just start the db in UPGRADE mode to proceed with the upgrade.

Here is the example how to use NOOPEN clause:

                 RMAN> DUPLICATE TARGET DATABASE to mydb
                          FROM ACTIVE DATABASE
                          SECTION SIZE 500M
                          USING COMPRESSED BACKUPSETS
                          NOOPEN;

Written by Syed Jaffar Hussain

4793 2 /
Follow / 7.20.2013 at 9:39am

Wonderful article for Oracle database ...............

Related links vox.ulule.com/a-utility-to-repairrecover-not-responding-oracle-d-5960

Follow / 5.13.2014 at 7:10am

Great and helpful articles for administrator or users who always deal with Oracle database. Here is another helpful blog - oracle.filerepairtool.net/.../tips-recover-corrupted-oracle-database-file,hope this may help them too.