| usage of hostname in tnsnames.ora and resolving the same with hosts file on linux platform. [message #538288] |
Thu, 05 January 2012 03:08  |
 |
snehalgandhi
Messages: 43
Registered: February 2011
Location: Ahmedabad
|
Member |
|
|
I am writing hostname in the tnsnames.ora file instead of IP address, also the host file of the server contains proper entry, but still i am not able to connect to database. Can any one guide on the same ?
Entries of tnsnames.ora
=======================
SUNPROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdb)(PORT = 1521))
)
(CONNECT_DATA =
(SID = oradb)
)
)
Entries in hosts file on linux
==============================
192.138.2.66 testdb
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: usage of hostname in tnsnames.ora and resolving the same with hosts file on linux platform. [message #538295 is a reply to message #538294] |
Thu, 05 January 2012 03:33  |
|
snehalgandhi
Messages: 43
Registered: February 2011
Location: Ahmedabad
|
Member |
|
|
Ping Output
=================
C:\Documents and Settings\devlop>ping testdb
Pinging testdb [191.9.200.6] with 32 bytes of data:
Reply from 191.9.200.6: bytes=32 time<10ms TTL=128
Reply from 191.9.200.6: bytes=32 time<10ms TTL=128
Reply from 191.9.200.6: bytes=32 time<10ms TTL=128
Reply from 191.9.200.6: bytes=32 time<10ms TTL=128
Ping statistics for 191.9.200.6:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms
tnsping output
==============
C:\Documents and Settings\devlop>ping testdb
Pinging testdb [191.9.200.6] with 32 bytes of data:
Reply from 191.9.200.6: bytes=32 time<10ms TTL=128
Reply from 191.9.200.6: bytes=32 time<10ms TTL=128
Reply from 191.9.200.6: bytes=32 time<10ms TTL=128
Reply from 191.9.200.6: bytes=32 time<10ms TTL=128
Ping statistics for 191.9.200.6:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms
===================================================
Note: Please ignore the ip address....
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: usage of hostname in tnsnames.ora and resolving the same with hosts file on linux platform. [message #538362 is a reply to message #538315] |
Thu, 05 January 2012 09:12 |
 |
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
John Watson wrote on Thu, 05 January 2012 15:49Quote:conn prod/prod@sunprod; Is the semicolon causing your problem? It shouldn;t be there.
It`s not an issue all the time....
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jan 5 20:30:34 2012
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
dev> conn sriram/sriram@ind;
Connected.
ind>
@OP
Quote:C:\Documents and Settings\devlop>tnsping sunprod
TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 05-JAN-20
12 16:16:58
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
E:\oracle\ora92\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = payroll)(PORT = 1521))) (CONNECT_DATA = (SID = ashima)))
TNS-12535: TNS:operation timed out
Quote:C:\Documents and Settings\devlop>tnsping sunprod
TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 05-JAN-20
12 16:09:15
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
E:\oracle\ora92\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = testdb)(PORT = 1521))) (CONNECT_DATA = (SID = oradb)))
OK (30 msec)
C:\Documents and Settings\devlop>sqlplus
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jan 5 16:09:25 2012
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: prod@sunprod
Enter password:
ERROR:
ORA-12505: TNS:listener could not resolve SID given in connect descriptor
Enter user-name:
Two different hosts and two different errors.
On both servers ....
1) Any firewall installed ?
2) Does your server allow you on 1521 ?
3) Are you able to connect to DB on server rather than client ?
Sriram Sanka
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: usage of hostname in tnsnames.ora and resolving the same with hosts file on linux platform. [message #538431 is a reply to message #538430] |
Thu, 05 January 2012 23:29 |
|
snehalgandhi
Messages: 43
Registered: February 2011
Location: Ahmedabad
|
Member |
|
|
Dear BlankSwan
output of lsnrctl status
=========================
[oracle@payroll etc]$ lsnrctl status listener1
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 06-JAN-2012 10:50:30
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC2)))
STATUS of the LISTENER
------------------------
Alias LISTENER1
Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date 30-NOV-2011 11:18:22
Uptime 36 days 23 hr. 32 min. 8 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/oracle/product/10.2.0.1/network/admin/listener.ora
Listener Log File /opt/oracle/product/10.2.0.1/network/log/listener1.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC2)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=191.9.200.14)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "ashima" has 2 instance(s).
Instance "ashima", status UNKNOWN, has 1 handler(s) for this service...
Instance "ashima", status READY, has 1 handler(s) for this service...
Service "ashimaXDB" has 1 instance(s).
Instance "ashima", status READY, has 1 handler(s) for this service...
Service "ashima_XPT" has 1 instance(s).
Instance "ashima", status READY, has 1 handler(s) for this service...
Service "workpyrl" has 2 instance(s).
Instance "workpyrl", status UNKNOWN, has 1 handler(s) for this service...
Instance "workpyrl", status READY, has 1 handler(s) for this service...
Service "workpyrlXDB" has 1 instance(s).
Instance "workpyrl", status READY, has 1 handler(s) for this service...
Service "workpyrl_XPT" has 1 instance(s).
Instance "workpyrl", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@payroll etc]$
output of lsnrctl service
========================
[oracle@payroll etc]$ lsnrctl service listener1
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 06-JAN-2012 10:52:53
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC2)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "ashima" has 2 instance(s).
Instance "ashima", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:44251 refused:0
LOCAL SERVER
Instance "ashima", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1651 refused:0 state:ready
LOCAL SERVER
Service "ashimaXDB" has 1 instance(s).
Instance "ashima", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: payroll, pid: 7799>
(ADDRESS=(PROTOCOL=tcp)(HOST=payroll)(PORT=45850))
Service "ashima_XPT" has 1 instance(s).
Instance "ashima", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1651 refused:0 state:ready
LOCAL SERVER
Service "workpyrl" has 2 instance(s).
Instance "workpyrl", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:7461 refused:0
LOCAL SERVER
Instance "workpyrl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:231 refused:0 state:ready
LOCAL SERVER
Service "workpyrlXDB" has 1 instance(s).
Instance "workpyrl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: payroll, pid: 7859>
(ADDRESS=(PROTOCOL=tcp)(HOST=payroll)(PORT=38451))
Service "workpyrl_XPT" has 1 instance(s).
Instance "workpyrl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:231 refused:0 state:ready
LOCAL SERVER
The command completed successfully
[oracle@payroll etc]$
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
