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

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

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

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,

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 

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)
)
)

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 

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.

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.

you have no service called orc10g in the listener

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

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?

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

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

 

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

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

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

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

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

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

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

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.

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

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

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.

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!

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....)

 

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?

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

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

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.

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.

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?

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.

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???

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

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,

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?

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?

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?? ;))

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:

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:

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:

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:

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:

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,

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

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

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

yes

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

hmmm... this seems familiar...

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

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

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

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

Dawar

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.

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.

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>

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 =>

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

Thanks.

swb

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?

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

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!

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!

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:

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:

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?

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:

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:

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?

[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>

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:

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

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 ?

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:

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:

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:

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:

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:

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:

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:

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?

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

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.

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).

i need enable snmp ...
i m using 10.2

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,

Thanks Tom.

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

Thanks

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,

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

@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

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.

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.

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?
 

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?

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

I can see the amout of trouble shooting required to just too much for getting this database working

Product mangement need to make sure developers do their job.
I am seeing Oracle versions created like toys.
The development should maintian the product for at least 5 years instead of creating versions like a toy. It is not realistic.

The reality with customers are they are extreamly slow to adapt they have a lot more work to work on especially on the business side.
For example a lot of my customers are uing 11g and there a very few that have moved to 12c.

And this does not only apply to customers it applies to vendors...