Oracle Database Autoupgrade Utility is a new feature designed in Oracle 19c to automate the Upgrade process which Identifies issues before upgrade, Performs Preupgrade actions, Deploying the upgrades and Performs Post upgrade actions . You can upgrade multiple databases at the same time using a single configuration file.
Refer the below links to get more information on Oracle 19c Autoupgrade Utility,
https://mikedietrichde.com/2019/04/29/the-new-autoupgrade-utility-in-oracle-19c/
Description
In this article I will demonstrate an overview on upgrading Oracle database from 12.2.0.1 to 19.3.0.0 using Oracle Database Autoupgrade utility.
Below are the High level steps:
- Install Oracle 19.3.0.0 binaries
- Prerequisite for Autoupgrade
- Create the config file
- Analyze the database
- Deploy the upgrade
- Post upgrade task
Environment Details:
Source Hostname: new19c
Database version: 12.2.0.1
Database Name: cdbdev
ORACLE_HOME: /u01/app/oracle/product/12.2.0/db_1/
Target Hostname: new19c
Databaes Version: 19.3.0.0
Database name: cdbdev
ORACLE_HOME: /u01/app/oracle/product/19.3.0/dbhome_1Source DB Details
SQL> select name, open_mode, version, status from v$database, v$instance;
NAME OPEN_MODE VERSION STATUS
--------- -------------------- ----------------- ------------
CDBDEV READ WRITE 12.2.0.1.0 OPEN1. Install Oracle 19.3.0.0 binaries
I have already completed the installation of 19c, Refer here for step by step installation of Oracle 19.3.0.0 on Linux 7 64bit.
2. Prerequisite for Autoupgrade
Download the latest autoupgrade.jar file
Autoupgrade utility autoupgrade.jar file exists by default under $ORACLE_HOME/rdbms/admin directory from Oracle 19.3 release onwards, however Oracle strongly recommends to download the latest AutoUpgrade version before doing the upgrade. Click here to download the latest version.
Replace the autoupgrade.jar with the latest version downloaded
[oracle@new19c ~]$ mv $ORACLE_HOME/rdbms/admin/autoupgrade.jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar-bkp
[oracle@new19c ~]$ cp /tmp/autoupgrade.jar $ORACLE_HOME/rdbms/admin/
[oracle@new19c ~]$
[oracle@new19c ~]$ $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -version build.hash e84c9c2
build.version 19.10.0
build.date 2020/10/23 10:36:46
build.max_target_version 19
build.supported_target_versions 12.2,18,19
build.type productionJava version
Java version should be 8 or later, which is available by default in Oracle Database homes from release 12.1.0.2 and latest.
[oracle@new19c temp]$ $ORACLE_HOME/jdk/bin/java -version
java version "1.8.0_201"
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)
[oracle@new19c temp]$3. Create the config file
Create a directory to hold all upgrade config and log files.
[oracle@new19c ~]$ mkdir /u01/19c-autoupg
[oracle@new19c ~]$ cd /u01/19c-autoupgCreate the sample config file
cd /u01/19c-autoupg
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/jdk/bin
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -create_sample_file config
--output
[oracle@new19c ~]$ cd /u01/19c-autoupg
[oracle@new19c 19c-autoupg]$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@new19c 19c-autoupg]$ export PATH=$PATH:$ORACLE_HOME/jdk/bin
[oracle@new19c 19c-autoupg]$ $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -create_sample_file config
Created sample configuration file /u01/19c-autoupg/sample_config.cfgModify the config file
Copy the sample config file and make the necessary changes as per the database environment.
cd /u01/19c-autoupg
cp sample_config.cfg cdbdev_db_config.cfg
vi cdbdev_db_config.cfgThis is the config file I used for upgrade:
[oracle@new19c 19c-autoupg]$ cat cdbdev_db_config.cfg
global.autoupg_log_dir=/u01/19c-autoupg/upg_logs
#
# Database cdbdev
#
upg1.dbname=cdbdev
upg1.start_time=NOW
upg1.source_home=/u01/app/oracle/product/12.2.0/db_1/
upg1.target_home=/u01/app/oracle/product/19.3.0/dbhome_1
upg1.sid=cdbdev
upg1.log_dir=/u01/19c-autoupg/upg_logs/cdbdev
upg1.upgrade_node=new19c
upg1.target_version=19.3
upg1.run_utlrp=yes
upg1.timezone_upg=yes4. Analyze the database
Autoupgrade Analyze mode checks your database to see if it is ready for the upgrade. This will reads data from the database and does not perform any updates.
Execute autoupgrade in analyze mode with the below syntax,
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/jdk/bin
cd /u01/19c-autoupg
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config cdbdev_db_config.cfg -mode ANALYZEOutput
[oracle@new19c 19c-autoupg]$ $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config cdbdev_db_config.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> lsdg
Unrecognized cmd: lsdg
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
| 100| cdbdev|PRECHECKS|PREPARING|RUNNING|20/11/19 03:27|03:27:28|Loading database information|
+----+-------+---------+---------+-------+--------------+--------+----------------------------+
Total jobs 1
upg> Job 100 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished successfully [1]
Jobs failed [0]
Jobs pending [0]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 100 for cdbdev
[oracle@new19c 19c-autoupg]$We can monitor, manage and control the jobs from the autoupgrade console. Example:
lsj – to list the jobs
status – to show the job status
tasks – shows the tasks executing
All Analyze logs are created under autoupg_log_dir
[oracle@new19c 100]$ cd /u01/19c-autoupg/upg_logs/cdbdev/cdbdev/100/prechecks/
[oracle@new19c prechecks]$ ls -l
total 536
-rwx------. 1 oracle oinstall 5051 Nov 19 03:28 cdbdev_checklist.cfg
-rwx------. 1 oracle oinstall 18050 Nov 19 03:28 cdbdev_checklist.json
-rwx------. 1 oracle oinstall 17101 Nov 19 03:28 cdbdev_checklist.xml
-rwx------. 1 oracle oinstall 36704 Nov 19 03:28 cdbdev_preupgrade.html
-rwx------. 1 oracle oinstall 17649 Nov 19 03:28 cdbdev_preupgrade.log
-rwx------. 1 oracle oinstall 158030 Nov 19 03:28 prechecks_cdb_root.log
-rwx------. 1 oracle oinstall 140241 Nov 19 03:28 prechecks_pdbdev.log
-rwx------. 1 oracle oinstall 139243 Nov 19 03:28 prechecks_pdb_seed.log
[oracle@new19c prechecks]$ We can review the html file (cdbdev_preupgrade.html) which will list all precheck Errors, warnings and recommendations.
5. Deploy the upgrade
Autoupgrade Deploy mode performs the actual upgrade of the database from preugrade source database analysis to post-upgrade checks.
Note: Before deploying the upgrade, you must have a backup plan in place.
Execute the autoupgrade in DEPLOY mode using the below syntax,
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1export PATH=$PATH:$ORACLE_HOME/jdk/bincd /u01/19c-autoupg$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config cdbdev_db_config.cfg -mode DEPLOYOnce the upgrade process is started consider monitoring the logs to see the progress of the upgrade. Autoupgrade logs are available under,
/u01/19c-autoupg/upg_logs/cdbdev/cdbdev/101/dbupgradeOutput
[oracle@new19c 19c-autoupg]$ $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config cdbdev_db_config.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> tasks
+--+-------------+-------------+
|ID| NAME| Job#|
+--+-------------+-------------+
| 1| main| WAITING|
|35| jobs_mon| WAITING|
|36| console| RUNNABLE|
|37| queue_reader| WAITING|
|38| cmd-0| WAITING|
|54|job_manager-0| WAITING|
|56| event_loop|TIMED_WAITING|
|57| bqueue-101| WAITING|
|61| quickSQL| RUNNABLE|
+--+-------------+-------------+
upg>
upg> logs
AutoUpgrade logs folder [/u01/19c-autoupg/upg_logs/cfgtoollogs/upgrade/auto]
logs folder [cdbdev][/u01/19c-autoupg/upg_logs/cdbdev/cdbdev]
upg> lsj
+----+-------+---------+---------+--------+--------------+--------+----------------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+--------+--------------+--------+----------------------------+
| 101| cdbdev|PREFIXUPS|EXECUTING|FINISHED|20/11/19 03:46|03:48:44|Loading database information|
+----+-------+---------+---------+--------+--------------+--------+----------------------------+
Total jobs 1
upg> lsj
+----+-------+-----+---------+-------+--------------+--------+----------------------+
|Job#|DB_NAME|STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+-----+---------+-------+--------------+--------+----------------------+
| 101| cdbdev|DRAIN|EXECUTING|RUNNING|20/11/19 03:46|03:48:52|Shutting down database|
+----+-------+-----+---------+-------+--------------+--------+----------------------+
Total jobs 1
upg> status
---------------- Config -------------------
User configuration file [/u01/19c-autoupg/cdbdev_db_config.cfg]
General logs location [/u01/19c-autoupg/upg_logs/cfgtoollogs/upgrade/auto]
Mode [DEPLOY]
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 [0]
Total CDB being processed [1]
Jobs finished successfully [0]
Jobs finished/aborted [0]
Jobs in progress [1]
Jobs stage summary
Job ID: 101
DB name: cdbdev
SETUP <1 min
GRP <1 min
PREUPGRADE <1 min
PRECHECKS <1 min
PREFIXUPS 1 min
DRAIN <1 min
DBUPGRADE 12 min (IN PROGRESS)
------------ Resources ----------------
Threads in use [32]
JVM used memory [115] MB
CPU in use [13%]
Processes in use [18]
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------------------+
| 101| cdbdev|DBUPGRADE|EXECUTING|RUNNING|20/11/19 03:46|04:38:42|70%Upgraded CDB$ROOT|
+----+-------+---------+---------+-------+--------------+--------+--------------------+
Total jobs 1
upg> /
+----+-------+---------+---------+-------+--------------+--------+--------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------------------+
| 101| cdbdev|DBUPGRADE|EXECUTING|RUNNING|20/11/19 03:46|06:31:20|95%Upgraded PDB$SEED|
+----+-------+---------+---------+-------+--------------+--------+--------------------+
Total jobs 1
upg> /
+----+-------+----------+---------+-------+--------------+--------+-------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+----------+---------+-------+--------------+--------+-------------+
| 101| cdbdev|POSTFIXUPS|EXECUTING|RUNNING|20/11/19 03:46|06:38:37|Remaining 1/9|
+----+-------+----------+---------+-------+--------------+--------+-------------+
Total jobs 1
upg> /
+----+-------+----------+---------+-------+--------------+--------+----------------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+----------+---------+-------+--------------+--------+----------------------------+
| 101| cdbdev|POSTFIXUPS|EXECUTING|RUNNING|20/11/19 03:46|06:43:37|Loading database information|
+----+-------+----------+---------+-------+--------------+--------+----------------------------+
Total jobs 1
upg> /
+----+-------+-----------+---------+-------+--------------+--------+---------------------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+-----------+---------+-------+--------------+--------+---------------------+
| 101| cdbdev|POSTUPGRADE|EXECUTING|RUNNING|20/11/19 03:46|06:45:16|Creating final SPFILE|
+----+-------+-----------+---------+-------+--------------+--------+---------------------+
Total jobs 1
upg> /
+----+-------+-----------+---------+-------+--------------+--------+----------+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|
+----+-------+-----------+---------+-------+--------------+--------+----------+
| 101| cdbdev|POSTUPGRADE|EXECUTING|RUNNING|20/11/19 03:46|06:46:01|Restarting|
+----+-------+-----------+---------+-------+--------------+--------+----------+
Total jobs 1
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 cdbdev
---- Drop GRP at your convenience once you consider it is no longer needed ----
Drop GRP from cdbdev: drop restore point AUTOUPGRADE_9212_CDBDEV122010
[oracle@new19c 19c-autoupg]$Check the upg_summary.log
Oracle Database Release 19 Post-Upgrade Status Tool 11-19-2020 06:30:0
Container Database: CDBDEV
[CON_ID: 2 => PDB$SEED]
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server VALID 19.3.0.0.0 00:34:10
JServer JAVA Virtual Machine VALID 19.3.0.0.0 00:02:25
Oracle XDK VALID 19.3.0.0.0 00:01:19
Oracle Database Java Packages VALID 19.3.0.0.0 00:00:10
OLAP Analytic Workspace VALID 19.3.0.0.0 00:00:55
Oracle Label Security VALID 19.3.0.0.0 00:00:11
Oracle Database Vault VALID 19.3.0.0.0 00:03:00
Oracle Text VALID 19.3.0.0.0 00:00:42
Oracle Workspace Manager VALID 19.3.0.0.0 00:00:52
Oracle Real Application Clusters OPTION OFF 19.3.0.0.0 00:00:00
Oracle XML Database VALID 19.3.0.0.0 00:03:32
Oracle Multimedia VALID 19.3.0.0.0 00:00:46
Spatial VALID 19.3.0.0.0 00:09:15
Oracle OLAP API VALID 19.3.0.0.0 00:00:26
Datapatch 00:04:50
Final Actions 00:05:11
Post Upgrade 00:02:06
Post Compile 00:11:29
Total Upgrade Time: 01:17:50 [CON_ID: 2 => PDB$SEED * ]
Asterisks denotes compilation time has been included during the upgrade process.
Database time zone version is 26. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.
Upgrade Times Sorted In Descending Order
Total Upgrade Time: 01:17:50 [CON_ID: 2 => PDB$SEED * ]
Total Upgrade Time: 01:06:31 [CON_ID: 1 => CDB$ROOT]
Total Upgrade Time: 01:06:12 [CON_ID: 3 => PDBDEV]
Grand Total Upgrade Time: [0d:2h:42m:43s]
[oracle@new19c dbupgrade]$
Timezone file upgrade and database recompilation has already completed by the autoupgrade utility as the below values are adjusted as “yes” in the config file,
upg1.run_utlrp=yes =yes # yes(default) to run utlrp as part of upgrade
upg1.timezone_upg=yes # yes(default) to upgrade timezone if neededCheck the Timezone version
SQL> SELECT VERSION FROM V$TIMEZONE_FILE;
VERSION
----------
32
SQL>Check the db details
SQL> select name, open_mode, version, status from v$database, v$instance;
NAME OPEN_MODE VERSION STATUS
--------- -------------------- ----------------- ------------
CDBDEV READ WRITE 19.0.0.0.0 OPEN
SQL>
6. Post-upgrade task
Once the upgrade is successful and all testing is done, drop the restore point.
Drop the Guaranteed restore point
SQL> select name from v$restore_point;
NAME
------------------------------
AUTOUPGRADE_9212_CDBDEV122010
SQL>
SQL> drop restore point AUTOUPGRADE_9212_CDBDEV122010;
Restore point dropped.
SQL>Change the compatible parameter
Note: After the upgrade, database has to be tested properly before updating the compatible parameter. Once the parameter is updated database cannot be downgraded.
show parameter compatible
alter system set compatible='19.0.0' scope=spfile;
shutdown immediate;
startup;
show parameter compatible
--output
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.2.0
noncdb_compatible boolean FALSE
SQL> alter system set compatible='19.0.0' scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1560278096 bytes
Fixed Size 9135184 bytes
Variable Size 973078528 bytes
Database Buffers 570425344 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 19.0.0
noncdb_compatible boolean FALSE
SQL>
It’s Done. Database is successfully upgraded from 12c to 19c.
Thank You for reading my post.
Hope this helps…
Visit Today : 156
Total Visit : 424610
Total Hits : 1021552
Who's Online : 1
Nice post about a better and better getting tool. One suggestion: Please take the output of the analyze phase very serious and try to fix things manually (especially for the “no fixup available” tasks”.
With MS Windows, everything is a little bit more complicated. If someone wants to know more about Oracle autoupgrade and MS Windows, just contact me. I have spoken at the biggest german speaking user conference about the topic. I also post about problems and solutions regarding autoupgrade at my blog.
Thanks Joerg, good suggestion.
Very nice and detailed, just include one chapter to patch OH 19.3 to latest RU available before upgrade database.
Awesome.Thanks for Sharing.Keep up the good work.