重庆思庄技术分享——ORA-03135 When Connecting to the Database

ORA-03135 When Connecting to the Database

现象：
When attempting to connect to the primary database, the following error occurs.

  ERROR
    ORA-03135 connection lost contact

Generating an errorstack trace on the error:

SQL> alter session set events '3135 trace name errorstack level 3';
SQL> connect <username>/<password>


The trace file indicates that we a trying to update the AUDSES$ sequence.
The LGWR process is waiting on the LNS process:

waiting for 'LGWR-LNS wait on channel' blocking sess=0x(nil) seq=59925
wait_time=0 seconds since wait started=0 =0, =0, =0

LNS is waiting:

waiting for 'LNS wait on SENDREQ' blocking sess=0x(nil) seq=446
wait_time=0 seconds since wait started=0 =0, =0, =0


原因：
The LGWR is hung waiting for LNS which in turn is hung waiting for a tcp timeout, the session is waiting on 'log file sync' and eventually fails to connect after a minute. In this case the LGWR is stuck because we fail to update AUDSES$. AUDSES$ is a sequence which is getting updated on connection and is cached for 20 values so only every 20th connection or increment to audses$ will cause an update. If there is a problem to update the seq$, then the client connection will receive ORA-3135.

处理方法：
To implement the solution, please execute the following steps:

1. login to database

2. increase the cache size of AUDSES$ from 20 (default) to 10000

SQL> ALTER SEQUENCE sys.audses$ cache 10000;