12c How to plugin a non-CDB database to a Container Database (CDB)

The Container database cdb12c originally has one pluggable database testdb1 (as well as the seed PDB). We will see how we can plugin the upgraded database testdb2 into the container database cdb12c.

Note that to plugin a non-CDB database into a CDB, the non-CDB database needs to be of version 12c as well. So existing 11g databases will need to be upgraded to 12c before they can be part of a 12c CDB.

Let us take a look at the steps involved.

The first thing we need to do is to connect to the non-CDB, and run the DBMS_PDB.DESCRIBE procedure to construct an XML file that contains the metadata which describes the non-CDB.

We need to run this procedure while the database is in a read only mode.

[oracle@orasql-001-dev backup]$ sqlplus sys as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 8 09:07:37 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  801701888 bytes

Fixed Size                  2293496 bytes

Variable Size             314573064 bytes

Database Buffers          478150656 bytes

Redo Buffers                6684672 bytes

Database mounted.

SQL> alter database open read only;

Database altered.

BEGIN

  DBMS_PDB.DESCRIBE(

    pdb_descr_file => '/home/oracle/testdb2.xml');

END;

/

PL/SQL procedure successfully completed.

Now shutdown and open the non-CDB in read write node.

We need to then check if the non-CDB is now ready for conversion into a Pluggable Database (PDD) which is going to be contained in the Container Database.

Connect to the CDB and run the DBMS_PDB.CHECK_PLUG_COMPATIBILITY package where we provide the location of the XML metadata file which was generated in the first step we executed.

SQL> SET SERVEROUTPUT ON

DECLARE

  compatible CONSTANT VARCHAR2(3) :=

SQL>   2    3      CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(

  4             pdb_descr_file => '/home/oracle/testdb2.xml',

  5             pdb_name       => 'TESTDB2')

  6      WHEN TRUE THEN 'YES'

  7      ELSE 'NO'

  8  END;

  9  BEGIN

10    DBMS_OUTPUT.PUT_LINE(compatible);

11  END;

12  /

NO

PL/SQL procedure successfully completed.

Next we shutdown the non-CDB and plug it in to the CDB by converting it to a PLUGGABLE DATABASE.

SQL> CREATE PLUGGABLE DATABASE testdb2 using '/home/oracle/testdb2.xml'

  2   nocopy

  3  tempfile reuse;

Pluggable database created.

The XML file accurately describes the current locations of the files of the PDB

Since we are using the existing 12c non-CDB datafiles to create the CDB pluggable database , we will use the NOCOPY option.

If we want, we can leave the existing 12c non-cdb database and clone that to create a pluggable database

We can see that the testdb2 database is now a PDB and is in a mounted state.

SQL> select CON_ID, NAME, OPEN_MODE from V$PDBS;

    CON_ID NAME                           OPEN_MODE

---------- ------------------------------ ----------

         2 PDB$SEED                       READ ONLY

         3 TESTDB1                        READ WRITE

         4 TESTDB2                        MOUNTED

SQL> select  PDB_NAME, DBID , CON_ID, STATUS  from CDB_PDBS;

PDB_NAME                   DBID     CON_ID STATUS

-------------------- ---------- ---------- -------------

TESTDB1               571254526          1 NORMAL

PDB$SEED             4061785545          1 NORMAL

TESTDB2              1141508778          1 NEW

SQL> ALTER SESSION SET CONTAINER =testdb2;

Session altered.

SQL> show con_name

CON_NAME

------------------------------

TESTDB2

Now run the ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql script. This script must be run before the PDB can be opened for the first time.

After the script has been run open the PDB testdb2 in READ WRITE mode.

Now check the status.

SQL> alter session SET CONTAINER =CDB$ROOT;

Session altered.

SQL>  show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> select  PDB_NAME, DBID , CON_ID, STATUS  from CDB_PDBS;

PDB_NAME                   DBID     CON_ID STATUS

-------------------- ---------- ---------- -------------

TESTDB1               571254526          1 NORMAL

PDB$SEED             4061785545          1 NORMAL

TESTDB2              1141508778          1 NORMAL

3 rows selected.

Connect to the newly plugged-in PDB testdb2

[oracle@orasql-001-dev testdb2]$ sqlplus sh/sh@localhost:1525/testdb2

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 8 11:04:11 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select count(*) from sales;

COUNT(*)
———-
918843