How to Trace or Debug Database for issues / How to setup Tracing in database.

often during integration testing we encounter ORA Data base errors,  In this case we are debugging ORA- 02074 error.

here is how we can get more info about the tracing.

You need to set the command as below to you DB:

alter system set events '2074 trace name errorstack level 3';

If you would like to know more about the alter system set events command please

Oracle event trace types - by Burleson Consulting

When you set the above command and reproduce the issue, what ever that might be, look into the Alert_xxx.log file for statements such as below

Mon Apr 04 10:31:34 2011
Errors in file <Path>/trace/<InstanceName>_ora_20926.trc:
ORA-02074: cannot SET NLS in a distributed transaction
Mon Apr 04 10:31:36 2011
Errors in file <Path>/trace/<InstanceName>_ora_20950.trc:
ORA-02074: cannot SET NLS in a distributed transaction

This file gives the trace file name which contains the session dumps. In order to read the trace file, I believe you need extensive DBA skills. But do give it a shot.

How to Reset or Refresh Oracle ESB (10g) MetaData Repository.

Sometimes ESB repository does not behaves as expected especially after a rigorous DEV cycles or for some other reasons. In order to bring it back to clean, at $ORACLE_HOME/integration/esb/bin directory a script called ‘reset.bat/sh’ is available. This script will cleanup the ESB repository. Simply execute the command as shown below and follow the output.

./reset.sh -DDB_URL=jdbc:oracle:thin:@//db_host:db_port/sid -DDB_USER=<User> -DDB_PASSWORD=<Password>

Note: Once you are able to execute it successfully, its very important to restart your ESB instance before using it.

Also remember to Deploy/redeploy all the ESB and BPEL process once again.