Solved

Oracle 11g Release 2: unable to connect via sqlplus

Posted on 2015-01-14
13
677 Views
Last Modified: 2015-01-15
Hello Experts,

I am unable to connect to Oracle 11 g Release database.
Error: ORA-12543: TNS:destination host unreachable

Config:
* host A and host B are included in /etc/hosts
* firewall disabled on both servers
* At host A (Oracle server): tnsping successful, lsnrctl is up, and I am able to connect to database ok
* At host B (Oracle client): installed all dependencies, basiclite, sqlplus and odbc. Also, set $ORACLE_HOME, $ORACLE_SID, $ORACLE_BASE, $PATH, $LD_LIBRARY_PATH, and TNS_ADMIN.
* Both hosts tnsnames.ora have IP addresses instead of host names

I am not sure what I am missing.. but still unable to connect using either:
1. sqlplus "/ as sysdba"
2. sqlplus user/password@ORACLEDB
3. sqlplus user/password@192.168.1.120:1521/ORACLEDB
4. sqlplus user/password@192.168.1.120/ORACLEDB

any suggestions what else I can update and try?

In advance, thank you for your help.

Also, Server and client are the same versions
0
Comment
Question by:epifanio67
13 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40550257
I'm assuming that the error is from hostB?

From hostA:
ipconfig /all

From hostB:
ipconfig /all
ping 192.168.1.120
tnsping ORACLEDB
tracert 192.168.1.120
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40550260
oops.  Looks like you are on Unix.  I think ipconfig is Windows.  

Do whatever command shows the currently configured NICs and their IP address and related info.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40550758
oops.  Looks like you are on Unix.  I think ipconfig is Windows.  
Then use ifconfig on *nix system.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40551008
tnsping isn't going to work either.  Based on the original post, it would appear that instant client was installed.  As far as I know, tnsping isn't available with instant client.  I believe there is a way to copy it from a full client and make it work, but I don't see it as part of the install on my machine.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40551127
I don't recognize: "basiclite".  I usually install the full Oracle client, because then i knoiw that all of the typical Oracle networking utilities are included.  Do you have "tnsping.exe" in the $ORACLE_HOME/bin folder on the client host (host B)?

If yes, try that with the IP address of host A, like this:

tnsping 192.168.1.120

That should return a three-line response with "OK" on the last line.

If you don't have that utility in your $ORACLE_HOME/bin folder, try installing the full Oracle client.

If you do have that utility, but it returns an error, then something is configured wrong either in the tnsnames.ora file on host B, or possbily in the listener.ora file on host A.

Usually, this is the most difficult part of an Oracle system install: getting the SQL*Net configuration files set up correctly.  After these are correct, the rest of the system usually works fine.
0
 

Author Comment

by:epifanio67
ID: 40552350
Thank you so much for your help...

Here is what I did:
I installed basic oracle client and sqlplus successfully.

but I am still unable to connect from client (.117) to server (.112)

see configuration below:
//////////////////////////////////////////////////
Host A (192.168.1.112)- SERVER:
/////////////////////////////////////////////////
//******************** listener.ora ********************************************//
GENESYSDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.112)(PORT = 6060))
  )

SID_LIST_GENESYSDB =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.1/db_1)
      (SID_NAME = orcl)
    )
  )

ADR_BASE_GENESYS = /u01/app/oracle

ADR_BASE_GENESYSDB = /u01/app/oracle

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.112)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle


//******************* tnsnames.ora ********************************************//
GENESYSDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.112)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = genesysdb)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.112)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
  
//******************* sqlnet.ora  ********************************************//
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT, HOSTNAME)

ADR_BASE = /u01/app/oracle

//****************** lsnrctl status ********************************************//
LSNRCTL> status genesysdb
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.112)(PORT=6060)))
STATUS of the LISTENER
------------------------
Alias                     genesysdb
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                15-JAN-2015 14:42:11
Uptime                    0 days 0 hr. 9 min. 59 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.1/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/voracle/genesysdb/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.112)(PORT=6060)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

//****************** tnsping results ********************************************//
Used parameter files:
/u01/app/oracle/product/11.2.1/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.112)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = genesysdb)))
OK (0 msec)
OK (0 msec)
OK (10 msec)
OK (0 msec)

//////////////////////////////////////////////////
Host B (192.168.1.117) - CLIENT
/////////////////////////////////////////////////
//************************* listener.ora *****************************//
GENESYSDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.112)(PORT = 6060))
  )

ADR_BASE_GENESYSDB = /u01/app/oracle

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.112)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

//************************* tnsnames.ora *****************************//
GENESYSDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.112)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = genesysdb)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.112)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

//********************** sqlnet.ora *********************************//
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT, HOSTNAME)

ADR_BASE = $ORACLE_BASE

Open in new window


I appreciate any help on this...

no firewal....

Regards,
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40552437
>>I installed basic oracle client and sqlplus successfully.

This does sound like the Instant Client.  So no tnsping.

Where is the ping from the .117 client to the .112 server?
Where is the ifconfig output?
Where is the tracert?
0
 

Author Comment

by:epifanio67
ID: 40552534
thanks slightwv....

quick question before I run those commands...

Should the oracle client be the same version that the database server?

//******* Server:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


//*********** client:
[genesys@confserv instantclient_11_2]$ cat SQLPLUS_README
SQLPLUS Package Information
===========================

Sun Aug 25 11:18:32 GMT 2013

Client Shared Library 32-bit - 11.2.0.4.0

System name:    Linux
Release:        2.6.9-89.0.0.0.1.ELxenU
Version:        #1 SMP Tue May 19 04:48:26 EDT 2009
Machine:        i686

Open in new window


thank you for your help..
0
 

Author Comment

by:epifanio67
ID: 40552564
below output requested:
[genesys@confserv instantclient_11_2]$ /sbin/ifconfig -a
eth0      Link encap:Ethernet  HWaddr 00:0C:29:98:46:5D
          inet addr:192.168.1.117  Bcast:192.168.1.255  Mask:255.255.255.0
          inet6 addr: fe80::20c:29ff:fe98:465d/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:865239 errors:39 dropped:25 overruns:0 frame:0
          TX packets:266349 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:990152190 (944.2 MiB)  TX bytes:20121142 (19.1 MiB)
          Interrupt:59 Base address:0x2000

lo        Link encap:Local Loopback
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:5871 errors:0 dropped:0 overruns:0 frame:0
          TX packets:5871 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:9896509 (9.4 MiB)  TX bytes:9896509 (9.4 MiB)

sit0      Link encap:IPv6-in-IPv4
          NOARP  MTU:1480  Metric:1
          RX packets:0 errors:0 dropped:0 overruns:0 frame:0
          TX packets:0 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:0 (0.0 b)  TX bytes:0 (0.0 b)

[genesys@confserv instantclient_11_2]$ tracert 192.168.1.112
The specified type of tracerouting is allowed for superuser only
[genesys@confserv instantclient_11_2]$ su
Password:
[root@confserv instantclient_11_2]# tracert 192.168.1.112
traceroute to 192.168.1.112 (192.168.1.112), 30 hops max, 40 byte packets
 1  voracle (192.168.1.112)  0.252 ms  0.270 ms  0.294 ms

Open in new window

0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40552630
Just for grins, from the client:
telnet 192.168.1.112 1521

It will either connect or not.  Either way, there isn't anything else to 'test' with this.  Jsut let us know if it connected or not.

>>Should the oracle client be the same version that the database server?

No.  Unless the versions are MANY versions apart, they all talk to one another.

>>sqlplus user/password@ORACLEDB

From the original question can I assume that ORACLEDB was replaced by GENESYSDB?

Tripple check TNS_ADMIN is pointing to the location of the tnsnames.ora file.

Personally, I never use that.  With the instant client, I manually create a network\admin folder under the instant client folder and place the tnsnames.ora and sqlnet.ora file in there.  That is the 'default' location for them.


Is this in a virtual environment?
0
 

Author Comment

by:epifanio67
ID: 40552668
Thank you so much..

I hate when I take things for granted
[root@confserv instantclient_11_2]# telnet 192.168.1.112 1521
Trying 192.168.1.112...
telnet: connect to address 192.168.1.112: No route to host
telnet: Unable to connect to remote host: No route to host

Open in new window


Regards,
0
 

Author Closing Comment

by:epifanio67
ID: 40552669
Thank you so much for your help...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40552678
Glad to help!

Process of elimination...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now