Oracle 12c New Features

Online Oracle Training

LESSON 09

How plugging a Non-CDB into a CDB

Assuming that we have a non-CDB database named "mydbs." We want to move it to mycdb and change it to a pluggable database. One way to do this, it is to use the Export/Import DataPump tool. The other method is using the DBMS_PDB package. You need to execute this package in the non-CDB mydbs to generate XML file to describe the metadata on your non-CDB database. Then you use the XML file to create your pluggable database (pdb_mydbs) in mycdb. At last, you need to convert the plugged non-CDB to a pluggable database by running the noncdb_to_pdb.sql file. This sql file will delete unnecessary metadata from PDB SYSTEM tablespace.

Login to the non-CDB mydbs database and change the database open mode to read only. Then execute the DBMS_PDB.describe procedure to create the XML file.

# sqlplus /nolog

SQL> connect / as sysdba

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database open read only;

SQL> execute dbms_pdb.describe ("/oradata/mydbs/xmlmydbs.xml");

SQL> shutdown immediate;

Now, use the xmlmydbs.xml file to create your new pluggable database (pdb_mydbs) at your multitenant architecture database (mycdb).

Login to the mycdb multitenant architecture database

# cd ~

# . ./.profile

# sqlplus /nolog

SQL> connect / as sysdba

--Just to make sure where you are

SQL> show parameter name

        --or--

Get the container name

SQL> show con_name

Try to create pluggable database using the non-CDB database.

Notice that you should remove the temporary tablespace from non-CDB tablespace since it will be re-generated by the "create pluggable database" command.

Remove the TEMP data file.

SQL>host;

# rm /oradata/mydbs/temp01.dbf

--or--

SQL>! rm /oradata/mydbs/temp01.dbf

Start creating pluggable database using your xml file.

SQL> CREATE PLUGGABLE DATABASE pdb_mydbs

   2   USING "/oradata/mydbs/xmlmydbs.xml" NOCOPY;

Pluggable database created.

Notice that you use NOCOPY option if you have planned not to move data files from one location to another.

Use the netca tool to create a net service name for the pdb_mydbs database which is not pluggable and name it pdb_mydbs

Login to the pdb_mydbs as sysdba

# sqlplus /nolog

SQL> connect sys/mypass@ pdb_mydbs as sysdba

Get the container name

SQL> show con_name

Execute the noncdb_to_pdb.sql file. It is located at $ORACLE_HOME/rdbms/admin directory. This file will delete unnecessary metadata from PDB SYSTEM tablespace.

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

Open your new pluggable database (pdb_mydbs).

Now, query the V$PDBS view.

SQL> SELECT con_id, name, open_mode, dbid FROM v$pdbs;

Notice that the open_mode value for the pdb_mydbs pluggable database is "MOUNTED."

Change the pdb_mydbs open mode from "mounted"  to "read write."

SQL> ALTER PLUGGABLE DATABASE pdb_mydbs OPEN;

Now, query the V$PDBS view.

SQL> SELECT con_id, name, open_mode, dbid FROM v$pdbs;

Notice that the open_mode value for the pdb_mydbs pluggable database is "READ WRITE "

You can use EasyConnect to the pdb_mydbs pluggable database as sysdba.

SQL> connect sys/mypass@localhost:1521/ pdb_mydbs as sysdba

Get the container name

SQL> show con_name

Good Luck!