Wednesday, August 7, 2013

How to create a PDB from a Non-CDB using DBMS_PDB package

To test this scenario I have recently created using DBCA a non container database named ORCL. CDB001 and CDBTEST are instead two container databases.
[oracle@vsi08devpom ~]$ ps -ef|grep smon
oracle    4081     1  0 Jul15 ?        00:01:30 ora_smon_CDB001
oracle   11394     1  0 10:20 ?        00:00:00 ora_smon_ORCL
oracle   12586  9609  0 10:46 pts/0    00:00:00 grep smon
oracle   16455     1  0 Jul17 ?        00:01:34 ora_smon_CDBTEST
I want to consolidate this database plugging it into a container database.
In this scenario I'm going to use the DBMS_PDB package to create the XML file with the metadata of ORCL database.
Before using the DBMS_PDB.DESCRIBE procedure, the ORCL database needs to be in READ ONLY mode.
[oracle@vsi08devpom ~]$ export ORACLE_SID=ORCL
[oracle@vsi08devpom ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Aug 7 10:47:29 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@ORCL> show con_name;

CON_NAME
------------------------------
Non Consolidated
SQL@ORCL> select name, open_mode from V$DATABASE;

NAME   OPEN_MODE
--------- --------------------
ORCL   READ WRITE

SQL@ORCL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL@ORCL> startup mount;
ORACLE instance started.

Total System Global Area  471830528 bytes
Fixed Size      2289688 bytes
Variable Size    264245224 bytes
Database Buffers   197132288 bytes
Redo Buffers      8163328 bytes
Database mounted.
SQL@ORCL> alter database open read only;

Database altered.
SQL@ORCL> begin
  2  dbms_pdb.describe( pdb_descr_file => '/opt/app/oracle/oradata/orcl.xml');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL@ORCL> host ls /opt/app/oracle/oradata/orcl*
/opt/app/oracle/oradata/orcl.xml

SQL@ORCL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL@ORCL> exit
Now connect to the container database. In my case I want to plug ORCL database into the CDBTEST container. It currently contains 4 pluggable databases.
[oracle@vsi08devpom ~]$ export ORACLE_SID=CDBTEST
[oracle@vsi08devpom ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Aug 7 11:07:12 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@CDBTEST> show con_name;

CON_NAME
------------------------------
CDB$ROOT

SQL@CDBTEST> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDBTEST1         READ WRITE
PDBTEST2         READ WRITE
PDBTEST3         READ WRITE
PDB003          READ ONLY
Here is the situation of the directories for the CDBTEST container database:
[oracle@vsi08devpom ~]$ cd /opt/app/oracle/oradata/CDBTEST/
[oracle@vsi08devpom CDBTEST]$ ll
total 3459784
-rw-r----- 1 oracle oinstall   17973248 Aug  7 11:13 control01.ctl
drwxr-x--- 2 oracle oinstall       4096 Aug  1 14:54 PDB003
drwxr-x--- 2 oracle oinstall       4096 Jul 17 11:25 pdbseed
drwxr-x--- 2 oracle oinstall       4096 Jul 17 13:16 PDBTEST1
drwxr-x--- 2 oracle oinstall       4096 Jul 17 13:17 PDBTEST2
drwxr-x--- 2 oracle oinstall       4096 Jul 17 13:18 PDBTEST3
-rw-r----- 1 oracle oinstall   52429312 Aug  7 11:12 redo01.log
-rw-r----- 1 oracle oinstall   52429312 Aug  7 00:16 redo02.log
-rw-r----- 1 oracle oinstall   52429312 Aug  7 06:00 redo03.log
-rw-r----- 1 oracle oinstall 1310728192 Aug  7 11:12 sysaux01.dbf
-rw-r----- 1 oracle oinstall  828383232 Aug  7 11:12 system01.dbf
-rw-r----- 1 oracle oinstall   63971328 Aug  7 11:01 temp01.dbf
-rw-r----- 1 oracle oinstall 1205870592 Aug  7 11:12 undotbs01.dbf
-rw-r----- 1 oracle oinstall    5251072 Aug  7 06:05 users01.dbf
Now you can plug the ORCL database into the CDBTEST container database using the usual syntax (have a look at this post)
SQL@CDBTEST> create pluggable database ORCL using '/opt/app/oracle/oradata/orcl.xml' copy file_name_convert=('/opt/app/oracle/oradata/ORCL','/opt/app/oracle/oradata/CDBTEST/ORCL');

Pluggable database created.
The Oracle software copies under the directory /opt/app/oracle/oradata/CDBTEST/ORCL all the datafiles coming from /opt/app/oracle/oradata/ORCL location.
[oracle@vsi08devpom CDBTEST]$ ll
total 3459788
-rw-r----- 1 oracle oinstall   17973248 Aug  7 12:10 control01.ctl
drwxr-x--- 2 oracle oinstall       4096 Aug  7 11:49 ORCL
drwxr-x--- 2 oracle oinstall       4096 Aug  1 14:54 PDB003
drwxr-x--- 2 oracle oinstall       4096 Jul 17 11:25 pdbseed
drwxr-x--- 2 oracle oinstall       4096 Jul 17 13:16 PDBTEST1
drwxr-x--- 2 oracle oinstall       4096 Jul 17 13:17 PDBTEST2
drwxr-x--- 2 oracle oinstall       4096 Jul 17 13:18 PDBTEST3
-rw-r----- 1 oracle oinstall   52429312 Aug  7 12:10 redo01.log
-rw-r----- 1 oracle oinstall   52429312 Aug  7 00:16 redo02.log
-rw-r----- 1 oracle oinstall   52429312 Aug  7 06:00 redo03.log
-rw-r----- 1 oracle oinstall 1310728192 Aug  7 12:10 sysaux01.dbf
-rw-r----- 1 oracle oinstall  828383232 Aug  7 12:07 system01.dbf
-rw-r----- 1 oracle oinstall   63971328 Aug  7 12:03 temp01.dbf
-rw-r----- 1 oracle oinstall 1205870592 Aug  7 12:10 undotbs01.dbf
-rw-r----- 1 oracle oinstall    5251072 Aug  7 06:05 users01.dbf
A new pluggable database is created with a NEW status
SQL@CDBTEST> select pdb_id, pdb_name, dbid, status, creation_scn, con_id from CDB_PDBS;

    PDB_ID PDB_NAME     DBID STATUS        CREATION_SCN CON_ID
---------- ---------- ---------- ------------- ------------ ----------
  2 PDB$SEED   4063610283 NORMAL   217      1
  3 PDBTEST1   3064465721 NORMAL      1547881      1
  4 PDBTEST2   2395404598 NORMAL      1548944      1
  5 PDBTEST3   2434165039 NORMAL      1550036      1
  6 PDB003     1448206714 NORMAL      2744910      1
  7 ORCL       1350603571 NEW      3226095      1
It's still not the time to open the new PDB.
You need first to execute, while connected to the new pluggable database, the script $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql:
[oracle@vsi08devpom CDBTEST]$ ll /opt/app/oracle/product/12.1.0/db_1/rdbms/admin/noncdb_to_pdb.sql 
-rw-r--r-- 1 oracle oinstall 19191 Apr 15 22:27 /opt/app/oracle/product/12.1.0/db_1/rdbms/admin/noncdb_to_pdb.sql

SQL@CDBTEST> alter session set container=ORCL;

Session altered.

SQL@CDBTEST> @/opt/app/oracle/product/12.1.0/db_1/rdbms/admin/noncdb_to_pdb.sql
SQL@CDBTEST> SET SERVEROUTPUT ON
SQL@CDBTEST> SET FEEDBACK 1
SQL@CDBTEST> SET NUMWIDTH 10
...
...
...
SQL@CDBTEST> -- leave the PDB in the same state it was when we started
SQL@CDBTEST> 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@CDBTEST> 
SQL@CDBTEST> WHENEVER SQLERROR CONTINUE;
SQL@CDBTEST>
Now you can open the new ORCL pluggable database.
SQL@CDBTEST> show con_name;

CON_NAME
------------------------------
ORCL
SQL@CDBTEST> alter database open;

Database altered.
The database is so available and ready to be used.
SQL@CDBTEST> select pdb_id, pdb_name, dbid, status, creation_scn, con_id from CDB_PDBS;

    PDB_ID PDB_NAME     DBID STATUS        CREATION_SCN CON_ID
---------- ---------- ---------- ------------- ------------ ----------
  2 PDB$SEED   4063610283 NORMAL   217      1
  3 PDBTEST1   3064465721 NORMAL      1547881      1
  4 PDBTEST2   2395404598 NORMAL      1548944      1
  5 PDBTEST3   2434165039 NORMAL      1550036      1
  6 PDB003     1448206714 NORMAL      2744910      1
  7 ORCL       1350603571 NORMAL      3226095      1
That's all.

8 comments:

oakleyses said...

oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet

oakleyses said...

air max, hollister, true religion outlet, nike blazer, louboutin, ray ban sunglasses, polo ralph lauren, michael kors, true religion jeans, sac guess, sac longchamp, hogan outlet, ralph lauren, vans pas cher, sac louis vuitton, air max pas cher, nike free pas cher, nike free, air max, mulberry, nike roshe run, sac burberry, hollister, vanessa bruno, louis vuitton, lululemon, michael kors pas cher, oakley pas cher, air jordan, ray ban pas cher, new balance pas cher, polo lacoste, converse pas cher, north face, sac louis vuitton, michael kors, sac hermes, nike tn, timberland, louis vuitton uk, longchamp, true religion jeans, nike air max, air force, north face

oakleyses said...

abercrombie and fitch, instyler, ghd, bottega veneta, ugg boots, jimmy choo outlet, soccer shoes, ugg pas cher, herve leger, beats by dre, birkin bag, abercrombie and fitch, north face jackets, soccer jerseys, mont blanc, rolex watches, lululemon outlet, celine handbags, nike roshe run, nike trainers, giuseppe zanotti, hollister, wedding dresses, nike huarache, mcm handbags, vans shoes, chi flat iron, babyliss pro, north face outlet, nike roshe, ugg australia, ugg, marc jacobs, barbour, nfl jerseys, p90x, new balance shoes, asics running shoes, ferragamo shoes, mac cosmetics, insanity workout, uggs outlet, reebok outlet, longchamp, valentino shoes

oakleyses said...

converse, air max, gucci, canada goose, juicy couture outlet, canada goose, wedding dresses, moncler, ralph lauren, lancel, montre homme, moncler, louboutin, oakley, karen millen, vans, coach outlet store online, air max, canada goose jackets, ugg, hollister clothing store, louis vuitton, baseball bats, hollister, rolex watches, juicy couture outlet, iphone 6 cases, canada goose uk, canada goose outlet, ugg, moncler, moncler outlet, timberland boots, hollister, supra shoes, moncler, canada goose, converse shoes, toms shoes, moncler, moncler, canada goose, ugg boots, ray ban, parajumpers, canada goose

oakleyses said...

jordan shoes, christian louboutin, uggs outlet, michael kors outlet online, uggs on sale, louis vuitton outlet, louis vuitton outlet, louis vuitton, ray ban sunglasses, replica watches, christian louboutin uk, chanel handbags, michael kors outlet online, uggs outlet, longchamp outlet, nike air max, michael kors outlet, burberry handbags, tiffany and co, polo outlet, nike free, nike air max, ugg boots, oakley sunglasses, ray ban sunglasses, michael kors outlet online, oakley sunglasses, christian louboutin outlet, longchamp outlet, prada handbags, gucci handbags, prada outlet, oakley sunglasses wholesale, michael kors outlet, oakley sunglasses, kate spade outlet, christian louboutin shoes, louis vuitton outlet, tory burch outlet, ugg boots, michael kors outlet online, burberry outlet, cheap oakley sunglasses, louis vuitton, ray ban sunglasses, nike outlet, longchamp outlet

oakleyses said...

sac vanessa bruno, new balance, vans pas cher, ray ban uk, nike blazer pas cher, true religion outlet, michael kors outlet, true religion outlet, replica handbags, polo lacoste, oakley pas cher, coach purses, hollister uk, abercrombie and fitch uk, nike free uk, north face uk, louboutin pas cher, polo ralph lauren, hollister pas cher, nike air max uk, michael kors pas cher, nike air max, true religion jeans, timberland pas cher, nike air max uk, coach outlet, air max, michael kors, jordan pas cher, sac hermes, north face, lululemon canada, coach outlet store online, nike roshe, sac longchamp pas cher, nike air force, mulberry uk, hogan outlet, ralph lauren uk, longchamp pas cher, michael kors, converse pas cher, burberry pas cher, nike roshe run uk, true religion outlet, kate spade, nike free run, nike tn, ray ban pas cher, guess pas cher

oakleyses said...

asics running shoes, babyliss, soccer jerseys, hermes belt, reebok outlet, ipad cases, oakley, iphone cases, soccer shoes, iphone 5s cases, nfl jerseys, north face outlet, abercrombie and fitch, ghd hair, vans outlet, iphone 6 cases, hollister, nike roshe run, wedding dresses, mac cosmetics, lululemon, new balance shoes, jimmy choo outlet, instyler, giuseppe zanotti outlet, p90x workout, s6 case, chi flat iron, iphone 6s cases, longchamp uk, baseball bats, mcm handbags, iphone 6 plus cases, bottega veneta, ferragamo shoes, timberland boots, mont blanc pens, insanity workout, nike air max, nike trainers uk, herve leger, nike huaraches, celine handbags, north face outlet, beats by dre, iphone 6s plus cases, valentino shoes, ralph lauren, hollister clothing, louboutin

oakleyses said...

moncler uk, louis vuitton, thomas sabo, wedding dresses, barbour, moncler, gucci, montre pas cher, supra shoes, hollister, barbour uk, nike air max, karen millen uk, pandora uk, moncler, canada goose uk, coach outlet, ugg, juicy couture outlet, swarovski, canada goose, louis vuitton, moncler outlet, louis vuitton, hollister, ugg,ugg australia,ugg italia, canada goose outlet, replica watches, pandora jewelry, ugg,uggs,uggs canada, moncler, canada goose outlet, ugg pas cher, louis vuitton, juicy couture outlet, swarovski crystal, louis vuitton, canada goose, pandora charms, canada goose outlet, links of london, marc jacobs, lancel, converse, converse outlet, toms shoes, doudoune moncler, moncler, pandora jewelry, canada goose jackets, vans, canada goose