Convert Non-CDB to a PDB in Oracle 19c : In this article we are going to explain about how to Convert a Non-Container Database (CDB) to a Pluggable Database (PDB) or plugin a Non-Container Database (CDB) into a Container Database (CDB) as a Pluggable Database (PDB) in Oracle 19c. Below are steps for Convert Non-CDB to a PDB in Oracle 19c.
Prerequisites:
Make sure you have one existing Container database if not then create a Container database.
Environment:
Non-Container Database (CDB)
=======================
Platform : Linux x86_64
ORACLE_SID : DELHI
ORACLE_HOME : /u01/app/oracle/product/19c/dbhome_1
VERSION : 19.3.0.0.0
File system : +ASM
Container Database (CDB)
=======================
Platform : Linux x86_64
ORACLE_SID : ORCL
ORACLE_HOME : /u01/app/oracle/product/19c/dbhome_1
VERSION : 19.3.0.0.0
File system : +ASMFirstly, you have to cleanly shutdown the Non-Container Database (CDB):
[oracle@primary ~]$ . oraenv
ORACLE_SID = [ORCL] ? DELHI
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@primary ~]$
[oracle@primary ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 21 01:05:06 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
SQL> select name, open_mode, cdb from v$database;
NAME OPEN_MODE CDB
--------- -------------------- ---
DELHI READ WRITE NO
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>Startup the Non-Container Database (CDB) in read only mode:
SQL> startup open read only;
ORACLE instance started.
Total System Global Area 805303360 bytes
Fixed Size 8901696 bytes
Variable Size 218103808 bytes
Database Buffers 570425344 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL>
SQL> select name, open_mode, cdb from v$database;
NAME OPEN_MODE CDB
--------- -------------------- ---
DELHI READ ONLY NO
SQL>
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------
+DATA/DELHI/DATAFILE/system.313.1151841147
+DATA/DELHI/DATAFILE/sysaux.314.1151841203
+DATA/DELHI/DATAFILE/undotbs1.315.1151841229
+DATA/DELHI/DATAFILE/users.316.1151841231
SQL>Describe the Non-Container Database (CDB) and generate the xml file:
SQL> exec dbms_pdb.describe('/tmp/DELHI_noncdb.xml');
PL/SQL procedure successfully completed.
SQL>
SQL> !ls -ltrh /tmp/DELHI_noncdb.xml
-rw-r--r--. 1 oracle dba 6.6K Jan 21 01:09 /tmp/DELHI_noncdb.xml
SQL>
Shutdown the Non-Container Database (CDB) database:
SQL> select name, open_mode, cdb from v$database;
NAME OPEN_MODE CDB
--------- -------------------- ---
DELHI READ ONLY NO
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>Execute below script to check DELHI (Non-Container Database) compatibility with ORCL (Container Database):
[oracle@primary ~]$ . oraenv
ORACLE_SID = [DELHI] ? ORCL
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@primary ~]$
[oracle@primary ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 21 01:12:56 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
SQL> set lines 400 pages 4000
SQL> col PDB_NAME for a30
SQL> select name, open_mode, cdb, pdb_name, status from v$database,dba_pdbs;
NAME OPEN_MODE CDB PDB_NAME STATUS
--------- -------------------- --- ------------------------------ ----------
ORCL READ WRITE YES ORCLPDB NORMAL
ORCL READ WRITE YES PDB$SEED NORMAL
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/tmp/DELHI_noncdb.xml')
3 WHEN TRUE THEN 'YES'
4 ELSE 'NO'
5 END;
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE(compatible);
8 END;
9 /
YES
PL/SQL procedure successfully completed.
SQL>
-- Incase output is NO, then check PDB_PLUG_IN_VIOLATIONS view
SQL> col cause for a20
SQL> col name for a20
SQL> col message for a35 word_wrapped
SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='DELHI';
NAME CAUSE TYPE MESSAGE STATUS
-------------------- -------------------- --------- ----------------------------------- ---------
DELHI Non-CDB to PDB WARNING PDB plugged in is a non-CDB, PENDING
requires noncdb_to_pdb.sql be run.
DELHI CDB undo mode WARNING Undo mode mismatch: PDB using LOCAL PENDING
undo. CDB using SHARED undo.
SQL>Plug the Non-Container Database (DELHI) into the Container Database (ORCL) :
Note: As per your requirement, you can used one of below listed all options with the “create pluggable database”.
COPY - With this option, DELHI will remain intact. All data files will remain untouched. For PDB files will be copied to the new location, provided with the parameter FILE_NAME_CONVERT.
NOCOPY - Existing files will be used and after completion of the operation, DELHI will not remain usable. As new PDB is using the same data files.
MOVE - using parameter FILE_NAME_CONVERT, existing datafiles will be moved to the new location, hence after the operation completion, DELHI will not be usable.
If you want to copy/move all datafiles and tempfiles to a specific destination use below command:
create pluggable database REPORT using '/tmp/DELHI_noncdb.xml' copy file_name_convert=('/u02/oradata/DELHI','/u01/app/oracle/oradata/ORCL/REPORT');
create pluggable database REPORT using '/tmp/DELHI_noncdb.xml' move file_name_convert=('/u02/oradata/DELHI','/u01/app/oracle/oradata/ORCL/REPORT');
- create pluggable database REPORT using ‘/tmp/DELHI_noncdb.xml’; ———->> By default it will copy all datafiles and tempfiles from source destination to target destination.
- create pluggable database REPORT using ‘/tmp/DELHI_noncdb.xml’ copy; ———->> It will copy all datafiles and tempfiles from source destination to target destination.
- create pluggable database REPORT using ‘/tmp/DELHI_noncdb.xml’ nocopy tempfile reuse; ———->> It will copy only tempfiles from source destination to target destination rest of all datafiles destination will be same.
- create pluggable database REPORT using ‘/tmp/DELHI_noncdb.xml’ move; ———->> It will move all datafiles and tempfiles from source destination to target destination.
Here in my case we use “COPY“ option with the “create pluggable database” and rename the database from DELHI to REPORT. Both database are using ASM and same disk group ‘+DATA’ therefore we didn’t required to use FILE_NAME_CONVERT parameter.
SQL> set echo on;
SQL> set time on;
01:17:18 SQL>
01:17:20 SQL> create pluggable database REPORT using '/tmp/DELHI_noncdb.xml' copy;
Pluggable database created.
01:18:41 SQL>
Verify newly created PDB REPORT on Container Database (CDB) ORCL :
SQL> select name, open_mode, cdb, pdb_name, status from v$database,dba_pdbs;
NAME OPEN_MODE CDB PDB_NAME STATUS
--------- -------------------- --- ------------------------------ ----------
ORCL READ WRITE YES ORCLPDB NORMAL
ORCL READ WRITE YES PDB$SEED NORMAL
ORCL READ WRITE YES REPORT NEW
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
4 REPORT MOUNTED
SQL>
--check that datafiles and tempfiles are copied to new location
SQL> alter session set container=REPORT;
Session altered.
SQL> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------
+DATA/ORCL/09267473694C212FE0636538A8C0929D/DATAFILE/system.323.1158802437
+DATA/ORCL/09267473694C212FE0636538A8C0929D/DATAFILE/sysaux.325.1158802437
+DATA/ORCL/09267473694C212FE0636538A8C0929D/DATAFILE/undotbs1.324.1158802437
+DATA/ORCL/09267473694C212FE0636538A8C0929D/DATAFILE/users.327.1158802437
SQL> select name from v$tempfile;
NAME
------------------------------------------------------------------------------------------
+DATA/ORCL/09267473694C212FE0636538A8C0929D/TEMPFILE/temp.326.1158802453
SQL>Run noncdb_to_pdb.sql script on new PDB (REPORT):
SQL> alter session set container=REPORT;
Session altered.
SQL>
SQL> @?/rdbms/admin/noncdb_to_pdb.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/noncdb_to_pdb.sql /st_rdbms_19/1 2018/12/08 04:29:56 nneeluru Exp $
SQL> Rem
SQL> Rem noncdb_to_pdb.sql
SQL> Rem
SQL> Rem Copyright (c) 2011, 2018, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem noncdb_to_pdb.sql - Convert PDB
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem Converts DB to PDB.
SQL> Rem
SQL> Rem NOTES
SQL> Rem Given a DB with proper obj$ common bits set, we convert it to a proper
SQL> Rem PDB by deleting unnecessary metadata.
...
......... Output lines trimmed
...
01:02:39 SQL> set termout ON
01:02:39 SQL> set time OFF
SQL> set timing OFF
SQL> set trimout ON
SQL> set trimspool ON
SQL> set underline "-"
SQL> set verify OFF
SQL> set wrap ON
SQL> set xmloptimizationcheck OFF
SQL>Now open the pluggable database (REPORT) and check PDB_PLUG_IN_VIOLATIONS details if you getting any error :
SQL> alter pluggable database open;
Pluggable database altered.
SQL>
SQL> alter pluggable database save state;
Pluggable database altered.
SQL>
SQL> select name,cause,type,message,status from pdb_plug_in_violations where status <> 'RESOLVED' and name ='REPORT';
no rows selected
SQL>
SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='REPORT';
NAME CAUSE TYPE MESSAGE STATUS
---------- -------------------- ---------- -------------------------------------------------------------------------------- ---------
REPORT Non-CDB to PDB ERROR PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. RESOLVED
1 row selected.
SQL>The non CDB database has been successfully converted to a PDB now.
I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You can write to mail me through : orakldba@gmail.com.