Oracle 12cR2 | Recover PDB$SEED using existing PDB | Without Backup | Recreating PDB$SEED | Part 2
Dear DBA Connections,
Recover PDB$SEED using existing PDB (without backup):
In the previous video, I have explored the method of recovering a seed pluggable database using a VALID seed pluggable database backup. In this video, I will explore the process of recovering or recreating a seed pluggable database when there is no backup available. In the event of seed pluggable database being in a unusable state (corrupted/datafile missing), we can recreate the seed pluggable database using any of the existing pluggable database (local or remote).
Let me demonstrate this method, with a quick example. In the following example, the seed pluggable database file is missing and we are not able to use it for creation of new pluggable databases.
---//
---// not able to create PDB due to missing PDB$SEED datafile //---
---//
SQL> create pluggable database CDB2PLUG admin user pdb_admin identified by oracle file_name_convert=('/u02/oradata/CDB1/pdbseed/','/u02/oradata/CDB2PLUG/');
Since, we do not have a backup available for the seed pluggable database, we can't restore the missing files as demonstrated in the earlier video. We need to drop and recreate a fresh seed pluggable database using any of the existing pluggable databases (local or remote)
Let's drop the seed pluggable database PDB$SEED from the container database.
---//
---// trying to drop seed pluggable database //---
---//
SQL> drop pluggable database "pdb$seed" including datafiles;
As the error suggests, we need to close the seed pluggable database to be able to drop it from the container. Let's close the seed pluggable database first.
---//
---// trying to close the seed pluggable database //---
---//
SQL> alter pluggable database "pdb$seed" close;
Like we have seen earlier, we can't alter the seed pluggable database state in the default mode. We need to set the hidden parameter _oracle_script to TRUE to be able to change the seed pluggable database state.
---//
---// dropping PDB$SEED by setting _oracle_script to TRUE //---
---//
SQL> alter session set "_oracle_script"=true;
Session altered.
SQL> alter pluggable database "pdb$seed" close;
Pluggable database altered.
SQL> drop pluggable database "pdb$seed" including datafiles;
Pluggable database dropped.
---//
---// validate PDB$SEED is dropped //---
---//
SQL> show pdbs
---//
---// reset _oracle_script to FALSE //---
---//
SQL> alter session set "_oracle_script"=false;
We have dropped the unusable seed pluggable database (PDB$SEED) from the container. Now, we can recreate it using any of this existing pluggable database. However, to be able to recreate the seed pluggable database from an existing pluggable database, the existing pluggable database needs to be in READ-ONLY mode. We will basically clone an existing pluggable database to create the seed pluggable database.
Let's put one of the existing pluggable database in read only mode.
---//
---// putting existing PDB in READ-ONLY mode for cloning //---
---//
SQL> show pdbs
SQL> alter pluggable database CDB1PLUG close;
SQL> alter pluggable database CDB1PLUG open read only;
SQL> show pdbs
We have kept the existing pluggable database CDB1PLUG in READ-ONLY mode as a prerequisite for cloning. Let's recreate the seed pluggable database using this existing pluggable database as shown below.
---//
---// recreate PDB$SEED by cloning existing PDB //---
---//
SQL> create pluggable database "pdb$seed" from CDB1PLUG admin user pdb_admin identified by oracle file_name_convert=('/u02/oradata/CDB1/CDB1PLUG/','/u02/oradata/CDB1/pdbseed/');
SQL> show pdbs
SQL> select name from v$datafile where con_id=2;
---//
---// put the existing PDB back in READ-WRITE mode //---
---//
SQL> alter pluggable database CDB1PLUG close;
SQL> alter pluggable database CDB1PLUG open read write;
We have successfully recreated the seed pluggable database by cloning an existing pluggable database. Now, we need to keep the seed pluggable database (PDB$SEED) in READ-ONLY mode to be able to use it for other pluggable database creation. However, we can't not directly put the new seed database in the READ-ONLY mode. We need to first open it in READ-WRITE mode for data dictionary synchronization.
---//
---// 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;
At this stage, we have completely recovered (recreated) the seed pluggable database and it can be now used to create new pluggable databases.
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: