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 \ -ignorePreReqsExecute it:
chmod 755 cre_db.sh ./cre_db.sh3. 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; exit4. 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 noncdbtopdbExecute it:
chmod 755 cre_sample_file.sh ./cre_sample_file.shThe 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.2There 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 analyzeExecute it:
chmod 755 analyze_db.sh ./analyze_db.shIn 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 deployExecute it:
chmod 755 deploy.sh ./deploy.shThe 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.logLooking 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 reuseto 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
No comments:
Post a Comment