Google it ....

Showing posts with label XML DB. Show all posts
Showing posts with label XML DB. Show all posts

Wednesday, March 25, 2015

Textual description of firstImageUrl

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

In this blog post I'll show you how to solve error:
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 :)