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:
1 2 3 4 5 6 7 8 9 |
RMAN> desc dba_profiles; Name Null? Type ---------------------------- -------- ---------------------------- PROFILE NOT NULL VARCHAR2(128) RESOURCE_NAME NOT NULL VARCHAR2(32) RESOURCE_TYPE VARCHAR2(8) LIMIT VARCHAR2(128) COMMON VARCHAR2(3) |
You can run SQL statements from RMAN command prompt:
1 2 3 4 5 6 7 |
RMAN> select sysdate from dual; SYSDATE --------- 10-SEP-14 RMAN> |
You can run DDL/DML Commands from RMAN Command prompt, but note that in order to insert you need to use:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
RMAN> create table ora_table(col1 number, col2 varchar2(20)); Statement processed RMAN> insert into ora_table values (1,'Test'); Statement processed RMAN> update ora_table set col1=2; Statement processed RMAN> drop table ora_table; Statement processed |
The user can SHUTDOWN/STARTUP the database and also can use ALTER commands:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
RMAN> shutdown immediate database closed database dismounted Oracle instance shut down RMAN> startup mount connected to target database (not started) Oracle instance started database mounted Total System Global Area 1610612736 bytes Fixed Size 2924928 bytes Variable Size 520097408 bytes Database Buffers 1073741824 bytes Redo Buffers 13848576 bytes RMAN> alter database open; Statement processed |
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.
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:
1 2 3 4 5 6 |
RMAN> RUN { STARTUP MOUNT; RESTORE DATABASE; RECOVER DATABASE; ALTER DATABASE OPEN; } |
Note that restoring CDB database will restore all the pluggable databases.
You can restore only ROOT Database using below script:
1 2 3 4 5 6 |
RMAN> RUN { STARTUP MOUNT; RESTORE DATABASE ROOT; RECOVER DATABASE ROOT; ALTER DATABASE OPEN; } |
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.
1 2 3 4 5 |
RMAN > RUN { RESTORE PLUGGABLE DATABASE PDB1, PDB2; RECOVER PLUGGABLE DATABASE PDB1, PDB2; ALTER PLUGGABLE DATABASE PDB1, PDB2 OPEN; } |
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.
1 2 3 4 5 |
$ rman target=bkpadm@PDB1 RMAN> run{ RESTORE DATABASE; RECOVER DATABASE; } |
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:
1 2 3 |
SET UNTIL TIME "TO_DATE(’01-Jan-2014 01:00:00’,’DD-MON-YYYY HH24:MI:SS’)"; SET UNTIL SCN 1999945; # alternatively, specify SCN SET UNTIL SEQUENCE 100; # alternatively, specify log seq |
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.
Load comments