Pythian Blog: Technical Track

Mining Autoupgrade Results

by Michael Dinh on Dec 3, 2019 12:00:00 AM

We wanted to define a consistent process for upgrading databases using an autoupgrade method for single instance databases: Create directory for autoupgrade/log: $ORACLE_BASE/admin/$ORACLE_UNQNAME/autoupgrade/log, where $ORACLE_UNQNAME=db_unique_name Create upgrade.config as shown: $ORACLE_BASE/admin/$ORACLE_UNQNAME/${ORACLE_UNQNAME}_upgrade.config 

 global.autoupg_log_dir=$ORACLE_BASE/admin/$ORACLE_UNQNAME/autoupgrade/log
 upg1.log_dir=$ORACLE_BASE/admin/$ORACLE_UNQNAME/autoupgrade/log
 upg1.dbname=$ORACLE_UNQNAME
 upg1.sid=$ORACLE_SID
 upg1.start_time=NOW
 upg1.source_home=/oracle/app/product/11.2/dbhome_1
 upg1.target_home=/oracle/app/product/12.2/dbhome_1
 upg1.upgrade_node=localhost
 upg1.target_version=12.2
 upg1.timezone_upg=no
 upg1.restoration=yes
Let's take a took at summary for autograde job process 102. Find autoupgrade directories: 
$ export JOBNO=102
 $ ls -l $ORACLE_BASE/admin/$ORACLE_UNQNAME/autoupgrade/log/*/*/*
 -rwx------ 1 oracle dba 73349 Nov 04 12:46 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/100/autoupgrade_20191104.log
 -rwx------ 1 oracle dba 233 Nov 04 12:46 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/100/autoupgrade_20191104_user.log
 -rwx------ 1 oracle dba 0 Nov 04 12:46 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/100/autoupgrade_err.log
 -rwx------ 1 oracle dba 71390 Nov 04 13:07 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/101/autoupgrade_20191104.log
 -rwx------ 1 oracle dba 233 Nov 04 13:06 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/101/autoupgrade_20191104_user.log
 -rwx------ 1 oracle dba 0 Nov 04 13:06 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/101/autoupgrade_err.log
 -rwx------ 1 oracle dba 891207 Nov 04 16:01 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/autoupgrade_20191104.log
 -rwx------ 1 oracle dba 12371 Nov 04 16:01 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/autoupgrade_20191104_user.log
 -rwx------ 1 oracle dba 245 Nov 04 15:50 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/autoupgrade_err.log
 -rwx------ 1 oracle dba 1118 Nov 04 12:46 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/temp/after_upgrade_pfile_ORACLE_SID.ora
 -rwx------ 1 oracle dba 0 Nov 04 14:05 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/temp/ORACLE_SID.restart
 -rwx------ 1 oracle dba 2236 Nov 04 15:48 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/temp/ORACLE_SID_autocompile.sql
 -rwx------ 1 oracle dba 16805 Nov 04 13:52 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/temp/ORACLE_SID_catnoamd.sql
 -rwx------ 1 oracle dba 3685 Nov 04 13:56 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/temp/ORACLE_SID_catnoexf.sql
 -rwx------ 1 oracle dba 19753 Nov 04 13:56 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/temp/ORACLE_SID_catnorul.sql
 -rwx------ 1 oracle dba 20740 Nov 04 13:54 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/temp/ORACLE_SID_emremove.sql
 -rwx------ 1 oracle dba 883 Nov 04 15:48 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/temp/ORACLE_SID_objcompare.sql
 -rwx------ 1 oracle dba 1118 Nov 04 12:46 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/temp/before_upgrade_pfile_ORACLE_SID.ora
 -rwx------ 1 oracle dba 1168 Nov 04 14:00 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/temp/during_upgrade_pfile_ORACLE_SID.ora
 
 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/100/prechecks:
 total 376
 -rwx------ 1 oracle dba 2781 Nov 04 12:46 ORACLE_SID_checklist.cfg
 -rwx------ 1 oracle dba 9328 Nov 04 12:46 ORACLE_SID_checklist.json
 -rwx------ 1 oracle dba 9962 Nov 04 12:46 ORACLE_SID_checklist.xml
 -rwx------ 1 oracle dba 25980 Nov 04 12:46 ORACLE_SID_preupgrade.html
 -rwx------ 1 oracle dba 10018 Nov 04 12:46 ORACLE_SID_preupgrade.log
 -rwx------ 1 oracle dba 121687 Nov 04 12:46 prechecks_ORACLE_SID.log
 
 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/101/prechecks:
 total 376
 -rwx------ 1 oracle dba 2781 Nov 04 13:07 ORACLE_SID_checklist.cfg
 -rwx------ 1 oracle dba 9328 Nov 04 13:07 ORACLE_SID_checklist.json
 -rwx------ 1 oracle dba 9962 Nov 04 13:07 ORACLE_SID_checklist.xml
 -rwx------ 1 oracle dba 25980 Nov 04 13:07 ORACLE_SID_preupgrade.html
 -rwx------ 1 oracle dba 10018 Nov 04 13:07 ORACLE_SID_preupgrade.log
 -rwx------ 1 oracle dba 121687 Nov 04 13:07 prechecks_ORACLE_SID.log
 
 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/dbupgrade:
 total 446064
 -rwx------ 1 oracle dba 13059 Nov 04 15:48 autoupgrade20191104135121ORACLE_SID.log
 -rwx------ 1 oracle dba 12042 Nov 04 15:48 ORACLE_SID_autocompile20191104135121ORACLE_SID0.log
 -rwx------ 1 oracle dba 551 Nov 04 15:48 ORACLE_SID_autocompile20191104135121ORACLE_SID_catcon_10617264.lst
 -rwx------ 1 oracle dba 208215073 Nov 04 15:48 catupgrd20191104135121ORACLE_SID0.log
 -rwx------ 1 oracle dba 7481470 Nov 04 15:46 catupgrd20191104135121ORACLE_SID1.log
 -rwx------ 1 oracle dba 5527017 Nov 04 15:46 catupgrd20191104135121ORACLE_SID2.log
 -rwx------ 1 oracle dba 7040784 Nov 04 15:46 catupgrd20191104135121ORACLE_SID3.log
 -rwx------ 1 oracle dba 527 Nov 04 14:01 catupgrd20191104135121ORACLE_SID_catcon_17039806.lst
 -rwx------ 1 oracle dba 0 Nov 04 15:23 catupgrd20191104135121ORACLE_SID_datapatch_normal.err
 -rwx------ 1 oracle dba 1050 Nov 04 15:46 catupgrd20191104135121ORACLE_SID_datapatch_normal.log
 -rwx------ 1 oracle dba 0 Nov 04 15:17 catupgrd20191104135121ORACLE_SID_datapatch_upgrade.err
 -rwx------ 1 oracle dba 702 Nov 04 15:18 catupgrd20191104135121ORACLE_SID_datapatch_upgrade.log
 -rwx------ 1 oracle dba 9877 Nov 04 15:18 during_upgrade_pfile_catctl.ora
 -rwx------ 1 oracle dba 32649 Nov 04 14:01 phase.log
 -rwx------ 1 oracle dba 1489 Nov 04 15:48 upg_summary.log
 -rwx------ 1 oracle dba 46 Nov 04 15:48 upg_summary_report.log
 -rwx------ 1 oracle dba 408 Nov 04 15:48 upg_summary_report.pl
 
 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/drain:
 total 16
 -rwx------ 1 oracle dba 4952 Nov 04 14:01 drain_ORACLE_SID.log
 
 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/postchecks:
 total 104
 -rwx------ 1 oracle dba 969 Nov 04 15:50 ORACLE_SID_checklist.cfg
 -rwx------ 1 oracle dba 3202 Nov 04 15:50 ORACLE_SID_checklist.json
 -rwx------ 1 oracle dba 3395 Nov 04 15:50 ORACLE_SID_checklist.xml
 -rwx------ 1 oracle dba 16916 Nov 04 15:50 ORACLE_SID_postupgrade.html
 -rwx------ 1 oracle dba 3383 Nov 04 15:50 ORACLE_SID_postupgrade.log
 -rwx------ 1 oracle dba 14861 Nov 04 15:50 postchecks_ORACLE_SID.log
 
 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/postfixups:
 total 40
 -rwx------ 1 oracle dba 14864 Nov 04 16:00 postchecks_ORACLE_SID.log
 -rwx------ 1 oracle dba 3262 Nov 04 15:59 postfixups_ORACLE_SID.log
 
 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/postupgrade:
 total 24
 -rwx------ 1 oracle dba 10177 Nov 04 16:01 postupgrade.log
 
 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/prechecks:
 total 376
 -rwx------ 1 oracle dba 2781 Nov 04 13:52 ORACLE_SID_checklist.cfg
 -rwx------ 1 oracle dba 9328 Nov 04 13:52 ORACLE_SID_checklist.json
 -rwx------ 1 oracle dba 9962 Nov 04 13:52 ORACLE_SID_checklist.xml
 -rwx------ 1 oracle dba 25980 Nov 04 13:52 ORACLE_SID_preupgrade.html
 -rwx------ 1 oracle dba 10018 Nov 04 13:52 ORACLE_SID_preupgrade.log
 -rwx------ 1 oracle dba 121687 Nov 04 13:51 prechecks_ORACLE_SID.log
 
 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/prefixups:
 total 304
 -rwx------ 1 oracle dba 121708 Nov 04 14:00 prechecks_ORACLE_SID.log
 -rwx------ 1 oracle dba 32727 Nov 04 14:00 prefixups_ORACLE_SID.log
 
 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/102/preupgrade:
 total 8
 -rwx------ 1 oracle dba 98 Nov 04 13:51 preupgrade.log
 
 /orahome/oracle/app/admin/ORACLE_SID/autoupgrade/log/cfgtoollogs/upgrade/auto:
 total 880
 -rwx------ 1 oracle dba 414589 Nov 04 16:01 autoupgrade.log
 -rwx------ 1 oracle dba 780 Nov 04 13:00 autoupgrade_err.log
 -rwx------ 1 oracle dba 3113 Nov 04 16:01 autoupgrade_user.log
 drwx------ 2 oracle dba 4096 Nov 04 14:01 config_files
 drwx------ 2 oracle dba 256 Nov 04 16:01 lock
 -rwx------ 1 oracle dba 12381 Nov 04 16:01 state.html
 drwx------ 2 oracle dba 4096 Nov 04 16:01 status
 $
Find timing for autoupgrade process: 
$ tail -12 `ls $ORACLE_BASE/admin/$ORACLE_UNQNAME/autoupgrade/log/$ORACLE_UNQNAME/$JOBNO/autoupgrade*.log|egrep -v 'user|err'`
 2019-11-04 16:01:21.082 INFO ----------------------Stages Summary------------------------ - DispatcherOSHelper.writeStageSummary
 2019-11-04 16:01:21.082 INFO SETUP <1 min - DispatcherOSHelper.writeStageSummary
 2019-11-04 16:01:21.083 INFO PREUPGRADE <1 min - DispatcherOSHelper.writeStageSummary
 2019-11-04 16:01:21.083 INFO PRECHECKS <1 min - DispatcherOSHelper.writeStageSummary
 2019-11-04 16:01:21.083 INFO GRP <1 min - DispatcherOSHelper.writeStageSummary
 2019-11-04 16:01:21.084 INFO PREFIXUPS 8 min - DispatcherOSHelper.writeStageSummary
 2019-11-04 16:01:21.084 INFO DRAIN <1 min - DispatcherOSHelper.writeStageSummary
 2019-11-04 16:01:21.084 INFO DBUPGRADE 108 min - DispatcherOSHelper.writeStageSummary
 2019-11-04 16:01:21.085 INFO POSTCHECKS <1 min - DispatcherOSHelper.writeStageSummary
 2019-11-04 16:01:21.085 INFO POSTFIXUPS 9 min - DispatcherOSHelper.writeStageSummary
 2019-11-04 16:01:21.086 INFO POSTUPGRADE <1 min - DispatcherOSHelper.writeStageSummary
 2019-11-04 16:01:21.086 INFO End of dispatcher instance for ORACLE_SID - AutoUpgDispatcher.run
Find timing for database upgrade: 
$ tail -35 $ORACLE_BASE/admin/$ORACLE_UNQNAME/autoupgrade/log/$ORACLE_UNQNAME/102/dbupgrade/catupgrd*${ORACLE_UNQNAME}0.log
 ========== PROCESS ENDED ==========
 SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
 Start of Summary Report
 ------------------------------------------------------
 
 Oracle Database 12.2 Post-Upgrade Status Tool 11-04-2019 15:46:14
 
 Component Current Version Elapsed Time
 Name Status Number HH:MM:SS
 
 Oracle Server UPGRADED 12.2.0.1.0 00:19:53
 JServer JAVA Virtual Machine UPGRADED 12.2.0.1.0 00:07:34
 Oracle Workspace Manager UPGRADED 12.2.0.1.0 00:02:16
 OLAP Analytic Workspace UPGRADED 12.2.0.1.0 00:00:23
 Oracle OLAP API UPGRADED 12.2.0.1.0 00:00:23
 Oracle XDK UPGRADED 12.2.0.1.0 00:01:15
 Oracle Text UPGRADED 12.2.0.1.0 00:01:24
 Oracle XML Database UPGRADED 12.2.0.1.0 00:05:28
 Oracle Database Java Packages UPGRADED 12.2.0.1.0 00:00:25
 Oracle Multimedia UPGRADED 12.2.0.1.0 00:03:25
 Spatial UPGRADED 12.2.0.1.0 00:09:03
 Oracle Application Express UPGRADED 5.0.4.00.12 00:23:17
 Final Actions 00:04:44
 Post Upgrade 00:00:09
 
 Total Upgrade Time: 01:20:32
 
 Database time zone version is 14. It is older than current release time
 zone version 26. Time zone upgrade is needed using the DBMS_DST package.
 
 Grand Total Upgrade Time: [0d:1h:47m:34s]
 
 End of Summary Report
 ------------------------------------------------------
 $
Find timing for datapatch: 
$ cat $ORACLE_BASE/admin/$ORACLE_UNQNAME/autoupgrade/log/$ORACLE_UNQNAME/$JOBNO/dbupgrade/catupgrd*datapatch_normal.log
 SQL Patching tool version 12.2.0.1.0 Production on Mon Nov 4 15:23:22 2019
 Copyright (c) 2012, 2019, Oracle. All rights reserved.
 
 Log file for this invocation: /orahome/oracle/app/cfgtoollogs/sqlpatch/sqlpatch_14221746_2019_11_04_15_23_22/sqlpatch_invocation.log
 
 Connecting to database...OK
 Bootstrapping registry and package to current versions...done
 Determining current state...done
 
 Current state of SQL patches:
 Bundle series DBRU:
 ID 190716 in the binary registry and not installed in the SQL registry
 
 Adding patches to installation queue and performing prereq checks...
 Installation queue:
 Nothing to roll back
 The following patches will be applied:
 29757449 (DATABASE JUL 2019 RELEASE UPDATE 12.2.0.1.190716)
 
 Installing patches...
 Patch installation complete. Total patches installed: 1
 
 Validating logfiles...
 Patch 29757449 apply: SUCCESS
 logfile: /orahome/oracle/app/cfgtoollogs/sqlpatch/29757449/23013437/29757449_apply_ORACLE_SID_2019Nov04_15_24_11.log (no errors)
 SQL Patching tool complete on Mon Nov 4 15:46:00 2019
 $
Hopefully, this will be useful as a starting point to determine autoupgrade process timing.
Topics: Google Cloud Platform Google Cloud Big Data Technical Track Cloud Scala Pubsub Twitter

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.