Shulou(Shulou.com)06/01 Report--

Oracle job cannot automatically run the following numerous errors Errors in file / oracle/admin/orcl/bdump/orcl_j000_2158798.trc:ORA-12012: error on auto execute of job 482ORA-12008: error in materialized view refresh pathORA-12170: TNS:Connect timeout occurredORA-06512: at "SYS.DBMS_SNAPSHOT", line 1883ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2089ORA-06512: at "SYS.DBMS_IREFRESH", line 683ORA-06512: at "SYS.DBMS_REFRESH" Line 195ORA-06512: at line 1Sat Apr 26 21:57:53 2014Errors in file / oracle/admin/orcl/bdump/orcl_j000_2158798.trc:ORA-12012: error on auto execute of job 425ORA-12170: TNS:Connect timeout occurredORA-06512: at "SYS.DBMS_SNAPSHOT", line 1883ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2089ORA-06512: at "SYS.DBMS_IREFRESH", line 683ORA-06512: at "SYS.DBMS_REFRESH", line 195 main environment introduction It is normal to refresh the materialized view between the two databases. One day, the automatic job execution of the main database fails, and it is a persistent failure. The main reason is the timeout of ORA-12170: TNS:Connect timeout occurred TNS. The troubleshooting idea can be set on the network. Network troubleshooting ping each other's IP addresses and gateways between the two hosts to see if there is any packet loss. Check whether the firewall port 1521 is normal. The network engineer confirms that the firewall port 1521 is open normally, and the telnet 1521 is also connected to the 3.tnsping. Their respective service names are $tnsping orclTNS Ping Utility for 64-bit Aix: Version 10.2.0.4.0-Production on 05-June-2014 11:47:24Copyright (c) 1997, 2006, 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 yyzfTNS Ping Utility for 64-bit Aix: Version 10.2.0.4.0-Production on 05-June-2014 11:48:24Copyright (c) 1997, 2006, 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) tnsping service name is also 4. 0 of OK. So far, all the networks seem to be normal, and it seems that there should be no problems with the network, but job has been reporting errors all the time. Because the two hosts are no longer on the same network segment, the ping task continues, suddenly there is packet loss at certain moments, and telnet 1521 port is also blocked for several times. So it seems that there is still something wrong with the network, and finally confirm with the customer that someone has replanned the network. So check the host ip and route Command: OK stdout: yes stderr: noBefore command completion Additional instructions may appear below.Routing tablesDestination Gateway Flags Refs Use If Exp GroupsRoute 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 en1-- = > 10 UHSb 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 en0-- = > 10.0.1 UGHS 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-- 127Compare 8 127.0.0.1 U 31 6493884 lo0-- 192.168.10.0 192.168.10.9 UHSb 0 en0- -= > 192.168.10.0 192.168.10.10 UHSb 0 0 en1-- = > 192.168.10 UHSb 27 192.168.10.9 U 0 193565 en0-- = > 192.168.10.10 U1 209927 en1-- 192.168.10.9 127.0.0.1 UGHS 23 13738093 lo0-- 192.168.10.10 127.0.1 UGHS 0 151330 lo0-- 192.168.10.31 192.168.10.9 UHSb 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 way There is still a problem with the routing of the host. There are two default routes for the host. If there are multiple default gateways in the AIX, there will be intermittent packet loss. So go to the routing table of AIX and see that there are two default routes, 192.168.10.1 and 192.168.10.254. There must be problems with the existence of such two routes on the same network card. The use usage of both routes is very high. After confirming with the network engineer that the route of .254 is invalid, the reason for its existence may be that when you modified the route before, you forgot * the old route led to * abandoned route > # route delete-if en0 default 192.168.254192.168.10.254 net default: gateway 192.168.10.254 routes *, after one hour of observation, all job executed normally, and there seemed to be no TNS timeout. Any parameters of the production system must be modified in accordance with strict standards and inspection procedures, and stability is more important than anything else.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.