解决ora600-[qmtb_init_len]错误

今天在做9i到10g数据库的升级，升级过程中因为错误而强行中断。

查看alert日志发现如下错误：
Sat Mar 22 16:35:50 2008
Thread 1 advanced to log sequence 385
  Current log# 3 seq# 385 mem# 0: /opt/oracle/oradata/irmdb/redo03.log
Sat Mar 22 16:36:06 2008
SERVER COMPONENT id=CATJAVA: status=VALID, version=10.2.0.3.0, timestamp=2008-03
-22 16:36:06
Sat Mar 22 16:37:07 2008
SERVER COMPONENT id=CONTEXT: status=VALID, version=10.2.0.3.0, timestamp=2008-03
-22 16:37:07
Sat Mar 22 16:37:19 2008
Errors in file /opt/oracle/admin/irmdb/udump/irmdb_ora_819378.trc:
ORA-00600: internal error code, arguments: [qmtb_init_len], [], [], [], [], [],
[], []
Sat Mar 22 16:37:23 2008
Errors in file /opt/oracle/admin/irmdb/udump/irmdb_ora_819378.trc:
ORA-00600: internal error code, arguments: [qmtb_init_len], [], [], [], [], [],
[], []
Sat Mar 22 16:37:26 2008
Errors in file /opt/oracle/admin/irmdb/udump/irmdb_ora_819378.trc:
ORA-00600: internal error code, arguments: [qmtb_init_len], [], [], [], [], [],
[], []

又遇见了比较可恶的ora600错误，那么错误的原因查了一下metalink是由于(qmtb_init_len)也就XDB的一个在数据库升级之前组件是invalid状态，我们可以通过查看dba_registry视图来获得结果.

SQL> desc dba_registry;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------
 COMP_ID                                   NOT NULL VARCHAR2(30)
 COMP_NAME                                          VARCHAR2(255)
 VERSION                                            VARCHAR2(30)
 STATUS                                             VARCHAR2(11)
 MODIFIED                                           VARCHAR2(29)
 CONTROL                                   NOT NULL VARCHAR2(30)
 SCHEMA                                    NOT NULL VARCHAR2(30)
 PROCEDURE                                          VARCHAR2(61)
 STARTUP                                            VARCHAR2(8)
 PARENT_ID                                          VARCHAR2(30)

SQL> select comp_id,comp_name,version,status from dba_registry
  2  where status='INVALID';

COMP_ID                        COMP_NAME
------------------------------ ------------------------------
VERSION                        STATUS
------------------------------ -----------
XDB                            Oracle XML Database
9.2.0.6.0                      INVALID

现在XDB处于INVALID的状态。

Metalink给出的意见是：
However, it can be resolved by making the following changes to the catqm.sql and dbmsxsch.sql scripts and then dropping and recreating the XDB database

1.  Make backup copies of the 'catqm.sql' and 'dbmsxsch.sql' scripts in your 9.2.0.X
     $ORACLE_HOME/rdbms/admin directory.

2.  Then edit the 'dbmsxsch.sql' script. and comment out the call to 'prvtxsch.plb' at line 269, e.g.:

Current line 269: @@prvtxsch.plb
Change to: rem @@prvtxsch.plb

Then save this file.

3. Now edit the 'catqm.sql' script. and add the following entry at line 198 after the call to catxdbpi, e.g.:

Current line 197: @@catxdbpi
Current line 198:
Change to: @@prvtxsch.plb

之后就可以de-install和re-install我们的XDB了
下面是一些步骤仅供参考：
How to Deinstall and Reinstall XML Database (XDB)
****************************
XDB SCHEMA REMOVAL STEPS:
****************************
WARNING: The steps to remove the XML Database feature (XDB schema) will completely remove the XDB repository including any user defined information contained within it.
Therefore, if there are any registered schemas or XML Database items created, you will be required to recreate all of your user defined structures, data and re-register all schemas previously registered.

If you are running Oracle Applications with iSetup see NOTE:402785.1 linked below.

WARNING: If you are on database release 10.1.x or 10.2.x the XDB Feature is Mandatory in order to use any of the member functions of the XMLTYPE. This is true even if you are not using the repository , or registered schema aspects of the XDB feature.

1. Shutdown and restart the database.

2. Connect as sysdba and run the catnoqm.sql script. Maintain a spool of the
script. running:

UNIX: $ORACLE_HOME/rdbms/admin subdirectory
WINDOWS: $ORACLE_HOME\rdbms\admin subdirectory

For Example:
SQL> set echo on
SQL> spool xdb_removal.log
SQL> @?/rdbms/admin/catnoqm.sql


3. Modify the init.ora file or spfile to include the following minimums to prepare the DB for reinstalling the XDB schema:

shared_pool_size =150 MB
java_pool_size =150 MB

4. Turn on AUTO EXTEND on the XDB tablespace.
If you do not want to do this , make sure you target a tablespace with at
least
.100 MB of free space for non-UTF8 DB
.150 MB of free space for an AL32UTF8 DB

5. Shutdown the database immediate, and startup the database normal

NOTE: Failure to restart the database at this step can cause XDB catqm.sql installation to fail with an internal error similar to the following: ORA-7445 [qmr_hdl_copy()+48]

*****************************
XDB SCHEMA INSTALL STEPS:
*****************************
1. Connect as sysdba and run the catqm.sql script. Maintain a spool of the
script. running:

UNIX: $ORACLE_HOME/rdbms/admin subdirectory
WINDOWS: $ORACLE_HOME\rdbms\admin subdirectory

The catqm.sql script. requires the following parameters be passed to it when
run:
A. XDB user password
B. XDB user default tablespace
C. XDB user temporary tablespace
Therefore the syntax to run catqm.sql will be:
SQL> catqm.sql A B C

For Example:
SQL> set echo on
SQL> spool xdb_install.log
SQL>@?/rdbms/admin/catqm.sql XDB XDB TEMP

The Following Step is for Release 9.2.x ONLY skip to step 3 if running 10.1.x or above

2.Reconnect to SYS again and run the following to load the XDB java library.

SQL>@?/rdbms/admin/catxdbj.sql

NOTE: In order to load the XDB java libraries in catxdbj.sql, you must first have a valid Java Virtual Machine installation in the DB (JVM) and a valid XDK.
Also, make sure that the database is started with Oracle9i Release 2 (9.2.0) compatibility or higher for database version 9.2


3. If the following line is not already apart of the database system parameters (init.ora/spfile).
NOTE: PLEASE REPLACE <sid> ,instanceid1,2 etc with your actual values

a. Non-RAC
dispatchers="(PROTOCOL=TCP) (SERVICE=<sid>XDB)"
b. RAC
instanceid1.dispatchers="(PROTOCOL=TCP) (SERVICE=<instanceid1>XDB)"
instanceid2.dispatchers="(PROTOCOL=TCP) (SERVICE=<instanceid2>XDB)"
etc ...
c.If you are not using the default Listener ensure you have set LOCAL_LISTENER in the (init.ora/spfile)
as prescribed for RAC/NON-RAC instances or the end points will not register.


4. Check for any invalid XDB owned objects:

SQL> select count(*) from dba_objects
where wner='XDB' and status='INVALID';

COUNT(*)
----------
0

5. Check DBA_REGISTRY for XDB status:
SQL> select comp_name, status, version from DBA_REGISTRY where comp_name=
'Oracle XML Database'

The results should indicate the correct version and patch in a valid status.

6. Restart database and listener to enable Oracle XML DB protocol access.

之后再次验证VALID:
SQL> select comp_name, status, version from DBA_REGISTRY where comp_name=
  2  'Oracle XML Database' ;

COMP_NAME
--------------------------------------------------------------------------------
STATUS      VERSION
----------- ------------------------------
Oracle XML Database
VALID       9.2.0.6.0

并重新升级数据库,ok成功。

备注如果在重新安装XDB时，alert日志中出现如下错误，那么肯定是link lib库的问题
ORA-00600: internal error code, arguments: [unable to load XDB library]

我们可以参照metalink的步骤来解决：
* fact: XML Database (XDB)
* symptom: Configuration of XDB fails
* symptom: 0509-022 Cannot load module <$ORACLE_HOME>/lib32/libxdb.so.
*
* symptom: 0509-124 The program is a discontinued 64-bit object file.
* symptom: ORA-00600 [unable to load XDB library]
* cause: LD_LIBRARY_PATH and LIBPATH pointed to the wrong directories.

fix:

Set LD_LIBRARY_PATH and LIBPATH to the correct values

csh :
  $ setenv LD_LIBRARY_PATH=$ORACLE_HOME/lib
  $ setenv LIBPATH=$ORACLE_HOME/lib

ksh :
  $ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
  $ export LIBPATH=$ORACLE_HOME/lib