Export problem and the invalid XDB library

This was about to be a regular export to the filesystem which is running on AIX 5.1 Operating system and the Oracle Database version is 9.2.0.7 and the Oracle client version is 10.2.0.3. But the weird error just popped out and interesting search results came up. Here is the story of the export.

Export: Release 10.2.0.3.0 - Production on Wed Oct 3 11:52:37 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8ISO8859P9 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user PARITEM 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user PARITEM 
About to export PARITEM's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 600 encountered
ORA-00600: internal error code, arguments: [unable to load XDB library], [], [], [], [], [], [], []
EXP-00000: Export terminated unsuccessfully

It seems there is a problem with the XDB library. When i select from dba_registry to see the components and the statuses, i realized there is a problem with the XML Database component. After investigation and searching the metalink, i came up to the a document which explains how to recreate the XML database (please see the end of the post for the related resources)

--INVALID XML Database component

SQL> col comp_name format a30
SQL> set pagesize 100
SQL> select comp_name, status, version from dba_registry;

COMP_NAME                      STATUS      VERSION
------------------------------ ----------- ------------------------------
Oracle9i Catalog Views         VALID       9.2.0.7.0
Oracle9i Packages and Types    VALID       9.2.0.7.0
Oracle Workspace Manager       VALID       9.2.0.1.0
JServer JAVA Virtual Machine   VALID       9.2.0.7.0
Oracle XDK for Java            VALID       9.2.0.9.0
Oracle9i Java Packages         VALID       9.2.0.7.0
Oracle Text                    VALID       9.2.0.7.0
Oracle XML Database            INVALID     9.2.0.7.0
Spatial                        VALID       9.2.0.7.0
Oracle Ultra Search            VALID       9.2.0.7.0
Oracle Data Mining             VALID       9.2.0.7.0
OLAP Analytic Workspace        UPGRADED    9.2.0.7.0
Oracle OLAP API                UPGRADED    9.2.0.7.0
OLAP Catalog                   VALID       9.2.0.7.0

14 rows selected.

-- Dropping xml database
SQL> @?/rdbms/admin/catnoqm.sql;
SQL> drop trigger sys.xdb_installation_trigger;
SQL> drop trigger sys.dropped_xdb_instll_trigger;
SQL> drop table dropped_xdb_instll_tab;

-- Recreating the xml database
SQL> startup migrate;
SQL> @?/rdbms/admin/catproc.sql;
SQL> @?/rdbms/admin/catqm.sql;
SQL> @?/rdbms/admin/dbmsxsch.sql;
SQL> @?/rdbms/admin/catxdbj.sql; -- only in 9i
SQL> @?/rdbms/admin/xdbpatch;

SQL> select comp_name, status, version from dba_registry;

COMP_NAME                      STATUS      VERSION
------------------------------ ----------- ------------------------------
Oracle9i Catalog Views         VALID       9.2.0.7.0
Oracle9i Packages and Types    VALID       9.2.0.7.0
Oracle Workspace Manager       VALID       9.2.0.1.0
JServer JAVA Virtual Machine   VALID       9.2.0.7.0
Oracle XDK for Java            VALID       9.2.0.9.0
Oracle9i Java Packages         VALID       9.2.0.7.0
Oracle Text                    VALID       9.2.0.7.0
Oracle XML Database            VALID       9.2.0.7.0
Spatial                        VALID       9.2.0.7.0
Oracle Ultra Search            VALID       9.2.0.7.0
Oracle Data Mining             VALID       9.2.0.7.0
OLAP Analytic Workspace        UPGRADED    9.2.0.7.0
Oracle OLAP API                UPGRADED    9.2.0.7.0
OLAP Catalog                   VALID       9.2.0.7.0

14 rows selected.

Re run the export now !...

 

resources:
http://www.oratransplant.nl/2005/11/22/unable-to-export-char-semantic-102-database/
Note:339938.1 - Full Export From 10.2.0.1 Aborts With EXP-56 ORA-932 (Inconsistent Datatypes) EXP-0
Note:243554.1 - How to Deinstall and Reinstall XML Database (XDB)

The way of opatch 10.2.0.4 Database on AIX

[oracle@]:/oracle/asmhome1/OPatch > opatch version
Invoking OPatch 10.2.0.5.1

OPatch Version: 10.2.0.5.1

OPatch succeeded.

[oracle@]:/oracle/asmhome1/OPatch > cd
[oracle@]:/oracle > . .profile

[oracle@]:/oracle > sql

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 1 09:48:52 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system checkpoint;

System altered.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@]:/oracle > 
[oracle@]:/oracle > . .profile_asm
[YOU HAVE NEW MAIL]
[oracle@]:/oracle > sql

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 1 09:54:58 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown;
ASM diskgroups dismounted
ASM instance shutdown
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@]:/oracle > lsnrctl stop

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 01-JUN-2011 09:49:09

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=defbora01)(PORT=1521)))
The command completed successfully

[oracle@]:/oracle/orahome1/OPatch > su - 
root's Password: 
[root@]:/home/root> /usr/sbin/slibclean
[root@]:/home/root>

[oracle@]:/oracle > cd $ORACLE_HOME/OPatch
[oracle@]:/oracle/asmhome1/OPatch > ls
11725015                              jlib                                  opatch.pl
README.txt                            ocm                                   opatchprereqs
crs                                   opatch                                p11725015_10204_AIX5L-cpuapr2011.zip
docs                                  opatch.bat                            p6880880_102000_AIX64-5L-opatch.zip
emdpatch.pl                           opatch.ini
[oracle@]:/oracle/asmhome1/OPatch > cd 11725015 
[oracle@]:/oracle/asmhome1/OPatch/11725015 > opatch napply --skip_subset --skip_duplicate

... 
(output truncated)
...

--------------------------------------------------------------------------------
**********************************************************************
**                       ATTENTION                                  **
**                                                                  **
** Please note that this Patch Installation is                      **
** not complete until all the Post Installation instructions        **
** noted in the Readme accompanying this patch, have been           **
** successfully completed.                                          **
**                                                                  **
**********************************************************************

--------------------------------------------------------------------------------


The local system has been patched and can be restarted.

UtilSession: N-Apply done.

OPatch succeeded.

[oracle@]:/oracle/asmhome1/OPatch/11725015 > 
[oracle@]:/oracle/asmhome1/OPatch/11725015 > opatch lsinv

... 
(output truncated)
...

[oracle@]:/oracle > . .profile
[oracle@]:/oracle/orahome1/OPatch > cd 11725015 
[oracle@]:/oracle/orahome1/OPatch/11725015 > opatch napply --skip_subset --skip_duplicate

...
(output truncated)
...

[oracle@]:/oracle/orahome1/OPatch/11725015 > sql

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 1 10:20:14 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 3221225472 bytes
Fixed Size                  2087480 bytes
Variable Size             637535688 bytes
Database Buffers         2566914048 bytes
Redo Buffers               14688256 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/catbundle.sql cpu apply
SQL> @?/rdbms/admin/utlrp.sql

[oracle@]:/oracle/orahome1/cpu/view_recompile > ls -l
total 24
-rwxr-xr-x    1 oracle   dba            2095 Jul 10 2008  recompile_precheck_jan2008cpu.sql
-rwxr-xr-x    1 oracle   dba            5143 Jul 10 2008  view_recompile_jan2008cpu.sql
[oracle@]:/oracle/orahome1/cpu/view_recompile > sql

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 1 10:34:16 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @recompile_precheck_jan2008cpu.sql;

Running precheck.sql...

Number of views to be recompiled :2226
-----------------------------------------------------------------------

Number of objects to be recompiled :4347
Please follow the README.txt instructions for running viewrecomp.sql

PL/SQL procedure successfully completed.

SQL> @view_recompile_jan2008cpu.sql;

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


1 row created.


Commit complete.

No. of Invalid Objects is :1848
Please refer to README.html to for instructions on validating these objects

PL/SQL procedure successfully completed.

Logfile for the current viewrecomp.sql session is : vcomp_CORET_01Jun2011_11_47_53.log
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 3221225472 bytes
Fixed Size                  2087480 bytes
Variable Size             637535688 bytes
Database Buffers         2566914048 bytes
Redo Buffers               14688256 bytes
Database mounted.
Database opened.
SQL> 

SQL> select bundle_series, action, version from dba_registry_history;

BUNDLE_SERIES          ACTION  VERSION
---------------------- ---------------- --------
CPU                    APPLY CPU 10.2.0.4


SQL> SELECT * FROM registry$history where ID = '6452863';

SQL> select bundle_series, action, version from registry$history where ID = '6452863';

BUNDLE_SERIES                  ACTION     VERSION
------------------------------ ---------- ------------------------------
CPU

Error in checking condition of instance on node

After rebooting both rac nodes srvctl started to complain about the condition of the second node of my cluster.

[oracle@EPRHEL6 admin]$ srvctl status database -d orcl
Instance ORCL1 is running on node eprhel5
PRKO-2015 : Error in checking condition of instance on node: eprhel6

[oracle@EPRHEL6 admin]$ sqlplus system/password@ORCL2

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 27 00:03:11 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name: 

srvctl also complains when i was trying to start the instance on the second node. So i decided to start the instance manually by using sqlplus.

[oracle@EPRHEL6 admin]$ sqlplus "/ as sysdba"


SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 27 00:03:24 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> startup;
Oracle instance started.

Total System Global Area 599785472 bytes
Fixed Size     2022600 bytes
Variable Size   188744504 bytes
Database Buffers  402653184 bytes
Redo Buffers     6365184 bytes
Database mounted.
Database opened.
SQL> alter system register;

System altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

[oracle@EPRHEL6 admin]$ sqlplus system/password@ORCL2

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 27 00:04:18 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

It seems there is not any problem with the instance itself. Sqlplus barely connects to the instance ORCL2. There should be a problem about the way of communication between srvctl and the instance.

[oracle@EPRHEL6 admin]$ crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora....L1.inst application    ONLINE    ONLINE    eprhel5     
ora....L2.inst application    ONLINE    UNKNOWN   eprhel6     
ora.ORCL.db    application    ONLINE    ONLINE    eprhel5     
ora....SM1.asm application    ONLINE    ONLINE    eprhel5     
ora....L5.lsnr application    ONLINE    ONLINE    eprhel5     
ora....el5.gsd application    ONLINE    ONLINE    eprhel5     
ora....el5.ons application    ONLINE    ONLINE    eprhel5     
ora....el5.vip application    ONLINE    ONLINE    eprhel5     
ora....SM2.asm application    ONLINE    ONLINE    eprhel6     
ora....L5.lsnr application    OFFLINE   OFFLINE               
ora....L6.lsnr application    ONLINE    ONLINE    eprhel6     
ora....el6.gsd application    ONLINE    ONLINE    eprhel6     
ora....el6.ons application    ONLINE    ONLINE    eprhel6     
ora....el6.vip application    ONLINE    ONLINE    eprhel6     

[oracle@EPRHEL6 admin]$ srvctl start listener -n EPRHEL6

[oracle@EPRHEL6 admin]$ crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora....L1.inst application    ONLINE    ONLINE    eprhel5     
ora....L2.inst application    ONLINE    UNKNOWN   eprhel6     
ora.ORCL.db    application    ONLINE    ONLINE    eprhel5     
ora....SM1.asm application    ONLINE    ONLINE    eprhel5     
ora....L5.lsnr application    ONLINE    ONLINE    eprhel5     
ora....el5.gsd application    ONLINE    ONLINE    eprhel5     
ora....el5.ons application    ONLINE    ONLINE    eprhel5     
ora....el5.vip application    ONLINE    ONLINE    eprhel5     
ora....SM2.asm application    ONLINE    ONLINE    eprhel6     
ora....L5.lsnr application    OFFLINE   OFFLINE               
ora....L6.lsnr application    ONLINE    ONLINE    eprhel6     
ora....el6.gsd application    ONLINE    ONLINE    eprhel6     
ora....el6.ons application    ONLINE    ONLINE    eprhel6     
ora....el6.vip application    ONLINE    ONLINE    eprhel6  

[oracle@EPRHEL6 admin]$ sqlplus system/password@ORCL1

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 27 00:04:35 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> show parameter listener;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string
remote_listener                      string      LISTENERS_ORCL
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

I think there is a problem with listener configuration or the listener resource itself. But everything seems fine except that OFFLINE resource. After searching google a little bit, i found a solution indicates listener configuration. I decided to recreate the listeners with netca. I will first delete listener named LISTENER from both ASM and DB homes using netca and then recreate them only using DB home. Maybe this resolves the problem.

My action plan is first stop all asm and db instances. Manually remove that OFFLINE listener which is very confusing. Remove all the listener configuration from the cluster with netca and recreate using db home. Here we go.

[oracle@EPRHEL6 db]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 27-DEC-2010 00:22:16

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_EPRHEL6
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                27-DEC-2010 00:02:31
Uptime                    0 days 0 hr. 19 min. 44 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/asm/network/admin/listener.ora
Listener Log File         /oracle/product/asm/network/log/listener_eprhel6.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.28.4.226)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.28.4.246)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...
Service "ORCL" has 2 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Instance "ORCL2", status READY, has 2 handler(s) for this service...
Service "ORCLXDB" has 2 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Instance "ORCL2", status READY, has 1 handler(s) for this service...
Service "ORCL_XPT" has 2 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Instance "ORCL2", status READY, has 2 handler(s) for this service...
The command completed successfully

[oracle@EPRHEL6 db]$ srvctl stop database -d orcl
[oracle@EPRHEL6 db]$ srvctl stop asm -n EPRHEL5
[oracle@EPRHEL6 db]$ srvctl stop asm -n EPRHEL6
[oracle@EPRHEL6 db]$ crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora....L1.inst application    OFFLINE   OFFLINE               
ora....L2.inst application    OFFLINE   OFFLINE               
ora.ORCL.db    application    OFFLINE   OFFLINE               
ora....SM1.asm application    OFFLINE   OFFLINE               
ora....el5.gsd application    ONLINE    ONLINE    eprhel5     
ora....el5.ons application    ONLINE    ONLINE    eprhel5     
ora....el5.vip application    ONLINE    ONLINE    eprhel5     
ora....SM2.asm application    OFFLINE   OFFLINE               
ora....L5.lsnr application    OFFLINE   OFFLINE               
ora....el6.gsd application    ONLINE    ONLINE    eprhel6     
ora....el6.ons application    ONLINE    ONLINE    eprhel6     
ora....el6.vip application    ONLINE    ONLINE    eprhel6     

[oracle@EPRHEL6 db]$ crs_getperm ora.eprhel6.LISTENER_EPRHEL5.lsnr
Name: ora.eprhel6.LISTENER_EPRHEL5.lsnr
owner:oracle:rwx,pgrp:dba:rwx,other::r--,
[oracle@EPRHEL6 db]$ crs_unregister ora.eprhel6.LISTENER_EPRHEL5.lsnr
[oracle@EPRHEL6 db]$ crs_profile -delete ora.eprhel6.LISTENER_EPRHEL5.lsnr
CRS-0170: The resource 'ora.eprhel6.LISTENER_EPRHEL5.lsnr' doesn't exist.

[oracle@EPRHEL6 db]$ crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora....L1.inst application    ONLINE    ONLINE    eprhel5     
ora....L2.inst application    ONLINE    ONLINE    eprhel6     
ora.ORCL.db    application    ONLINE    ONLINE    eprhel5     
ora....SM1.asm application    ONLINE    ONLINE    eprhel5     
ora....L5.lsnr application    ONLINE    ONLINE    eprhel5     
ora....el5.gsd application    ONLINE    ONLINE    eprhel5     
ora....el5.ons application    ONLINE    ONLINE    eprhel5     
ora....el5.vip application    ONLINE    ONLINE    eprhel5     
ora....SM2.asm application    ONLINE    ONLINE    eprhel6     
ora....L6.lsnr application    ONLINE    ONLINE    eprhel6     
ora....el6.gsd application    ONLINE    ONLINE    eprhel6     
ora....el6.ons application    ONLINE    ONLINE    eprhel6     
ora....el6.vip application    ONLINE    ONLINE    eprhel6     
[oracle@EPRHEL6 db]$ srvctl status database -d orcl
Instance ORCL1 is running on node eprhel5
Instance ORCL2 is running on node eprhel6
[oracle@EPRHEL6 db]$ 

It seems problem is solved.