Oracle 12c has introduced the multi-tenant architecture for Oracle database, where a single container database (CDB$ROOT) can have multiple pluggable databases (PDBs). This new architecture is introduced to ease the management of Oracle databases, where by we can consolidate multiple Oracle databases into a single container database (CDB). In the multi-tenant architecture, ideally we use the SEED template pluggable database (PDB$SEED) to create any new pluggable database within the container database (CDB$ROOT). The SEED pluggable database (PDB$SEED) acts as a template for creating fresh pluggable databases and we are not allowed to alter the configuration of SEED pluggable database (by default opens up in READ ONLY mode).
There are possibilities, that the SEED pluggable database (PDB$SEED) may become corrupt or unusable due to file system issues or due to any other unforeseen reasons. In that case, we can't use the seed pluggable database (PDB$SEED) for creating new pluggable databases in the respective container.
In this article, I will discuss about the different methods that we can follow to recover or recreate the seed pluggable database (PDB$SEED) in the event of seed being in a unusable state (corrupted)
Recover PDB$SEED using backup
Backup is the first place of defense to recover or restore a database. It is always recommended to backup the databases to be able to restore it when required. In the multi-tenant architecture, we have the option of taking backup of individual pluggable databases or all the pluggable databases together along with the container database.
If the seed pluggable database becomes unusable, we can restore it using the backup. Here is the pictorial representation of the overall process involved in restoring a seed pluggable database from a backup.
Since, I am simulating the failure of the seed pluggable database (PDB$SEED), let me take a backup of the seed pluggable database (PDB$SEED) for this demonstration. We will use this backup to restore/reover the seed database later.
##---
##--- Taking backup of seed pluggable database (PDB$SEED) ---##
##---
[oracle@labserver2 ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Thu Apr 28 23:16:24 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORPCDB2 (DBID=2428270533)
RMAN> backup database "pdb$seed" format '/backup/orpcdb2/seed/orpcdb2_seed_%U.bkp';
Starting backup at 28-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
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=/data/oracle/orpcdb2/pdbseed/users01.dbf
input datafile file number=00002 name=/data/oracle/orpcdb2/pdbseed/system01.dbf
input datafile file number=00004 name=/data/oracle/orpcdb2/pdbseed/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 28-APR-16
channel ORA_DISK_1: finished piece 1 at 28-APR-16
piece handle=/backup/orpcdb2/seed/orpcdb2_seed_02r470qb_1_1.bkp tag=TAG20160428T231627 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 28-APR-16
Starting Control File Autobackup at 28-APR-16
piece handle=/app/oracle/product/12.1.0.2/dbs/c-2428270533-20160428-00 comment=NONE
Finished Control File Autobackup at 28-APR-16
RMAN>
For the purpose of demonstration, I am deleting the system datafile belonging to the seed pluggable database (PDB$SEED) as shown below. This will make the seed pluggable database unusable and we will not be able to use the seed pluggable database for creating new pluggable databases in the respective container database (CDB$ROOT). In a real time scenario, the seed pluggable database may become unusable due to a number of unforeseen reasons.
##---
##--- deleting system datafile from seed pluggable database ---##
##---
[oracle@labserver2 ~]$ rm /data/oracle/orpcdb2/pdbseed/system01.dbf
[oracle@labserver2 ~]$ ls -lrt /data/oracle/orpcdb2/pdbseed/system01.dbf
ls: /data/oracle/orpcdb2/pdbseed/system01.dbf: No such file or directory
I have deleted the system datafile belonging to seed pluggable database (PDB$SEED). Let's try to create a new pluggable database using the seed pluggable databases.
---//
---// create pluggable database using seed (PDB$SEED) //---
---//
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 CDB2_PDB_1 READ WRITE NO
SQL> create pluggable database CDB2_PDB_2 admin user pdb_admin identified by oracle
2 file_name_convert=('/data/oracle/orpcdb2/pdbseed/','/data/oracle/orpcdb2/cdb2_pdb_2/')
3 ;
create pluggable database CDB2_PDB_2 admin user pdb_admin identified by oracle
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 2
ORA-01110: data file 2: '/data/oracle/orpcdb2/pdbseed/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
As expected, we are not able to create new pluggable database using the seed pluggable database (PDB$SEED). Let's use the seed pluggable database backup to restore/recover the seed pluggable database. We need to close the seed pluggable database to be able to restore the missing file. Let's close the seed pluggable database (PDB$SEED).
---//
---// trying to close seed pluggable database //---
---//
SQL> alter pluggable database "pdb$seed" close;
alter pluggable database "pdb$seed" close
*
ERROR at line 1:
ORA-65017: seed pluggable database may not be dropped or altered
As we can see, we are not allowed to alter the state of the seed pluggable database (PDB$SEED). However, there is workaround where we can set the hidden parameter _oracle_script to TRUE and that will allow us to change the seed pluggable database (PDB$SEED) state as shown below.
---//
---// closing 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> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 CDB2_PDB_1 READ WRITE NO
Let's restore the missing seed datafile using the backup that was taken earlier
##---
##--- validating the availability of backup ---##
##---
RMAN> list backup of datafile 2;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 221.38M DISK 00:00:06 28-APR-16
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20160428T231627
Piece Name: /backup/orpcdb2/seed/orpcdb2_seed_02r470qb_1_1.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 664727 13-FEB-16 /data/oracle/orpcdb2/pdbseed/system01.dbf
##---
##--- restoring missing seed datafile (file# 2) ---##
##---
[oracle@labserver2 seed]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Thu Feb 11 14:33:46 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: orpcdb2 (DBID=3871804100)
RMAN> restore datafile 2;
Starting restore at 28-APR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=130 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /data/oracle/orpcdb2/pdbseed/system01.dbf
channel ORA_DISK_1: reading from backup piece /backup/orpcdb2/seed/orpcdb2_seed_02r470qb_1_1.bkp
channel ORA_DISK_1: piece handle=/backup/orpcdb2/seed/orpcdb2_seed_02r470qb_1_1.bkp tag=TAG20160428T231627
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 28-APR-16
We have restored the missing seed datafile. Let's open the seed database in it's intended READ ONLY state.
##---
##--- trying to open seed pluggable database after datafile restore ---##
##---
RMAN> alter pluggable database "pdb$seed" open read only;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 04/28/2016 23:32:20
ORA-65017: seed pluggable database may not be dropped or altered
As expected, we are not allowed to alter the seed pluggable database state. Like earlier, we need to use the hidden parameter _oracle_script to be able to open the seed pluggable database as shown below.
##--- ##--- opening PDB$SEED by setting _oracle_script to TRUE ---## ##--- RMAN> alter session set "_oracle_script"=true; Statement processed RMAN> alter pluggable database "pdb$seed" open read only; Statement processed##--- ##--- validate PDB$SEED is opened in READ ONLY mode ---## ##--- RMAN> select con_id,name,open_mode,restricted from v$pdbs; CON_ID NAME OPEN_MODE RES ---------- ------------------------------ ---------- --- 2 PDB$SEED READ ONLY NO 3 CDB2_PDB_1 READ WRITE NO ##--- ##--- reset _oracle_script to FALSE ---## ##--- RMAN> alter session set "_oracle_script"=false; Statement processed
We have successfully restored and recovered the seed pluggable database (PDB$SEED). Now, we should be able to create new pluggable databases using the seed pluggable database as shown below.
---//
---// creating pluggable database using PDB$SEED //---
---//
SQL> create pluggable database CDB2_PDB_2 admin user pdb_admin identified by oracle
2 file_name_convert=('/data/oracle/orpcdb2/pdbseed/','/data/oracle/orpcdb2/cdb2_pdb_2/')
3 ;
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 CDB2_PDB_1 READ WRITE NO
4 CDB2_PDB_2 MOUNTED
Recover PDB$SEED using existing PDB (without backup)
In the previous section, we have explored the method of recovering a seed pluggable database using a VALID seed pluggable database backup. In this section, we 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) as represented by the following diagram.
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 CDB2_PDB_3 admin user pdb_admin identified by oracle
2 file_name_convert=('/data/oracle/orpcdb2/pdbseed/','/data/oracle/orpcdb2/cdb2_pdb_3/')
3 ;
create pluggable database CDB2_PDB_3 admin user pdb_admin identified by oracle
*
ERROR at line 1:
ORA-19505: failed to identify file "/data/oracle/orpcdb2/pdbseed/system01.dbf"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
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 section. 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;
drop pluggable database "pdb$seed" including datafiles
*
ERROR at line 1:
ORA-65025: Pluggable database PDB$SEED is not closed on all instances.
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;
alter pluggable database "pdb$seed" close
*
ERROR at line 1:
ORA-65017: seed pluggable database may not be dropped or altered
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
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 CDB2_PDB_1 READ WRITE NO
4 CDB2_PDB_2 MOUNTED
---//
---// reset _oracle_script to FALSE //---
---//
SQL> alter session set "_oracle_script"=false;
Session altered.
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
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 CDB2_PDB_1 READ WRITE NO
4 CDB2_PDB_2 READ WRITE NO
SQL> alter pluggable database CDB2_PDB_2 close;
Pluggable database altered.
SQL> alter pluggable database CDB2_PDB_2 open read only;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 CDB2_PDB_1 READ WRITE NO
4 CDB2_PDB_2 READ ONLY NO
We have kept the existing pluggable database CDB2_PDB_2 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 CDB2_PDB_2
2 file_name_convert=('/data/oracle/orpcdb2/cdb2_pdb_2/','/data/oracle/orpcdb2/pdbseed/')
3 ;
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 CDB2_PDB_1 READ WRITE NO
4 CDB2_PDB_2 READ ONLY NO
SQL> select name from v$datafile where con_id=2;
NAME
------------------------------------------------------------
/data/oracle/orpcdb2/pdbseed/system01.dbf
/data/oracle/orpcdb2/pdbseed/sysaux01.dbf
/data/oracle/orpcdb2/pdbseed/users01.dbf
---//
---// put the existing PDB back in READ-WRITE mode //---
---//
SQL> alter pluggable database CDB2_PDB_2 close;
Pluggable database altered.
SQL> alter pluggable database CDB2_PDB_2 open read write;
Pluggable database altered.
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;
Session altered.
---//
---// READ-ONLY open is not allowed for the first time //---
---//
SQL> alter pluggable database PDB$SEED open read only;
alter pluggable database PDB$SEED open read only
*
ERROR at line 1:
ORA-65085: cannot open pluggable database in read-only mode---//
---// open PDB$SEED in READ-WRITE mode for dictionary synchronization //---
---//
SQL> alter pluggable database PDB$SEED open read write;
Pluggable database altered.
---//
---// put PDB$SEED back in READ-ONLY mode after dictionary synchronization //---
---//
SQL> alter pluggable database PDB$SEED close;
Pluggable database altered.
SQL> alter pluggable database PDB$SEED open read only;
Pluggable database altered.
---//
---// validate PDB$SEED state //---
---//
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 CDB2_PDB_1 READ WRITE NO
4 CDB2_PDB_2 READ WRITE NO
---//
---// reset _oracle_script to FALSE //---
---//
SQL> alter session set "_oracle_script"=false;
Session altered.
At this stage, we have completely recovered (recreated) the seed pluggable database and it can be now used to create new pluggable databases.
Recover PDB$SEED from another PDB$SEED (without backup)
In the previous section, we 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 ORPCDB1 to recreate the the seed pluggable database in the local container database ORPCDB2.
---// ---// creating DB Link from local to remote container //--- ---// SQL> create database link remote_seed_link 2 connect to system identified by oracle 3 using '//labserver1.oraclebuffer.com:1521/orpcdb1' 4 ; Database link created.---// ---// validate the remote database link //--- ---// SQL> select name,cdb from v$database@remote_seed_link; NAME CDB --------------- --- ORPCDB1 YES SQL> select con_id,name,open_mode from v$pdbs@remote_seed_link where name='PDB$SEED'; CON_ID NAME OPEN_MODE ---------- --------------- ---------- 2 PDB$SEED READ ONLY
We have created a database link from the local container database (ORPCDB2) to the remote container database (ORPCDB1). 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_orpcdb1.xml', pdb_name => 'pdb$seed@REMOTE_SEED_LINK');
PL/SQL procedure successfully completed.
SQL> !ls -lrt /home/oracle/seed_orpcdb1.xml
-rw-r--r-- 1 oracle dba 5344 Apr 29 01:04 /home/oracle/seed_orpcdb1.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;
NAME
------------------------------------------------------------
/data/oracle/orpcdb1/pdbseed/system01.dbf
/data/oracle/orpcdb1/pdbseed/sysaux01.dbf
/data/oracle/orpcdb1/pdbseed/users01.dbf
SQL> select name from v$tempfile@REMOTE_SEED_LINK where con_id=2;
NAME
------------------------------------------------------------
/data/oracle/orpcdb1/pdbseed/temp01.dbf
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@labserver2 ~]$ scp oracle@labserver1:/data/oracle/orpcdb1/pdbseed/*.dbf /data/oracle/orpcdb2/pdbseed/
oracle@labserver1's password:
sysaux01.dbf 100% 220MB 44.0MB/s 00:05
system01.dbf 100% 225MB 32.1MB/s 00:07
temp01.dbf 100% 62MB 20.7MB/s 00:03
users01.dbf 100% 500MB 38.5MB/s 00:13
[oracle@labserver2 ~]$
Now, we can recreate the seed pluggable database in local container database (ORPCDB2) 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 //---
---//
SQL> create pluggable database "pdb$seed" using '/home/oracle/seed_orpcdb1.xml'
2 source_file_name_convert=('/data/oracle/orpcdb1/pdbseed/','/data/oracle/orpcdb2/pdbseed/')
3 NOCOPY
4 TEMPFILE REUSE
5 ;
Pluggable database created.
---//
---// validate PDB$SEED creation //---
---//
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 CDB2_PDB_1 READ WRITE NO
4 CDB2_PDB_2 READ WRITE NO
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;
Session altered.
---//
---// READ-ONLY open is not allowed for the first time //---
---//
SQL> alter pluggable database PDB$SEED open read only;
alter pluggable database PDB$SEED open read only
*
ERROR at line 1:
ORA-65085: cannot open pluggable database in read-only mode
---//
---// open PDB$SEED in READ-WRITE mode for dictionary synchronization //---
---//
SQL> alter pluggable database PDB$SEED open read write;
Pluggable database altered.
---//
---// put PDB$SEED back in READ-ONLY mode after dictionary synchronization //---
---//
SQL> alter pluggable database PDB$SEED close;
Pluggable database altered.
SQL> alter pluggable database PDB$SEED open read only;
Pluggable database altered.
---//
---// validate PDB$SEED state //---
---//
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 CDB2_PDB_1 READ WRITE NO
4 CDB2_PDB_2 READ WRITE NO
---//
---// reset _oracle_script to FALSE //---
---//
SQL> alter session set "_oracle_script"=false;
Session altered.
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 article, we have explored different methods available for restoring or recreating a seed pluggable database (PDB$SEED) in the event of seed database being in the UNUSABLE state. It is always recommended to take periodic backup of the seed pluggable database even though it is just a template for creating other pluggable databases. In the presence of a VALID backup, the seed pluggable database restoration/recovery becomes hassle free and straight forward. However, we still have ways (as discussed throughout this article) to recreate the seed pluggable database in the absence of a VALID backup.
Start the discussion at forums.toadworld.com