Oracle Database 12c – RMAN New Features: Part1

Oracle Database 12c has new enhancements and additions in Recovery Manager (RMAN).  The Recovery Manager continues to enhance and extend the reliability, efficiency, and availability of Oracle Database Backup and Recovery. In this article series, I will be explaining the new features and how it will help Oracle community. In this article I will cover: SQL Interface Improvements SYSBACKUP Privilege

Oracle Database 12c has new enhancements and additions in Recovery Manager (RMAN).  The Recovery Manager continues to enhance and extend the reliability, efficiency, and availability of Oracle Database Backup and Recovery. In this article series, I will be explaining the new features and how it will help Oracle community.

In this article I will cover:

  • SQL Interface Improvements
  • SYSBACKUP Privilege
  • Support for multitenant container and pluggable databases
  • DUPLICATE enhancements

SQL Interface Improvements

In Oracle 12c, you can run SQL commands in RMAN without preceding the command with the SQL keyword.  You also no longer need to enclose the SQL command in quotes.

The RMAN DESCRIBE provides the same functionality of SQL*Plus DESCRIBE:

You can run SQL statements from RMAN command prompt:

You can run DDL/DML Commands from RMAN Command prompt, but note that in order to insert you need to use:

The user can SHUTDOWN/STARTUP the database and also can use ALTER commands:

With this new SQL Interface improvement’s, users don’t need to switch between RMAN and SQL* Plus prompts during Backup & Recovery, administration…etc.

SYSBACKUP Privilege

Prior to 12c, users needed SYSDBA privilege to backup the database. The new SYSBACKUP privilege allows the user the permissions to perform only backup operations.

The SYSBACKUP privilege allows the DBA to perform RMAN backup commands without additional privileges. Using this new role in 12c, you can segregate Administration and Backup operations.

With RMAN you have same authentication options that are available with SQL*Plus, which are Operating system authentication and password file authentication.

To connect to RMAN using Operating system Authentication Authentication with the SYSBACKUP Privilege use:
$ rman target ' "/ as sysbackup" '

Authentication with the SYSDBA Privilege use:
$ rman target ' "/ as sysdba" '

You can also implicitly connect using below command
$ rman target /

To Connect to RMAN using Password file Authentication Authentication with the SYSBACKUP Privilege use:
$ rman target1 ‘ “bkpadm@DB1 as sysbackup” ‘

Where bkpadm is the user and should have SYSDBA privilege.

Authentication with the SYSDBA Privilege
$ rman target ‘ “sysadm@DB1 as sysdba” ‘

You can implicitly connect using below command. Where sysadm is the user and should have SYSDBA privilege.
$ rman target sysadm@DB1

Note that SYSBACKUP does not include data access privilege, such as SELECT ANY TABLE. When you don’t specify the role explicitly then the default used is AS SYSDBA.

Support for multitenant container and pluggable databases

The multitenant container database (CDB) and pluggable databases (PDB) are introduced in Oracle 12c, and RMAN provides full support for backup and recovery. Using RMAN you can back up an entire container database or individual pluggable databases and also can perform point-in-time recovery. But it is recommended that you turn on control file auto backup. Otherwise point-in-time recovery for pluggable databases may not work efficiently when you need to undo data file additions or deletions.

The multitenant architecture manages many databases as one and retains the isolation, resource control of each database. This will help to manage both infrastructure and human resources effectively.

containerdatabase

Backing up a container database is similar to backing up a non-container database. When you back up a container database, RMAN backs up the root, pluggable databases in the container, and archive logs.  When you need to restore you can choose the whole container, one or more pluggable databases or the root only.

Backup the CDB, PDB, and root

You should have SYSBACKUP or SYSDBA privilege to backup any of the databases.

You can backup the Container Database (CDB) as same as non-container database using below command:
RMAN> BACKUP DATABASE plus ARCHIVELOG;

You can backup the Pluggable Database (PDB) using below command:
RMAN> BACKUP PLUGGABLE DATABASE PDB1, PDB2;

Or connect to pluggable Database in RMAN :
% rman target sys@PDB1
RMAN> BACKUP DATABASE;

You can backup the root using below command:
RMAN> BACKUP DATABASE ROOT;

Complete recovery of CDB, PDB and root

You should have SYSBACKUP or SYSDBA privilege to restore any of the databases.
Restoring Container Database (CDB) is similar to non-container database.
You can restore the whole CDB using below script:

Note that restoring CDB database will restore all the pluggable databases.
You can restore only ROOT Database using below script:

You can restore Pluggable Databases in two ways. Either you can restore from root container and connect directly to PDB to restore.

Use below script to restore from root. Using this approach you can able to restore and recover multiple PDB’s with a single command.

Use below script to connect PDB, restore and recover the database. Using this approach you will be able to restore and recover only one PDB.

The steps for performing a point-in-time recovery of the CDB or PDB are the same as a normal database. But note that when you perform Point-in-time recovery on the CDB, it will effect on all the PDBs as well.

When you perform point-in-time recovery on a PDB, it will affect that single database.
The command to perform a point-in-time recovery is:

You can refer below URL for point-in-time recovery using RMAN: http://www.oracleracexpert.com/2009/11/how-to-restore-database-to-point-in.html

Below are the few examples to ALTER PLUGGABLE DATABASE.

  • Use this command to open all PDBs in one command:
    ALTER PLUGGABLE DATABASE ALL OPEN;
  • Use this command to open all PDBs except PDB3:
    ALTER PLUGGABLE DATABASE ALL EXCEPT PDB3 OPEN;
  • Use this command to open PDB4,PDB5 in read only mode:
    ALTER PLUGGABLE DATABASE PDB4, PDB5 OPEN READ ONLY;
  • Use below command to shut down all PDBs in single command:
    ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

Backup of Archived redo logs

You can back up archive logs when they connect to root as a common user with SYSDBA or SYSBACKUP privilege, but you cannot back up or delete archive logs when you connect to PDB as a local user with SYSDBA or SYSBACKUP privilege.

You are only able to switch the archived logs when you connect to the root of a CDB, but you cannot switch archived redo logs when connected to a PDB.

If you have more than one archive log destination, when you use RMAN to backup the archive redo logs it backs up only one copy of the archived redo logs. RMAN does not include more than one copy because multiple destinations will have same log sequence number.

You can use any of the below commands to backup the archived redo logs
The command below backs up the database and all the archived redo logs:
RMAN > BACKUP DATABASE PLUS ARCHIVELOG;

The command below only backs up one copy of the sequence number for all archived redo logs.
RMAN> BACKUP ARCHIVELOG ALL;

DUPLICATE enhancements:

When you duplicate a database using RMAN DUPLICATE, the database is created and opened with RESETLOGS mode. With Oracle database 12c, you can specify that the database must not be opened with “NOOPEN” clause.

This NOOPEN clause useful under following situations:

  • If you need to make changes to initialization parameters such as block change tracking, flashback database settings
  • Opening the database conflict with other source database
  • If you plan to create database for upgrade and want to open in upgrade mode

The command below creates duplicate database, but it will not open.
RMAN> DUPLICATE TARGET DATABASE TO DB1
FROM ACTIVE DATABASE NOOPEN;

Conclusion

Oracle Database 12c offers new enhancements and additions in Recovery Manager (RMAN).  Take the advantage of new features for efficient backup & recovery.