ORA-04098: trigger ‘SYS.XDB_PI_TRIG’ is invalid and failed re-validation

Today i’ll show you how to solve this error.

O/S : CentOS release 5.9 (Final)
DB  : Oracle database 11.2.0.3

We created database with script, with custom character set and with only XML DB component
1
2
after creation database there were many invalid objects related XML DB.
Solution:
add LD_LIBRARY_PATH in bash_profile or profile if it is not for oracle user

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

Remove XDB, The catnoqm.sql script drops XDB.

SQL> shutdown immediate;
SQL> startup;
SQL> @ $ORACLE_HOME/rdbms/admin/catnoqm.sql

Install XDB,
The catqm.sql script requires the following parameters be passed to it when run:

A. XDB user password
B. XDB user default tablespace
(Any tablespace other than SYSTEM, UNDO and TEMP can be specified. The specified tablespace must already exist prior to running the script.)
C. XDB user temporary tablespace
D. SecureFiles = YES or NO
(If YES is specified, the XDB repository will use SecureFile storage. If NO is specified, LOBS will be used.
To use SecureFiles, compatibility must be set to 11.2.
The tablespace specified for the XDB repository must be using Automatic Segment Space Management (ASSM) for SecureFiles to be used.)

Therefore the syntax to run catqm.sql is the following:

SQL> catqm.sql A B C D

For Example:

SQL> @ $ORACLE_HOME/rdbms/admin/catqm.sql xdb_pass SYSAUX TEMP YES

## IMPORTANT: You must shutdown and restart the database between removal and reinstall ##

SQL> shutdown immediate;
SQL> startup;
SQL> @ $ORACLE_HOME/rdbms/admin/catqm.sql xdb_pass SYSAUX TEMP YES     
SQL> @ $ORACLE_HOME/rdbms/admin/utlrp.sql

Check status of XDB

select comp_name, version, status from dba_registry where comp_id = 'XDB';

Check for invalid objects owned by XDB

select owner, object_name, object_type, status from dba_objects where status = 'INVALID' and owner = 'XDB';

good luck with XDB🙂

One thought on “ORA-04098: trigger ‘SYS.XDB_PI_TRIG’ is invalid and failed re-validation

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