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.

No comments: