########################################################
How to migrate a NON-CONTAINER-CDB to PDB using DBMS_PDB
########################################################
====================================
Before Migration to Pluggable DB
====================================
SQL> select name,cdb from v$database;
NAME CDB
--------- ---
DBTRMRK NO
SQL> select instance_name,host_name,version,status from gv$instance;
INSTANCE_NAME HOST_NAME VERSION STATUS
------------- ----------------------------------- ---------- --------
dbtrmrk dbtrmrk.localdomain 12.1.0.1.0 OPEN
====================================
Migration Tasks
====================================
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP OPEN READ ONLY;
ORACLE instance started.
Total System Global Area 801701888 bytes
Fixed Size 2293496 bytes
Variable Size 327155976 bytes
Database Buffers 465567744 bytes
Redo Buffers 6684672 bytes
Database mounted.
Database opened.
====================================
DESCRIBE the NON-CONTAINER-CDB
====================================
SQL> BEGIN
2 DBMS_PDB.DESCRIBE(
3 pdb_descr_file => '/tmp/dbtrmrk.xml');
4 END;
5 /
PL/SQL procedure successfully completed.
====================================
SHUTDOWN NON-CONTAINER-CDB
====================================
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
====================================
Analyze NON-CONTAINER-CDB XML File
====================================
[oracle@dbtrmrk ~]$ cat /tmp/dbtrmrk.xml
<?xml version="1.0" encoding="UTF-8"?>
<PDB>
<pdbname>dbtrmrk</pdbname>
<cid>0</cid>
<byteorder>1</byteorder>
<vsn>186646528</vsn>
<dbid>863520319</dbid>
<cdbid>863520319</cdbid>
<guid>F7F3F5DD6A1E30D5E043C9A8A8C0C264</guid>
<uscnbas>3396841</uscnbas>
<uscnwrp>0</uscnwrp>
<rdba>4303816</rdba>
<tablespace>
<name>SYSTEM</name>
<type>0</type>
<tsn>0</tsn>
<status>1</status>
<issft>0</issft>
<file>
<path>/u01/app/oracle/dbdata/dbtrmrk/system01.dbf</path>
<afn>1</afn>
<rfn>1</rfn>
<createscnbas>7</createscnbas>
<createscnwrp>0</createscnwrp>
<status>1</status>
<fileblocks>117760</fileblocks>
<blocksize>8192</blocksize>
<vsn>186646528</vsn>
<fdbid>863520319</fdbid>
<fcpsw>0</fcpsw>
<fcpsb>3396840</fcpsb>
<frlsw>0</frlsw>
<frlsb>995548</frlsb>
<frlt>826799617</frlt>
</file>
</tablespace>
<tablespace>
<name>SYSAUX</name>
<type>0</type>
<tsn>1</tsn>
<status>1</status>
<issft>0</issft>
<file>
<path>/u01/app/oracle/dbdata/dbtrmrk/sysaux01.dbf</path>
<afn>2</afn>
<rfn>2</rfn>
<createscnbas>2188</createscnbas>
<createscnwrp>0</createscnwrp>
<status>1</status>
<fileblocks>119040</fileblocks>
<blocksize>8192</blocksize>
<vsn>186646528</vsn>
<fdbid>863520319</fdbid>
<fcpsw>0</fcpsw>
<fcpsb>3396840</fcpsb>
<frlsw>0</frlsw>
<frlsb>995548</frlsb>
<frlt>826799617</frlt>
</file>
</tablespace>
<tablespace>
<name>TEMP</name>
<type>1</type>
<tsn>3</tsn>
<status>1</status>
<issft>0</issft>
<bmunitsize>128</bmunitsize>
<file>
<path>/u01/app/oracle/dbdata/dbtrmrk/dbtrmrk/temp01.dbf</path>
<afn>1</afn>
<rfn>1</rfn>
<createscnbas>995619</createscnbas>
<createscnwrp>0</createscnwrp>
<status>1</status>
<fileblocks>2560</fileblocks>
<blocksize>8192</blocksize>
<vsn>186646528</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>/u01/app/oracle/dbdata/dbtrmrk/users01.dbf</path>
<afn>4</afn>
<rfn>4</rfn>
<createscnbas>18919</createscnbas>
<createscnwrp>0</createscnwrp>
<status>1</status>
<fileblocks>640</fileblocks>
<blocksize>8192</blocksize>
<vsn>186646528</vsn>
<fdbid>863520319</fdbid>
<fcpsw>0</fcpsw>
<fcpsb>3396840</fcpsb>
<frlsw>0</frlsw>
<frlsb>995548</frlsb>
<frlt>826799617</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>JAVAVM=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>
<dv>0</dv>
<ncdb2pdb>1</ncdb2pdb>
<APEX>4.2.0.00.27:0</APEX>
<parameters>
<parameter>processes=300</parameter>
<parameter>sessions=472</parameter>
<parameter>sga_target=805306368</parameter>
<parameter>db_block_size=8192</parameter>
<parameter>compatible=11.2.0.0.0</parameter>
<parameter>open_cursors=300</parameter>
<parameter>pga_aggregate_target=268435456</parameter>
</parameters>
<tzvers>
<tzver>primary version:18</tzver>
<tzver>secondary version:0</tzver>
</tzvers>
<walletkey>0</walletkey>
</optional>
</PDB>
====================================
LOGIN TO CONTAINER DATABASE
====================================
SQL> select name,cdb from v$database;
NAME CDB
--------- ---
CDBDEMO YES
SQL> select con_id,name from v$pdbs;
CON_ID NAME
---------- ------------------------------
2 PDB$SEED
3 PDB1
====================================
Choose Destination PDB Location
====================================
SQL> select name from v$datafile;
NAME
----------------------------------------
/u03/oradata/cdbdemo/system01.dbf
/u03/oradata/cdbdemo/sysaux01.dbf
/u03/oradata/cdbdemo/undotbs01.dbf
/u03/oradata/cdbdemo/pdbseed/system01.dbf
/u03/oradata/cdbdemo/users01.dbf
/u03/oradata/cdbdemo/pdbseed/sysaux01.dbf
/u03/oradata/cdbdemo/pdb1/system01.dbf
/u03/oradata/cdbdemo/pdb1/sysaux01.dbf
/u03/oradata/cdbdemo/pdb1/pdb1_users01.dbf
====================================
Create PDB
====================================
Note: In order to save time, I have already copied over the files so using NOCOPY option.
SQL>CREATE PLUGGABLE DATABASE PDBTRMRK USING '/tmp/dbtrmrk.xml'
SOURCE_FILE_NAME_CONVERT = ('/u01/app/oracle/dbdata/dbtrmrk/','/u03/oradata/cdbdemo/pdbtrmrk/')
NOCOPY TEMPFILE REUSE;
Pluggable database created.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 MOUNTED
PDBTRMRK MOUNTED
====================================
Clean-Up New PDB
====================================
SQL> ALTER SESSION SET CONTAINER=PDBTRMRK;
Session altered.
SQL> @$ORACLE_HOME/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> alter system flush shared_pool;
System altered.
SQL> /
System altered.
SQL> /
System altered.
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;
====================================
OPEN PDB
====================================
SQL> ALTER PLUGGABLE DATABASE "PDBTRMRK" OPEN;
Pluggable database altered.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 MOUNTED
PDBTRMRK READ WRITE
Enjoy.....Abid Malik
How to migrate a NON-CONTAINER-CDB to PDB using DBMS_PDB
########################################################
====================================
Before Migration to Pluggable DB
====================================
SQL> select name,cdb from v$database;
NAME CDB
--------- ---
DBTRMRK NO
SQL> select instance_name,host_name,version,status from gv$instance;
INSTANCE_NAME HOST_NAME VERSION STATUS
------------- ----------------------------------- ---------- --------
dbtrmrk dbtrmrk.localdomain 12.1.0.1.0 OPEN
====================================
Migration Tasks
====================================
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP OPEN READ ONLY;
ORACLE instance started.
Total System Global Area 801701888 bytes
Fixed Size 2293496 bytes
Variable Size 327155976 bytes
Database Buffers 465567744 bytes
Redo Buffers 6684672 bytes
Database mounted.
Database opened.
====================================
DESCRIBE the NON-CONTAINER-CDB
====================================
SQL> BEGIN
2 DBMS_PDB.DESCRIBE(
3 pdb_descr_file => '/tmp/dbtrmrk.xml');
4 END;
5 /
PL/SQL procedure successfully completed.
====================================
SHUTDOWN NON-CONTAINER-CDB
====================================
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
====================================
Analyze NON-CONTAINER-CDB XML File
====================================
[oracle@dbtrmrk ~]$ cat /tmp/dbtrmrk.xml
<?xml version="1.0" encoding="UTF-8"?>
<PDB>
<pdbname>dbtrmrk</pdbname>
<cid>0</cid>
<byteorder>1</byteorder>
<vsn>186646528</vsn>
<dbid>863520319</dbid>
<cdbid>863520319</cdbid>
<guid>F7F3F5DD6A1E30D5E043C9A8A8C0C264</guid>
<uscnbas>3396841</uscnbas>
<uscnwrp>0</uscnwrp>
<rdba>4303816</rdba>
<tablespace>
<name>SYSTEM</name>
<type>0</type>
<tsn>0</tsn>
<status>1</status>
<issft>0</issft>
<file>
<path>/u01/app/oracle/dbdata/dbtrmrk/system01.dbf</path>
<afn>1</afn>
<rfn>1</rfn>
<createscnbas>7</createscnbas>
<createscnwrp>0</createscnwrp>
<status>1</status>
<fileblocks>117760</fileblocks>
<blocksize>8192</blocksize>
<vsn>186646528</vsn>
<fdbid>863520319</fdbid>
<fcpsw>0</fcpsw>
<fcpsb>3396840</fcpsb>
<frlsw>0</frlsw>
<frlsb>995548</frlsb>
<frlt>826799617</frlt>
</file>
</tablespace>
<tablespace>
<name>SYSAUX</name>
<type>0</type>
<tsn>1</tsn>
<status>1</status>
<issft>0</issft>
<file>
<path>/u01/app/oracle/dbdata/dbtrmrk/sysaux01.dbf</path>
<afn>2</afn>
<rfn>2</rfn>
<createscnbas>2188</createscnbas>
<createscnwrp>0</createscnwrp>
<status>1</status>
<fileblocks>119040</fileblocks>
<blocksize>8192</blocksize>
<vsn>186646528</vsn>
<fdbid>863520319</fdbid>
<fcpsw>0</fcpsw>
<fcpsb>3396840</fcpsb>
<frlsw>0</frlsw>
<frlsb>995548</frlsb>
<frlt>826799617</frlt>
</file>
</tablespace>
<tablespace>
<name>TEMP</name>
<type>1</type>
<tsn>3</tsn>
<status>1</status>
<issft>0</issft>
<bmunitsize>128</bmunitsize>
<file>
<path>/u01/app/oracle/dbdata/dbtrmrk/dbtrmrk/temp01.dbf</path>
<afn>1</afn>
<rfn>1</rfn>
<createscnbas>995619</createscnbas>
<createscnwrp>0</createscnwrp>
<status>1</status>
<fileblocks>2560</fileblocks>
<blocksize>8192</blocksize>
<vsn>186646528</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>/u01/app/oracle/dbdata/dbtrmrk/users01.dbf</path>
<afn>4</afn>
<rfn>4</rfn>
<createscnbas>18919</createscnbas>
<createscnwrp>0</createscnwrp>
<status>1</status>
<fileblocks>640</fileblocks>
<blocksize>8192</blocksize>
<vsn>186646528</vsn>
<fdbid>863520319</fdbid>
<fcpsw>0</fcpsw>
<fcpsb>3396840</fcpsb>
<frlsw>0</frlsw>
<frlsb>995548</frlsb>
<frlt>826799617</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>JAVAVM=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>
<dv>0</dv>
<ncdb2pdb>1</ncdb2pdb>
<APEX>4.2.0.00.27:0</APEX>
<parameters>
<parameter>processes=300</parameter>
<parameter>sessions=472</parameter>
<parameter>sga_target=805306368</parameter>
<parameter>db_block_size=8192</parameter>
<parameter>compatible=11.2.0.0.0</parameter>
<parameter>open_cursors=300</parameter>
<parameter>pga_aggregate_target=268435456</parameter>
</parameters>
<tzvers>
<tzver>primary version:18</tzver>
<tzver>secondary version:0</tzver>
</tzvers>
<walletkey>0</walletkey>
</optional>
</PDB>
====================================
LOGIN TO CONTAINER DATABASE
====================================
SQL> select name,cdb from v$database;
NAME CDB
--------- ---
CDBDEMO YES
SQL> select con_id,name from v$pdbs;
CON_ID NAME
---------- ------------------------------
2 PDB$SEED
3 PDB1
====================================
Choose Destination PDB Location
====================================
SQL> select name from v$datafile;
NAME
----------------------------------------
/u03/oradata/cdbdemo/system01.dbf
/u03/oradata/cdbdemo/sysaux01.dbf
/u03/oradata/cdbdemo/undotbs01.dbf
/u03/oradata/cdbdemo/pdbseed/system01.dbf
/u03/oradata/cdbdemo/users01.dbf
/u03/oradata/cdbdemo/pdbseed/sysaux01.dbf
/u03/oradata/cdbdemo/pdb1/system01.dbf
/u03/oradata/cdbdemo/pdb1/sysaux01.dbf
/u03/oradata/cdbdemo/pdb1/pdb1_users01.dbf
====================================
Create PDB
====================================
Note: In order to save time, I have already copied over the files so using NOCOPY option.
SQL>CREATE PLUGGABLE DATABASE PDBTRMRK USING '/tmp/dbtrmrk.xml'
SOURCE_FILE_NAME_CONVERT = ('/u01/app/oracle/dbdata/dbtrmrk/','/u03/oradata/cdbdemo/pdbtrmrk/')
NOCOPY TEMPFILE REUSE;
Pluggable database created.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 MOUNTED
PDBTRMRK MOUNTED
====================================
Clean-Up New PDB
====================================
SQL> ALTER SESSION SET CONTAINER=PDBTRMRK;
Session altered.
SQL> @$ORACLE_HOME/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> alter system flush shared_pool;
System altered.
SQL> /
System altered.
SQL> /
System altered.
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;
====================================
OPEN PDB
====================================
SQL> ALTER PLUGGABLE DATABASE "PDBTRMRK" OPEN;
Pluggable database altered.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 MOUNTED
PDBTRMRK READ WRITE
Enjoy.....Abid Malik
No comments:
Post a Comment