Ora-12170:tns:connect timeout occurred ora-06512:at "SYS. Dbms_snapshot "

Tag: ora-12170:tns:connect timeout occurred ora-06512:at "Sys.dbms_snapshot"

oracle job  cannot automatically run   report the following large number of 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, two databases to refresh materialized views, usually normal, one day to start the main library automatic job execution failed, and is a continuous failure to see the main reason is ORA-12170:  tns:connect timeout occurred tns timeout caused, Troubleshooting ideas can probably be set on the network 1. Network troubleshooting between the two hosts ping each other's IP address and gateway, see if there is no packet loss situation at the beginning of the view by View no Drop packet 2. Check if firewall 1521 port is normal network engineer confirm firewall 1521 Port normal open,telnet  1521 are also the 3.tnsping  individual service names of the pass $tnsping orcltns ping utility for 64-bit aix:  Version 10.2.0.4.0 - production on 05-6 month  -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-6 month  -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 OK 4. All networks appear to be normal, the network seems to be no problem, but the job is always an error, because the two hosts are no longer the same network segment, the ping task is continuing, suddenly in a few moments there is a drop packet phenomenon,telnet  1521 Port also has a certain number of times the phenomenon of the network is still a problem, finally with the customer confirmed that someone has re-planned the network, so look at the host IP and routing 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):d efault             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      -      -        Thus, the host's routing is still problematic, the host has two default routes      in Aix if there is a default gateway set more than one, there will be a time-out of the loss of the packet phenomenon, So into the AIX route table to see, sure enough there are two default routes, 192.168.10.1 and 192.168.10.254, the same network card exists such two routes must be problematic, the use of both routes is very high, After confirming with the network engineer that the. 254 route is invalid, the reason may be that the old route was forgotten when the route was modified earlier * * * * * Deprecated routing > #route  delete  -if en0  default  192.168.10.254192.168.10.254 net default: gateway 192.168.10.254* * * After the route, observe an hour, all jobs are performed normally, no more TNS timeout appears, modify any parameters of the production system to follow strict standards and check the process to do, stability over all

This article is from the "O Record" blog, so be sure to keep this source http://evils798.blog.51cto.com/8983296/1420950