Home » RDBMS Server » Networking and Gateways » connect using tnsname
connect using tnsname [message #284900] Sun, 02 December 2007 14:52 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
my tnsname works when i tested through tnsping.
but when using in sqlplus its not resolving properly.
my sqlnet.ora is empty.

oracle@kitcomp bin]$ tnsping prod

TNS Ping Utility for Linux: Version 10.1.0.3.0 - Production on 03-DEC-2007 02:16:00

Copyright (c) 1997, 2003, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/OraHome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = kitcomp.tapasi.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PROD)))
OK (0 msec)
[oracle@kitcomp bin]$ sqlplus "scott/tiger@prod"

SQL*Plus: Release 10.1.0.3.0 - Production on Mon Dec 3 02:16:18 2007

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor



Can someone please advice ?
Re: connect using tnsname [message #284901 is a reply to message #284900] Sun, 02 December 2007 14:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
what does the following show:
12:54:44 SQL> column owner            format a12
12:54:44 SQL> column db_link          format a32
12:54:44 SQL> column user_name        format a12
12:54:44 SQL> column host             format a12
12:54:44 SQL> set lines 131
12:54:44 SQL> select * from dba_db_links
12:54:44   2  /
Re: connect using tnsname [message #284902 is a reply to message #284901] Sun, 02 December 2007 14:59 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
SQL> select * from dba_db_links;

no rows selected
Re: connect using tnsname [message #284904 is a reply to message #284900] Sun, 02 December 2007 15:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
you must CREATE DATABASE LINK... before @PROD will work from SQL*Plus
Re: connect using tnsname [message #284923 is a reply to message #284900] Sun, 02 December 2007 22:40 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

ORA-12514

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Cause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.
Action: - Wait a moment and try to connect a second time.

- Check which services are currently known by the listener by executing: lsnrctl services <listener name>

- Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.

- If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.

- Check for an event in the listener.log file.

>my sqlnet.ora is empty.
And you must recreate your sqlnet.ora file from NETCA tools.


[Updated on: Sun, 02 December 2007 22:54]

Report message to a moderator

Re: connect using tnsname [message #284928 is a reply to message #284900] Sun, 02 December 2007 22:53 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:

you must CREATE DATABASE LINK... before @PROD will work from SQL*Plus


I think it is not true.

Only to connect to other database tnsnames entry is enough. No need database link. Only to access data from other database database link is required.
Re: connect using tnsname [message #284940 is a reply to message #284900] Sun, 02 December 2007 23:28 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

To the OP, invoke lsnrctl status in the database to which you connect and paste the output here , also you need to paste the tnsnames.ora entry of the client machine.
Re: connect using tnsname [message #285126 is a reply to message #284900] Mon, 03 December 2007 11:55 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
i am doing this test in the same machine itself.

my listener status is as follows
[oracle@kitcomp bin]$ ./lsnrctl stat

LSNRCTL for Linux: Version 10.1.0.3.0 - Production on 03-DEC-2007 23:24:41

Copyright (c) 1991, 2004, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=kitcomp.tapasi.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.1.0.3.0 - Production
Start Date                03-DEC-2007 23:16:12
Uptime                    0 days 0 hr. 8 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/OraHome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/OraHome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=kitcomp.tapasi.com)(PORT=1521)))
Services Summary...
Service "PROD.KITCOMP.TAPASI.COM" has 2 instance(s).
  Instance "PROD", status UNKNOWN, has 1 handler(s) for this service...
  Instance "prod", status READY, has 1 handler(s) for this service...
Service "prodXDB.kitcomp.tapasi.com" has 1 instance(s).
  Instance "prod", status READY, has 1 handler(s) for this service...
The command completed successfully




tnsnames.ora

PROD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = kitcomp.tapasi.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PROD)
    )
  )
Re: connect using tnsname [message #285127 is a reply to message #284900] Mon, 03 December 2007 11:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please post the contents of sqlnet.ora file if you have one.
Re: connect using tnsname [message #285129 is a reply to message #284900] Mon, 03 December 2007 12:10 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
tnsnames.ora and listener.ora are the one i created manualy.

as i mentioned in first post ,i dont have any contents in sqlnet.ora
Re: connect using tnsname [message #285134 is a reply to message #284900] Mon, 03 December 2007 12:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
gkrishn,
Sorry about that.
I'm not paying close enough attention to this thread.

What, if anything, appears in sqlnet.log file for the period of time around when your connection attempt fails?

Part of me wonders if case sensitivity difference between PROD & prod is impacting your results.

[Updated on: Mon, 03 December 2007 12:24] by Moderator

Report message to a moderator

Re: connect using tnsname [message #285137 is a reply to message #284900] Mon, 03 December 2007 12:37 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
though its in linux, SERVICE NAMES are not case sensitive .

./tnsping PROD and
./tnsping prod give OK result .
Re: connect using tnsname [message #285196 is a reply to message #284900] Mon, 03 December 2007 22:08 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:

(SERVICE_NAME = PROD)


I don't see service name PROD.......

There is prodXDB.kitcomp.tapasi.com
Re: connect using tnsname [message #285197 is a reply to message #284900] Mon, 03 December 2007 22:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
While logged onto the database server system post the results of the following command.
ps -ef | grep -i pmon

Re: connect using tnsname [message #285203 is a reply to message #285126] Mon, 03 December 2007 22:42 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hello,
Did you try after recreate sqlnet.ora file as I said previously.
>And you must recreate your sqlnet.ora file from NETCA tools.

Previous Topic: ora-12154
Next Topic: newbie: database link not active
Goto Forum:
  


Current Time: Fri Mar 29 05:18:07 CDT 2024