How to Plug non-CDB database to CDB database?

How to Plug non-CDB database oracle11g to CDB database?

This article is in continuation of my previous article i.e. Upgrade Oracle Database 11g to 12c .
One can use below listing without referring to previous notes. Just make sure here I have used my non-CDB database name as ORCL11G because it was migrated from 11g to 12c without changing SID of the database.

NOTE: ORCL11G instance is now our new 12c upgraded database. We haven't change SID while upgrading our database. 

There are several methods to migrate non-CDB database to PDB database.

1) Clone a Remote Non-CDB
2) Using DBMS_PDB
3) Using Data Pump (expdb, impdp)
4) Using Replication

Here I will use DBMS_PDB to migrate non-CDB to PDB.

Setp 1) Generate .xml file.
In this step, we will generate PDBOrcl11g.xml file, which we will used further to create PDB database.
- Connect to your non-CDB database. [In our case it is ORCL11G.]
- Shutdown the database
- Startup in read only mode
- Run DBMS_PDB.DESCRIBE to generate PDBOrcl11g.xml

Below is the listing for the same.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup open read only;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2924928 bytes
Variable Size             520097408 bytes
Database Buffers         1073741824 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.
SQL>
SQL> BEGIN
  2  DBMS_PDB.DESCRIBE(pdb_descr_file => '/tmp/PDBOrcl11g.xml');
  3  END;
  4  /
PL/SQL procedure successfully completed.
SQL>

Step 2) Connect to an existing CDB and create a new PDB

Connect to an existing CDB and create a new PDB using the file describing the non-CDB database. Must use the FILE_NAME_CONVERT parameter to convert the existing files to the new location.

As you see in below screenshot, we are connected to “orcl” CDB container. “orcl” CDB container is already having PDBORCL as one PDB. We will add our recently upgraded database “orcl11g” in this CDB container as “PDBORCL11g”.

Your can refer my previous article how to upgrade 11g to 12c. Click Here

Step 3) Create pluggable database PDBORCL11g

Create directory pdborcl11g under “/mnt/devops_0/Oracle/oradata/orcl/” path, where all PDB’s data files be located in.

Now, run below command to create new PDB named as PDBORCL11G

CREATE PLUGGABLE DATABASE PDBORCL11G USING '/tmp/PDBOrcl11g.xml' COPY FILE_NAME_CONVERT = ('/mnt/devops_0/Oracle/oradata/orcl11g/ORCL11G/','/mnt/devops_0/Oracle/oradata/orcl/pdborcl11g/');

As you can see in above screenshot newly created PDB i.e. PDBORCL11G is on mounted state.

Step 4) Run script noncdb_to_pdb.sql

Now, Switch to the PDB container and run the "$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql" script to clean up the new PDB, removing any items that should not be present in a PDB.

1) ALTER SESSION SET CONTAINER=PDBORCL11G;
2) @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

Above script output is attached here.


Step 5) Startup the PDB and check the open mode.

Now, Start the PDB and check the open mode.

1) ALTER SESSION SET CONTAINER=PDBORCL11G;
2) ALTER PLUGGABLE DATABASE OPEN;

Here, we are done with migration from non-CDB to PDB of Oracle Database 12c.

Step 6) Check all PDBs and PMON Process.
Now check all PDB status from the root container


SQL> alter session set container=cdb$root;
Session altered.
SQL>
SQL> select con_id,dbid, name, open_mode from v$containers;

CON_ID       DBID    NAME                                OPEN_MODE
----------      ----------     ------------------------------  ----------
         1   1441782042   CDB$ROOT                      READ WRITE
         2   2980200325   PDB$SEED                       READ ONLY
         3       85573530   PDBORCL                         READ WRITE
         4   1021306567   PDBORCL11G                  READ WRITE

4 rows selected.
SQL>


Check the Database processes

-bash-4.1$ ps -ef | grep pmon
db1212   10033  8449  0 12:46 pts/3    00:00:00 grep pmon
db1212   12145     1  0 Jun08 ?        00:00:37 ora_pmon_orcl
-bash-4.1$

Our new PDBORCL11G is now in read write mode. Now you can connect to your new PDB for your regular operations.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert