RMAN Pluggable Database Backup and Recovery in a Multitenant Environment (Doc ID 1521005.1) To BottomTo Bottom

Document Purpose Scope Details References APPLIES TO: Oracle Database Cloud Schema Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Oracle Database Exadata Cloud Machine - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Backup Service - Version N/A and later Information in this document applies to any platform. PURPOSE An overview of how RMAN can be used to backup and recover Pluggable Databases - a new feature in Oracle Database 12c. SCOPE DBAs supporting large databases with backups made via RMAN who are looking to implement a Container Database with one or more Pluggable Databases. Knowledge of RMAN and its architecture is required. DETAILS What is a Container Database? A 12c database is either a non-Container Database or a Container Database - from here on referred to as non-CDB or CDB respectively. All databases created prior to 12c are non-CDBs. A CDB is an Oracle database that includes zero, one, or many customer-created Containers or Pluggable Databases referred to as PDB. The CDB has: one ROOT container (CDB$ROOT) containing SYSTEM, SYSAUX, UNDO, and TEMP tablespaces, Controlfiles and Redologs one SEED container (PDB$SEED) containing SYSTEM, SYSAUX, TEMP, EXAMPLE tablespaces, used as a template to create new PDBs What is a Pluggable Database? A pluggable Database (PDB) is a user-created container holding the data and code for a specific application eg HR, Payroll etc. A PDB: has SYSTEM, SYSAUX, TEMP tablespaces contains any number of other user created tablespaces writes to the container UNDO tablespace, controlfiles and redologs Undo and redo is annotated with details of the PDB that they belong to. RMAN Pluggable Database Backup The RMAN user must have either SYSDBA or the new SYSBACKUP priviledge. RMAN can be run from ROOT container: rman target sys/@t12ccdb rman target / or from the PDB: rman target sys/@t12cpdb1 When connected to a PDB, all commands pertain to that PDB only. When connected to ROOT, commands pertain to any file in the CDB unless qualified by the PDB name. RMAN command REPORT SCHEMA can be used to identify the files in a Container Database. This example shows a CDB (T12cCDB) with one PDB (T12cPDB1): % rman target sys/@t12ccdb RMAN> report schema; using target database control file instead of recovery catalog Report of database schema for database with db_unique_name T12CCDB ** (filenames have been edited for clarity) List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 960 SYSTEM *** .../oradata/T12CCDB/datafile/o1_mf_system_8008cm5s_.dbf 3 660 SYSAUX *** .../oradata/T12CCDB/datafile/o1_mf_sysaux_80089voz_.dbf 4 50 UNDOTBS1 *** .../oradata/T12CCDB/datafile/o1_mf_undotbs1_8gtp7g6l_.dbf 5 250 PDB$SEED:SYSTEM *** .../oradata/T12CCDB/C4B70772D4DF1DF8E0437108DC0A7D20/datafile/o1_mf_system_8008jc7k_.dbf 6 5 USERS *** .../oradata/T12CCDB/datafile/o1_mf_users_8008fnov_.dbf 7 490 PDB$SEED:SYSAUX *** .../oradata/T12CCDB/C4B70772D4DF1DF8E0437108DC0A7D20/datafile/o1_mf_sysaux_8008jc8m_.dbf 8 250 T12CPDB1:SYSTEM *** .../oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_system_8008r3wh_.dbf 9 510 T12CPDB1:SYSAUX *** .../oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_sysaux_8008r3vl_.dbf 10 5 T12CPDB1:USERS *** .../oradata/T12CCDB/datafile/o1_mf_users_8gtp7ghf_.dbf 20 100 T12CPDB1:RECTBL *** .../oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_rectbl_8hfcv26r_.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 530 TEMP 32767 .../oradata/T12CCDB/datafile/o1_mf_temp_8bz0jcxg_.tmp 2 20 PDB$SEED:TEMP 32767 .../oradata/T12CCDB/C40F9B49FC9C19E0E0430BAAE80AFF01/datafile/o1_mf_temp_8bz0jfkj_.tmp 3 20 T12CPDB1:TEMP 32767 .../oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_temp_8bz0jh7x_.tmp REPORT SCHEMA command is currently the only command that makes it easy to determine the name of the PDB that a file belongs to. If connected to PDB, only the PDB datafiles are listed: % rman target sys/@t12cpdb1 RMAN> report schema; List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 8 250 T12CPDB1:SYSTEM *** .../oradata/T12CCDB/datafile/o1_mf_system_8hloc72d_.dbf 9 510 T12CPDB1:SYSAUX *** .../oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_sysaux_8008r3vl_.dbf 10 5 T12CPDB1:USERS *** .../oradata/T12CCDB/datafile/o1_mf_users_8hlowbh2_.dbf 20 100 T12CPDB1:RECTBL *** .../oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_rectbl_8hfcv26r_.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 3 20 T12CPDB1:TEMP 32767 .../oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_temp_8bz0jh7x_.tmp 1. Complete CDB backup Backup CDB$ROOT, PDB$SEED and ALL PDBS: % rman target sys/@t12ccdb RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT; RMAN> LIST BACKUP OF DATABASE; List of Backup Sets =================== ... ------- ---- -- ---------- ----------- ------------ --------------- 82 Full 2.46G DISK 00:01:01 17-JAN-13 BP Key: 83 Status: AVAILABLE Compressed: NO Tag: TAG20130117T114547 Piece Name: .../fast_recovery_area/T12CCDB/backupset/2013_01_17/o1_mf_nnndf_TAG20130117T114547_8hhs3cgs_.bkp List of Datafiles in backup set 82 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 2139245 17-JAN-13 .../oradata/T12CCDB/datafile/o1_mf_system_8008cm5s_.dbf 3 Full 2139245 17-JAN-13 .../oradata/T12CCDB/datafile/o1_mf_sysaux_80089voz_.dbf 4 Full 2139245 17-JAN-13 .../oradata/T12CCDB/datafile/o1_mf_undotbs1_8gtp7g6l_.dbf 5 Full 1621614 13-JUL-12 .../oradata/T12CCDB/C4B70772D4DF1DF8E0437108DC0A7D20/datafile/o1_mf_system_8008jc7k_.dbf 6 Full 2139245 17-JAN-13 .../oradata/T12CCDB/datafile/o1_mf_users_8008fnov_.dbf 7 Full 1621614 13-JUL-12 .../oradata/T12CCDB/C4B70772D4DF1DF8E0437108DC0A7D20/datafile/o1_mf_sysaux_8008jc8m_.dbf 8 Full 2139245 17-JAN-13 .../oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_system_8008r3wh_.dbf 9 Full 2139245 17-JAN-13 .../oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_sysaux_8008r3vl_.dbf 10 Full 2139245 17-JAN-13 .../oradata/T12CCDB/datafile/o1_mf_users_8gtp7ghf_.dbf 20 Full 2139245 17-JAN-13 .../oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_rectbl_8hfcv26r_.dbf 2. Partial CDB backup Backup only PDB T12CPDB1: %rman target sys/@t12ccdb RMAN> BACKUP PLUGGABLE DATABASE T12CPDB1 TAG 'T12CPDB1'; RMAN> LIST BACKUP; ... ------- ---- -- ---------- ----------- ------------ --------------- 85 Full 590.52M DISK 00:00:14 17-JAN-13 BP Key: 86 Status: AVAILABLE Compressed: NO Tag: T12CPDB1 Piece Name: .../fast_recovery_area/T12CCDB/backupset/2013_01_17/o1_mf_nnndf_T12CPDB1_8hhswy1c_.bkp List of Datafiles in backup set 85 Container ID: 3, PDB Name: T12CPDB1 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 8 Full 2139742 17-JAN-13 .../oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_system_8008r3wh_.dbf 9 Full 2139742 17-JAN-13 .../oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_sysaux_8008r3vl_.dbf 10 Full 2139742 17-JAN-13 .../oradata/T12CCDB/datafile/o1_mf_users_8gtp7ghf_.dbf 20 Full 2139742 17-JAN-13 .../oradata/T12CCDB/C4B71645EF062616E0437108DC0A91E4/datafile/o1_mf_rectbl_8hfcv26r_.dbf You do not need to specify a TAG, as in example above to identify backups of Pluggable Database, TAG is just used in this sample. RMAN LIST BACKUP command shows you the information to which Database, or Pluggable Database a rman backup belongs to. However as FRA shows GUID in its PATH name, so in case needed you may use alternate following sample query to identify to which PDB a Backup belongs to: In this sample: The GUID for T12CPDB1 is C4B71645EF062616E0437108DC0A91E4. From the CDB: SQL> SET LINES 150 SQL> SELECT CON_ID, DBID, CON_UID, GUID, NAME FROM v$pdbs; CON_ID DBID CON_UID GUID NAME ---------- ---------- ---------- -------------------------------- ------------------------------ 2 4031181962 4031181962 C40F9B49FC9C19E0E0430BAAE80AFF01 PDB$SEED 3 575001283 575001283 C4B71645EF062616E0437108DC0A91E4 T12CPDB1 3. Partial PDB backup 3a. Backup system and sysaux tablespace from PDB T12CPDB1 whilst connected to ROOT: % rman target sys/@t12ccdb RMAN>BACKUP TABLESPACE T12CPDB1:SYSTEM, T12CPDB1:SYSAUX; 3b. Backup system tablespace from pluggable database T12CPDB1 and the SYSAUX tablespace from ROOT CDB: When connected to ROOT if you do not specify the PDB prefix, the ROOT container is assumed. % rman target sys/@t12ccdb RMAN>BACKUP TABLESPACE T12CPDB1:SYSTEM, SYSAUX; 3c. File# however is unique so you can backup datafiles when connected to ROOT without having to specify the container name if you use file#: To backup datafile 3 from CDB$ROOT and datafile 20 from PDB T12CPDB1 % rman target sys/@t12ccdb RMAN> BACKUP DATAFILE 3,20; RMAN Pluggable Database Recovery 1. Loss of system datafile from PDB T12cPDB1 The Container Database and all other PDBs are usually unaffected, only PDB T12CPDB1 is unavailable. Restore must be done from ROOT. However loss of a SYSTEM datafile of PDB is as critical as loss of a SYSTEM datafile of CDB/non-CDB, i.e. this will may lead to unpredictable behaviour, mostly crash the entire CDB (i.e. all PDBs will be unavailable). In this case, you need to restore/recover this SYSTEM datafile of PDB in MOUNT state of CDB. This behaviour will be enhanced in future releases, i.e., loss of SYSTEM datafile of PDB will NOT crash the CDB or other PDBs. % rman target / RMAN> RESTORE DATAFILE 8; RMAN> RECOVER DATAFILE 8; RMAN> ALTER PLUGGABLE DATABASE T12CPDB1 OPEN; 2. Loss of any non-system datafile from PDB eg datafile 10 USERS tablespace Depending on the circumstances, the file may be already offlined if not - offline it: % rman sys/@t12cpdb1 RMAN> ALTER DATABASE DATAFILE 10 OFFLINE; RMAN> RESTORE DATAFILE 10; RMAN> RECOVER DATAFILE 10; RMAN> ALTER DATABASE DATAFILE 10 ONLINE; 3. Loss of a complete tablespace from PDB PDB T12CPDB1 remains open. % rman target sys/oracle@t12ccpdb1 RMAN> ALTER TABLESPACE USERS OFFLINE; RMAN> RESTORE TABLESPACE USERS; RMAN> RECOVER TABLESPACE USERS; RMAN> ALTER TABLESPACE USERS ONLINE; 4: Loss of entire PDB % rman target sys/@t12ccdb RMAN> RESTORE PLUGGABLE DATABASE T12CPDB1; RMAN> RECOVER PLUGGABLE DATABASE T12CPDB1; RMAN> ALTER PLUGGABLE DATABASE T12cPDB1 open; Note: # LOSS OF PLUGGABLE DATABASE is not the same as if Pluggable DAtabase is DROPPED - LOSS OF PLUGGABLE DATABASE: This is for example if pluggable database/datafiles are accidently deleted, corrupted etc... but the repository/metadta are still known and existing. In this case the Metadata for the PDB do still exist, so REstore form backup is possible - IF DROP PLUGGABLE DATABASE is done This will drop the PDB and remove the metadata from repository, so restore ( including PDB - PITR to before the dropped time ) fails like . RMAN-06813: could not translate pluggable database PDB1 Also , If we do backup of single pluggable database backup, this will not be useful to restore single pluggable database in the different server OR to perform Point in time recovery. ******** We must be having backup of root & pluggable database **************