Skip to Main Content
  • Questions
  • Locatin of listener.ora and tnsnames.ora files

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Ganesh.

Asked: August 28, 2001 - 10:24 pm UTC

Answered by: Tom Kyte - Last updated: June 01, 2012 - 6:44 am UTC

Category: Database - Version: 7.3

Viewed 100K+ times! This question is

You Asked

Hi Tom,

As far as my knowledge goes I understand in Solaris filesystem 'listener.ora' and 'tnsnames.ora' files are in /var/opt/oracle/
area and for HP and Digital machines these files are found in /etc/
area by default .Is there anyway in which I can keep these files to some other area in the filesystem.If Yes, then do let me know.


Thank you Tom .

and we said...

You can use the TNS_ADMIN environment variable to put them where ever you want

$ORACLE_HOME/network/admin

works as well -- you can put them there instead.

and you rated our response

  (95 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

multiple homes

August 29, 2001 - 10:03 am UTC

Reviewer: Randy from Texas

What is your suggestion for the location when using multiple homes? Should the tns files be specific to a home (for compatiblity)?

Tom Kyte

Followup  

August 29, 2001 - 10:13 am UTC

It depends on your needs.

If you are using ONE listener for all databases (you can use the listener from 817 to service and 815 database for example), you would put them in one place.

If you are running a listener/server - you might want to put your listener.ora into the $ORACLE_HOME/network/admin directory for each oracle_home just to keep them separate.

As for the tnsnames.ora file, i generally find it easiest to use one on a machine instead of one per server as they typically all end up with the same list of servers.


Unix Servers

August 29, 2001 - 12:04 pm UTC

Reviewer: A reader

On most unix servers, if you have a common listener.ora file for multiple databases, the most common place is in /etc directory.

August 29, 2001 - 10:20 pm UTC

Reviewer: Tianhua Wu from Atlanta, GA

use truss(unix) or strace(linux) on "tnsping service_name", you will see Oracle check several places.

multiple oracle home and one listener

August 15, 2004 - 4:03 pm UTC

Reviewer: suhail from NY USA

Hello Tom,

I have Oracle 9i and Oracle 10G running on a windows2000 machine. My 9i oracle home points to c:\9idb2 and 10G to c:\10gdb. I have added the 10G listener to 9idb oracle home and here is how my listener.ora file looks like.

# LISTENER.ORA Network Configuration File: c:\9idb2\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = sumbul)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = c:\9idb2)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = taha)
      (ORACLE_HOME = c:\9idb2)
      (SID_NAME = taha)
    )
  )

SID_LIST_LISTENER_10G =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = c:\10gDB)
      (PROGRAM = extproc)
    )
  )

LISTENER_10G =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = SUMBUL)(PORT = 1525))
      )
    )
  )

When I checked the status of listener, both the listeners are running :

C:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 15-AUG-2004 15:42
:04

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Produc
tion
Start Date                15-AUG-2004 13:46:06
Uptime                    0 days 1 hr. 55 min. 59 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   c:\9idb2\network\admin\listener.ora
Listener Log File         c:\9idb2\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sumbul)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sumbul)(PORT=8080))(Presentation=HTT
P)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sumbul)(PORT=2100))(Presentation=FTP
)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sumbul)(PORT=8080))(Presentation=HTT
P)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sumbul)(PORT=2100))(Presentation=FTP
)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orc10g" has 1 instance(s).
  Instance "orc10g", status READY, has 1 handler(s) for this service...
Service "orc10gXDB" has 1 instance(s).
  Instance "orc10g", status READY, has 1 handler(s) for this service...
Service "taha" has 2 instance(s).
  Instance "taha", status UNKNOWN, has 1 handler(s) for this service...
  Instance "taha", status READY, has 1 handler(s) for this service...
Service "tahaXDB" has 1 instance(s).
  Instance "taha", status READY, has 1 handler(s) for this service...
The command completed successfully


My problem is I can connect to 9idb service but when I try to connect to 10G database I am getting following error:

SQL> conn scott/tiger@ORC10G  -- 10g
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


SQL> conn scott/tiger@taha       --9i
Connected.
SQL> 

My tnsnames.ora at both oracle home looks like as follows:

--9i tnsnames.ora file-------
# tnsnames.ora Network Configuration File: c:\9idb2\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORC10G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = SUMBUL)(PORT = 1525))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orc10g)
    )
  )

TAHA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sumbul)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = taha)
    )
  )

INST1_HTTP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sumbul)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = MODOSE)
      (PRESENTATION = 
http://HRService
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )


---10G tnsnames.ora fle ------------------

# tnsnames.ora Network Configuration File: c:\10gDB\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORC10G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = SUMBUL)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orc10g)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

TAHA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sumbul)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = taha)
    )
  )

Any idea what I am doing wrong? TNS_ADMIN is also set to c:\9idb2\network\admin

Thanks for your help

Suhail
 

Tom Kyte

Followup  

August 16, 2004 - 8:18 am UTC

?? I have added the 10G listener to 9idb oracle home ??? huh?


you would


a) kill the 9i listener

b) configure the 10g listener in the 10g home

c) have the databases register with the 10g listener (change the 9i configuration of the database to register with 10g, not 9i)

Still an't connect to 10G

August 16, 2004 - 8:09 pm UTC

Reviewer: Suhail from NY

Tom,

I killed the 9i listener and moved the listener entry of 9i to 10G listener, my TNS_ADMIN is set to 10g $oracle_home\network\admin, but when I am trying to connect to 10G , I am getting "no listener" error. I canconnect to 9iDb with no problem. 

SQL> conn scott/tiger@orc10g
ERROR:
ORA-12541: TNS:no listener

Here is the status of listener

C:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 16-AUG-2004 19:51
:42

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   32-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sumbul)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Produ
ction
Start Date                16-AUG-2004 19:50:24
Uptime                    0 days 0 hr. 1 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   c:\10gdb\network\admin\listener.ora
Listener Log File         c:\10gDB\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sumbul)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sumbul)(PORT=8080))(Presentation=HTT
P)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sumbul)(PORT=2100))(Presentation=FTP
)(Session=RAW))
Services Summary...
Service "orc10g" has 1 instance(s).
  Instance "orc10g", status READY, has 1 handler(s) for this service...
Service "orc10gXDB" has 1 instance(s).
  Instance "orc10g", status READY, has 1 handler(s) for this service...
Service "taha" has 1 instance(s).
  Instance "taha", status READY, has 1 handler(s) for this service...
Service "tahaXDB" has 1 instance(s).
  Instance "taha", status READY, has 1 handler(s) for this service...
The command completed successfully

My listner.ora at $oracle_home\network\admin looks like as follws:

# listener.ora Network Configuration File: c:\10gDB\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER_10g =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = SUMBUL)(PORT = 1525))
      )
    )
  )

SID_LIST_LISTENER_10g =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = c:\10gDB)
      (PROGRAM = extproc)
    )
  )

LISTENER_9i =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = sumbul)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER_9i =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = c:\9idb2)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = taha)
      (ORACLE_HOME = c:\9idb2)
      (SID_NAME = taha)
    )
  )

What I am doing wrong?

Thanks

Suhail 

Tom Kyte

Followup  

August 16, 2004 - 8:16 pm UTC

do a tnsping, see what orc10g is resolving to -- looks like it might not be the listener you have running.

(that is, is your tnsnames.ora file setup properly here)

here is what you requested

August 16, 2004 - 8:58 pm UTC

Reviewer: Suhail from NY

Here is tnsping info for both instance

C:\>tnsping orc10g

TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 16-AUG-2
004 20:41:44

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

Used parameter files:
c:\10gdb\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = SUMBUL)(PORT = 1525))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAM
E = orc10g)))
TNS-12541: TNS:no listener

C:\>tnsping taha

TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 16-AUG-2
004 20:43:29

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

Used parameter files:
c:\10gdb\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = sumbul)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAM
E = taha)))
OK (40 msec)

C:\>


One more thing , when I tried to register 10G databse using DBCA, I selected Cofigure databse option then ORC10G from the list then I got following error

ORA-00119:invalid specification for system parameter LOCAL_LISTNER
ORA-00132:syntax error or unresolved network name 'LISTENER_ORC10G'

and I could not continue DB registration. My tnsnames.ora looks like as below ( at 10g home)

ORC10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SUMBUL)(PORT = 1525))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orc10g)
)
)

TAHA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sumbul)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = taha)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)



Tom Kyte

Followup  

August 17, 2004 - 7:16 am UTC

you only have a 1521 listener

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sumbul)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sumbul)(PORT=8080))(Presentation=HTT
P)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sumbul)(PORT=2100))(Presentation=FTP
)(Session=RAW))


correct that. seems you didn't start the 10g one.

listener 10G is not running

August 17, 2004 - 8:52 am UTC

Reviewer: Suhail

I have started the 10G listener from command line and I can see its running in the windows service, however when I do lsnrctl status , its still not showing me 

C:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 17-AUG-2004 08:37
:41

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Produc
tion
Start Date                17-AUG-2004 08:16:45
Uptime                    0 days 0 hr. 20 min. 57 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   c:\10gdb\network\admin\listener.ora
Listener Log File         c:\9idb2\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sumbul)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sumbul)(PORT=8080))(Presentation=HTT
P)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sumbul)(PORT=2100))(Presentation=FTP
)(Session=RAW))
Services Summary...
Service "taha" has 1 instance(s).
  Instance "taha", status READY, has 1 handler(s) for this service...
Service "tahaXDB" has 1 instance(s).
  Instance "taha", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\>

and I am getting the same error from where I started ie

SQL> conn scott/tiger@orc10g
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Warning: You are no longer connected to ORACLE.
SQL> 


Tnsping seems to be working for orc10g instance

C:\>tnsping orc10g

TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 17-AUG-2
004 08:40:04

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

Used parameter files:
c:\10gdb\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = SUMBUL)(PORT = 1525))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAM
E = orc10g)))
OK (40 msec)

C:\>

Thanks

Suhail 

Tom Kyte

Followup  

August 17, 2004 - 9:00 am UTC

lsnrctl status LISTENER_NAME

I have deleted 9idb still same problem

August 17, 2004 - 10:03 pm UTC

Reviewer: Suhail from NY

I have completly deleted 9idb from the hard disk and cleaned my registry. I just have 10G and am still getting the same problem.

Here is the status of listener

LSNRCTL> status listener
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SUMBUL)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Produ
ction
Start Date 17-AUG-2004 21:37:47
Uptime 0 days 0 hr. 11 min. 16 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File c:\10gDB\network\admin\listener.ora
Listener Log File c:\10gDB\network\log\listener.log
(ENDPOINT=(HANDLER=(HANDLER_MAXLOAD=0)(HANDLER_LOAD=0)(ESTABLISHED=0)(REFUSED=0)
(HANDLER_ID=FCA0739423B8-4A19-80E2-181A5567936C)(PRE=any)(SESSION=NS)(DESCRIPTIO
N=(ADDRESS=(PROTOCOL=tcp)(HOST=sumbul)(PORT=1521)))))
(ENDPOINT=)
(SERVICE=(SERVICE_NAME=PLSExtProc)(INSTANCE=(INSTANCE_NAME=PLSExtProc)(NUM=1)(IN
STANCE_STATUS=UNKNOWN)(NUMREL=1)))
The command completed successfully
LSNRCTL>


Why am I getting TNS-12541: TNS:no listener error. My windows service is running, I cannot connect to orc10g and getting the same error:

C:\Documents and Settings\sami>sqlplus scott/tiger@orc10g

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Aug 17 21:39:19 2004

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

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


Enter user-name:


My listener.ora looks like as below:

# listener.ora Network Configuration File: c:\10gDB\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = c:\10gDB)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SUMBUL)(PORT = 1521))
)
)
)


My tnsnames.ora looks like as below:

# tnsnames.ora Network Configuration File: c:\10gdb\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORC10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sumbul)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orc10g)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)



Why 10G is not recognizing listener?

Thanks for your help.




Tom Kyte

Followup  

August 18, 2004 - 7:49 am UTC

it is recognizing the listener, now that you fixed the ports up (using 1521 in both places, not 1525)

however, the SERVICE you are requesting -- "orc10g" is not known:

[tkyte@xtkyte-pc tkyte]$ oerr ora 12514
12514, 00000, "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.
[tkyte@xtkyte-pc tkyte]$


Basically, you want to do a lsnrctl services - see what your database is registering itself as and make sure to use that service name (just like in 9i, 8i, and so on -- nothing changed here)

database is not registering the service

August 18, 2004 - 7:57 am UTC

Reviewer: Suhail

Here is my lsnrcl services:

C:\>lsnrctl

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 18-AUG-2004 07:43
:33

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set displaymode raw
Service display mode is RAW
LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SUMBUL)(PORT=1521)))
Services Summary...
(SERVICE=(SERVICE_NAME=PLSExtProc)(INSTANCE=(INSTANCE_NAME=PLSExtProc)(NUM=1)(IN
STANCE_STATUS=UNKNOWN)(HANDLER=(HANDLER_DISPLAY=DEDICATED SERVER)(HANDLER_INFO=L
OCAL SERVER)(HANDLER_MAXLOAD=0)(HANDLER_LOAD=0)(ESTABLISHED=0)(REFUSED=0)(HANDLE
R_ID=4070F323469F-4A1C-B29C-E3C17111C7B4)(PRE=any)(HANDLER_NAME=DEDICATED)(SESSI
ON=NS)(ADDRESS=(PROTOCOL=beq)(PROGRAM=extproc)(ENVS='ORACLE_HOME=c:\10gDB,ORACLE
_SID=PLSExtProc')(ARGV0=extprocPLSExtProc)(ARGS='(LOCAL=NO)')))(NUMREL=1)))
The command completed successfully
LSNRCTL>

I can see in my windows services both listener and orc10g are running.

Tom Kyte

Followup  

August 18, 2004 - 9:39 am UTC

you have no orc10g service. your database registers with a listener, perhaps that database isn't even up.

why raw? just makes it slightly harder for us humans to read.

August 18, 2004 - 8:32 am UTC

Reviewer: Dave

you have no service called orc10g in the listener

Tom Kyte

Followup  

August 18, 2004 - 9:45 am UTC

you don't need one, the database registers with the listener using the information in the local_listener init.ora parameter (which defaults to the current host, port 1521 if not set)

sys@ORA9IR2> show parameter service_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string ora9ir2
sys@ORA9IR2> show parameter local_list

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string

sys@ORA9IR2> shutdown

so, db is down, listener.ora is:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xtkyte-pc.us.oracle.com)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/ora9ir2)
(PROGRAM = extproc)
)
)




[ora9ir2@xtkyte-pc admin]$ lsnrctl services

LSNRCTL for Linux: Version 9.2.0.5.0 - Production on 18-AUG-2004 09:34:28

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
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
The command completed successfully


shows there is an extproc service, just like theirs... now startup:


[ora9ir2@xtkyte-pc admin]$ startup

SQL*Plus: Release 9.2.0.5.0 - Production on Wed Aug 18 09:34:53 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

idle> ORACLE instance started.

Total System Global Area 168891480 bytes
Fixed Size 451672 bytes
Variable Size 100663296 bytes
Database Buffers 67108864 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
idle> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

and we'll see that the database automagically registers itself:

[ora9ir2@xtkyte-pc admin]$ !ls
lsnrctl services

LSNRCTL for Linux: Version 9.2.0.5.0 - Production on 18-AUG-2004 09:35:00

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
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 "ora9ir2" has 1 instance(s).
Instance "ora9ir2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "ora9ir2XDB" has 1 instance(s).
Instance "ora9ir2", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER <machine: xtkyte-pc.us.oracle.com, pid: 5274>
(ADDRESS=(PROTOCOL=tcp)(HOST=xtkyte-pc.us.oracle.com)(PORT=33678))
The command completed successfully
[ora9ir2@xtkyte-pc admin]$



Thanks

August 18, 2004 - 9:06 am UTC

Reviewer: Suhail

Thanks Dave for pointing me the problem, I need to add this

(SID_DESC =
(GLOBAL_DBNAME = win_db)
(ORACLE_HOME = c:\10gdb)
(SID_NAME = windb)
)


Thanks

Tom Kyte

Followup  

August 18, 2004 - 9:47 am UTC

no, you don't and you don't really want to. dynamic service registration is easier.

whats is dynamic service registration

August 18, 2004 - 9:59 am UTC

Reviewer: Suhail

So what is this dynamic service registration? Are you saying that I should not add following entry in listener.ora

(SID_DESC =
(GLOBAL_DBNAME = orc10g)
(ORACLE_HOME = c:\10gdb)
(SID_NAME = orc10g)
)

Could you shed some light on Dynamic Service Registration, what is this about?

Tom Kyte

Followup  

August 18, 2004 - 10:19 am UTC

it is what I just demonstrated above?


database down = no service in the listener
database up = service in the listener

init.ora parameters were listed above.


</code> http://docs.oracle.com/docs/cd/B10501_01/network.920/a96580/listener.htm#490265 <code>

Good to know

August 18, 2004 - 10:34 am UTC

Reviewer: Suhail

I am sorry, somehow my page did not refersh and I could not see your earlier message. Now I know and its working fine. I learned something new today.

Thanks

I still have the problem

August 18, 2004 - 8:01 pm UTC

Reviewer: Suhail

Here, what I did :

C:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 18-AUG-2004 19:42
:39

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SUMBUL)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Produ
ction
Start Date                18-AUG-2004 18:58:35
Uptime                    0 days 0 hr. 44 min. 6 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   c:\10gDB\network\admin\listener.ora
Listener Log File         c:\10gDB\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sumbul)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

C:\>net start OracleServiceorc10g
The OracleServiceORC10G service is starting.
The OracleServiceORC10G service was started successfully.


C:\>sqlplus "sys/miu55sw as sysdba"

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Aug 18 19:43:15 2004

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

Connected to an idle instance.

SQL> show parameter service_name
ORA-01034: ORACLE not available
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_ORC10G'
SQL>exit

C:\>lsnrctl services

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 18-AUG-2004 19:47
:22

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SUMBUL)(PORT=1521)))
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
The command completed successfully


I cannot start the database somehow? keep coming this error "ORA-00132: syntax error or unresolved network name 'LISTENER_ORC10G' "

Amy idea what should I do now?

Thanks

 

Tom Kyte

Followup  

August 18, 2004 - 11:43 pm UTC

you set the parameter wrong.

since you are using the default server/port, local_listener need not be set, but if you want to set it -- see the link above? it is to the documentation that describes this feature in detail.

How can I get the tnsnames.ora entries?

September 12, 2004 - 4:31 am UTC

Reviewer: Thaha Hussain from Manama, Bahrain

Toad is diplaying tnsnames.ora entries (connect strings) when it starts up, in a drop down list box (without connecting to the database).

I can search the Oracle Home\network\admin\tnsnames.ora and pick those entries by writing a parsing program. Is there any easy way?

Thanks
Thaha Hussain

Tom Kyte

Followup  

September 12, 2004 - 11:21 am UTC

that is what toad did, but it is really pretty easy to do

$ grep '^[A-Za-z]' /home/ora9ir2/network/admin/tnsnames.ora | sed 's/=.*//;s/,.*//'
EXTPROC_CONNECTION_DATA.US.ORACLE.COM
ORA9IR2.US.ORACLE.COM
INST1_HTTP.US.ORACLE.COM
ora920
ORA9IR1.LOCALDOMAIN


Under unix does that for example -- the tns entry must be in column 1, nothing else but # is allow to be in column 1, so just keep lines with something in colum 1 that is not a #, remove anything after the "=" and or ","

November 18, 2004 - 10:28 am UTC

Reviewer: Nishant from India

Sir,

I have 8i and 9i database running on a single machine.
when i connect to 9i on console it gives me "tns no listener" error but it also shows established connections :

LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 18-NOV-2004 20:56:
49

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "CMSSRPL" has 1 instance(s).
Instance "CMSSRPL", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:75 refused:0 state:ready
LOCAL SERVER
Service "CMSSRPLXDB" has 1 instance(s).
Instance "CMSSRPL", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER <machine: VENUS, pid: 1264>
(ADDRESS=(PROTOCOL=tcp)(HOST=venus.hfclconnect.com)(PORT=2167))
Service "Legacy.hfclconnect.com" has 1 instance(s).
Instance "Legacy", status READY, has 2 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
D000 <machine: VENUS, pid: 2904>
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=venus.hfclconnect.com)(PORT=4
334))(PRESENTATION=oracle.aurora.server.SGiopServer)(SESSION=RAW))
"DEDICATED" established:38643 refused:0 state:ready
LOCAL SERVER
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 "cmssrpl.hfclconnect.com" has 1 instance(s).
Instance "cmssrpl", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:8 refused:0
LOCAL SERVER
The command completed successfully

----------------


the listener.ora file have

# LISTENER.ORA Network Configuration File: d:\Oracle\Oracle92\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = venus)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = d:\Oracle\Oracle92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = cmssrpl.hfclconnect.com)
(ORACLE_HOME = d:\Oracle\Oracle92)
(SID_NAME = cmssrpl)
)
)
------------------------


is it due to global db name ???

please guide me up

thanx in advance




Tom Kyte

Followup  

November 18, 2004 - 10:56 am UTC

your TNSNAMES.ORA is what tells the client how to find the listener, your tnsnames.ora is wrong (it is not shown here, but it is wrong, it has the wrong port number or hostname in it)

November 19, 2004 - 8:11 am UTC

Reviewer: Nishant choudhary from India

Sir,
sending you tnsname.ora .... i didn't found any port conflict ...plz guide..


# TNSNAMES.ORA Network Configuration File: d:\Oracle\Oracle92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL9I.HFCLCONNECT.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = venus)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL9i.hfclconnect.com)
)
)

CMSS.HFCLCONNECT.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.1.97)(PORT = 1521))
)
(CONNECT_DATA =
(SID = cmss)
)
)

HPNEW.HFCLCONNECT.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.4.14)(PORT = 1521))
)
(CONNECT_DATA =
(SID = hpora)
(SERVER = DEDICATED)
)
)

OXYGEN.HFCLCONNECT.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.1.99)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = OXYGEN)
)
)

TBMSTEST.HFCLCONNECT.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.4.14)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = tbmstest)
)
)

HPORA_172.17.4.14 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.4.14)(PORT = 1521))
)
(CONNECT_DATA =
(SID = hpora)
(SERVER = DEDICATED)
)
)

MIS.HFCLCONNECT.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = URANUS)(PORT = 1521))
)
(CONNECT_DATA =
(SID = MIS)
(SERVER = DEDICATED)
)
)

CATDBPD.HFCLCONNECT.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.6.8)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = catdbpd)
)
)

DMS.HFCLCONNECT.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NEPTUNE)(PORT = 1521))
)
(CONNECT_DATA =
(SID = DMS)
(SERVER = DEDICATED)
)
)

PANTHER.HFCLCONNECT.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.4.41)(PORT = 1521))
)
(CONNECT_DATA =
(SID = panther)
(SERVER = DEDICATED)
)
)

LEGACY.HFCLCONNECT.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = VENUS)(PORT = 1521))
)
(CONNECT_DATA =
(SID = LEGACY)
(SERVER = DEDICATED)
)
)

OXYTEST.HFCLCONNECT.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = MERCURY)(PORT = 1521))
)
(CONNECT_DATA =
(SID = OXYGEN)
(SERVER = DEDICATED)
)
)

CMSSTEST.HFCLCONNECT.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.4.10)(PORT = 1521))
)
(CONNECT_DATA =
(SID = cmsstest)
(SERVER = DEDICATED)
)
)

EXTPROC_CONNECTION_DATA.HFCLCONNECT.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

CMSSRPL.HFCLCONNECT.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = VENUS)(PORT = 1521))
)
(CONNECT_DATA =
(SID = CMSSRPL)
(SERVER = DEDICATED)
)
)

INST1_HTTP.HFCLCONNECT.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = venus)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = </code> http://HRService <code>
)
)

PILOT =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.4.15)(PORT = 1525))
)
(CONNECT_DATA =
(SID = pilot)
(SERVER = DEDICATED)
)
)


thanx in advance




Tom Kyte

Followup  

November 19, 2004 - 11:25 am UTC

umm, no mention of what tns entry you were trying to use.

Look - no listener means that you entered:

sqlplus user/pass@tnsconnect_string

we took tnsconnect_string and went to the tnsnames.ora file converted it into the above "bigger string"

we used the hostname/port found therein and tried to open a socket to that host and port.

we found out "no one is home, go away" -- no listener.


So, look at your tns connect string, translate it using the above file and see if that host=/port= is correct, you have lots of different hosts in the above, you have lots of different ports in the above, you have lots of different connect strings in the above and I don't have any idea what one you are actualy trying to use!

Listener Not Installed in Windows 2000 - Oracle9i Release 2

November 30, 2004 - 6:08 am UTC

Reviewer: Ashok Shinde from India

I've installed oracle on windows 2000 server. But found that the listener service is not installed. I could not find any reason for not adding the listener to the service list. Can you suggest any solution.


Regards,
Ashok Shinde

Tom Kyte

Followup  

November 30, 2004 - 8:00 am UTC

configure listener.ora

C:\> lsnrctl start

service appears.




Still Windows Service Error

November 30, 2004 - 9:25 am UTC

Reviewer: Ashok Shinde from India

I had already tried starting the service my "lsnrctl start" command but it gave me the error stating that

-------
LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 30-NOV-2004 19:52:19

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Starting tnslsnr: please wait...

TNS-12536: TNS:operation would block
TNS-12560: TNS:protocol adapter error
TNS-00506: Operation would block
32-bit Windows Error: 997: Unknown error

-------

Can you now help ?

Regards,
Ashok Shinde

Tom Kyte

Followup  

November 30, 2004 - 9:38 am UTC

what might your listner.ora, sqlnet.ora look like

December 01, 2004 - 7:16 am UTC

Reviewer: Ashok Shinde from India

The TNSNAMES.ORA LOOKS LIKE :
-----------------------------

# TNSNAMES.ORA Network Configuration File: C:\oracle\ora92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

INST1_HTTP.COMNAME.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cqs758)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = </code> http://HRService <code>
)
)

EXTPROC_CONNECTION_DATA.COMNAME.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

DWLAB.COMNAME.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cqs758)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DWLAB)
)
)



The SQLNET.ORA WILL LOOK LIKE
------------------------------

# SQLNET.ORA Network Configuration File: C:\oracle\ora92\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DEFAULT_DOMAIN = COMNAME.com

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)


Regards,
Ashok Shinde

Tom Kyte

Followup  

December 01, 2004 - 9:50 am UTC

umm, please reread the request above. tnsnames != listener

you are trying to start a listener, the tnsnames.ora is not relevant.

Listener

December 02, 2004 - 12:44 am UTC

Reviewer: Ashok Shinde from India

Oops Sorry :-((((((

Here is the LISTENER.ORA
-------------------------

# LISTENER.ORA Network Configuration File: C:\oracle\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cqs758)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = DWLAB)
(ORACLE_HOME = C:\oracle\ora92)
(SID_NAME = DWLAB)
)
)



Regards,
Ashok Shinde

Tom Kyte

Followup  

December 02, 2004 - 7:36 am UTC

are you running a firewall on your machine.

Got The Solution

December 02, 2004 - 5:53 am UTC

Reviewer: Ashok Shinde from India

I managed to it resolved. Initially I use to log on to windows server as user (ASHOK) which had admin right (and is a member of ORA_DBA group) and they try starting the listerner service which repeatedly failed. Now, I logged to windows server in as the admin user (ADMIN) and tried starting the service. The listerner service started very well.
Now I do not know the cause of error. Now Can you give any reason of such behaviour ?

Regards,
Ashok Shinde.


Tom Kyte

Followup  

December 02, 2004 - 7:47 am UTC

ask someone who understands this "oh so simple operating system that all you need to do is point click and ship" i suppose. sorry, I'm claiming almost total ignorance of the nuances of windows.

RE: TNS-00506: Operation would block

December 02, 2004 - 9:33 am UTC

Reviewer: Mark A. Williams from Indianapolis, IN USA

When this error occurs on Windows it is typically the result of a failed or incorrect installation. Simply removing and recreating the listener service is an easy "fix". You say you were an "admin" user - is the account that you used to install and configure Oracle a member of the *local* Administrators group? What account runs the listener service? I guess those are moot points now though.

[soapbox on]
Using Oracle on Windows is like using Oracle on any other operating system - you need to be familiar with the o/s and Oracle in order to do it properly. Virtually all of the databases I support are on Windows and they are no more difficult to manage than the ones on UNIX. Since the installation process uses the Oracle Universal Installer, installing on Windows uses the same "point click and ship" that the Oracle Universal Installer does on other platforms.
[soapbox off]

- Mark


Tom Kyte

Followup  

December 02, 2004 - 11:27 am UTC

I was just commenting on the prevailing "thought" that "windows is oh so easy, no training required"....

lol.

the operation would block is frequently caused by personal firewalls as well.

prevailing thought, etc.

December 02, 2004 - 12:52 pm UTC

Reviewer: Mark A. Williams from Indianapolis, IN USA

Agreed.

Jonathan Lewis said something that is simple, yet important, in a recent seminar. A paraphase is: You tend to get good at what you do daily and don't realize often how much else there is and what others might know.

I definitely agree with Jonathan on that (as well as other things of course). I "do Windows" daily so it is sort of second-nature for me. I used to "do Solaris" daily but I am sure some of that has managed to get pushed off the end of the old brain lru...

- Mark

not using connect string from tnsnames.ora

January 13, 2005 - 4:11 pm UTC

Reviewer: VAH from ny

Tom,

Two questions:
a. Can I have a second tnsnames.ora file ( in a different locatino that the usual network/admin ) and export TNS_ADMIN ( before connecting ) to point to this new dir for one application and other applications use the default dir?

b. How does one use the information in tnsnames.ora for a given connect string instead of actually using the connect string to connect to sql? This would be a substitute for my (a) above to connect to sql using a second tnsnames.ora.

Tom Kyte

Followup  

January 13, 2005 - 4:54 pm UTC

a) sure

b) put everything from the right hand the "=" in place of the stuff on the left hand side.

if you have:


ORC10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SUMBUL)(PORT = 1525))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orc10g)
)
)

you could use scott/tiger@orc10g or

scott/tiger@(description=(addre..........)

Awesome

January 15, 2005 - 8:11 pm UTC

Reviewer: A reader


OK

March 16, 2005 - 12:04 pm UTC

Reviewer: Raju from Hyderabad,India

Hello Tom,
Is there any Data dictionary view which provides information
about Service_name,Port and Protocol?

If not,Any way exists in SQL*PLUS to find these information?

Please do reply.
Bye!



Tom Kyte

Followup  

March 17, 2005 - 7:24 am UTC

in 10g:

sys_context( 'userenv', 'service_name' ) 

but not in 9i.  in 9i/10g:

scott@ORA9IR2> select sys_context( 'userenv', 'network_protocol' ) from dual;
                                                                                
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
-------------------------------------------------------------------------------
tcp



However, if you enable auditing and

SQL> audit connect;

then you will find:

ops$tkyte@ORA9IR2> select comment_text from dba_audit_trail where sessionid = userenv('sessionid' );
 
COMMENT_TEXT
-------------------------------------------------------------------------------
Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.
0.1)(PORT=55290))
 


which is you are trying to audit would be the right way to audit (using the audit command....)

 

What is PLSExtProc's Purpose in life?

March 26, 2005 - 10:41 am UTC

Reviewer: Richard from Oxfordshire, UK

I'm using 10g 10.1.0.3.0 on Linux.

When I start the Listener, I see the following sort of output:

Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
... etc, etc

I saw the same sort of output in an earlier posting in this thread, so I hope this is a *legal* question and that you won't make fun of me (at least, not too much)!

What is PLSExtProc (I can't see any mention of it in the Oracle Database Master Index)? Is it *bad* that it doesn't seem to be starting-up on my machine?




Tom Kyte

Followup  

March 26, 2005 - 12:18 pm UTC

Only make fun of instant messenger speak (u no, lk this stuff were u hv 2 try and d-cipher what we mean plz)

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg11rtn.htm#1656 <code>

it is there for external procedures, the extproc program that can run and load your C code (write a stored procedure in C)

If you are not using it, you may certainly "unconfigure it"

Tom, you are getting a hang of it :-)

April 05, 2005 - 4:11 am UTC

Reviewer: Tarry Singh from Assen, Netherlands

Well tom, you are getting a hang of the messenger lingo.
u purty gud, u no :-)

Database Recovery

April 19, 2005 - 8:01 am UTC

Reviewer: Victor Wandera from Kenya

I would like some favor from you. Im somehow stuck. I instructed my client to be backing up the database by creating database dump (exp command) but they have just been copying datafiles i,e oradata directory. Now the server crushed, Could somebody explain to me on how to restore this database, coz i have created the database but when i i replace the control, data files and Index files, it seem not to be working. Kindly help its really urgent.

Regards

Eugene

Tom Kyte

Followup  

April 19, 2005 - 8:08 am UTC

please contact support for recovery assistance like this, lots more information needs to be gathered.

Fingers crossed that at least they shutdown the database before copying the files, else what you might have is just a bunch of files.

Recovery is something that needs to be tested (hindsight now).
Export is NOT a database backup tool, it is a data extraction tool.



**** oracle 10g services ****

May 02, 2005 - 4:47 am UTC

Reviewer: fethi FERJANI from Qatar

I am starting working with Oracle 10g. Also I use Reports Developer and Forms Developer 10g, of course. There are many services that are rannung. Is it possible to know all services needed by Oracle, Forms and Reports.

Also i want to know for each of the following : Oracle, Reports and Forms, which port is used? How I know the ports of the Oracle Products.

Many Thanks for helpful.

Tom Kyte

Followup  

May 02, 2005 - 8:28 am UTC

sorry -- I've not setup or administered the application server. otn.oracle.com-> discussion forums might be more useful for this one.

*** PlaceHolder Example ***

May 02, 2005 - 5:11 am UTC

Reviewer: fethi F. from Qatar

Hi ,

I am using Repors Developer. I try using PlaceHolder. I explaore the example from the Reports Developer Help. It is the following:

**************
Placeholder for Special Summary example

Suppose that you wanted to create a master/detail report that would list the highest paid employee(s) for each department. The output below shows a sample of what you would get:

Dname ACCOUNTING Loc NEW YORK Highest Paid

Employee(s):
Ename Job Sal
---------- --------- ---------
CLARK MANAGER 2450.00 KING
KING PRESIDENT 5000.00
MILLER CLERK 1300.00
Dname RESEARCH Loc DALLAS Highest Paid
Employee(s):
Ename Job Sal
---------- --------- ---------

SMITH CLERK 800.00 SCOTT and FORD
JONES MANAGER 2975.00
SCOTT ANALYST 3000.00
ADAMS CLERK 1100.00
FORD ANALYST 3000.00
To get the names of the highest paid employee(s), you create a placeholder column named HIGHEST_PAID_EMP of Datatype Character. The value of HIGHEST_PAID_EMP is set by a formula column named SET_HIGHEST_PAID_EMP of Datatype Character. R_COUNT is a summary column with a Function of Count. SET_HIGHEST_PAID_EMP contains the following PL/SQL function in Formula:

function set_plch_high_emp return character is
begin
/* Check if we are at first record in child, if so,
then reset placeholder */
if (:r_count <= 1) then
:highest_paid_emp := ' ';
end if;
/* Does this child record compare to the max sal
for the dept ? If yes, and placeholder already
set (i.e. we have more than one max sal) set the
placeholder to the concatenated string. Else
set the placeholder to the employee's name */
if (:sal = :max_dept_sal) then
if (:highest_paid_emp <> ' ') then
:highest_paid_emp := :highest_paid_emp||' and '
||:ename;
else
:highest_paid_emp := :ename;
end if;
end if;
return (:highest_paid_emp);
end;

*******************
The variable :max_dept_sal is not defined. Where I will be defined and what is its code.
many Thanks.

Tom Kyte

Followup  

May 02, 2005 - 8:33 am UTC

I know NOTHING about reports, otn.oracle.com -> forums

**** Listener Parameters to list ****

May 04, 2005 - 5:18 am UTC

Reviewer: Fethi f from qatar

Hi tom.

I want to list the port number, the connect string and the location of tnsnames.ora.

Can u tell me which commands i used in Oracle to do this.

I am trying (under system/manager):

select comment_text from dba_audit_trail;

Result: No rows selected

Why?

Tom Kyte

Followup  

May 04, 2005 - 9:12 am UTC

because you don't have auditing enabled, until you do, dba_audit_trail will remain empty.

the tnsnames.ora is just a file, there is no command to "query" it.

The location is by default in $ORACLE_HOME/network/admin but can be overridden by setting the TNS_ADMIN environment variable.

Dynamic Registration of Database witrh Listener at startup

May 08, 2005 - 12:45 am UTC

Reviewer: James McNaughton from UK

Hi there,

Just a note about dyanamic registration of the database with the listener at startup.

I'm totally new to the Oracle DBA side of things (whilst being a competent developer), and have recently installed Oracle 10g on my Windows XP (Home Edition) laptop.

I too was getting the ORA-12514 error message when attempting to connect via the Windows SQLplus client. I followed your advice regarding dynamic registration of my database service with the listener, but after shutting down and restarting the database from within SQLplus in DOS, this still would not work.

I therefore added the following to the listener.ora file in the SID_LIST section:

SID_DESC =
(GLOBAL_DBNAME = MainDB)
(ORACLE_HOME = C:\oracle\product\10.1.0\Db_1)
(SID_NAME = MainDB)
)

This has now fixed the problem.

I know this is not your preferred solution, but it works. This will allow me to continue my DBA studies. I now just have to figure out why the DBConsole service won't start.

Tom Kyte

Followup  

May 08, 2005 - 8:08 am UTC

that is called static registration.

it would imply your configuration for dynamic registration was not setup (eg: you decided not to use default ports for example and didn't set up the local listener)


but on a laptop, you shouldn't even need/use/want the listener for sqlplus. just sqlplus user/password would do it. no net.

*** port numbers used in Oracle , and developer

May 10, 2005 - 3:47 am UTC

Reviewer: fethi from qatar

Hi tom.

I want to list the port number, the connect string and the location of
tnsnames.ora.

Can u tell me which commands i used in Oracle to do this.

I am trying (under system/manager):

select comment_text from dba_audit_trail;

Result: No rows selected

I try: audit connect
The system answer me: Audit succeeded.

Also I use TNS_Admin in Registory for : Oracle, Reports and Forms to refer to the same tnsnames.ora

CAn you help me???

Tom Kyte

Followup  

May 10, 2005 - 8:53 am UTC

"u" is not here, I don't know this "u" person -- they do get a lot of requests however.


In order to have the audit trail enabled, you need to have AUDIT_TRAIL=db or AUDIT_TRAIL=true set in the parameter file. Is it?



reader

May 24, 2005 - 2:42 pm UTC

Reviewer: A reader

On unix machine what would be the minimum permission on
tnsnames.ora for client to connect to database
successfully. 004 does not seeem to work. Get ORA-12154
However 044 seems to work. The chmod is based on oracle:dba account



Tom Kyte

Followup  

May 24, 2005 - 3:57 pm UTC

the client needs to read the file. they need read access

Something more about tnsnames.ora file

December 08, 2005 - 8:04 am UTC

Reviewer: Suvendu from Bangalore, INDIA

Hi Tom,

I requested to add two connection string into tnsnames.ora file in a corporate enviornment where there are ORACLE_HOMEs from 7.2 to 10g, so I suggested to update the specific tnsnames.ora file on a particular home directory 9.2.0.4 though my application runs pointing to Oracle 9i. But I got strange response from the Sr.DBA's of these databases is " TNS name process is not a manual process. The additions for the TNS name process is a automated process that runs by a job, set time, and set date. It needs research on it". And they almost taken 4 days still they saying "Research continuing...".

So, I thought myself, there could be something lack of my knowledge. Could you please comment that, the above statement by them is correct or something is there which need to consider when adding the connection entries to the tnsnames.ora file?

Thanking you a lot being spending such your valuable time with me.

Regards,
Suvendu


Tom Kyte

Followup  

December 08, 2005 - 11:40 am UTC

this is their process they are researching, we don't have a job that does this.

December 08, 2005 - 4:53 pm UTC

Reviewer: Alexander the ok

Tom,

I desperately need your help find my listener. I've tried everything, dug through countless metalink articles. Basically it's as if the listener was not installed, only the oracle software allows you to view "installed products", and it shows up as being there. Everything I do results in:

C:\>lsnrctl stop

LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 08-DEC-2005 16:36:08

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=normar)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 61: Unknown error

I've checked my tnsnames and listener.ora files a dozen times, compared ports etc I have no idea what to do. I know I have no services for a tns listener along with my other oracle services (OracleUIHome...OracleServiceSID etc...) I do have a tnslsnr.exe in my bin dir, but that's about all I know at this point. Any idea where I should go from here?

Tom Kyte

Followup  

December 09, 2005 - 1:28 am UTC

the lsnrctl program is part of the listener, hence it is there.


this is just saying "there is nothing to stop here, it isn't RUNNING"

so, try starting it perhaps.

December 09, 2005 - 9:20 am UTC

Reviewer: Alexander the ok

Ok here's what happened:

C:\>lsnrctl start

LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 09-DEC-2005 09:04:50

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production
System parameter file is C:\OraHome1\network\admin\listener.ora
Log messages written to C:\OraHome1\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Normar.IBM.Local)(PORT=1521)))
TNS-01155: Incorrectly specified SID_LIST_LISTENER parameter in LISTENER.ORA
NL-00303: syntax error in NV string

Listener failed to start. See the error message(s) above...

My listener.ora:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = normar)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = c:\OraHome1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = RODNEY.IBM.LOCAL)
(ORACLE_HOME = c:\OraHome1)
(SID_NAME = RODNEY
)
)
If I had any idea what a nv string is I might be able to locate this "syntax error". See anything suspicous?

Nevermind

December 09, 2005 - 9:43 am UTC

Reviewer: Alexander the ok

Sorry Tom forget the last post you can delete it if you want. I was missing a ')' as you can see in my listener file. Thanks very much for your help. This was another case of not really being a command line guy hurt me (By me looking in the services, hey tns listener is not there, where'd it go?? ;))

Changing the Instance name

January 09, 2006 - 6:00 am UTC

Reviewer: PRASAD from India

Hi Tom,

Basic question : ORACLE_SID is the instance name or service_name is it right? If so by chaning the service_names parameter in init.ora I can change the instance name ( well, I would change ORACLE_SID in bash profile and SID_NAME in listener.ora and SERVICE_NAME in tnsnames.ora to connect). Is this is right way of changing instance name?

By following the above method I have observed the following:
My initial instance name is ORA10G and my database name also ORA10G. Some reason even after changing my instance name to "PRASAD" my listener is showing a service for ORA10G as well as for PRASAD. Is this is default behaviour? If so from where it is getting ORA10G service name after I have changed to PRASAD? I was under impression that it will show only new service_name that is PRASAD. I am on ORACLE 10G.

With regards,
Thanks Tom.

Tom Kyte

Followup  

January 09, 2006 - 8:03 am UTC

oracle_sid is the instance name.

a single instance may have zero one or more service names associated with it.



see the service_names init.ora parameter.

listener

September 14, 2006 - 10:23 am UTC

Reviewer: A reader

Tom:

When I log in using oracle web app I get this. Nothing changed in application code and everything worked fine for years. Does this tell you something changed in the server config files. The strange thing is that when I execute the SQL statement in line 163 using SQL*plus it works fine.

ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor
ORA-06512: at "DEV.MENU", line 163
ORA-06512: at "DEV.LOGIN", line 21
ORA-06512: at line 10


2. Can you explain in simple terms what each file is used for TNSNAMES.ora and LSITENER.ora. I keep confising the two.

thanks,

Tom Kyte

Followup  

September 14, 2006 - 10:44 am UTC

someone changed the tnsnames.ora file on the server it would sound like.


listener

September 14, 2006 - 11:38 am UTC

Reviewer: A reader

Tom:

DBA says he did not change anything. How do you trouble shoot this?

the strange thing is that when i create a link using that service name it works. The code uses a link from one databse to another.

Tom Kyte

Followup  

September 14, 2006 - 1:48 pm UTC

$ oerr ora 12514
12514, 00000, "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.


Basically, the listner that was gotten in touch with did not recognize this service name.

The tnsnames.ora file on the server (database server) was used to resolve this tns connect string into a host/service name.

There may be multiple tnsnames.ora files on the server - and the one used by one session can be different from the one used by another session.

EG:

a shared server has an environment inherited from the starting up of the data environment.

a dedicated server spawned by a listener on that machine has an environment inherited from the listener (which could be different than the environment used to start the database of course)

a dedicated server connection created directly by a user process (eg: not using a listener) will have an environment inherited from the client application.


All three environments in this example can point to different tnsnames.ora files.


I'd have the DBA search about for other tnsnames.ora copies, depending on the OS you can use "ps" sometimes to see the environment of processes - look for varying TNS_ADMIN settings in various environments.


listener

September 14, 2006 - 4:21 pm UTC

Reviewer: A reader

Tom:

You were correct. There were multiple files of the tnsnamens.ora, one under 9202 and one under 9206 and the service under 9202 one was changed.

thanks,

Tom Kyte

Followup  

September 14, 2006 - 4:53 pm UTC

ahh, my favorite words "you were correct" :)

(kidding... glad you found it)

listener

September 15, 2006 - 4:15 pm UTC

Reviewer: A reader

Tom:

1. Is there anyway in SQL*plus to check where a service name defined in the tnsnames.ora (server-- not client) is pointing to. I had to do tnsping service_name in unix to see the string.

2. When a client connect to oracle, are you using two strings. First the client looks at the client entry and then checks the server entry? How does it work exactly?

3. What is listener.ora for. Is this for configuring database listener. I thought there is only one on port 1521.

Thanks,

Tom Kyte

Followup  

September 16, 2006 - 2:29 pm UTC

1) no, and tnsping might be misleading. Look two reviews up for "why" (shared, dedicated, dedicated spawned directly could all have different environments)

2) I don't know what you mean.

3) that is for configuring a listener on a server.

tnsnames.ora

October 11, 2006 - 7:00 am UTC

Reviewer: A reader

Tom:

If you have 50 machines on a LAN runing PB client application accessing an oracle 8.6 database on a server. WE upgraded DB to 9.2.

1. Do you have to upgrade the clienbt PB code so it points to the 9.2 database. or you would only change the definition of the TNS service name in the thnsnames.ora on the machine.

2. Do you have to install oracle 9i client on each machine or this can be installed on a file server and shared by all 50.

3. Let us say all 50 machines have oracle client installed. Can you have them all share the same tnsnames.ora. How do you do that? How would the client code know to use the tnsnames.ora saved in certain server/directory.

thanks,

Tom Kyte

Followup  

October 11, 2006 - 8:18 am UTC

1) umm, what is the difference, changing the definition in the tnsnames.ora "points" to the 9.2 database. It would have the same net effect, do whatever is easiest for you.

2) there is an instant client you may install, it would be installed on each machine. however, unless you upgrade powerbuilder (assuming that is what you mean by pb - and not peanut butter for example) it would be linked with your old 8i libraries.

talk to the makers of "PB" to see what they say needs be done for their product.

3) yes, you could put it on a shared disk (network drive) and use the tns_admin environment variable. Or you can use a directory service (ldap for example) instead of a tnsnames.ora

Is listener.log file generates on either client or the server?

October 18, 2006 - 2:12 pm UTC

Reviewer: Dawar Naqvi from LA, CA _ USA

Tom,

Is listener.log file generates on either client or the server?

If not then which log file generates on either client or the server?

Actually, while defining our security policy I come to the stage to find which logs can be generated on either the client or the server?

And also explain me about SQL*Net trace and log files.

cheers,
Dawar

Tom Kyte

Followup  

October 18, 2006 - 3:43 pm UTC

listener logs are generated by the listener and the listener runs on the server.

hence, listener logs would be where your listener is.


what do you need explained about sqlnet trace files?? that is a fairly "broad" question after all.

October 18, 2006 - 4:50 pm UTC

Reviewer: Dawar from LA, CA --- USA

is it possible to generate " sql*net trace and Log files " either on client or server?




Tom Kyte

Followup  

October 18, 2006 - 4:57 pm UTC

you can generate client trace files on the client.

you can generate server trace files on the server (listener.log)

all are documented in the net admin guide.

</code> http://docs.oracle.com/docs/cd/B19306_01/network.102/b14212/troublestng.htm#sthref1603 <code>



tnsnames.ora, sqlnet.ora & listener.ora files are not exit

October 25, 2006 - 5:57 pm UTC

Reviewer: Dawar from LA, CA -- USA

Hello Tom,

Sorry to bug you again.

we setup few DB servers.

I went to holidays and now I have noticed two servers does not have tnsnames.ora, sqlnet.ora & listener.ora files.

I have checked under $ORACLE_HOME/network/admin.
Then I run find . -name "tnsnames.ora" -print from the root.

And find files only under $ORACLE_HOME/network/admin/samples.
But they are only samples files.

But I can connect to the database via Enterprise manager console or from the command prompt using sqlplus.

** First I need to investigate what happened with files?
** second Do I need to create files again? using netca is ok?

** They are not in production yet. Next week we need to import some test data on those servers.

Any feedback will be appreciated.
Dawar
LA, USA



Tom Kyte

Followup  

October 25, 2006 - 6:00 pm UTC

you only need those files to connect over the network.

are you?


if you just:

sqlplus user/pass


you do not need a tnsnames.ora (you are connecting locally), if you use enterprise manager from your desktop as a client/server tool - your desktop would need the tnsnames.ora, not the server,

the sqlnet.ora is always optional - you tell me if you need it, or not,

the listener.ora is only needed to make it so you can connect over the network to the server - if you are not, you don't need it.

tnsnames.ora, sqlnet.ora & listener.ora files are not exit

October 25, 2006 - 6:02 pm UTC

Reviewer: Dawar from LA, CA USA

yes
Tom Kyte

Followup  

October 25, 2006 - 6:06 pm UTC

yes WHAT?@%$#????

what does "yes" mean.

details, details, preciseness, details...

think about it, I cannot see your screen from here.

tnsnames.ora, sqlnet.ora & listener.ora files are not exit

October 25, 2006 - 6:19 pm UTC

Reviewer: Dawar from LA, CA USA

Tom,

Sorry for incomplete answer.

You said:

you only need those files to connect over the network.

are you?

My feed back:
Yes

You Said:

the listener.ora is only needed to make it so you can connect over the network to the server - if you are not, you don't need it.

My feed back:
I am connection to the database server over the network.
From my desktop to the server.

Without listener.ora file in the server??? How???

Here is my assignment
I need to import data to the servers remotely over the network.

Do I need listener.ora file?

cheers,

Dawar

tnsnames, sqlnet, listener deja vu all over again

October 25, 2006 - 7:32 pm UTC

Reviewer: Mark A. Williams from Indianapolis, IN USA

hmmm... this seems familiar...

</code> http://forums.oracle.com/forums/thread.jspa?threadID=437281&tstart=0 <code>

Tom Kyte

Followup  

October 25, 2006 - 9:49 pm UTC

well, at least the world is consistent :)

same question
same answers....

tnsnames.ora, sqlnet.ora & listener.ora files are not exit

October 26, 2006 - 1:36 am UTC

Reviewer: Dawar from LA, CA USA

Tom,

Actually it was firewall issue with one box.

Now I can connect to the Database server over the network from my desktop (PC) through sqlplus or enterprise manager console.


My new question with new situation is follows:

Is it possible to connect Database from client (window PC) over the network without listener.ora file exit in database server?

IF yes, how?

cheers,
Dawar

Tom Kyte

Followup  

October 26, 2006 - 11:46 am UTC

technically, no you do not.

the listener will start and databases can register to it. You technically do not need a listener.ora

SID given in connect descriptor could not be resolved

November 19, 2006 - 2:57 pm UTC

Reviewer: Dawar Naqvi from Los Angeles, CA USA

Hello Tom,

I installed Oracle DB 10.2 on AIX.
I can connect to the database from server. NO issue.

But when I tried to connect from client from my PC,
I got the following message.

"SID given in connect descriptor could not be resolved."

$ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production on 19-NOV-2006 10:34:26

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC2)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production
Start Date 19-NOV-2006 10:34:20
Uptime 0 days 0 hr. 0 min. 5 sec
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File /opt/app/oracle/Ora10.2.0/network/admin/listener.ora
Listener Log File /opt/app/oracle/Ora10.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC2)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fully qualified name)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/Ora10.2.0/network/adm
in/tnsnames.ora
# Generated by Oracle configuration tools.

sidname =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = fully qualified name)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sidname)
)
)

LISTENER_sidname =
(ADDRESS = (PROTOCOL = TCP)(HOST = fully qualified name)(PORT = 1521))


EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

$


$ more listener.ora
# listener.ora Network Configuration File: /opt/app/oracle/Ora10.2.0/network/adm
in/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/app/oracle/Ora10.2.0)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2))
(ADDRESS = (PROTOCOL = TCP)(HOST = fully qulified name)(PORT = 1521))
)
)

Dawar


Tom Kyte

Followup  

November 20, 2006 - 2:49 am UTC

it would appear you are using the wrong sid.

the tnsnames.ora on the server is quite simply "not relevant", the one on your pc would be.

SID given in connect descriptor could not be resolved--DONE

November 19, 2006 - 3:45 pm UTC

Reviewer: Dawar Naqvi from Los Angeles, CA USA

I restart the database from the server and problem has solved.

Dawar

Multiple Oracle Homes

December 15, 2006 - 12:13 pm UTC

Reviewer: A reader

On one Solaris server, we have two oracle homes 9i and 10g. we want to use just one listener off of 10g home (the current listener is running from 9i home). what are the steps that we need to do in order to bring up the listener from 10g home.

Thanks.

Tom Kyte

Followup  

December 15, 2006 - 12:14 pm UTC

same steps you would follow if there was just a 10g oracle home. nothing different.

my scenario

February 06, 2007 - 4:21 pm UTC

Reviewer: Stewart Bryson from Atlanta, GA

Listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u00/app/oracle/product/10.2.0/db1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ironman.transcendentdata.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

I start the listener:

[oracle@ironman admin]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 06-FEB-2007 02:17:22

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

Starting /u00/app/oracle/product/10.2.0/db1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u00/app/oracle/product/10.2.0/db1/network/admin/listener.ora
Log messages written to /u00/app/oracle/product/10.2.0/db1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ironman.transcendentdata.com)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ironman.transcendentdata.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 06-FEB-2007 02:17:22
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u00/app/oracle/product/10.2.0/db1/network/admin/listener.ora
Listener Log File /u00/app/oracle/product/10.2.0/db1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ironman.transcendentdata.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@ironman admin]$

I start the database:

SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 71305460 bytes
Database Buffers 92274688 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL>

Now we look at the listener status again:

[oracle@ironman admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 06-FEB-2007 02:18:17

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ironman.transcendentdata.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 06-FEB-2007 02:17:22
Uptime 0 days 0 hr. 0 min. 54 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u00/app/oracle/product/10.2.0/db1/network/admin/listener.ora
Listener Log File /u00/app/oracle/product/10.2.0/db1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ironman.transcendentdata.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dtdinc.transcendentdata.com" has 1 instance(s).
Instance "dtdinc", status READY, has 1 handler(s) for this service...
Service "dtdinc_XPT.transcendentdata.com" has 1 instance(s).
Instance "dtdinc", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@ironman admin]$

Database parameters:
SQL> show parameter global

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size string
global_names boolean TRUE
SQL> show parameter listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string
remote_listener string
SQL> show parameter dispatch

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string
max_dispatchers integer

Can tnsping on the local box, but cannot connect:

[oracle@ironman admin]$ tnsping dtdinc.transcendentdata.com

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 06-FEB-2007 02:19:49

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

Used parameter files:
/u00/app/oracle/product/10.2.0/db1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ironman.transcendentdata.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = dtdinc)))
OK (10 msec)
[oracle@ironman admin]$ sqlplus brysons@dtdinc.transcendentdata.com

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 6 02:20:03 2007

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

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


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@ironman admin]$

Can ping server from another box, but cannot tnsping:
cygwin => ping ironman
PING ironman.transcendentdata.com (192.168.1.4): 56 data bytes
64 bytes from 192.168.1.4: icmp_seq=0 ttl=64 time=2 ms
64 bytes from 192.168.1.4: icmp_seq=1 ttl=64 time=1 ms
64 bytes from 192.168.1.4: icmp_seq=2 ttl=64 time=1 ms
64 bytes from 192.168.1.4: icmp_seq=3 ttl=64 time=1 ms
64 bytes from 192.168.1.4: icmp_seq=4 ttl=64 time=1 ms
64 bytes from 192.168.1.4: icmp_seq=5 ttl=64 time=1 ms
64 bytes from 192.168.1.4: icmp_seq=6 ttl=64 time=1 ms

----ironman.transcendentdata.com PING Statistics----
7 packets transmitted, 7 packets received, 0.0% packet loss
round-trip (ms) min/avg/max/med = 1/1/2/1
cygwin => tnsping dtdinc.transcendentdata.com

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 06-FEB-2007 16:14:00

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

Used parameter files:
C:\oracle\product\10.2.0\owb_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ironman.transcendentdata.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = dtdinc.transcendentdata.com)))
TNS-12560: TNS:protocol adapter error
cygwin =>

Tnsnames.ora looks the same on server and client:
DTDINC.TRANSCENDENTDATA.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ironman.transcendentdata.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dtdinc.transcendentdata.com)
)
)

Sqlnet is also the same:

# sqlnet.ora Network Configuration File: /u00/app/oracle/product/10.2.0/db1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

NAMES.DEFAULT_DOMAIN = transcendentdata.com

One possible caveat: the server is a VMWare virtual machine running Oracle Enterprise Linux... the client is Windows XP host (with Cygwin to explain the odd unix prompt).

The VMWare issue doesn't have any bearing on the fact that I can't connect on the local server... but may have a bearing on the inability to connect remotely. I am using a bridged network connection, and can ssh between the environments just fine.

Thanks for your help.

Tom Kyte

Followup  

February 06, 2007 - 4:39 pm UTC

Service "dtdinc.transcendentdata.com" has 1 instance(s).


you are missing the domain in the service name in the tnsnames.ora

my scenario cont.

February 06, 2007 - 4:26 pm UTC

Reviewer: Stewart Bryson from Atlanta, GA

Forgot this:
Server:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL>

Client:

cygwin => sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 6 16:22:32 2007

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

SQL>

partially there

February 06, 2007 - 5:01 pm UTC

Reviewer: Stewart Bryson from Atlanta, GA

That corrected the issue with not being able to connect from the local server. Thanks for that... I missed it. From the client (the windows VMWare host), I get the same error. Do you think this could be VMWare related, even though I can ssh between the two?

Thanks.

cygwin => tnsping dtdinc.transcendentdata.com

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 06-FEB-2007 16:55:22

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

Used parameter files:
C:\oracle\product\10.2.0\owb_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ironman.transcendentdata.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = dtdinc.transcendentdata.com)))
TNS-12560: TNS:protocol adapter error
cygwin =>

Tom Kyte

Followup  

February 07, 2007 - 12:54 pm UTC

can you "ping" ironman.tra.....com ?

Firewall related

February 06, 2007 - 5:43 pm UTC

Reviewer: Stewart Bryson from Atlanta, GA

Oracle Enterprise Linux installs by default with the "hardened" linux turned on. Had to allow 1521 through.

Thanks.

swb


March 12, 2007 - 5:13 pm UTC

Reviewer: Su Baba

sqlplus allows the use of connect string to make the connection. For example

sqlplus test/test@(description=(address=(host=test-server)(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))

This works. However, when I tried the same thing with sqlldr, it complains about it:

sqlldr userid=test/test@(description=(address=(host=test-server)(protocol=tcp)(port=1521))(connect_data=(sid=orcl))) control=sql/testx.ctl

LRM-00116: syntax error at 'test/testt@(desc' following '='

SQL*Loader: Release 10.2.0.2.0 - Production on Mon Mar 12 15:11:26 2007

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

SQL*Loader-100: Syntax error on command-line


Is there anyway to use a connect string in SQL*Loader?



Tom Kyte

Followup  

March 12, 2007 - 7:36 pm UTC

use quotes

or set TWO_TASK (on unix) LOCAL (in windows) in the environment.

March 13, 2007 - 12:52 pm UTC

Reviewer: Su Baba

This is Windows. I tried both single and double quotes. No luck. Any ideas?

sqlldr userid=test/testt@"(description=(address=(host=test-server)(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))" control=sql/testx.ctl
LRM-00116: syntax error at 'test/testt@(desc' following '='

SQL*Loader: Release 10.2.0.2.0 - Production on Tue Mar 13 09:40:45 2007

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

SQL*Loader-100: Syntax error on command-line


sqlldr userid=test/testt@'(description=(address=(host=test-server)(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))' control=sql/testx.ctl
LRM-00116: syntax error at 'test/testt@'(des' following '='

SQL*Loader: Release 10.2.0.2.0 - Production on Tue Mar 13 09:40:52 2007

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

SQL*Loader-100: Syntax error on command-line


Tom Kyte

Followup  

March 13, 2007 - 3:27 pm UTC

set LOCAL in the environment like I suggested?

How to Identify where my tnsnames.ora file is?

May 16, 2007 - 2:48 pm UTC

Reviewer: Joe from Horsham, PA

The computer that I am using is using a tnsname.ora file that is on the network, but the problem is I don't know exactly where it is. I checked the one in my local (C:\oracle\network\admin\), and it wasn't there. I searched the entire c:\ drive and it wasn't there either. Is there a way for me to find out where my tnsnames.ora file is?

Thanks Tom!

Tom Kyte

Followup  

May 17, 2007 - 10:49 am UTC

check to see what your TNS_ADMIN is set to (environment or registry setting)

Where is my tnsname.ora file?

May 16, 2007 - 2:50 pm UTC

Reviewer: Joe from Horsham, PA

The computer that I am using is using a tnsname.ora file that is on the network, but the problem is I don't know exactly where it is. I checked the one in my local (C:\oracle\network\admin\), and it wasn't there. I searched the entire c:\ drive and it wasn't there either. Is there a way for me to find out where my tnsnames.ora file is?

Additional: I also checked the TNS_ADMIN variable, it wasn't in my computer.

Thanks Tom!

tns file

February 29, 2008 - 12:50 pm UTC

Reviewer: A reader

om:

Can you explain to me what is that first part of TNS entry "MCPP.ABC.DEF.GOV".
I thought it is only a label and it will actuall us whatever the service name defined in SERVICE_NAME which is what is defined on the service.

We have two locations for client software that connect to another remote place for database server.
Why would the first name have to be different for each site?

IT seems part of the name relies on the network you are on?



customer site entry

MCPP.ABC.DEF.ORG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server.def.org)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = MCPP)
)
)


developer site entry

MCPP.MYNETWORK.ROOT.NET =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server.def.org)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = MCPP)
)
)


2. do you usally recommend saving this file on a loca lserver instead of 50 separate machines local drive? can this be done and is there disadvantages with that?
Tom Kyte

Followup  

March 01, 2008 - 12:02 pm UTC

mcpp.abc.def.org is just the tns connect string "name", it can be whatever you like, it is only used by the client to be translated into the actual connect string.

those two entries from the customer site and the developer site are for the same database and service. A rose is a rose...


2) you make that call, it is entirely dependent on your names. If you are worried about file proliferation - you would use a naming service instead of files.

tnsnames

March 01, 2008 - 3:26 pm UTC

Reviewer: A reader

Tom:

yes but my question since it is only a name/alias, why it works on our site and does not work when we deploy it on customer site?

for customer site, we have to change the name to the second string i listed.

the client powerbuilder application is the same. do you think this has to do with what is defined in SQLNET.ora?

is there a relation between sqlnet.ora and tnsnames.ora.

2. can you also describe how the communication actually works meaning when i do

connect scott/tiger@abcd;

does my net8 software look for abcd in tnsnames.ora for abcd and translate it and then connect to database where the is another tnsnames.ora file for valid service names.

how is sqlnet.ora used.
Tom Kyte

Followup  

March 01, 2008 - 10:17 pm UTC

does your customer have free unfettered access to your host? might there be a firewall.

can you ping and tnsping that entry.


if your use user/pass@x.y.z - where x.y.z is what is in the tnsnames.ora file, the sqlnet.ora doesn't count (would only count if you used a default domain in the sqlnet.ora and used user/pass@x - without qualifying the domain.




if you use scott/tiger@abcd - the sqlnet.ora will be used to find the default domain, turing abcd into abcd.y.z - and then the tnsnames.ora will be used to turn abcd.y.z into a connect string and then the connect string is used to connect


by setting the environment variable TNS_ADMIN, you can have the default location of the sqlnet.ora and tnsnames.ora changed - but they would be in the same place (as each other)

tns

March 02, 2008 - 1:11 am UTC

Reviewer: A reader

Tom:

yes the client is on same network where host is. there is no firewall.
we go trhough firewall.

I think there is an entry in the sqlnet.ora. This is what might be causing it because client said if they take the "ABC.DEF.ORG" and just used MCPP it works.




1. based on definition of sqlnet.ora, the value of default_domain should not be appended it the net service name alias has a domain???

*********************************
names.default_domain
The value of this parameter is appended to the net service name if the net service name doesn't already have a domain.
******************************



2. can you advise me on what is the cleanest way to set up this so we won't run into issues on every install (have same files configuration).
do you take out the parameter from sqlnet.ora or you have to define a domain and this domain will work in each site the same way. I would think you have to change the domain for each site , would not you.

But it seems you do not have to set it and just have one common tnsnames.ora for all sites with one sqlnet.ora with blank default_domain setup.


3. If i used the TNS_DOMAIN thing you have to go to every machine and do the setup in windows registry. also this depends on whether the file server is up or not. if it is down they can't connect. it seems there is some downside to this approach instead of loading client on every machine. what do you think?
Tom Kyte

Followup  

March 02, 2008 - 10:53 am UTC

1) correct, if you use a.b.c.d - then default domain won't be used. If you use a - then the default domain would be.

2) test your configuration and use the same one for your customer as you use for you (since you are all one big network - why would you do it differently???)

YOU make the choice whether to
a) have a default domain
b) override the default domain
c) whatever

It is your setup - you make the choice as to what you want to do.


You need not change the domain for each site, it is just a string, just a name. You don't even have to use it if you don't want to.

3) I only mentioned tns_admin because you asked how the files are found - if your customer has it set - then it would change where we look. I was not suggesting "set it".


tns

March 02, 2008 - 11:39 am UTC

Reviewer: A reader

1. OK, since our tnsnames.ora has a.b.c.d regardless of the different values of "b.c.d" then the default_domain parameter WILL NEVER be used by the client application by using the TNS alias.



The question remain is why "a.b.c.d" works on our site and not on customer site since SQLNET.ORA is not even being used now. it is same PB application that should be using same ALIAS.

THe PB application refers to connection "a". Can it be in the way PB refers to the file by alias or actual service name.


2. is there a way to test whether connection is using the domain in sqlnet.ora. Would you try to connect from sql(plus and see if it works or not?


3. Are you saying that I can take out "DOMAIN_NAME" parameter from sqlnet.ora.
it is not really needed for anything? How does it get setup in the first place?


Tom Kyte

Followup  

March 02, 2008 - 2:27 pm UTC

1) that is not true. The default domain will be used whenever the client application doesn't supply a domain - it depends on what the application specifies - NOT what the tnsnames.ora has.

Since the powerbuilder application uses just A, as I've said a couple of times already - the default domain from the sqlnet.ora IS BEING USED.

that is likely it, you have the wrong default domain in there.

(you know, you haven't even said what the error is yet...)

2) it is using the default domain if the client just says A. (i've said that - if you just use A, the default domain will be appended and then the tnsnames.ora is used)

3) I'm not saying anything here - well, maybe other than "can you get your DBA who has likely set this up hundreds of times to guide and counsel you". I'm afraid to tell you "what" to do here.

tns

March 02, 2008 - 5:19 pm UTC

Reviewer: A reader

TOm:

OK i understand what you are saying.

i will check this out tomorrow. but i think you are correct.

THE client application has one connection "XYZ". This means it is using the sqlnet.ora default domain. It is probably different on each computer.

Which is best to do:

1. REMOVE the default_domain from sqlnet.ora completely. THat means there will be nothing to append and environment will be the same.

2. Or have same default_domain in sqlnet.ora even though they might have different domain that our company?
would that work.


Can the string in default_domain be the same in sqlnet.ora. I mean if i set it to "oracle.com" would it still work even though i am not on that domain.

Thank you,



Tom Kyte

Followup  

March 02, 2008 - 8:20 pm UTC

... Which is best to do ...

Please, think about this, this is simple "configuration". You have the facts:

a) tnsnames.ora has a.b.c.d

b) sqlnet.ora has default_domain x.y.z

c) client uses just "a" (for whatever reason, just a, not a.b.c.d)


Your choices (YOUR choices)

a) normalize the default domain everywhere
b) have client use a.b.c.d


You pick.


There is no such thing as "being in a domain" in this sense, it is just a naming convention. The default domain (just like with networking!!) is appended to any unqualified tnsname (just like an unqualified hostname would be) and then looked up.

March 02, 2008 - 10:40 pm UTC

Reviewer: A reader

Tom:

yes, what i am thinking is remove default domain parameter or leave it blank in sqlnet.ora.

that way we have one same client and one tnsnames.ora.

the problem is caused by default domain in sqlnet.ora.

you said there is not need for that parameter. so i am thinking why even use it. it is not used for something else

do you agree?
Tom Kyte

Followup  

March 02, 2008 - 10:53 pm UTC

sigh.....

Ok, if you

$ sqlplus u/p@a


and

1) there is no default domain, then we look for A, just A, nothing else, only A in the tnsnames.ora (you don't have A, you have A.x.y.z)

2) there is a default domain, then we look for A.default.domain.from.sqlnet.ora in the tnsnames.ora (and yours did not match)

So, remove the default domain in your case (remember, you are only using A) and you will

FAIL

just as you are now. Because your tnsnames.ora has A.x.y.z, not just A, and the default domain you currently have isn't giving x.y.z (and removing it therefore will fix - nothing)....


[ora10gr2@dellpe admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /home/ora10gr2/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES)
[ora10gr2@dellpe admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /home/ora10gr2/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ora10gr2.x.y.z =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dellpe)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = ora10gr2)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

[ora10gr2@dellpe admin]$ sqlplus scott/tiger@ora10gr2

SQL*Plus: Release 10.2.0.2.0 - Production on Sun Mar 2 22:50:37 2008

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[ora10gr2@dellpe admin]$ sqlplus scott/tiger@ora10gr2.x.y.z

SQL*Plus: Release 10.2.0.2.0 - Production on Sun Mar 2 22:50:41 2008

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

scott%ORA10GR2>



You have to either

a) use the correct default domain
b) use A.x.y.z in your application
c) use just A in the tnsnames.ora (NO DOMAIN)

in short, you must ensure that

sqlplus u/p@a

resolves to a string in the tnsnames.ora after adding ANY default domain found in the sqlnet.ora


If you have no default domain, then you better find A in the tnsnames.ora, as you have no domain.





March 03, 2008 - 12:16 pm UTC

Reviewer: A reader

Tom:

Yes I was going to do either A or C.

Any advantages/disadvantages to either one. WOuld you prefer having a domain in sqlnet.ora.

a) use the correct default domain
c) use just A in the tnsnames.ora (NO DOMAIN)


2. I also found that some machines hae sqlnet.ora file deleted. Is not this a MUST file for oracle client.

Tom Kyte

Followup  

March 03, 2008 - 2:44 pm UTC

it is totally, entirely, 100% up to you whether you want the short name or the long name. bear in mind if you go "short" and someone ADDS the domain to sqlnet.ora you are back where you began...

if you use short name & sqlnet.ora has default domain - you are right here again.

so - maybe you just be explicit, you say to customer "you must use a.b.c.d in the connect string in the application and your tnsnames.ora must have a.b.c.d"


2) no, it is entirely optional. All of the files are - none are needed.

tns

March 03, 2008 - 2:45 pm UTC

Reviewer: A reader

TOm:

another small question.

I found that my machine does not have variable TNS_ADMIN defined. How does the client know where to look for in for the file tnsnames.ora?

Do i have to define a variable for TNS_ADMIN and tell it which directory to look for like

c:\ora92\network\admin
Tom Kyte

Followup  

March 03, 2008 - 8:52 pm UTC

it uses the default, the tns_admin is typically NOT set. It is not usual to find it set...

It defaults to oracle_home/network/admin

multiple database requires different SQLNET.ora setting

October 30, 2008 - 7:27 pm UTC

Reviewer: P from MA, USA

Hi Tom,

I have 2 database entry in my tnsnames.ora. For database #1, I need to have following entry in sqlnet.ora :

SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT= (MD5)
SQLNET.ENCRYPTION_CLIENT = required

If I keep the above entry in sqlnet.ora, I get error while login to database #2 and If I remove the sqlnet.ora file or comment the entries then I can login to database #2 but not database #1.

How do I resolve the issue ?

Tom Kyte

Followup  

November 02, 2008 - 4:11 pm UTC

you could have different sqlnet.ora's and a 'script' to set the environment right for to point to each one...

but - why doesn't database 1 just demand encryption and database 2 - not?

multiple database requires different SQLNET.ora setting

November 03, 2008 - 2:27 pm UTC

Reviewer: P from MA

Can you please tell me what should be in the script to do that and where I should store the script ?

database #1 is new production instance that requires some security features to be on to connect but other databases are development or other production systems that dont require any security set up.
Tom Kyte

Followup  

November 11, 2008 - 11:45 am UTC

you write the script, you can tell us where the network configuration files are by setting the TNS_ADMIN environment variable.

So, you write your own startup script, it'll set the TNS_ADMIN environment variable before starting the database instance, that instance will "inherit" your environment and will look for the configuration files where ever you pointed the TNS_ADMIN to.

tns

January 30, 2009 - 8:57 pm UTC

Reviewer: A reader

Tom:

What do you recommend for a situation where user client machines that have oracle client 9i software are getting upgraded to XP and the oracle directory will be protected (installed by admin account). A PB client app uses the oracle client to connect to database server. In order for us be able to edit that tnsnames.roa file without using admin account:

Would you consider
- installing tnsnames.ora on a different local directory and setting TNS_ADMIN to that local directory so you can updated as desired.
- let all 100 machines point to a shared server with the TNS_NAMES file. any disadavatages here.
- another better solution.
Tom Kyte

Followup  

January 31, 2009 - 8:26 am UTC

A better solution for lots of clients - use a centralized, replicated nameserver managed by the IT staff.

single fileshare = single point of failure.

100 copies on 100 desktops = manageability nightmare.


tns

January 31, 2009 - 10:24 am UTC

Reviewer: A reader

Tom:

what do you mean by replicated naemserver? is this different than any file server shared by users.

We would still have 100 copies of oracle client. no way around this.
but do you really see a need for changing tnsnames.ora other than when you change a server or database which does not happen frequently.

Can we also have it in a c:\myapp directory and have the TNS_ADMIN point to c:\myapp

Tom Kyte

Followup  

January 31, 2009 - 10:42 am UTC

Sort of like DNS for databases.

http://docs.oracle.com/docs/cd/B19306_01/network.102/b14212/concepts.htm#sthref216

see directory and external naming.



As for the 100 copies of the Oracle client, if you got to 10g clients, you could use the instant (small, very small) client.

the question is DO YOU see a name in YOUR environment for changing a tnsnames.ora - only you, in your environment, with your requirements, your needs, your restrictions, your abilities - can answer that.

January 31, 2009 - 11:02 am UTC

Reviewer: A reader

Tom:

thanks, it is 9i for the time being and will migrate to 11 g in 3-4 months.

I will look into the external and ldap directory option.

But just to confirm, would having a file on each client in c:\myapp\tnsnames.ora and setting TNS_ADMIN local environment to that directory work?

For the file server option, it seems it has to be a NIS or directory server. It is not a matter of storing a file on a share drive and setting TNS_ADMIN to point ot it. right?


Tom Kyte

Followup  

January 31, 2009 - 2:45 pm UTC

... But just to confirm, would having a file on each client in
c:\myapp\tnsnames.ora and setting TNS_ADMIN local environment to that directory
work?
...

yes.


...
For the file server option, it seems it has to be a NIS or directory server. It
is not a matter of storing a file on a share drive and setting TNS_ADMIN to
point ot it. right?
....

why wouldn't it be? you just need the file to appear as if it were on the users machine.

tns

January 31, 2009 - 8:21 pm UTC

Reviewer: A reader

TOm:

with directroy servers i think you need LDAp drivers and read the file entries using LDAP?

is "single point of failure" good or bad?
Tom Kyte

Followup  

February 02, 2009 - 10:10 am UTC

... is "single point of failure" good or bad? ...

are you joking?
http://www.google.com/search?q=define+single+point+of+failure



anyway, read the link I provided above to the networking documentation, it tells you what you can do, what needs to be done to do it, everything is there smk.

TNS

April 08, 2009 - 7:03 pm UTC

Reviewer: A reader

Tom:

Can you clarify how a power builder client application connect to a database server?

We user oracle client/tnsnames.ora to connect a client to a remote database server. Are we using the internet or web servers any way?

I thought only browser/web applications use internet but it sounds like the client will also hit the database server directly using TCP/IP and oracle net without the user of intermediary web server.
Tom Kyte

Followup  

April 13, 2009 - 3:32 pm UTC

not really sure what you mean by all of this....

the internet is a network, a tcp/ip network that existed long before a web browser was written, long before web servers existed.

It is a network, it is used by thousands of services, http is just one protocol out of hundreds - you have ftp, smtp, etc etc etc.

sql*net is our library of networking code that can use the tcp/ip network to communicate between a client and a server.

network

April 13, 2009 - 11:14 pm UTC

Reviewer: sam

Tom:

If you have a client machine connecting to a server using sql*net. how do i know what network it is using?

is it using the internet or some Local area network or private WAN? is it always using this network called the internet.
Tom Kyte

Followup  

April 14, 2009 - 11:09 am UTC

Sam - tell me what is different about the local network and the internet?


The internet is a network.
Your LAN is a network.

Your LAN might be connected to the broader internet via your internet service provider. Or your LAN might be connected to a WAN run by your company which in turn might be connected to the internet.


They are just tcp/ip networks - they all look the same to software. You give addresses (ip addresses) and maybe a port (Oracle uses 1521 by default, easily changed) and the software connects to that port on that address (using connected tcp/ip sockets of course, not going to get into UDP and other things - you can buy a book on networking if you are interested)

net

April 14, 2009 - 7:43 pm UTC

Reviewer: sam

Tom:

With LAN i have a PC and a server and a dedicated cable between them. I do not care if my ISP is down. I am still working. It is my own network.

With internet you are dependent on the ISP and your data is going from router to router before it hits the server.

So now you say "so what". it make a diff in securty issues but that is not what i am after.

I originally thought that internet is 90% http stuff. And most client/server communication is direct using a LAN. It seem now that if i have a remote machine that connects to the server, iam using the internet but without http. It is using Sql*net over tcp/ip communication.

correct?
Tom Kyte

Followup  

April 15, 2009 - 8:54 am UTC

You might want to pick up a book on networking if you are interested in how this all works.

There is no difference between the internet, your local area network, whatever.

They all have routing, they all have cables, wires, infrastructure.

If the machine you need to access is next to your desk, you can probably get to it without an ISP to hook you up to the broader network. If the machine you need to access is 50 miles away - you'll need a network provider in all likelihood - be it your employer with their own private network (which probably isn't theirs but a leased line from a telco) or a general ISP you buy the service from.

With the "internet" (which is JUST A NETWORK remember), you might be dependent on your ISP to "get on", but you are not dependent on your ISP to get to your server in general. Once you are on the 'internet', you can hit machines all over the place - whether they are serviced by your ISP or joe-schmoes ISP. The internet is just a big bunch of computers hooked to the same network.



Your thoughts on the 'internet' are entirely incorrect. Stop thinking of the internet as something "different from your LAN", it is not, it is an extension of it. It is *not* different from your LAN, it is the same - just 'bigger'.


tnsping gives wrong port

July 16, 2009 - 3:22 am UTC

Reviewer: Sikki from UAE

Hi Tom,

I have a Sun server with Oracle and non-oracle user and installed 9i & 10g. 10g listener is configured with port 1526 and 9i is with 6517. DB name - ctst - 9i.

If i say tnsping of ctst as oracle user it connects to right port 6517

TNS Ping Utility for Solaris: Version 9.2.0.7.0 - Production on 16-JUL-2009 10:55:39

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = TCP) (PROTOCOL = TCP) (HOST = itsun108.hq.emirates.com) (PORT = 6517))) (CONNECT_DATA = (SID = ctst)))
OK (20 msec)


same thing if i say as non-oracle user in the same server it uses 10g home, I am sure both 9i & 10g uses same tnsnames.ora.

as non-oracle user

TNS Ping Utility for Solaris: Version 9.2.0.7.0 - Production on 16-JUL-2009 06:57:34

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (Host = localhost) (Port = 1526)) (CONNECT_DATA = (SID = CTST)))
OK (20 msec)

How do I identify which tnsnames.ora the non-orale user using, please advice.

Thanks,
Sikki
Tom Kyte

Followup  

July 16, 2009 - 11:31 am UTC

... I
am sure both 9i & 10g uses same tnsnames.ora.
...

how are you sure, because I am *very* sure they are not.

On unix's - the search is usually (found via strace)

access("/home/tkyte/.tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory) look in current users home directory for .tnsnames.ora

access("/tmp/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory) my TNS_ADMIN environment variable was set to /tmp, so look there next

access("/etc/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory) let's try /etc for one...

access("/home/ora10gr2/network/admin/tnsnames.ora", F_OK) = 0 then look in $ORACLE_HOME/network/admin

stat64("/home/ora10gr2/network/admin/tnsnames.ora", {st_mode=S_IFREG|0640, st_size=1182, ...}) = 0
open("/home/ora10gr2/network/admin/tnsnames.ora", O_RDONLY|O_LARGEFILE) = 7
read(7, "# tnsnames.ora Network Configura"..., 4096) = 1182
stat64("/home/ora10gr2/network/admin/tnsnames.ora", {st_mode=S_IFREG|0640, st_size=1182, ...}) = 0





Sikki

July 19, 2009 - 6:52 pm UTC

Reviewer: Sikki from UAE

Excellent Tom, Found another hidden tnsnames.ora in non-oracle user home directory.

Could you please clarify is this the expected behaviour by Oracle taking hidden tnsnames.ora (.tnsnames.ora) in home directory instead of looking into $ORACLE_HOME/network/admin/tnsnames.ora?

I have checked in echo $PATH, but no where the home directory defined.

Tom Kyte

Followup  

July 24, 2009 - 11:31 am UTC

yes, it is the expected behavior

tnsnames.ora file lookup order

July 24, 2009 - 10:33 am UTC

Reviewer: Chad Worthington from Cincinnati, OH USA

What is the lookup order of the tnsnames.ora file? Is it OS dependent? Does Oracle [10g Client] on Windows look in the local working directory first? It appears to for our application. Is this the proper lookup order:
1. local working directory
2. TNS_ADMIN, if set
3. %ORACLE_HOME%\network\admin
We install different applications on WinXP WSs from diff. groups that all need to update the tnsnames.ora file and we don't have LDAP setup (LDAP best solution?). The apps are overwriting the file on one another and causing connection issues (since "my" entry was removed when they replaced the file).
Tom Kyte

Followup  

July 26, 2009 - 7:30 am UTC

.. Is it OS dependent? ...

yes, no such thing as ~/.tnsnames.ora on windows for example.


On windows it should be TNS_ADMIN and then in the Oracle Home.


Centralization has benefits - yes, definitely (in answer to LDAP)

regarding snmp in listener.ora file

September 07, 2009 - 3:45 am UTC

Reviewer: prasanna sahu from india

i need enable snmp ...
i m using 10.2

tnsnames.ora

March 05, 2010 - 2:02 am UTC

Reviewer: A reader

Hi Tom,

Is this possible in any situation? there are two entries in tnsnames.ora with same host, same port, only SID is different.
I just saw in one of the environment, and I am not able to understand.
Thanks in advance.

Regards,

Tom Kyte

Followup  

March 05, 2010 - 5:40 am UTC

why would this not be possible?

Yes, it is possible, it has to be - you can have more than one database instance on a given host.

It is not only possible, but is (unfortunately) a very very widespread practice (I say "one host, one instance")

Same Port

March 05, 2010 - 3:13 pm UTC

Reviewer: A reader

Thanks Tom.

I know about same host multiple instances, but my question was around same port for two instances on the same machine.

Thanks

Tom Kyte

Followup  

March 05, 2010 - 3:26 pm UTC

sure, one listener can service as many instances as you want on that machine.

The LISTENER has a port, the instances - not so.

One listener
many instances

Downside

March 05, 2010 - 5:37 pm UTC

Reviewer: A reader

Thanks Tom,
Is there any downside of this configuration (one listener to serve many instances)? i.e., if one has choice which is the recommended option?
Regards,
Tom Kyte

Followup  

March 09, 2010 - 10:26 am UTC

unless you have really high connect rates (that is the only time the listener is involved) it should be OK (and easier to manage than having many listeners)

and if you have really high connect rates, you should fix that - rather than setup more listeners, that is a big problem :)

Error

May 02, 2010 - 10:05 am UTC

Reviewer: A reader from India

Hi Tom,

I installed oracle 10g client on my system with administrator type.

My tnsnames.ora:

ORC10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.176.159.167)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orc10g)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

My listener.ora:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\client_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.176.159.167)(PORT = 1521))
)
)

when I give Scott, tiger, orc10g in sqlplus, I am getting no listener error.

when I tried to start listener, I am getting the following error:

D:\oracle\product\10.2.0\client_1\BIN>lsnrctl start listener
'lsnrctl' is not recognized as an internal or external command, operable program or batch file.

Please help

Tom Kyte

Followup  

May 06, 2010 - 11:25 am UTC

use the services to start things, you'll find the database and listener services there.

Client does not include listener

May 07, 2010 - 11:31 pm UTC

Reviewer: Mark Williams from The Midwest, USA

@A Reader

You say you installed the 10g client. However, the listener is not part of the client components - it is part of the server components.

Regards,

Mark
Tom Kyte

Followup  

May 08, 2010 - 7:42 am UTC

laughing out loud at myself.

Yes, of course.

Tnsnames/Connections pointing to multiple databases

April 08, 2011 - 8:55 am UTC

Reviewer: Thiru from NJ USA

Hi Tom,

Tried finding an answer to my question but got none. If you could kindly respond to this :

We have Business Direct application that points to Cold Fusion servers that in turn points to our Oracle Database. When connecting, CF has the port,sid and schema information. Is it possible to connect to two databases simultaneously. The intention is to make changes on both databases at the same time ( sequential is ok). The db name, schema name and port remains the same but on a different server.

Thanks for the time.
Tom Kyte

Followup  

April 12, 2011 - 3:34 pm UTC

Is it possible to connect to two databases simultaneously.

Absolutely, you would do that via a database link.

I do not recommend making changes to two databases in real time however, it compromises availability, it has performance implications. It would be (in my opinion) better to make the change on one database and schedule a job to propagate the change to another database shortly after - in the background.

but you can do it via a database link, we'll two phase commit it and everything for you.

service registration

August 17, 2011 - 11:13 am UTC

Reviewer: Jason

Supported services, that is, the services to which the listener forwards client requests, can be configured in the listener.ora file or this information can be dynamically registered with the listener. This dynamic registration feature is called service registration and is used by Oracle9i or Oracle8i instances. The registration is performed by the PMON process--an instance background process--of each database instance that has the necessary configuration in the database initialization parameter file. Dynamic service registration does not require any configuration in the listener.ora file.


Tom Kyte

Followup  

August 17, 2011 - 4:38 pm UTC

umm, no kidding? what was your point exactly?

tnsnames.ora and database which instance connect to.

April 11, 2012 - 3:58 am UTC

Reviewer: A reader

Hi tom,

My question is all about 'one instance connect to more than one database'.

I know it can but can only connect to 1 single at the same time.

I learned from some place that the way to make it happen is 'modify service_name of tnsnames.ora file', right?

My confusion is tnsnames.ora is only related to naming resolution, how can it affect which database instance is connecting to:

1. SQL> startup nomount
ORACLE instance started.

Total System Global Area  372449280 bytes
Fixed Size                  1336624 bytes
Variable Size             285215440 bytes
Database Buffers           79691776 bytes
Redo Buffers                6205440 bytes

2. !> ps -ef | grep ora
    proved the backgroup processes was started
 
So far, a instance(SGA+backgroup processes) is started but not connect to any database yet, right?

3. SQL> alter database mount;
   This will connect instance to a database, right?
   But this step is just to read control file, how comes tnsnames.ora is involved?
   Should we change control file somehow to make 'connect-to-another-database' happen?
   
Then i am thinking might be something happened from very beginning, so i did below:

1. !> export ORACLE_SID=noexist
2. !> sqlplus /noloc
3. SQL> conn /as sysdba
4. SQL> startup
   Error appears and indicate there is no initnoexist.ora
5. !> cp initexist.ora initnoexist.ora
6. !>sqlplus /nolog
7. SQL> conn /as sysdba
8. SQL> startup
   it can start the database successfully, a little bit strange, any idea?
  
So from the whole testing, i did not see any involving of file tnsnames.ora. 
But i noticed, when will issue 'startup nomount', it will refer to either spfile or init file, which has a '*.db_name=xxx'.
Does this mean before we start a instance, which database it will connect to has been finalized, we can only set the database
a instance connect to before start the instance, noway to change after the instance has been started and before mount it?
 

Tom Kyte

Followup  

April 11, 2012 - 11:31 am UTC

'one instance connect to more than one database'.

that is not true. An instance may mount and open a SINGLE database - ever. You have to destroy the instance and recreate it to open another database.

An instance is attached to a single database.
A single database may have zero, one or more instances attached to it.


3) that will mount a database - the tnsnames.ora file had nothing to do with it. You MIGHT have used the tnsnames.ora to connect to the instance in sqplus, but that tnsnames.ora won't direct the instance as to what database to mount. the init.ora or spfile.ora did that.


one 'intance' with multiple 'database'

June 01, 2012 - 12:29 am UTC

Reviewer: A reader

Hi tom,

Still have some confustion about the 'database' and 'instance'.

I am reading 'Expert-Oracle-Database-Architecture' and noticed below 
'By simply having many different parameter files, I can mount and open any one of these databases. Here, I
have one instance at a time but many databases, only one of which is accessible at any time'

I did below test:

1. i use DBCA to create another database named 'leo'
2. after DBCA fully finished, i base on 'ps -ef | grep smon' and see below. So it means 2 instances, right?
   oracle    9964     1  0 20:10 ?        00:00:00 ora_smon_leo
   oracle   27777     1  0 May22 ?        00:00:29 ora_smon_orcl
3. then i do below
   3.1 shutdown the new instance
    !> export ORACLE_SID=leo
    !> sqlplus /nolog
    SQL> conn /as sysdba
    SQL> shutdown immediate
    SQL> exit
   3.2 shutdown my existing instance named orcl
    !> export ORACLE_SID=orcl
    !> sqlplus /nolog
    SQL> conn /as sysdba
    SQL> shutdown immediate
    SQL> exit
   3.3 create a new spfile for 'orcl' whose content is new created 'leo'
 !> cd $ORACLE_HOME/dbs   
 !> mv  spfileorcl.ora spfileorcl.ora.bak
    !> cp spfileleo.ora spfileorcl.ora
   3.4 restart instance 'orcl'
       !> sqlplus /nolog
    SQL> conn /as sysdba
    SQL> select name from v$database;
         LEO
4. and from above result, it seems my existing 'orcl' instance connected to the new created database 'leo'.
   Is it what you are saying above in your book?

5. If i want to use instance 'orcl' to mount and open database 'orcl' again, i need to change the spfile
   back as what i did in step 3.3 reversely. Is there any simple way? Like utilize tnsnames.ora file?
   
6. Further question is: can we change the database to be mounted after i start the instance?
   My thought is we have steps nomount->mount->open, but we have to provide the parameter file before nomount
   it means before mount a database, instance already know what database it will link to.
   Why can not we do it at nomount stage?

Tom Kyte

Followup  

June 01, 2012 - 6:44 am UTC

2) correct, two instances - orcl and leo



4) as a result your NEW instance 'orcl' mounted a database 'leo'. An instance is a very transient thing. Once you shut it down - it disappears forever, never to be seen again. an instance is a set of processes and memory. If you shut it down - poof - it is gone.

when you start an instance, it is a brand new instance that never ever existed before and never ever will exist again.

5) the simple way is simply - put the right control files = and db_name = in your init.ora. those two things tell the newly created instance what database to access.

there is no networking involved here at all, none.

6) because the design decision was made that the parameter file will be processed once at startup time. It is not a bad decision - you should start your databases pretty infrequently.

SQL

May 20, 2014 - 10:12 am UTC

Reviewer: shankar from india

HI , Tom Pl.let me know which way we can learn better SQL tunings ..