Monday morning – it’s time to write a new blog post. And actually I will set priorities based on what you reported to me in the past week. This way it may prevent others from hitting the same pitfall. The AutoUpgrade may fail when patch ID column is NULL in REGISTRY$HISTORY.
Photo by Etienne Boulanger on Unsplash
What is happening?
You start a database upgrade with autoupgrade. But the initial check will fail with a message like this:
AutoUpgrade tool launched with default options There was an error initializing the patching information for entry upg2
The AutoUpgrade won’t kick off the database upgrade.
Why is it happening?
Actually you don’t hit an “upgrade” issue. Of course, it happens when you try to upgrade a database. But the root cause is a NULL value in the ID column in REGISTRY$HISTORY (visible also via DBA_REGISTRY_HISTORY).
Let me show you a quick example.
This is from my 11.2.0.4 database in the lab:
You see that the ID column is populated for each PSU I applied. Hence, there won’t be any issue when I attempt to upgrade this database. But what happens when I set the ID column to NULL for one of the entries?
update registry$history set ID = NULL where ID=160419; commit;
When I invoke my recent version of autoupgrade (Oct 28, 2019), then I receive:
$ java -jar $OH19/rdbms/admin/autoupgrade.jar -config UP19.cfg -mode analyze AutoUpgrade tool launched with default options There was an error initializing the patching information for entry upg2
A quick look into the autoupgrade_err.log does tell me a bit more:
2019-11-25 11:18:29.201 ERROR 4 - UpgradeConfigDBValidator.findPatchInfo
java.lang.ArrayIndexOutOfBoundsException: 4
at oracle.upgrade.autoupgrade.config.UpgradeConfigDBValidator.findContainerPatches(UpgradeConfigDBValidator.java:396)
at oracle.upgrade.autoupgrade.config.UpgradeConfigDBValidator.findPatchInfo(UpgradeConfigDBValidator.java:329)
at oracle.upgrade.autoupgrade.config.links.UpgradeConfigMaker.process(UpgradeConfigMaker.java:605)
at oracle.upgrade.autoupgrade.config.CLILink.nextLink(CLILink.java:53)
at oracle.upgrade.autoupgrade.config.links.InternalSettingsParser.process(InternalSettingsParser.java:124)
at oracle.upgrade.autoupgrade.config.CLILink.nextLink(CLILink.java:53)
at oracle.upgrade.autoupgrade.config.links.UserConfigFileParser.process(UserConfigFileParser.java:180)
at oracle.upgrade.autoupgrade.config.CLILink.nextLink(CLILink.java:53)
at oracle.upgrade.autoupgrade.config.links.ContextFinder.process(ContextFinder.java:133)
at oracle.upgrade.autoupgrade.config.CLILink.nextLink(CLILink.java:53)
at oracle.upgrade.autoupgrade.config.links.LoggerMaker.process(LoggerMaker.java:68)
at oracle.upgrade.autoupgrade.config.CLILink.nextLink(CLILink.java:53)
at oracle.upgrade.autoupgrade.config.links.AutoUpgradeLocker.process(AutoUpgradeLocker.java:73)
The tool does not like the NULL entry in the ID column.
Based on the number of people who reported this issue recently to me I guess, it may not be so uncommon to have a NULL value in the ID column for an older patch.
Simple workaround?
Of course in this case there are multiple workarounds. But the one I would recommend is to update the ID column with the correct value from the COMMENTS field. In my case this would be:
update registry$history set ID = 160419 where ID is NULL and COMMENTS like '%160419%';
But even if you have no useful information in the COMMENTS field, you could simply update the ID column with a value such as “1” – and then the tool will proceed.
Just don’t delete the entire row from REGISTRY$HISTORY please – even though this would work of course as well.
I’m pretty sure that my team mates added a workaround into a future version of the autoupgrade tool already.
Just in addition, I tested if this would fail with a normal database upgrade, too. It doesn’t as this check is not part of the preupgrade.jar checks but is only executed by AutoUpgrade. The same applies to the DBUA as it settles on our preupgrade.jar as well.
Further Information
Start here if you are seeking for more information about AutoUpgrade:
–Mike
Hi Mike,
this worked for me.
many thanks !
Bart
Thanks to everybody who brought this to my attention. Actually my team mates have seen this, too, and fixed it already in an upcoming version of AutoUpgrade. The tool won’t fail then anymore if such a NULL value is in the patch table.
Cheers!
Mike
Hi Mike,
I found another strange behaviour.
If you hav a
SET SERVEROUTPUT ON
in your glogin.sql, the upgrade will fail with a ORA-04023.
If you delete the line, autoupgrade runs completely without complaints.
By the way: same error occurs when doing a create database with this glogin.sql active.
Maybe this is already known, but I did not find anything about it.
Thanks again for your great presentations at DOAG Conference, you realy convinced me to start evaluating Autoupgrade.
If only this kind of quality software would exist for RUs…
Regards
Andreas
— Details —
oracle@oradb-t-ntr002:~> /oracle/product/db/TSTTD04/194/jdk/bin/java -jar /install/sw/oracle/Tools/autoupgrade/19.7/autoupgrade.jar -version
build.hash 9c85160
build.version 20191024
build.date 2019/10/24 12:47:31
build.max_target_version 19
build.type production
oracle@oradb-t-ntr002:/oracle/product/db/TSTTD04/194/sqlplus/admin> /oracle/product/db/TSTTD04/194/jdk/bin/java -jar /install/sw/oracle/Tools/autoupgrade/19.7/autoupgrade.jar -config ~/upgrade_TSTTD04_to_19.cfg -mode deploy
AutoUpgrade tool launched with default options
+——————————–+
| Starting AutoUpgrade execution |
+——————————–+
1 databases will be processed
Type ‘help’ to list console commands
upg> lsj
+—-+——-+———+———+——-+————–+——–+——–+—————+
|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME|END_TIME| UPDATED| MESSAGE|
+—-+——-+———+———+——-+————–+——–+——–+—————+
| 102|TSTTD04|PRECHECKS|PREPARING|RUNNING|19/11/27 09:51| N/A|09:51:00|Loading DB info|
+—-+——-+———+———+——-+————–+——–+——–+—————+
Total jobs 1
upg>
————————————————-
Errors in database [TSTTD04]
Stage [DBUPGRADE]
Operation [STOPPED]
Status [ERROR]
Info [
Error: UPG-1400
UPGRADE FAILED [tsttd04]
Cause: This indicates that the database upgrade failed with errors.
For further details, see the log file located at /oracle/upg_logs/TSTTD04/TSTTD04/102/autoupgrade_20191127_user.log]
————————————————-
Logs: [/oracle/upg_logs/TSTTD04/TSTTD04/102/autoupgrade_20191127_user.log]
————————————————-
-> /oracle/upg_logs/TSTTD04/TSTTD04/102/autoupgrade_20191127_user.log
…
CAUSE: ERROR at Line 700 in [/oracle/upg_logs/TSTTD04/TSTTD04/102/dbupgrade/catupgrd20191127095059tsttd040.log]
REASON: CATCTL FATAL ERROR
ACTION: [MANUAL]
DETAILS:
…
-> /oracle/upg_logs/TSTTD04/TSTTD04/102/dbupgrade/catupgrd20191127095059tsttd040.log
…
2019-11-27 09:52:31 DEBUG> /oracle/product/db/TSTTD04/194/rdbms/admin/catcon.pm:1315 catcon::print_exec_DB_script_output – ORA-04023: Object SYS.STANDARD could not be validated or authorized
…
oracle@oradb-t-ntr002:/oracle/product/db/TSTTD04/194/sqlplus/admin> cat glogin.sql
SET SERVEROUTPUT ON
Hi Andreas,
thanks for the feedback – and let me run this through my team mates.
Cheers,
Mike
Andreas,
can you check please if this happens with a normal upgrade, too? We suspect that this may happen as well without looking at the logs yet.
I think there was a change. I remember our PL/SQL PM heavily complaining in 12.2.0.1 as everything he defined in his glogin.sql did not work as expected anymore.
Thanks,
Mike
Hi Mike,
I am sorry, but I don’t have a testcase for conventional upgrade path ready to run.
As I didn’t do the upgrade this way for several years I can’t create and run one with the limited amount of time I have at my disposal for this subject 🙁
My research during building my automated database deployment was, that this ORA-04023 happens, if one internal package on which serveroutput on depends is not initialized/installed. This happens clearly during the create database, as there is a call to sqlplus before any package exists.
Regards
Andreas
See your point, Andreas.
THanks,
Mike
Hi Andreas,
I discussed this with the team. The issue is actually not an upgrade issue but something buried in the RDBMS. There’s not much we can change here.
At this stage, you can’t issue that command because the dictionary is not prepared to handle it.
That is a know error with upgrade but also with creation of a database. And there’s not much we can do.
The only way around this would be if the Server would ignore glogin.sql.
Cheers,
Mike
Hi Andreas,
quick feedback. My team mates have discovered the issue. And fixed it already. A future version of the autopgrade won’t stop upgrade anymore at this stage. We know that SQL*Plus has this problem but autopgrade shouldn’t.
For your reference:
When you see AUPG-1333 in the bugs.txt at the end of the AutoUpgrade note, then the fix is included.
Thanks again – and sorry for pushing back at first. It took a bit of extra examination to detect what has happened here.
Cheers,
Mike
Hi Mike,
that would be great.
To me it looked like catcon was running catupgrd by help of sqlplus.
I have eliminated “set serveroutput on” from my glogin.sql, but I will have a look when testing the next autoupgrade version.
Keep on going the userfriendly way – thats the next big thing in Oracle-IT after autonomous comesuser 🙂
Andreas
… after autonomous comes userfriendly 🙂
Hi Andreas,
actually the problem isn’t in autoupgrade but in catcon.pm, the underlying library which enables actions to be run in all containers. The upgrade is utilizing this Multitenant tool.
Thanks,
Mike
Hi Mike
I’m using autoupgrade 21.1.3.
This comes up whith this error:
Fix the specified validations before running AutoUpgrade in DEPLOY mode.
Before running AutoUpgrade in DEPLOY mode, all job based validations must succeed.
The following job based validations will fail if AutoUpgrade is run in DEPLOY mode.
*Validating glogin.sql file content Errors while connecting to DB, MIG2DB
How to handle this?
If I remove glogin.sql the upgrade crashes with missing login.sql.
Should I set ORACLE_PATH to the location of login.sql?`
Regards
Christian
Hi Christian,
did you open an SR for the issues? Please share the SR number with me as we’d like to see the logs.
I assume you have downloaded and uses the most recent version of AutoUpgrade beforehand?
Cheers,
Mike
Hi Mike
Thanks.
Yes, of course newest version of autoupgrade
SR is SR 3-2641336904.
Fact is, the error message of precheck is little bit confusing (in my eyes)
We have to handle a server change.
As You noted in Your slides I set target directory to an temp dir.
(There is no Oracle 19 installed on source server)
Therefore there is a missing glogin which causes the error in precheck.
If I install oracle 19 on source, set target to a valid 19 home the message disapears.
However, on target system autoupgrade still crashes complainig of missing login.sql (See DocId 2593418.1)
I set SQLPATH, copied login.sql in this direction… no effect
Just an other info:
I tried the same one on Linux – and it run without error.
I remember that there was a bug, wich caused the SQLPATH be ignored.
SQLPATH is the variable on Windows System, right?
Thx Christian
Hello Mike,
First of all Happy New Year!
Based on your post “AutoUpgrade may fail when patch ID column is NULL” I found a solution for the problem described below. Thank you.
I am testing Database 11.2.0.4 upgrade to 19c and fall back scenarios and I face today one issue.
I already solved it myself, but I woild like to know if this is a known issue or I did something wrong.
Case description:
Operating System IBM AIX (64-bit)
Source Database Version: “Database Patch Set Update : 11.2.0.4.191015 (29913194)”
Applied Patches in addition: 28125601, 20898997, 20348910
Target Database Version: Database Release Update : 19.5.0.0.191015 (30125133)
Applied Patches in addition: Patch 29202461, Patch description: “ORA-00904 CON_DBID INVALID IDENTIFIER EXECUTING AWRUPD12.SQL”
Scenario:
1. Upgrade 11g database to 19c using AutoUpgrade tool, build.version 20191125 – Status SUCCESS.
2. Fall back approach: Downgrade the upgraded database back to 11g using catdwgrd.sql (dbdowngrade didn’t work for me), MOS Doc ID 2548962.1 – Status SUCCESS, except some invalid objects.
3. Upgrade back to 19c using AutoUpgrade tool.
– The AutoUpgrade analyze ended with the error: “There was an error initializing the patching information for entry upg1”
– The error in autoupgrade_err.log was:
2020-01-06 10:49:47.451 ERROR Errors executing [SELECT NVL(TO_CHAR(action_time, ‘DD-MON-YYYY HH24:MI:SS’),’0′) ||’#’||
NVL(action,’0′)||’#’||
NVL(bundle_series,’0′)||’#’||
NVL(comments,’0′)||’#’||
NVL(id,0)
FROM sys.dba_registry_history h1
WHERE action = ‘APPLY’ AND
comments like ‘%’ || ‘11.2.0.4’ || ‘%’ AND
not exists(SELECT 1 FROM SYS.DBA_REGISTRY_HISTORY h2
WHERE h1.id = h2.id AND
h1.action_time < h2.action_time AND
h2.action = 'ROLLBACK') order by action_time, comments;
NVL(bundle_series,'0')||'#'||
*
ERROR at line 3:
ORA-00904: "BUNDLE_SERIES": invalid identifier
And in fact the colimn BUNDLE_SERIES didn't exist in the SYS.DBA_REGISTRY_HISTORY view.
4. Recreating the SYS.DBA_REGISTRY_HISTORY to include BUNDLE_SERIES column.
5. Upgrade back to 19c using AutoUpgrade tool – Status SUCCESS.
Thank you in advance for your feedback
Best Regards
Tzonka Dimova
Hi Tzonka,
this seems to be an issue with the downgrade script not reverting the changes correctly.
Do you have the logs still somewhere? I wonder why you have seen invalid objects after the downgrade. Once you recompile there shouldn’t be any.
I repeat the same exercise you did right now in my env with the same patch levels (oct 2019 for both homes plus the AWR patch you’ve added to 19.5).
Cheers,
Mike
Hi Tzonka,
there are actually two issues happening. One is that autoupgrade does do something wrong (we fixed this is a newer drop).
The other thing is that the downgrade seems to revert something which needs to be corrected by another catbundle.sql invocation.
What I did:
1. Uprgaded my 11.2.0.4 database to 19.5.0 with autoupgrade
2. Downgraded my 19.5.0 database back down to 11.2.0.4 (Oct 19)
3. Then run catbundle.sql again BEFORE invoking another upgrade
==> otherwise the error you see happens
4. Invoke the autoupgrade again to 19.5.0
This way, no errors happen.
Cheers,
Mike
Hi Mike,
Thank you for the responce.
In my test I didn’t execute catbundle.sql after the downgrade. I executed catdwgrd.sql and catrelod.sql only dnd utrp.sql for compiling of the invalid objects.
I am going to download the newest AutoUpgrade version re-test the same scenario.
Cheers
Tzonka
I exe
Hi Tzonka,
thanks – I’m not 100% sure if the newest version of AutoUpgrade has the fix my mates did already.
But once you execute catbundle (or datapatch before downgrade to roll out 19.5.0 changes) you shouldn’t hit it.
The problem is that there is a column getting added – and the datapatch folks haven’t followed the principles and didn’t create a mechanism which rolls this back during downgrade. Catbundle.sql cleans it up.
Mike
Hi Mike,
I am getting the same error message as Andreas Huber:
INFO Errors executing [SELECT nvl(dbms_registry.is_loaded(‘CONTEXT’), 0) from sys.dual;
SELECT nvl(dbms_registry.is_loaded(‘CONTEXT’), 0) from sys.dual
*
ERROR at line 1:
ORA-04023: Object SYS.DBMS_REGISTRY could not be validated or authorized
INFO Errors executing [SELECT UPPER(DBMS_STATS.GET_PREFS(‘CONCURRENT’)) FROM sys.dual;
SELECT UPPER(DBMS_STATS.GET_PREFS(‘CONCURRENT’)) FROM sys.dual
*
ERROR at line 1:
ORA-04023: Object SYS.DBMS_STATS could not be validated or authorized
But there is no SET SERVEROUTPUT ON in glogin.sql, and as you can see, these are listed as INFO messages, and could be ignored, correct?
The upgrade itself fails with the error message:
ERROR drain failed: oracle.upgrade.autoupgrade.utils.errors.AutoUpgException: AutoUpgException [UPG-1708]
I couldn’t find anything on MOS or Google. 🙁
Do you know maybe, whyt could be the cause of this issue? It seems, I have to create a Service Request for it…
SE2 on Windows 2016.
In the meantime, restore is ongoing, because there is no GRP in the SE2. 🙁
Hi Dejan,
sorry for the late reply – do you still struggle with this?
If yes, we will need the logs please.
You can use the -zip option to put all logs together:
https://mikedietrichde.com/2020/04/08/troubleshooting-restoring-and-restarting-autoupgrade/
java -jar $OH19/rdbms/admin/autoupgrade.jar -config DB12.cfg -zip
Thanks, and sorry for the inconvenience!
Mike
Hi Mike
WIth latest autoupgrade.jar I faced similar issue. But your solution unfortunately did not help . As in my case DB is 11.2.0.4 and no entry in registry$history (empty) .
So I upgraded DB with old autoupgrade.jat which by default comes in 19c.
Do you have any solution with latest autoupgrade.jar and no entry in registry$history ? Any workaround?
Hi Prashant,
please open an SR and upload your logs from AU.
I have no other w/as than described in my post or customers described in the comments section of it.
Cheers,
Mike
Hi Mike .
in my case it failed to compile the invalid ojbects but the database is open R/W not and compatible is still in the older version . .shouLd I ignore it and compile objects manually?
Yes, and I’d guess you’ve done this already.
COMPATIBLE never gets raised with upgrade unless we have to (for instance, if you’d have COMPATIBLE=10.2.0 in an 11g database and upgrade it to 19c now).
Cheers,
Mike
Just an other info:
I tried the same one on Linux – and it run without error.
I remember that there was a bug, wich caused the SQLPATH be ignored.
SQLPATH is the variable on Windows System, right?
Thx Christian
Hello Mike,
To fix the below issue i have ran catbundle.sql PSU apply and it fixed the issue. it has created the missing column BUNDLE_SERIES in the table SYS.DBA_REGISTRY_HISTORY
ERROR at line 3:
ORA-00904: “BUNDLE_SERIES”: invalid identifier
Hi Mike
I had to open an SR as I run into an error when upgrading a Windows
(See my posting in this Forum)
It would be great if You take a look at the SR, as You can get all logfiles, etc.
How can I deliver the SR-ID?
Thanks
Christian
Hi Christian,
seriously, as much as I’d like to help you here, I have no Win system to reproduce anything 🙁
You please must push the SR forward.
Cheers,
Mike
Hi Mike,
thanks for this feedback.
Running on windows is a little bit crazy, I know.
Well I opend an SR – with success! 😉
And – sorry to say this – it’s an bug that comes up with windows – and only
with windows.
Your support offered two solutions, however.
I’m not sure wether it’s ok to post the SR here in the forum.
If you like to get the SR, just contact me. From my point of view, a hint from “Mr. upgrade” (my colleague gave this title to You) would be helpfull in Your forums.
Cheers
Christian
Hi Christian,
you can easily post the SR here on the blog as only Oracle employees with special privileges will be able to view it.
Thanks,
Mike
Hi Mike,
Thanks
The SR is SR 3-26413369041.
I had to think about the solution offered quite a lot – then I found a solution.
1) Install an Oracle 19 RDBMS and do an Upgrade to 19.10 or later.
2) Install an Oracle 19 RDBMS and install ONLY he Patch 29869909 (in an different Directory!)
3) Copy sqplus.exe and sqlplus.sym from directory 2) to 1) (after backup of course)
4) Then, the autoupgrade to directory 1) runs fine! 😉
Cheers
Christian!
Thanks Christian – and you did exactly what the developer did in the bug as well – creating a SQL Plus with the fix, and copying it back into your current home. Just make sure that it either doesn’t get overwritten the next time you patch.
What I see from the SR:
The engineer didn’t tell you that:
a) there are no one-offs on Windows
b) in order to get this fixed, the engineer needs to request an inclusion into a future Windows Bundle Patch.
Nobody requested an inclusion into a future BP – hence, you should request it via the SR. Otherwise it will take until 21c until this is fixed.
Cheers,
Mike
Hi Mike,
well I found an workaround and I’m happy with it.
We will update the SR however.
Cheers
Christian
Thanks!