DEV Community

Cover image for How to Convert Non-CDB to PDB using Autoupgrade
Project-42
Project-42

Posted on

How to Convert Non-CDB to PDB using Autoupgrade

If you didn't hear it enough already, Multitenant and Pluggable Databases (PDBs) are here to stay. In fact, 21c (already here for Cloud users) is already the first "Only Multitenant" Version.
So, what do you do with all those Non-CDB systems you didn't want to convert to PDB? Easy, just Migrate them to your CDB.
And how do you do that you ask? Even easier, using Autoupgrade.

I'm in love with Autoupgrade and how simplifies things specially for people than don't get involve in many upgrades or migrations, and the fact that this "little piece of magic" allows you not only to Upgrade systems, but also to migrate them so same versions systems, is just another reason why I like it every day a bit more.

Let's imaging the following scenario: we have a Non-CDB database in 19c but we just realized it should be a CDB.
There is not magic conversion from Non-CDB to CDB, so we will need to migrate it and convert the Non-CDB into a PDB that we can plug into a CDB system.
Lucky for us, Autoupgrade does all the steps in just one go.

For this example, the Non-CDB RAC database is called "nocdb19" and our CDB target is called "cdb19"

The process we need to follow is just as simple as when we did all the previous tests with Autoupgrade.

We create the Autoupgrade config file which will tell the script the source and target systems.

If you want to check more options (like changing PDB name etc..) have a look to more options in the Database Upgrade Guide

[oracle@rac1-node1 AutoUpgrade]$ cat /home/oracle/AutoUpgrade/nocdb19_to_cdb19_as_PDB.cfg
#
# Sample config file for AutoUpgrade
#
# build version 21.1.2
# build date    2021/02/24 17:11:08
#
global.autoupg_log_dir=/home/oracle/AutoUpgrade/global_logs
upg1.log_dir=/home/oracle/AutoUpgrade/convert_PDB_logs/

upg1.source_home=/u01/app/oracle/product/19.3.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/19.3.0/dbhome_1
upg1.sid=nocdb191
upg1.target_cdb=cdb191
[oracle@rac1-node1 AutoUpgrade]$ 
Enter fullscreen mode Exit fullscreen mode

To make sure all is fine, we run the script in Analyze mode and check for errors

[oracle@rac1-node1 AutoUpgrade]$ java -jar /home/oracle/AutoUpgrade/autoupgrade.jar -config /home/oracle/AutoUpgrade/nocdb19_to_cdb19_as_PDB.cfg -mode analyze
AutoUpgrade tool launched with default options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be analyzed
Type 'help' to list console commands
upg> Job 100 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished                  [1]
Jobs failed                    [0]
Jobs pending                   [0]

Please check the summary report at: 
/home/oracle/AutoUpgrade/global_logs/cfgtoollogs/upgrade/auto/status/status.html
/home/oracle/AutoUpgrade/global_logs/cfgtoollogs/upgrade/auto/status/status.log
[oracle@rac1-node1 AutoUpgrade]$ 

-- No errors/warnings

[oracle@rac1-node1 AutoUpgrade]$ egrep -i 'error|warning' convert_PDB_logs/nocdb191/100/autoupgrade_20210318.log 
[oracle@rac1-node1 AutoUpgrade]$ 
Enter fullscreen mode Exit fullscreen mode

Since my system is not the fastest, I executed the deployment using nohup and noconsole mode

[oracle@rac1-node1 AutoUpgrade]$ nohup java -jar /home/oracle/AutoUpgrade/autoupgrade.jar -config /home/oracle/AutoUpgrade/nocdb19_to_cdb19_as_PDB.cfg -mode deploy -noconsole &
[1] 27001
[oracle@rac1-node1 AutoUpgrade]$ nohup: ignoring input and appending output to ‘nohup.out’

[oracle@rac1-node1 AutoUpgrade]$ 
Enter fullscreen mode Exit fullscreen mode

After some time, we can see the job completed

2021-03-18 18:17:03.485 INFO Closed Pdb Count for container [NOCDB19] [0] - DBUtils.pdbBounceRequired 
2021-03-18 18:17:03.487 INFO Finished - DBUtils.openPDBs 
2021-03-18 18:17:03.487 INFO Finished - Database.restartDB 
2021-03-18 18:17:03.487 INFO The pdb(s) created in database nocdb191 were successfully restarted - FinalRestart.restart 
2021-03-18 18:17:03.488 INFO Ending PostUpgradeWrap task - NonCDBToPDB.executeNonCDBToPDB 
2021-03-18 18:17:03.497 INFO Starting ValidateNewPDB task - NonCDBToPDB.executeNonCDBToPDB 
2021-03-18 18:17:05.607 INFO Ending ValidateNewPDB task - NonCDBToPDB.executeNonCDBToPDB 
2021-03-18 18:17:05.615 INFO Starting WindowServices task - NonCDBToPDB.executeNonCDBToPDB 
2021-03-18 18:17:05.616 INFO Ending WindowServices task - NonCDBToPDB.executeNonCDBToPDB 
2021-03-18 18:17:05.641 INFO NonCDBToPDB completed - ExecuteNonCDBToPDB.executeStage 
2021-03-18 18:17:05.656 INFO Completed job NONCDBTOPDB - AbstractHelper.apply 
2021-03-18 18:17:05.661 INFO Dispatcher finished for nocdb191 - DeployModeHelper.executeDeploy 
2021-03-18 18:17:05.664 INFO ----------------------Stages  Summary------------------------ - DispatcherOSHelper.writeStageSummary 
2021-03-18 18:17:05.664 INFO     SETUP             <1 min                                  - DispatcherOSHelper.writeStageSummary 
2021-03-18 18:17:05.665 INFO     NONCDBTOPDB       27 min                                  - DispatcherOSHelper.writeStageSummary 
2021-03-18 18:17:05.665 INFO End of dispatcher instance for nocdb19 - AutoUpgDispatcher.run 
Enter fullscreen mode Exit fullscreen mode

And voila!, we got a new PDB called NOCDB19 in our CDB system "cdb19"

DB_NAME   INSTANCE_NAME   CDB HOST_NAME                 DATABASE_ROLE   OPEN_MODE
--------- --------------- --- ------------------------- ---------------- -----------
CDB19     cdb192          YES rac1-node2.raclab.local   PRIMARY         READ WRITE
CDB19     cdb191          YES rac1-node1.raclab.local   PRIMARY         READ WRITE

SQL> SQL> SQL>   2    3    4  
   INST_ID     CON_ID NAME         OPEN_MODE  OPEN_TIME                    STATUS
--------------- ---------- -------------------- ---------- ---------------------------------------- ----------
     1      2 PDB$SEED         READ ONLY  18-MAR-21 08.13.50.971 +00:00        NORMAL
     2      2 PDB$SEED         READ ONLY  18-MAR-21 08.13.53.962 +00:00        NORMAL
     1      3 PDB191           READ WRITE 18-MAR-21 08.13.53.700 +00:00        NORMAL
     2      3 PDB191           READ WRITE 18-MAR-21 08.13.56.411 +00:00        NORMAL

     1      4 NOCDB19          READ WRITE 18-MAR-21 18.16.47.420 +00:00        NORMAL <<<<<
     2      4 NOCDB19          READ WRITE 18-MAR-21 18.16.55.730 +00:00        NORMAL <<<<<

6 rows selected.

SQL>
Enter fullscreen mode Exit fullscreen mode

Since the Copy option was not specified, we are still using same datafiles of the source system

CON_ID NAME         FILE_ID TABLESPACE_NAME    FILE_NAME
----------- ------------ ------- --------------- ------------------------------------------------------------------------------
     3 PDB191            20 OES                +DATA/CDB19/9546EDCAA0357560E0530C01A8C0441E/DATAFILE/oes.366.1044894879
     3 PDB191            21 SOE                +DATA/CDB19/9546EDCAA0357560E0530C01A8C0441E/DATAFILE/soe.267.1044895011
     3 PDB191            11 SYSAUX             +DATA/CDB19/9546EDCAA0357560E0530C01A8C0441E/DATAFILE/sysaux.286.1022088461
     3 PDB191            10 SYSTEM             +DATA/CDB19/9546EDCAA0357560E0530C01A8C0441E/DATAFILE/system.285.1022088461
     3 PDB191            12 UNDOTBS1           +DATA/CDB19/9546EDCAA0357560E0530C01A8C0441E/DATAFILE/undotbs1.284.1022088461
     3 PDB191            13 UNDO_2             +DATA/CDB19/9546EDCAA0357560E0530C01A8C0441E/DATAFILE/undo_2.288.1022088513
     3 PDB191            14 USERS              +DATA/CDB19/9546EDCAA0357560E0530C01A8C0441E/DATAFILE/users.289.1022088517

     4 NOCDB19           39 P42                +DATA/NOCDB19/DATAFILE/p42.402.1067523581       <<<<<<
     4 NOCDB19           38 P42                +DATA/NOCDB19/DATAFILE/p42.403.1067523571       <<<<<<
     4 NOCDB19           34 SYSAUX             +DATA/NOCDB19/DATAFILE/sysaux.290.1067502169    <<<<<<
     4 NOCDB19           33 SYSTEM             +DATA/NOCDB19/DATAFILE/system.292.1067502077    <<<<<<
     4 NOCDB19           35 UNDOTBS1           +DATA/NOCDB19/DATAFILE/undotbs1.293.1067502215  <<<<<<
     4 NOCDB19           37 UNDOTBS2           +DATA/NOCDB19/DATAFILE/undotbs2.396.1067502757  <<<<<<
     4 NOCDB19           36 USERS              +DATA/NOCDB19/DATAFILE/users.294.1067502217     <<<<<<

14 rows selected.

SQL> 
Enter fullscreen mode Exit fullscreen mode

To resolve that "inconsistency", we just move the datafiles (this is an online operation since 12c)
Remember the TEMP files!!

SQL> alter database move datafile 38;

Database altered.

SQL> alter database move datafile 39;

Database altered.


SQL> select file_id, tablespace_name, File_name
from dba_data_files
where TABLESPACE_NAME = 'P42'
order by 1 asc
/  2    3    4    5  

FILE_ID TABLESPACE_NAME            FILE_NAME
------------ ------------------------------ --------------------------------------------------------------------------------
     38 P42                +DATA/CDB19/BDCC558660BA7D78E0530B01A8C0ABA3/DATAFILE/p42.398.1067539287
     39 P42                +DATA/CDB19/BDCC558660BA7D78E0530B01A8C0ABA3/DATAFILE/p42.403.1067539303


SQL> alter database move datafile  33; 
alter database move datafile  34; 
alter database move datafile  35; 
alter database move datafile  36; 
alter database move datafile  37;

Database altered.
[....]


CON_ID NAME         FILE_ID TABLESPACE_NAME        FILE_NAME
----------- -------------------- ------- ------------------------------ -----------------------------------------------------------------------------------------------
     3 PDB191            20 OES                +DATA/CDB19/9546EDCAA0357560E0530C01A8C0441E/DATAFILE/oes.366.1044894879
     3 PDB191            21 SOE                +DATA/CDB19/9546EDCAA0357560E0530C01A8C0441E/DATAFILE/soe.267.1044895011
     3 PDB191            11 SYSAUX             +DATA/CDB19/9546EDCAA0357560E0530C01A8C0441E/DATAFILE/sysaux.286.1022088461
     3 PDB191            10 SYSTEM             +DATA/CDB19/9546EDCAA0357560E0530C01A8C0441E/DATAFILE/system.285.1022088461
     3 PDB191            12 UNDOTBS1           +DATA/CDB19/9546EDCAA0357560E0530C01A8C0441E/DATAFILE/undotbs1.284.1022088461
     3 PDB191            13 UNDO_2             +DATA/CDB19/9546EDCAA0357560E0530C01A8C0441E/DATAFILE/undo_2.288.1022088513
     3 PDB191            14 USERS              +DATA/CDB19/9546EDCAA0357560E0530C01A8C0441E/DATAFILE/users.289.1022088517
     4 NOCDB19           39 P42                +DATA/CDB19/BDCC558660BA7D78E0530B01A8C0ABA3/DATAFILE/p42.403.1067539303
     4 NOCDB19           38 P42                +DATA/CDB19/BDCC558660BA7D78E0530B01A8C0ABA3/DATAFILE/p42.398.1067539287
     4 NOCDB19           34 SYSAUX             +DATA/CDB19/BDCC558660BA7D78E0530B01A8C0ABA3/DATAFILE/sysaux.292.1067539609
     4 NOCDB19           33 SYSTEM             +DATA/CDB19/BDCC558660BA7D78E0530B01A8C0ABA3/DATAFILE/system.402.1067539569
     4 NOCDB19           35 UNDOTBS1           +DATA/CDB19/BDCC558660BA7D78E0530B01A8C0ABA3/DATAFILE/undotbs1.290.1067539637
     4 NOCDB19           37 UNDOTBS2           +DATA/CDB19/BDCC558660BA7D78E0530B01A8C0ABA3/DATAFILE/undotbs2.294.1067539679
     4 NOCDB19           36 USERS              +DATA/CDB19/BDCC558660BA7D78E0530B01A8C0ABA3/DATAFILE/users.293.1067539677

14 rows selected.

SQL> 
Enter fullscreen mode Exit fullscreen mode

We can now delete the old non-CDB system from clusterware

[oracle@rac1-node1 AutoUpgrade]$ srvctl config
cdb121
cdb122
cdb18
cdb19
db112
db121
db122
db18
db19
nocdb19 <<<<<<<<
single
TEST
upg11
upg19
[oracle@rac1-node1 AutoUpgrade]$ 


[oracle@rac1-node1 AutoUpgrade]$ srvctl remove database -d nocdb19
Remove the database nocdb19? (y/[n]) y
[oracle@rac1-node1 AutoUpgrade]$ 
Enter fullscreen mode Exit fullscreen mode

Just in case you prefer to keep a copy of the system you are migrating, you just need to add the copy option (target_pdb_copy_option) to the Autoupgrade configuration file
Below you can see same scenario after I recreated the Source system again

[oracle@rac1-node1 AutoUpgrade]$ cat nocdb19_to_cdb19_as_PDB_copy.cfg
#
# Sample config file for AutoUpgrade
#
# build version 21.1.2
# build date    2021/02/24 17:11:08
#
global.autoupg_log_dir=/home/oracle/AutoUpgrade/global_copy_logs
upg1.log_dir=/home/oracle/AutoUpgrade/convert_PDB_copy_logs/

upg1.source_home=/u01/app/oracle/product/19.3.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/19.3.0/dbhome_1
upg1.sid=nocdb191
upg1.target_cdb=cdb191
upg1.target_pdb_copy_option=file_name_convert=NONE
[oracle@rac1-node1 AutoUpgrade]$ 
Enter fullscreen mode Exit fullscreen mode

Deploy the script the same way

[oracle@rac1-node1 AutoUpgrade]$ nohup java -jar /home/oracle/AutoUpgrade/autoupgrade.jar -config /home/oracle/AutoUpgrade/nocdb19_to_cdb19_as_PDB_copy.cfg -mode deploy -noconsole &
[1] 11162
[oracle@rac1-node1 AutoUpgrade]$ nohup: ignoring input and appending output to ‘nohup.out’
Enter fullscreen mode Exit fullscreen mode

In case you want to know "what is going on", you can check your Autoupgrade log and temp locations and see the different scripts and options are used.

[oracle@rac1-node1 AutoUpgrade]$ cat convert_PDB_copy_logs/nocdb191/temp/createpdb_nocdb19_NOCDB19COPY.sql 
set echo OFF
set verify ON
set define "&"


Rem Name of the new pluggable database that will be created in CDB cdb191.  Do not change this value
define pdbName = "NOCDB19COPY"


Rem Path of the xml file that will be used to create the new pluggable database NOCDB19COPY.  Do not change this value
define xmlFilePath = "/home/oracle/AutoUpgrade/convert_PDB_copy_logs/nocdb191/101/noncdbtopdb/NOCDB19COPY.xml"


Rem The file_name_convert option.  Quotes must remain around the defined string.
define fileNameConvertOption = "COPY file_name_convert=NONE"


Rem Clone clause option. Quotes must remain around the defined string.
define asClone = "as clone"


create pluggable database "&pdbName" &asClone using '&xmlFilePath' &fileNameConvertOption tempfile reuse;

[oracle@rac1-node1 AutoUpgrade]$ 
Enter fullscreen mode Exit fullscreen mode

If you want to know more about those folders, have a look to this Video:

AutoUpgrade and extensive logging

After a while, the new PDB is ready


2021-03-20T09:54:17.643866+00:00
NOCDB19COPY(4):Completed: alter pluggable database "NOCDB19COPY" open read write force instances=all

2021-03-20 09:54:23.378 INFO Ending WindowServices task - NonCDBToPDB.executeNonCDBToPDB 
2021-03-20 09:54:23.407 INFO NonCDBToPDB completed - ExecuteNonCDBToPDB.executeStage 
2021-03-20 09:54:23.412 INFO Completed job NONCDBTOPDB - AbstractHelper.apply 
2021-03-20 09:54:23.417 INFO Dispatcher finished for nocdb191 - DeployModeHelper.executeDeploy 
2021-03-20 09:54:23.421 INFO ----------------------Stages  Summary------------------------ - DispatcherOSHelper.writeStageSummary 
2021-03-20 09:54:23.421 INFO     SETUP             <1 min                                  - DispatcherOSHelper.writeStageSummary 
2021-03-20 09:54:23.422 INFO     NONCDBTOPDB       25 min                                  - DispatcherOSHelper.writeStageSummary 
2021-03-20 09:54:23.422 INFO End of dispatcher instance for nocdb19 - AutoUpgDispatcher.run 


   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
Enter fullscreen mode Exit fullscreen mode

And since we used the copy option, we have the Datafiles copied and system is not using the source datafiles

FILE_ID TABLESPACE_NAME            FILE_NAME
------------ ------------------------------ --------------------------------------------------------------------------------
     45 SYSTEM                 +DATA/CDB19/BDF581683E5E1A9CE0530B01A8C0CB14/DATAFILE/system.340.1067679129
     46 SYSAUX                 +DATA/CDB19/BDF581683E5E1A9CE0530B01A8C0CB14/DATAFILE/sysaux.339.1067679129
     47 UNDOTBS1               +DATA/CDB19/BDF581683E5E1A9CE0530B01A8C0CB14/DATAFILE/undotbs1.354.1067679125
     48 USERS                  +DATA/CDB19/BDF581683E5E1A9CE0530B01A8C0CB14/DATAFILE/users.388.1067679125
     49 UNDOTBS2               +DATA/CDB19/BDF581683E5E1A9CE0530B01A8C0CB14/DATAFILE/undotbs2.344.1067679125
     50 P42                +DATA/CDB19/BDF581683E5E1A9CE0530B01A8C0CB14/DATAFILE/p42.306.1067679125
     51 P42                +DATA/CDB19/BDF581683E5E1A9CE0530B01A8C0CB14/DATAFILE/p42.325.1067679125

7 rows selected.

SQL> 
Enter fullscreen mode Exit fullscreen mode

Very easy right?

So time to migrate all your systems!!


** WARNING!! **



If you use old Autoupgrade versions, you can find little hiccups like Migration failing to restart correctly one of the Source Systems.
I validated this is NOT longer an issue in current version 20210421, so please ALWAYS use the latest Version from AutoUpgrade Tool (Doc ID 2485457.1)
-- Upgrade failed since the non-CDB failed to get restarted correctly:

-- Info from /u01/app/oracle/diag/rdbms/nocdb19/nocdb191/trace/alert_nocdb191.log

2021-03-18T17:14:42.731632+00:00
alter database open read only
2021-03-18T17:14:42.846064+00:00
AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access
2021-03-18T17:14:42.852026+00:00
Errors in file /u01/app/oracle/diag/rdbms/nocdb19/nocdb191/trace/nocdb191_ora_28231.trc:
ORA-16002: database or pluggable database already open for read/write access by another instance
ORA-16002 signalled during: alter database open read only...
2021-03-18T17:14:43.277847+00:00
ALTER SYSTEM SET remote_listener=' rac1-scan:1521' SCOPE=MEMORY SID='nocdb191';
2021-03-18T17:14:43.279822+00:00
ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='nocdb191';

-- Info from convert_PDB_logs/nocdb191/101/autoupgrade_20210318.log

2021-03-18 17:13:24.874 INFO Ending QuickPluginCompatibility task - NonCDBToPDB.executeNonCDBToPDB 
2021-03-18 17:13:24.890 INFO Starting PerformDescribe task - NonCDBToPDB.executeNonCDBToPDB 
2021-03-18 17:14:43.649 ERROR NonCDBToPDB failed: oracle.upgrade.autoupgrade.utils.errors.AutoUpgException: AutoUpgException [UPG-2002#ORACLE instance started.
Total System Global Area 5804912944 bytes
Fixed Size              8909104 bytes
Variable Size        1090519040 bytes
Database Buffers     4697620480 bytes
Redo Buffers            7864320 bytes
Database mounted.
alter database open read only
*
ERROR at line 1:
ORA-16002: database or pluggable database already open for read/write access by another instance


] - ExecuteNonCDBToPDB.executeStage 
oracle.upgrade.autoupgrade.utils.errors.AutoUpgException: AutoUpgException [UPG-2002#ORACLE instance started.
Total System Global Area 5804912944 bytes
Fixed Size              8909104 bytes
Variable Size        1090519040 bytes
Database Buffers     4697620480 bytes
Redo Buffers            7864320 bytes
Database mounted.
alter database open read only
*
ERROR at line 1:
ORA-16002: database or pluggable database already open for read/write access by another instance


-- Restarted the system with only 1 instance:

[oracle@rac1-node1 AutoUpgrade]$ srvctl stop database -d nocdb19 ; srvctl start instance -i nocdb191 -d nocdb19
[oracle@rac1-node1 AutoUpgrade]$ 


[oracle@rac1-node1 AutoUpgrade]$ nohup java -jar /home/oracle/AutoUpgrade/autoupgrade.jar -config /home/oracle/AutoUpgrade/nocdb19_to_cdb19_as_PDB.cfg -mode deploy -noconsole &
[1] 1622
[oracle@rac1-node1 AutoUpgrade]$ nohup: ignoring input and appending output to ‘nohup.out’

[oracle@rac1-node1 AutoUpgrade]$

-- Everything completed fine
Enter fullscreen mode Exit fullscreen mode

Top comments (0)