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;