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>

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>

4.- Shutdown the database.

[oracle@rac1-node1 ~]$ srvctl stop database -d nocdb19
[oracle@rac1-node1 ~]$

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>

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 ~]$

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>

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>

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>

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

Blog

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

Project-42

Join Our Newsletter. No Spam, Only the good stuff.

Related