ORA-12170: TNS: ConnecttimeoutoccurredORA-06512: at "SYS. D

ORA-12170: TNS: ConnecttimeoutoccurredORA-06512: atSYS. DBMS_SNAPSHOT

ORA-12170: TNS: Connect timeout occurred ORA-06512: at SYS. DBMS_SNAPSHOT

Oracle jobs cannot run automatically and report the following errors:

Errors in file/oracle/admin/orcl/bdump/orcl_j000_2158798.trc:
ORA-12012: error on auto execute of job 482
ORA-12008: error in materialized view refresh path
ORA-12170: TNS: Connect timeout occurred
ORA-06512: at "SYS. DBMS_SNAPSHOT", line 1883
ORA-06512: at "SYS. DBMS_SNAPSHOT", line 2089
ORA-06512: at "SYS. DBMS_IREFRESH", line 683
ORA-06512: at "SYS. DBMS_REFRESH", line 195
ORA-06512: at line 1
Sat Apr 26 21:57:53 2014
Errors in file/oracle/admin/orcl/bdump/orcl_j000_2158798.trc:
ORA-12012: error on auto execute of job 425
ORA-12170: TNS: Connect timeout occurred
ORA-06512: at "SYS. DBMS_SNAPSHOT", line 1883
ORA-06512: at "SYS. DBMS_SNAPSHOT", line 2089
ORA-06512: at "SYS. DBMS_IREFRESH", line 683
ORA-06512: at "SYS. DBMS_REFRESH", line 195


Main Environment Introduction: refresh the materialized view between two databases. Normally, the automatic job execution of the master database fails one day, and it is a continuous failure.

Check the reason is mainly due to the ORA-12170: TNS: Connect timeout occurred TNS timeout caused by, troubleshooting ideas can probably be set on the network


1. network troubleshooting

Ping each other's IP addresses and gateways to check whether packet loss exists.

Check no packet loss at the beginning of the period

2. Check whether port 1521 of the firewall is normal.

The network engineer confirmed that port 1521 of the firewall is open normally, and telnet 1521 is successful.

3. The respective service names of tnsping
$ Tnsping orcl
TNS Ping Utility for 64-bit Aix: Version 10.2.0.4.0-Production on-2014 11:47:24
Copyright (c) 1997,200 6, Oracle Corporation. All rights reserved.
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.10.8) (PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = orcl )))
OK (0 msec)
OK (10 msec)


$ Tnsping yyzf
TNS Ping Utility for 64-bit Aix: Version 10.2.0.4.0-Production on-2014 11:48:24
Copyright (c) 1997,200 6, Oracle Corporation. All rights reserved.
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.11.9) (PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = yyzf )))
OK (0 msec)
OK (10 msec)

The tnsping service name is also OK.

4. so far, all networks seem to be normal, and the network seems to be normal, but the job still reports an error because the two hosts are no longer in the same network segment, and the ping task continues, suddenly there was packet loss at some time, and the telnet port 1521 failed several times. In this case, the network still had a problem. Finally, I confirmed with the customer that someone had re-planned the network, view the Host ip address and route


Command: OK stdout: yes stderr: no

Before command completion, additional instructions may appear below.

Routing tables
Destination Gateway Flags Refs Use If Exp Groups

Route tree for Protocol Family 2 (Internet ):
Default 192.168.10.1 UG 60 442255197 en0--=>
Default 192.168.10.254 UG 40 432853407 en0 --
10.0.0.0 10.0.0.1 UHSb 0 0 en1--=>
10/27 10.0.0.1 U 1 11339108 en1 --
10.0.0.1 127.0.0.1 UGHS 0 1840821 lo0 --
10.0.0.31 10.0.0.1 UHSb 0 10 en1 --
10.0.1.0 10.0.1.1 UHSb 0 0 en0-=>
10.0.1/27 10.0.1.1 U 2 12382572 en0 --
10.0.1.1 127.0.0.1 UGHS 2 3268795 lo0 --
10.0.1.31 10.0.1.1 UHSb 0 10 en0 --
127/8 127.0.0.1 U 31 6493884 lo0 --
192.168.10.0 192.168.10.9 UHSb 0 0 en0-=>
192.168.10.0 192.168.10.10 UHSb 0 0 en1--=>
192.168.10/27 192.168.10.9 U 0 193565 en0--=>
192.168.10/27 192.168.10.10 U 1 209927 en1 --
192.168.10.9 127.0.0.1 UGHS 23 13738093 lo0 --
192.168.10.10 127.0.0.1 UGHS 0 151330 lo0 --
192.168.10.31 192.168.10.9 UHSb 0 0 en0-=>
192.168.10.31 192.168.10.10 UHSb 0 0 en1 --

Route tree for Protocol Family 24 (Internet v6 ):
: 1: 1 UH 0 0 lo0 --


In this case, there are still problems with the Host Routing, and there are two default routes on the host.

If multiple default gateways are configured in AIX, packet loss may occur during intermittent disconnection. Therefore, you can check the AIX route table. There are two default routes, 192.168.10.1 and 192.168.10.254, there must be a problem with the two routes in the same Nic. The use of the two routes is very high and confirmed with the network engineer. 254 of the routes are invalid because you forget the old route when modifying the route.



As a result, *** discarded routes

> # Route delete-if en0 default 192.168.10.254
192.168.10.254 net default: gateway 192.168.10.254

* ** After the route, observe for one hour. All jobs run normally without the TNS timeout.

It seems that any modification to production system parameters must follow strict standards and inspection procedures. Stability is more important than everything else.

-------------------------------------- Split line --------------------------------------

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

-------------------------------------- Split line --------------------------------------