Oracle 12cR2 | Recover PDB$SEED from Remote PDB$SEED | Without Backup | PDB$SEED Remote Clone|Part 3
Dear DBA Connections,
In the previous video, I have explored the method for recreating a missing/corrupted seed pluggable database by cloning an existing pluggable database. However, that method requires the existing pluggable database to be kept in READ-ONLY mode for the purpose of cloning. In the method of cloning an existing pluggable, we will eventually clone everything from the existing pluggable database and probably include application specific objects which we do not want to keep in the seed pluggable database. Therefore, it would not be a good idea to recreate the seed pluggable database by cloning an existing pluggable database.
We have another option for recreating a seed pluggable database, which can used to avoid cloning an existing pluggable database and prevent copying application related objects in the seed pluggable database. In this method, we can use a seed pluggable database (PDB$SEED) from another (compatible) container database to recreate the unusable seed pluggable database (PDB$SEED) in the required container database as shown in the following diagram.
In the following example, I will use the seed pluggable database (PDB$SEED) from remote container database CDB2 to recreate the the seed pluggable database in the local container database CDB1.
Target TNS entry needs to put on source side:
[oracle@srlabprmy admin]$ tnsping CDB2
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 15-AUG-2020 05:28:34
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0.1/db_2/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.58.202)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDB2)))
OK (70 msec)
---//
---// creating DB Link from local to remote container //---
---//
create database link remote_seed_link connect to system identified by ora12345 using '192.168.58.202:1521/CDB2';
---//
---// validate the remote database link //---
---//
SQL> select name,cdb from v$database@remote_seed_link;
SQL> select con_id,name,open_mode from v$pdbs@remote_seed_link where name='PDB$SEED';
We have created a database link from the local container database (CDB1) to the remote container database (CDB2). Now, we will use this link to create the XML manifest file, which will represent the structure of remote seed pluggable database (PDB$SEED).
---//
---// create XML manifest file representing remote PDB$SEED structure //---
---//
SQL> exec DBMS_PDB.DESCRIBE(pdb_descr_file => '/home/oracle/seed_CDB2.xml', pdb_name => 'pdb$seed@REMOTE_SEED_LINK');
SQL> !ls -lrt /home/oracle/seed_CDB2.xml
We have generated XML manifest file describing the remote seed pluggable database structure. Now, we need to copy the datafiles belonging to the remote seed pluggable database over to the local container database server. Let's identify the remote seed pluggable database's datafiles that needs to be copied.
---//
---// identify remote PDB$SEED's datafiles to be copied //---
---//
SQL> select name from v$datafile@REMOTE_SEED_LINK where con_id=2;
SQL> select name from v$tempfile@REMOTE_SEED_LINK where con_id=2;
Let's copy the files from remote container database server to the local container database server (under the location, where we want the local seed's datafiles to be present)
---//
---// copying remote seed pluggable database's datafiles to local container database server //---
---//
[oracle@srlabsby admin]$ scp /data/oracle/orpcdb1/pdbseed/*.dbf /data/oracle/orpcdb2/pdbseed/
Now, we can recreate the seed pluggable database in local container database (CDB1) using the remote pluggable database's XML manifest file that we had generated earlier. Let's recreate the seed pluggable database in the local container database.
---//
---// create local seed pluggable database using remote seed's XML manifest file //---
---//
create pluggable database "pdb$seed" using '/home/oracle/seed_CDB2.xml' source_file_name_convert=('/u02/oradata/CDB2/pdbseed/','/u02/oradata/CDB1/pdbseed/') NOCOPY TEMPFILE REUSE;
---//
---// validate PDB$SEED creation //---
---//
SQL> show pdbs
At this stage, we have recreated the seed pluggable database (PDB$SEED) using another remote seed pluggable database. We can now open the seed pluggable database for dictionary synchronization followed by keeping the seed in READ-ONLY mode as shown in the previous section.
---//
---// set _oracle_script to TRUE to be able to alter PDB$SEED state //---
---//
SQL> alter session set "_oracle_script"=true;
---//
---// READ-ONLY open is not allowed for the first time //---
---//
SQL> alter pluggable database PDB$SEED open read only;
---//
---// open PDB$SEED in READ-WRITE mode for dictionary synchronization //---
---//
SQL> alter pluggable database PDB$SEED open read write;
---//
---// put PDB$SEED back in READ-ONLY mode after dictionary synchronization //---
---//
SQL> alter pluggable database PDB$SEED close;
SQL> alter pluggable database PDB$SEED open read only;
---//
---// validate PDB$SEED state //---
---//
SQL> show pdbs
---//
---// reset _oracle_script to FALSE //---
---//
SQL> alter session set "_oracle_script"=false;
We have now completely recreated the seed pluggable database and kept it in the desired state (READ-ONLY). Now, we should be able to create new pluggable databases using this seed pluggable database (PDB$SEED)
Conclusion:
In this video, I have explored how to restoring or recreating a seed pluggable database (PDB$SEED) in the event of seed database being in the UNUSABLE state. However, we still have ways to recreate the seed pluggable database in the absence of a VALID backup.
Regards,
Ramesh.
Please follow me on:
DBA BLOG:https://www.sachinrameshoracledbablog.com
YOUTUBE: https://www.youtube.com/oraclef1
TWITTER: https://twitter.com/sachinrameshdba
FACEBOOK: https://www.facebook.com/rameshkumar.krishnamoorthy.9
LINKEDIN:https://www.linkedin.com/in/ramesh-kumar-krishnamoorthy-3a67ba69/
Email: oralclehelplines@gmail.com
Post a Comment: