Resolving ORA-4023 during a 10gR2->11gR1 upgrade

Here's one for a hypothetical frustrated Googler. I just had something goofy happen to me while testing preparations for a database upgrade from 10gR2 to 11gR1. I'd already been through the process a few times on this server, so I wasn't expecting any problems. Since production upgrades usually happen during time windows that guarantee reduced mental capacity, however, I'd decided to perform one last dry run to verify my documentation and to test my "upgrade day" checklist. I cleaned out the database files and related configurations from the previous successful upgrade, and started over from step 0.

Everything was going as expected until I actually ran DBUA. The first step after selecting the database to upgrade, "Gathering database information," was failing with the message:

ORA-04023: Object SYS.STANDARD could not be validated or authorized

At first, I thought I might have fallen prey to the conditions described in My Oracle Support Note 729909.1: Upgrading to 11.1.0 and DBUA reports ORA-4023 On SYS.STANDARD, but the diagnostic steps in the note did not match what I was seeing. Next stop: the DBUA logs in $ORACLE_BASE/cfgtoollogs/dbua/logs. The answer became obvious when I saw the following lines in the trace.log from the failed dbua session (some lines omitted for brevity):

[main] [2:35:45:170] [DatabasesPage.initializePage:351]  Loading databases instance found in OraTab/Registry
[main] [2:35:45:170] [OsUtilsUnix.enumerateSIDs:350]  checking sid: xxxx
[main] [2:35:45:179] [Database.getStepSQLInterface:690]  OH in database: /opt/oracle/app/oracle/product/11.1.0/dbhome_1
[main] [2:35:45:186] [Database.getStepSQLInterface:718]  sqlplus created with home:=/opt/oracle/app/oracle/product/11.1.0/dbhome_1 and sid:=xxxx
[Thread-4] [2:35:49:44] [CompManager.setSelectedDatabase:1339]  setSelectedDatabase::oracleHome=/opt/oracle/app/oracle/product/11.1.0/dbhome_1 sid=xxxx
[Thread-4] [2:35:49:45] [CompManager.setSelectedDatabase:1424]  Old home=/opt/oracle/app/oracle/product/11.1.0/dbhome_1
[Thread-4] [2:35:49:47] [CompManager.setSelectedDatabase:1425]  New home=/opt/oracle/app/oracle/product/11.1.0/dbhome_1

Sure enough, when I checked the same log file from a previous successful upgrade, the corresponding lines in the trace.log file all referred to the old (10.2.0) ORACLE_HOME. Despite all my careful cleanup, I had somehow forgotten to reset the value of the ORACLE_HOME for the database in /etc/oratab:

[oracle@testsrv logs]$ grep product /etc/oratab

To make matters worse, I had also failed to notice that the ORACLE_HOME value was wrong in the DBUA interface. This, I suppose, is why we test. :-P

After setting the ORACLE_HOME in /etc/oratab back to the 10gR2 value, I was able to complete the upgrade successfully.

One Comment

  1. Hope
    Posted 10 April 2011 at 17:11 | Permalink

    this is exactly what i wanted to find out.
    after you installed the software and ready to upgrade. do you need set ORACLE_HOME AND TNS_ADMIN to your old 10g ?
    do you need to create a 11g listener for the 10g database before you start the dbua?

Post a Comment

Your email is never published nor shared. Required fields are marked *