1. create Pluggable database from NON_CDB database.
[oracle@db-42 ~]$ ora si SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 5 13:07:08 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> SQL> select CDB from v$database; CDB --- NO
use DBMS_PDB.DESCRIBE to create an XML file to describe the database.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 4.0486E+10 bytes
Fixed Size 4658128 bytes
Variable Size 5905583152 bytes
Database Buffers 3.4494E+10 bytes
Redo Buffers 82210816 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
SQL>
BEGIN
DBMS_PDB.DESCRIBE(
pdb_descr_file => '/tmp/conv_cdb.xml');
END;
/ SQL> 2 3 4 5
PL/SQL procedure successfully completed.
SQL> ! cat /tmp/conv_cdb.xml |more
<?xml version="1.0" encoding="UTF-8"?>
<PDB>
<pdbname>nocdb</pdbname>
<cid>0</cid>
<byteorder>1</byteorder>
<vsn>202375168</vsn>
<dbid>3060397207</dbid>
<cdbid>3060397207</cdbid>
<guid>E0BE4CA142ED4DFCE0432A00000A62D7</guid>
<uscnbas>1732161</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>/data/app2/oracle/oradata/nocdb/system01.dbf</path>
<afn>1</afn>
<rfn>1</rfn>
<createscnbas>7</createscnbas>
<createscnwrp>0</createscnwrp>
<status>1</status>
<fileblocks>98560</fileblocks>
<blocksize>8192</blocksize>
<vsn>202375168</vsn>
<fdbid>3060397207</fdbid>
<fcpsw>0</fcpsw>
<fcpsb>1732160</fcpsb>
<frlsw>0</frlsw>
<frlsb>1720082</frlsb>
<frlt>819985944</frlt>
</file>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
plug NOCDB into a existing CDB database “oracle12c”
[oracle@db-42 pdb_12c2]$ ora si
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 5 13:12:17 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> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4061728203 PDB$SEED READ ONLY
3 430324419 LIUYANG READ WRITE
4 1674643208 YANG READ WRITE
5 3060388583 TT READ WRITE
SQL> !mkdir -p /data/app2/oracle/oradata/CON_CDB/datafile
SQL> CREATE PLUGGABLE DATABASE con_cdb USING '/tmp/conv_cdb.xml' move file_name_convert=('/data/app2/oracle/oradata/nocdb/','/data/app2/oracle/oradata/CON_CDB/datafile/') ;
Pluggable database created.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4061728203 PDB$SEED READ ONLY
3 430324419 LIUYANG MOUNTED
4 1674643208 YANG MOUNTED
5 3060388583 TT MOUNTED
6 3060397207 CON_CDB MOUNTED
SQL> set serveroutput on;
SQL> declare
2 compat boolean := FALSE;
begin
compat := dbms_pdb.check_plug_compatibility(pdb_descr_file => '/tmp/noncdb.xml');
if compat
then
dbms_output.put_line('Yes');
else
dbms_output.put_line('No');
end if;
end;
/ 3 4 5 6 7 8 9 10 11 12
No
PL/SQL procedure successfully completed.
SQL> alter session set container=CON_CDB
2 ;
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>#######################################################################
.....
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> WHENEVER SQLERROR CONTINUE;
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4061728203 PDB$SEED READ ONLY
3 430324419 LIUYANG MOUNTED
4 1674643208 YANG MOUNTED
5 3060388583 TT MOUNTED
6 3060397207 CON_CDB MOUNTED -----added
5 rows selected.
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4061728203 PDB$SEED READ ONLY
3 430324419 LIUYANG READ WRITE
4 1674643208 YANG READ WRITE
5 3060388583 TT READ WRITE
6 3060397207 CON_CDB READ WRITE
5 rows selected.
2. Clone Pluggable database from the same database
[oracle@db-42 pdb_12c2]$ ora si
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 5 14:11:02 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> alter pluggable database CON_CDB close immediate;
Pluggable database altered.
SQL> alter pluggable database CON_CDB open read only;
Pluggable database altered.
SQL> CREATE PLUGGABLE DATABASE newpdb FROM CON_CDB
FILE_NAME_CONVERT = ('/data/app2/oracle/oradata/CON_CDB/datafile/', '/data/app2/oracle/oradata/newpdb/datafile/')
PATH_PREFIX = '/data/app2/oracle/oradata/newpdb/datafile'; 2 3
Pluggable database created.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4061728203 PDB$SEED READ ONLY
3 430324419 LIUYANG READ WRITE
4 1674643208 YANG READ WRITE
5 3060388583 TT READ WRITE
6 3060397207 CON_CDB READ ONLY
7 1613018658 NEWPDB MOUNTED
6 rows selected.
SQL> alter pluggable database NEWPDB open ;
Pluggable database altered.
SQL> alter pluggable database CON_CDB close immediate;
Pluggable database altered.
SQL> alter pluggable database CON_CDB open;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4061728203 PDB$SEED READ ONLY
3 430324419 LIUYANG READ WRITE
4 1674643208 YANG READ WRITE
5 3060388583 TT READ WRITE
6 3060397207 CON_CDB READ WRITE
7 1613018658 NEWPDB READ WRITE
6 rows selected.
3. Migrate PDB from one CDB to another CDB
(from database oracle12 -> testcdb)
[oracle@db-42 pdb_12c2]$ ora si SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 5 14:45:41 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> alter pluggable database NEWPDB close immediate; Pluggable database altered. SQL> alter pluggable database NEWPDB UNPLUG into '/tmp/newpdb.xml'; Pluggable database altered. SQL> SELECT NAME,CDB FROM V$DATABASE; NAME CDB --------- --- ORACLE12 YES SQL> ! mkdir -p /data/app2/oracle/oradata/newpdb_new/datafile/
login to another CDB (testcdb) copy datafile from source database(oracle12)
[oracle@db-42 ~]$ ora si
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 5 14:44:23 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> SELECT NAME,CDB FROM V$DATABASE;
NAME CDB
--------- ---
TESTCDB YES
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4062640301 PDB$SEED READ ONLY
SQL> CREATE PLUGGABLE DATABASE newpdb_new
USING '/tmp/newpdb.xml'
FILE_NAME_CONVERT =
('/data/app2/oracle/oradata/newpdb/datafile/', '/data/app2/oracle/oradata/newpdb_new/datafile/')
COPY
STORAGE (MAXSIZE 2G)
TEMPFILE REUSE;
2 3 4 5 6 7
Pluggable database created.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4062640301 PDB$SEED READ ONLY
3 1613018658 NEWPDB_NEW MOUNTED
SQL> alter pluggable database NEWPDB_NEW open;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4062640301 PDB$SEED READ ONLY
3 1613018658 NEWPDB_NEW READ WRITE
Also you can use “SOURCE_FILE_NAME_CONVERT” to swith datafile
first UNPLUG pluggable database CON_CDB
SQL> alter pluggable database CON_CDB close immediate; Pluggable database altered. SQL> alter pluggable database CON_CDB UNPLUG into '/tmp/CON_CDB.xml'; Pluggable database altered.
move datafile to new directories
eg:
/data/app2/oracle/oradata/CON_CDB/datafile/system01.dbf–>/data/app2/oracle/oradata/CON_CDB/datafile1/system01.dbf
..
all of datafile moved,than you can tell oracle where are datafiles by “SOURCE_FILE_NAME_CONVERT” parameter
[oracle@db-42 datafile]$ ora si
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 5 15:11:41 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> CREATE PLUGGABLE DATABASE CON_CDB_NEW
2 USING '/tmp/CON_CDB.xml'
SOURCE_FILE_NAME_CONVERT =('/data/app2/oracle/oradata/CON_CDB/datafile/','/data/app2/oracle/oradata/CON_CDB/datafile1/')
MOVE
FILE_NAME_CONVERT = ('/data/app2/oracle/oradata/CON_CDB/datafile1/', '/data/app2/oracle/oradata/CON_CDB_NEW/datafile/')
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M); 3 4 5 6
Pluggable database created.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4062640301 PDB$SEED READ ONLY
3 3060397207 CON_CDB_NEW MOUNTED
SQL> alter pluggable database CON_CDB_NEW open ;
Pluggable database altered.