Restore and Recovery Pluggable Database (PDB) after dropping Pluggable Database (PDB) in Multitenant Environment

Oracle Community

Restore and Recovery Pluggable Database (PDB) after dropping Pluggable Database (PDB) in Multitenant Environment

Introduction:
 
Oracle 12c has introduced Multitenant architecture to provide better Resource management, Security, make patching and db upgrade easier. Data dictionary is stored in Container database (CDB) and user data will be stored at Pluggable database (PDB). Backup can be taken either at CDB or PDB level through sysdba or sysbackup privileged user.  Though PDB is considered as a database, PDB is an integral part of CDB.  In case PDB is dropped mistakenly, it has to be restored through Point in time recovery method as we recover other database objects.
 
 
List the Pluggable databases
 
Connect to Container database and list the pluggable databases

 

SQL> show pdbs
 
    CON_ID        CON_NAME                              OPEN MODE     RESTRICTED
    --------         ------------------------------      --------------    ---------------

2                          PDB$SEED                               READ ONLY       NO

 

Create new pluggable database
 
SQL> create pluggable database testpdb admin user test identified by test;
Pluggable database created.
 
Take backup of Container Database (CDB)

Connect to RMAN using sysdba or sysbackup user and take backup of CDB database which includes pluggable database testpdb

 

RMAN> backup database plus archivelog tag 'full db  backup';
 
Starting backup at 17-JUN-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=16 RECID=2 STAMP=882424443
input archived log thread=1 sequence=17 RECID=3 STAMP=882431108
input archived log thread=1 sequence=18 RECID=4 STAMP=882431236
input archived log thread=1 sequence=19 RECID=5 STAMP=882635340
input archived log thread=1 sequence=20 RECID=6 STAMP=882635505
input archived log thread=1 sequence=21 RECID=7 STAMP=882635663
input archived log thread=1 sequence=22 RECID=8 STAMP=882635874
channel ORA_DISK_1: starting piece 1 at 17-JUN-15
channel ORA_DISK_1: finished piece 1 at 17-JUN-15
piece handle=/u01/app/oracle/fast_recovery_area/TESTCDB/backup/testpdb116q9nt32_1_1 tag=FULL DB  BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-JUN-15
 
Starting backup at 17-JUN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/TESTCDB/datafile/o1_mf_system_bfsn1bxv_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/TESTCDB/datafile/o1_mf_sysaux_bfsmz9q0_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/TESTCDB/datafile/o1_mf_undotbs1_bfsn3399_.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/TESTCDB/datafile/o1_mf_users_bfsn325o_.dbf
channel ORA_DISK_1: starting piece 1 at 17-JUN-15
channel ORA_DISK_1: finished piece 1 at 17-JUN-15
piece handle=/u01/app/oracle/fast_recovery_area/TESTCDB/backup/testpdb117q9nt34_1_1 tag=TAG20150617T163756 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/TESTCDB/datafile/o1_mf_sysaux_bfsn3zgm_.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/TESTCDB/datafile/o1_mf_system_bfsn3zgr_.dbf
channel ORA_DISK_1: starting piece 1 at 17-JUN-15
channel ORA_DISK_1: finished piece 1 at 17-JUN-15
piece handle=/u01/app/oracle/fast_recovery_area/TESTCDB/backup/testpdb118q9nt5f_1_1 tag=TAG20150617T163756 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00016 name=/u01/app/oracle/oradata/TESTCDB/18B95CFF8F9F0D55E0530F02000AE32A/datafile/o1_mf_sysaux_br34mork_.dbf
input datafile file number=00015 name=/u01/app/oracle/oradata/TESTCDB/18B95CFF8F9F0D55E0530F02000AE32A/datafile/o1_mf_system_br34monw_.dbf
channel ORA_DISK_1: starting piece 1 at 17-JUN-15
channel ORA_DISK_1: finished piece 1 at 17-JUN-15
piece handle=/u01/app/oracle/fast_recovery_area/TESTCDB/backup/testpdb119q9nt6i_1_1 tag=TAG20150617T163756 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 17-JUN-15
 
Starting backup at 17-JUN-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=23 RECID=9 STAMP=882636021
channel ORA_DISK_1: starting piece 1 at 17-JUN-15
channel ORA_DISK_1: finished piece 1 at 17-JUN-15
piece handle=/u01/app/oracle/fast_recovery_area/TESTCDB/backup/testpdb11aq9nt7m_1_1 tag=FULL DB  BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-JUN-15
 
Starting Control File and SPFILE Autobackup at 17-JUN-15
piece handle=/u01/app/oracle/fast_recovery_area/TESTCDB/autobackup/2015_06_17/o1_mf_s_882636023_br3577rr_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 17-JUN-15
 
List the backup pieces

 

RMAN> list backup;
 
List of Backup Sets
===================
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
37      9.14M      DISK        00:00:01     17-JUN-15
        BP Key: 37   Status: AVAILABLE  Compressed: NO  Tag: FULL DB  BACKUP
        Piece Name: /u01/app/oracle/fast_recovery_area/TESTCDB/backup/testpdb116q9nt32_1_1
 
  List of Archived Logs in backup set 37
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    16      1898172    15-JUN-15 1898233    15-JUN-15
  1    17      1898233    15-JUN-15 1899565    15-JUN-15
  1    18      1899565    15-JUN-15 1899623    15-JUN-15
  1    19      1899623    15-JUN-15 1924925    17-JUN-15
  1    20      1924925    17-JUN-15 1925893    17-JUN-15
  1    21      1925893    17-JUN-15 1925957    17-JUN-15
  1    22      1925957    17-JUN-15 1926132    17-JUN-15
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
38      Full    1.21G      DISK        00:01:10     17-JUN-15
        BP Key: 38   Status: AVAILABLE  Compressed: NO  Tag: TAG20150617T163756
        Piece Name: /u01/app/oracle/fast_recovery_area/TESTCDB/backup/testpdb117q9nt34_1_1
  List of Datafiles in backup set 38
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1926138    17-JUN-15 /u01/app/oracle/oradata/TESTCDB/datafile/o1_mf_system_bfsn1bxv_.dbf
  3       Full 1926138    17-JUN-15 /u01/app/oracle/oradata/TESTCDB/datafile/o1_mf_sysaux_bfsmz9q0_.dbf
  4       Full 1926138    17-JUN-15 /u01/app/oracle/oradata/TESTCDB/datafile/o1_mf_undotbs1_bfsn3399_.dbf
  6       Full 1926138    17-JUN-15 /u01/app/oracle/oradata/TESTCDB/datafile/o1_mf_users_bfsn325o_.dbf
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
39      Full    680.22M    DISK        00:00:28     17-JUN-15
        BP Key: 39   Status: AVAILABLE  Compressed: NO  Tag: TAG20150617T163756
        Piece Name: /u01/app/oracle/fast_recovery_area/TESTCDB/backup/testpdb118q9nt5f_1_1
  List of Datafiles in backup set 39
  Container ID: 2, PDB Name: PDB$SEED
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5       Full 1820908    20-FEB-15 /u01/app/oracle/oradata/TESTCDB/datafile/o1_mf_system_bfsn3zgr_.dbf
  7       Full 1820908    20-FEB-15 /u01/app/oracle/oradata/TESTCDB/datafile/o1_mf_sysaux_bfsn3zgm_.dbf
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
40      Full    680.23M    DISK        00:00:28     17-JUN-15
        BP Key: 40   Status: AVAILABLE  Compressed: NO  Tag: TAG20150617T163756
        Piece Name: /u01/app/oracle/fast_recovery_area/TESTCDB/backup/testpdb119q9nt6i_1_1
  List of Datafiles in backup set 40
  Container ID: 3, PDB Name: TESTPDB
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  15      Full 1925849    17-JUN-15 /u01/app/oracle/oradata/TESTCDB/18B95CFF8F9F0D55E0530F02000AE32A/datafile/o1_mf_system_br34monw_.dbf
  16      Full 1925849    17-JUN-15 /u01/app/oracle/oradata/TESTCDB/18B95CFF8F9F0D55E0530F02000AE32A/datafile/o1_mf_sysaux_br34mork_.dbf
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
41      2.43M      DISK        00:00:00     17-JUN-15
        BP Key: 41   Status: AVAILABLE  Compressed: NO  Tag: FULL DB  BACKUP
        Piece Name: /u01/app/oracle/fast_recovery_area/TESTCDB/backup/testpdb11aq9nt7m_1_1
 
  List of Archived Logs in backup set 41
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    23      1926132    17-JUN-15 1926434    17-JUN-15
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
42      Full    17.20M     DISK        00:00:01     17-JUN-15
        BP Key: 42   Status: AVAILABLE  Compressed: NO  Tag: TAG20150617T164023
        Piece Name: /u01/app/oracle/fast_recovery_area/TESTCDB/autobackup/2015_06_17/o1_mf_s_882636023_br3577rr_.bkp
  SPFILE Included: Modification time: 17-JUN-15
  SPFILE db_unique_name: TESTCDB
  Control File Included: Ckp SCN: 1926443      Ckp time: 17-JUN-15
 
Drop the pluggable database (PDB)
 
As a test case we drop the pluggable database testpdb
 
SQL> drop pluggable database testpdb;
Pluggable database dropped.
 

·     In case if we try to restore PDB as a database, we will get below error

 
RMAN> restore pluggable database testpdb;
 
Starting restore at 17-JUN-15
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/17/2015 16:46:46
RMAN-06813: could not translate pluggable database TESTPDB
 
Restore procedure
 
Pluggable database (PDB) can’t be restored as a database. It needs to be restored as database object. For that auxiliary instance has to be created temporarily where CDB and required PDB will be restored. Later PDB will be unplugged from auxiliary instance and attached to target instance

 

Create a temporary auxiliary instance to restore container database (CDB) and required pluggable database (PDB)
 
Create password file for auxiliary instance
 
[oracle@Server dbs]$ orapwd file=orapwtestcdb1 password=sys entries=5
[oracle@Server dbs]$ ls -lrt orapwtestcdb1
-rw-r-----. 1 oracle oinstall 5120 Jun 17 17:04 orapwtestcdb1
 
Create init file for auxiliary instance

 

*.compatible='12.1.0.0.0'
*.control_files='/u01/app/oracle/oradata/TESTCDB1/controlfile/o1_mf_bfsn3k7o_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_name='testcdb1'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testcdb1XDB)'
*.enable_pluggable_database=true
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
 
Start the auxiliary instance in nomount state

 

[oracle@Server dbs]$ export ORACLE_SID=testcdb1
[oracle@Server dbs]$ sqlplus sys/sy as sysdba
 
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jun 17 17:04:57 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL>  startup nomount pfile=inittestcdb1.ora
ORACLE instance started.
Total System Global Area          242208768 bytes
Fixed Size                                 2849120 bytes
Variable Size                             184551072 bytes
Database Buffers                      50331648 bytes
Redo Buffers                4476928 bytes
 
Connect auxiliary instance through RMAN
 
[oracle@Server dbs]$ rman auxiliary sys/sys
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Jun 17 17:08:45 2015
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
connected to auxiliary database: TESTCDB1 (not mounted)
 
Execute Duplicate command for CDB and particular PDB using backup location
 
RMAN>  Duplicate database to 'testcdb1' noopen backup location '/u01/app/oracle/fast_recovery_area/TESTCDB/backup/';
 
Starting Duplicate Db at 17-JUN-15
contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
 
Oracle instance shut down
 
connected to auxiliary database (not started)
Oracle instance started
 
Total System Global Area          242208768 bytes
Fixed Size                                 2849120 bytes
Variable Size                184551072 bytes
Database Buffers                      50331648 bytes
Redo Buffers                            4476928 bytes
 
contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''/u01/app/oracle/oradata/TESTCDB1/controlfile/o1_mf_bfsn3k7o_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''TESTCDB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''TESTCDB1'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/u01/app/oracle/fast_recovery_area/TESTCDB/backup/o1_mf_s_882636099_br359n4j_.bkp';
   alter clone database mount;
}
executing Memory Script
 
sql statement: alter system set  control_files =   ''/u01/app/oracle/oradata/TESTCDB1/controlfile/o1_mf_bfsn3k7o_.ctl'' comment= ''Set by RMAN'' scope=spfile
 
sql statement: alter system set  db_name =  ''TESTCDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
 
sql statement: alter system set  db_unique_name =  ''TESTCDB1'' comment= ''Modified by RMAN duplicate'' scope=spfile
 
Oracle instance shut down
Oracle instance started
Total System Global Area     242208768 bytes
 
Fixed Size                     2849120 bytes
Variable Size                184551072 bytes
Database Buffers              50331648 bytes
Redo Buffers                   4476928 bytes
 
Starting restore at 17-JUN-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
 
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/TESTCDB1/controlfile/o1_mf_bfsn3k7o_.ctl
Finished restore at 17-JUN-15
 
database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
 
contents of Memory Script:
{
   set until scn  1926434;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   set newname for clone datafile  7 to new;
   set newname for clone datafile  15 to new;
   set newname for clone datafile  16 to new;
   restore
   clone database
   ;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 17-JUN-15
using channel ORA_AUX_DISK_1
 
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TESTCDB/backup/testpdb117q9nt34_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TESTCDB/backup/testpdb117q9nt34_1_1 tag=TAG20150617T163756
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TESTCDB/backup/testpdb118q9nt5f_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TESTCDB/backup/testpdb118q9nt5f_1_1 tag=TAG20150617T163756
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00016 to /u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TESTCDB/backup/testpdb119q9nt6i_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TESTCDB/backup/testpdb119q9nt6i_1_1 tag=TAG20150617T163756
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 17-JUN-15
 
contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
 
datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=882637965 file name=/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_system_br36zdcj_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=882637965 file name=/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_sysaux_br36zdcn_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=882637965 file name=/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_undotbs1_br36zdcq_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=882637965 file name=/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_system_br371fvr_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=882637965 file name=/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_users_br36zdfb_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=14 STAMP=882637966 file name=/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_sysaux_br371frz_.dbf
datafile 15 switched to datafile copy
input datafile copy RECID=15 STAMP=882637966 file name=/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_system_br372tts_.dbf
datafile 16 switched to datafile copy
input datafile copy RECID=16 STAMP=882637966 file name=/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_sysaux_br372ttf_.dbf
 
contents of Memory Script:
{
   set until scn  1926434;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script
 
executing command: SET until clause
 
Starting recover at 17-JUN-15
using channel ORA_AUX_DISK_1
 
starting media recovery
 
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=23
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TESTCDB/backup/testpdb11dq9nta2_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TESTCDB/backup/testpdb11dq9nta2_1_1 tag=TAG20150617T164113
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/TESTCDB1/archivelog/2015_06_17/o1_mf_1_23_br373zdg_.arc thread=1 sequence=23
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/TESTCDB1/archivelog/2015_06_17/o1_mf_1_23_br373zdg_.arc RECID=1 STAMP=882637967
media recovery complete, elapsed time: 00:00:02
Finished recover at 17-JUN-15
Oracle instance started
 
Total System Global Area     242208768 bytes
 
Fixed Size                     2849120 bytes
Variable Size                184551072 bytes
Database Buffers              50331648 bytes
Redo Buffers                   4476928 bytes
 
contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''TESTCDB1'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
}
executing Memory Script
 
sql statement: alter system set  db_name =  ''TESTCDB1'' comment= ''Reset to original value by RMAN'' scope=spfile
 
sql statement: alter system reset  db_unique_name scope=spfile
Oracle instance started
 
Total System Global Area     242208768 bytes
 
Fixed Size                     2849120 bytes
Variable Size                184551072 bytes
Database Buffers              50331648 bytes
Redo Buffers                   4476928 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTCDB1" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1  SIZE 50 M ,
  GROUP   2  SIZE 50 M ,
  GROUP   3  SIZE 50 M
 DATAFILE
  '/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_system_br36zdcj_.dbf',
  '/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_system_br371fvr_.dbf',
  '/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_system_br372tts_.dbf'
 CHARACTER SET WE8MSWIN1252
 
 
contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   set newname for clone tempfile  2 to new;
   set newname for clone tempfile  3 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_sysaux_br36zdcn_.dbf",
 "/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_undotbs1_br36zdcq_.dbf",
 "/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_users_br36zdfb_.dbf",
 "/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_sysaux_br371frz_.dbf",
 "/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_sysaux_br372ttf_.dbf";
   switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
 
renamed tempfile 1 to /u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 2 to /u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_temp_%u_.tmp in control file
 
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_sysaux_br36zdcn_.dbf RECID=1 STAMP=882637984
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_undotbs1_br36zdcq_.dbf RECID=2 STAMP=882637984
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_users_br36zdfb_.dbf RECID=3 STAMP=882637984
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_sysaux_br371frz_.dbf RECID=4 STAMP=882637984
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_sysaux_br372ttf_.dbf RECID=5 STAMP=882637984
 
datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=882637984 file name=/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_sysaux_br36zdcn_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=882637984 file name=/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_undotbs1_br36zdcq_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=3 STAMP=882637984 file name=/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_users_br36zdfb_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=882637984 file name=/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_sysaux_br371frz_.dbf
datafile 16 switched to datafile copy
input datafile copy RECID=5 STAMP=882637984 file name=/u01/app/oracle/oradata/TESTCDB1/datafile/o1_mf_sysaux_br372ttf_.dbf
Leaving database unopened, as requested
Cannot remove created server parameter file
Finished Duplicate Db at 17-JUN-15
 
RMAN> exit
 

Connect to auxiliary CDB instance and check PDB existence

 
[oracle@Server dbs]$ sqlplus "/ as sysdba"
 
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jun 17 17:31:47 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
SQL> alter database open resetlogs;
Database altered.
 
SQL> show pdbs
 
    CON_ID        CON_NAME                              OPEN MODE     RESTRICTED
----------          ------------------------------      ----------          ----------
         2             PDB$SEED                               READ ONLY       NO
         3             TESTPDB                                  MOUNTED
 

Check validity of PDB by opening it

 

SQL> alter pluggable database testpdb open;
Pluggable database altered.
 
SQL> show pdbs
 
    CON_ID        CON_NAME                              OPEN MODE     RESTRICTED
----------          ------------------------------      ----------          ----------
         2             PDB$SEED                               READ ONLY       NO
         3             TESTPDB                                  READ WRITE     NO
 

Close the PDB to unplug it

 
SQL> alter pluggable database testpdb close immediate;
Pluggable database altered.
 
SQL> show pdbs
 
    CON_ID        CON_NAME                              OPEN MODE     RESTRICTED
----------          ------------------------------      ----------          ----------
         2             PDB$SEED                               READ ONLY       NO
         3             TESTPDB                                  MOUNTED
 

Execute unplug command to detach PDB from auxiliary CDB

 
SQL>  alter pluggable database testpdb unplug into '/tmp/testpdb.xml';
Pluggable database altered.
 

Plug PDB into Target database

 

·     Check existing PDBs

 
SQL> show pdbs
 
    CON_ID        CON_NAME                              OPEN MODE     RESTRICTED
----------          ------------------------------      ----------          ----------
         2             PDB$SEED                               READ ONLY       NO
 

Execute create pluggable database using XML file created through unplug command. Use copy command to move pdb data files to new location

 

SQL>  create pluggable database testpdb using '/tmp/testpdb.xml'
COPY   2  ;
Pluggable database created.
 
SQL> show pdbs

 

    CON_ID        CON_NAME                              OPEN MODE     RESTRICTED
----------          ------------------------------      ----------          ----------
         2             PDB$SEED                               READ ONLY       NO
         3             TESTPDB                                  MOUNTED
 
Summary: If Pluggable database (PDB) is dropped mistakenly in Container Database (CDB), it has to be restored through Point in time recovery (PITR) method as we recover other database objects.
9688 1 /
Follow / 24 Sep 2015 at 7:17am

nice one.....