News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Oracle 19c AutoUpgrade Utility (Part 1)

  • Posted by Gavin Soorma
  • On June 12, 2019
  • 1 Comments
  • 18c, 18c new feature, 19c, autoupgrade, upgrade

AutoUpgrade utility is a new feature in Oracle 19c and is designed to automate the upgrade process – this includes not just the database upgrade but also automating both the pre-upgrade as well as post-upgrade steps.

Consider a case where as a DBA you have not one but hundreds of database which need to be upgraded and until now the only option we had was to upgrade each of these databases either manually or via the DBUA utility.

All we need to do in case of the new 19c autoupgrade utility is to create a configuration file which contains the details of the databases which need to be upgraded and then use the java based autoupgrade.jar file.

The autoupgrade.jar file is available in the Oracle 19c database software in the $ORACLE_HOME/rdbms/admin directory. The recommendation however is to use the autoupgrade.jar file which can be downloaded from the MOS note 2485457.1.

The utility requires Java 8 and we can use the Java 8 available in the Oracle 19c database software home.

[oracle@host02 admin]$ pwd
/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin

[oracle@host02 admin]$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1

[oracle@host02 admin]$ $ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -version
build.version 20190207
build.date 2019/02/07 12:35:56
build.label RDBMS_PT.AUTOUPGRADE_LINUX.X64_190205.1800

 

Note the difference in the version of the autoupgrade.jar file which is downloaded from MOS.

[oracle@host02 sf_software]$ cp autoupgrade.jar /home/oracle

[oracle@host02 sf_software]$ cd /home/oracle

[oracle@host02 ~]$ $ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -version
build.version 20190513
build.date 2019/05/13 16:59:48

 

The autoupgrade utility can be run in a number of different modes.

Analyze

Performs a read-only pre-upgrade analysis of databases before upgrade and identifies any issues which might prevent a successful upgrade. We can run AutoUpgrade in Analyze mode on the source Oracle Database home during normal database operation.

The Analyze mode produces a report which identifies upgrade issues and possible errors that would occur if we do not correct them, either by running an automatic fixup script, or by manual corrective action.

Fixup

In Fixup mode, AutoUpgrade performs the same checks that it also performs in Analyze mode but after completing these pre-upgrade checks it then runs automated fixups of the source database in preparation for the database upgrade.

Deploy

In the Deploy mode, the autoupgrade utility performs the actual upgrade of the database as well as also performs a number of post-upgrade steps like recompile invalid objects and timezone DST upgrade among other things.

This is an example of a configuration file I will use to upgrade two 12.2 databases to Oracle 19c.

[oracle@host01 admin]$ cat /tmp/config.txt 
#
# Global logging directory pertains to all jobs
#
global.autoupg_log_dir=/u02/app/oracle/autoupgrade        # Top level logging directory (Required)

#
# Database 1
#
upg1.dbname=db1                                
upg1.source_home=/u02/app/oracle/product/12.2.0/dbhome_1 
upg1.target_home=/u01/app/oracle/product/19.3.0/dbhome_1 
upg1.sid=db1                                  
upg1.start_time=09/06/2019 17:30:00                     
upg1.log_dir=/u02/app/oracle/autoupgrade/db1
upg1.upgrade_node=localhost                   
upg1.run_utlrp=yes  
upg1.timezone_upg=yes 
upg1.target_version=12.2

#
# Database 2
#


upg2.dbname=db2                            # Database Name (Required)
upg2.source_home=/u02/app/oracle/product/12.2.0/dbhome_1 # Source Home (Required)
upg2.target_home=/u01/app/oracle/product/19.3.0/dbhome_1 # Target home (Required)
upg2.sid=db2                                 # Oracle Sid (Required)
upg2.start_time=09/06/2019 19:30:00                            # Start time of the operation (Required)
upg2.log_dir=/u02/app/oracle/autoupgrade/db2            # Local logging directory (Required)
upg2.upgrade_node=localhost                    # Upgrade node that operation will run on (Required)
upg2.run_utlrp=yes  # yes(default) to run utlrp as part of upgrade, no to skip it (Optional)
upg2.timezone_upg=yes # yes(default) to upgrade timezone if needed, no to skip it (Optional)
upg2.target_version=12.2                      # Oracle Home Target version number (Required)

Execute autoupgrade in ANALYZE mode

The Autoupgrade Console enables us to monitor as well as manage and control the jobs started by the autoupgrade utility.

For example we use the ‘lsj’ command while in the console prompt which will list the running upgrade jobs along with the progress and status.

 

[oracle@host02 autoupgrade]$ $ORACLE_HOME/jdk/bin/java -jar /home/oracle/autoupgrade.jar -config /tmp/config.txt -mode analyze 
Autoupgrade tool launched with default options
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
2 databases will be analyzed
Type 'help' to list console commands
upg> lsj
+----+-------+---------+---------+--------+--------------+--------+--------+---------------+
|Job#|DB_NAME|    STAGE|OPERATION|  STATUS|    START_TIME|END_TIME| UPDATED|        MESSAGE|
+----+-------+---------+---------+--------+--------------+--------+--------+---------------+
| 104|    DB1|PRECHECKS|PREPARING| RUNNING|19/06/09 16:57|     N/A|16:57:45|Remaining 49/71|
| 105|    DB2|    SETUP|PREPARING|FINISHED|19/06/09 16:57|     N/A|16:57:34|      Scheduled|
+----+-------+---------+---------+--------+--------------+--------+--------+---------------+
Total jobs 2

upg> lsj
+----+-------+---------+---------+--------+--------------+--------+--------+---------------+
|Job#|DB_NAME|    STAGE|OPERATION|  STATUS|    START_TIME|END_TIME| UPDATED|        MESSAGE|
+----+-------+---------+---------+--------+--------------+--------+--------+---------------+
| 104|    DB1|PRECHECKS|PREPARING| RUNNING|19/06/09 16:57|     N/A|16:57:51|Remaining 15/71|
| 105|    DB2|    SETUP|PREPARING|FINISHED|19/06/09 16:57|     N/A|16:57:34|      Scheduled|
+----+-------+---------+---------+--------+--------------+--------+--------+---------------+
Total jobs 2

upg> tasks
+---+--------------+-------------+
| ID|          NAME|         Job#|
+---+--------------+-------------+
|  1|          main|      WAITING|
| 30|      jobs_mon|      WAITING|
| 31|       console|     RUNNABLE|
| 32|  queue_reader|      WAITING|
| 33|         cmd-0|      WAITING|
| 44| job_manager-0|      WAITING|
| 47|    event_loop|TIMED_WAITING|
| 48|    bqueue-104|      WAITING|
| 50|    checks-104|      WAITING|
| 51|rep_checks-104|TIMED_WAITING|
|105|    db1-puic-0|      WAITING|
|106|    db1-puic-1|      WAITING|
|170|      quickSQL|     RUNNABLE|
+---+--------------+-------------+

upg> status 
---------------- Config -------------------
User configuration file    [/tmp/config.txt]
General logs location      [/u02/app/oracle/autoupgrade        # Top level logging directory (Required)/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 drop GRP abort time     [3] minutes
------------------------ Jobs ------------------------
Total databases in configuration file [2]
Total Non-CDB being processed         [2]
Total CDB being processed             [0]
Jobs finished successfully            [0]
Jobs finished/aborted                 [0]
jobs in progress                      [2]
------------ Resources ----------------
Threads in use                        [21]
JVM used memory                       [41] MB
CPU in use                            [13%]
Processes in use                      [14]

upg> Job 104 completed
Job 105 completed
------------------- Final Summary --------------------
Number of databases            [ 2 ]

Jobs finished successfully     [2]
Jobs failed                    [0]
Jobs pending                   [0]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 104 FOR DB1
Job 105 FOR DB2

[oracle@host02 autoupgrade]$ 

 
Note the log files which have been created for each database – as we have only run autoupgrade with the Analyze option, the only directory which is created is prechecks directory.

For each database which has been analyzed, we can review the HTML file which lists the pre-check warnings and recommendations.
 

[oracle@host01 prechecks]$ pwd
/u02/app/oracle/autoupgrade/db1/db1/104/prechecks
[oracle@host01 prechecks]$ ls -l
total 180
-rwx------. 1 oracle oinstall   1967 May 21 00:29 db1_checklist.cfg
-rwx------. 1 oracle oinstall   1616 May 21 00:29 db1_checklist.json
-rwx------. 1 oracle oinstall   1892 May 21 00:29 db1_checklist.xml
-rwx------. 1 oracle oinstall  23354 May 21 00:29 db1_preupgrade.html
-rwx------. 1 oracle oinstall   7619 May 21 00:29 db1_preupgrade.log
-rwx------. 1 oracle oinstall 138146 May 21 00:29 prechecks_db1.log



[oracle@host01 prechecks]$ pwd
/u02/app/oracle/autoupgrade/db2/db2/105/prechecks
[oracle@host01 prechecks]$ ls -lrt 
total 180
-rwx------. 1 oracle oinstall 138147 May 21 00:29 prechecks_db2.log
-rwx------. 1 oracle oinstall   1901 May 21 00:29 db2_checklist.xml
-rwx------. 1 oracle oinstall   1976 May 21 00:29 db2_checklist.cfg
-rwx------. 1 oracle oinstall   7543 May 21 00:29 db2_preupgrade.log
-rwx------. 1 oracle oinstall   1625 May 21 00:29 db2_checklist.json
-rwx------. 1 oracle oinstall  23230 May 21 00:29 db2_preupgrade.html

 
 


 
 
View the db1_preupgrade.html file ….

 

1 Comments

Vijay kumar
  • Jun 13 2019
Really fruitful knowledge

Leave Reply

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