ORA-04098: trigger 'SYS.XDB_PI_TRIG' is invalid and failed re-validation
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
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 :)
