Wednesday, 15 January 2025

Cloning a non-container database (non-CDB) to pluggable database (PDB)

Converting a non-CDB to PDB is very easy, thanks to DBMS_PDB package provided by Oracle from 12c onwards. DBMS_PDB allows to you create an XML and generate the metadata for non-CDB just like you'll create it when you unplug any PDB.

Here I'll be mapping a non-CDB (CDB2) database to a container database (CDB1) as a pluggable database and name it as PDB2.

Let's begin...

1) Ensure the non-CDB database is in READ ONLY state.

[oracle@oravm19c-2 admin]$ sqlplus sys@cdb2 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 15 12:02:14 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 
SQL> 
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> startup open read only;
ORACLE instance started.

Total System Global Area 1107295376 bytes
Fixed Size		    8895632 bytes
Variable Size		  301989888 bytes
Database Buffers	  788529152 bytes
Redo Buffers		    7880704 bytes
Database mounted.
Database opened.

2) Generate XML metadata using DBMS_PDB.DESCRIBE procedure as shown below.

SQL> BEGIN
  DBMS_PDB.DESCRIBE(
    pdb_descr_file => '/u01/cdb2.xml');
END;
/  2    3    4    5  

PL/SQL procedure successfully completed.

SQL> 

3) Now you can shutdown your non-CDB instance.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

4) Connect to Container database where you want to plug this as PDB.

[oracle@oravm19c-2 admin]$ . oraenv
ORACLE_SID = [cdb2] ? cdb1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oravm19c-2 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 15 12:08:08 2025
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> select name, open_mode from V$database;

NAME	  OPEN_MODE
--------- --------------------
CDB1	  READ WRITE

5) Create the PDB using XML, COPY and FILE_NAME_CONVERT options.

SQL> create pluggable database pdb2 using '/u01/cdb2.xml' copy file_name_convert=('/u01/app/oracle/oradata/CDB2/', '/u01/app/oracle/oradata/CDB1/pdb2/');

Pluggable database created.

6) Switch to PDB2 and run non-CDB to PDB conversion script as shown below.

SQL> @?/rdbms/admin/noncdb_to_pdb.sql

The script will take a while to finish so be patient and output log is too long so can't paste the full version but it should finish with something like this,

12:22:03 > } h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style><title>SQL*Plus Report</title>" BODY "" TABLE "border='1' width='90%' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PRE OFF
12:22:03 SQL> set markup CSV OFF DELIMITER , QUOTE ON
12:22:03 SQL> set newpage 1
12:22:03 SQL> set null ""
12:22:03 SQL> set numformat ""
12:22:03 SQL> set numwidth 10
12:22:03 SQL> set pagesize 100
12:22:03 SQL> set pause OFF
12:22:03 SQL> set recsep WRAP
12:22:03 SQL> set recsepchar " "
12:22:03 SQL> set rowlimit OFF
12:22:03 SQL> set rowprefetch 1
12:22:03 SQL> set securedcol OFF
12:22:03 SQL> set serveroutput OFF
12:22:03 SQL> set shiftinout invisible
12:22:03 SQL> set showmode OFF
12:22:03 SQL> set sqlblanklines OFF
12:22:03 SQL> set sqlcase MIXED
12:22:03 SQL> set sqlcontinue "> "
12:22:03 SQL> set sqlnumber ON
12:22:03 SQL> set sqlpluscompatibility 19.0.0
12:22:03 SQL> set sqlprefix "#"
12:22:03 SQL> set sqlprompt "SQL> "
12:22:03 SQL> set sqlterminator ";"
12:22:03 SQL> set statementcache 0
12:22:03 SQL> set suffix "sql"
12:22:03 SQL> set tab OFF
12:22:03 SQL> set termout ON
12:22:03 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> 

7) Open the newly created PDB now and validate.

SQL> show con_name;

CON_NAME
------------------------------
PDB2
 
SQL> alter pluggable database open;

Pluggable database altered.

SQL> col name for a20;
SQL> select name, open_mode from V$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB2                 READ WRITE

1 row selected.

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         5 PDB2                           READ WRITE NO

The conversion has successfully completed at this point. Your newly migrated PDB is available for use now.

I hope this helps !!

No comments:

Post a Comment