DEV Community

Cover image for How to convert Non-CDB to PDB using noncdb_to_pdb.sql
Project-42
Project-42

Posted on

How to convert Non-CDB to PDB using noncdb_to_pdb.sql

I posted recently How to Convert Non-CDB to PDB using Autoupgrade but I thought was good to know how it was done before Autoupgrade and also, to see how to do it in case you dont want to use Autoupgrade (even though I really reocmend it, is much more simple and specially easy to scale up)

The best document to follow for this would be How to Convert Non-CDB to PDB Database in 12c - Testcase (Doc ID 2012448.1))

Like we did in previous post, the idea will be to convert the Database nocdb19 into a cdb19 PDB, so let's start the process already

1.- To convert non-CDB to PDB, you have to cleanly shutdown the DB:
2.- Once the DB is shutdown cleanly, open it in read only mode:

[oracle@rac1-node1 ~]$ srvctl stop database -d nocdb19 
[oracle@rac1-node1 ~]$ srvctl start instance -i nocdb191 -d nocdb19 -o "read only"
[oracle@rac1-node1 ~]$ 


DB_NAME   DB_UNIQUE_NAME  INSTANCE_NAME   CDB  HOST_NAME               DATABASE_ROLE    OPEN_MODE    STATUS
--------- --------------- --------------- ---- ----------------------- ---------------- ------------ -----------
NOCDB19   nocdb19         nocdb191        NO   rac1-node1.raclab.local PRIMARY          READ ONLY    OPEN

nocdb191 - SQL>
Enter fullscreen mode Exit fullscreen mode

3.- Describe the database and generate the xml file:

nocdb191 - SQL>
BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => '/home/oracle/nocdb19.xml');
END;
/nocdb191 - SQL>  2    3    4  

PL/SQL procedure successfully completed.

nocdb191 - SQL>
Enter fullscreen mode Exit fullscreen mode

4.- Shutdown the database.

[oracle@rac1-node1 ~]$ srvctl stop database -d nocdb19
[oracle@rac1-node1 ~]$ 
Enter fullscreen mode Exit fullscreen mode

5.- Check if it is compatible with cdb, run below in target CDB

cdb191 - SQL>SET SERVEROUTPUT ON;
DECLARE
compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/home/oracle/nocdb19.xml')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
cdb191 - SQL>  2    3    4    5    6    7    8    9  YES

PL/SQL procedure successfully completed.

cdb191 - SQL>
Enter fullscreen mode Exit fullscreen mode

6.- Connect to the CDB where database has to be plugged in:
To avoid conflicts, I changed PDB name to NOCDB192, but this whould not happen in a clean CDB, I just did few tests before this one

[oracle@rac1-node1 ~]$ cat create_nocdb19_pdb.sql
alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss';
set time on
show con_name
CREATE PLUGGABLE DATABASE NOCDB192 USING '/home/oracle/nocdb19.xml' 
COPY 
FILE_NAME_CONVERT = NONE;
[oracle@rac1-node1 ~]$ 
Enter fullscreen mode Exit fullscreen mode

7.- Execute the script created

[oracle@rac1-node1 ~]$ nohup sqlplus / as sysdba @create_nocdb19_pdb.sql &
[1] 26694
[oracle@rac1-node1 ~]$ nohup: ignoring input and appending output to ‘nohup.out’

- Alertlog:
2021-03-20T10:56:20.073209+00:00
CREATE PLUGGABLE DATABASE NOCDB192 USING '/home/oracle/nocdb19.xml'
COPY
FILE_NAME_CONVERT = NONE
(END)
[.....]
NOCDB192(6):queued detach DA request 0xae138a10 for pdb 6, ospid 20671 
2021-03-20T10:57:23.343941+00:00
Increasing priority of 2 RS
Domain Action Reconfiguration started (domid 6, new da inc 2, cluster inc 4)
Instance 1 is detaching from domain 6 (lazy abort? 0) 
 Global Resource Directory partially frozen for domain action
* domain detach - domain 6 valid ? 1
 Non-local Process blocks cleaned out
 Set master node info 
 Dwn-cvts replayed, VALBLKs dubious
 All grantable enqueues granted
freeing rdom 6
freeing the fusion rht of pdb 6
freeing the pdb enqueue rht 
Domain Action Reconfiguration complete (total time 0.0 secs) 
Decreasing priority of 2 RS
Completed: CREATE PLUGGABLE DATABASE NOCDB192 USING '/home/oracle/nocdb19.xml'
COPY
FILE_NAME_CONVERT = NONE
[oracle@rac1-node1 ~]$ 


cdb191 - SQL>show pdbs

    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED           READ ONLY  NO
     3 PDB191             READ WRITE NO
     4 NOCDB19COPY            READ WRITE NO
     5 NOCDB192           MOUNTED
cdb191 - SQL>
Enter fullscreen mode Exit fullscreen mode

8.- Switch to the PDB container and run the script "$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql"
I created a small script to execute it from OS using nohup

[oracle@rac1-node1 ~]$ cat convert_nocdb19_to_pdb.sql 
show pdbs
alter session set container=NOCDB192;
alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss';
set time on
show con_name
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
[oracle@rac1-node1 ~]$ 


[oracle@rac1-node1 ~]$ nohup sqlplus / as sysdba @convert_nocdb19_to_pdb.sql &
[1] 27409
[oracle@rac1-node1 ~]$ nohup: ignoring input and appending output to ‘nohup.out’

[oracle@rac1-node1 ~]$ 



[oracle@rac1-node1 ~]$ tail -150f nohup.out 
[....]

    CON_ID CON_NAME           OPEN MODE  RESTRICTED
--------------- ------------------------------ ---------- ----------
     2 PDB$SEED           READ ONLY  NO
     3 PDB191             READ WRITE NO
     4 NOCDB19COPY        READ WRITE NO
     5 NOCDB192           MOUNTED

Session altered.


Session altered.


CON_NAME
-----------------------------------
NOCDB192
11:42:51 cdb191 - SQL>SET FEEDBACK 1
11:42:51 cdb191 - SQL>SET NUMWIDTH 10
[....]

12:02:08 cdb191 - SQL>set underline "-"
12:02:08 cdb191 - SQL>set verify OFF
12:02:08 cdb191 - SQL>set wrap ON
12:02:08 cdb191 - SQL>set xmloptimizationcheck OFF
12:02:08 cdb191 - SQL>
Enter fullscreen mode Exit fullscreen mode

9.- Startup the PDB and check the open mode.

cdb191 - SQL>show pdbs

    CON_ID CON_NAME           OPEN MODE  RESTRICTED
--------------- ------------------------------ ---------- ----------
     2 PDB$SEED           READ ONLY  NO
     3 PDB191             READ WRITE NO
     4 NOCDB19COPY        READ WRITE NO
     5 NOCDB192           MOUNTED

cdb191 - SQL>ALTER PLUGGABLE DATABASE NOCDB192 OPEN INSTANCES=ALL;

Pluggable database altered.


DB_NAME   DB_UNIQUE_NAME         INSTANCE_NAME        CDB HOST_NAME               STARTUP                      DATABASE_ROLE    OPEN_MODE        STATUS
-------------- ------------------------------ -------------------- --- ----------------------------------- ---------------------------------------- ---------------- -------------------- ------------
CDB19     cdb19              cdb192           YES rac1-node2.raclab.local         20-MAR-2021 09:08:50             PRIMARY      READ WRITE       OPEN
CDB19     cdb19              cdb191           YES rac1-node1.raclab.local         20-MAR-2021 09:08:48             PRIMARY      READ WRITE       OPEN

   INST_ID     CON_ID NAME         OPEN_MODE  OPEN_TIME                    STATUS
--------------- ---------- -------------------- ---------- ---------------------------------------- ----------
     1      2 PDB$SEED         READ ONLY  20-MAR-21 09.10.14.399 +00:00        NORMAL
     2      2 PDB$SEED         READ ONLY  20-MAR-21 09.10.30.588 +00:00        NORMAL
     1      3 PDB191           READ WRITE 20-MAR-21 09.10.17.214 +00:00        NORMAL
     2      3 PDB191           READ WRITE 20-MAR-21 09.10.33.451 +00:00        NORMAL
     1      4 NOCDB19COPY      READ WRITE 20-MAR-21 09.53.58.317 +00:00        NORMAL
     2      4 NOCDB19COPY      READ WRITE 20-MAR-21 09.54.13.416 +00:00        NORMAL
     1      5 NOCDB192         READ WRITE 20-MAR-21 12.05.03.594 +00:00        NORMAL
     2      5 NOCDB192         READ WRITE 20-MAR-21 12.04.57.371 +00:00        NORMAL

8 rows selected.

cdb191 - SQL>
Enter fullscreen mode Exit fullscreen mode

By the way, if you are using anything older than 19.9, remember to install Patch 25809128 in order to speed up the process

Bug 25809128 - To improve the overall runtime (performance) of the noncdb_to_pdb.sql script (Doc ID 25809128.8)


Migrating to PDB using noncdb_to_pdb.sql? Be sure to be on 19.9.0 or later. Bug 25809128 will speed things up considerably.

— Daniel Overby Hansen (@dohdatabase) May 5, 2021

Top comments (0)