Oracle 11g Release 2: unable to connect via sqlplus

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
epifanio67Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Alexander Eßer [Alex140181]Software DeveloperCommented:
oops.  Looks like you are on Unix.  I think ipconfig is Windows.  
Then use ifconfig on *nix system.
0
 
johnsoneSenior Oracle DBACommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
epifanio67Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
epifanio67Author Commented:
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
 
epifanio67Author Commented:
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
 
epifanio67Author Commented:
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
 
epifanio67Author Commented:
Thank you so much for your help...
0
 
slightwv (䄆 Netminder) Commented:
Glad to help!

Process of elimination...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.