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

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

— Problem Statement:

When upgrading to, 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;

——– —————————————————————–
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 00:12:35

The pre 10G utlxplan creates a table called PLAN_TABLE.
The 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,, or 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 or 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 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$.
— 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;

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;

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.

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: Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: