Fix: “Oracle Database Server” component in an invalid status while upgrading to 10.2.0.4

Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.4 to 10.2.0.4
This problem can occur on any platform.
Oracle Server Enterprise Edition – Version: 10.2.0.4 to 10.2.0.4
Symptoms

— Problem Statement:

When upgrading to 10.2.0.4, following errors for package body DBMS_SQLPA are found in upgrade log file (catupgrd.sql spool file or DBUA Oracle_server.log )

” Warning: Package Body created with compilation errors.

SQL> show errors;
Errors for PACKAGE BODY DBMS_SQLPA:

LINE/COL ERROR
——– —————————————————————–
113/5 PL/SQL: SQL Statement ignored
118/44 PL/SQL: ORA-00904: “OTHER_XML”: invalid identifier ”

This error leaves the “Oracle Database Server” component in an invalid status.

Component Status Version HH:MM:SS
Oracle Database Server INVALID 10.2.0.4.0 00:12:35
Cause
Bug 6893073 – DBMS_SQLPA IS INVALID ORA-904: “OTHER_XML”: INVALID IDENTIFIER

The pre 10G utlxplan creates a table called PLAN_TABLE.
The 10.2.0.4 catplan drops plan_table$ and then creates a public SYNONYM PLAN_TABLE for PLAN_TABLE$. The table takes precedence over the public synonym.

Customers upgrading to 10.2.0.2, 10.2.0.3, or 11.1.0.6 won’t encounter this bug during upgrade because for those releases SQL Performance Analyzer exists in the format of a one-off, which is not run as part of upgrade.
Customers upgrading to 10.2.0.4 or 11.1.0.7 will encounter the bug only and only if they are upgrading a pre-10g (e.g., 9i or 8) db where admin/utlxplan.sql was explicitly run by/as SYS to create a (user) plan table in the SYS schema. I believe this is very rare as DBA won’t use the SYS schema to do explain plan of user/application queries.

The dbms_sqlpa is a package owned by SYS that has a static SQL statement that queries the plan table which is expected to contain the other_xml column.
This is why we get an error if SYS.plan_table does not have this column. The other_xml column was introduced in 10.2.0.1 and added to the temporary global plan table plan_table$. plan_table$ was introduced in 10.1 as a replacement to table plan_table which is used to store the result of explain plan
command. plan_table has become a public synonym to plan_table$.
Solution
— To implement the solution, please execute the following steps:

1. There is an BUG (6893073) logged for the issue and only have a patch to fix this issue on Sun Solaris SPARC (64-bit). So on Sun Solaris SPARC (64-bit) platforms please apply patch 6893073 to fix this issue.

2. For the other platforms in the mean time can you please follow below Workaround:

SQL>sqlplus / as sysdba
SQL>drop table plan_table;
SQL>@?/rdbms/admin/utlxplan
SQL>@?/rdbms/admin/prvtspao.plb
SQL>@?/rdbms/admin/utlrp.sql

Query the dba_registry to confirm that the “Oracle Database Server” component is now in a VALID status:

SQL> select substr(comp_id,1,15) comp_id, substr(comp_name,1,30) comp_name, substr(version,1,10) version, status from dba_registry order by modified;

Note:
It is possible that when you tried to apply the above Workaround you aren’t able to drop plan_table:

SQL> drop table plan_table;
drop table plan_table
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01008: not all variables bound
ORA-06512: at line 21

You obtain this issue when cursor_sharing is set to force. So setting cursor_sharing to exact you were able to drop the table plan_table and apply the Workaround.

Advertisements

2 Responses

  1. Wonderful post however I was wanting to know if you could write a litte more on this topic?
    I’d be very thankful if you could elaborate a little bit more. Cheers!

  2. Very good article! We will be linking to this particularly
    great post on our website. Keep up the good writing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: