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

Oracle 19c New Feature AutoUpgrade Utility (Part 3)

  • Posted by Gavin Soorma
  • On June 13, 2019
  • 0 Comments
  • 18c, 19c, 19c new feature, autoupgrade, new features, upgrade

The AutoUpgrade feature automates each step of a typical upgrade process and enables us to perform an upgrade with as little human intervention as possible.

AutoUpgrade is based on a configuration file which contains details of the databases we want to upgrade and we also have an AutoUpgrade job manager which executes the various upgrade related jobs depending on which phase or mode AutoUpgrade is executed with.

The autoupgrade.jar file which is used by autoupgrade utility is part of Oracle 19c software by default located in $ORACLE_HOME/rdbms/admin – note that the file is not present in lower versions. We have to download this from the MOS note (2485457.1) and we can use this utility to automate upgrades from 12c R2 to 18c as well -not limited to just Oracle 19c.

In AutoUpgrade 19c Part 1, we executed Autoupgrade in ANALYZE mode which performed a read-only check of the database and returned a report which highlighted any warnings or potential errors which might occur in the database upgrade as well as provided some recommendations.

In AutoUpgrade 19c Part 2 we executed Autoupgrade in FIXUP mode where it not only performs the checks that it performs while in Analyze mode, but after completing these checks, AutoUpgrade then performs all automated fixup tasks that are required to fix the earlier release source database before before the upgrade is commenced.

The AutoUpgrade DEPLOY processing mode performs the actual upgrade of the database as well as performing all the steps performed in the Analyze and Fixup phases discussed in Part 1 and Part 2. Basically in Deploy mode, AutoUpgrade runs all upgrade tasks on the database from pre-upgrade source database analysis to post-upgrade checks.

While we had earlier executed the Analyze and Fixup phases for two databases, DB1 and DB2, we are only running the upgrade for one of the databases (DB1) – so we need to amend the /tmp/config.txt file accordingly.
 
Execute AutoUpgrade in DEPLOY mode
 

[oracle@host02 prechecks]$ /u01/app/oracle/product/19.3.0/dbhome_1/jdk/bin/java -jar /home/oracle/autoupgrade.jar -config /tmp/config.txt -mode deploy
Autoupgrade tool launched with default options
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be processed
Type 'help' to list console commands

 

Executing Prechecks
 

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|        MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
| 100|    DB1|PRECHECKS|PREPARING|RUNNING|19/06/12 12:10|     N/A|12:10:04|Loading DB info|
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
Total jobs 1

 
Running Fixup jobs
 

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|        MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
| 100|    DB1|PREFIXUPS|EXECUTING|RUNNING|19/06/12 12:10|     N/A|12:10:41|Loading DB info|
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
Total jobs 1

 
Drain phase – copy Wallet (if exists) and shut down database
 

upg> lsj
+----+-------+-----+---------+-------+--------------+--------+--------+-------+
|Job#|DB_NAME|STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|MESSAGE|
+----+-------+-----+---------+-------+--------------+--------+--------+-------+
| 100|    DB1|DRAIN|EXECUTING|RUNNING|19/06/12 12:10|     N/A|12:10:57|       |
+----+-------+-----+---------+-------+--------------+--------+--------+-------+
Total jobs 1

 
Start the database upgrade
 

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+-------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+-------+
| 100|    DB1|DBUPGRADE|EXECUTING|RUNNING|19/06/12 12:10|     N/A|12:11:17|Running|
+----+-------+---------+---------+-------+--------------+--------+--------+-------+
Total jobs 1

 
Check the status of the job
 

upg> status
---------------- Config -------------------
User configuration file    [/tmp/config.txt]
General logs location      [/u02/app/oracle/autoupgrade/new        # Top level logging directory (Required)/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 drop 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]
------------ Resources ----------------
Threads in use                        [20]
JVM used memory                       [32] MB
CPU in use                            [13%]
Processes in use                      [18]

upg> tasks
+---+------------------+-------------+
| ID|              NAME|         Job#|
+---+------------------+-------------+
|  1|              main|      WAITING|
| 20|          jobs_mon|      WAITING|
| 21|           console|     RUNNABLE|
| 22|      queue_reader|      WAITING|
| 23|             cmd-0|      WAITING|
| 29|     job_manager-0|      WAITING|
| 31|        event_loop|TIMED_WAITING|
| 32|        bqueue-100|      WAITING|
|344|         exec_loop|      WAITING|
|350|       monitor_db1|TIMED_WAITING|
|351|        catctl_db1|      WAITING|
|352| abort_monitor_db1|TIMED_WAITING|
|353|        async_read|     RUNNABLE|
+---+------------------+-------------+

 
Check the status of the database upgrade – note %Upgraded value is changing
 

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+-----------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|    MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+-----------+
| 100|    DB1|DBUPGRADE|EXECUTING|RUNNING|19/06/12 12:10|     N/A|12:11:44|8%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+--------+-----------+
Total jobs 1

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|     MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
| 100|    DB1|DBUPGRADE|EXECUTING|RUNNING|19/06/12 12:10|     N/A|12:19:38|19%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
Total jobs 1

 
Upgrade is now complete – recompiling invalid objects
 

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|     MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
| 100|    DB1|DBUPGRADE|EXECUTING|RUNNING|19/06/12 12:10|     N/A|12:51:24|90%Compiled |
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
Total jobs 1

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|     MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
| 100|    DB1|DBUPGRADE|EXECUTING|RUNNING|19/06/12 12:10|     N/A|12:51:24|90%Compiled |
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
Total jobs 1

 

Running post upgrade Fixup jobs like upgrade Timezone DST and restarting the database
 

upg> lsj
+----+-------+----------+---------+-------+--------------+--------+--------+-------------+
|Job#|DB_NAME|     STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|      MESSAGE|
+----+-------+----------+---------+-------+--------------+--------+--------+-------------+
| 100|    DB1|POSTFIXUPS|EXECUTING|RUNNING|19/06/12 12:10|     N/A|13:00:40|Remaining 1/3|
+----+-------+----------+---------+-------+--------------+--------+--------+-------------+
Total jobs 1

upg> lsj
+----+-------+----------+---------+-------+--------------+--------+--------+---------------+
|Job#|DB_NAME|     STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|        MESSAGE|
+----+-------+----------+---------+-------+--------------+--------+--------+---------------+
| 100|    DB1|POSTFIXUPS|EXECUTING|RUNNING|19/06/12 12:10|     N/A|13:03:01|Loading DB info|
+----+-------+----------+---------+-------+--------------+--------+--------+---------------+
Total jobs 1

upg> lsj
+----+-------+-----------+---------+--------+--------------+--------+--------+-------------+
|Job#|DB_NAME|      STAGE|OPERATION|  STATUS|    START_TIME|END_TIME| UPDATED|      MESSAGE|
+----+-------+-----------+---------+--------+--------------+--------+--------+-------------+
| 100|    DB1|POSTUPGRADE|EXECUTING|FINISHED|19/06/12 12:10|     N/A|13:03:58|RESTARTING_DB|
+----+-------+-----------+---------+--------+--------------+--------+--------+-------------+
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 DB1

[oracle@host02 prechecks]$ 

 
Note oratab file entry has been updated for database db1
 

[oracle@host02 2019_06_12]$ cat /etc/oratab
#
# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
db1:/u01/app/oracle/product/19.3.0/dbhome_1:N
db2:/u02/app/oracle/product/12.2.0/dbhome_1:N
[oracle@host02 2019_06_12]$ 

 
Note the various upgrade directories and log files for each stage
 

[oracle@host02 100]$ ls -l
total 744
-rwx------ 1 oracle oinstall 716018 Jun 12 13:04 autoupgrade_20190612.log
-rwx------ 1 oracle oinstall   8389 Jun 12 13:03 autoupgrade_20190612_user.log
-rwx------ 1 oracle oinstall      0 Jun 12 12:10 autoupgrade_err.log
drwx------ 2 oracle oinstall   4096 Jun 12 12:55 dbupgrade
drwx------ 2 oracle oinstall   4096 Jun 12 12:11 drain
drwx------ 2 oracle oinstall   4096 Jun 12 12:57 postchecks
drwx------ 2 oracle oinstall   4096 Jun 12 13:03 postfixups
drwx------ 2 oracle oinstall   4096 Jun 12 13:03 postupgrade
drwx------ 2 oracle oinstall   4096 Jun 12 12:10 prechecks
drwx------ 2 oracle oinstall   4096 Jun 12 12:10 prefixups
drwx------ 2 oracle oinstall   4096 Jun 12 12:10 preupgrade
[oracle@host02 100]$ 

 

View the Upgrade Summary report
 

[oracle@host02 dbupgrade]$ pwd
/u02/app/oracle/autoupgrade/db1/db1/100/dbupgrade
[oracle@host02 dbupgrade]$ ls -l
total 71464
-rwx------ 1 oracle oinstall    12129 Jun 12 12:55 autoupgrade20190612121003db1.log
-rwx------ 1 oracle oinstall 49061634 Jun 12 12:55 catupgrd20190612121003db10.log
-rwx------ 1 oracle oinstall  8551109 Jun 12 12:45 catupgrd20190612121003db11.log
-rwx------ 1 oracle oinstall  6557311 Jun 12 12:45 catupgrd20190612121003db12.log
-rwx------ 1 oracle oinstall  8833106 Jun 12 12:45 catupgrd20190612121003db13.log
-rwx------ 1 oracle oinstall      532 Jun 12 12:11 catupgrd20190612121003db1_catcon_18422.lst
-rwx------ 1 oracle oinstall        0 Jun 12 12:40 catupgrd20190612121003db1_datapatch_upgrade.err
-rwx------ 1 oracle oinstall     1303 Jun 12 12:43 catupgrd20190612121003db1_datapatch_upgrade.log
-rwx------ 1 oracle oinstall    38515 Jun 12 12:46 catupgrd20190612121003db1_stderr.log
-rwx------ 1 oracle oinstall    31341 Jun 12 12:55 db1_autocompile20190612121003db10.log
-rwx------ 1 oracle oinstall      546 Jun 12 12:47 db1_autocompile20190612121003db1_catcon_25152.lst
-rwx------ 1 oracle oinstall     2070 Jun 12 12:55 db1_autocompile20190612121003db1_stderr.log
-rwx------ 1 oracle oinstall     4187 Jun 12 12:43 during_upgrade_pfile_catctl.ora
-rwx------ 1 oracle oinstall    32574 Jun 12 12:11 phase.log
-rwx------ 1 oracle oinstall     1728 Jun 12 12:55 upg_summary.log
-rwx------ 1 oracle oinstall       46 Jun 12 12:55 upg_summary_report.log
-rwx------ 1 oracle oinstall      423 Jun 12 12:55 upg_summary_report.pl
[oracle@host02 dbupgrade]$ cat upg_summary.log

Oracle Database Release 19 Post-Upgrade Status Tool    06-12-2019 12:45:3
Database Name: DB1

Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS

Oracle Server                          UPGRADED      19.3.0.0.0  00:14:19
JServer JAVA Virtual Machine           UPGRADED      19.3.0.0.0  00:01:17
Oracle XDK                             UPGRADED      19.3.0.0.0  00:01:03
Oracle Database Java Packages          UPGRADED      19.3.0.0.0  00:00:13
OLAP Analytic Workspace                UPGRADED      19.3.0.0.0  00:00:16
Oracle Label Security                  UPGRADED      19.3.0.0.0  00:00:06
Oracle Database Vault                  UPGRADED      19.3.0.0.0  00:00:19
Oracle Text                            UPGRADED      19.3.0.0.0  00:00:35
Oracle Workspace Manager               UPGRADED      19.3.0.0.0  00:00:40
Oracle Real Application Clusters       UPGRADED      19.3.0.0.0  00:00:00
Oracle XML Database                    UPGRADED      19.3.0.0.0  00:01:40
Oracle Multimedia                      UPGRADED      19.3.0.0.0  00:00:45
Spatial                                UPGRADED      19.3.0.0.0  00:06:02
Oracle OLAP API                        UPGRADED      19.3.0.0.0  00:00:13
Datapatch                                                        00:03:50
Final Actions                                                    00:03:57
Post Upgrade                                                     00:00:12

Total Upgrade Time: 00:32:15

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.

Grand Total Upgrade Time:    [0d:0h:44m:25s]
[oracle@host02 dbupgrade]$ 

 

View the post upgrade fixup log file
 

cat postfixups_db1.log 
...
...

temp/sqlsessend.sql][DB1] - old_time_zones_exist$UpgTarget.call 
2019-06-12 13:02:57.333 INFO Executing SQL [@/u02/app/oracle/autoupgrade/new/db1/db1/temp/sqlsessend.sql
] in [DB1, container:DB1] - ExecuteSql.sendSqlCmdToSqlPlus 
2019-06-12 13:02:57.383 INFO End Reading process Output Stream - ReadInputStream.run 
2019-06-12 13:02:57.383 INFO Begin Closing File /u02/app/oracle/autoupgrade/new/db1/db1/temp/DB1/TimeZone/DB1.log - ReadInputStream.run 
2019-06-12 13:02:57.383 INFO End Closing File /u02/app/oracle/autoupgrade/new/db1/db1/temp/DB1/TimeZone/DB1.log - ReadInputStream.run 
2019-06-12 13:02:57.383 INFO Finished - ReadInputStream.run 
2019-06-12 13:02:57.384 INFO Complete [/u02/app/oracle/autoupgrade/new/db1/db1/temp/sqlsessend.sql][DB1] - old_time_zones_exist$UpgTarget.call 
2019-06-12 13:02:57.384 INFO Looking for error in log file after /u02/app/oracle/autoupgrade/new/db1/db1/temp/sqlsessend.sql execution on [DB1] - old_time_zones_exist$UpgTarget.call 
2019-06-12 13:02:57.384 INFO Closing sqlplus with exitValue 0 [DB1] - old_time_zones_exist$UpgTarget.call 
2019-06-12 13:02:57.384 INFO The Timezone upgrade has finished for [DB1] - old_time_zones_exist.NonCDBTimeZoneUpg 
2019-06-12 13:02:57.384 INFO Finished - old_time_zones_exist.NonCDBTimeZoneUpg 
2019-06-12 13:02:57.384 INFO Finished FIXUP [OLD_TIME_ZONES_EXIST][DB1][SUCCESSFUL] - DBUpgradeInspector$FixUpTrigger.executeFixUp 

 

View the post upgrade log file
 

[oracle@host02 postupgrade]$ cat postupgrade.log 
2019-06-12 13:03:58.001 INFO Deserializing /u02/app/oracle/autoupgrade/new        # Top level logging directory (Required)/cfgtoollogs/upgrade/auto/config_files/dbstate_DB1 file from {1} - DBState.deserialize 
2019-06-12 13:03:58.031 INFO 
DataBase Name:db1
Sid Name     :db1
Source Home  :/u02/app/oracle/product/12.2.0/dbhome_1
Target Home  :/u01/app/oracle/product/19.3.0/dbhome_1
 - PostActions. 
2019-06-12 13:03:58.031 INFO Executing PostUpgrade - AutoUpgPostActions.runPostActions 
2019-06-12 13:03:58.039 INFO Starting - PostActions.runPostActions 
2019-06-12 13:03:58.042 INFO Starting - PostActions.upgPostActionsDriver 
2019-06-12 13:03:58.043 INFO Starting - Oratab.updateOraTab 
2019-06-12 13:03:58.043 INFO Begin Updating oratab /etc/oratab - Oratab.updateOraTab 
2019-06-12 13:03:58.045 INFO Updating oratab file /etc/oratab completed with success - Oratab.updateOraTab 
2019-06-12 13:03:58.053 INFO Starting - NetworkFiles.copyNetworkFiles 
2019-06-12 13:03:58.053 INFO Begin Copying network files - NetworkFiles.copyNetworkFiles 
2019-06-12 13:03:58.084 INFO File /u02/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora does not exist - NetworkFiles.copyFile 
2019-06-12 13:03:58.084 INFO Copying/merging file listener.ora ended - NetworkFiles.copyFile 
2019-06-12 13:03:58.085 INFO IFILE /u02/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora not found, skipping... - NetworkFiles.processIFile 
2019-06-12 13:03:58.085 INFO File /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora.tmp does not exist - NetworkFiles.copyFile 
2019-06-12 13:03:58.085 INFO Copying/merging file /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora.tmp ended - NetworkFiles.copyFile 
2019-06-12 13:03:58.085 INFO File /u02/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora does not exist - NetworkFiles.copyFile 
2019-06-12 13:03:58.085 INFO Copying/merging file sqlnet.ora ended - NetworkFiles.copyFile 
2019-06-12 13:03:58.085 INFO End Copying network files - NetworkFiles.copyNetworkFiles 
2019-06-12 13:03:58.085 INFO Finished - NetworkFiles.copyNetworkFiles 
2019-06-12 13:03:58.096 INFO Starting - PasswordFile.copyPasswordFile 
2019-06-12 13:03:58.096 INFO Begin Copying Password File - PasswordFile.copyPasswordFile 
2019-06-12 13:03:58.100 INFO Copying password file from /u02/app/oracle/product/12.2.0/dbhome_1/dbs/orapwdb1 to /u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwdb1 - PasswordFile.copyPasswordFile 
2019-06-12 13:03:58.107 INFO Copying password file completed with success - PasswordFile.copyPasswordFile 
2019-06-12 13:03:58.107 INFO End Copying Password File - PasswordFile.copyPasswordFile 
2019-06-12 13:03:58.107 INFO Finished - PasswordFile.copyPasswordFile 
2019-06-12 13:03:58.110 INFO Resetting DB state - DBState.resetStateOnTarget 
2019-06-12 13:03:58.110 INFO Resetting the CONCURRENT DBMS_STATS preference for DB1 - ConcurrentStat.resetConcurrentValue 
2019-06-12 13:03:58.110 INFO The CONCURRENT DBMS_STATS preference for DB1 was already set to OFF. - ConcurrentStat.resetConcurrentValue 
2019-06-12 13:03:58.112 INFO Return status is SUCCESS - PostActions.writeStatusLog 
2019-06-12 13:03:58.121 INFO Update of oratab [DB1]
	[/etc/oratab] [SUCCESS] [None]

Network Files [DB1]
	[/u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora] [SUCCESS] [None]
	[/u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora] [SUCCESS] [None]
	[/u01/app/oracle/product/19.3.0/dbhome_1/network/admin/sqlnet.ora] [SUCCESS] [None]

Copy of password file [DB1]
	[/u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwdb1] [SUCCESS] [None]

Database State
	Resetting the database's state: [SUCCESS] [None]

 - PostActions.upgPostActionsDriver 
2019-06-12 13:03:58.122 INFO Finished - PostActions.upgPostActionsDriver 
2019-06-12 13:03:58.122 INFO Finished - PostActions.runPostActions 
2019-06-12 13:03:58.122 INFO No postupgrade user action defined - AutoUpgPostActions.runPostActions 
[oracle@host02 postupgrade]$ 

 

Note the Guaranteed Restore Point which was created automatically by Autoupgrade
 


SQL> select GUARANTEE_FLASHBACK_DATABASE,NAME	,TIME,PRESERVED from v$restore_point;

GUA
---
NAME
--------------------------------------------------------------------------------
TIME									    PRE
--------------------------------------------------------------------------- ---
YES
AUTOUPGRADE_221145114461854_DB1
12-JUN-19 12.10.29.000000000 PM 					    YES


 

 

0 Comments

Leave Reply

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