| << Back to previous view |
|
[QA-40] "Oracle Database Server" status is INVALID after applying 10.2.0.4 PatchSet. Created: 15/Jun/08 Updated: 15/Jun/08 |
|
| Status: | Closed |
| Project: | Questions & Answers |
| Fix Version/s: | None |
| Type: | Oracle - Administration | Priority: | Major |
| Reporter: | ubTools Support | Assignee: | ubTools Support |
| Resolution: | Answered | Votes: | 0 |
| Product Version: | Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production |
| Operating System: | IBM-AIX |
| Description |
After applying 10.2.0.4.0 PatchSet into 10.2.0.3.0, catupgrd.sql logs shows the following:
... SQL> CREATE OR REPLACE PACKAGE BODY dbms_sqlpa wrapped 2 a000000 3 1 4 abcd 5 abcd 6 abcd ... 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 SQL> ... Component Status Version HH:MM:SS Oracle Database Server INVALID 10.2.0.4.0 00:09:22 JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:02:43 Oracle XDK VALID 10.2.0.4.0 00:00:29 Oracle Database Java Packages VALID 10.2.0.4.0 00:00:14 Oracle Text VALID 10.2.0.4.0 00:00:21 Oracle XML Database VALID 10.2.0.4.0 00:02:02 Oracle Workspace Manager VALID 10.2.0.4.3 00:00:43 Oracle Data Mining VALID 10.2.0.4.0 00:00:20 OLAP Analytic Workspace VALID 10.2.0.4.0 00:00:16 OLAP Catalog VALID 10.2.0.4.0 00:00:55 Oracle OLAP API VALID 10.2.0.4.0 00:00:43 Oracle interMedia VALID 10.2.0.4.0 00:02:24 Spatial VALID 10.2.0.4.0 00:01:34 Oracle Ultra Search VALID 10.2.0.4.0 00:00:22 Oracle Expression Filter VALID 10.2.0.4.0 00:00:09 Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:36 Oracle Rule Manager VALID 10.2.0.4.0 00:00:08 . |
| Comments |
| Comment by ubTools Support [ 15/Jun/08 06:32 PM ] |
|
Compiling DBMS_SQLPA causes the problem. To find the object including OTHER_XML column, ERRORSTACK trace for ORA-904 would be useful. But, since it's a known column of PLAN_TABLE, it's not required while diagnosing the problem.
There were both SYS.PLAN_TABLE as a table and PUBLIC.PLAN_TABLE as a public synonym in the database: SQL> select owner,object_name,object_type from dba_objects where owner in ('SYS','PUBLIC') and upper(object_name) like 'PLAN_TABLE%';
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
PUBLIC
PLAN_TABLE
SYNONYM
SYS
PLAN_TABLE
TABLE
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
SYS
PLAN_TABLE$
TABLE
SQL> select TABLE_OWNER,TABLE_NAME from dba_synonyms where OWNER='PUBLIC' and SYNONYM_NAME='PLAN_TABLE';
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
SYS PLAN_TABLE$
SQL>
But, not all columns of SYS.PLAN_TABLE table and PUBLIC.PLAN_TABLE synonym are same: SQL> desc sys.plan_table Name Null? Type ----------------------------------------- -------- ---------------------------- STATEMENT_ID VARCHAR2(30) TIMESTAMP DATE REMARKS VARCHAR2(80) OPERATION VARCHAR2(30) OPTIONS VARCHAR2(255) OBJECT_NODE VARCHAR2(128) OBJECT_OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) OBJECT_INSTANCE NUMBER(38) OBJECT_TYPE VARCHAR2(30) OPTIMIZER VARCHAR2(255) SEARCH_COLUMNS NUMBER ID NUMBER(38) PARENT_ID NUMBER(38) POSITION NUMBER(38) COST NUMBER(38) CARDINALITY NUMBER(38) BYTES NUMBER(38) OTHER_TAG VARCHAR2(255) PARTITION_START VARCHAR2(255) PARTITION_STOP VARCHAR2(255) PARTITION_ID NUMBER(38) OTHER LONG SQL> SQL> desc sys.plan_table$ Name Null? Type ----------------------------------------- -------- ---------------------------- STATEMENT_ID VARCHAR2(30) PLAN_ID NUMBER TIMESTAMP DATE REMARKS VARCHAR2(4000) OPERATION VARCHAR2(30) OPTIONS VARCHAR2(255) OBJECT_NODE VARCHAR2(128) OBJECT_OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) OBJECT_ALIAS VARCHAR2(65) OBJECT_INSTANCE NUMBER(38) OBJECT_TYPE VARCHAR2(30) OPTIMIZER VARCHAR2(255) SEARCH_COLUMNS NUMBER ID NUMBER(38) PARENT_ID NUMBER(38) DEPTH NUMBER(38) POSITION NUMBER(38) COST NUMBER(38) CARDINALITY NUMBER(38) BYTES NUMBER(38) OTHER_TAG VARCHAR2(255) PARTITION_START VARCHAR2(255) PARTITION_STOP VARCHAR2(255) PARTITION_ID NUMBER(38) OTHER LONG OTHER_XML CLOB DISTRIBUTION VARCHAR2(30) CPU_COST NUMBER(38) IO_COST NUMBER(38) TEMP_SPACE NUMBER(38) ACCESS_PREDICATES VARCHAR2(4000) FILTER_PREDICATES VARCHAR2(4000) PROJECTION VARCHAR2(4000) TIME NUMBER(38) QBLOCK_NAME VARCHAR2(30) SQL> Since table access takes precedence on synonym access, SYS.PLAN_TABLE table was used. But, this table doesn't have a column named OTHER_XML, which caused the problem. After dropping SYS.PLAN_TABLE table, PUBLIC.PLAN_TABLE synonym used: SQL> drop table sys.plan_table; Table dropped. SQL> SQL> desc plan_table Name Null? Type ----------------------------------------- -------- ---------------------------- STATEMENT_ID VARCHAR2(30) PLAN_ID NUMBER TIMESTAMP DATE REMARKS VARCHAR2(4000) OPERATION VARCHAR2(30) OPTIONS VARCHAR2(255) OBJECT_NODE VARCHAR2(128) OBJECT_OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) OBJECT_ALIAS VARCHAR2(65) OBJECT_INSTANCE NUMBER(38) OBJECT_TYPE VARCHAR2(30) OPTIMIZER VARCHAR2(255) SEARCH_COLUMNS NUMBER ID NUMBER(38) PARENT_ID NUMBER(38) DEPTH NUMBER(38) POSITION NUMBER(38) COST NUMBER(38) CARDINALITY NUMBER(38) BYTES NUMBER(38) OTHER_TAG VARCHAR2(255) PARTITION_START VARCHAR2(255) PARTITION_STOP VARCHAR2(255) PARTITION_ID NUMBER(38) OTHER LONG OTHER_XML CLOB DISTRIBUTION VARCHAR2(30) CPU_COST NUMBER(38) IO_COST NUMBER(38) TEMP_SPACE NUMBER(38) ACCESS_PREDICATES VARCHAR2(4000) FILTER_PREDICATES VARCHAR2(4000) PROJECTION VARCHAR2(4000) TIME NUMBER(38) QBLOCK_NAME VARCHAR2(30) SQL> Applying PatchSet did not give INVALID status: Component Status Version HH:MM:SS Oracle Database Server VALID 10.2.0.4.0 00:09:20 JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:02:56 Oracle XDK VALID 10.2.0.4.0 00:00:28 Oracle Database Java Packages VALID 10.2.0.4.0 00:00:14 Oracle Text VALID 10.2.0.4.0 00:00:22 Oracle XML Database VALID 10.2.0.4.0 00:02:05 Oracle Workspace Manager VALID 10.2.0.4.3 00:00:45 Oracle Data Mining VALID 10.2.0.4.0 00:00:21 OLAP Analytic Workspace VALID 10.2.0.4.0 00:00:16 OLAP Catalog VALID 10.2.0.4.0 00:00:55 Oracle OLAP API VALID 10.2.0.4.0 00:00:41 Oracle interMedia VALID 10.2.0.4.0 00:02:24 Spatial VALID 10.2.0.4.0 00:01:37 Oracle Ultra Search VALID 10.2.0.4.0 00:00:22 Oracle Expression Filter VALID 10.2.0.4.0 00:00:09 Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:37 Oracle Rule Manager VALID 10.2.0.4.0 00:00:08 . |
| Comment by ubTools Support [ 15/Jun/08 06:34 PM ] |
|