The autoupgrade tool can convert a non-cdb database to a PDB running in a container database.
If you need to switch to a multitenant architechture, this could be a way forward.
In my case, I had a non-cdb database of versjon 19c running on my server testserver1.oric.no, and I want to convert it to a PDB.
This is what I did to move away from the non-cdb architecture:
1. Download the latest version of autoupgrade from Oracle Support: AutoUpgrade Tool (Doc ID 2485457.1)
On your database server, make a backup of the existing $ORACLE_HOME/rdbms/admin/autoupgrade.jar file, then transfer the new version of the file you just downloaded from Oracle into the same folder.
2. create a container database.
You can execute the database configuration assistant silently to quickly set one up. Put this in a shell script and call it for example "cre_cdb.sh":
export ORACLE_SID=cdb
dbca -silent -createDatabase \
-templateName General_Purpose.dbc \
-gdbname cdb -sid cdb -responseFile NO_VALUE \
-characterSet AL32UTF8 \
-sysPassword secret \
-systemPassword secret \
-createAsContainerDatabase true \
-numberOfPDBs 0 \
-databaseType MULTIPURPOSE \
-memoryMgmtType auto_sga \
-totalMemory 2048 \
-storageType FS \
-datafileDestination "/data01/oradata/" \
-redoLogFileSize 50 \
-emConfiguration NONE \
-ignorePreReqs
Execute it:
chmod 755 cre_db.sh
./cre_db.sh
3. Set some parameters in the cdb:
alter system set db_recovery_file_dest_size=10G;
alter system set db_recovery_file_dest='/fra';
alter system set db_create_file_dest = '/data01/oradata';
shutdown immediate
startup mount
alter database archivelog;
alter database open;
exit
4. When the container database is ready, create a configuration file.
Put this in a file called "cre_sample_file.sh":
$ORACLE_BASE/product/19c/jdk/bin/java \
-jar $ORACLE_BASE/product/19c/rdbms/admin/autoupgrade.jar \
-create_sample_file config /sw/oracle/admin/mydb/sql/config.txt noncdbtopdb
Execute it:
chmod 755 cre_sample_file.sh
./cre_sample_file.sh
The output should be a file that you now open in an editor and edit. This is how mine looked:
upg1.log_dir=/sw/oracle/cfgtoollogs/autoupgrade/mydb
upg1.sid=mydb
upg1.source_home=/sw/oracle/product/19c
upg1.target_cdb=cdb
upg1.target_home=/sw/oracle/product/19c
upg1.target_pdb_name=mydb
upg1.start_time=NOW # Optional. 10 Minutes from now
upg1.upgrade_node=testserver1.oric.no # Optional. To find out the name of your node, run the hostname utility. Default is ''localhost''
upg1.run_utlrp=yes # Optional. Whether or not to run utlrp after upgrade
upg1.target_version=19 # Oracle version of the target ORACLE_HOME. Only required when the target Oracle database version is 12.2
There are many additional options that could potentially be relevant, but in my case the ones listed above was all that was needed.
5. Analyze the database before converting.
Put the following in a script called "analyze_db.sh":
$ORACLE_BASE/product/19c/jdk/bin/java \
-jar $ORACLE_BASE/product/19c/rdbms/admin/autoupgrade.jar \
-config /sw/oracle/admin/mydb/sql/config.txt -mode analyze
Execute it:
chmod 755 analyze_db.sh
./analyze_db.sh
In my case, this job returned very quickly, since there is no upgrade job do be done, we are simply converting from an non-cdb to a PDB!
6. Finally, convert the database to a pdb.
Put the following into a file called "deploy.sh":
$ORACLE_BASE/product/19c/jdk/bin/java \
-jar $ORACLE_BASE/product/19c/rdbms/admin/autoupgrade.jar \
-config /sw/oracle/admin/mydb/sql/config.txt -mode deploy
Execute it:
chmod 755 deploy.sh
./deploy.sh
The deploy phase goes through several steps and leaves you at the prompt. You can watch the progress by executing "lsj" at the prompt:
AutoUpgrade 23.1.230224 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 Non-CDB(s) will be processed
Type 'help' to list console commands
upg> lsj
+----+-------+-----------+---------+-------+----------+-------+-------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+----+-------+-----------+---------+-------+----------+-------+-------------------+
| 101| mydb|NONCDBTOPDB|EXECUTING|RUNNING| 15:19:20| 1s ago|Compatibility check|
+----+-------+-----------+---------+-------+----------+-------+-------------------+
Total jobs 1
upg> lsj
+----+-------+-----------+---------+-------+----------+-------+------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|
+----+-------+-----------+---------+-------+----------+-------+------------------+
| 101| mydb|NONCDBTOPDB|EXECUTING|RUNNING| 15:19:20| 3s ago|Executing describe|
+----+-------+-----------+---------+-------+----------+-------+------------------+
Total jobs 1
.
.
.
upg> lsj
+----+-------+---------+---------+-------+----------+-------+-------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED|MESSAGE|
+----+-------+---------+---------+-------+----------+-------+-------+
| 101| mydb|DBUPGRADE|EXECUTING|RUNNING| 15:19:20| 8s ago|Running|
+----+-------+---------+---------+-------+----------+-------+-------+
Total jobs 1
upg> Job 101 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished [1]
Jobs failed [0]
Jobs restored [0]
Jobs pending [0]
Please check the summary report at:
/sw/oracle/admin/mydb/sql/cfgtoollogs/upgrade/auto/status/status.html
/sw/oracle/admin/mydb/sql/cfgtoollogs/upgrade/auto/status/status.log
Looking at the log file /sw/oracle/admin/mydb/sql/cfgtoollogs/upgrade/auto/status/status.log I see that many of the stages were very quick to return:
==========================================
Autoupgrade Summary Report
==========================================
[Date] Mon Jun 19 15:28:58 CEST 2023
[Number of Jobs] 1
==========================================
[Job ID] 101
==========================================
[DB Name] mydb
[Version Before Upgrade] 19.19.0.0.0
[Version After Upgrade] 19.19.0.0.0
------------------------------------------
[Stage Name] PREUPGRADE
[Status] SUCCESS
[Start Time] 2023-06-19 15:19:20
[Duration] 0:00:00
[Log Directory] /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/preupgrade
------------------------------------------
[Stage Name] DRAIN
[Status] SUCCESS
[Start Time] 2023-06-19 15:19:21
[Duration] 0:00:00
[Log Directory] /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/drain
------------------------------------------
[Stage Name] NONCDBTOPDB
[Status] SUCCESS
[Start Time] 2023-06-19 15:19:21
[Duration] 0:08:51
[Log Directory] /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/noncdbtopdb
------------------------------------------
[Stage Name] DBUPGRADE
[Status] SUCCESS
[Start Time] 2023-06-19 15:28:12
[Duration] 0:00:42
[Log Directory] /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/dbupgrade
------------------------------------------
[Stage Name] POSTUPGRADE
[Status] SUCCESS
[Start Time] 2023-06-19 15:28:57
[Duration] 0:00:00
[Log Directory] /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/postupgrade
------------------------------------------
[Stage Name] SYSUPDATES
[Status] SUCCESS
[Start Time] 2023-06-19 15:28:58
[Duration]
[Log Directory] /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/sysupdates
------------------------------------------
Summary: /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/dbupgrade/datapatch_summary.log
If I move into the /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/noncdbtopdb folder, I can see
ls -altrh
total 480K
-rw------- 1 oracle dba 6.9K Jun 19 15:20 mydb-MYDB.xml
-rw------- 1 oracle dba 1.7K Jun 19 15:20 createpdb_mydb_MYDB.log
-rw------- 1 oracle dba 323K Jun 19 15:28 noncdbtopdb_mydb_MYDB.log
-rw------- 1 oracle dba 127K Jun 19 15:28 noncdb_to_pdb_mydb.log
drwx------ 2 oracle dba 4.0K Jun 19 15:28 .
drwx------ 8 oracle dba 4.0K Jun 19 15:28 ..
This stage executes the command
create pluggable database "MYDB" using '/sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/noncdbtopdb/mydb-MYDB.xml' NOCOPY tempfile reuse
to perform the actual plug-in operation of the old non-cdb database, so that it becomes a PDB. This was the only stage that took a bit of time to complete, almost 9 minutes.
There are numerous ways to migrate from the non-container architecture to multitenant architecture, this is just one of them.
Things to notice:
* My non-cdb database was called "mydb" - notice the lower case of the name. During the process, this was ignored and the database was renamed to MYDB when it was recreated as a PDB.
There seem to be no way to change this behaviour.
Credit to Tim Hall for the usual excellent work in his
blog post about the same topic, but also involving upgrading to a higher version of the Oracle software.
The official Oracle 19c documenation about
AutoUpgrade Configuration File for Non-CDB Upgrades on the Same System is essential reading
Lots of good stuff on the
Mike Dietrich blog
