Wednesday, October 19, 2016

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

The Container database cdb12c originally has one pluggable database testpdb (as well as the seed PDB). We will see how we can plugin the 12C non-cdb database testdb12c 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.

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@server- testdb12c]$ sqlplus sys as sysdba

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.

SQL> BEGIN
DBMS_PDB.DESCRIBE(
pdb_descr_file => '/home/oracle/testdb12c.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) :=
  CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
  pdb_descr_file => '/home/oracle/testdb12c.xml',
  pdb_name       => 'testdb12c')
  WHEN TRUE THEN 'YES'
  ELSE 'NO'
  END;
  BEGIN
  DBMS_OUTPUT.PUT_LINE(compatible);
  END;
  /

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 testdb12c using '/home/oracle/testdb12c.xml'
nocopy
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 testdb12c 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 testpdb                              READ WRITE
         4 testdb12c                          MOUNTED

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

PDB_NAME                   DBID     CON_ID STATUS
-------------------- ---------- ---------- -------------
testpdb               571254526          1 NORMAL
PDB$SEED         4061785545       1 NORMAL
testdb12c            1141508778        1 NEW

SQL> ALTER SESSION SET CONTAINER =testdb12c;
Session altered.

SQL> show con_name

CON_NAME
------------------------------
testdb12c

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 testdb12c 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
-------------------- ---------- ---------- -------------
testpdb               571254526          1 NORMAL
PDB$SEED       4061785545         1 NORMAL
testdb12c            1141508778        1 NORMAL

3 rows selected.

Connect to the newly plugged-in PDB testdb12c

[oracle@server- testdb12c]$ sqlplus sh/sh@localhost:1525/testdb12c

SQL> select count(*) from sales;

COUNT(*)
———-
918843

No comments:

Post a Comment