Tuesday, March 8, 2011

Tracing a specific ORA error

To trace a specific ORA error I am using below scenario:

One of out users complained us about below error while running a third party application:
"ORA-01775: looping chain of synonyms"

We have a 4 node RAC and we went through all 4 alert logs but could not find it on alert logs.
Then we have done below on "node 1":
Start trace:
SQL> alter system set events '1775 trace name ERRORSTACK level 3';

Then, told the user to connect to "node 1" [using SID & IP in TNS ] repeat the action caused the error.

Now on alert log on "node 1" we found below entry:

Sat Mar 05 11:59:31 2011
Errors in file /u01/app/oracle/diag/rdbms/dw/dw1/trace/dw1_ora_30788.trc:
ORA-01775: looping chain of synonyms

From the trace file found in the alert log we point out the query & object responsible for the error.
In this case we found that a synonym pointing a table but the table was not in dba_objects.

Now,on "node 1" stop trace:
SQL> alter system set events '1775 trace name errorstack off';

More about trace:
http://toddlerdba.blogspot.com/2011/01/session-trace.html
https://netfiles.uiuc.edu/jstrode/www/oraelmt/trace_using_parameters.html

No comments:

Post a Comment