Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Thursday, July 19, 2007

10g Startup upgrade shows ORA-00604 ORA-00942 in alert log

I was called to help out in an issue during an Oracle 10g upgrade. When the STARTUP UPGRADE command was given, the following error appeared in the alert log file:

Errors in file $ORACLE_HOME/admin/$TWO_TASK/bdump/$TWO_
TASK_smon_nnnn.trc: (Actual file name changed to generic variables)
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

To get more details about which table was missing we put in this line in the init.ora file:

event="942 trace name ERRORSTACK level 3"
An additional trace file was generated which gave us this detail:

ORA-00942: table or view does not exist
Current SQL statement for this session:
delete from javaobj$ where obj#=:1

Usually all objects with a $ suffix are owned by SYS.

SQL> desc sys.javaobj$
ORA-04043: object javaobj$ does not exist

So the error was coming because javaobj$ did not exist.

We checked in other 9i databases and found that javaobj$ did not exist there too. But a describe on 10g databases found the javaobj$"

SQL> desc sys.javaobj$
Name Null? Type
----------------------------------------- -------- ----------------------------

Further research led me to metalink note 316889.1 Complete checklist for manual upgrades to 10gR2. Step 20 in that note says:

- Set the COMPATIBLE initialization parameter to an appropriate value. If you are upgrading from then set the COMPATIBLE parameter to 9.2.0 until after the upgrade has been completed successfully. If you are upgrading from 9.2.0 or 10.1.0 then leave the COMPATIBLE parameter set to it's current value until the upgrade has been completed successfully. This will avoid any unnecessary ORA-942 errors from being reported in SMON trace files during the upgrade (because the upgrade is looking for 10.2 objects that have not yet been created)

The compatible parameter was set to 10.2.0 in the init.ora file. That's why even though the database was still in 9i, it was looking for objects present in 10g. However since this error could be ignored, I advised the team doing the upgrade to go ahead and complete the upgrade. I also asked them to update their installation document to set the compatible parameter to 10.2.0 only after the upgrade is over, so that they do not witness this issue in further iterations.

No comments: