How to Restore Dropped PDB in Multitenant using Auxiliary instance ( For Single Instance Database)
If a pluggable database (PDB)/datafiles are accidentally deleted, corrupted etc. the repository/metadata for the PDB still exists and it can be restored and recoverd If a PDB is accidentally/intentionally dropped using DROP PLUGGABLE DATABASE <PDBNAME>, this will drop the PDB and remove the metadata from repository.
Therefore, a restore of the PDB (including PDB PITR to before the dropped time) will fail with an error such as RMAN-06813: could not translate pluggable database PDBxx.
Above error is identified as bug 19809171 which required to apply the patch if recovery of Dropped PDB fails.
As workaround, we can can restore/recover the dropped PDB in an 'Auxiliary' instance as Out-Place restore/recover.
Note: This note can be used as alternative method where Dropped pdb can be restored and recovered in auxiliary instance and can be plugged again in Source ( production ) database.
In a multitenant container database (CDB), the metadata for data dictionary tables and view definitions is stored only in the root. However, each pluggable database (PDB) has its own set of data dictionary tables and views for the database objects contained in the PDB.
So, a pluggable database (PDB) cannot be restored as a database. It must be restored as database object using an auxiliary instance.
Steps with Example:
1. Create new pluggable database
SQL> create pluggable database pdb3 admin user test identified by test;
COLUMN PDB_NAME FORMAT A15 SQL> SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;
PDB_ID PDB_NAME STATUS
---------- --------------- --------- 2 PDB$SEED NORMAL 3 PDB1 NORMAL 4 PDB2 NORMAL 5 PDB3 NORMAL <=======
2. Take backup of Container Database (CDB)
# Connect to RMAN using sysdba or sysbackup user and take backup of CDB database which includes pluggable database pdb2drop # rman> backup database format '/u02/oradata/dbcdb/bkp/%d_%s_%t_%U.bkp' plus archivelog format '/u02/oradata/dbcdb/bkp/arc_%s_%t_%U.bkp' TAG 'full db backup'; rman> backup current controlfile format '/u02/oradata/dbcdb/bkp/CF_%d_%s_%t_%U.bkp' tag 'CF_Backup' ;3. Drop the pluggable database (pdb3) which created in (1)
-- To simulate the case we drop the pluggable database pdb3 SQL> drop pluggable database pdb3 INCLUDING DATAFILES ;4. Restoring dropped pluggable database in same source database fails with RMAN-06813
# while trying to restore PDB as a database, in same container we will get below error
RMAN> restore pluggable database pdb3;
Starting restore at 25-APR-16 using channel ORA_DISK_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 25/04/2016 10:10:40 RMAN-06813: could not translate pluggable database pdb3
5. Create a temporary auxiliary instance in same source (Production) Server or in another server (Auxiliary Server ) to restore container database (CDB) and required pluggable database (PDB)
In this example, auxiliary instance created in same source server
5.1 Create password file for auxiliary instance
$ cd $ORACLE_HOME/dbs $ orapwd file=dbcdbaux password=syspwd5.2 Create init file for auxiliary instance
-- create initdbcdbaux.ora -- verify compatible parameter matches the version of your source db (initdbcdb.ora) # initdbcdbaux.ora audit_file_dest='/u02/oradata/dbcdbaux/admin/adump/' diagnostic_dest='/u02/oradata/dbcdbaux/admin/' audit_trail='db' compatible='12.1.0.2.0' control_files='/u02/oradata/dbcdbaux/ctl/CONTROL01.CTL' db_name='dbcdbaux' instance_name=dbcdbaux db_unique_name=dundbcdbaux enable_pluggable_database=true remote_login_passwordfile='EXCLUSIVE' undo_tablespace='UNDOTBS1' db_create_file_dest='/u02/oradata/dbcdbaux/oradata/' <== Restores datafiles in OMF format in this location db_create_online_log_dest_1='/u02/oradata/dbcdbaux/oradata/redo/' <== Since database is duplicated in same server, provide appropriate location otherwise duplicate process might overwrite source database ORL which corrupt source database.
6. Start the auxiliary instance in nomount state
$ export ORACLE_SID=dbcdbaux $ sqlplus sys/syspwd as sysdba sql> startup nomount
7. If you are creating Auxiliary instance in another server (Auxiliary Server ) then copy the backup pieces from the production server to the auxiliary server
If the auxiliary instance is created in the same server as production, then RMAN can read the backup pieces from "/u02/oradata/dbcdb/bkp/" as below
7.1 Connect auxiliary instance through RMAN
# RMAN $ export ORACLE_SID=dbcdbaux $ rman auxiliary sys/syspwd8. Execute Duplicate command for CDB using "BACKUP LOCATION" in Auxiliary Instance.
8.1 If the source database has ONLY ONE pdb then
RMAN> Duplicate database to 'dbcdbaux' noopen backup location '/u02/oradata/dbcdb/bkp/';
8.2 If the source database has multiple PDBs and if we want to duplicate CDB with ONLY ONE pdb which we need to restore, then mention the pdb name as below.
RMAN> DUPLICATE database to 'dbcdbaux' PLUGGABLE DATABASE pdb3 noopen backup location '/u02/oradata/dbcdb/bkp/' ;
RMAN Duplicate Output for (8.2):
======================
[oracle@olnxn1] export ORACLE_SID=dbcdbaux
[oracle@olnxn1]$ rman auxiliary /
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Apr 25 17:25:14 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: DBCDBAUX (not mounted)
RMAN> DUPLICATE database to 'dbcdbaux' PLUGGABLE DATABASE pdb3 noopen backup location '/u02/oradata/dbcdb/bkp/' ;
Starting Duplicate Db at 25-APR-16
contents of Memory Script: { sql clone "alter system set db_name = ''DBCDB'' comment= ''Modified by RMAN duplicate'' scope=spfile"; shutdown clone immediate; startup clone force nomount restore clone primary controlfile from '/u02/oradata/dbcdb/bkp/CF_DBCDB_50_910104958_1ir3u6bu_1_1.bkp'; alter clone database mount; } executing Memory Script
sql statement: alter system set db_name = ''DBCDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 1048576000 bytes
Fixed Size 2932336 bytes Variable Size 603980176 bytes Database Buffers 436207616 bytes Redo Buffers 5455872 bytes
Starting restore at 25-APR-16 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=28 device type=DISK
channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 output file name=/u02/oradata/dbcdbaux/ctl/CONTROL01.CTL Finished restore at 25-APR-16
database mounted released channel: ORA_AUX_DISK_1 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=28 device type=DISK allocated channel: ORA_AUX_DISK_2 channel ORA_AUX_DISK_2: SID=29 device type=DISK allocated channel: ORA_AUX_DISK_3 channel ORA_AUX_DISK_3: SID=31 device type=DISK allocated channel: ORA_AUX_DISK_4 channel ORA_AUX_DISK_4: SID=32 device type=DISK Skipping pluggable database PDB1 Skipping pluggable database PDB2 Automatically adding tablespace SYSTEM Automatically adding tablespace SYSAUX Automatically adding tablespace PDB$SEED:SYSTEM Automatically adding tablespace PDB$SEED:SYSAUX Automatically adding tablespace UNDOTBS1 Skipping tablespace DEFTBS Skipping tablespace PDB$SEED:USERTBS Skipping tablespace PDB$SEED:DEFTBS Not connected to TARGET, cannot verify that set of tablespaces being duplicated does not have SYS objects
contents of Memory Script: { set until scn 2644208; set newname for clone datafile 1 to new; set newname for clone datafile 2 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 38 to new; set newname for clone datafile 39 to new; set newname for clone datafile 40 to new; set newname for clone datafile 41 to new; restore clone database skip forever tablespace "DEFTBS", "PDB$SEED":"USERTBS", "PDB$SEED":"DEFTBS", "PDB1":"USERTBS", "PDB1":"SYSTEM", "PDB1":"SYSAUX", "PDB1":"DEFTBS", "PDB2":"USERTBS", "PDB2":"SYSTEM", "PDB2":"SYSAUX", "PDB2":"DEFTBS" ; } 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
executing command: SET NEWNAME
Starting restore at 25-APR-16 using channel ORA_AUX_DISK_1 using channel ORA_AUX_DISK_2 using channel ORA_AUX_DISK_3 using channel ORA_AUX_DISK_4
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 00004 to /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_sysaux_%u_.dbf channel ORA_AUX_DISK_1: reading from backup piece /u02/oradata/dbcdb/bkp/DBCDB_32_910103927_10r3u5bn_1_1.bkp channel ORA_AUX_DISK_2: starting datafile backup set restore channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_2: restoring datafile 00038 to /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_system_%u_.dbf channel ORA_AUX_DISK_2: reading from backup piece /u02/oradata/dbcdb/bkp/DBCDB_38_910104205_16r3u5kd_1_1.bkp .. .. .. channel ORA_AUX_DISK_3: piece handle=/u02/oradata/dbcdb/bkp/DBCDB_36_910104093_14r3u5gt_1_1.bkp tag=TAG20160425T143623 channel ORA_AUX_DISK_3: restored backup piece 1 channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:02:45 channel ORA_AUX_DISK_1: piece handle=/u02/oradata/dbcdb/bkp/DBCDB_27_910103783_0rr3u577_1_1.bkp tag=TAG20160425T143623 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: reading from backup piece /u02/oradata/dbcdb/bkp/DBCDB_27_910103783_0rr3u577_2_1.bkp channel ORA_AUX_DISK_1: piece handle=/u02/oradata/dbcdb/bkp/DBCDB_27_910103783_0rr3u577_2_1.bkp tag=TAG20160425T143623 channel ORA_AUX_DISK_1: restored backup piece 2 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:02 Finished restore at 25-APR-16
contents of Memory Script: { switch clone datafile all; } executing Memory Script
datafile 1 switched to datafile copy input datafile copy RECID=10 STAMP=910114359 file name=/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_system_ckw1nnhv_.dbf datafile 2 switched to datafile copy input datafile copy RECID=11 STAMP=910114359 file name=/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_system_ckw1np5z_.dbf datafile 3 switched to datafile copy input datafile copy RECID=12 STAMP=910114360 file name=/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_sysaux_ckw1hc2l_.dbf datafile 4 switched to datafile copy input datafile copy RECID=13 STAMP=910114360 file name=/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_sysaux_ckw1gw1z_.dbf datafile 5 switched to datafile copy input datafile copy RECID=14 STAMP=910114360 file name=/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_undotbs1_ckw1mccg_.dbf datafile 38 switched to datafile copy input datafile copy RECID=15 STAMP=910114360 file name=/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_system_ckw1gw5g_.dbf datafile 39 switched to datafile copy input datafile copy RECID=16 STAMP=910114360 file name=/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_sysaux_ckw1gw5f_.dbf datafile 40 switched to datafile copy input datafile copy RECID=17 STAMP=910114360 file name=/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_deftbs_ckw1gw5l_.dbf datafile 41 switched to datafile copy input datafile copy RECID=18 STAMP=910114360 file name=/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_usertbs_ckw1h3yr_.dbf
contents of Memory Script: { set until scn 2644208; recover clone database skip forever tablespace "DEFTBS", "PDB$SEED":"USERTBS", "PDB$SEED":"DEFTBS", "PDB1":"USERTBS", "PDB1":"SYSTEM", "PDB1":"SYSAUX", "PDB1":"DEFTBS", "PDB2":"USERTBS", "PDB2":"SYSTEM", "PDB2":"SYSAUX", "PDB2":"DEFTBS" delete archivelog ; } executing Memory Script
executing command: SET until clause
Starting recover at 25-APR-16 using channel ORA_AUX_DISK_1 using channel ORA_AUX_DISK_2 using channel ORA_AUX_DISK_3 using channel ORA_AUX_DISK_4
Executing: alter database datafile 8 offline drop Executing: alter database datafile 7 offline drop Executing: alter database datafile 12 offline drop Executing: alter database datafile 9 offline drop Executing: alter database datafile 10 offline drop Executing: alter database datafile 11 offline drop Executing: alter database datafile 16 offline drop Executing: alter database datafile 13 offline drop Executing: alter database datafile 14 offline drop Executing: alter database datafile 15 offline drop 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=2425 channel ORA_AUX_DISK_1: reading from backup piece /u02/oradata/dbcdb/bkp/arc_48_910104279_1gr3u5mn_1_1.bkp channel ORA_AUX_DISK_1: piece handle=/u02/oradata/dbcdb/bkp/arc_48_910104279_1gr3u5mn_1_1.bkp tag=FULL DB BACKUP 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=/u02/oradata/dbcdbaux/aux11_2425_861808862.arc thread=1 sequence=2425 Oracle Error: ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 6 needs more recovery to be consistent ORA-01110: data file 6: '/u02/oradata/dbcdb/data/cdb/DEFTBS01.DBF'
channel clone_default: deleting archived log(s) archived log file name=/u02/oradata/dbcdbaux/aux11_2425_861808862.arc RECID=1 STAMP=910114363 media recovery complete, elapsed time: 00:00:02 Finished recover at 25-APR-16 Oracle instance started
Total System Global Area 1048576000 bytes
Fixed Size 2932336 bytes Variable Size 603980176 bytes Database Buffers 436207616 bytes Redo Buffers 5455872 bytes
contents of Memory Script: { sql clone "alter system set db_name = ''DBCDBAUX'' comment= ''Reset to original value by RMAN'' scope=spfile"; } executing Memory Script
sql statement: alter system set db_name = ''DBCDBAUX'' comment= ''Reset to original value by RMAN'' scope=spfile Oracle instance started
Total System Global Area 1048576000 bytes
Fixed Size 2932336 bytes Variable Size 603980176 bytes Database Buffers 436207616 bytes Redo Buffers 5455872 bytes sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DBCDBAUX" RESETLOGS ARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 3 MAXDATAFILES 1024 MAXINSTANCES 16 MAXLOGHISTORY 2921 LOGFILE GROUP 1 SIZE 5 M , GROUP 2 SIZE 5 M , GROUP 3 SIZE 5 M DATAFILE '/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_system_ckw1nnhv_.dbf', '/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_system_ckw1np5z_.dbf', '/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_system_ckw1gw5g_.dbf' CHARACTER SET AL32UTF8
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 5 to new; switch clone tempfile all; catalog clone datafilecopy "/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_sysaux_ckw1hc2l_.dbf", "/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_sysaux_ckw1gw1z_.dbf", "/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_undotbs1_ckw1mccg_.dbf", "/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_sysaux_ckw1gw5f_.dbf", "/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_deftbs_ckw1gw5l_.dbf", "/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_usertbs_ckw1h3yr_.dbf"; switch clone datafile all; } executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_tempts1_%u_.tmp in control file renamed tempfile 2 to /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_tempts1_%u_.tmp in control file renamed tempfile 5 to /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_tempts1_%u_.tmp in control file
cataloged datafile copy datafile copy file name=/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_sysaux_ckw1hc2l_.dbf RECID=1 STAMP=910114393 cataloged datafile copy datafile copy file name=/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_sysaux_ckw1gw1z_.dbf RECID=2 STAMP=910114393 cataloged datafile copy datafile copy file name=/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_undotbs1_ckw1mccg_.dbf RECID=3 STAMP=910114393 cataloged datafile copy datafile copy file name=/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_sysaux_ckw1gw5f_.dbf RECID=4 STAMP=910114393 cataloged datafile copy datafile copy file name=/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_deftbs_ckw1gw5l_.dbf RECID=5 STAMP=910114393 cataloged datafile copy datafile copy file name=/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_usertbs_ckw1h3yr_.dbf RECID=6 STAMP=910114393
datafile 3 switched to datafile copy input datafile copy RECID=1 STAMP=910114393 file name=/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_sysaux_ckw1hc2l_.dbf datafile 4 switched to datafile copy input datafile copy RECID=2 STAMP=910114393 file name=/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_sysaux_ckw1gw1z_.dbf datafile 5 switched to datafile copy input datafile copy RECID=3 STAMP=910114393 file name=/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_undotbs1_ckw1mccg_.dbf datafile 39 switched to datafile copy input datafile copy RECID=4 STAMP=910114393 file name=/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_sysaux_ckw1gw5f_.dbf datafile 40 switched to datafile copy input datafile copy RECID=5 STAMP=910114393 file name=/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_deftbs_ckw1gw5l_.dbf datafile 41 switched to datafile copy input datafile copy RECID=6 STAMP=910114393 file name=/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_usertbs_ckw1h3yr_.dbf Leaving database unopened, as requested Finished Duplicate Db at 25-APR-16
RMAN>
Note:
# After RMAN Duplicate , before to open restored PDB in AUXILIARY Instance will shows as "_UNKNOWN_PDB_"
RMAN> report schema;
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA <====== Ignore RMAN-06139 warning message Report of database schema for database with db_unique_name DUNDBCDBAUX
List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 670 SYSTEM *** /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_system_ckw1nnhv_.dbf 2 215 PDB$SEED:SYSTEM *** /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_system_ckw1np5z_.dbf 3 460 SYSAUX *** /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_sysaux_ckw1hc2l_.dbf 4 240 PDB$SEED:SYSAUX *** /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_sysaux_ckw1gw1z_.dbf 5 460 UNDOTBS1 *** /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_undotbs1_ckw1mccg_.dbf 38 215 _###_UNKNOWN_PDB_#_5:SYSTEM *** /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_system_ckw1gw5g_.dbf 39 240 _###_UNKNOWN_PDB_#_5:SYSAUX *** /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_sysaux_ckw1gw5f_.dbf 40 10 _###_UNKNOWN_PDB_#_5:DEFTBS *** /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_deftbs_ckw1gw5l_.dbf 41 10 _###_UNKNOWN_PDB_#_5:USERTBS *** /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_usertbs_ckw1h3yr_.dbf
List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 17 TEMPTS1 17 /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_tempts1_%u_.tmp 2 6 PDB$SEED:TEMPTS1 6 /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_tempts1_%u_.tmp 5 6 _###_UNKNOWN_PDB_#_5:TEMPTS1 6 /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_tempts1_%u_.tmp
9. Open the auxiliary database with resetlogs and open the dropped pdb which restored here.
9.1 SQL> alter database open resetlogs;
9.2 SQL> alter pluggable database pdb3 open;
[oracle@olnxn1] export ORACLE_SID=dbcdbaux
[oracle@olnxn1] $ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 25 17:34:29 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED MOUNTED 5 _###_UNKNOWN_PDB_#_5 MOUNTED SQL>
SQL> alter database open resetlogs;
Database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED 4 PDB2 MOUNTED 5 PDB3 MOUNTED <============ Dropped PDB
SQL> alter pluggable database pdb3 open;
Pluggable database altered.
SQL> show pdbs
ON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED 4 PDB2 MOUNTED 5 PDB3 READ WRITE NO
Note: For other pluggable database which NOT restored/ recovered (pdb2 and pdb1) cannot be opened
SQL> alter pluggable database pdb2 open; alter pluggable database pdb2 open * ERROR at line 1: ORA-01173: data dictionary indicates missing data file from system tablespace
SQL> alter pluggable database pdb1 open; alter pluggable database pdb1 open * ERROR at line 1: ORA-01173: data dictionary indicates missing data file from system tablespace
Do crosscheck with report Schema
[oracle@olnxn1]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Apr 25 17:38:21 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBCDBAUX (DBID=1288823513)
RMAN> report schema;
using target database control file instead of recovery catalog Report of database schema for database with db_unique_name DUNDBCDBAUX
List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 670 SYSTEM YES /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_system_ckw1nnhv_.dbf 2 215 PDB$SEED:SYSTEM NO /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_system_ckw1np5z_.dbf 3 460 SYSAUX NO /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_sysaux_ckw1hc2l_.dbf 4 240 PDB$SEED:SYSAUX NO /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_sysaux_ckw1gw1z_.dbf 5 460 UNDOTBS1 YES /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_undotbs1_ckw1mccg_.dbf 6 0 DEFTBS NO /u01/app/oracle/12.1.0.2/db/dbs/MISSING00006 7 0 PDB$SEED:DEFTBS NO /u01/app/oracle/12.1.0.2/db/dbs/MISSING00007 8 0 PDB$SEED:USERTBS NO /u01/app/oracle/12.1.0.2/db/dbs/MISSING00008 38 215 PDB3:SYSTEM NO /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_system_ckw1gw5g_.dbf <======== name changed from "_UNKNOWN_PDB_" 39 240 PDB3:SYSAUX NO /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_sysaux_ckw1gw5f_.dbf <======== name changed from "_UNKNOWN_PDB_" 40 10 PDB3:DEFTBS NO /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_deftbs_ckw1gw5l_.dbf <======== name changed from "_UNKNOWN_PDB_" 41 10 PDB3:USERTBS NO /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_usertbs_ckw1h3yr_.dbf <======== name changed from "_UNKNOWN_PDB_"
List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 17 TEMPTS1 17 /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_tempts1_ckw2084d_.tmp 2 6 PDB$SEED:TEMPTS1 6 /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_tempts1_ckw20pyt_.tmp 5 6 PDB3:TEMPTS1 6 /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_tempts1_ckw2180v_.tmp <======== name changed from "_UNKNOWN_PDB_"
10. Close the PDB
SQL> alter pluggable database pdb3 close immediate;11. Unplug the PDB to detach from auxiliary CDB
SQL> alter pluggable database pdb3 unplug into '/tmp/pdb3.xml';
On production server (the original container CDB) plug this PDB:
12. Create pluggable database using XML file which created previous step.
12.1 In our example datafiles are created in OMF format in different location . So,pdb3 can be created using FILE_NAME_CONVERT and COPY command as below
SQL> Create pluggable database pdb3 using '/tmp/pdb3.xml' COPY FILE_NAME_CONVERT = ('/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_system_ckw1gw5g_.dbf','/u02/oradata/dbcdb/data/pdb3/system.dbf', '/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_sysaux_ckw1gw5f_.dbf','/u02/oradata/dbcdb/data/pdb3/sysaux.dbf', '/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_deftbs_ckw1gw5l_.dbf','/u02/oradata/dbcdb/data/pdb3/deftbs.dbf', '/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_usertbs_ckw1h3yr_.dbf','/u02/oradata/dbcdb/data/pdb3/usertbs.dbf', '/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_tempts1_ckw2180v_.tmp','/u02/oradata/dbcdb/data/pdb3/temp.dbf') tempfile reuse;
Note: PDB datafiles are copied from Auxiliary location to Prod location by oracle and datafile names renamed in controlfile through above command
12.2 Alternatively we can copy the datafiles manually by using OS command from Auxiliary location to Production location , then it can be plugged as below using SOURCE_FILE_NAME_CONVERT and NOCOPY
12.2.2 [oracle@olnxn1]$ cp /u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/* /u02/oradata/dbcdb/data/pdb3/.
12.2.3 SQL> create pluggable database pdb3 using '/tmp/pdb3.xml' SOURCE_FILE_NAME_CONVERT = ('/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_system_ckw1gw5g_.dbf','/u02/oradata/dbcdb/data/pdb3/system.dbf', '/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_sysaux_ckw1gw5f_.dbf','/u02/oradata/dbcdb/data/pdb3/sysaux.dbf', '/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_deftbs_ckw1gw5l_.dbf','/u02/oradata/dbcdb/data/pdb3/deftbs.dbf', '/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_usertbs_ckw1h3yr_.dbf','/u02/oradata/dbcdb/data/pdb3/usertbs.dbf', '/u02/oradata/dbcdbaux/data/DUNDBCDBAUX/datafile/o1_mf_tempts1_ckw2180v_.tmp','/u02/oradata/dbcdb/data/pdb3/temp.dbf') nocopy tempfile reuse;
12.3 If datafile location of Production and Auxiliary are SAME and both instance are residing in same server then pdb can be plugged to production database as below
SQL> create pluggable database pdb3 using '/tmp/pdb3.xml' NOCOPY ;
Note: For NOCOPY , COPY , FILE_NAME_COVERT , SOURCE_FILE_NAME_CONVERT , TEMPFILE REUSE refer oracle documentation http://docs.oracle.com/database/121/SQLRF/statements_6010.htm#CCHBGHCI Database SQL Language Reference .. CREATE PLUGGABLE DATABASE .. for detailed explanation .
13. Check existence of PDB:
SQL> show pdbsCON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED 4 PDB2 MOUNTED 5 PDB3 MOUNTED <=============== Plugged In
SQL> alter pluggable database pdb3 open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED 4 PDB2 MOUNTED 5 PDB3 READ WRITE NO SQL>
