Creating Pluggable Databases – Part 2
As discussed in my previous post Creating Pluggable Databases – Part I, the different ways to create PDB are as mentioned below.
2. Creating a PDB from another PDB or non-CDB (Cloning a PDB or non-CDB)
3. Creating a PDB by plugging in a an unplugged PDB
4. Creating PDB from a non-CDB
Clicking on each of these links would take you to the respective detailed method. In this post, I’ve discussed in the remaining 2 methods (“Creating PDB by plugging in an unplugged PDB” and “Creating a PDB from a non-CDB”) of creating a PDB in detailed.
Creating a PDB by plugging in a an unplugged PDB:
Environment:
Hostname : ora12c-1 Source CDB Name : MAINCDB Source PDB Name : TRANSPDB Target CDB Name : ORACDB Target PDB Name : orapdb4
So here I have Transpdb as the PDB which is currently plugged into the CDB named MAINCDB. What I do is, I unplug TRANSPDB from MAINCDB and plug it into ORACDB as ORAPDB4 database.
I’m using a different name as ORAPDB4 for the new PDB. I could also use the same name as TRANSPDB to plug into ORACDB.
So, let’s unplug TRANSPDB from MAINCDB. Connect to the ROOT container of MAINCDB, close the PDB to be unplugged and unplug it describing it in the XML file.
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT
SQL> select status,instance_name from v$instance; STATUS INSTANCE_NAME ------ ---------------------- OPEN maincdb SQL> select name,open_mode from v$pdbs; NAME OPEN_MODE ----------- -------------- PDB$SEED READ ONLY TRANSPDB READ WRITE
Let me close TRANSPDB and unplug it into the XML file called TRANSPDB.XML
SQL> alter pluggable database TRANSPDB close immediate; Pluggable database altered. SQL> alter pluggable database TRANSPDB unplug into '/u03/TRANSPDB.xml'; Pluggable database altered.
If you are unsure about the location of the datafiles of TRANSPDB, the details can be obtained from the XML file.
[oracle@ora12c-1 ~]$ grep dbf /u03/TRANSPDB.xml <path>/u01/app/oracle/oradata/maincdb/transpdb/system01.dbf</path> <path>/u01/app/oracle/oradata/maincdb/transpdb/sysaux01.dbf</path> <path>/u01/app/oracle/oradata/maincdb/transpdb/transpdb_temp01.dbf</path> <path>/u01/app/oracle/oradata/maincdb/transpdb/SAMPLE_SCHEMA_users01.dbf</path> <path>/u01/app/oracle/oradata/maincdb/transpdb/example01.dbf</path> <path>/u01/app/oracle/oradata/maincdb/transpdb/myts01.dbf</path> [oracle@ora12c-1 ~]$
Create necessary directory for the new PDB to be plugged into ORACDB.
[oracle@ora12c-1 ~]$ mkdir -p /u01/app/oracle/oradata/oracdb/orapdb4 [oracle@ora12c-1 ~]$
Now connect to ORACDB as ROOT container and run the “CREATE PLUGGABLE DATABASE using XML” command.
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> select status,instance_name from v$instance; STATUS INSTANCE_NAME ------------ ---------------- OPEN oracdb
SQL> create pluggable database orapdb4 using '/u03/TRANSPDB.xml'
2 copy file_name_convert=('/u01/app/oracle/oradata/maincdb/transpdb/','/u01/app/oracle/oradata/oracdb/orapdb4/');
Pluggable database created.
Here I’m specifying the XML file describing the TRANSPDB to create the new PDB. Also, there are some additional clauses that are used.
COPY: This clause is to be used if you would like oracle to copy the files from the Source PDB location to the Target PDB location. In my case, I’m copying the datafiles of TRANSPDB from “/u01/app/oracle/oradata/maincdb/transpdb/” location to “/u01/app/oracle/oradata/oracdb/orapdb4/”
NOCOPY: If you do not want Oracle to copy the files and would like to have the current location of data files of source PDB as the location for the new PDB, then make use of this option.
MOVE: If you would like Oracle to move the datafiles from the location of the source PDB to the Target PDB location, then use this.
FILE_NAME_CONVERT=When using a copy clause, you need to specify the source and destination file location and this can be done using the FILE_NAME_CONVERT clause.
AS CLONE: This clause is to be used when an existing PDB was already created with the unplugged PDB and a new PDB under the same CDB is now being created using the same unplugged PDB.
SOURCE_FILE_NAME_CONVERT: If the location of the files in the XML are different from that what you have for the source files, then this caluse is to be used. Let’s say that you have the source PDB on host1 and it’s datafiles are at location “/u01/sourcePDB/” and the same is recorded in the XML file. On the target server, you copy these files manually to say “/u03/targetPDB”. But when you use the XML file to create the PDB, the location of the files in the XML do not match or are not accurate as the files still point to location “/u01/sourcePDB/”. In such cases, you can use the SOURCE_FILE_NAME_CONVERT clause which is used mainly for the source PDB files if the XML file does not describe the exact current location of the source PDB files.
Now I see that ORAPDB4 is the NEW PDB plugged into ORACDB.
SQL> select pdb_id,pdb_name,status from cdb_pdbs order by pdb_id; PDB_ID PDB_NAME STATUS ------ ----------- ------------- 2 PDB$SEED NORMAL 3 ORAPDB1 NORMAL 4 ORAPDB2 NORMAL 5 ORAPDB3 NORMAL 6 ORAPDB4 NEW
SQL> select name,open_mode from v$pdbs; NAME OPEN_MODE ----------- ------------- PDB$SEED READ ONLY ORAPDB1 MOUNTED ORAPDB2 MOUNTED ORAPDB3 MOUNTED ORAPDB4 MOUNTED
Open the newly created PDB in READ WRITE mode.
SQL> alter pluggable database orapdb4 open; Pluggable database altered. SQL> select name,open_mode from v$pdbs; NAME OPEN_MODE ----------------- --------------- PDB$SEED READ ONLY ORAPDB1 MOUNTED ORAPDB2 MOUNTED ORAPDB3 MOUNTED ORAPDB4 READ WRITE
If the PDB is created using a COPY clause, and the data files of the unplugged source PDB are still in tact in the source location, then you could use the same procedure as above to plug it back to it’s CDB.
Environment:
Hostname : ora12c-1 NON-CDB Name : noncdb1 Target CDB Name : oracdb Target PDB Name : orapdb5
A non-CDB is a normal database and does not has any containers associated with it. Before we proceed, shutdown the NON-CDB and open it in READ ONLY mode.
SQL> select d.con_id,i.status,i.instance_name,d.cdb,d.open_mode from v$database d,v$instance i; CON_ID STATUS INSTANCE_NAME CDB OPEN_MODE ------ --------- --------------- ------- -------------------- 0 OPEN noncdb1 NO READ ONLY
Using the DBMS_PDB.DESCRIBE package, you can create an XML file that describes the locations of the datafiles of the non-cdb.
SQL> BEGIN 2 DBMS_PDB.DESCRIBE( 3 pdb_descr_file => '/u02/oradata/noncdb/noncdb1.xml'); 4 END; 5 / PL/SQL procedure successfully completed.
SQL> !ls -lrt /u02/oradata/noncdb/noncdb1.xml -rw-r--r-- 1 oracle oinstall 3974 Sep 21 22:21 /u02/oradata/noncdb/noncdb1.xml
Now shutdown the non-CDB to plug into the CDB oracdb.
SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down.
The target CDB has 1 existing PDB called orapdb1. Now let’s proceed to plug in the NON-CDB as a PDB.
SQL> select d.con_id,i.status,i.instance_name,d.cdb,d.open_mode from v$database d,v$instance i; CON_ID STATUS INSTANCE_NAME CDB OPEN_MODE ------ ---------- ---------------- ------ -------------------- 0 OPEN oracdb YES READ WRITE SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED -------- ------------ ------------- ------------ 2 PDB$SEED READ ONLY NO 3 ORAPDB1 MOUNTED
Use the “Create Pluggable database using” XML file statement to plug in the non-CDB.
I’m using here the NOCOPY cluase as I would like to retain the current location of the datafiles of the non-CDB as use the same after it’s plugged into the CDB. I do not want them on a different location.
SQL> create pluggable database orapdb5 using '/u02/oradata/noncdb/noncdb1.xml' 2 nocopy tempfile reuse; Pluggable database created.
The new PDB orapdb5 is created, in other words, the non-CDB is now plugged into the CDB as a PDB. But, it doesn’t ends here.
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED -------- --------------- ------------ ------------ 2 PDB$SEED READ ONLY NO 3 ORAPDB1 MOUNTED 4 orapdb5 MOUNTED
Do not OPEN the new PDB. Connect to this new PDB container and run the “noncdb_to_pdb.sql” script located at $ORACLE_HOME/rdbms/admin. This script OPENS the PDB in restricted mode, performs certain changes and then closes the PDB.
SQL> alter session set container=orapdb5; Session altered. SQL> @?/rdbms/admin/noncdb_to_pdb.sql
You can see that ORAPDB5 is not OPENED. Open the PDB and query it.
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED -------- ------------ ------------ ------------- 2 PDB$SEED READ ONLY NO 3 ORAPDB1 MOUNTED 4 orapdb5 MOUNTED
SQL> alter pluggable database orapdb5 open; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED -------- -------------- -------------- ------------- 2 PDB$SEED READ ONLY NO 3 ORAPDB1 MOUNTED 4 orapdb5 READ WRITE NO
SQL> alter session set container=orapdb5; Session altered.
You can see that the new PDB is using the datafiles that was at the source location as we used the NOCOPY clause while creating the PDB.
SQL> select file_name,status from dba_data_files; FILE_NAME STATUS ---------------------------------------------- ---------------------- /u02/oradata/noncdb/noncdb1/users01.dbf AVAILABLE /u02/oradata/noncdb/noncdb1/sysaux01.dbf AVAILABLE /u02/oradata/noncdb/noncdb1/system01.dbf AVAILABLE 3 rows selected.
COPYRIGHT
© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.
Shivananda Rao 
Leave a Reply