Convert NON-CDB to Multitenant PDB (Pluggable Database)

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Aug 29, 2017

Convert NON-CDB to Multitenant PDB (Pluggable Database)

NONCDB to CDB conversion offers several options to choose from depending upon the size of the database. If the database is huge in size you have NOCOPY option available. This will minimize the amount of extra space needed along with amount of time it takes to perform a copy/move operation at minimal risk.

Introduction:

With Oracle 12c, we have multitenant database options available to host multiple Pluggable database (PDB) with a Single Container Database (CDB). Sometimes we have the requirement to convert Non-CDB database into a CDB (Pluggable database). In this blog I discuss the ways to convert a Non-CDB database into a CDB database.

Test Case Setup Details:

Host: ABC123.xyz.com

DB: noncdb12c (non-cdb database)

Version: 12.1.0.2 

Suppose you have one NON-CDB database instance noncdb12c (12.1.0.2) running on the machine "ABC123.xyz.com". You have to now create a CDB database on the same machine as cdb12c (12.1.0.2). You need to make sure you have enough Disk Space to hold the second database which we are going to convert into a CDB.

database it management

Steps for Conversion. 

Step 1.  Cleanly Shutdown the Non-CDB Database Instance. 

          ==> set environment to noncdb12c

          ==> sqlplus / as sysdba

          ==> Shutdown immediate 

Step 2.  Once You Have Cleanly Shutdown the Database, Start Up the database in Mount Exclusive Mode and Open the Database in Read-Only Mode. 

          ==> set environment to noncdb12c

          ==> sqlplus / as sysdba        

          ==> startup mount exclusive

          ==> alter database open read only; 

Step 3. Generate a Pluggable Database Manifest File from the Non-Container Database. 

          ==> set environment to noncdb12c

          ==> sqlplus / as sysdba        

          ==> exec dbms_pdb.describe (pdb_descr_file=>'/tmp/noncdb12c_manifest_file.xml'); 

Step 4. Shutdown the NON-CDB file Once Step 3 Completes Successfully. 

          ==> Set environment to noncdb12c

          ==> sqlplus / as sysdba

          ==> shutdown immediate 

Step 5. Start the CDB (cdb12c) if it’s Not Already Up and Check the Compatibility with CDB.

          ==> set environment to cdb12c

          ==> sqlplus / as sysdba

          ==> startup (If not up)

          ==> Run below at SQL prompt. 

          SET SERVEROUTPUT ON;

          DECLARE

          Compatible CONSTANT VARCHAR2(3) :=CASE  DBMS_PDB.CHECK_PLUG_COMPATIBILITY 

          (pdb_descr_file => '/tmp/noncdb12c_manifest_file.xml')

          WHEN TRUE THEN 'YES'

          ELSE 'NO'

          END;

          BEGIN

          DBMS_OUTPUT.PUT_LINE(compatible);

          END;

          / 

Step 6. Once it Completes Successfully, Query PDB_PLUG_IN_VIOLATIONS View from CBB Database for Any Errors.

          ==> set environment to cdb12c

          ==> sqlplus / as sysdba

          ==> select name, cause, type, message, status from PDB_PLUG_IN_VIOLATIONS where

                   name='NONCDB12C';  

Note: "There should be no violations reported. If there are any, you need to fix it before proceeding". 

Step 7. Connect to the CDB Where Database has to be Plugged in Using the Noncdb Manifest File and Plug the PDDB12C Database.

          ==> set environment to cdb12c

          ==> sqlplus / as sysdba         

          ==> CREATE PLUGGABLE DATABASE pdb12c USING '/tmp/noncdb12c_manifest_file.xml'

          COPY

          FILE_NAME_CONVERT = ('<Datafile_Location_for_noncdb>', 'Datafile_Location_for_pdb'); 

          Note: "Below options are supported and you can chose one based on the env" 

1) COPY: The datafiles of noncdb remains intact and it is copied to create PDBs at new locations and keep original datafiles intact at the original location. (This would mean that a noncdb database would still be operational after the creation of a PDB).

2) MOVE: The datafiles of noncdb are moved to a new location to create a PDB. In this case, noncdb database would not be available after a PDB is created.

  • NOCOPY: The datafiles of noncdb are used to create a PDB2 and it uses same existing location. In this case, a noncdb database would not be available after a PDB is created. 

You can use FILE_NAME_CONVERT parameter to specify the new location of the datafiles while using COPY or MOVE option. 

Step 8. Once Step 7 Completes Successfully, Switch to the PDB Container and Run the "$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql".

          ==> set environment to cdb12c

          ==> sqlplus / as sysdba

          ==> alter session set container=pdb12c

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

Step 9.  Startup the PDB and Check the Open Mode.

          ==> set environment to cdb12c

          ==> sqlplus / as sysdba

          ==> ALTER PLUGGABLE DATABASE OPEN;

          ==>SELECT name, open_mode FROM v$pdbs; 

Conclusion:

NONCDB to CDB conversion offers several options to choose from depending upon the size of the database. If the database is huge in size you have NOCOPY option available. This will minimize the amount of extra space needed along with amount of time it takes to perform a copy/move operation at minimal risk. The risk arise on account of issues while converting with NOCOPY option. You may not have original database files intact and so to restore back the database to previous state, you will need to restore the database using the backups taken before the activity.

When database size is not huge, it is always recommended to use COPY option so that if there are any issues, fallback to previous noncdb is straightforward as original files are always intact. For any questions click below. You can also leave a comment in the field below:

Ask Akshay