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
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)