Monday, July 1, 2013

Oracle 12c New Features - Convert a database to be Pluggable

In a previous blog post I demonstrated creating a Pluggable Database (PDB) from the PDB$SEED database  which is created at the same time as the Container Database (CDB).  That process was quick and easy, but is only useful for creating new empty databases.  

If we want to migrate existing databases which contain data (e.g upgraded from 11g) to the CDB/PDB multitenant architecture we need to convert them to be PDBs so we can plug them in.

For this example I created a stand-alone database called NONCDB.

[oracle@rac1 T12]$ export ORACLE_SID=NONCDB
[oracle@rac1 T12]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 2 10:31:54 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, Real Application Testing
and Unified Auditing options

We can see in v$database that it is not a CDB or PDB.

SQL> select CDB from v$database;

CDB
---
NO

To convert it to be a PDB we first need to get the database in a consistent state and run 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 1235959808 bytes
Fixed Size                  2287816 bytes
Variable Size             452986680 bytes
Database Buffers          771751936 bytes
Redo Buffers                8933376 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

SQL> BEGIN
 DBMS_PDB.DESCRIBE(
  pdb_descr_file => '/u01/app/oracle/oradata/NONCDB/noncdb.xml');
 END;

PL/SQL procedure successfully completed.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Now we can plug NONCDB into a existing CDB database T12.

[oracle@rac1 T12]$ export ORACLE_SID=T12
[oracle@rac1 T12]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 2 10:42:01 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, Real Application Testing
and Unified Auditing options

SQL> select CDB from v$database;

CDB
---
YES

I am plugging the database in to a CDB on the same server as the original database so I will create the PDB with NOCOPY TEMPFILE REUSE.  If you are changing directory structures then you would need to use FILE_NAME_CONVERT.

SQL> CREATE PLUGGABLE DATABASE NONCDB USING '/u01/app/oracle/oradata/NONCDB/noncdb.xml' NOCOPY tempfile reuse;

Pluggable database created.

Now we need to update the data dictionary in the new PDB by running noncdb_to_pdb.sql

SQL> alter session set container=NONCDB;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

This script has a lot of output which I will not show but unfortunately it ended with an error :-

SQL> -- get rid of idl_ub1$ rows for MDL java objects
SQL> delete from sys.idl_ub1$ where obj# in (select obj# from sys.obj$ where bitand(flags, 65536)=65536 and type# in (28,29,30,56));
^Cdelete from sys.idl_ub1$ where obj# in (select obj# from sys.obj$ where bitand(flags, 65536)=65536 and type# in (28,29,30,56))
                *
ERROR at line 1:
ORA-01013: user requested cancel of current operation

The new PDB was left in READ WRITE state after the script exited and seems usable, but due to the error I can't be sure everything completed OK so it would be worth checking with Oracle Support.


SQL> select name, open_mode from v$pdbs;



NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
NONCDB                         READ WRITE

SQL> alter session set container=NONCDB;

Session altered.

SQL> create user test identified by test;

User created.

We can see that by converting the existing database to a PDB it only kept the SYSTEM, SYSAUX, USERS tablespaces, and has dropped  the UNDO datafiles along with the original REDO logs and control files.

SQL> select file_name from dba_data_files;

FILE_NAME
-----------------------------------------------------------------
/u01/app/oracle/oradata/NONCDB/system01.dbf
/u01/app/oracle/oradata/NONCDB/sysaux01.dbf
/u01/app/oracle/oradata/NONCDB/users01.dbf


[oracle@rac1 NONCDB]$ pwd
/u01/app/oracle/oradata/NONCDB
[oracle@rac1 NONCDB]$ ls -lrt
total 1712016
-rw-r----- 1 oracle oinstall  52429312 Jul  2 10:30 redo03.log
-rw-r----- 1 oracle oinstall  52429312 Jul  2 10:30 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Jul  2 10:36 redo01.log
-rw-r----- 1 oracle oinstall  57679872 Jul  2 10:37 undotbs01.dbf
-rw-r--r-- 1 oracle oinstall      3986 Jul  2 10:38 noncdb.xml
-rw-r----- 1 oracle oinstall  10043392 Jul  2 10:39 control02.ctl
-rw-r----- 1 oracle oinstall  10043392 Jul  2 10:39 control01.ctl
-rw-r----- 1 oracle oinstall  62922752 Jul  2 10:45 temp01.dbf
-rw-r----- 1 oracle oinstall   5251072 Jul  2 11:03 users01.dbf
-rw-r----- 1 oracle oinstall 807411712 Jul  2 11:03 system01.dbf
-rw-r----- 1 oracle oinstall 702554112 Jul  2 11:03 sysaux01.dbf