Sunday, June 30, 2013

Moving a Non Container Database (Non CDB) to Container Database (CDB, as PDB)

In this post, I discuss moving a regular Non CDB Database to CDB architecture. DBMS_PDB.DESCRIBE metadata procedure is used for the same purpose. Following analogy applies to the post,
  
  ORA12C - Non CDB Database (Instance)
  CONDB - CDB Database (Instance)

Please follow steps below to move a Non CDB to CDB environment as PDB,


1. Open the Non CDB (ORA12C) in READ-ONLY Mode to keep it transactionally consistent.


SQL> SHUTDOWN IMMEDIATE

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area  668082176 bytes

Fixed Size    2291952 bytes
Variable Size  507512592 bytes
Database Buffers  155189248 bytes
Redo Buffers    3088384 bytes
Database mounted.
SQL> ALTER DATABASE OPEN READ ONLY;
Database altered.

SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;


NAME  OPEN_MODE

--------- --------------------
ORA12C  READ ONLY

2. Connect to the Non-CDB Database(ORA12C) and execute DBMS_PDB.DESCRIBE procedure to generate an xml file that will help to build the Pluggable Database on the CDB Database(CONDB)

SQL> BEGIN
  DBMS_PDB.DESCRIBE(
    pdb_descr_file => '/backup/expdp/ora12c.xml');
END;
/
  2    3    4    5  
PL/SQL procedure successfully completed.

3. Shutdown the Non CDB (ORA12C) Database, and login to CDB Database (CONDB) as SYS user


On Non CDB (ORA12C)

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.

ORACLE instance shut down.

On CDB (CONDB)
[oracle@ora12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Jun 30 17:07:00 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

4. On CDB (CONDB), Create the PDB Database by mapping the XML file that Is generated in step 2

SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;

NAME  OPEN_MODE
--------- --------------------
CONDB  READ WRITE

SQL> CREATE PLUGGABLE DATABASE ORA12C USING '/backup/expdp/ora12c.xml'
  COPY
  FILE_NAME_CONVERT = ('/dbs/ORA12C/', '/dbs/CONDB/ORA12C/');  2    3  
Pluggable database created.

From the parameter FILE_NAME_CONVERT,  "/dbs/ORA12C/" is the source Database files location (Non CDB), and "dbs/CONDB/ORA12C/" is the target database files location (CDB/PDB). When we execute the above command, Oracle copies files from Non CDB's location to CDBs and plugs the database into Container, and creates it. Following query gives the status of the new PDB,

SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDB       READ WRITE
SALESPDB       READ WRITE
PDB1       READ WRITE
ORA12C             MOUNTED

5. Connect to the new PDB that has been created on CDB Database (CONDB) as SYS, and execute the "noncdb_to_pdb.sql" script.

SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;

NAME  OPEN_MODE
--------- --------------------
CONDB  READ WRITE

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

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

During the execution of script, Oracle restarts the PDB several times to complete the activity.

Sample Script output:

SQL> SET SERVEROUTPUT ON
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL> 
SQL> WHENEVER SQLERROR EXIT;
SQL> 
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01403: no data found"
DOC>   error if we're not in a PDB.
DOC>   This script is intended to be run right after plugin of a PDB,
DOC>   while inside the PDB.
DOC>#######################################################################
DOC>###################################################################
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
SQL> alter session set container = "&pdbname";

Session altered.

SQL> 
SQL> -- leave the PDB in the same state it was when we started
SQL> BEGIN
  2    execute immediate '&open_sql &restricted_state';
  3  EXCEPTION
  4    WHEN OTHERS THEN
  5    BEGIN
  6      IF (sqlcode <> -900) THEN
  7        RAISE;
  8      END IF;
  9    END;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL> 
SQL> WHENEVER SQLERROR CONTINUE;
SQL> SPOOL OFF;
SQL> !pwd
/home/oracle

6. Open the PDB in READ WRITE Mode, confirm the status, and make it available for Client Operations,  it is also advisable to take a backup.

SQL> ALTER PLUGGABLE DATABASE ORA12C OPEN;

Pluggable database altered.

SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;

NAME                           OPEN_MODE
------------------------------ ----------
ORA12C                         READ WRITE
1 row selected.

Hope it helps. Please stay tuned, add me to your G+ Circles!! Your feedback always helps me and motivates me to blog more, please leave your valuable comment that is always a pleasure to read.

Many Thanks,

Regards