Introduction:- In this article, we are going to have look on PDBs backup. Normally we have learnt about a different kind of backup available in Oracle. In cloud computing, we have backups in PDBs. In RMAN we have a script to know about the backup history. Now we can get the information of PDB(backup). n large scale industries have multiple kinds of databases are available sometimes we get confused about the backup. To solve this issue we have a new feature in 12c to know about PDBs backup’s information. Let’s have look on ways to know about PDBs backup. I hope this information is valuable for you.
In this case the container database, named CDB001, contains three pluggable databases named PDB001, PDB002 and PDB003: it is also possible to easily identify the seed container used as template when you want to create new PDBs.
[oracle@localhost ~]$ rman target / Recovery Manager: Release 12.1.0.1.0 - Production on Thu Feb 20 16:37:50 2014 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB001 (DBID=4134986109) RMAN> report schema; using target database control file instead of recovery catalog Report of database schema for database with db_unique_name CDB001 List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 780 SYSTEM *** /app/oracle/oradata/CDB001/system01.dbf 2 260 PDB$SEED:SYSTEM *** /app/oracle/oradata/CDB001/pdbseed/system01.dbf 3 800 SYSAUX *** /app/oracle/oradata/CDB001/sysaux01.dbf 4 625 PDB$SEED:SYSAUX *** /app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf 5 310 UNDOTBS1 *** /app/oracle/oradata/CDB001/undotbs01.dbf 6 5 USERS *** /app/oracle/oradata/CDB001/users01.dbf 7 260 PDB001:SYSTEM *** /app/oracle/oradata/CDB001/PDB001/system01.dbf 8 635 PDB001:SYSAUX *** /app/oracle/oradata/CDB001/PDB001/sysaux01.dbf 9 5 PDB001:USERS *** /app/oracle/oradata/CDB001/PDB001/PDB001_users01.dbf 10 260 PDB002:SYSTEM *** /app/oracle/oradata/CDB001/PDB002/system01.dbf 11 635 PDB002:SYSAUX *** /app/oracle/oradata/CDB001/PDB002/sysaux01.dbf 12 5 PDB002:USERS *** /app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf 19 260 PDB003:SYSTEM *** /app/oracle/oradata/CDB001/PDB003/system01.dbf 20 635 PDB003:SYSAUX *** /app/oracle/oradata/CDB001/PDB003/sysaux01.dbf 21 5 PDB003:USERS *** /app/oracle/oradata/CDB001/PDB003/PDB001_users01.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 73 TEMP 32767 /app/oracle/oradata/CDB001/temp01.dbf 2 61 PDB$SEED:TEMP 32767 /app/oracle/oradata/CDB001/pdbseed/temp01.dbf 3 20 PDB001:TEMP 32767 /app/oracle/oradata/CDB001/PDB001/temp01.dbf 4 20 PDB002:TEMP 32767 /app/oracle/oradata/CDB001/PDB002/temp01.dbf 5 20 PDB003:TEMP 32767 /app/oracle/oradata/CDB001/PDB003/temp01.dbf
After you successfully connect to the container database with RMAN it is not sufficient to have a look at the third line to determine if you are using the container database or a pluggable one.
That line simply specifies you are connected to a target database and when connecting to a pluggable database it could be misleading.
Have a look at the following attempts to connect first to the root container and then directly to the PDB001 pluggable database.
Where and what are the differences if you exclude the time settings ?
[oracle@localhost admin]$ rman target / Recovery Manager: Release 12.1.0.1.0 - Production on Thu Feb 17 17:18:05 2014 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB001 (DBID=4134986109) [oracle@localhost admin]$ rman target sys/oracle@PDB001 Recovery Manager: Release 12.1.0.1.0 - Production on Thu Feb 17 17:19:24 2014 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB001 (DBID=4134986109)
When connected to a pluggable database using the report schema command is possible to deduce the PDB name looking at its datafiles and locations: indeed in this case only the datafiles associated with the connected PDB are listed in the output of the report schema command.
RMAN> report schema; using target database control file instead of recovery catalog Report of database schema for database with db_unique_name CDB001 List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 7 260 SYSTEM *** /app/oracle/oradata/CDB001/PDB001/system01.dbf 8 635 SYSAUX *** /app/oracle/oradata/CDB001/PDB001/sysaux01.dbf 9 5 USERS *** /app/oracle/oradata/CDB001/PDB001/PDB001_users01.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 3 20 TEMP 32767 /app/oracle/oradata/CDB001/PDB001/temp01.dbf
Quit the previous conection to the pluggable database and connect again to the ROOT container and have a look at the list backup command.
List backup command is not so useful when you still don’t have any backups available.
RMAN> list backup; specification does not match any backup in the repository
So let’s get first a full database backup.
When you are connected to the ROOT container any commands you send using RMAN are related to the ROOT container; of course you can, while connected to the ROOT container, execute command on the pluggable database but you have to specify the pluggable database name and additional syntax.
RMAN> backup database; Starting backup at 20-FEB-14 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=81 device type=DISK channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00003 name=/app/oracle/oradata/CDB001/sysaux01.dbf input datafile file number=00001 name=/app/oracle/oradata/CDB001/system01.dbf input datafile file number=00005 name=/app/oracle/oradata/CDB001/undotbs01.dbf input datafile file number=00006 name=/app/oracle/oradata/CDB001/users01.dbf channel ORA_DISK_1: starting piece 1 at 20-FEB-14 channel ORA_DISK_1: finished piece 1 at 20-FEB-14 piece handle=/app/oracle/fast_recovery_area/CDB001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8d3v9_.bkp tag=TAG20140220T163840 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:03:16 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00008 name=/app/oracle/oradata/CDB001/PDB001/sysaux01.dbf input datafile file number=00007 name=/app/oracle/oradata/CDB001/PDB001/system01.dbf input datafile file number=00009 name=/app/oracle/oradata/CDB001/PDB001/PDB001_users01.dbf channel ORA_DISK_1: starting piece 1 at 20-FEB-14 channel ORA_DISK_1: finished piece 1 at 20-FEB-14 piece handle=/app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8l78o_.bkp tag=TAG20140220T163840 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00011 name=/app/oracle/oradata/CDB001/PDB002/sysaux01.dbf input datafile file number=00010 name=/app/oracle/oradata/CDB001/PDB002/system01.dbf input datafile file number=00012 name=/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf channel ORA_DISK_1: starting piece 1 at 20-FEB-14 channel ORA_DISK_1: finished piece 1 at 20-FEB-14 piece handle=/app/oracle/fast_recovery_area/CDB001/E1F329ECE0F411E6E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8n97w_.bkp tag=TAG20140220T163840 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00020 name=/app/oracle/oradata/CDB001/PDB003/sysaux01.dbf input datafile file number=00019 name=/app/oracle/oradata/CDB001/PDB003/system01.dbf input datafile file number=00021 name=/app/oracle/oradata/CDB001/PDB003/PDB001_users01.dbf channel ORA_DISK_1: starting piece 1 at 20-FEB-14 channel ORA_DISK_1: finished piece 1 at 20-FEB-14 piece handle=/app/oracle/fast_recovery_area/CDB001/E2B9BE56B8B936CEE045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8q9jq_.bkp tag=TAG20140220T163840 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00004 name=/app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf input datafile file number=00002 name=/app/oracle/oradata/CDB001/pdbseed/system01.dbf channel ORA_DISK_1: starting piece 1 at 20-FEB-14 channel ORA_DISK_1: finished piece 1 at 20-FEB-14 piece handle=/app/oracle/fast_recovery_area/CDB001/E19363E52C005C9AE045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8so9h_.bkp tag=TAG20140220T163840 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55 Finished backup at 20-FEB-14 Starting Control File and SPFILE Autobackup at 20-FEB-14 piece handle=/app/oracle/fast_recovery_area/CDB001/autobackup/2014_02_20/o1_mf_s_840041213_9jd8vj0m_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 20-FEB-14 With the list command you can display information about the available backups. Looking at that information you can be able to know that the o1_mf_nnndf_TAG20140220T163840_9jd8l78o_.bkp backup is related to a full backup of the pluggable database named PDB001, the o1_mf_nnndf_TAG20140220T163840_9jd8n97w_.bkp backup is related to PDB002 and o1_mf_nnndf_TAG20140220T163840_9jd8q9jq_.bkp to PDB003. RMAN> list backup; using target database control file instead of recovery catalog List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 38 Full 386.84M DISK 00:03:08 20-FEB-14 BP Key: 38 Status: AVAILABLE Compressed: YES Tag: TAG20140220T163840 Piece Name: /app/oracle/fast_recovery_area/CDB001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8d3v9_.bkp List of Datafiles in backup set 38 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 3146659 20-FEB-14 /app/oracle/oradata/CDB001/system01.dbf 3 Full 3146659 20-FEB-14 /app/oracle/oradata/CDB001/sysaux01.dbf 5 Full 3146659 20-FEB-14 /app/oracle/oradata/CDB001/undotbs01.dbf 6 Full 3146659 20-FEB-14 /app/oracle/oradata/CDB001/users01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 39 Full 210.43M DISK 00:01:01 20-FEB-14 BP Key: 39 Status: AVAILABLE Compressed: YES Tag: TAG20140220T163840 Piece Name: /app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8l78o_.bkp List of Datafiles in backup set 39 Container ID: 3, PDB Name: PDB001 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 7 Full 3147302 20-FEB-14 /app/oracle/oradata/CDB001/PDB001/system01.dbf 8 Full 3147302 20-FEB-14 /app/oracle/oradata/CDB001/PDB001/sysaux01.dbf 9 Full 3147302 20-FEB-14 /app/oracle/oradata/CDB001/PDB001/PDB001_users01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 40 Full 210.28M DISK 00:01:31 20-FEB-14 BP Key: 40 Status: AVAILABLE Compressed: YES Tag: TAG20140220T163840 Piece Name: /app/oracle/fast_recovery_area/CDB001/E1F329ECE0F411E6E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8n97w_.bkp List of Datafiles in backup set 40 Container ID: 4, PDB Name: PDB002 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 10 Full 3120954 14-AUG-13 /app/oracle/oradata/CDB001/PDB002/system01.dbf 11 Full 3120954 14-AUG-13 /app/oracle/oradata/CDB001/PDB002/sysaux01.dbf 12 Full 3120954 14-AUG-13 /app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 41 Full 210.24M DISK 00:01:14 20-FEB-14 BP Key: 41 Status: AVAILABLE Compressed: YES Tag: TAG20140220T163840 Piece Name: /app/oracle/fast_recovery_area/CDB001/E2B9BE56B8B936CEE045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8q9jq_.bkp List of Datafiles in backup set 41 Container ID: 5, PDB Name: PDB003 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 19 Full 3121273 14-AUG-13 /app/oracle/oradata/CDB001/PDB003/system01.dbf 20 Full 3121273 14-AUG-13 /app/oracle/oradata/CDB001/PDB003/sysaux01.dbf 21 Full 3121273 14-AUG-13 /app/oracle/oradata/CDB001/PDB003/PDB001_users01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 42 Full 207.56M DISK 00:00:53 20-FEB-14 BP Key: 42 Status: AVAILABLE Compressed: YES Tag: TAG20140220T163840 Piece Name: /app/oracle/fast_recovery_area/CDB001/E19363E52C005C9AE045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8so9h_.bkp List of Datafiles in backup set 42 Container ID: 2, PDB Name: PDB$SEED File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 2 Full 1786693 16-JUL-13 /app/oracle/oradata/CDB001/pdbseed/system01.dbf 4 Full 1786693 16-JUL-13 /app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 43 Full 17.20M DISK 00:00:03 20-FEB-14 BP Key: 43 Status: AVAILABLE Compressed: NO Tag: TAG20140220T164653 Piece Name: /app/oracle/fast_recovery_area/CDB001/autobackup/2014_02_20/o1_mf_s_840041213_9jd8vj0m_.bkp SPFILE Included: Modification time: 20-FEB-14 SPFILE db_unique_name: CDB001 Control File Included: Ckp SCN: 3148638 Ckp time: 20-FEB-14
Looking only at the name of the backup set how you can deduce which PDB that backup belongs to ?
If your database is still available you can query the V$BACKUP_FILES view and match the container id with the V$CONTAINERS view for example.
SQL> select pkey, name, guid, fname from v$containers a, v$backup_files b where a.con_id = b.con_id and file_type = 'PIECE' order by pkey; PKEY NAME GUID FNAME ---------- -------- -------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------- 38 CDB$ROOT E19363E52C015C9AE045000000000001 /app/oracle/fast_recovery_area/CDB001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8d3v9_.bkp 39 PDB001 E1F26215682E1142E045000000000001 /app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8l78o_.bkp 40 PDB002 E1F329ECE0F411E6E045000000000001 /app/oracle/fast_recovery_area/CDB001/E1F329ECE0F411E6E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8n97w_.bkp 41 PDB003 E2B9BE56B8B936CEE045000000000001 /app/oracle/fast_recovery_area/CDB001/E2B9BE56B8B936CEE045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8q9jq_.bkp 42 PDB$SEED E19363E52C005C9AE045000000000001 /app/oracle/fast_recovery_area/CDB001/E19363E52C005C9AE045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8so9h_.bkp 43 CDB$ROOT E19363E52C015C9AE045000000000001 /app/oracle/fast_recovery_area/CDB001/autobackup/2014_02_20/o1_mf_s_840041213_9jd8vj0m_.bkp 6 rows selected.
But how can you identify which pluggable database the backup belongs to looking only at the location of the backup set ?
From the file location you can only use the Globally Unique ID (associated with every pluggable databases by the Oracle software) and deduce the name of the pluggable database: if you don’t have access to the database it is quite difficult to remember that association.
For this reason when dealing with container and pluggable databases it is heartily recommended to tag your backups using specific names and easily refer to them when required.
RMAN> backup pluggable database PDB001 tag 'PDB001_FULL_20140303'; Starting backup at 03-MAR-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=38 device type=DISK channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00008 name=/app/oracle/oradata/CDB001/PDB001/sysaux01.dbf input datafile file number=00007 name=/app/oracle/oradata/CDB001/PDB001/system01.dbf input datafile file number=00009 name=/app/oracle/oradata/CDB001/PDB001/PDB001_users01.dbf channel ORA_DISK_1: starting piece 1 at 03-MAR-14 channel ORA_DISK_1: finished piece 1 at 03-MAR-14 piece handle=/app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_03_03/o1_mf_nnndf_PDB001_FULL_20140303_9k94y5t2_.bkp tag=PDB001_FULL_20140303 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06 Finished backup at 03-MAR-14 Starting Control File and SPFILE Autobackup at 03-MAR-14 piece handle=/app/oracle/fast_recovery_area/CDB001/autobackup/2014_03_03/o1_mf_s_841246744_9k950bv6_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 03-MAR-14 RMAN> list backup of pluggable database PDB001; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 39 Full 210.43M DISK 00:01:01 20-FEB-14 BP Key: 39 Status: AVAILABLE Compressed: YES Tag: TAG20140220T163840 Piece Name: /app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8l78o_.bkp List of Datafiles in backup set 39 Container ID: 3, PDB Name: PDB001 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 7 Full 3147302 20-FEB-14 /app/oracle/oradata/CDB001/PDB001/system01.dbf 8 Full 3147302 20-FEB-14 /app/oracle/oradata/CDB001/PDB001/sysaux01.dbf 9 Full 3147302 20-FEB-14 /app/oracle/oradata/CDB001/PDB001/PDB001_users01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 44 Full 210.45M DISK 00:01:02 03-MAR-14 BP Key: 44 Status: AVAILABLE Compressed: YES Tag: PDB001_FULL_20140303 Piece Name: /app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_03_03/o1_mf_nnndf_PDB001_FULL_20140303_9k94y5t2_.bkp List of Datafiles in backup set 44 Container ID: 3, PDB Name: PDB001 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 7 Full 3170400 03-MAR-14 /app/oracle/oradata/CDB001/PDB001/system01.dbf 8 Full 3170400 03-MAR-14 /app/oracle/oradata/CDB001/PDB001/sysaux01.dbf 9 Full 3170400 03-MAR-14 /app/oracle/oradata/CDB001/PDB001/PDB001_users01.dbf
Looking at the location name you can now be able to determine that the o1_mf_nnndf_PDB001_FULL_20140303_9k94y5t2_.bkp backup set is related to PDB001 pluggable database.
