Advertisements
Skip to content
October 2, 2015 / Shivananda Rao P

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.

 

1. Creating a PDB using Seed

 

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.

 

Creating PDB from a non-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, 2013, 2014, 2015. 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: