Upgrade to Oracle 19c using Autoupgrade utility

Posted on November 26, 2020 by Oracle 12c Oracle 19c Oracle Database 4
Oracle 19c Autoupgrade

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/

https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/using-autoupgrade-oracle-database-upgrades.html#GUID-71883C8C-7A34-4E93-8955-040CB04F2109

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:

  1. Install Oracle 19.3.0.0 binaries
  2. Prerequisite for Autoupgrade
  3. Create the config file
  4. Analyze the database
  5. Deploy the upgrade
  6. 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_1

Source 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        OPEN

1. 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 production

Java 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-autoupg

Create 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.cfg

Modify 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.cfg

This 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=yes

4. 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 ANALYZE

Output

[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 DEPLOY

Once 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/dbupgrade

Output

[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 needed

Check 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…

Tags:

Share

You may also like...

4 Responses

  1. Joerg Sobottka says:
    December 7, 2020 at 3:20 pm

    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.

    Reply
  2. Jose Monteiro says:
    December 8, 2020 at 6:30 pm

    Very nice and detailed, just include one chapter to patch OH 19.3 to latest RU available before upgrade database.

    Reply
  3. Rakesh Kumar says:
    February 17, 2021 at 8:09 pm

    Awesome.Thanks for Sharing.Keep up the good work.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories

Visitor Count

0424627
Visit Today : 173
Total Visit : 424627
Total Hits : 1021636
Who's Online : 2