Sandesh Bangera

The following set of diagnostic data collection routines must be run when database is not responding to TNS connection request and queries to views like v$session, etc hang. The symptoms of this hang are Unable to connect using tnsnames to local instance. E.g. if ssh on node 01A, then cannot sqlplus to instance hosted on that server. E.g. sqlplus username/password@connectstring Unable to connect using tnsname .e.g sqlplus username/password@connectstring_active Bequeath connections are successful. E.g. sqlplus / as sysdba Querying certain V$ views causes the session to hang. Diagnostic Data set 1:   SQL> set lines 250 pages 1000   SQL> spool xle_xkclfx.out   SQL> select * from x$le;   SQL> select * from x$kclfx;   SQL> exec dbms_lock.sleep(60);   SQL> select * from x$le;   SQL> select * from x$kclfx;   SQL> exec dbms_lock.sleep(60);   SQL> select * from x$le;   SQL> select ...

At times while resolving streams replication issues, it might be faster to generate individual queries associated with an failed LCR instead of relying on routines provided by the DBMS_APPLY_ADM package.In addition the script attached to this blog can be used to clear LCR back log in parallel as long as the source commit order is maintained. The driving query in the anonymous block is as follows and chose an appropriate filter to handle your issue for currec in (  select local_transaction_id, message_count  from dba_apply_error    where  -- local_transaction_id = '70.18.2231436' --# Use this filter to handle one LCR at a time     -- ERROR_MESSAGE LIKE '%DBOR_USERS%' --# Use this filter to handle LCR which have errors in the same table     -- error_creation_time >= to_timestamp('18-MAR-2013 08:00:00','dd-MON-RRRR HH24:MI:SS')  --#  Use the following two to filter on a date range.     --and error_creati...