In this article, we’ll perform an upgrade from 12cR2 Non container database to a 19c pluggable database using autoupgrade. If you are not aware of this autoupgrade utility, I would recommend to read my blog post on this topic Oracle 19c AutoUpgrade Utility which explains the workflow, stages and modes in autoupgrade utility.
For 11gR2 (Non-CDB) to 19c (PDB) upgrade which was far more challenging, read Upgrade 11gR2 (Non-CDB) to 19c (PDB) using AutoUpgrade.
Pre-requisites & Setup
- Source Database Name: ORCL11
- Source Database Version: 12cR2
- Source Database Home: /apps/oracle/server/12.2.0.1/dbhome_1
- Target CDB: cdb1
- Target CDB Version: 19c
- Target Database Home: /apps/oracle/server/19.3.0.0/dbhome_1
- Both Source and Target are on same server
Login to the database server and check the autoupgrade version included in 19c binaries. Please ensure to use the latest version of Autoupgrade, so if needed Download the latest autoupgrade from MOS Note: 2485457.1 – AutoUpgrade Tool.
oracle> java -jar autoupgrade.jar -version
build.hash bf4ccd4
build.version 19.9.2
build.date 2020/08/31 13:47:51
build.max_target_version 19
build.supported_target_versions 12.2,18,19
build.type production
The only manual task of upgrading a database is to create the configuration file, please find the parameters used in the config file to upgrade a 12cR2 database to 19c (By copying the datafiles so that we have a safe failback option available):
global.autoupg_log_dir=/home/oracle/autoupgrade_global
global.target_home=/apps/oracle/server/19.3.0.0/dbhome_1
global.target_version=19.10
global.restoration=no
upg1.upgrade_node=localhost
upg1.source_home=/apps/oracle/server/11.2.0.4/dbhome_1
upg1.sid=ORCL11
upg1.dbname=ORCL11
upg1.start_time=NOW
upg1.target_cdb=cdb1
upg1.target_pdb_name=PDB12
upg1.target_pdb_copy_option=('/apps/oracle/oradata/ORCL11','/apps/oracle/oradata/PDB12')
upg1.log_dir=/home/oracle/upgrade11gR2
upg1.target_home=/apps/oracle/server/19.3.0.0/dbhome_1
upg1.target_version=19.10
upg1.run_utlrp=yes
upg1.timezone_upg=yes
If needed you can create a sample config file using the -create_sample_file argument of autoupgrade utility:
oracle> /apps/oracle/server/19.3.0.0/dbhome_1/jdk/bin/java -jar /apps/oracle/server/19.3.0.0/dbhome_1/rdbms/admin/autoupgrade.jar -create_sample_file config
Created sample configuration file /home/oracle/sample_config.cfg
oracle>
Once you have the config file created, run the autoupgrade utility in ANALYZE mode. (Refer Oracle 19c AutoUpgrade Utility to understand the details of different modes in autoupgrade.jar)
oracle> java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config 12cr2upgrade.cfg -mode ANALYZE
Previous execution found loading latest data
Total jobs recovered: 0
AutoUpgrade tool launched with default options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be analyzed
Type 'help' to list console commands
upg>
upg>
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+---------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+---------------+
| 102| ORCL11|PRECHECKS|PREPARING|RUNNING|21/04/13 17:22|17:22:41|Remaining 71/78|
+----+-------+---------+---------+-------+--------------+--------+---------------+
Total jobs 1
upg>
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+---------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+---------------+
| 102| ORCL11|PRECHECKS|PREPARING|RUNNING|21/04/13 17:22|17:22:44|Remaining 57/78|
+----+-------+---------+---------+-------+--------------+--------+---------------+
Total jobs 1
upg>
upg> status
---------------- Config -------------------
User configuration file [/home/oracle/12cr2upgrade.cfg]
General logs location [/home/oracle/autoupgrade_global/cfgtoollogs/upgrade/auto]
Mode [ANALYZE]
DB upg fatal errors ORA-00600,ORA-07445
DB Post upgrade abort time [60] minutes
DB upg abort time [1440] minutes
DB restore abort time [120] minutes
DB GRP abort time [3] minutes
------------------------ Jobs ------------------------
Total databases in configuration file [1]
Total Non-CDB being processed [1]
Total CDB being processed [0]
Jobs finished successfully [0]
Jobs finished/aborted [0]
Jobs in progress [1]
Jobs stage summary
Job ID: 102
DB name: ORCL11
SETUP <1 min
PRECHECKS <1 min (IN PROGRESS)
------------ Resources ----------------
Threads in use [38]
JVM used memory [61] MB
CPU in use [13%]
Processes in use [21]
upg>
upg>
upg> Job 102 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished successfully [1]
Jobs failed [0]
Jobs pending [0]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 102 for ORCL11
oracle>
oracle> cd /home/oracle/upgrade11gR2/ORCL11/
oracle> ls -ltr
total 8
drwx------ 2 oracle oinstall 4096 Apr 13 17:22 temp
drwx------ 3 oracle oinstall 4096 Apr 13 17:22 102
oracle> cd 102
oracle> ls -ltr
total 92
-rwx------ 1 oracle oinstall 0 Apr 13 17:22 autoupgrade_err.log
-rwx------ 1 oracle oinstall 296 Apr 13 17:22 autoupgrade_20210413_user.log
drwx------ 2 oracle oinstall 4096 Apr 13 17:22 prechecks
-rwx------ 1 oracle oinstall 83247 Apr 13 17:22 autoupgrade_20210413.log
oracle>
oracle> cd prechecks
oracle>
oracle> ls -ltr
total 212
-rwx------ 1 oracle oinstall 162613 Apr 13 17:22 prechecks_orcl11.log
-rwx------ 1 oracle oinstall 7625 Apr 13 17:22 orcl11_checklist.xml
-rwx------ 1 oracle oinstall 8044 Apr 13 17:22 orcl11_checklist.json
-rwx------ 1 oracle oinstall 2178 Apr 13 17:22 orcl11_checklist.cfg
-rwx------ 1 oracle oinstall 7729 Apr 13 17:22 orcl11_preupgrade.log
-rwx------ 1 oracle oinstall 23043 Apr 13 17:22 orcl11_preupgrade.html
oracle>
Review the logfiles to see if there were any errors in the analyze mode and more importantly to get a better understanding of the process. Here autoupgrade gives you a nice HTML file in the prechecks folder, most of the checks would be labelled as Fixup Available = YES and those would be taken care by Autoupgrade utility itself. You only have to focus on the ones where Fixup Available = NO (You can ignore the ones that are with severity INFO or RECOMMEND) if there are any.
Once the Fixup available = NO are fixed manually, we can trigger the upgrade in DEPLOY mode.
oracle> java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config 12cr2upgrade.cfg -mode DEPLOY
AutoUpgrade tool launched with default options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be processed
Type 'help' to list console commands
upg>
upg>
upg>
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+------------+
| 103| ORCL11|DBUPGRADE|EXECUTING|RUNNING|21/04/13 17:30|17:49:21|10%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+------------+
Total jobs 1
upg>
upg> status -job 103
Progress
-----------------------------------
Start time: 21/04/13 17:30
Elapsed (min): 20
End time: N/A
Last update: 2021-04-13T17:49:21.318
Stage: DBUPGRADE
Operation: EXECUTING
Status: RUNNING
Pending stages: 5
Stage summary:
SETUP <1 min
PREUPGRADE <1 min
PRECHECKS <1 min
PREFIXUPS 5 min
DRAIN 3 min
DBUPGRADE 10 min (IN PROGRESS)
Job Logs Locations
-----------------------------------
Logs Base: /home/oracle/upgrade11gR2/ORCL11
Job logs: /home/oracle/upgrade11gR2/ORCL11/103
Stage logs: /home/oracle/upgrade11gR2/ORCL11/103/dbupgrade
TimeZone: /home/oracle/upgrade11gR2/ORCL11/temp
Additional information
-----------------------------------
Details:
[Upgrading] is [10%] completed for [orcl11]
+---------+-------------+
|CONTAINER| PERCENTAGE|
+---------+-------------+
| ORCL11|UPGRADE [10%]|
+---------+-------------+
Error Details:
None
upg
To get details of what’s happening in the background you can tail -f the catupgrd*.log file in /home/oracle/upgrade11gR2/ORCL11/103/dbupgrade location. For brevity, I’ll just share the output of lsj and status commands of autoupgrade but I strongly recommend to monitor the logfiles.
Lets check the upgrade status using lsj & status commands of autoupgrade.
upg> /
+----+-------+---------+---------+-------+--------------+--------+------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+------------+
| 103| ORCL11|DBUPGRADE|EXECUTING|RUNNING|21/04/13 17:30|18:01:22|39%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+------------+
Total jobs 1
upg>
upg>
upg> status -job 103
Progress
-----------------------------------
Start time: 21/04/13 17:30
Elapsed (min): 33
End time: N/A
Last update: 2021-04-13T18:01:22.831
Stage: DBUPGRADE
Operation: EXECUTING
Status: RUNNING
Pending stages: 5
Stage summary:
SETUP <1 min
PREUPGRADE <1 min
PRECHECKS <1 min
PREFIXUPS 5 min
DRAIN 3 min
DBUPGRADE 23 min (IN PROGRESS)
Job Logs Locations
-----------------------------------
Logs Base: /home/oracle/upgrade11gR2/ORCL11
Job logs: /home/oracle/upgrade11gR2/ORCL11/103
Stage logs: /home/oracle/upgrade11gR2/ORCL11/103/dbupgrade
TimeZone: /home/oracle/upgrade11gR2/ORCL11/temp
Additional information
-----------------------------------
Details:
[Upgrading] is [39%] completed for [orcl11]
+---------+-------------+
|CONTAINER| PERCENTAGE|
+---------+-------------+
| ORCL11|UPGRADE [39%]|
+---------+-------------+
Error Details:
None
upg>
upg>
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+------------+
| 103| ORCL11|DBUPGRADE|EXECUTING|RUNNING|21/04/13 17:30|22:15:12|95%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+------------+
Total jobs 1
upg>
After this the DBUPGRADE stage finishes and comes the POSTCHECKS, POSTFIXUPS and POSTUPGRADE stages and finally the job is completed.
upg>
upg> Job 101 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished successfully [1]
Jobs failed [0]
Jobs pending [0]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 101 FOR ORCL
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCL13 READ WRITE NO
4 PDB12 READ WRITE NO
SQL>
IMPORTANT TIP: In case the autoupgrade prompt is ever disconnected you just have to re-run the autoupgrade.jar again in DEPLOY mode and it will pickup from where it left and open the autoupgrade prompt for you.
Hope this blog will help you upgrade your DB using autoupgrade, feel free to comment below with any errors that you have faced when using autoupgrade or in case you want to share your views on this awesome autoupgrade utility.
Oracle Database Internal Mechanism 