Database 12c: Convert Non-CDB to PDB


To get data from a Non-CDB to a PDB you have to do a convert of a Non-CDB to PDB or use Datapump or a tool for logical migration. The conversion process is similar to unplug and plug of a pdb.

I’vh tried the following ways, which are all possible:

  • 11.2.0.3 –> 12.1.0.1 (First upgrade to a Non-CDB and then convert)
  • 12.1.0.1 EE Non-CDB –> 12.1.0.1 EE
  • 12.1.0.1 SE Non-CDB –> 12.1.0.1 EE
  • 12.1.0.1 EE Non-CDB –> 12.1.0.1 SE
  • 12.1.0.1 SE Non-CDB –> 12.1.0.1 SE

Ok, let’s start.

1. Bring Non-CDB in a consistent state:

[bash]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 18 23:30:06 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> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size 2296280 bytes
Variable Size 692061736 bytes
Database Buffers 343932928 bytes
Redo Buffers 5595136 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

2. Create the export XML

SQL>
BEGIN
 DBMS_PDB.DESCRIBE(
 pdb_descr_file => '/opt/oracle/unplug/db12c1_convert.xml');
END;
/
PL/SQL procedure successfully completed.

Take a look into the XML File, check the last important part (bold marked):

<?xml version="1.0" encoding="UTF-8"?>
<PDB>
 <pdbname>DB12C1</pdbname>
 <cid>0</cid>
 <byteorder>1</byteorder>
 <vsn>202375168</vsn>
 <dbid>4126847348</dbid>
 <cdbid>4126847348</cdbid>
 <guid>E1CE01B6E8AB0B36E0430100007F25C7</guid>
 <uscnbas>1759919</uscnbas>
 <uscnwrp>0</uscnwrp>
 <rdba>4194824</rdba>
 <tablespace>
 <name>SYSTEM</name>
 <type>0</type>
 <tsn>0</tsn>
 <status>1</status>
 <issft>0</issft>
 <file>
 <path>/opt/oracle/app/oradata/DB12C1/datafile/o1_mf_system_8yjcbjpj_.dbf</path>
 <afn>1</afn>
 <rfn>1</rfn>
 <createscnbas>7</createscnbas>
 <createscnwrp>0</createscnwrp>
 <status>1</status>
 <fileblocks>99840</fileblocks>
 <blocksize>8192</blocksize>
 <vsn>202375168</vsn>
 <fdbid>4126847348</fdbid>
 <fcpsw>0</fcpsw>
 <fcpsb>1759918</fcpsb>
 <frlsw>0</frlsw>
 <frlsb>1720082</frlsb>
 <frlt>821131319</frlt>
 </file>
 </tablespace>
 <tablespace>
 <name>SYSAUX</name>
 <type>0</type>
 <tsn>1</tsn>
 <status>1</status>
 <issft>0</issft>
 <file>
 <path>/opt/oracle/app/oradata/DB12C1/datafile/o1_mf_sysaux_8yjc93j2_.dbf</path>
 <afn>3</afn>
 <rfn>3</rfn>
 <createscnbas>3922</createscnbas>
 <createscnwrp>0</createscnwrp>
 <status>1</status>
 <fileblocks>89600</fileblocks>
 <blocksize>8192</blocksize>
 <vsn>202375168</vsn>
 <fdbid>4126847348</fdbid>
 <fcpsw>0</fcpsw>
 <fcpsb>1759918</fcpsb>
 <frlsw>0</frlsw>
 <frlsb>1720082</frlsb>
 <frlt>821131319</frlt>
 </file>
 </tablespace>
 <tablespace>
 <name>TEMP</name>
 <type>1</type>
 <tsn>3</tsn>
 <status>1</status>
 <issft>0</issft>
 <bmunitsize>128</bmunitsize>
 <file>
 <path>/opt/oracle/app/oradata/DB12C1/datafile/o1_mf_temp_8yjcdrw0_.tmp</path>
 <afn>1</afn>
 <rfn>1</rfn>
 <createscnbas>1720182</createscnbas>
 <createscnwrp>0</createscnwrp>
 <status>1</status>
 <fileblocks>7680</fileblocks>
 <blocksize>8192</blocksize>
 <vsn>202375168</vsn>
 <autoext>1</autoext>
 <maxsize>4194302</maxsize>
 <incsize>80</incsize>
 </file>
 </tablespace>
 <tablespace>
 <name>USERS</name>
 <type>0</type>
 <tsn>4</tsn>
 <status>1</status>
 <issft>0</issft>
 <file>
 <path>/opt/oracle/app/oradata/DB12C1/datafile/o1_mf_users_8yjccxy4_.dbf</path>
 <afn>6</afn>
 <rfn>6</rfn>
 <createscnbas>26029</createscnbas>
 <createscnwrp>0</createscnwrp>
 <status>1</status>
 <fileblocks>640</fileblocks>
 <blocksize>8192</blocksize>
 <vsn>202375168</vsn>
 <fdbid>4126847348</fdbid>
 <fcpsw>0</fcpsw>
 <fcpsb>1759918</fcpsb>
 <frlsw>0</frlsw>
 <frlsb>1720082</frlsb>
 <frlt>821131319</frlt>
 </file>
 </tablespace>
 <optional>
 <csid>178</csid>
 <ncsid>2000</ncsid>
 <options>
 <option>APS=12.1.0.1.0</option>
 <option>CATALOG=12.1.0.1.0</option>
 <option>CATJAVA=12.1.0.1.0</option>
 <option>CATPROC=12.1.0.1.0</option>
 <option>CONTEXT=12.1.0.1.0</option>
 <option>DV=12.1.0.1.0</option>
 <option>JAVAVM=12.1.0.1.0</option>
 <option>OLS=12.1.0.1.0</option>
 <option>ORDIM=12.1.0.1.0</option>
 <option>OWM=12.1.0.1.0</option>
 <option>SDO=12.1.0.1.0</option>
 <option>XDB=12.1.0.1.0</option>
 <option>XML=12.1.0.1.0</option>
 <option>XOQ=12.1.0.1.0</option>
 </options>
 <olsoid>0</olsoid>
 <dv>0</dv>
 <ncdb2pdb>1</ncdb2pdb>
 <APEX>4.2.0.00.27:1</APEX>
 <parameters>
 <parameter>processes=300</parameter>
 <parameter>memory_target=1048576000</parameter>
 <parameter>db_block_size=8192</parameter>
 <parameter>compatible=12.1.0.0.0</parameter>
 <parameter>open_cursors=300</parameter>
 </parameters>
 <tzvers>
 <tzver>primary version:18</tzver>
 <tzver>secondary version:0</tzver>
 </tzvers>
 <walletkey>0</walletkey>
 </optional>
</PDB>

3. Shutdown source database and login in the CDB
4. Check compatibility

SET SERVEROUTPUT ON
DECLARE
 compatible CONSTANT VARCHAR2(3) := 
 CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
 pdb_descr_file => '/opt/oracle/unplug/db12c1_convert.xml',
 pdb_name => 'NCDB1')
 WHEN TRUE THEN 'YES'
 ELSE 'NO'
END;
BEGIN
 DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
NO

PL/SQL procedure successfully completed.

SQL> 
/

If the scripts returns NO, then check PDB_PLUG_IN_VIOLATIONS view

SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='NCDB1'

NAME                           CAUSE                  TYPE      MESSAGE                                                                                              STATUS
------------------------------ ---------------------- --------- ---------------------------------------------------------------------------------------------------- ---------
NCDB1                          OPTION                 WARNING   Database option APS mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.          PENDING
NCDB1                          OPTION                 WARNING   Database option SDO mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.          PENDING
NCDB1                          OPTION                 WARNING   Database option XOQ mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.          PENDING
NCDB1                          Non-CDB to PDB         WARNING   PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.                                      PENDING
NCDB1                          Parameter              WARNING   CDB parameter sga_target mismatch: Previous 788529152 Current 1979711488                             PENDING
NCDB1                          Parameter              WARNING   CDB parameter pga_aggregate_target mismatch: Previous 262144000 Current 655360000                    PENDING

6 rows selected.

There are WARNING’s only. We can continue.

5. Plug-in Non-CDB:
5.1 Use the NOCOPY Mode:

SQL> CREATE PLUGGABLE DATABASE ncdb1 USING '/opt/oracle/unplug/db12c1_convert.xml' NOCOPY;
*
ERROR at line 1:
ORA-27038: created file already exists
ORA-01119: error in creating database file
'/opt/oracle/app/oradata/DB12C1/datafile/o1_mf_temp_8yjcdrw0_.tmp'

If use NOCOPY you can delete the tempfile or reuse it

SQL> CREATE PLUGGABLE DATABASE ncdb1 USING '/opt/oracle/unplug/db12c1_convert.xml' NOCOPY TEMPFILE REUSE;

Pluggable database created.

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_system_8xmpwox6_.dbf
/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_sysaux_8xmptylz_.dbf
/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_undotbs1_8xmpygh6_.dbf
/opt/oracle/app/oradata/pdbseed/system01.dbf
/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_users_8xmpyf7f_.dbf
/opt/oracle/app/oradata/pdbseed/sysaux01.dbf
/opt/oracle/app/oradata/DB12C1/datafile/o1_mf_system_8yjcbjpj_.dbf
/opt/oracle/app/oradata/DB12C1/datafile/o1_mf_sysaux_8yjc93j2_.dbf
/opt/oracle/app/oradata/DB12C1/datafile/o1_mf_users_8yjccxy4_.dbf

9 rows selected.

5.2 Use the COPY mode

SQL> CREATE PLUGGABLE DATABASE ncdb1 USING '/opt/oracle/unplug/db12c1_convert.xml' COPY;

Pluggable database created.

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_system_8xmpwox6_.dbf
/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_sysaux_8xmptylz_.dbf
/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_undotbs1_8xmpygh6_.dbf
/opt/oracle/app/oradata/pdbseed/system01.dbf
/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_users_8xmpyf7f_.dbf
/opt/oracle/app/oradata/pdbseed/sysaux01.dbf
/opt/oracle/app/oradata/CDB12C/E1CE01B6E8AB0B36E0430100007F25C7/datafile/o1_mf_system_8yjr6xjs_.dbf
/opt/oracle/app/oradata/CDB12C/E1CE01B6E8AB0B36E0430100007F25C7/datafile/o1_mf_sysaux_8yjr7262_.dbf
/opt/oracle/app/oradata/CDB12C/E1CE01B6E8AB0B36E0430100007F25C7/datafile/o1_mf_users_8yjr766m_.dbf

6. Switch containert to PDB and start convert script:

SQL> alter session set container = NCDB1;

Session altered.

SQL> @?/rdbms/admin/noncdb_to_pdb.sql
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>#######################################################################
DOC>#
SQL> 
SQL> VARIABLE cdbname VARCHAR2(128)
SQL> VARIABLE pdbname VARCHAR2(128)
SQL> BEGIN
 2 SELECT sys_context('USERENV', 'CDB_NAME')
 3 INTO :cdbname
 4 FROM dual
 5 WHERE sys_context('USERENV', 'CDB_NAME') is not null;
 6 SELECT sys_context('USERENV', 'CON_NAME')
 7 INTO :pdbname
 8 FROM dual
 9 WHERE sys_context('USERENV', 'CON_NAME') <> 'CDB$ROOT';
 10 END;
 11 /

PL/SQL procedure successfully completed.

SQL> 
SQL> COLUMN pdbname NEW_VALUE pdbname
SQL> COLUMN pdbid NEW_VALUE pdbid
SQL> 
SQL> select :pdbname pdbname from dual;

PDBNAME
--------------------------------------------------------------------------------
NCDB1

1 row selected.
...
script is running some very interesting things
...
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>

7. Now you can open the PDB, there may occur an error:

SQL> show con_name

CON_NAME
------------------------------
NCDB1

SQL> alter database open read write;
alter database open read write
*
ERROR at line 1:
ORA-24344: success with compilation error

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

1 row selected.

In alertlog you will see
********************************************** ******************
WARNING: Pluggable Database NCDB1 with pdb id - 3 is altered with warnings.
Please look into PDB_PLUG_IN_VIOLATIONS view for more details.
****************************************************************

8. To correct the ORA-24344 check PDB_PLUG_IN_VIOLATIONS, correct them and resync

SQL> alter session set container=CDB$ROOT;
Session altered.

SQL> alter pluggable database ncdb1 close;

Pluggable database altered.

SQL> alter pluggable database ncdb1 open restricted;

Pluggable database altered.

SQL> exec dbms_pdb.sync_pdb();

PL/SQL procedure successfully completed.

SQL> alter pluggable database ncdb1 close immediate;

Pluggable database altered.

SQL> alter pluggable database ncdb1 open;

Pluggable database altered.

9. Recheck open problems

SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='NCDB1';

NAME                           CAUSE                  TYPE      MESSAGE                                                                                              STATUS
------------------------------ ---------------------- --------- ---------------------------------------------------------------------------------------------------- ---------
NCDB1                          Non-CDB to PDB         ERROR     PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.                                      RESOLVED
NCDB1                          OPTION                 WARNING   Database option APS mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.          PENDING
NCDB1                          OPTION                 WARNING   Database option SDO mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.          PENDING
NCDB1                          OPTION                 WARNING   Database option XOQ mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0.          PENDING

9. Done. PDB is ready to use. Don’t forget to backup the new database.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s