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]$
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]$
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]$
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
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>
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>
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>
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]$
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]$
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’
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]$
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
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>
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
Top comments (0)