How to skip a Fixup in AutoUpgrade

In this blog post I’d like to demonstrate how to skip a fixup in AutoUpgrade. This can be very useful in cases where you know that an issue may happen. Recently we saw several cases with unexpected long runtime for the gathering of fixed objects stats.

How to skip a Fixup in AutoUpgrade

Photo by Warren Wong on Unsplash

Analyze Mode

During the analyze phase, AutoUpgrade creates a <SID>_checklist.cfg file. You will find it in your job’s prechecks subdirectory, e.g.

/home/oracle/logs/DB12/100/prechecks

My SID is DB12, and 100 is the job number.

This is how the db12_checklist.cfg looks in my example:

[SID]          [DB12]
==========================================
[container]          [DB12]
==========================================
[checkname]          DICTIONARY_STATS
[stage]              PRECHECKS
[fixup_available]    YES
[runfix]             YES
[severity]           RECOMMEND
----------------------------------------------------

[checkname]          HIDDEN_PARAMS
[stage]              PRECHECKS
[fixup_available]    NO
[runfix]             N/A
[severity]           RECOMMEND
----------------------------------------------------

[checkname]          POST_DICTIONARY
[stage]              POSTCHECKS
[fixup_available]    YES
[runfix]             YES
[severity]           RECOMMEND
----------------------------------------------------

[checkname]          POST_FIXED_OBJECTS
[stage]              POSTCHECKS
[fixup_available]    YES
[runfix]             YES
[severity]           RECOMMEND
----------------------------------------------------

[checkname]          OLD_TIME_ZONES_EXIST
[stage]              POSTCHECKS
[fixup_available]    YES
[runfix]             YES
[severity]           WARNING
----------------------------------------------------

[checkname]          MANDATORY_UPGRADE_CHANGES
[stage]              PRECHECKS
[fixup_available]    YES
[runfix]             YES
[severity]           INFO
----------------------------------------------------

[checkname]          RMAN_RECOVERY_VERSION
[stage]              PRECHECKS
[fixup_available]    NO
[runfix]             N/A
[severity]           INFO
----------------------------------------------------

[checkname]          TABLESPACES_INFO
[stage]              PRECHECKS
[fixup_available]    NO
[runfix]             N/A
[severity]           INFO
----------------------------------------------------

[checkname]          DIR_SYMLINKS
[stage]              POSTCHECKS
[fixup_available]    NO
[runfix]             N/A
[severity]           WARNING
----------------------------------------------------

[checkname]          ORDIM_INFO_DESUPPORT
[stage]              PRECHECKS
[fixup_available]    NO
[runfix]             N/A
[severity]           INFO
----------------------------------------------------

Different Stages: PRECHECKS and POSTCHECKS

When a check applies to the “before upgrade” phase, it is labeled as PRECHECKS. And vice-versa, if it applies to the “after upgrade” phase, we labeled it with POSTCHECKS. And when you see the tag fixup_available at YES, then you can decide to skip this step.

In my example, I’d like to skip all stats gathering, before and after the upgrade. Hence, I’m editing the db12_checklist.cfg file to (I show only the differences to the one above):

[SID]          [DB12]
==========================================
[container]          [DB12]
==========================================
[checkname]          DICTIONARY_STATS
[stage]              PRECHECKS
[fixup_available]    YES
[runfix]             NO 
[severity]           RECOMMEND
----------------------------------------------------

[checkname]          HIDDEN_PARAMS
[stage]              PRECHECKS
[fixup_available]    NO
[runfix]             N/A
[severity]           RECOMMEND
----------------------------------------------------

[checkname]          POST_DICTIONARY
[stage]              POSTCHECKS
[fixup_available]    YES
[runfix]             NO 
[severity]           RECOMMEND
----------------------------------------------------

[checkname]          POST_FIXED_OBJECTS
[stage]              POSTCHECKS
[fixup_available]    YES
[runfix]             NO 
[severity]           RECOMMEND
----------------------------------------------------

[checkname]          OLD_TIME_ZONES_EXIST
[stage]              POSTCHECKS
[fixup_available]    YES
[runfix]             YES
[severity]           WARNING
----------------------------------------------------

[checkname]          MANDATORY_UPGRADE_CHANGES
[stage]              PRECHECKS
[fixup_available]    YES
[runfix]             YES
[severity]           INFO
----------------------------------------------------

[checkname]          RMAN_RECOVERY_VERSION
[stage]              PRECHECKS
[fixup_available]    NO
[runfix]             N/A
[severity]           INFO
----------------------------------------------------

[checkname]          TABLESPACES_INFO
[stage]              PRECHECKS
[fixup_available]    NO
[runfix]             N/A
[severity]           INFO
----------------------------------------------------

[checkname]          DIR_SYMLINKS
[stage]              POSTCHECKS
[fixup_available]    NO
[runfix]             N/A
[severity]           WARNING
----------------------------------------------------

[checkname]          ORDIM_INFO_DESUPPORT
[stage]              PRECHECKS
[fixup_available]    NO
[runfix]             N/A
[severity]           INFO
----------------------------------------------------

As you see, I changed runfix for all three stats items from YES to NO.

Add checklist parameter

Now the important part is to tell AutoUpgrade to use now my adjusted checklist file. And this is done with the <prefix>.checklist parameter in the config file. If you miss this step, you can do as many updates to the checklist file – but AutoUpgrade will never use it.

I’m adding the checklist parameter to my config file:

global.autoupg_log_dir=/home/oracle/logs
upg1.dbname=DB12
upg1.start_time=NOW
upg1.source_home=/u01/app/oracle/product/12.2.0.1
upg1.target_home=/u01/app/oracle/product/19
upg1.sid=DB12
upg1.log_dir=/home/oracle/logs
upg1.upgrade_node=localhost
upg1.target_version=19
upg1.restoration=no
upg1.checklist=/home/oracle/logs/DB12/100/prechecks/db12_checklist.cfg

Deploy Mode

Finally, I run AutoUpgrade now with this config file in deploy mode:

Never trust a webpage – let us check the logs. I’m interested in the autoupgrade_xxxxx.log below:

-rwx------.  1 oracle dba 556546 Jul 21 17:57 autoupgrade_20200721.log
-rwx------.  1 oracle dba  14191 Jul 21 17:57 autoupgrade_20200721_user.log
-rwx------.  1 oracle dba      0 Jul 21 17:04 autoupgrade_err.log
drwx------.  2 oracle dba   4096 Jul 21 17:52 dbupgrade
drwx------.  2 oracle dba     28 Jul 21 17:05 drain
drwx------.  2 oracle dba   4096 Jul 21 17:54 postchecks
drwx------.  2 oracle dba     60 Jul 21 17:56 postfixups
drwx------.  2 oracle dba     29 Jul 21 17:57 postupgrade
drwx------.  2 oracle dba   4096 Jul 21 17:05 prechecks
drwx------.  2 oracle dba     58 Jul 21 17:05 prefixups
drwx------.  2 oracle dba     28 Jul 21 17:04 preupgrade

At first, I search for the tags DICTIONARY_STATS, POST_DICTIONARY and POST_FIXED_OBJECTS as they are named in the checklist file.

2020-07-21 17:54:26.063 INFO Content of the checklist /home/oracle/logs/DB12/100/prechecks/db12_checklist.cfg is:
[SID]          [DB12]
==========================================
[container]          [DB12]
==========================================
[checkname]          DICTIONARY_STATS
[stage]              PRECHECKS
[fixup_available]    YES
[runfix]             NO
[severity]           RECOMMEND
----------------------------------------------------
...

So the checklist file I edited has been parsed and used.

And further down I find:

2020-07-21 17:54:26.069 INFO POST_DICTIONARY POSTCHECKS NO true RECOMMEND
POST_FIXED_OBJECTS POSTCHECKS NO true RECOMMEND

For skipping the DICTIONARY_STATS I don’t find anything in the prefixups.log in the ../prefixups directory. It hasn’t been run either.

With this query I finally check if the stats in my dictionary are still outdated.

column owner format a6
column table_name format a30

set pages 4000

select count(*),
       owner,
       to_char(last_analyzed,'YYYY-MM-DD') LAST_ANALYZED
  from dba_tab_statistics
  where owner in ('SYS','SYSTEM','XDB')
  GROUP BY owner, to_char(last_analyzed,'YYYY-MM-DD')
  ORDER BY LAST_ANALYZED DESC;

  COUNT(*) OWNER  LAST_ANALY
---------- ------ ----------
       530 SYS
        13 SYSTEM
         5 XDB
        23 SYS    2020-07-21
        37 SYSTEM 2020-07-21
       143 SYS    2020-01-21
         1 SYS    2020-01-11
      1256 SYS    2019-11-18
         2 SYSTEM 2019-11-18
         6 XDB    2019-11-18
       105 SYS    2019-10-18
      1112 SYS    2019-04-18
       130 SYSTEM 2019-04-18
        24 XDB    2019-04-18

And they are.

Background Information

If you’d ask yourself how AutoUpgrade determines whether it needs to gather stats before the upgrade: If it discovers that either schema stats or dictionary stats on SYS or SYSTEM have been gathered in the past 8 days, it deems the stats as OK. Otherwise it will invoke schema stats gathering on SYS and SYSTEM.

So if you’d gather stats by yourself before the upgrade, regardless if you used the EXEC DBMS_STATS.GATHER_DICTIONARY_STATS or our preferred method with EXEC DBMS_STATS.GATHER_SCHEMA_STATS at least on SYS, the refresh of dictionary stats gathering at the beginning of the upgrade will be skipped. And you will save time.

Summary

It is quite simple to skip one or many fixups in AutoUpgrade if you really need to. Of course, this technique does not scale. And we may add one or another parameter in the future.

Interestingly, the upgrade ran slower than with refreshed dictionary stats beforehand. I repeated the run 3x but with schema stats. And the upgrade completed in average 25% faster than with no stats refresh before upgrade.

Further Links and Information

–Mike

Share this: