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.
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
- AutoUpgrade: Refresh Status Information
- AutoUpgrade: Where do you find all the logfiles?
- The new AutoUpgrade Utility – Download, documentation and supported versions
–Mike
Hi Mike ,
The Autoupgrade doesnt give back the OS command promt when it has errored/STOPPED or Completed . it simply Hangs
Is this Expected or bug
It does not hang – you need to hit RETURN.
Otherwise please open an SR and upload all the AU logs you collected with autoupgrade.jar -config yourconfig.cfg -zip
Cheers,
Mike
Hi Mike,
that solved our “gather fixed objects” problem.
Even with a purged audit trail, the gathering of fixed objects took ages, at lest in one case.
Thanks to you and the team for the improving autoupgrade even further!
cheers
Daniel
Thanks Daniel 🙂
Always a pleasure to work with you!
Cheers,
Mike
This semms not to work any longer I had problems with the TIMEZONE stuff in postfixups, tried your way and got :
——
The content of the user config file /stage/software/aix/oracle/autoupgrade/ebicsdbt.cfg was altered after a deploy which may lead to corruption or invalid settings,
the AutoUpgrade will stop for safety, make sure to restore the original content of the file prior running the tool again
or if you wish to start from the beginning then remove or change the autoupg_log_dir directory
——
I modified the checklist in place and it did run.
Sorry for the botched -up formatting
Actually skipping time zone is done via the standard parameter:
upg1.timezone_upg=no
Can you share more details please?
Cheers,
Mike
Hello Mike,
for one database, we are stuck at POST_DICTIONARY FIXUP with reason “AUTOUPGRADE_INVALID_OBJECT_FOUND” because of invalid objects. These are known invalids because of missing database link tnsnames.ora entries in test environment and can be ignored.
Can we modify the settings file while autoupgrade is waiting with error on prompt and then just continue the job with “resume”?
Regards,
Martin
Looks like this was not the only issue in POST_DICTIONARY. Second issue was exec dbms_stats.gather_dictionary_stats() is failing when NLS_TERRITORY is germany because of NLS_NUMERIC_CHARACTERS. Turns out that there multiple rows with “.5” and also an orphaned entry in stat prefs table with a “dot” as decimal separator. (“.5”) After changing this from “.5” to “1” for all the rows and deleting the orphaned row, i could successfully proceed with “resume -job 101”. (SR 3-27904610241)
This issue is probably too specific and not interesting for the public community.
Martin,
I will check your SR number and we’ll get back to you directly.
Thanks,
Mike
Hi Martin,
from the SR I see what the issue was and that you’ve solved it already.
Let me know if you need further assistance.
Cheers,
Mike