The steps below outline how to convert an Oracle 12c non-container database to a pluggable database in the multitenant architecture. Oracle has moved all future releases to the multitenant architecture so conversion will eventually be necessary.
These steps are written for Oracle 12.1; however, the basic premise and steps remain similar for later versions.
---------------------------
--- ENVIRONMENT INFORMATION
ORACLE_HOME: /u01/app/oracle/product/12.1.0.2/
NON-CDB DATABASE NAME: SANDBOX
CDB DATABASE NAME: CDB1
PDB DATABASE NAME: SANDBOX
---------------------------1. Create database parameter file and create all referenced directory paths
[oracle@bigdatalite ~] vi $ORACLE_HOME/dbs/initCDB1.ora
DB_NAME = CDB1
DB_BLOCK_SIZE = 8192
DB_CREATE_FILE_DEST = /u01/app/oracle/oradata
DB_RECOVERY_FILE_DEST = /u01/app/oracle/fast_recovery_area
CONTROL_FILES = ('/u01/app/oracle/oradata/CDB1/control01.ctl', '/u01/app/oracle/fast_recovery_area/CDB1/controlfile/control2.ctl')
2. Set environment and startup database off of the parameter file
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
export ORACLE_SID=CDB1
sqlplus / as sysdba
SYS@CDB1> startup nomount pfile='$ORACLE_HOME/dbs/initCDB1.ora';
3. Create container database
SYS@CDB1>
CREATE DATABASE CDB1
USER SYS IDENTIFIED BY <PASSWORD>
USER SYSTEM IDENTIFIED BY <PASSWORD>
CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE TEMP01
UNDO TABLESPACE UNDOTBS01
ENABLE PLUGGABLE DATABASE
/4. Run catcdb.sql script to create all components required by a CDB
@?/rdbms/admin/catcdb.sql5. Prepare the non-CDB environment for conversion.
The first step is to shutdown the non-CDB database, open in read-only mode, and create the XML file needed for CDB conversion
export ORACLE_SID=SANDBOX
sqlplus / as sysdba
--- check number of invalid objects and compile (if necessary)
SYS@SANDBOX> select * from dba_objects where status <> 'VALID';
no rows selected
SYS@SANDBOX> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@SANDBOX> startup open read only;
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 377487464 bytes
Database Buffers 687865856 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SYS@SANDBOX> show con_name;
CON_NAME
------------------------------
SANDBOX
SYS@SANDBOX>
BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => '/tmp/SANDBOXNonPDB.xml');
END;
/
PL/SQL procedure successfully completed.
SYS@SANDBOX> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
6. On the CDB, check pluggable compatibility.
[oracle@bigdatalite ~]$ export ORACLE_SID=CDB1
[oracle@bigdatalite ~]$ echo $ORACLE_SID
CDB1
[oracle@bigdatalite ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 30 22:40:13 2020
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
SYS@CDB1> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SYS@CDB1> SET SERVEROUTPUT ON;
DECLARE
compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/tmp/SANDBOXNonPDB.xml')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
YES
PL/SQL procedure successfully completed.
7. If the CHECK_PLUG_COMPATIBILITY returns “YES,” everything should be in order.
However, review the PDB_PLUG_IN_VIOLATIONS view for errors/warnings and correct as necessary.
SYS@CDB1>col cause for a20
col name for a20
col message for a35 word_wrapped
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='SANDBOX'
/
NAME CAUSE TYPE
-------------------- -------------------- ---------
MESSAGE STATUS
----------------------------------- ---------
SANDBOX Non-CDB to PDB WARNING
PDB plugged in is a non-CDB, PENDING
requires noncdb_to_pdb.sql be run.
8. Create a pluggable database using the XML file generated earlier.
If necessary, you can include the FILE_NAME_CONVERT parameter here to convert data file paths to a separate location. By default, the files will be copied to the location specified in the DB_CREATE_FILE_DEST parameter mentioned above. Additionally, I chose to use the default COPY parameter to retain the previous files to be deleted later. If you want to perform a direct conversion, include NOCOPY instead.
SYS@CDB1> CREATE PLUGGABLE DATABASE SANDBOX USING '/tmp/SANDBOXNonPDB.xml';
Pluggable database created.
9. Run the $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql script
SYS@CDB1> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SANDBOX MOUNTED
SYS@CDB1> alter session set CONTAINER=SANDBOX;
Session altered.
SYS@CDB1> show con_name;
CON_NAME
------------------------------
SANDBOX
SYS@CDB1> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
...
PL/SQL procedure successfully completed.
10. Open the pdb and review for potential errors
SYS@CDB1> show error
No errors.
SYS@CDB1> show con_name;
CON_NAME
------------------------------
SANDBOX
SYS@CDB1> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 SANDBOX MOUNTED
SYS@CDB1> ALTER PLUGGABLE DATABASE OPEN;
Pluggable database altered.
SYS@CDB1> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 SANDBOX READ WRITE NO
SYS@CDB1> select message,time from pdb_plug_in_violations;
no rows selected
11. Check invalid objects and compile if necessary
SYS@CDB1> select * from dba_objects where status <> 'VALID';
no rows selected
It's as simple as that! We now have a completed migration from non-container to container architecture. If you have any questions or comments, reach out to us here or in the comment section below.