主备库间出现间歇性的ORA-12570报错

刘泽宇

Log Shipment Between Primary and Standby Intermittently Errors with ORA-12570 (Doc ID 550103.1)       

APPLIES TO:
Oracle Net Services - Version 9.2.0.1.0 to 11.2.0.1 [Release 9.2 to 11.2]
Information in this document applies to any platform.
Archive log shipment between nodes in Dataguard intermittently fail.

Checked for relevance on 19-MAR-2010.

SYMPTOMS
Shipment of log files between primary and standby Dataguard nodes may fail intermittently with
ORA-12570 TNS:packet reader failure.  If there is a firewall between the two nodes, this document may provide a solution.

Alert log shows

Creating archive destination LOG_ARCHIVE_DEST_4: 'Orcl2'
ARC0: Error 12570 Creating archive log file to 'Orcl2'
Sat Sep 29 00:59:48 2007
Errors in file /Software/Oracle/udump/orcl1_arc0_817588.trc:
ORA-12570: TNS:packet reader failure
Udump trace shows

*** 2007-09-29 00:59:48.452
Error 12570 creating standby archive log file at host 'Orcl2'
*** 2007-09-29 00:59:48.453
ARC0: Error 12570 Creating archive log file to 'Orcl2'
*** 2007-09-29 00:59:48.453
kcrrfail: dest:4 err:12570 force:0
ORA-12570: TNS:packet reader failure



CAUSE
If the firewall has a maximum idle time setting, it may be severing the connection between primary and standby databases if it perceives the connection to be idle.  This can happen when one side or the other is busy processing rather than transmitting data across the wire.

SOLUTION
If disabling the firewall max idle time setting is not an option, the following workaround may resolve the issue:

Set the following parameter in the sqlnet.ora file that would be referenced by each database, primary and standby.  This file is usually found in $ORACLE_HOME/network/admin or $TNS_ADMIN.

SQLNET.EXPIRE_TIME=n

Where n is some number of minutes. Preferably below 10.

e.g.

SQLNET.EXPIRE_TIME=5
When this parameter is enabled, a small 10 byte probe packet will be sent from node to node at specific intervals.   In our example, at roughly 5 minute intervals after the timer is armed, the probe packet will be sent until the timer is disarmed.   This should prevent an idle time termination by the firewall.

Set this parameter in the sqlnet.ora file on both servers, so any change such as switchover between nodes, ensures the parameter is set for both directions.