Hello,
I have a setup consisting of a primary database and a physical standby Data Guard configuration, all running on Oracle 11.2.0.2.0 and Windows Server 2008 SP2.
My issue started with the "ORA-16778: redo transport error for one or more databases", digging further the problem narrows down to a TNS protocol error, but this problem is only on the standby database - all other instances on the same server don't have any connection issues. When I try to connect manually to the standby instance with sqlplus, only about half of connections succeeds (maybe every other connection I try), here are the log files from the failed connections:
Primary server alert log:
Fatal NI connect error 12518, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PS03.ad.rferl.org)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=HPROD89.ad.rferl.org)(UR=A)(CID=(PROGRAM=c:\app\oracle\product\11.2.0.2.0\dbhome_1\bin\ORACLE.EXE)(HOST=PS01)(USER=SYSTEM))))
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
Time: 07-MAR-2011 09:37:03
Tracing not turned on.
Tns error struct:
ns main err code: 12564
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Error 12518 received logging on to the standby
FAL[server, ARC4]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
Mon Mar 07 09:42:11 2011
***********************************************************************
Fatal NI connect error 12518, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PS03.ad.rferl.org)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=HPROD89.ad.rferl.org)(UR=A)(CID=(PROGRAM=c:\app\oracle\product\11.2.0.2.0\dbhome_1\bin\ORACLE.EXE)(HOST=PS01)(USER=SYSTEM))))
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
Time: 07-MAR-2011 09:42:11
Tracing not turned on.
Tns error struct:
ns main err code: 12564
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
***********************************************************************
Fatal NI connect error 12518, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PS03.ad.rferl.org)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=HPROD89.ad.rferl.org)(UR=A)(CID=(PROGRAM=c:\app\oracle\product\11.2.0.2.0\dbhome_1\bin\ORACLE.EXE)(HOST=PS01)(USER=SYSTEM))))
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
Time: 07-MAR-2011 09:42:12
Tracing not turned on.
Tns error struct:
ns main err code: 12564
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Error 1031 received logging on to the standby
Mon Mar 07 09:43:12 2011
***********************************************************************
Fatal NI connect error 12518, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PS03.ad.rferl.org)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=HPROD89.ad.rferl.org)(UR=A)(CID=(PROGRAM=c:\app\oracle\product\11.2.0.2.0\dbhome_1\bin\ORACLE.EXE)(HOST=PS01)(USER=SYSTEM))))
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
Time: 07-MAR-2011 09:43:13
Tracing not turned on.
Tns error struct:
ns main err code: 12564
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
***********************************************************************
Fatal NI connect error 12518, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PS03.ad.rferl.org)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=HPROD89.ad.rferl.org)(UR=A)(CID=(PROGRAM=c:\app\oracle\product\11.2.0.2.0\dbhome_1\bin\ORACLE.EXE)(HOST=PS01)(USER=SYSTEM))))
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
Time: 07-MAR-2011 09:43:13
Tracing not turned on.
Tns error struct:
ns main err code: 12564
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Error 1031 received logging on to the standby
Standby server listener log:
07-MAR-2011 08:56:19 * service_update * hclone89 * 0
07-MAR-2011 08:56:21 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=HPROD89.ad.rferl.org)(UR=A)(CID=(PROGRAM=C:\app\oracle\product\11.2.0.2.0\dbhome_2\bin\sqlplus.exe)(HOST=PS03)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.17.41.29)(PORT=56099)) * establish * HPROD89.ad.rferl.org * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
64-bit Windows Error: 203: Unknown error
Any take on this? Why is only the standby instance affected?
As far as the redolog transport, logs do get applied, just with a huge "transport lag".
Thanks.