Sunday 13 October 2013

SQLPLUS APPS Connection is intermittently failing

SQLPLUS APPS Connection is intermittently failing

Reason for this Issue: Invalid TNS address supplied or destination is not listening. This error can also occur because of underlying network transport problems.

For my case it was wrong gateway configuration.

Successful:

mkkebstestu1@mkktestserver1:/home/mkkebstestu1>sqlplus apps/*************

SQL*Plus: Release 8.0.6.0.0 - Production on Mon Oct 29 15:42:35 2012

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Unsuccessful Attempts:

mkkebstestu1@mkktestserver1:/home/mkkebstestu1>sqlplus apps/************

SQL*Plus: Release 8.0.6.0.0 - Production on Mon Oct 29 15:46:49 2012

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

ERROR:
ORA-12203: TNS:unable to connect to destination


Enter user-name: 

mkkebstestu1@mkktestserver1:/home/mkkebstestu1>netstat -rn
Routing tables
Destination        Gateway           Flags   Refs     Use  If   Exp  Groups

Route Tree for Protocol Family 2 (Internet):
default            172.30.0.1        UG        1     34949 en2      -      -   =>
default            172.30.0.10       UG        7    795361 en2      -      -
60.60.60.1         127.0.0.1         UGHS      0      1733 lo0      -      -   =>
60.60.60.1         60.60.60.1        UHSb      0      1733 en2      -      -   =>
60.60.60.1/24      60.60.60.1        U         0         0 en2      -      -
60.60.60.255       60.60.60.1        UHSb      0         4 en2      -      -
127/8              127.0.0.1         U        14    781913 lo0      -      -
172.30.0.0         172.30.0.92       UHSb      0         0 en2      -      -   =>
172.30/24          172.30.0.92       U         2   6576453 en2      -      -
172.30.0.92        127.0.0.1         UGHS      1       458 lo0      -      -
172.30.0.255       172.30.0.92       UHSb      0         4 en2      -      -

Route Tree for Protocol Family 24 (Internet v6):
::1                ::1               UH        0        92 lo0      -      -
mkkebstestu1@mkktestserver1:/home/mkkebstestu1>

>>>> If you notice the netstat spool there are two gatways are defined. This is not a correct configuration. Highlighted in RED.


Solution: Flush the Gateway from smitty

mkktestserver1[/] # smitty route

Press Enter AFTER making all desired changes.

                                                        [Entry Fields]
  Flush Routing Table in the Current Running System   yes                                                                          +
  Flush IPV4 Routes in the Configuration Data Base    yes                                                                          +
  Flush IPV6 Routes in the Configuration Data Base    no                                                                           +
   (effective in the next system restart)

No comments:

Post a Comment