multiple homes
Randy, August 29, 2001 - 10:03 am UTC
What is your suggestion for the location when using multiple homes? Should the tns files be specific to a home (for compatiblity)?
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
A reader, August 29, 2001 - 12:04 pm UTC
On most unix servers, if you have a common listener.ora file for multiple databases, the most common place is in /etc directory.
Tianhua Wu, August 29, 2001 - 10:20 pm UTC
use truss(unix) or strace(linux) on "tnsping service_name", you will see Oracle check several places.
multiple oracle home and one listener
suhail, August 15, 2004 - 4:03 pm UTC
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
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
Suhail, August 16, 2004 - 8:09 pm UTC
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
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
Suhail, August 16, 2004 - 8:58 pm UTC
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)
)
)
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
Suhail, August 17, 2004 - 8:52 am UTC
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
August 17, 2004 - 9:00 am UTC
lsnrctl status LISTENER_NAME
I have deleted 9idb still same problem
Suhail, August 17, 2004 - 10:03 pm UTC
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.
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
Suhail, August 18, 2004 - 7:57 am UTC
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.
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.
Dave, August 18, 2004 - 8:32 am UTC
you have no service called orc10g in the listener
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
Suhail, August 18, 2004 - 9:06 am UTC
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
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
Suhail, August 18, 2004 - 9:59 am UTC
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?
Good to know
Suhail, August 18, 2004 - 10:34 am UTC
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
Suhail, August 18, 2004 - 8:01 pm UTC
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
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?
Thaha Hussain, September 12, 2004 - 4:31 am UTC
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
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 ","
Nishant, November 18, 2004 - 10:28 am UTC
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
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)
Nishant choudhary, November 19, 2004 - 8:11 am UTC
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
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
Ashok Shinde, November 30, 2004 - 6:08 am UTC
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
November 30, 2004 - 8:00 am UTC
configure listener.ora
C:\> lsnrctl start
service appears.
Still Windows Service Error
Ashok Shinde, November 30, 2004 - 9:25 am UTC
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
November 30, 2004 - 9:38 am UTC
what might your listner.ora, sqlnet.ora look like
Ashok Shinde, December 01, 2004 - 7:16 am UTC
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
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
Ashok Shinde, December 02, 2004 - 12:44 am UTC
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
December 02, 2004 - 7:36 am UTC
are you running a firewall on your machine.
Got The Solution
Ashok Shinde, December 02, 2004 - 5:53 am UTC
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.
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
Mark A. Williams, December 02, 2004 - 9:33 am UTC
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
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.
Mark A. Williams, December 02, 2004 - 12:52 pm UTC
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
VAH, January 13, 2005 - 4:11 pm UTC
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.
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
A reader, January 15, 2005 - 8:11 pm UTC
OK
Raju, March 16, 2005 - 12:04 pm UTC
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!
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?
Richard, March 26, 2005 - 10:41 am UTC
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?
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 :-)
Tarry Singh, April 05, 2005 - 4:11 am UTC
Well tom, you are getting a hang of the messenger lingo.
u purty gud, u no :-)
Database Recovery
Victor Wandera, April 19, 2005 - 8:01 am UTC
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
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 ****
fethi FERJANI, May 02, 2005 - 4:47 am UTC
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.
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 ***
fethi F., May 02, 2005 - 5:11 am UTC
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.
May 02, 2005 - 8:33 am UTC
I know NOTHING about reports, otn.oracle.com -> forums
**** Listener Parameters to list ****
Fethi f, May 04, 2005 - 5:18 am UTC
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?
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
James McNaughton, May 08, 2005 - 12:45 am UTC
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.
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
fethi, May 10, 2005 - 3:47 am UTC
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???
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
A reader, May 24, 2005 - 2:42 pm UTC
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
May 24, 2005 - 3:57 pm UTC
the client needs to read the file. they need read access
Something more about tnsnames.ora file
Suvendu, December 08, 2005 - 8:04 am UTC
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
December 08, 2005 - 11:40 am UTC
this is their process they are researching, we don't have a job that does this.
Alexander the ok, December 08, 2005 - 4:53 pm UTC
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?
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.
Alexander the ok, December 09, 2005 - 9:20 am UTC
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
Alexander the ok, December 09, 2005 - 9:43 am UTC
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
PRASAD, January 09, 2006 - 6:00 am UTC
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.
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
A reader, September 14, 2006 - 10:23 am UTC
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,
September 14, 2006 - 10:44 am UTC
someone changed the tnsnames.ora file on the server it would sound like.
listener
A reader, September 14, 2006 - 11:38 am UTC
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.
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
A reader, September 14, 2006 - 4:21 pm UTC
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,
September 14, 2006 - 4:53 pm UTC
ahh, my favorite words "you were correct" :)
(kidding... glad you found it)
listener
A reader, September 15, 2006 - 4:15 pm UTC
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,
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
A reader, October 11, 2006 - 7:00 am UTC
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,
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?
Dawar Naqvi, October 18, 2006 - 2:12 pm UTC
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
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.
Dawar, October 18, 2006 - 4:50 pm UTC
is it possible to generate " sql*net trace and Log files " either on client or server?
tnsnames.ora, sqlnet.ora & listener.ora files are not exit
Dawar, October 25, 2006 - 5:57 pm UTC
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
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
Dawar, October 25, 2006 - 6:02 pm UTC
yes
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
Dawar, October 25, 2006 - 6:19 pm UTC
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
Mark A. Williams, October 25, 2006 - 7:32 pm UTC
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
Dawar, October 26, 2006 - 1:36 am UTC
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
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
Dawar Naqvi, November 19, 2006 - 2:57 pm UTC
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
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
Dawar Naqvi, November 19, 2006 - 3:45 pm UTC
I restart the database from the server and problem has solved.
Dawar
Multiple Oracle Homes
A reader, December 15, 2006 - 12:13 pm UTC
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.
December 15, 2006 - 12:14 pm UTC
same steps you would follow if there was just a 10g oracle home. nothing different.
my scenario
Stewart Bryson, February 06, 2007 - 4:21 pm UTC
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.
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.
Stewart Bryson, February 06, 2007 - 4:26 pm UTC
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
Stewart Bryson, February 06, 2007 - 5:01 pm UTC
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 =>
February 07, 2007 - 12:54 pm UTC
can you "ping" ironman.tra.....com ?
Firewall related
Stewart Bryson, February 06, 2007 - 5:43 pm UTC
Oracle Enterprise Linux installs by default with the "hardened" linux turned on. Had to allow 1521 through.
Thanks.
swb
Su Baba, March 12, 2007 - 5:13 pm UTC
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?
March 12, 2007 - 7:36 pm UTC
use quotes
or set TWO_TASK (on unix) LOCAL (in windows) in the environment.
Su Baba, March 13, 2007 - 12:52 pm UTC
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
March 13, 2007 - 3:27 pm UTC
set LOCAL in the environment like I suggested?
How to Identify where my tnsnames.ora file is?
Joe, May 16, 2007 - 2:48 pm UTC
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!
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?
Joe, May 16, 2007 - 2:50 pm UTC
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
A reader, February 29, 2008 - 12:50 pm UTC
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?
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
A reader, March 01, 2008 - 3:26 pm UTC
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.
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
A reader, March 02, 2008 - 1:11 am UTC
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?
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
A reader, March 02, 2008 - 11:39 am UTC
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?
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
A reader, March 02, 2008 - 5:19 pm UTC
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,
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.
A reader, March 02, 2008 - 10:40 pm UTC
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?
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.
A reader, March 03, 2008 - 12:16 pm UTC
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.
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
A reader, March 03, 2008 - 2:45 pm UTC
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
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
P, October 30, 2008 - 7:27 pm UTC
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 ?
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
P, November 03, 2008 - 2:27 pm UTC
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.
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
A reader, January 30, 2009 - 8:57 pm UTC
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.
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
A reader, January 31, 2009 - 10:24 am UTC
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
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.
A reader, January 31, 2009 - 11:02 am UTC
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?
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
A reader, January 31, 2009 - 8:21 pm UTC
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?
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
A reader, April 08, 2009 - 7:03 pm UTC
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.
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
sam, April 13, 2009 - 11:14 pm UTC
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.
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
sam, April 14, 2009 - 7:43 pm UTC
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?
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
Sikki, July 16, 2009 - 3:22 am UTC
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
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
Sikki, July 19, 2009 - 6:52 pm UTC
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.
July 24, 2009 - 11:31 am UTC
yes, it is the expected behavior
tnsnames.ora file lookup order
Chad Worthington, July 24, 2009 - 10:33 am UTC
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).
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
prasanna sahu, September 07, 2009 - 3:45 am UTC
i need enable snmp ...
i m using 10.2
tnsnames.ora
A reader, March 05, 2010 - 2:02 am UTC
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,
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
A reader, March 05, 2010 - 3:13 pm UTC
Thanks Tom.
I know about same host multiple instances, but my question was around same port for two instances on the same machine.
Thanks
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
A reader, March 05, 2010 - 5:37 pm UTC
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,
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
A reader, May 02, 2010 - 10:05 am UTC
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
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
Mark Williams, May 07, 2010 - 11:31 pm UTC
@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
May 08, 2010 - 7:42 am UTC
laughing out loud at myself.
Yes, of course.
Tnsnames/Connections pointing to multiple databases
Thiru, April 08, 2011 - 8:55 am UTC
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.
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
Jason, August 17, 2011 - 11:13 am UTC
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.
August 17, 2011 - 4:38 pm UTC
umm, no kidding? what was your point exactly?
tnsnames.ora and database which instance connect to.
A reader, April 11, 2012 - 3:58 am UTC
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?
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'
A reader, June 01, 2012 - 12:29 am UTC
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?
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
shankar, May 20, 2014 - 10:12 am UTC
HI , Tom Pl.let me know which way we can learn better SQL tunings ..
A reader, June 01, 2022 - 7:07 am UTC
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...