In this post, I discuss moving a regular Non CDB Database to CDB architecture. DBMS_PDB.DESCRIBE metadata procedure is used for the same purpose. Following analogy applies to the post,
ORCL - Non CDB Database (Instance)
Step -1 Identify CDB or non CDB database
[oracle@r12c orcl]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 10 18:49:34 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> SELECT name, open_mode FROM v$pdbs ORDER BY name;
no rows selected
SQL> select con_id, dbid, name from v$pdbs;
no rows selected
SQL> select cdb from v$database;
CDB
---
NO
Step -2 Creat CDB database
Below steps to create CDB Database (Instance)
$ ./runinstaller
Step -3
Open the Non CDB (ORCL) in READ-ONLY Mode to keep it transactionally consistent.
SQL> STARTUP OPEN READ ONLY;
ORACLE instance started.
Total System Global Area 1275068416 bytes
Fixed Size 2923968 bytes
Variable Size 872415808 bytes
Database Buffers 385875968 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.
Step -4
Connect to the Non-CDB Database(ORCL) and execute DBMS_PDB.DESCRIBE procedure to generate an xml file that will help to build the Pluggable Database on the CDB Database(CDB)
SQL> BEGIN
DBMS_PDB.DESCRIBE(
pdb_descr_file => '/tmp/cdb.xml');
END;
/ 2 3 4 5
PL/SQL procedure successfully completed.
Step -5
Shutdown the Non CDB (ORCL) Database, and login to CDB Database (CDB) as SYS user
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Step -6
On CDB container database Create the PDB Database by mapping the XML file that Is generated in step -4
[oracle@r12c datafile]$ export ORACLE_SID=cdb1
[oracle@r12c datafile]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 10 20:14:23 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select cdb from v$database;
CDB
---
YES
SQL> select name from v$database;
NAME
---------
CDB1
SQL> SELECT pdb_name, status FROM dba_pdbs ORDER BY pdb_name;
PDB_NAME STATUS
----------- ----------
PDB NORMAL
PDB$SEED NORMAL
SQL> CREATE PLUGGABLE DATABASE orcl USING '/tmp/cdb.xml'
COPY
FILE_NAME_CONVERT = ('/db/app/product/oradata/orcl/','/db/app/product/oradata/orcl/pdb1/'); 2 3
Pluggable database created.
SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB MOUNTED
ORCL MOUNTED
Step -7
Connect to the new PDB that has been created on CDB Database (CONDB) as SYS, and execute the "noncdb_to_pdb.sql" script.
SQL> ALTER SESSION SET CONTAINER=ORCL;
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> 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> 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>
SQL> WHENEVER SQLERROR CONTINUE;
SQL> commit;
Commit complete.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@r12c datafile]$
Step -8
Open the PDB in READ WRITE Mode, confirm the status, and make it available for Client Operations, it is also advisable to take a backup.
[oracle@r12c datafile]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 10 20:46:02 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB MOUNTED
ORCL MOUNTED
SQL> ALTER PLUGGABLE DATABASE ORCL open;
Pluggable database altered.
SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB MOUNTED
ORCL READ WRITE
