Thanks for the question, Avinash.

Asked: August 11, 2000 - 2:32 am UTC

Last updated: December 19, 2012 - 12:08 pm UTC

Version: 8.1.5

Viewed 10K+ times! This question is

You Asked

Hi Tom,
This is an urgent query. Please let me know if the port 1521 is default port for the TNSLinstener. I have a database server on port 1527 how can I make the clients connect on this port or can I have one listener service connect to listen for 2 servers. For further clarification, please take a look at the attached listener.ora file. Also let me know how I can check the names of listeners running on a server (the command).

Thanks and Regards,
Avinash

#
# This file is written by OFS
# on Fri Mar 10 12:24:13 2000
#
LISTENER =
(ADDRESS_LIST =
(ADDRESS=
(PROTOCOL= IPC)
(KEY= oracle.world)
)
(ADDRESS=
(PROTOCOL= IPC)
(KEY= ORCL)
)
(ADDRESS=
(PROTOCOL= IPC)
(KEY= EXTPROC0)
)
(ADDRESS=
(COMMUNITY= NMP.world)
(PROTOCOL= NMP)
(SERVER= SCCPRCI)
(PIPE= ORAPIPE)
)
(ADDRESS=
(PROTOCOL= TCP)
(Host= sccprci)
(Port= 1521)
)
(ADDRESS=
(PROTOCOL= TCP)
(Host= sccprci)
(Port= 1526)
)
(ADDRESS=
(PROTOCOL= TCP)
(Host= 127.0.0.1)
(Port= 1521)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = sccprci)
(SID_NAME = ORCL)
)
(SID_DESC =
(SID_NAME = extproc)
(PROGRAM=extproc)
)
)

CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = 0
STARTUP_WAIT_TIME_LISTENER = 0
PASSWORDS_LISTENER = (oracle)

Fslsccora =
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL=IPC)
(KEY=PRD)
)
(ADDRESS=
(PROTOCOL=IPC)
(KEY=PRD.WORLD)
)
(ADDRESS=
(COMMUNITY=SAP.WORLD)
(PROTOCOL=TCP)
(Host=SCCORA)
(Port=1527)
)
)

SID_LIST_Fslsccora =
(SID_LIST=
(SID_DESC=
(SDU=32768)
(SID_NAME=PRD)
(ORACLE_HOME=E:\orant)
(PRESPAWN_MAX=10)
)
)

STARTUP_WAIT_TIME_Fslsccora = 0
TRACE_LEVEL_Fslsccora = OFF
CONNECT_TIMEOUT_Fslsccora = 10

and Tom said...

If you are using "host naming" convention (this is a method that does not require the client to have a tnsnames.ora file at all. You must be using tcp, you must only have one default database per host. The client only needs to know the hostname of the server to connect) then yes, 1521 is the default and only port.

If you are using tnsnames.ora, the Oracle nameserver, or any other method to connec then no, 1521 is not a magic port, is not a default port. In this case, 1521 is simply the port used by "convention". The clients would, typically in their tnsnames.ora, connect to the listener on some specified port number. 1521 is the convention used by many people, it is neither mandatory or necessary.

$ lsnrctl status

is the command that will show you the status of your listener.

Rating

(178 ratings)

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

Comments

Status unknown

Reader, July 01, 2003 - 12:30 pm UTC

Tom, please see below. What is "status unknown" means? Thanks.

LSNRCTL> start
Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production
System parameter file is C:\oracle\ora92\network\admin\listener.ora
Log messages written to C:\oracle\ora92\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc
)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cvenkate-us.us.oracle.co
m)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Produc
tion
Start Date 01-JUL-2003 11:28:59
Uptime 0 days 0 hr. 0 min. 2 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File C:\oracle\ora92\network\admin\listener.ora
Listener Log File C:\oracle\ora92\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cvenkate-us.us.oracle.com)(PORT=1521
)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "practice.world" has 1 instance(s).
Instance "practice", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

July 01, 2003 - 12:45 pm UTC

the database hasn't come back and told the listener about these services yet. No one has used them. eg, when I start my listener:

[ora920@tkyte-pc admin]$ lsnrctl start

LSNRCTL for Linux: Version 9.2.0.3.0 - Production on 01-JUL-2003 12:42:17

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

Starting /usr/oracle/ora920/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 9.2.0.3.0 - Production
System parameter file is /usr/oracle/ora920/network/admin/listener.ora
Log messages written to /usr/oracle/ora920/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tkyte-pc)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 9.2.0.3.0 - Production
Start Date 01-JUL-2003 12:42:17
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /usr/oracle/ora920/network/admin/listener.ora
Listener Log File /usr/oracle/ora920/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tkyte-pc)(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

and after a couple of seconds

[ora920@tkyte-pc admin]$ lsnrctl status

LSNRCTL for Linux: Version 9.2.0.3.0 - Production on 01-JUL-2003 12:45:53

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 9.2.0.3.0 - Production
Start Date 01-JUL-2003 12:42:17
Uptime 0 days 0 hr. 3 min. 36 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /usr/oracle/ora920/network/admin/listener.ora
Listener Log File /usr/oracle/ora920/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tkyte-pc)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tkyte-pc)(PORT=8080))(Presentation=HTTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tkyte-pc)(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 "ora920" has 1 instance(s).
Instance "ora920", status READY, has 1 handler(s) for this service...
Service "ora920XDB" has 1 instance(s).
Instance "ora920", status READY, has 1 handler(s) for this service...
The command completed successfully
[ora920@tkyte-pc admin]$

the database came back and said "hey, I'm here!" No one used extprocs -- so it is "unknown" until used.

A reader, July 01, 2003 - 1:28 pm UTC

web browser direct connection

Reader, July 09, 2003 - 12:42 pm UTC

If time permits, could you please help me in finding more info on the subject connection using HTTP. Is there a doc link that you can provide me with regard to how to configure listener to accept http connections? It would be of great help. Also, what applications use this kind of listener configuration.

Thanks for your time.

July 09, 2003 - 1:47 pm UTC

see
</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96620/xdb24pro.htm#1656 <code>

how to configure listener?

Reader, July 09, 2003 - 5:05 pm UTC

Tom, I looked at the link but could not find info on how to configure the listener to accept http connection. Could you please help? Thanks.

July 10, 2003 - 9:58 am UTC

I asked Sean Dillon, our resident XML Technologist, to take a look at this and here's what he had to say:
---------------

Well, it depends on what you are trying to connect to... If you are just trying to use an HTTP listener, the database is shipped w/ Apache which sits in the code tree of the database in $ORACLE_HOME/Apache.  The other way, and the way I THINK you're talking about, is through XML DB to get to the XML DB Repository...

In Oracle9i RELEASE 2, Oracle has implemented the "Protocol Server" (which provides FTP, HTTP and WebDAV connection capabilities into the database) in the XML DB Repository.  In order to configure the protocol server, you use XML DB APIs to update the XML DB configuration file, /sys/xdbconfig.xml.  The reason you use APIs to update this file is because it doesn't live on your OS or in the $ORACE_HOME tree, it resides INSIDE the database in the XML DB Repository.  To see this config, submit the following in sql*plus:
SQL> connect system/blahblah
Connected.

SQL> set long 100000
SQL> set lines 120
SQL> select dbms_xdb.cfg_get().getClobVal()
  2    from dual;

DBMS_XDB.CFG_GET().GETCLOBVAL()
--------------------------------------------------------------------------------
<xdbconfig xmlns="http: //xmlns.oracle.com/xdb/xdbconfig.xsd" xmlns:xsi="http: //www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http: //xmlns.oracle.com/xdb/xdbconfig.xsd                                     http: //xmlns.oracle.com/xdb/xdbconfig.xsd">
  <sysconfig>
    <acl-max-age>900</acl-max-age>
    <acl-cache-size>32</acl-cache-size>
    <invalid-pathname-chars>,</invalid-pathname-chars>
    <call-timeout>300</call-timeout>
    <max-session-use>100</max-session-use>
    <default-lock-timeout>3600</default-lock-timeout>
    <resource-view-cache-size>1048576</resource-view-cache-size>
    <protocolconfig>
      <common> *cut for brevity * </common>
      <ftpconfig>
        <ftp-port>2100</ftp-port>
        <ftp-listener>local_listener</ftp-listener>
        <ftp-protocol>tcp</ftp-protocol>
        <session-timeout>6000</session-timeout>
      </ftpconfig>
      <httpconfig>
        <http-port>8080</http-port>
        <http-listener>local_listener</http-listener>
        <http-protocol>tcp</http-protocol>
        <session-timeout>6000</session-timeout>
        <server-name>XDB HTTP Server</server-name>
        <max-header-size>16384</max-header-size>
        <max-request-body>2000000000</max-request-body>
        <servlet-realm>Basic realm=&quot;XDB&quot;</servlet-realm>
        <webappconfig> * cut for brevity * </webappconfig>
      </httpconfig>
    </protocolconfig>
  </sysconfig>
</xdbconfig>

This SHOWS YOU what your ftp and http configuration is.  Specifically, /xdbconfig/sysconfig/protocolconfig/httpconfig/http-port/text() is the HTTP port your protocol server is listening on :-).  You can try to connect in a web browser by specifying the hostname of your database server along w/ the port of your protocol server's http setting, i.e. slaphappy.us.oracle.com:8080/.  This will prompt me for a username/password (it's a DATABASE, after all ;-)), and once I successfully authenticate I'm connected to the XML DB Repository.

To edit this configuration, you call an XML DB API:

DBMS_XDB.CFG_UPDATE( newconfig SYS.XMLTYPE )

You pass the new xdbconfig.xml file TO this procedure, which updates the configuration.  This may be a bit daunting if you're not used to using Oracle's XMLType, so here's an example of using this call to update the FTP & HTTP ports of the protocol server:

begin
  dbms_xdb.cfg_update(
    updatexml(updatexml(dbms_xdb.cfg_get(),
                        '/xdbconfig/descendant::ftp-port/text()', '2121'),
              '/xdbconfig/descendant::http-port/text()', '19090'));
end;
/

This updates the FTP port to 2121 and the HTTP port to 19090.  For more information on the database's protocol server, the FTP, HTTP and/or WebDAV connection settings, XML DB, etc... see the XML Database Developer's Guide in the Oracle9i Release2 documentation set (available on TechNet (otn.oracle.com).  In THAT document, Chapter 19 and Appendix A are YOUR friends :-).  I hope this has helped, good luck!

________________________________________________________
Sean Dillon - Principal Technologist, Oracle Corporation
Author "Beginning Oracle Programming" www.amazon.com/exec/obidos/ASIN/186100690X

wow! Great stuff

Reader, July 12, 2003 - 2:23 pm UTC

Thanks

ipc protocol

Reader, July 12, 2003 - 2:30 pm UTC

Tom, What is an IPC protocol and where and how it is used? I have experience only in TCP/IP protocol. Is there any advantage in using IPC over TCP? Thanks.

July 12, 2003 - 2:48 pm UTC

IPC is interprocess communication.

you have messages, pipes, socketpairs and so on -- it is alot like just using sockets with tcp/ip.

ipc is generally limited to "a machine", not over a network.

ipc used to be a tad faster then tcp but recent tests have shown this to be less and less true.

ipc

Reader, July 12, 2003 - 3:39 pm UTC

So, does it mean that tnsnames.ora configuration on clients machines can never contain an entry for listener to receive connection for IPC protocol? If this is true, where exactly this is configured? My understanding is that listener can listen on both TCP/IP and IPC protocol? Thanks.

July 12, 2003 - 6:37 pm UTC

it is configured in the listener and used for local processes, like external procedures for example or local connections on the same machine.

external procedures?

Ramu, July 12, 2003 - 8:00 pm UTC

I am new to oracle DBA. When you say, external procedures, are they not part of database? Could they be written in C or Java? Could you please help me understanding about how listener can listen for external procedures -- any example of listener config for ext procedures would be helpful. Thanks.

July 12, 2003 - 9:02 pm UTC

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

lsnrctl status unknown

Mark A. Williams, July 19, 2003 - 12:32 pm UTC

Metalink note 151223.1

An UNKNOWN status means that the instance is registered statically (with a SID_LIST) in the listener.ora file rather than dynamically with service registration. Therefore, the status is not known. The SID_LIST in the listener.ora file is not required. One of the reasons why Oracle recommends not using one, is because it may cause un-necessary overheads on the listener process. The SID_LIST section only needs to be used for instances such as PLSExtProc. It is not normally a problem having the instance duplicated in the SID_LIST, but it is preferable not to have it.

HTH,

Mark

Great Help

Kuo-Chun, July 19, 2003 - 6:03 pm UTC

Tom/Mark:

Thanks for the help. I was wondering about the dynamic service registration and now I know what it is.

No listener, but can connect to the database!!

Praveen KV, July 24, 2003 - 10:10 am UTC

Hi Tom,

1)
Here I have a very interesting problem. I am using Oracle version 9.0.1.1 on Windows 2000. I stopped the
listener service from control panel->services.
Still I am able to connect to the database(ie without using the service name - like 'connect scott/tiger')! But when I infact started the listener service and used the service name-ORCL (ie, 'connect scott@ORCL/tiger') to connect to the database, I get the 'TNS: no listener" error!!!

When I intially created the database (name=ORCL), I created the listener and configured a service named ORCL to this listener (using Net Manager). I was able to connect to the database with and without using a service name. But later I installed Oracle 9iDS and then created additional listeners and services and then uninstalled them. Since then this strange thing happens. How is it possible to connect to the database WITHOUT a listener at all.

2) A similar problem had existed with Oracle 9i DS also. I was able to create a forms application using Oracle9i Forms.
I can compile it successfully. But when I run the application (web form), the applet gets started, and asks for log in information - but then 'TNS: protocol adaptor error'. JInitiator version is 1.3.1.9. Is this because of
version difference between the Oracle DataBase server(9.0.1.1) and Designer Suite(9.0.2.1). Or am I a complete dump about the listener concepts?

July 24, 2003 - 3:52 pm UTC

well, scott/tiger needs no listener and scott@ORCL/tiger does

so, no mystery there, it would be a mystery if the second worked.....

the listener is ONLY used when you use @dbname

as for july 09, 2003

j, December 12, 2003 - 3:32 pm UTC

begin
dbms_xdb.cfg_update(
updatexml(updatexml(dbms_xdb.cfg_get(),
'/xdbconfig/descendant::ftp-port/text()', '2121'),
'/xdbconfig/descendant::http-port/text()', '19090'));
end;

doesn't work (with my 9.2.0.1), but something like:

update XDB.XDB$Config
set SYS_NC_RowInfo$ = UpdateXML(SYS_NC_RowInfo$, '/xdbconfig/descendant::http-port/text()', '19090').

sets the http-port as needed.

please, tell me: how can one get the first statement to work? is there anything wrong with the second approach?

December 13, 2003 - 11:26 am UTC

tell me what "does not work" mean???

also -- don't even think about updating system tables unless support tells you to. You have NO idea what ramifications/side effects that may cause. don't even dream about going there.

j., December 15, 2003 - 6:07 am UTC

well, logged on as SYS i get ORA-06550 (updatexml has to be declared), which i do not get in case i use updatexml in a SQL statement.

December 15, 2003 - 8:08 am UTC

do not log in as sys. you never want to log in as sys. sys is special. don't do it.

connect "as sysdba" to do maintenance (startup, shutdown, backups and the like) -- install software when directed to install as sysdba

but nothing else. NOTHING else.

j., December 15, 2003 - 11:03 am UTC

that 's clear. but question remains: what do i have to do in order to get the valid approach (using DBMS package in conjuntion with updatexml) to work? how do i get the "updatexml must be declared" exception for that? isn't updatexml a built-in function?

please help.

j.

j., December 18, 2003 - 7:29 am UTC

does one have to get "special privileges" granted to get this update to work?

December 18, 2003 - 11:15 am UTC

One of the guys I work with saw this and says:

Tom,

I was on asktom looking for why the function updatexml is giving me problem and found this question

The last question is the same I was looking for and the aanswer is that theres bug the doesnt allow updatexml from plsql so to answer the persons question

So to change the http/ftp port od XML DB use the following

declare

v_cfg xmltype;

BEGIN

select updateXML(

updateXML(DBMS_XDB.CFG_GET(),

'/xdbconfig/descendant::ftp-port/text()', '2121'),

'/xdbconfig/descendant::http-port/text()', '19090')

into v_cfg

from dual;

DBMS_XDB.CFG_UPDATE(v_cfg);

END;

/

Kris Rice....

what privileges does kris have?

j., December 18, 2003 - 4:31 pm UTC

i 've tried the snippet and got ORA-31050: access denied ORA-06512: in "XDB.DBMS_XDB", line 168.

granting execute on XDB.DBMS_XDB (with grant option) didn't help.

can you please provide me with a solution for that issue and the reference of the bug mentioned?

thank you very much in advance.

December 19, 2003 - 9:37 am UTC

Hi, This is kris.

my account was granted DBA.

has kris got more?

j., December 20, 2003 - 1:05 pm UTC

i create a new user, granted connect, logon and dba.

execution of the abovementioned script caused:
ORA-00604 - error occurred at recursive SQL level
ORA-00942 - table or view doesn't exist

???

there must be another difference between SYS and kris

j., December 22, 2003 - 2:14 am UTC

'cause SYS succeeds ...

Could you please give me a hand on how to achieve the goal the RIGHT way?

j., December 28, 2003 - 8:06 am UTC

it just seems to be a matter of privileges, isn't it?

December 30, 2003 - 7:17 pm UTC

I ran into the same problem you have on my 9.2.0.1 database.  there is a bug in the DB in 9.2.0.1.  I upgraded it to 9.2.0.4 and everything worked as follows.

I suggest upgrading to 9.2.0.4 which is the latest patchset available for the 9.2 series.

Here's what i did....
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> drop user klrice_xdb;

User dropped.

SQL> create user klrice_xdb identified by k;

User created.

SQL> grant dba,connect to klrice_xdb;

Grant succeeded.

SQL> conn klrice_xdb/k
Connected.
SQL> declare
  2  
  3   v_cfg xmltype;
  4  
  5  BEGIN
  6  
  7   select     updateXML(
  8  
  9                  updateXML(DBMS_XDB.CFG_GET(),
 10  
 11                           '/xdbconfig/descendant::ftp-port/text()', '2121'),
 12  
 13                           '/xdbconfig/descendant::http-port/text()', '19090')
 14  
 15     into v_cfg
 16  
 17     from dual;
 18  
 19    DBMS_XDB.CFG_UPDATE(v_cfg);
 20  
 21  END;
 22  /

PL/SQL procedure successfully completed.

SQL>

listener port

atul, December 29, 2003 - 12:57 pm UTC

Hi,

How to know which port address database is using to listen?
apart from listener.ora or tnsnames.ora??

Thank.
Atul

December 29, 2003 - 1:10 pm UTC

that is pretty much it.

you use the listener.ora to tell the listener what port to use. thats it.

question on SID_LIST in listener.ora

A reader, December 30, 2003 - 10:42 am UTC

Mark wrote ..
"Metalink note 151223.1

An UNKNOWN status means that the instance is registered statically (with a
SID_LIST) in the listener.ora file rather than dynamically with service
registration. Therefore, the status is not known. The SID_LIST in the
listener.ora file is not required. One of the reasons why Oracle recommends not
using one, is because it may cause un-necessary overheads on the listener
process. The SID_LIST section only needs to be used for instances such as
PLSExtProc. It is not normally a problem having the instance duplicated in the
SID_LIST, but it is preferable not to have it.
"

However, while using agentctl, some of my friends mentioned that discovery of the node will get in trouble if you don't have SID_LIST. It looks in /var/opt/oracle/oratab and listener.ora. Sure enough, I did not see some entries. Added SID_LIST, stopped/started the agent the network/agent/services.ora looks better and OEM can see the database on the node as well. Any comments.

Thanks

Listner port

Madhu, December 31, 2003 - 9:05 am UTC

Atul,
You can also try using tnsping command , which displays the port being used .

C:\>tnsping idwdt301

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 31-DEC-2003 09:00:06

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

Used parameter files:
C:\Oracle\Ora91\network\admin\sqlnet.ora

Used ONAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=TCP.na.gpcom)(PROTOCOL=TCP)(Host=bdhp423
1.na.gp.com)(Port=1521)))(CONNECT_DATA=(SID=idwdt301)))
OK (10 msec)

Thanks,
Madhu

nt-service for oracle-http-server doesn't start anymore ...

j., January 07, 2004 - 10:59 am UTC

as for december 28, 2003: thank you very much for your help. applying the patch solved the problem (ports can now be set via DBMS package).

but: since the upgrade the oracle-http-server doesn't start anymore, and no error message is reported.

could you please supply me with another hint?

nt-event-log-entries for failing http-server-startups

j., January 07, 2004 - 11:14 am UTC

The Apache service named c:\programs\oracle9i\Apache\Apache\apache.exe reported the following error:
>>> FastCgiServer: redefinition of a previously defined FastCGI server "c:/programs/oracle9i/bin/isqlplus" <<<
before the error.log file could be opened.
More information may be available in the error.log file.

unfortunately there 's no such error log file available ...

issue "solved"

j., January 07, 2004 - 11:24 am UTC

one of the entries following the abovementioned one pointed to a syntax error in line 92 of file isql.conf (which in turn includes "FastCgiServer"). this file was untouched. commenting out this line made at least the http-server run again ...

Use one listener in either oracle 8i or 9i in PC for two servers to work

David Jiang, January 29, 2004 - 4:48 pm UTC

Tom,

I know you can setup the TNS_NAMES in PC'regedit to point oracle8i to use oracle9i's listener so that only one listener in oracle9i is used to function for both. But is there any way you can set this up without TNS_NAMES, such as modify the listener.ora file or change the NET Manger? Please show me how to do, I know you can but I am confused with the logic behind it and the way to do it. Is it the same way you do in UNIX machine? Thanks a lot.

January 30, 2004 - 7:59 am UTC

tnsnames.ora has nothing to do with the listener setup.

tnsnames.ora tells a client what port to connect to on what host and send what service name. It tells the listener nothing.

you can ONLY set this up with the listener.ora. You just have multiple entries in there (as you probably already do)

Need more explanation

davjia@auto-trol.com, January 30, 2004 - 10:38 am UTC

Tom, thanks for your prompt answer. Here is why I ask you the question. I have one Oracle8i and one Oracle9i servers in the same machine. Oracle8i listener is not on, Oracle9i is on. Here is the listener.ora:
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = republic)(PORT = 1521))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\Oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = REP9R.DEV.CENTRA.AUTO_TROL.COM)
(ORACLE_HOME = D:\Oracle\ora92)
(SID_NAME = REP9R)
)
(SID_DESC =
(GLOBAL_DBNAME = REP8B.DEV.CENTRA.AUTO_TROL.COM)
(ORACLE_HOME = D:\Oracle\ora81)
(SID_NAME = REP8B)
)
)
so now the client can connect both Oracel 8i and 9i if they have the right connection setting in the tnsnames.ora.
BUt it still works the same after I remove the Oracle8i setting part from Listener.ora file. Can you tell me WHY or I miss something here? Now the listener.ora is like below:
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = republic)(PORT = 1521))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\Oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = REP9R.DEV.CENTRA.AUTO_TROL.COM)
(ORACLE_HOME = D:\Oracle\ora92)
(SID_NAME = REP9R)
)
)

January 30, 2004 - 10:59 am UTC

because databases know how to register themselves with a listener. The database registers with the listener when the database is configured to do so.

Your listener.ora file can be virtually "empty" if you like.

listener.ora empty?

Reader, January 30, 2004 - 11:56 am UTC

If I have named my listener as listenertest, how does lsnrctl utility know to start my listener (for ex: lsnrctl start listenertest) if I did not specify it in listener.ora file? I don't understand how listener.ora can be empty? Thanks.

January 30, 2004 - 7:46 pm UTC

it is called "defaults".

defaults to "hostname" and port 1521

then, databases come register themselves with it and you can use it to get to the databases.

Question regarding the "virtual" Listener

David, January 30, 2004 - 3:54 pm UTC

Tom,

What you explain here is even confusing. This virtual listener only works if you have one oracle instance in one machine and you log on from the server. It does not work if you have multiple instances eg. Oracle8i and Oracle9i in the same machine, or if you connect from a client via SQLNet. Please give me some pratical example if I am wrong. No theory please!

January 30, 2004 - 8:05 pm UTC

what theory?

many servers can register with that listener. you set it up in the init.ora for the database.

Tom is right

reader, January 30, 2004 - 6:16 pm UTC

Yes, PMON registers all of these info with the listener. No need to specify info for services and sids in the listener.ora file anymore.

Thanks a lot.

David, February 02, 2004 - 4:49 pm UTC

Tom,

Very good and useful information. Thanks a lot.

Authenticating Users

Jayesh Antony Jose, March 08, 2004 - 8:25 pm UTC

Hello Tom,

Which process is responsible for authenticating a user when he is connecting to oracle server
1) thru listener
2) by IPC

Is it done by the already running server process or the newly spawned server porcess?

March 09, 2004 - 10:51 am UTC

the dedicated/shared server you get connected to does this regardless of the path you take.

Password Verfification using Oracle

Jayesh Antony Jose, March 10, 2004 - 1:56 am UTC

Hello,
Suppose the user is providing invalid username and password and if the server is starting a newprocess for validating the user, will it be a unnecessary operation.
Will it be better if we use the already server processes (background)which are running on the server to validate the user and start a new one only after authentication...?

March 10, 2004 - 8:56 am UTC

unecessary means "something that was not needed"

in order to authenticate...
you need a physical connection (since the information you need to authenticate is in the database)...
a physical connection requires a process...

Besides, why optimize for the uncommon - infrequent case here. You would hope that 99.999% of your authenticates actually "suceed"

Authentication

Jayesh Antony Jose, March 10, 2004 - 10:14 am UTC

What I meant to say is can't we use the already running background server process(around 8 are there) for this authentication?

March 10, 2004 - 3:35 pm UTC

and so for the 99.999% of the time you do successfully connect you want to bother in a serial fashion dbwr or lgwr (who have important jobs to do) to "authenticate" someone?

No, it would not be wise to make the authentication process a long and winding road when it almost always results in "success".

Listener for external procedure

Reader, June 05, 2004 - 8:44 pm UTC

Is it recommended to have a separate listener for external procedures? If so, can I create a OS user (not part of DBA group) and the listener for external procedure be started using this OS user account rather than starting the listener as user 'oracle'? I am thinking of doing as below. Could you please check? Thanks.
(1) Create a OS user not part of dba group
(2) Provide only read priv to this OS user to listener.ora file
(3) Provide executable priv on listener executable file for this OS user
(4) Create a listener with a name say, listenerEXTPROC
(5) Start listenerEXTPROC from this OS user account

Thanks.

June 06, 2004 - 10:47 am UTC

recommended? Yes/No/Maybe.

depends on your environment, on your needs. If you want extprocs to run as "user x", instead of the oracle software account -- then yes, it would be recommended. If you don't care or need to have the extprocs run as Oracle -- then no, it would not be.

updatexml not available in pl/sql

malcolm, June 15, 2004 - 12:48 pm UTC

This a followup to Sean Dillon's followup.

The code snippet (which is copied from the 9i XDB User's Manual) doesn't work on 9iR2 because updatexml is only a SQL function, not yet a PL/SQL function.

From metalink: The enhancement request number is 2295055 titled "XMLTYPE FUNCTION UPDATEXML NOT AVAILABLE IN PL/SQL BLOCK". 

SQL>begin
  2    dbms_xdb.cfg_update(
  3      updatexml(updatexml(dbms_xdb.cfg_get(),
  4                          '/xdbconfig/descendant::ftp-port/text()', '2121'),
  5                '/xdbconfig/descendant::http-port/text()', '19090'));
  6  end;
  7  /
    updatexml(updatexml(dbms_xdb.cfg_get(),
    *
ERROR at line 3:
ORA-06550: line 3, column 5:
PLS-00201: identifier 'UPDATEXML' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

SQL>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

A workaround is to use NDS like this:

  1  declare
  2     xdb_config sys.xmltype;
  3  begin
  4     execute immediate
  5        'select updatexml(dbms_xdb.cfg_get(), ' ||
  6                          '''/xdbconfig/descendant::http-port/text()'', ''8081'') ' ||
  7        '  from dual' into xdb_config;
  8     dbms_xdb.cfg_update(xdb_config);
  9* end;
SQL>/

PL/SQL procedure successfully completed.

Hope that helps somebody.

Oracle Developer

Dawar, August 25, 2004 - 3:54 pm UTC

Tom,

We have some time problem in our production.
It is 7.3.4 database.
Prior to me consultants install this database.

They also built some sub programs.
Users just select those problem from the menu and run on daily basis.

I do not know exact location of all programs yet.

If we have any problem in this sub -program. what is the best place to find the error.

Is Listener.log best palce to find errors?
If not, where I should go to see errors?

Actually iopened listenere.log and find very old logs since they implement oracle 7.3.4 (1999).

/disk/app/oracle/product/7.3.4/network/log
dhs3 46: ls
listener.log witsdev.log

What is the used of Listener.log?
we have Sun Solaris 5.5 for our 7.3.4 Database.

Any feed back will be appreciated.

cheers,
Dawar

August 26, 2004 - 9:06 am UTC

I don't know what you mean by "sub program" exactly -- but the listener log just is a list of connects to the database, what tried to connect to the database and when. just a log.

Oracle Developer

Dawar, August 26, 2004 - 11:15 am UTC

My mean from sub program is for any packages, procedure or function to perform any action.

Is there any way to find out the cause of error in any routine programs?

Regards,
Dawar

August 26, 2004 - 11:25 am UTC

besides reading the error message? still not sure what you are looking for. if the procedure throws an exception, it would be normal to have the error message printed out -- and then you read it and figure out what is wrong.

Need Help

A reader, September 01, 2004 - 6:59 pm UTC

Hi Tom,
We want to change our default port to some other port other than 1521. For this reason I have created a new listener and configured it. I created a new service to listen on this port with shared server configuration. But it does not connect giving the error TNS:12519 or TNS:12523.
It was understandable as the dispatchers were registered with the default listener. So I changed it using the Local_listener initialization parameter. And it worked.
But I wanted to try another way.
I wanted to configure the dispatcher to register with 1521 and the other listener as well.
So I issued alter system set dispatchers = '(PROTOCOL=TCP) (dispatchers = 4)(listener = listener)','(PROTOCOL=TCP) (dispatchers = 4)(listener = newlist)';
But it generated errors saying that the syntax is incorrect.
I tried a lot of combinations but to no avail. Please help me out to resolve this.
Thanks as always..

September 01, 2004 - 8:57 pm UTC

why would you run *two* listeners?

but, that aside, you can use static registration, like we always did in 7x and 8.0 - right in the listener.ora?

listener not available.

APL, September 10, 2004 - 3:20 am UTC

We are running some PB application. All the applications are using a single listener which is in oracle8.1.7_64 bits version. Applications are using 8i and 9i databases which contains 32 and 64 bits. The problem is, after running some applications listener is shutting down automatically. So we need to start it manually every time. Can You recommend any solution for this problem?

September 10, 2004 - 8:35 am UTC

please contact support.

8i Listener for 9i Database

Naveen.C, September 10, 2004 - 9:30 am UTC

I am also facing the the same problem
I have ORACLE 8.1.7.4 version TNSLSNR and ORACLE 8.1.7, 9.2.0.5 database 64bit and 32 bit. The listener crashes in between. Some documents says lsnrctl below 9.2 do have this problem if 32 bit and 64 bit databases are there are same time.
Will upgrading the listener to 9i Rel 2 do any good.. ??

September 10, 2004 - 9:41 am UTC

please contact support.

(you know, i confused why this isn't the first thing people would do? this is what support is really good at, what support does, what support was designed for. I can answer "how to" questions -- things they cannot. they resolve configuration, installation, "crashes" and things like that)

I upgraded

Naveen.C, September 24, 2004 - 12:17 am UTC

Hi.
I didn't contact the support
I upgraded the listener and it worked.

I feel the Dynamic Registration feature of 9i was creating the problem with 8i Listener.

I got the clue when I constantly monitored the STATUS of listener and it was ever increasing.. One database having about 4 entries.. !!! and after it grows a certain limit the listener crashes..

Thanks

September 24, 2004 - 10:45 am UTC

You are always supposed to use the listener of the highest versioned database.

Support, upon asking you questions like "so what versions you got going there" would have answered this straight away. In fact, if I had read it closer, i would have said that too -- it is just that this was so "clearly something for support" and this ain't support -- so i skipped over it.

duplicate value in Listener !

A reader, October 26, 2004 - 5:19 pm UTC

Hi Tom,

I have the same problem that one guy had before. When I run lsnrctl status the result is:

"
oracle@dev2> lsnrctl status

LSNRCTL for HPUX: Version 9.0.1.2.0 - Production on 26-OCT-2004 16:42:33

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for HPUX: Version 9.2.0.1.0 - Production
Start Date 26-OCT-2004 16:16:58
Uptime 0 days 0 hr. 25 min. 34 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /opt/oracle/product/9.2.0/network/admin/listener.ora
Listener Log File /opt/oracle/product/9.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dev2)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dev2)(PORT=8080))(Presentation=HTTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dev2)(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 "dev2" has 1 instance(s).
Instance "nmdb01", status UNKNOWN, has 1 handler(s) for this service...
Service "nmdb01.cbc.ca" has 2 instance(s).
Instance "nmdb01", status READY, has 1 handler(s) for this service...
Instance "nmdb01", status BLOCKED, has 1 handler(s) for this service...
Service "testdb" has 2 instance(s).
Instance "testdb", status UNKNOWN, has 1 handler(s) for this service...
Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdbXDB" has 1 instance(s).
Instance "testdb", status READY, has 1 handler(s) for this service...
The command completed successfully
"

As you see, I have two rows for each DB. You mentioned that I need to remove them from listener.ora, however, when I comment them out in listener.ora, my client can not connect to DBs. Here is listener.ora

"
# LISTENER.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dev2)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = testdb)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
(SID_NAME = testdb)
)
# (SID_DESC =
# (GLOBAL_DBNAME = dev2)
# (ORACLE_HOME = /opt/oracle/product/9.0.1)
# (SID_NAME = nmdb01)
# )
)
"

and here is tnsnames.ora:

Dev2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dev2.tor.cbc.ca)(PORT = 1521))
)
(CONNECT_DATA =
(SID = nmdb01)
)
)

I am using Oracle9.2 on HP-UX.

1) Why do I get two lines for each db? How can I remove one?

2) when I uncomment 'dev2' in listener.ora, I can connect from client. but either way (I mean having 'dev2' in listener.ora or not having that) , I can not connect from the local sever with SQLPLUS. (I already added 'dev2' to tnsnames.ora on the server)

Thank you so much for your info,
- Arash

October 26, 2004 - 6:21 pm UTC

no, that is totally different -- you have "two instances", but only one service.

they had two SERVICES. totally different.

are you setting up for automatic registration? do you have two databases with the same names pointing to this listener.

A reader, October 26, 2004 - 8:56 pm UTC

I have two Oracle on that box.
1- 9.0.1
2- 9.2.0

I don't have the same db name. 'testdb' is 9.2 and 'dev2' is 9.0

I need to have both Oracle up for a while and eventually I'll remove 9.0

my questions are:

1- If there are two, how can I disable one of the listeners. ideally listener 9.0

2- how can I set 9.2 listener as a default .

generally what is the solution to resolve this confliction on HP-UX?

Thanks again for your time,
- Arash

October 26, 2004 - 9:06 pm UTC

you have only one listener.

2 databases registering as the same service there.

so, what are your init.ora parameters set to regarding service_names

A reader, October 26, 2004 - 10:58 pm UTC

Oracle 9.0 service_names is : nmdb01.cbc.ca

oracle 9.2 service_names is : testdb

HTH.

Thanks

A reader, October 26, 2004 - 11:37 pm UTC

Tom,

I checked listener.ora for both oracle 9.0 and oracle9.2

They had one db in commen :
SID_LIST_LISTENER =
(SID_LIST =
# (SID_DESC =
# (SID_NAME = PLSExtProc)
# (ORACLE_HOME = /opt/oracle/product/9.0.1)
# (PROGRAM = extproc)
# )

after I comment it out and restart the listener I got just one line for each db,

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dev2)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dev2" has 1 instance(s).
Instance "nmdb01", status UNKNOWN, has 1 handler(s) for this service...
Service "testdb" has 1 instance(s).
Instance "testdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

However, when I make the first connection to either of DBs, I get this:

Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dev2" has 1 instance(s).
Instance "nmdb01", status UNKNOWN, has 1 handler(s) for this service...
Service "nmdb01.cbc.ca" has 2 instance(s).
Instance "nmdb01", status BLOCKED, has 1 handler(s) for this service...
Instance "nmdb01", status READY, has 1 handler(s) for this service...
Service "testdb" has 2 instance(s).
Instance "testdb", status UNKNOWN, has 1 handler(s) for this service...
Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdbXDB" has 1 instance(s).
Instance "testdb", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle@dev2>

could you please let me know?

Thanks,

October 27, 2004 - 7:18 am UTC

you have automatic registration on.

You have manual registration on.

if you remove the manual (from listener.ora) and restart the listener and then database (so it registers right then and there upon startup with the listener). Also, if you had waited a minute or two after removing the listener entries for the databases to self register -- the same thing would have happened.

if this bothers you you can either

1) remove the manual entries from the listener.ora
2) stop dynamic registration

consider:

[ora9ir2@tkyte-pc-isdn admin]$ cat listener.ora
# LISTENER.ORA Network Configuration File: /home/ora9ir2/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
)
)

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

just like you have now, database is NOT running:

[ora9ir2@tkyte-pc-isdn admin]$ lsnrctl start

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 27-OCT-2004 06:49:09

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

[ora9ir2@tkyte-pc-isdn admin]$ lsnrctl services

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 27-OCT-2004 06:49:19

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 UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully

ok, now start database:

[ora9ir2@tkyte-pc-isdn admin]$ startup

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Oct 27 06:49:26 2004

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

Connected to an idle instance.

idle> ORACLE instance started.

Total System Global Area 470881660 bytes
Fixed Size 451964 bytes
Variable Size 201326592 bytes
Database Buffers 268435456 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
idle> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
[ora9ir2@tkyte-pc-isdn admin]$ lsnrctl services

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 27-OCT-2004 06:49:37

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 2 instance(s).
Instance "ora9ir2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
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: tkyte-pc-isdn, pid: 25511>
(ADDRESS=(PROTOCOL=tcp)(HOST=tkyte-pc-isdn)(PORT=52229))
The command completed successfully

normal, ok, fine, not a problem, no issues
if you remove the entry from the listener.ora:

[ora9ir2@tkyte-pc-isdn admin]$ lsnrctl stop

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 27-OCT-2004 06:52:49

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
The command completed successfully
[ora9ir2@tkyte-pc-isdn admin]$ vi listener.ora
[ora9ir2@tkyte-pc-isdn admin]$ cat listener.ora
# LISTENER.ORA Network Configuration File: /home/ora9ir2/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
)
)

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

[ora9ir2@tkyte-pc-isdn admin]$ lsnrctl start

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 27-OCT-2004 06:53:03

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

Starting /home/ora9ir2/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 9.2.0.4.0 - Production
System parameter file is /home/ora9ir2/network/admin/listener.ora
Log messages written to /home/ora9ir2/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tkyte-pc-isdn)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 9.2.0.4.0 - Production
Start Date 27-OCT-2004 06:53:03
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /home/ora9ir2/network/admin/listener.ora
Listener Log File /home/ora9ir2/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tkyte-pc-isdn)(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
[ora9ir2@tkyte-pc-isdn admin]$ lsnrctl services

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 27-OCT-2004 06:53:07

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

right now, since we started the listener AFTER the database, the service is not registered. wait a minute and:

[ora9ir2@tkyte-pc-isdn admin]$ lsnrctl services

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 27-OCT-2004 06:54:18

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: tkyte-pc-isdn, pid: 25511>
(ADDRESS=(PROTOCOL=tcp)(HOST=tkyte-pc-isdn)(PORT=52229))
The command completed successfully
[ora9ir2@tkyte-pc-isdn admin]$

they are and people can start connecting again

A reader, October 27, 2004 - 11:59 am UTC

Thank you so much!

Your site is better than every book and university! I learned a lot.

Thank you again,
- Arash

not useful for me

jitendra, November 18, 2004 - 4:53 am UTC

basically i m searching for change the password of listner
but there is no any words for it.
bye

November 18, 2004 - 10:44 am UTC

did you try "umm" the documentation perhaps?

i went to the net admin guide table of contents...
ctl-f'ed for password
and found this:

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

Listener

Le, December 09, 2004 - 12:56 am UTC

Hi Tom,

I followed your steps above to start the listener first then startup my instance, but for some strange reason, the "lsnrctl services" does not display any info about dispatcher D000 although the dispatcher is running.  I configured my database in shared server mode and it's running on Solaris 9.  I configured all the required parameters for shared server but it looked like the listener is not communicating with the dispatcher.  I can only connect using dedicated connection.  I have this problem on the Unix server only.  Have you seen this problem before?  Can you help me identify the problem?


SQL*Plus: Release 9.2.0.1.0 - Production on Thu Dec 9 05:20:01 2004

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


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> show parameter dispatch

NAME                                 TYPE        VALUE
----------------- ----------- ------------------------------
dispatchers             string      (PROTOCOL=TCP) (SERVICE=apecdb.apec)
                                                 
max_dispatchers         integer     5
mts_dispatchers         string      (PROTOCOL=TCP) (SERVICE=apecdb.apec) 
mts_max_dispatchers     integer     5


apec1:oracle> ps -ef | grep d0
  oracle 26900     1  0 00:28:05 ?        0:00 ora_d000_apecdb


apec1:oracle> lsnrctl services

LSNRCTL for Solaris: Version 9.2.0.1.0 - Production on 09-DEC-2004 05:23:21

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=apec1)(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
Service "apecdb.apec" has 1 instance(s).
  Instance "apecdb", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:5 refused:0
         LOCAL SERVER
The command completed successfully


I waited and kept checking and checking but the listener still does not display the dispatcher info.

December 09, 2004 - 1:14 pm UTC

what are the other net related paramters like local listener set to in your init.ora. perhaps you are registering with some other listener.

Listener

A reader, December 09, 2004 - 7:50 pm UTC

Tom,
It turned out that the dispatcher does not communicate with the listener because the listener is pointing to a logical host. In the listener.ora, I had to put HOST=loghost instead of the local hostname for failover purpose in Sun Cluster Environment. I tried setting the DISPATCHER parameter to contain the (HOST=loghost) but it didn't work. Is there another way to tell the dispatcher to accept the loghost? The listener works fine with all dedicated connections. Thank you very much for your help.

Le

What's PLSExtProc instance

Laxman Kondal, January 11, 2005 - 3:05 pm UTC

Hi Tom

What is - Service "PLSExtProc" has 1 instance(s). -- in lsnrctl status output?

Whats the purpose of this instance and where, when and how do this instance is used and by whom?

Thanks

January 11, 2005 - 3:15 pm UTC

it is for external procedures.

it is optional, if you don't use them, you don't have to have them configured.

do we need listener.ora to configure everytime?

abc, April 25, 2005 - 4:13 pm UTC

We are configuring listener.ora everytime ?

DO you think its an good idea or needful?

April 25, 2005 - 5:08 pm UTC

everytime what? not sure what you mean by "everytime"

listener

abc, April 26, 2005 - 2:06 pm UTC

"Everytime" means..

if I have 10 databases then why people keeping 10 enteries in listener.ora?

April 26, 2005 - 2:12 pm UTC

because they want to connect to them in order to start them up over the network?

You can use dynamic registration (no new entries, database contact listener) or static registration.

with static, you can connect "as sysdba" remotely even when the database is down. with dynamic you cannot.

but, if you have 10 databases under one listener on a single machine, you've got perhaps bigger fish to fry (like consolidation of these 10 into a single instance)

how to know the port number from both server and client side

Jianhui, May 24, 2005 - 9:47 am UTC

Hello Tom,
When a DB connection is established, is it a easy way to get the port numbers used by the server process (v$views?) and client side application?(assuming TCP/IP and dedicated server configuration)
Best Regards,

May 24, 2005 - 1:02 pm UTC

which port number... there are dedicated server connections, shared server connections.

the port the listener is on is well known (and in dedicated server, the same for the dedicated server) by the client.

dedicated server

Jianhui, May 24, 2005 - 3:31 pm UTC

Tom,
I meant the port number used by dedicated server process on server side, and the port number used by client application. For example, client side application SQL*PLUS, what port number does it use to communicate with server side process after the server process is be spwand by the listener? And on the server side, which port is used by the oracleSID process? Hope this makes question clear, as for the port number that listener uses(i.e. 1521), it's nothing to do with my question.

Best,

May 24, 2005 - 4:09 pm UTC

1521 is the port

the listener forks off the child (the dedicated server)
they inherit the connection

the connection is hostname+1521 if that is what you are using

only if you use something more complex like shared sever, connection manager would it be different.

Check the listener.log file for the port numbers used by the server process

Logan Palanisamy, May 24, 2005 - 7:57 pm UTC

24-MAY-2005 16:54:34 * (CONNECT_DATA=(SERVICE_NAME=mydb)(CID=(PROGRAM=)(HOST=oradb02)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=oradb02)(PORT=32823)) *

Unix permissions.

Jit, May 25, 2005 - 4:51 am UTC

Tom,

Not sure if my question is related to original question or not.
Is it possible for an operating system user (say Unix user) with read only permissions on Oracle binaries to stop/start the listener? If yes, how can this be controlled?

Thanks
Jit

May 25, 2005 - 7:51 am UTC

you can password protect the listener.

do dedicated servers share one port(i.e. 1521)?

jianhui, May 25, 2005 - 10:24 am UTC

<<
1521 is the port

the listener forks off the child (the dedicated server)
they inherit the connection
>>

Tom,
If all dedicated server processes used 1521 port, how could they distinguish which request sent by the client belongs to which server process? How could it be possible to open the same port in multiple processes, I am confused. Does this imply the listener acts as operator(dispatcher) to switch the client requests to the corresponding server processes, but this is not what i read from oracle document. For dedicated server, the listener gets connection request and spawn the server process and then bequeth the connection to client and the server process, there will be no more business of listener for this established connection.

Here is what i guess:
1) client application opens a port(i.e. 12100) and sends connection request to listeners port (i.e 1521)
2) listener gets the request from port 1521 and fork server process oracleSID
3) the server process oracleSID opens a port (i.e 13100)
4) listener passes oracleSID's information to client application
5) after authentication, connection established between server process and client application, communication channel looks like
clientIP.12100 <====> hostIP.13100
6) there is no more business of listener, it can continue to listen to 1521 for other connection request.

My questioin was how to find out the port numbers of client side application and server side oracleSID process, in the example they are 12100, 13100.

May 25, 2005 - 1:03 pm UTC

because tcp-ip is a connected client server protocol.

Just like all web stuff goes to "port 80" for example.

you connect to listener, listener "forks" (copies itself). This forked copy inherits all open file descriptors (sockets included). The forked copy exec's the dedicated server.

the client -- you would have to interrogate using OS calls on the client.

the server -- it is inherited normally. the server process does NOT redirect
the client with normal dedicated servers

$ netstat -a

Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 *:1521 *:* LISTEN

tcp 0 0 xtkyte-pc.us.orac:55606 xtkyte-pc.us.oracl:1521 ESTABLISHED
tcp 0 0 xtkyte-pc.us.orac:56441 xtkyte-pc.us.oracl:1521 ESTABLISHED
tcp 0 0 xtkyte-pc.us.orac:56454 xtkyte-pc.us.oracl:1521 ESTABLISHED

tcp 0 0 xtkyte-pc.us.oracl:1521 xtkyte-pc.us.orac:55606 ESTABLISHED
tcp 0 0 xtkyte-pc.us.oracl:1521 xtkyte-pc.us.orac:56441 ESTABLISHED
tcp 0 0 xtkyte-pc.us.oracl:1521 xtkyte-pc.us.orac:56454 ESTABLISHED

[tkyte@xtkyte-pc tkyte]$ sqlplus scott/tiger@ora9ir2.localhost.localdomain

SQL*Plus: Release 9.2.0.5.0 - Production on Wed May 25 13:05:02 2005

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

Connected to:
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

scott@ORA9IR2> !netstat -a | grep 1521
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 *:1521 *:* LISTEN

tcp 0 0 xtkyte-pc.us.orac:55606 xtkyte-pc.us.oracl:1521 ESTABLISHED
tcp 0 0 xtkyte-pc.us.orac:56441 xtkyte-pc.us.oracl:1521 ESTABLISHED
tcp 0 0 xtkyte-pc.us.orac:56454 xtkyte-pc.us.oracl:1521 ESTABLISHED
tcp 0 0 localhost.localdo:56465 localhost.localdom:1521 ESTABLISHED

tcp 0 0 xtkyte-pc.us.oracl:1521 xtkyte-pc.us.orac:55606 ESTABLISHED
tcp 0 0 xtkyte-pc.us.oracl:1521 xtkyte-pc.us.orac:56441 ESTABLISHED
tcp 0 0 xtkyte-pc.us.oracl:1521 xtkyte-pc.us.orac:56454 ESTABLISHED
tcp 0 0 localhost.localdom:1521 localhost.localdo:56465 ESTABLISHED

added another 1521, fork/exec, inherit file descriptors, parent (listener) closes it and client communicates with dedicated server that was fork/execed off.

how multiple processes share one port

jianhui, May 25, 2005 - 1:37 pm UTC

Tom,
Thanks for the demo. Did you use windows, which would be threaded server? But in UNIX, how could multiple server processes reconginize whether incoming request is actually from the connection it opened? For instance, i have 10 client connections, and 10 dedicated server processes, as you said, these 10 server processes all use 1521 port, how could they know the message sent to that port belongs to themselves but not other server processes? Secondly, you can actually stop or kill the listener process, the already established connections before the listener was killed will still function well, this indicates that listener has nothing to do with the session once established.

May 25, 2005 - 3:32 pm UTC

that was linux.

fork/exec -- open file descriptors, it is very natural and done frequently on unix (in fact it is relatively "new" on windows with shared sockets)

you can kill the listener, once it forks, the resource is totally duplicated, the listener closed the connection right after it forked the child.

dynamic registration

Dave, May 31, 2005 - 4:30 pm UTC

Does server side load balanacing work ONLY with dynamic registration? Thanks.

June 01, 2005 - 8:06 am UTC

With dedicated server, the database must register via the remote_listener

For shared server, the dispatchers must be "cross registered" with other listeners, the database does need to tell the listeners about the dispatchers behind the services.. This can be done with the dispatchers= parameter.

</code> http://docs.oracle.com/docs/cd/B10501_01/network.920/a96580/listener.htm#490372 <code>
describes the necessary setup.

A reader, June 24, 2005 - 12:04 am UTC

Tom

I just installed Oracle 10g on my local desktop. The contents of the listener.ora and tnsnames.ora look like the below..

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\10g)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = otn )(PORT = 1521))
)
)
)

tnsnames.ora

ORCL10G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = otn)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl10g)
)
)

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

My listener.ora is totally silent about my local database i.e. orcl10g, but still I'm able to connect as scott/tiger@orcl10g. How is it happening?

I'm able to connect as scott/tiger@orcl10g but not as scott/tiger even though I only have only a single database on my local. Why?

Lastly, I need to install another database on my local desktop. Now when I do that, and login as sys/oracle as sysdba, how can I login to the database of my choice. How and where should I set my oracle_sid.

Please answer.

Thanks
Otn

June 24, 2005 - 6:35 am UTC

dynamic registration, the database registers itself with the listener after startup.

if scott/tiger isn't working, it would indicate your environment isn't set up in your account. (oracle_home and oracle_sid)

You can use dbca to pick your "default" oracle instance or use the environment (set ORACLE_SID....) as that'll override it or use the listener (you'd need to have static registration in the listener.ora to do this if you want to startup the database via sqlplus) to connect to the right instance.

How

A reader, June 24, 2005 - 9:56 am UTC

Tom

---
if scott/tiger isn't working, it would indicate your environment isn't set up in
your account. (oracle_home and oracle_sid
----

How should I set up the environmnet in the above context, where should I set the oracle_home and oracle_sid, I'm on windows XP, and oracle database 10G

-------
You can use dbca to pick your "default" oracle instance or use the environment
(set ORACLE_SID....) as that'll override it or use the listener (you'd need to
have static registration in the listener.ora to do this if you want to startup
the database via sqlplus) to connect to the right instance.
-------

can you please show how I can do the static registration in the listener.ora.

A reader, June 25, 2005 - 2:30 am UTC

What is the difference between global database name ( we are asked toprovide this when we install oracle db using dbca) oracle_sid, oracle instance name we find in the v$instance, and name we find in v$database.

Thanks

June 25, 2005 - 9:41 am UTC

oracle_sid is used to find the parameter files and such. $ORACLE_HOME/dbs/init$ORACLE_SID.ora for example, uses the oracle_sid.

oracle_sid + oracle_home on Unix is used to generate a key to attach to the shared memory segment known as the SGA as well.

the global database name is a name that is to be unique in your set of federated databases. The database link I create to point to your database should use YOUR unique global name. This helps to ensure we are connecting to the database we think we are connecting to. And since the dblink has to be named after the global name, it helps enforce that global uniqueness in your set of federated databases.

The instance name is the oracle sid generally.

A reader, June 25, 2005 - 12:06 pm UTC

instance name is the sid generally

Your answer above suggests that instance name can be different from sid. when will the instance name be different from sid and why?

If you are suggesting that in a oracle parallel configuration, v$instance will have information of the instance your are in then, that should be the main differene between v$database and v$instance.

If we are in a OPS enviorment then we have to query the v$instace to know the instance we are in, and to know the database name we have to query v$database.

I'm I right?

June 25, 2005 - 1:24 pm UTC

well, there is a SINGLE database and is mounted and opened by MANY instances. So, database is singular and instance is plural in Oracle. A database may have many instances associated with it.

The instance name column in v$instance will always be the oracle_sid

There is a parameter instance_name that can be used to register the database with a listener as a "different" instance name (confusing, agreed, it would have been better to call that column ORACLE_SID in v$instance)

A reader, June 25, 2005 - 4:39 pm UTC

Then the 'select name from v$database' is also oracle_sid?

i.e. if v$instance containts oracle_sid, then database name should be the one we set in init.ora. I'm I right?

What is the difference between sid, database name, and service name( which we see in tnsnames.ora) ?

June 25, 2005 - 5:16 pm UTC

dbname <> oracle_sid, they are different.

the database has a NAME and an instance with a totally different "name" might mount and open it.

sid = oracle_sid, is the instance (processes+memory) identifier.

database name = name you gave the collection of files that is the database. Not the sid.

service name - an instance can register itself with a listener using a variety of service names. A service at the listener level could point to many instances (load balancing).

A reader, June 25, 2005 - 5:21 pm UTC

TNSNAMES.ORA

ORCL10G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOMELAPTOP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl10g)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOMELAPTOP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)

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

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\orcl10g)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOMELAPTOP)(PORT = 1521))
)
)
)

Tom

I deleted all the databases and recreated two databases orcl10g and orcl.

Now I'm able to connect without giving the connect desriptor i.e. as scott/tiger , and also as scott/tiger@orcl or scott/tiger@orcl10g

1.When I login as system/oracle by default I login into orcl10g, how can I by default login into
orl database wihtout giving the connect descriptor.What should I do to achieve this?

2.Look at my listener.ora, it only hs information of my host, but there is no information of my two databases i.e.orcl and orcl10g
but still I'm able to login into the two databases. I'm confused as to how this is happening. Please explain as to
how this is happening.

I'm quite sure if my database is on a separate server, and I'm connecting from my local desktop, I will not be able to connect
if the listener.oral does not have an entry for each database.

Please help me understand.

June 25, 2005 - 5:56 pm UTC

1) in 10g, you would use dbca to pick your default database.

2) they (the databases) register with the listener after they startup, it is called dynamic registration, you can read about it in the network guide.

You would be wrong on the last point.

dave, June 25, 2005 - 6:03 pm UTC

I think for point one maybe he wanted to change his oracle_sid to that of the other database then it becomes the "default" one as such

June 25, 2005 - 6:34 pm UTC

in windows, that is a "registry" change. 10g - dbca does that (no more oracle home selector)

A reader, June 25, 2005 - 6:43 pm UTC

Tom

Can you please give me the link to the dynamic registration with listener.

Is there also a discussion about dynamic vs static registration. When is statis registration necessary.

Further, I just want to know how to set oracle_sid before logging into sqlplus and getting into a particular database.

Will the below suffice..

cmd

c:/ set oracle_sid=norcl10g

and then

sqlplus system/oracle

I did try the above but I'm getting the error 'ora-12560 - tnsprotocol adapter error'

2.

When will username/password alone will suffice to connect and when will it fail?

I'm trying to nail this understanding into my mind, please help me.

June 26, 2005 - 9:30 am UTC

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

(found by typing dynamic registration into the search box on the documentation, which is all on otn.oracle.com)

static registration is needed when you want to startup the database remotely. (since the database isn't UP, it won't be registered, so there would be nothing to connect to)

if you have the oracle home and oracle sid set, that should work if the database is in fact up and running.

the username/pw will work when they are correct and the environment is set properly (oracle_home/oracle_sid)

Thank You

Deepka Haldiya, July 21, 2005 - 4:03 pm UTC

Thank you Tom for this wonderful service you are providing.

May God Bless You.

Problem running Sean's example

Robert, August 05, 2005 - 12:49 pm UTC

Tom,

When I ran Sean's example, above, I got an error....

$ sqlplus /nolog

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Aug 5 11:38:45 2005

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

SQL> conn / as sysdba
Connected.
SQL> begin
  2    dbms_xdb.cfg_update(
  3      updatexml(updatexml(dbms_xdb.cfg_get(),
  4                          '/xdbconfig/descendant::ftp-port/text()', '2100'),
  5                '/xdbconfig/descendant::http-port/text()', '2200'));
  6  end;
  7  /
    updatexml(updatexml(dbms_xdb.cfg_get(),
    *
ERROR at line 3:
ORA-06550: line 3, column 5:
PLS-00201: identifier 'UPDATEXML' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored


SQL> exit

Can you help?

Thank you,

Robert.

August 05, 2005 - 2:39 pm UTC

ctl-f for sysdba on this page and stop using sysdba.

"Better" way to change ports (?)

Robert, August 05, 2005 - 1:32 pm UTC

Tom,

Thanks anyway but Metalink had an answer this time...
Note: 209564.1 has code for a utility: SYS.XDB_UTILITY which contains 2 procedures: 'set_http_port' and 'set_ftp_port'.
After creating this procedure I was able to successfully use it to change my http port from the default 8080.

Thanks anyway,

Robert.

URGENT: Listener Problem

Deepak, August 24, 2005 - 12:48 am UTC

Hi Tom,

I just created a 9.0.1 database on my Laptop. Everything went fine but the listener service. When I start the listener the status shows started. But when I use the tnsping utility the service crashes and the error message No Listener is displayed. I checked out the tnsnames.ora file and the listener.ora file, and everythings looks perfectly fine. When I try to start the crashed Listener service it gives 1067 Service error.

Please help me.

August 24, 2005 - 9:06 am UTC

please utilize support for something like this.

load balance

reader, September 09, 2005 - 10:01 am UTC

I understand how to configure tnsnames with load_balance=on to enable client load balancing. Also, we used local_listener and remote_listener parameters in RAC environment to configure server side load balancing. Is there a way/view to see whether listener is load balancing user connections? Thanks.

September 09, 2005 - 11:09 am UTC

gv$session, see if they look balanced. Is that what you are looking for?

load balance

reader, September 09, 2005 - 2:09 pm UTC

so you mean using gv$session, we manually count number of sessions in each instance to see if they are distributed? I was expecting if listener log file of some other views might give information about it. Thanks.

September 09, 2005 - 2:28 pm UTC

if you think querying gv$session "manual", try reading the linear listener.logs and figuring out how it is "balanced"

gv$session - a simple count(*) by inst_id, doesn't seem very hard?

I'm not sure what metrics you are looking for exactly so that is the best I can given that.

help

A reader, September 12, 2005 - 5:33 pm UTC

Tom:

In going through this link and in my experience, one listener is sufficient for one database instance. However with one listener we were getting 4 connections per sec (as seen in listener.log). So other DBA's added an extra listener and with two listeners there were 8 connections per sec. Now they want to stretch this and add more listeners (per application per se).....

I have two questions:

1) Does more listeners mean more number of connections per sec? Does this depend on number of concurrent sessions logged per sec?

2) Our database accidently has got 1 dispatcher and 1 minimum shared server. After adding second listener, the dispatcher always makes a "DEDICATED" connection. Do you think having two/multiple listeners make any change to dispatcher....( i know you might say both are apples and oranges)

As always your input is most valuable
Thanks,

September 12, 2005 - 5:57 pm UTC

if you are getting only 4 connections per second, you have a serious problem somewhere on that machine.

You should be able to get lots more than that. Time to look at this machine, remove Oracle from the equation first, just test the network, something is "amiss" here.

of prev review

A reader, September 12, 2005 - 7:16 pm UTC

Tom:

we have ruled out everything and it seems everyone is under conclusion that listener is the bottleneck. It cannot make more than 4 connections per sec. What is the maximum number of connections that a listener can make per sec? Now we are planning to take it to extreme and add more listeners per application.....Any articles which i can follow to understand the problem? The dispatcher seems to be busy 15% of time and shared servers 40-50% of the time....

Thanks,

September 13, 2005 - 11:41 am UTC

hundreds, thousands - or more.

if you are getting 4 connections per second there is something seriously wrong here, time to UNRULE out everythning you've ruled out and take another look.

multiple LISTENER for same database

Nikunj, September 14, 2005 - 9:27 am UTC

Hi Tom,

If two listener on two port i.e. 1521 and 1522 are listening for one (X database) database.

What are the pros and cros if one database have 2 listener and have 1 listener ?

Regards,

September 14, 2005 - 9:38 am UTC

pros - extreme cases.

cons - extra work, maintainance, monitoring.

can u explain more

Nikunj Thaker, September 14, 2005 - 10:00 am UTC

Followup:

pros - extreme cases.

cons - extra work, maintainance, monitoring.

September 14, 2005 - 10:13 am UTC

if and only if a single listener was getting so nailed, hit so hard, that it built up a queue of requests and started failing the requests because the queue was too long would I consider another listener.

And even then, it would likely be on another machine, for another instance, using the same database - if you are nailing a listener that hard....

Why do you believe you would need a second listener, it's job is pretty "easy"

queue

reader, September 15, 2005 - 1:15 pm UTC

queue was too long?

is there an error message that would tell us when it happens?
is there a way to specify a value for queue for a listener?
Thanks.

September 15, 2005 - 1:24 pm UTC

people wouldn't be able to connect, you would know. You'd get a tns error.

The queue is a tcp-ip queue, yes you can muck with it, but you need not in most cases

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

ORA-27140: attach to post/wait facility failed

A Reader, November 17, 2005 - 10:10 am UTC

Hi Tom,

Sorry if i am posting non relevant question here but i have searched your site and did not get anything related to ora-27140.

I have oracle 9.2.0.6 installed on Sun Solaris9 (5.9).
I run . oraenv and set the db name. Then I try to run sqlplus and want to make connection without using tns string but it is giving "ORA-27140: attach to post/wait facility failed" error.

I have searched metalink and set the sticky bit for Oracle binary "chmod 6751 oracle" but it did not work.

One more thing, when i am using ora9i user ( oracle installation user) then i can connect this way BUT when i am trying as some other user , getting the error.
While other user is also the member of dba group.

Pls, help users are waiting to connect.

Thanks.

November 18, 2005 - 7:21 am UTC

open a tar - support is pretty good for install/configuration issues.

How could users be waiting to connect - you are pre-development here...

reader

A reader, December 20, 2005 - 12:30 pm UTC

according to manual, the sid_list parameter is no longer
valid in oracle 8i and 9i. oracle uses service_names
parameter from db_domain or db_name from data dictionary
to auto register by PMON

Then is it possible, if there are say 6 databases on a
server, to configure multiple listeners ( one with port xxxx
and another with port yyyy) so that 2 databases use
port xxxx and 4 databases use port yyyy

December 20, 2005 - 1:04 pm UTC

no longer necessary - where does it say not valid?

reader

A reader, December 20, 2005 - 2:18 pm UTC

From Network Administration Guide 9.2
</code> http://download-west.oracle.com/docs/cd/B10501_01/network.920/a96580.pdf <code>

SID_LIST_listener_name
A section of the listener.ora file that defines the Oracle System Identifier (SID)
of the database served by the listener. This section is valid only for version 8.0
Oracle databases, as Oracle9i and Oracle8i instance information is automatically
registered with the listener.

December 20, 2005 - 2:57 pm UTC

well, it is necessary if you want to remotely start your database up! The database won't have "registered" and you won't get a dedicated server to fire up otherwise.

See page 12-9 in that pdf:

Oracle7 database, you must configure the listener.ora file with information
about the database. Static configuration is also required for other services, such as external procedures, Heterogeneous Services, and some management tools,
including Oracle Enterprise Manager.

reader

A reader, December 20, 2005 - 3:14 pm UTC

Thanks

When I start the listener, all databases register automatically.

My original question

"
Is it possible, if there are say 6 databases on a
server, to configure multiple listeners ( one with port xxxx
and another with port yyyy) so that 2 databases use
port xxxx and 4 databases use port yyyy
"

December 20, 2005 - 3:34 pm UTC

right, but in order to start the databases over the network...... (you would need the static registration)

my point: it is not "invalid", it is not entirely necessary in all cases - but still is a) supported, b) necessary for some things

Yes, you can do that - you would set the local listener in the init.ora to tell each about the listener it should contact and register with

(or use static registration and don't have any of them register :)

6 databases on a server?

Oracle Phan, December 21, 2005 - 7:25 am UTC

I would love to know the details about this. It seems to me the listener would be way down the list of worries with this.

December 21, 2005 - 7:55 am UTC

I've said more than once (not as many times as "use bind variables") the only and perfect number of instances on a single server is

one

a test machine - maybe. A production box - I would not want more than one instance (and as few machines as possible... meaning don't go buy a machine for each database server you think you want, fill up each database server until they are "full")

missing features

A reader, December 21, 2005 - 2:38 pm UTC

Hi Tom

I have configured 10 g database to listen through 8i tnsnames.ora file.
--------------------------------------------------------
10G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = abc)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = abc)
    )
  )

LISTENER_10G =
  (ADDRESS = (PROTOCOL = TCP)(HOST = abc)(PORT = 1522))

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

Of late I have been noticing that if I connect from client as ( asd/asd@10g )which has oracle 8i sqlplus it shows that it is connected to 10g but some features are missing.

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

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Dec 21 14:33:40 2005

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


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

SQL> 


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

Should it happen ???

December 21, 2005 - 7:56 pm UTC

using sqlplus 8i against 10g is going to be very very limiting.

try to run this valid query for example:

ops$tkyte@ORA10GR2> with t as (select * from dual)
  2  select * from t;

D
-
X



You'll be missing a lot using old sqlplus's against current databases - yes, this is expected.

reader

A reader, December 21, 2005 - 4:30 pm UTC

To Oracle phan

The multiple databases on a server have been inherited from predecesor
As you know in some organizations, it is not too easy to do a major
changes by consolidation or some thing along the line.

As for the listener, it is at the top of the list if
you consider security, firewall, IP Addresses and Ports that will
be passed through and so on

Basic listner questions

A reader, January 29, 2006 - 1:04 pm UTC

Tom,

If I change the port number to 1522 in both tnsnames.ora and listner.ora through Oracle net manager and try to connect to the database, I get the error 'ORA-12541: TNS:no listener'. If I change the port number back to 1521 in both tnsnames.ora and listner.ora, I am able to connect properly to the database.
My question
1) Why am I not able to connect to the DB if I change the port # to 1522?
2) What other changes should be made if I want to connect to the DB (using TNSNames.ora) using a port other than 1521.
3)
If I change the port number to 1522 in listner.ora, but not in tnsnames.ora - can connect fine
If I change the port number to 1522 in tnsnames.ora, but not in listner.ora - cannot connect
When I do tnsping, the tnsping is successful, but the port number shown is 1521 in both the cases above.
Where is tnsping picking up the port number 1521 from?

Thank you for your time.

January 29, 2006 - 1:21 pm UTC

1) could be firewall rules on your system or you did something wrong (eg: you didn't stop and restart the listener - for example)

1521, 1522, 1234, 6666, we don't care - we'll work on any of them.

2) stop listener, update listener.ora, start listener, use a tnsnames.ora entry that references correct port.

3) that indicates to me that you never stopped and restarted the listener, the only time it reads that file is during startup.

listner

mohammed awad, March 28, 2006 - 8:26 am UTC

respected sir,
i have started the listener on windows like this lsnrctl start and started successfully and on the client tnsping listener
and also successfully find the service
but when i tryed to login using (sqlplus user1/user1@myalias) using that alias it failed and gave me this message:
ORA-12514: Listener does not currently know of service requested in connect descriptor.
would u plz help me?
thanks with my regards

March 28, 2006 - 3:52 pm UTC

"u" isn't available - they did not come to work today.

"plz" ? German postal codes? I don't understand....

The database must not have registered with the listener. You have a service in your tnsnames.ora that the listener doesn't know about.

lsnrctl services

will show the registered services of the listener, what it knows about.

listener

mohammed awad, March 30, 2006 - 1:32 am UTC

respected sir,
when i start the listener and its status is ready and tryed to login from my client to server using alais(sqlplus user1/user1@moh2) i got this message and the listener status is ready:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
and this is my listener.ora and tnsnames.ora
listnere.ora:
# listener.ora Network Configuration File: C:\oracle\product\10.1.0\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_list1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.1.0)
(PROGRAM = extproc)
)
)

list1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = moh)(PORT = 1521))
)
)
)

tnsnames.ora:
# tnsnames.ora Network Configuration File: C:\oracle\product\10.1.0\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

MOH2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = moh)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = moh2)
)
)

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

would you please find out whats the problem?
thank you,

March 31, 2006 - 10:59 am UTC

the server hasn't registered with the listner

type

lsnrctl services

to see what services are known and registered with your listener.

solved

Fernando Sanchez, May 03, 2006 - 9:44 am UTC

It is working OK now, I had to modify the SERVICE_NAME in my tnsnames,

ORCL10 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = linux.site)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl10.site)
)
)

but I do not know how I could write it in the sqlnet.ora

Thanks.

Su Baba, July 12, 2006 - 7:25 pm UTC

The following are entries from my listener.ora file. What is the purpose of the first entry where "SID_NAME = PLSExtProc"? If I remove this entry, will it break anything?

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

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = xyz.abc.com)(PORT = 1521))
)
)

July 12, 2006 - 7:42 pm UTC

it is for external procedures and will break any external procedures you may be using (which is likely "none")

</code> http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_packages.htm#sthref818 <code>

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

Roger, July 18, 2006 - 11:37 am UTC

Hi Tom,

I am having trouble setting up my listener using "dynamic service". I have thoroughly read your previous responses on this before posting this question.

my tnsnames.ora:
-----------------
# tnsnames.ora Network Configuration File: /opt/oracle/product/10.2.0.1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

idw1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = idw1)
    )
  )

irep1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = irep1)
    )
  )

Listener.ora:
-------------
# listener.ora Network Configuration File: /opt/oracle/product/10.2.0.1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/oracle/product/10.2.0.1)
      (PROGRAM = extproc)
    )
  )

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

My services_names and local_listener parameters on DB: idw1

SQL> show parameter service_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      idw1
SQL>
SQL> show parameter local_list

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


Here is my listener's registered services:
-----------------------------------------
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 18-JUL-2006 15:13:44

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
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

Question
--------
1) If I have set the service_names parameter in my DB to the right service name (as shown by the show parameter command and tnsnames.ora), when the DB comes up, why doesn't it register with the listener?

2) If in the SID_LIST_LISTENER section of the listener.ora file, you don't register your instance statically, when connecting using sqlplus, you won't be able to do so(e.g. sqlplus sys@idw1 as sysdba). The only work around is setting your ORACLE_SID env variable and then connect like this: sqlplus sys as sysdba). Your thoughts on this please.

Thank you for your time and your service to the Oracle community.

July 19, 2006 - 8:13 am UTC

1) it would, if the database is on the same machine with the server.

you can use alter system register; to force it to register "right now" (maybe you started database, then listener - it will be a bit before database re-registers)

2) you can connect x/y@z as sysdba - as long as the database is up and running, what you cannot do is "start" the database unless you have statically registered it.

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

Roger, July 19, 2006 - 10:48 am UTC

Hi Tom,

Thank you for your prompt response.

I tried the "alter system register" but still my database refuses to register itself with the listener (even though the service_names parameter and tnsnames.ora parameters are in sync).

Also, once I comment out the static part of the listener.ora, and try to connect to the database, I get "ORA-12514: TNS:listener does not currently know of service requested in connect". The complete trial and error transcript is shown below.

My listener.ora (as you can see I commented out the SID_LIST_LISTENER entries for idw1 and irep1)
---------------------------------------------------
# listener.ora Network Configuration File: /opt/oracle/product/10.2.0.1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
#    (SID_DESC =
#      (SID_NAME = irep1)
#      (ORACLE_HOME = /opt/oracle/product/10.2.0.1)
#    )
#    (SID_DESC =
#      (SID_NAME = idw1)
#      (ORACLE_HOME = /opt/oracle/product/10.2.0.1)
#    )
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/oracle/product/10.2.0.1)
      (PROGRAM = extproc)
    )
  )

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

My tnsnames.ora
----------------------------------------------------------
# tnsnames.ora Network Configuration File: /opt/oracle/product/10.2.0.1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

#LISTENER_TRSTE =
#  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


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

idw1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = idw1)
    )
  )
irep1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = irep1)
    )
  )

Restarting the listener:
-----------------------------------------------------------
oracle@servername ~ $ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 19-JUL-2006 14:19:37

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully

oracle@servername ~ $ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 19-JUL-2006 14:19:46

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

Starting /opt/oracle/product/10.2.0.1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /opt/oracle/product/10.2.0.1/network/admin/listener.ora
Log messages written to /opt/oracle/product/10.2.0.1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                19-JUL-2006 14:19:46
Uptime                    0 days 0 hr. 0 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/10.2.0.1/network/admin/listener.ora
Listener Log File         /opt/oracle/product/10.2.0.1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(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

The services registered with the listener
----------------------------------------------------------
oracle@servername ~ $ lsnrctl services

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 19-JUL-2006 14:22:09

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
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


Now, if i try to connect, since the database is not registered with the database, I get:
-----------------------------------------------------------
SQL> connect sys/xxxxxx@irep1 as sysdba
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Warning: You are no longer connected to ORACLE.
SQL>
SQL> connect sys/xxxxxxxx@idw1 as sysdba
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

To follow your advice of forcing my database to register, I did the following, connected to the database using statically configured listener. In another terminal window, commented out the static part of the listener.ora for databases idw1 and irep1. Restarted the listener. From the first window, tried alter system register. But, still the database did not register.


My confusion
--------------------------------------------------------
1) If I have removed the static register part from the listener.ora, how do I connect to the database? Can you please give an example.

2) If there some log somewhere which shows that the database is trying to register but running into problems?

3) Is this anything to do with using localhost or 127.0.0.1 as shown in the tnsnames.ora?

Your help is sincerely appreciated.

July 20, 2006 - 7:47 am UTC

i'll ask you to use support to go thru and figure out what part of the configuration is wrong here - they are best suited for that.

this is likely due to your "localhost" thing (why would you do that, it doesn't seem to make sense - you are setting up a listener that you can only contact on that machine???).

if your hostname isn't "localhost" - the database is trying to contact a listener on the hostname machine - but it isn't running, it is running on localhost.

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

Roger, July 21, 2006 - 1:45 pm UTC

Hi Tom,

Just wanted to say thanks for your replies. The problem was due to the "localhost" hostname in the listener.ora. Putting the IP address of the server fixed it.

It was set to localhost in the first place because our Oracle database is an embedded db and we don't want the listener accepting any requests that don't originate from within the box.

So, once again thanks and I wrote this response to let you know how the issue was resolved.

July 23, 2006 - 7:42 am UTC

why bother with the listener at all then?

alter system register doesn't works

Manish, July 31, 2006 - 11:12 am UTC

Hi Tom,
we have dynamic listener registration in all of our prod databases. Sometimes we come across situations where after the server reboots the database doesn't registers itself to listener.

I have tried executing this command from that database ' alter system register', but this too didn't helped. I waited more than 15 min.

I have to perform these steps in order to get the listener registered to the database.
1. Shutdown the database.
2. Restart the listener by stop & start
3. start the database

This works all the time, but problem is that many a times we just can't restart the database.

We are using 9205 in HP-UX 11.11 64 bit.

Any possible reasons or workarounds.

July 31, 2006 - 12:28 pm UTC

sounds like a configuration issue more than anything else. Please utilize support for something like this.

TNS:listener problem

John, November 07, 2006 - 5:39 pm UTC

I have a local database installed (oracle XE). For various reasons I need to be able to connect to it like so:

scott/tiger@XE

logging on to sqlplus like
>sqlplus scott/tiger
works fine

here's the problem:

>sqlplus scott/tiger@XE

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

--
Here's the weird part

>tnsping xe
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = XE.er.usgs.gov
)))
OK (20 msec)

here's a look at the entry in my listener/tnsnames/sqlnet files

listener.ora----
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oraclexe\app\oracle\product\10.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\oraclexe\app\oracle\product\10.2.0\server)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
)

DEFAULT_SERVICE_LISTENER = (XE)

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

tnsnames.ora-------
XE.er.usgs.gov =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = XE.er.usgs.gov)
)
)

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

sqlnet.ora-----
NAMES.DEFAULT_DOMAIN = er.usgs.gov

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES,LDAP)

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

this worked fine right after i set it up. but after my first computer restart it does not work anymore.

any insight into what i might be missing now after my computer restart?

thanks in advance,
John

November 08, 2006 - 8:16 am UTC

lsrnctl services

what does that return. and does it match the service name in your tnsnames.ora.

tnsping just sees if a listener exists, nothing else.

TNS:listener problem cont.

A reader, November 08, 2006 - 10:37 am UTC

H:\>lsnrctl services

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 08-NOV-2006 08:22
:08

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
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
The command completed successfully

I don't see XE.er.usgs.gov in there, but i also have about 10 other listings for non-local databases in tnsnames.ora that are are not showing up when i do lsnrctl - and i can connect to those just fine...

thanks,
John

November 08, 2006 - 7:51 pm UTC

your XE database is likely not started therefore, it would register with the local listener.

TNS:listener problem cont. 2

John, November 08, 2006 - 11:36 am UTC

i added this to listener.ora

(SID_DESC =
(SID_NAME = XE.er.usgs.gov)
(ORACLE_HOME = D:\oraclexe\app\oracle\product\10.2.0\server)
)

here's the result of lsnrctl services now:

LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
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 "XE.er.usgs.gov" has 1 instance(s).
Instance "XE.er.usgs.gov", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully

on a side note- every time i try to connect and get refused, the "refused: #" increments in the print out for XE.er.usgs.gov when i issue lsnrctl services.

Service "XE.er.usgs.gov" has 1 instance(s).
Instance "XE.er.usgs.gov", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:1 <-- HERE
LOCAL SERVER

this time it is listed and matches the service name in tnsnames.ora

i restarted the listener and database. and seem to have made some progress since i'm getting a different error:

H:\>sqlplus scott/tiger@XE

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 8 09:25:31 2006

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

ERROR:
ORA-12518: TNS:listener could not hand off client connection

November 08, 2006 - 8:00 pm UTC

that is not the sid.

sid is simply XE

ORA-12518: TNS:listener could not hand off client connection

Debashis Payin, November 10, 2006 - 1:47 am UTC

Hi Tom
Good Morning. Need your help in resolving a problem.
While trying to connect, I've getting the error "ORA-12518: TNS:listener could not hand off client connection".
Please find the details as below :

/*********************************************************/
C:\>LSNRCTL SERVICES

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 10-NOV-2006 10:52
:50

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
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 "TESTXDB" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER <machine: IBM-85937E6C5B5, pid: 1464>
(ADDRESS=(PROTOCOL=tcp)(HOST=IBM-85937E6C5B5)(PORT=1481))
Service "TEST_XPT" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:2 state:ready
LOCAL SERVER
Service "test" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:2 state:ready
LOCAL SERVER
The command completed successfully

C:\>LSNRCTL STATUS

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 10-NOV-2006 11:01
:30

Copyright (c) 1991, 2005, 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 10.2.0.1.0 - Produ
ction
Start Date 10-NOV-2006 10:41:41
Uptime 0 days 0 hr. 19 min. 48 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oracle\product\10.2.0\db_1\network\admin\listener.o
ra
Listener Log File D:\oracle\product\10.2.0\db_1\network\log\listener.log

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=9.124.192.231)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "TESTXDB" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "TEST_XPT" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
The command completed successfully

/*********************************************************/

tnsnames.ora
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 9.124.192.231)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST)
)
)
/*********************************************************/

listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 9.124.192.231)(PORT = 1521))
)
)
/*********************************************************/
C:\>SQLPLUS DEV/DEV@TEST

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 10 11:03:02 2006

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

ERROR:
ORA-12518: TNS:listener could not hand off client connection

Enter user-name:
/*********************************************************/

Thanks
Debashis

November 10, 2006 - 8:50 am UTC

what is the entry for test in your listener.ora or does it dynamically register

Debashis Payin, November 13, 2006 - 5:32 am UTC

Hi Tom

    Well, I don't know what is Dynamic Registration, or how
i can retrive the info that it has Dynamic Registration. 
The fact is that, it was working before, with the same
listener.ora file. Suddenly it stopped working.
   Right now, if I do "SQLPLUS DEV/DEV" , it's getting
connected. If I do "SQLPLUS DEV/DEV@TEST", it's showing me
the above mentioned error. 

/********************************************************/
C:\>SQLPLUS DEV/DEV@TEST

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 13 14:52:19 2006

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

ERROR:
ORA-12518: TNS:listener could not hand off client connection


Enter user-name:
C:\>
C:\>SQLPLUS DEV/DEV

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 13 14:52:29 2006

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


Connected to:
Personal Oracle Database 10g Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>
SQL>

/********************************************************/


Thanks
Debashis

November 14, 2006 - 3:54 am UTC

right, thanks for posting the listener.ora entry.

How about asking your DBA to take a peek?

ORACLR_CONNECTION_DATA

A reader, January 11, 2007 - 1:08 pm UTC

Tom,
XE has preconfigured DB link after it is installed:
create public database link DBMS_CLRDBLINK
using 'ORACLR_CONNECTION_DATA';

Just curious - what is it for? And what is other record in TNSNAMES:EXTPROC_CONNECTION_DATA?

Thank you!

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = something)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

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

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

connection to Oracle XE from developer 2000

Williams G, January 19, 2007 - 4:42 pm UTC

Hi Tom

I´m having trouble connecting to Oracle XE 10g from forms, reports or graphics, 6i version, the following code was created automatically when installed and I copied TNSNAMES file to the default directory of Developer 2000.

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = something)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

I try to connect using User/password@XE
without success.

Thank you for your help.

ORACLE INITIALIZATION SHUTDOWN IN PROGRESS ERROR

Vishal C Sabharwal, February 12, 2007 - 1:58 am UTC

hi Tom
I working with ORACLE 10g database and from last 28 hours I am getting message ORACLE INITIALIZATION SHUTDOWN IN PROGRESS ERROR, and I am not able to start Database from last 28 hours. I know this is a general error. have started my services again and again but it not working. My database server is in Windows 2000 Server. So please give me some solution for this error.

February 12, 2007 - 10:33 am UTC

check out your alert log and see what it is saying

SERVICE_NAME vs. SID

Marcel, February 13, 2007 - 8:50 am UTC

Hi Tom,

I have a question about the parameter service_name and sid

with both I can connect me to the db
when I want to use the service_name option I must set

db_domain=oracle.com
db_name=db1
global_name=true
with these parameters the resulting service_name would be

db1.oracle.com

when I use the sid option within the tnsnames.ora the sid is used instead of service_name !

But where exactly is the difference between these two connection models

thanks in advance
marcel

February 13, 2007 - 10:07 am UTC

An instance has a single sid.

An instance may register with the listener using many service names (each with different resource profiles and such), in fact many instances may register with a listener using the same service.

A sid is so 1980, the service naming technique is the appropriate one going forward.

Alexander, June 27, 2007 - 3:01 pm UTC

Tom,

I have the same question as Marcel. I don't really understand what is better about the SERVICE_NAME from your explanation. It sounds like using a SID is more clear as to what you are connecting to. Can you explain why this is the new and/or better approach? Thanks.

July 02, 2007 - 10:10 am UTC

it is a layer of indirection.

A single service can point to many instances (eg: think CLUSTERING).

Services allow for dynamic registration with a listener.

A sid is limited in size and in general never need be known outside of the server itself.

DB Conneciton issues

Darin, July 13, 2007 - 3:18 pm UTC

Hello Tom,

We host a DB that is accessed by a remote client application via JDBC (not sure if they roll their own, or using someone elses JDBC stuff). The client is complaining that they are "not able to establish connections" within their application enforced 25 sec time-out.

System info:
Windows Server 2003
Recently migrated our data to a new 10.2.0.3 instance - the issues has only really revealed itself since the migration.
Oracle is set up for MTS
Client is using a connection pooling feature on their side also (I understand this can cause some issues, accoridng to your book).

I've been examining the listener.log & listener.trc files for evidence of failed connection attempts. I'm not seeing such, but not sure that I understand all in the log file either - and I def need some clarification on the trace file (questions will follow).

Example of listener.log entry(which appears to be successful connecton established):
13-JUL-2007 11:10:07 * (CONNECT_DATA=(SID=prd4)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=166.70.145.12)(PORT=14604)) * establish * prd4 * 0
note: we are in MTS mode
(1) Can you clarify what is declared as a connection request and what the log file is showing ESTABLISH in response to? is an entry made in the log file for each NEW connection attempted to the DB (i.e. I logon to the DB from sqlpplus, get my connection but send o query yet = 1 entry in log file), or is there a new entry for each query sent to the DB (i.e. I send the query, so now there are 2 entries in the log file)?

In the listener.trc file, I am not clear from the documentation from what perspective the send & receive packets are referenced. Ora doc states:
Note that the packets being sent or received have a prefix of "---> Send nnn bytes" or "<--- Received nnn bytes" showing that this node is sending or receiving a packet of a certain type and with nnn number of bytes. This prefix enables you to determine if the node is the client or the database server. The connection request is always sent by the client, but received by the database server (or listener).
I understand from this that ---> SEND is from DB to client, and <--- RECEIVE is from client to DB, but the example: ---> Send 198 bytes - Connect packet appears to be a connection request, why would the DB be sending a DB request? followed by <--- Received 76 bytes - Redirect packet - why would the client be redirect the DB?
(2) can you PLEASE clarify the direction of SEND/RECEIVE packets?
(3) does the trace file indicate how long a connectoin request (or even a query request) takes to be processed and returned to the client?

Following is the listener.ora file contents:

# listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

#LISTENER =
#  (DESCRIPTION =
#    (ADDRESS = (PROTOCOL = TCP)(HOST = evo.domain.com)(PORT = 1521))
#  )

TRACE_LEVEL_LISTENER=USER
TRACE_TIMESTAMP_LISTENER=TRUE

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = evo.domain.com)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = prd4.domain)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (SID_NAME = prd4)
    )
  )

In trying to decipher this prolem, I ran lsnrctl services and got the following:

D:\oracle\product\10.2.0\db_1\NETWORK>lsnrctl services

LSNRCTL for 32-bit Windows: Version 10.2.0.3.0 - Production on 13-JUL-2007 13:57:27

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=evo.domain.com)(PORT=1521)))
Services Summary...
Service "prd4.domain" has 2 instance(s).
  Instance "prd4", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:42 refused:0
         LOCAL SERVER
  Instance "prd4", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:17 refused:0 state:ready
         LOCAL SERVER
      "D000" established:22087 refused:9 current:52 max:1002 state:ready
         DISPATCHER <machine: EVO, pid: 2244>
         (ADDRESS=(PROTOCOL=TCP)(HOST=evo.domain.com)(PORT=5001))
Service "prd4_XPT.domain" has 1 instance(s).
  Instance "prd4", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:17 refused:0 state:ready
         LOCAL SERVER
      "D000" established:22087 refused:9 current:52 max:1002 state:ready
         DISPATCHER <machine: EVO, pid: 2244>
         (ADDRESS=(PROTOCOL=TCP)(HOST=evo.domain.com)(PORT=5001))
The command completed successfully

This confuses me also b/c we only have a single service registered (I thought) which is prd4 - not at all sure what prd4_xpt is.

I appreciate you insight into some of these issues.

Best Regards

July 13, 2007 - 7:49 pm UTC

can they connect with anything (lots of stuff there, just glanced at it)

any firewalls involved?

why are you using shared server, if clients have connection pool - not wise.

can the listener be listening and connect to a proc?

donald, August 16, 2007 - 11:13 am UTC

noting that the listener can have multiple ports to listen to, can the listener be attached to port 1533, and if any data is presented on that port, directly pass that data to a procedure, that will send data back out for a return on that same port. This would be without the 'connection' directly requesting a session. We want to 'hard wire' a connection directly to the database for a specific user running a legacy application that can not be changed, without creating a gateway or some other intermediate device. So I have been trying to figure out I can attach a package.procedure to a specific listener port and thus to the database (10r2). by the way, this is a dedicated environment and not open to the ususal security issues.

August 20, 2007 - 10:08 pm UTC

the listener does one thing

it connects you to the database and gets the heck out of the way.

the listener does not pass anything to the database, ever really - it just gets you connected.

sounds like you might want the equivalent of 'asktom' however. I use mod_plsql, un-authenticated (you need not log in).

You send an http request to mod_plsql
it runs a procedure and returns the result
and it is (usually) already logged in

Listener configuration - Multiple IP's for a single db

Thiru, October 16, 2007 - 4:49 pm UTC

The server where the db resides has two IP address. We are unable to connect to the server from any client using the ip address that is in the Listener.ora? Any attempt to do results in a Timout. Not sure if a second IP could be added to the listener and will that solve the issue. If so, can you please let us know how to ddo that?

1 to 3 - 1 listerner running on 3 ports

jas, March 13, 2008 - 12:45 pm UTC

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 13-MAR-2008 12:38:03

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.x.xx1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 9.2.0.4.0 - Production
Start Date 07-MAR-2008 13:58:50
Uptime 5 days 21 hr. 39 min. 13 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /data01/app/oracle/product/9.2/network/admin/listener.ora
Listener Log File /data01/app/oracle/product/9.2/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.xx1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=204.71.32.xx1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.xx2)(PORT=1526)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=204.71.32.xx2)(PORT=1526)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.xx3)(PORT=1527)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=204.71.32.xx3)(PORT=1527)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.xx4)(PORT=1527)))
((DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
.
.
.
.
..
.
.
.

The command completed successfully

Tom,

1 listener is running on 3 different ports. I am confuse that how is it possible.

March 15, 2008 - 8:43 am UTC

why are you confused?

programs can listen on as many ports as they wish. By default it listens on different ports these days - for different "things", port 8080 or 80 for HTTP traffic, 2100 for other things and so on. Very normal

ip=first in listener.ora

Pasko, March 15, 2008 - 1:12 pm UTC

Hi Thiru,

i don't know which Oracle Version you have, but may be this could be helpful.There is a parameter (IP=FIRST) in the listener.ora File

-----------------------------------------------------------
IP=FIRST creates a listening endpoint with the ip address which the HOST resolves. By default (without IP=FIRST) it will listen on INADDR_ANY, which will be all IP-Addresses on the Database Server!

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

Listener Question

Arju, March 24, 2008 - 5:25 am UTC

My question is about listener. When I log on to database an entry is created about connection. But how I can get an entry from when any session/client is disconnected from database?

March 24, 2008 - 11:22 am UTC

auditing - database auditing. The listener log doesn't provide you a good audit trail (you can create sessions without using that listener), you want database auditing it sounds like.

One LISTENER for both 9i and 10g

suresh, June 26, 2008 - 2:37 pm UTC

Tom,

I am bit confused about how the LISTENER works when we have two oracle home ( 9i and 10g ). The problem I am facing is that : Only when the Listener is started using the 10g tnslsnr(/u01/app/oracle/products/10.2.0/bin/tnslsnr) that I am able to connect to the 10g database from a client. If the 9i tnslsnr is used to start the Listener, I get the error "unable to start a dedicated server process"

The steps that I took are:

a. 9i oracle's LISTENER is configured to listen to the new 10g database ( new 10g Oracle Home). That means I have my Oracle 10g database listed in 9i Listener with the 10g Oracle Home.

The Listener status is:
LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER_92
Version TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
Start Date 26-JUN-2008 17:03:12
Uptime 0 days 1 hr. 18 min. 50 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/products/9.2.0/network/admin/listener.ora
Listener Log File /u01/app/oracle/products/10.2.0/network/log/listener_92.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=133.44.5.66)(PORT=1521)))
Services Summary...
Service "BO_PROMO" has 1 instance(s).
Instance "BO_PROMO", status UNKNOWN, has 1 handler(s) for this service...
Service "BO_PROMO.boq.lab.axe.com" has 1 instance(s).
Instance "BO_PROMO", status READY, has 1 handler(s) for this service...
Service "H_QA_NXT" has 1 instance(s).
Instance "H_QA_NXT", status UNKNOWN, has 1 handler(s) for this service...
Service "H_QA_NXT.boq.lab.axe.com" has 1 instance(s).
Instance "H_QA_NXT", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "RTESTQA" has 2 instance(s).
Instance "RTESTQA", status UNKNOWN, has 1 handler(s) for this service...
Instance "RTESTQA", status READY, has 1 handler(s) for this service...

---------------
The server RTESTQA is the 10g instance.

b. Sourced the .profile_RTESTQA that points to Oracle 10g Home, but points the TNS_ADMIN to Oracle 9i.

c. did a lsnrctl and did a start LISTENER_92.

With the above, the connectivity is fine from the client. But had I used the .profile of 9i oracle home that has the 9i Oracle Home and the TNS_ADMIN of 9i, the connectivity errors out though Listner starts up with all the above services.

Is it a requirment that the 10g tnslsnr be used or do you see any issue in this set up?

Appreicate your time.

June 26, 2008 - 4:36 pm UTC

the only supported configuration is to use the listener of the highest version.

You cannot use the 9i listener for 10g, that is not a supported configuration.

Two Listeners using the same Virtual IP

suresh, July 01, 2008 - 3:10 pm UTC

Thanks Tom for the reply. A quick followup question if you may:

We use Virutal IP for failover purposes. Can the two Listeners (LISTENER_92 (port 1521) and LISTENER_10G (port 1522)) have the same Virtual IP in the Listener.ora?

multiple listener

A reader, September 18, 2008 - 3:10 pm UTC

Greetings thomas,

if i had multiple listeners to the same database.
1) how can i know which session using which listener?
2) active listeners? (Listeners that have connections)

Thanks.

September 18, 2008 - 9:38 pm UTC

sessions do not "use" a listener.

the listener is used briefly - as a traffic cop - to get to the database. After you connect - you could shutdown the listener if you wanted.

listeners are transient things, they redirect - they do not have persistent connections.

multiple listener

A reader, September 19, 2008 - 12:45 am UTC

Greetings thomas,

and thanks like always.

If i had multiple listeners how could i know the active one?
lets say i have 5 listener, and i want to stop 3, how you suggest to do that, without any connection lost.
it could be just 2 listeners are listening and the other three are not, so how could i determine?

September 19, 2008 - 7:39 am UTC

they are all active by definition.

Any, all of them could be used.

They are all listening by definition, by the very fact they are running

and you, as the owner of this system, configure this stuff - you would know which ones YOU need.

Multiple Listener

M.Senthil Kumar, September 24, 2008 - 3:51 am UTC

In case of multiple listener each listener will (should ) have a different name.
In the lsnrctl you can check the status of each listener

lsnrctl> status prod
Which will give the status of the listener with the name <<prod>>

lsnrctl>status test
Which will give the status of the listener with the name <<test>>

Best Regards
Senthil Kumar. M

September 24, 2008 - 7:21 pm UTC

so?

how would that get them any closer to an answer?

Eh, by gum, lad

DJB, September 25, 2008 - 11:56 am UTC

'so?

how would that get them any closer to an answer?'

You've a sharp tongue in your head, you have, young man ! :)

9i listener stops from other server

Sabi, January 19, 2009 - 12:12 am UTC

Hi Tom,

I have recently came across issue with 9i Listener. In Server name linux02 where the listener is running with the port 1526. If I do the same configuration on other server name linux04 with the host pointing to linux02 and port 1526, i am able to stop the listener but cannot start.

Note : I have found whoever has the execute permission can able to stop the listener running on other server if configured. Due to some secutiry reason this cannot be kept for long and need your aid to fix this, please, your help would be highly appreciated.

Thanks,
Sabi

January 19, 2009 - 9:11 pm UTC

insufficient data here to say anything.

We have linux02 running a listener on port 1526

... If I do the same
configuration on other server name linux04 with the host pointing to linux02
and port 1526, i am able to stop the listener but cannot start.
...

now you lost me. but sounds like you might want to password protect your listners? Have you read about that?

Listener Status for External Procedure

Joe C., March 12, 2009 - 10:03 am UTC

I noted that the beginning of the output of every listener status command connects to EXTPROC

...
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)KEY=EXTPROC)))
STATUS of the LISTENER ...

===========================================================
We are having a problem with our configuration for EXTPROC and after "lsnrctl status" I noted that we are not "Connecting..." like the rest of the world: instead of the IPC protocol and a KEY, we are getting a TCP protocol and an empty HOST. Does this signify that anything is amiss?

...
LSNRCTL for 64-bit Windows: Version 10.2.0.4.0 - Production on 11-MAR-2009 10:13:37

Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER ...
...

March 12, 2009 - 1:02 pm UTC

it depends entirely on the setup of your listener.ora

what does that look like

Joe C., March 12, 2009 - 2:35 pm UTC

# listener.ora.dot0dta1dbrac04 Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\listener.ora.dot0dta1dbrac04
# Generated by Oracle configuration tools.

LISTENER_DOT0DTA1DBRAC03 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
(ADDRESS = (PROTOCOL = TCP)(HOST = dot0dta1dbvip03.mdot.w2k.state.me.us)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = dot0dta1dbrac03.mdot.w2k.state.me.us)(PORT = 1521)(IP = FIRST))
)
)

SID_LIST_LISTENER_DOT0DTA1DBRAC03 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\asm_1)
(PROGRAM = extproc)
(ENVS="EXTPROC_DLLS=c:\windows\syswow64\crtdll.dll")
)
)

March 12, 2009 - 2:48 pm UTC

is that word wrapping correct? Nothing can start in column 1 other than the very first bit before the = sign

(and do you want me to delete this since you have your host names in there?)

Joe C., March 12, 2009 - 3:13 pm UTC

Yes, it "was" wrapping; and, yes, here's the file w/o the domain -- a resend.

LISTENER_DOTDTADBRAC03 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
(ADDRESS = (PROTOCOL = TCP)(HOST = dotdtadbvip03)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = dotdtadbrac03)(PORT = 1521)(IP = FIRST))
)
)

SID_LIST_LISTENER_DOTDTADBRAC03 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\asm_1)
(PROGRAM = extproc)
(ENVS="EXTPROC_DLLS=c:\windows\syswow64\crtdll.dll")
)
)

March 12, 2009 - 4:26 pm UTC

when you do your listner status, are you asking for the status of DOTDTADBRAC03 ?

what is the exact command you are using? Seems you might be trying to contact the default listener - which you don't have configured.

Joe C., March 13, 2009 - 8:43 am UTC

This is a 2 node rac cluster and the listener is in the ASM.
Below is the lsnrctl status from node dotdtadbrac03:
----------------------------------------------------
C:>lsnrctl status

LSNRCTL for 64-bit Windows: Version 10.2.0.4.0 - Production on 13-MAR-2009 06:55:24

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER_dotdtadbrac03
Version TNSLSNR for 64-bit Windows: Version 10.2.0.4.0 - Production
Start Date 12-MAR-2009 10:54:28
Uptime 0 days 20 hr. 0 min. 57 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oracle\product\10.2.0\asm_1\network\admin\listener.ora
Listener Log File D:\oracle\product\10.2.0\asm_1\network\log\listener_dot0dta1dbrac03.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.99.57)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.99.39)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+asm1", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
Instance "+asm1", status BLOCKED, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "SYS$SYS.KUPC$C_1_20090312200158.FREEDEV" has 1 instance(s).
Instance "freedev1", status READY, has 2 handler(s) for this service...
Service "SYS$SYS.KUPC$S_1_20090312200158.FREEDEV" has 1 instance(s).
Instance "freedev1", status READY, has 2 handler(s) for this service...
Service "freedev" has 2 instance(s).
Instance "freedev1", status READY, has 2 handler(s) for this service...
Instance "freedev2", status READY, has 1 handler(s) for this service...
...
etc.
...
The command completed successfully

March 13, 2009 - 10:52 am UTC

the listener is in the ASM ? what does that mean?

lsnrctl status

you did a status on the default listener. It does not seem you are interested in the default listener, but rather:

LISTENER_DOTDTADBRAC03 =
SID_LIST_LISTENER_DOTDTADBRAC03 =

in the DOTDTADBRAC03 listener.

http://docs.oracle.com/docs/cd/B19306_01/network.102/b14212/admintools.htm#sthref554

how about

lsnrctl status DOTDTADBRAC03

Joe C., March 13, 2009 - 1:25 pm UTC

We've only got the one. And the o/p is the same, except for the "Connecting to..." (before the "STATUS of the LISTENER"). Still, it doesn't reference the EXTPROC.
------------------------------------------------------
C:\Documents and Settings\Oracle>lsnrctl status dotdtadbrac03

LSNRCTL for 64-bit Windows: Version 10.2.0.4.0 - Production on 13-MAR-2009 11:57:26

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

Connecting to (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=dotdtadbrac03))(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.99.39)(PORT=1521)))

STATUS of the LISTENER
...
--------------------------------------------------------
To note: we have a ticket in for the EXTPROC problem. Oracle thinks it is due to a 32-bit vs 64-bit discrepancy, but we've ruled that out. I am merely doing some brainstorming here, trying to connect the dots, noting that the o/p of our lsnrctl status doesn't display the "Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))" ...

March 13, 2009 - 3:10 pm UTC

that connecting to not being there is OK, you got connected to the listener - else you would see nothing in the output.

do a SERVICES instead of STATUS request - use the listener name.

Joe C., March 13, 2009 - 3:29 pm UTC

LSNRCTL for 64-bit Windows: Version 10.2.0.4.0 - Production on 13-MAR-2009 15:16:17

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

Connecting to (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=dotdtadbrac03))(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.99.39)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+asm1", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:340 refused:0 state:ready
LOCAL SERVER
Service "+ASM_XPT" has 1 instance(s).
Instance "+asm1", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:340 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:3 refused:0
LOCAL SERVER
Service "SYS$SYS.KUPC$C_1_20090312200158.FREEDEV" has 1 instance(s).
Instance "freedev1", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=dot0dta1dbvip03)(PORT=1521))
"DEDICATED" established:20 refused:0 state:ready
LOCAL SERVER
Service "SYS$SYS.KUPC$S_1_20090312200158.FREEDEV" has 1 instance(s).
Instance "freedev1", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=dot0dta1dbvip03)(PORT=1521))
"DEDICATED" established:20 refused:0 state:ready
LOCAL SERVER
Service "freedev" has 2 instance(s).
Instance "freedev1", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=dot0dta1dbvip03)(PORT=1521))
"DEDICATED" established:20 refused:0 state:ready
LOCAL SERVER
Instance "freedev2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:7 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=dot0dta1dbvip04)(PORT=1521))
...
etc.
...
The command completed successfully

March 13, 2009 - 3:58 pm UTC

there you go, it is registered there (extproc) okey dokey, it is ready to go.

Listener status/service question

Stuart, April 22, 2009 - 11:12 pm UTC

Hi Tom,

I have a 10.2.0.4 database on RHEL5 which has a web-based application installed locally, and is connecting via OCI thin client, used by approximately 30 people.

When I look at the 'listener status' and 'listener services' output, I see:

[oracle@buggy dbs]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 23-APR-2009 12:21:28

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=buggy.ctru.auckland.ac.nz)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date 07-FEB-2009 12:44:37
Uptime 75 days 0 hr. 36 min. 51 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u04/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u04/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=buggy.ctru.auckland.ac.nz)(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 "jiraprod.ctru.auckland.ac.nz" has 2 instance(s).
Instance "jiraprod", status UNKNOWN, has 1 handler(s) for this service...
Instance "jiraprod", status READY, has 1 handler(s) for this service...
Service "jiraprodXDB.ctru.auckland.ac.nz" has 1 instance(s).
Instance "jiraprod", status READY, has 1 handler(s) for this service...
Service "jiraprod_XPT.ctru.auckland.ac.nz" has 1 instance(s).
Instance "jiraprod", status READY, has 1 handler(s) for this service...
The command completed successfully

[oracle@buggy dbs]$ lsnrctl service

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 23-APR-2009 12:21:36

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=buggy.ctru.auckland.ac.nz)(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
Service "jiraprod.ctru.auckland.ac.nz" has 2 instance(s).
Instance "jiraprod", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Instance "jiraprod", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:25112 refused:0 state:ready
LOCAL SERVER
Service "jiraprodXDB.ctru.auckland.ac.nz" has 1 instance(s).
Instance "jiraprod", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: buggy.ctru.auckland.ac.nz, pid: 19240>
(ADDRESS=(PROTOCOL=tcp)(HOST=buggy.ctru.auckland.ac.nz)(PORT=57567))
Service "jiraprod_XPT.ctru.auckland.ac.nz" has 1 instance(s).
Instance "jiraprod", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:25112 refused:0 state:ready
LOCAL SERVER
The command completed successfully

My init.ora has the following entry:
[oracle@buggy dbs]$ egrep -i 'dispatcher|shared|listener' initjiraprod.ora | grep -v shared_pool_size
*.dispatchers='(PROTOCOL=TCP) (SERVICE=jiraprodXDB)'

The application's server.xml which makes the connection to the database has the following:
url="jdbc:oracle:thin:@localhost:1521:JIRAPROD"

My listener.ora and tnsnames.ora are as follows:

[oracle@buggy admin]$ cat listener.ora
# listener.ora Network Configuration File: /u04/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u04/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = jiraprod)
(ORACLE_HOME = /u04/app/oracle/product/10.2.0/db_1)
(GLOBAL_DBNAME = jiraprod.ctru.auckland.ac.nz)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = buggy.ctru.auckland.ac.nz)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

LOG_FILE_LISTENER=listener

[oracle@buggy admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u04/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

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

jiraprod.ctru.auckland.ac.nz =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = buggy.ctru.auckland.ac.nz)(PORT = 1521))
)
(CONNECT_DATA =
(SID = jiraprod)
(GLOBAL_NAME = jiraprod)
)
)

XML DB appears to be installed (XML is
'VALID' in dba_registry, and lots of XDB objects).

Questions:
1. Where did jiraprod_XPT come from? Is this to do with XML DB?

If so, it looks a bit odd, as the jiraprod_XDB service has had 0 established connections.

2. Looking at v$session.server, all the non-background processes are 'DEDICATED', but because the 'dispatchers' entry is set, I'm in shared server mode.

As I'm using the thin OCI client to connect, I presume the 'dispatchers' entry is redundant?

3. If jiraprod_XDB is having 0 established connections (database has been up for 74 days), then is it also fair to say that XML DB is not being used?

Looks like the ExtProc can be removed.

Thanks in advance

Stuart.

April 24, 2009 - 8:47 pm UTC

_XPT is part of data guard, see Note 367744.1

not entirely sure what you mean in #2

as for #3, it would mean that *service* hasn't been used to interact with XDB remotely - but doesn't mean XDB functionality wasn't utilized in the server itself.

and yes, if you are not using external procedures - deconfigure that, you don't need it.

Listener Question

Stuart, April 24, 2009 - 11:20 pm UTC

Thanks for your reply.

Note 367741.1 was very helpful. It also gave some useful information on XML DB. Thanks.

Just elaborating on #2,... what I meant was:

As I'm using the thin OCI client to connect, and not the Web client (http/XML DB), I am looking to remove the 'dispatcher' entry from my init.ora, as:

a. I am pretty sure that XML DB isn't being used elsewhere by the application (there is no reference to it anywhere in the installation guide of the application).

b. I don't see the need to be in shared server mode. (We only have 30 users, and all our sessions are 'dedicated').

Your thoughts?

April 27, 2009 - 1:34 pm UTC

a) you can almost certainly disable that one since it does appear you are not using it to connect from outside.

b) I would agree, you do not need shared server

A reader, January 26, 2010 - 11:39 am UTC

I am using SQL developer / PL SQL Developer on Windows XP connecting to Oracle 11g r1 on windows .

After few minutes , my session gets disconnected ( perhaps , frequently) .

Per my DBA , he does not see any issues in the server ?

My SQLNET.ORA contains the following

sqlnet.authentication_Service=(NTS)
names.directory_path=(tnsnames,ezconnect)

I added the following entries to this file to see if it solves the issue . Apparently not.

sqlnet.inbound_connect_time =3
sqlnet.expire_time = 10

January 29, 2010 - 2:42 pm UTC

sounds like you have a firewall/network rule that times you out after some minutes of inactivity. You would have to configure the sqlnet expire time on the SERVER (it is dead client detection) to get the server to ping you every n minutes.

But they you would be implicitly (explicitly I guess really) violating your systems security policy.

Probably best to talk to the NETWORK folks, not the DBA and ask them why they are timing out connections and what you are allowed, by your systems security policy, to do.

One last doubt...

Malcolm, February 16, 2010 - 12:04 pm UTC

Tom,

Sorry to revive this old thread. I have just one last doubt, and I've searched the web thoroughly with no success.

My question is the same as jianhui from va on 25 May 2005. How multiple processes share one port?

[quote]
fork/exec -- open file descriptors, it is very natural and done frequently on unix (in fact it is relatively "new" on windows with shared sockets)

you can kill the listener, once it forks, the resource is totally duplicated, the listener closed the connection right after it forked the child.
[/quote]

So I understand that the listener, and all the user background processes are listening to port 1521, and they all have access to this socket connection since they were fork/exec'ed from the listener process.

My remaining doubt is: when data, from any connected client or a new connection request, is sent to port 1521, which process picks it up? How does each process know which data is meant for it? This must be implemented in the protocol somehow, because AFAIK, sockets itself does not support multiplexing.

Thanks for your time.

February 17, 2010 - 8:23 am UTC

.. So I understand that the listener, and all the user background processes are
listening to port 1521 ..

no, they are not.

You need to read up on tcp/ip sockets and how they work - writing a program in C with them would be very educational - you would get down and dirty with them.

The listener listens on 1521 (by default, could be any port). When it receives a new request - it accepts the request - getting a new connected socket (with its own endpoints, separate and distinct from the 1521 thingy).

Now, the listener has these endpoints and forks a copy of itself. Now TWO listeners have these endpoints. The original listener process "forgets" that is has these endpoints (it closes them, it'll never use them) and goes back into its accept loop to accept the next connection. The child listener process closes all file descriptors it knows belong to the listener (things like the 1521 socket) - but keeps open things like stdout, stderr, stdin, and the endpoints of this newly accepted socket. It then execs (overlays itself) the oracle binary - the oracle binary inherits all of the open file descriptors - including stdin, stdout, stderr and the endpoint of that socket the listener accepted.

Now, that oracle binary is the only thing that has that endpoint and it reads/writes it.

Notice how in the 25 May output above, there is a NEW tcp/ip socket port in the second output - that is the new endpoint.

Thanks

Malcolm, February 19, 2010 - 9:41 am UTC

Thank you for the explanation. I probably know enough about TCP sockets to be dangerous.

The reason I thought all background processes continued to listen to port 1521, is because of my (mis)intereptation of the netstat output.

scott@ORA9IR2> !netstat -a | grep 1521
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 *:1521                  *:*                     LISTEN

tcp        0      0 xtkyte-pc.us.orac:55606 xtkyte-pc.us.oracl:1521 ESTABLISHED
tcp        0      0 xtkyte-pc.us.orac:56441 xtkyte-pc.us.oracl:1521 ESTABLISHED
tcp        0      0 xtkyte-pc.us.orac:56454 xtkyte-pc.us.oracl:1521 ESTABLISHED
tcp        0      0 localhost.localdo:56465 localhost.localdom:1521 ESTABLISHED

tcp        0      0 xtkyte-pc.us.oracl:1521 xtkyte-pc.us.orac:55606 ESTABLISHED
tcp        0      0 xtkyte-pc.us.oracl:1521 xtkyte-pc.us.orac:56441 ESTABLISHED
tcp        0      0 xtkyte-pc.us.oracl:1521 xtkyte-pc.us.orac:56454 ESTABLISHED
tcp        0      0 localhost.localdom:1521 localhost.localdo:56465 ESTABLISHED

To me it looks like the new connection is between ports 1521 and (ephemeral) 56465. If the forked process used a different port and forgot/closed 1521, I would expect to see a connection between two ephemeral ports...

Can you please help me understand where I am going wrong? Is it just netstat reporting the server socket (1521) that was used to create the new socket?

Sorry, answered my own question.

Malcolm, February 19, 2010 - 9:55 am UTC

Sorry Tom, I was too hasty to follow-up.

I did a little searching, and answered my own question. Netstat is reporting the server socket that the connection was made on (1521), but once the connection is established, the TCP stack of the OS 'demulitplexes incoming TCP datagrams based on all four values of local address, local port, foreign address and foreign port.'

So I've finally got to the bottom of it... phew.

Thanks again for your time.

A reader, March 03, 2010 - 10:27 pm UTC

<quote>
..... You would have to configure the sqlnet expire time on the SERVER (it is dead client detection) to get the server to ping you every n minutes.
...
</quote>

The DBA did set the SQLNET.EXPIRE time in the server , restarted the listener .. Still no change ...

March 04, 2010 - 9:48 am UTC

then what you say is probably not true.

Tell you what, take the sid/serial#, map that to an application and prove to yourself that said application is in fact no longer running. If it is running - what you are seeing is normal, expected, correct. If it is not running - then the setup for dead client detection was not done correctly or is not working for whatever reason.

Please - first - prove to yourself and all involved that the client application is in fact "exited from", prove that you have in fact an orphan session with no client.

listener.log file

Jatin, May 07, 2010 - 11:11 am UTC

Hi Tom

We want to decommission some of our databases as part of lifecycle management & want to test whether there were any active connections on the database or not (the user community not being sure of whether any business requirement might be for the app/db). So, we decided to check v$session for active connections and listener.log for client connections. In case we find anything,w e try mapping the same with the active_session_history & dba_hist_active_sess_history for the connection history and send out notifications..

Howvever, we come some across cases where listener.log has entries like:

04-MAY-2010 06:46:27 * (CONNECT_DATA=(SID=TIVD01)(CID=(PROGRAM=)(HOST=sf6ux003)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)
(HOST=10.116.168.41)(PORT=53112)) * establish * TIVD01 * 0

and when I map in dba_hist_active_sess_history I find a FOREGROUND process which is actually a sys owned job queue slave (J000 or J001 sort of.. ) at that time (but there are only oracle supplied scheduler jobs in dba_scheduler_jobs). Why do I see this connection in listener.log then? Is it safe to assume this is an INACTIVE session or a TOO QUICK (<1 sec) session, so not captured in ASH?

Is there a way to better interpret listener.log entries to make sure who is connecting at what time and doing what? Is there an equivalent way in 9ir2 (no ASH)?

PS: .. apart from auditing user connections..

May 07, 2010 - 11:44 am UTC

... We want to decommission some of our databases as part of lifecycle management &
want to test whether there were any active connections on the database or not
(the user community not being sure of whether any business requirement might be
for the app/db) ...

oh my gosh, you are kidding right? You have databases that you manage, backup, whatever - and you don't even know if they are used?

And you are going to use a process of "lets just peek at them and see"????

dba_hist is a blurry view of what has happened in the database. It does not contain all sessions - it contains sampled information and will never be a crystal clear picture of what is happening.

Why don't you enable auditing and audit connects - or create an on logon trigger to capture whatever you want (but this entire process is sketchy to me - trying to figure out if a database is "important" by doing this - something is wrong here)

well..

Jatin, May 07, 2010 - 12:57 pm UTC

the whole story is not that simple.

we have a landscape of > 250 db with different ola's working for last several years. over years we have migrated application from one schema to other, bought vendor solutions for part of things and built inhouse stuff for part/full functionality, clubbed db's to save costs and things like that. so application users/owners are not 100% sure if a part of something still be useful (although we are 90% sure of decomssioning). that's why we are relying on some more checks like connections to see if for last 10-15 days is someone on there..? and let's ask why is it still being used..?

can you tell me why i still see user=oracle program=sqlplus as i elaborated above..?

May 07, 2010 - 1:21 pm UTC

first time you mentioned sqlplus, you didn't elaborate on it before...

look for a cron job running sqlplus probably.

still, this is very sketchy, I cannot understand the environment that must be in place to allow something like this to be 'true'. I cannot understand how one could even manage such a set of databases - no one knows what they do, who they do it for, why the exist - IF they should exist. Mind boggling - that's all.

connection to remote is flaky,

A reader, February 04, 2011 - 12:01 pm UTC

I have a DB link from 11.1.0.7 (HP box) to a 11.2.0.1 RAC database on Linux. We are using LISTENER_SCAN on Linux.

I get ORA-12514 when trying to access Linux DB from Hp-UX db. This is not happening always. I get the error and in the next moment it connects. The problem is consistency.

I checked the listener services on Linux and it has registered the DB (and instance name).

How to debug the flakiness in connection? Any thing related to LISTENER_SCAN? I checked the listener.log and did not see any ORA error.

Thanks,

February 04, 2011 - 1:54 pm UTC

listener_scan can be called anything, it is just s listener name.

Is the machine with the listener under a heavy load when this happens?

follow up,

A reader, February 07, 2011 - 12:51 pm UTC

Hello,

It was noticed the listener on other 2 instances (this is a 3 node cluster) did not register database instances. I also checked the log.xml file and found error 12514 in them. I think this was the reason.

Question: Can we configure in such a way that Oracle knows the listener on other 2 instances have not registered the database instances so it can ignore them?

Thanks,

February 09, 2011 - 7:13 am UTC

can we configure *what* in such a way? so that *what* can ignore them?

scan listener,

A reader, March 03, 2011 - 10:51 am UTC

On a two node cluster we have 3 listener_scans. Two of them are runniing on node 1 and the other on node 2. We have 3 different instances on each of the servers.

Lately we are seeing one of the database instance is not registered in LISTENER_SCAN3. This particular instance is registered in SCAN1, SCAN2 and on the node listener.

We tried by "alter system register" and restarting scan listeners and every time SCAN3 is not registering this one particular database instance. Using srvctl command and ping command it is found there is no issue in any of the 3 scan ips. This problem is resulting in ORA-12514 and I can see numerous messages regarding this on listener3 log.xml file.

How to troubleshoot this problem?

Thanks,

Alexander, May 25, 2011 - 12:13 pm UTC

Tom,

Can you explain how a default listener works, and how can we stop one from firing up when dbstart is called? We had a problem recently where our database server was accidentally rebooted, and in our startup scripts we start our defined listeners. The problem was, dbstart was called first and apparently started a default listener in an ancient oracle home that's not being used, and since it was the wrong version on the same port as the current version, the database instance was still unavailable. Can you suggestion how I should appropriately deal with this? Thank you.

May 25, 2011 - 2:19 pm UTC

I don't use dbstart - but it basically scans your /etc/oratab (or where ever it is) and looks for lines that have an oracle sid, oracle home and a "Y" flag and starts those databases.

I would recommend personally creating your own startup script, that is what I've always done myself.

Alexander, May 25, 2011 - 2:23 pm UTC

Ok but I'm talking about listeners, does it do the same for listeners? We typically use our own too this must have been grandfathered in from old times.

May 25, 2011 - 2:27 pm UTC

yes, it starts listeners too - you can just vi the script to see what programs it can run...

if you update your etc/oratab to have N's at the end, it should do "nothing"

How To Upload Questions in this forum

Arjun, May 26, 2011 - 7:15 am UTC

Hi All,

please tell me how to upload my questions here.

i have one question

one table how many foreign key columns may contain

May 26, 2011 - 8:39 am UTC

as many as you like.

A reader, June 13, 2011 - 12:08 am UTC

Due to some reason none of my services are running.... What do I do ???

LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 13-JUN-2011 10:00:53
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Production
Start Date 13-JUN-2011 09:57:10
Uptime 0 days 0 hr. 3 min. 44 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File E:\app\product\11.1.0\db_1\network\admin\listener.ora
Listener Log File e:\app\diag\tnslsnr\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=aaa-123.XXX.yyy.com)(PORT=1521)))
The listener supports no services
The command completed successfully

Listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = aaa-123.XXX.yyy.com)(PORT = 1521))
)
)

June 17, 2011 - 10:51 am UTC

your network windows service is running, but apparently your databases have not started up. their windows services may or may not be started.

but, just start your databases up.

TNS:listener could not find available handler for requested type of server

A reader, August 01, 2011 - 1:56 am UTC

Dear Tom,

I am facing "TNS-12520: TNS:listener could not find available handler for requested type of server" in a production database from time to time. My production database is running in "DEDICATED" server mode and the listener status was showing fine as it was during that error. But whenever that error was apearing I got by querying "lsnrctl services" command:

"DEDICATED" established:332 refused:0 state:blocked

Can you please give us some clue and help?

August 01, 2011 - 11:58 am UTC

see support note: 240710.1

TNS:listener could not find available handler for requested type of server

A reader, August 01, 2011 - 1:09 pm UTC

Dear Tom,

I am sorry but we don't have any metalink access. I am not even sure how to get it. Therefore can you please let me know what can be done with the above case of "TNS:listener could not find available handler for requested type of server"?

August 01, 2011 - 2:11 pm UTC

It is typically due to server overload - the listener blocked new connections because the number of processes (as configured by processes) is going to be exceeded.

so, what is processes set to

and can your machine really handle that many active connections....

TNS:listener could not find available handler for requested type of server

A reader, August 01, 2011 - 9:39 pm UTC

Dear Tom,

Yes even I expected that also. That's why I had increased the "processes" parameter from 150 to 200. However the production database is running on "DEDICATED" server mode and I think it has to be transformed into "SHARED" server mode. Am I correct in my future action of course?

August 02, 2011 - 7:04 am UTC

Why do you need 200 connections? Are you running client server?

Why are you not queueing at the application server level? That is what app servers are supposed to do - to act as transaction processing monitors, as resource governors - not as resource killers.

shared server would be/could be one approach HOWEVER, you have to still figure out the maximum number of sessions - and then configure the SGA's large pool to be large enough to hold the sum of all of the UGA's (session memory) for those sessions statically. It is a somewhat suboptimal solution and would be best handled by controlling the number of connection pool connections.

TNS:listener could not find available handler for requested type of server

A reader, August 02, 2011 - 9:47 pm UTC

Dear Tom,

Thanks for the response. I was forced to increase my "processes" parameter from 150 to 200 since I saw the "select count(*) from v$session" was reaching about 146 (I may be wrong in my actions, Please point out what needed to be done).
But I observed that there are many "INACTIVE" sessions in my database from very long time (may be since morning even). That's why I thought if I can make the application to connect as shared server mode to the database, may be the no. of so many sessions minimizes. Also the application which we have in the front end is a .NET application which is the main cause of generating so many INACTIVE sessions.

August 03, 2011 - 7:30 am UTC

just re-read my answer above, I would write the same again in response to this. No change in my answer...

dynamic registration in listener

rizwan, August 18, 2011 - 4:18 am UTC

I am having two question related to dynamic registration in listener .. Below are the details 

C:\Users\rghadiya>set ORACLE_SID=ORCL

C:\Users\rghadiya>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Aug 18 14:26:58 2011

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


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

SQL> show  parameter service_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      TEST
SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      ORCL
SQL>

C:\Users\rghadiya>set ORACLE_SID=TESTING

C:\Users\rghadiya>sqlplus / as sysdba"

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Aug 18 14:28:39 2011

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


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

SQL> show  parameter service_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      TEST
SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      testing
SQL>

C:\Users\rghadiya>lsnrctl start

LSNRCTL for 32-bit Windows: Version 10.2.0.5.0 - Production on 18-AUG-2011 14:29:47

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

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 10.2.0.5.0 - Production
Log messages written to D:\oracle\product\10.2.0\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DTPW7-RGHADIYA.corp.capgemini.com)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.5.0 - Production
Start Date                18-AUG-2011 14:29:53
Uptime                    0 days 0 hr. 0 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         D:\oracle\product\10.2.0\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DTPW7-RGHADIYA.corp.capgemini.com)(PORT=1521)))
The listener supports no services
The command completed successfully

C:\Users\rghadiya>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.5.0 - Production on 18-AUG-2011 14:31:02

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.5.0 - Production
Start Date                18-AUG-2011 14:29:53
Uptime                    0 days 0 hr. 1 min. 14 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         D:\oracle\product\10.2.0\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DTPW7-RGHADIYA.corp.capgemini.com)(PORT=1521)))
Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCL_XPT" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "TEST" has 2 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
  Instance "testing", status READY, has 1 handler(s) for this service...
Service "testing" has 1 instance(s).
  Instance "testing", status READY, has 1 handler(s) for this service...
Service "testingXDB" has 1 instance(s).
  Instance "testing", status READY, has 1 handler(s) for this service...
Service "testing_XPT" has 1 instance(s).
  Instance "testing", status READY, has 1 handler(s) for this service...
The command completed successfully


My question is 

1) My service in both databases (ORCL AND testing ) is TEST .. Then why service with testing and ORCL is getting registered in listener ..
Is it that if service_name and instance_name doest not match then oracle register one service with "service_names" and one service with "instance_name"
both having same instance_name 

2) If i only include below lines in tnsnames.ora .. which instance doest it connects to .. i checked that it gets connect to ORCL sometimes and sometimes testing .. why is that so ? what is the algorithm ?

RIZ =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = {hostname} )(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST)
    )
  )



C:\Users\rghadiya>sqlplus system/rizwan@RIZ

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Aug 18 14:38:37 2011

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


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

SQL> select name from v$database;

NAME
---------
ORCL

SQL>

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\rghadiya>sqlplus system/rizwan@riz

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Aug 18 14:39:31 2011

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

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

August 23, 2011 - 2:49 am UTC

listener services can also be registered from the listener.ora as well as dynamically - and the database will register itself as "itself" in addition to the dynamic names you put in the service_names.

we use a load balancing algorithm based on "load" or round robin. The pmon process reports the server load to the listener every now and then. You can configure it however you wish.

http://docs.oracle.com/docs/cd/E11882_01/network.112/e10836/advcfg.htm#NETAG335

dynamic registration with listener

rizwan, August 22, 2011 - 2:46 am UTC

Hi Tom ,
I am waiting for your answer for above question .

August 30, 2011 - 2:10 pm UTC

geez.

To: rizwan from india

A reader, August 23, 2011 - 9:44 am UTC

You have created a confusing situation for Oracle. You have taken two databases and created a service on each of them with the same name, TEST.

Now the listener is showing two handlers for this service which is correct since the both database instances are available for this service.

The TNS entry you have shown is correct. However, it will randomly connect to one of the instances. If you attempt connection multiple times to each of the databases, sometimes it will throw an error like you are seeing, sometimes it will be successful.

I have never seen such a situation because I never create services with same name across instances of different databases. It will result in connection errors and cause major confusion. The situation you have created should be avoided in real databases.

August 30, 2011 - 3:15 pm UTC

That is not confusing, we do that all of the time, it is normal, it is expected, it is actually a feature, a well documented one.

He is not seeing any errors, I don't know why you say "it will throw an error"

This is a situation that is FREQUENTLY done in a clustered environment or in a reporting environment where you have more than on reporting instance. It is not something to avoid.

What is SDP?

A reader, August 24, 2011 - 8:03 am UTC

Hi Tom,
What is SDP and SDP listener. I believe it is new infiniband protocol for Exadata. How different is it from TCP?

August 30, 2011 - 3:48 pm UTC

http://docs.oracle.com/docs/cd/E11882_01/network.112/e10836/performance.htm#NETAG0143

A reader, August 30, 2011 - 3:23 pm UTC

Tom,
You said:
=======================
That is not confusing, we do that all of the time, it is normal, it is expected, it is actually a feature, a well documented one.

He is not seeing any errors, I don't know why you say "it will throw an error"

This is a situation that is FREQUENTLY done in a clustered environment or in a reporting environment where you have more than on reporting instance. It is not something to avoid.
=======================

The OP has created service across two non-RAC databases. Please test this situation and then you will know why I said it. I have tested this scenario and I am very familiar with RAC and workload management services.

If you have two non-RAC databases running on same server, serviced by same listener, and you create a service of same name on these separate databases, you will get connection errors because listener will direct connection randomly to either database. The username/password for one database will not work on the second one and you will see invalid username/password error like Rizwan is seeing.

Please let me know where it is documented to create a service of same name on two non-RAC databases running on same server.

Thanks...

August 31, 2011 - 8:39 am UTC

I told you when that would be sensible - and people do it, it is not an error, it is not unusual.

... or in a reporting environment where you have more than on reporting instance.

http://docs.oracle.com/docs/cd/E11882_01/network.112/e10836/concepts.htm#CIACABHJ

A service name can identify multiple database instances, and an instance can belong to multiple services. For this reason, the listener acts as a mediator between the client and instances and routes the connection request to the appropriate instance. Clients connecting to a service need not specify which instance they require.

Not a word about RAC in there.

You are making assumptions. did you see me say "where you have more than one reporting instance" - they would reasonably assumed to be "the same" as far as data, schemas, etc.

So again, not unusual, no errors - when you use it for what it was intended.

In real life, the two databases would be on separate servers (doesn't make sense in production to have them on the same machine) for extra processing power.

A reader, August 31, 2011 - 9:32 am UTC

Tom,
You said:
========
He is not seeing any errors, I don't know why you say "it will throw an error"

========

Actually Rizwan is seeing error:

C:\Users\rghadiya>sqlplus system/rizwan@riz

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Aug 18 14:39:31 2011

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

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

I did testing by creating the scenario Rizwan had created and only then posted the explanation. I have learned from you to create test scenario. If you want, I can create the scenario again and post the results but I will have to do it on 11g if that is acceptable.

Thanks...

August 31, 2011 - 2:00 pm UTC

you know, I don't know how I missed reading that bit - I totally missed the last line.

thanks.

But in general - the setup is 'sound' - it is not highly unusual - if you have two equivalent instances (be they in RAC or not), supporting the same service is "normal"

A reader, August 31, 2011 - 3:17 pm UTC

Tom,
You said:
========
But in general - the setup is 'sound' - it is not highly unusual - if you have two equivalent instances (be they in RAC or not), supporting the same service is "normal" 
========

Rizwan does not have two equivalent instances. These are different databases with different username and passwords. This is the reason why his connection attempt is failing. The following test case shows that connection attempts will randomly fail. So the configuration is undesirable. The test case is 11gr2 with Oracle restart, no RAC. Database names are rnd and orcl.

C:\>set ORACLE_HOME=D:\app\oracle\product\11.2.0\dbhome_1

C:\>set PATH=%ORACLE_HOME%\bin;%PATH%

C:\>srvctl status database -d rnd
Database is running.

C:\>srvctl status database -d orcl
Database is running.

C:\>set GI_HOME=D:\app\oracle\product\11.2.0\grid

C:\>%GI_HOME%\bin\lsnrctl status listener

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 31-AUG-2011 15:44:52

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
Start Date                29-AUG-2011 15:53:25
Uptime                    1 days 23 hr. 51 min. 27 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\app\oracle\product\11.2.0\grid\network\admin\listener.ora
Listener Log File         G:\ADR\diag\tnslsnr\dbserver10\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver10)(PORT=1521)))
Services Summary...
Service "+asm" has 1 instance(s).
  Instance "+asm", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "rnd" has 1 instance(s).
  Instance "rnd", status READY, has 1 handler(s) for this service...
Service "rndXDB" has 1 instance(s).
  Instance "rnd", status READY, has 1 handler(s) for this service...
The command completed successfully

We add services and start them:

C:\>srvctl add service -d rnd -s comm_serv -y automatic

C:\>srvctl add service -d orcl -s comm_serv -y automatic

C:\>srvctl start service -s comm_serv -d rnd

C:\>srvctl start service -s comm_serv -d orcl

Now the listener shows that service comm_serv has two handlers, rnd and orcl:

C:\>%GI_HOME%\bin\lsnrctl status listener

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 31-AUG-2011 15:48:22

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
Start Date                29-AUG-2011 15:53:25
Uptime                    1 days 23 hr. 54 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\app\oracle\product\11.2.0\grid\network\admin\listener.ora
Listener Log File         G:\ADR\diag\tnslsnr\dbserver10\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver10)(PORT=1521)))
Services Summary...
Service "+asm" has 1 instance(s).
  Instance "+asm", status READY, has 1 handler(s) for this service...
Service "comm_serv" has 2 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
  Instance "rnd", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "rnd" has 1 instance(s).
  Instance "rnd", status READY, has 1 handler(s) for this service...
Service "rndXDB" has 1 instance(s).
  Instance "rnd", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\>

Here is my TNS entry on my desktop:

SERV_TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = comm_serv)
    )
  )


From my desktop, I try to connect to rnd database using serv_test (the credentials system/abc123 are valid only on rnd database, system password for orcl is F1dcb3):


X:\>sqlplus system/abc123@serv_test

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 31 15:51:41 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning and Automatic Storage Management options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning and Automatic Storage Management options



X:\>sqlplus system/abc123@serv_test

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 31 15:51:44 2011

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

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


Enter user-name:


X:\>sqlplus system/abc123@serv_test

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 31 15:51:49 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning and Automatic Storage Management options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning and Automatic Storage Management options

X:\>sqlplus system/abc123@serv_test

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 31 15:51:54 2011

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

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


Enter user-name:

X:\>sqlplus system/abc123@serv_test

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 31 15:53:43 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning and Automatic Storage Management options

SQL>

When I get 10 failed attempts, the system user account on orcl gets locked. So what is the use of such configuration?

Now it can be argued that orcl is created as clone of rnd. Then having two databases on same server servicing part of same workload is pointless. 

If orcl is created as clone of rnd on a different server then it is not what Rizwan had done. These will be two separate servers, two listeners, maybe service name is same but nothing shared. So I still fail to see the usefulness of such a configuration.

August 31, 2011 - 3:52 pm UTC

Again, I will say this:

this is not unusual.
this is not abnormal.
this is not something to "not do"

In Rizwans case - it doesn't necessarily make sense since the two databases are not equivalent.

A reader, August 31, 2011 - 6:51 pm UTC

Tom,
If the databases are different, running on same server, serviced by same listener then creating a service with same name for both the databases will result in connectivity issues. Do you agree with this part?

As for the situation where both databases are exactly identical, how is it useful? You have yourself said many times that multiple database on one server do not make sense. They should be consolidated.

Please, please give a case where this setup will be useful. The only reason I am following up on this setup is that I have analyzed this setup from every angle and it is not making any sense to me. I need to know if there is a gap in my understanding.

Thanks...

September 01, 2011 - 7:50 am UTC

databases will
result in connectivity issues.

quite possibly

You have yourself said many times that multiple database on one server
do not make sense.

did you read what I wrote:

In real life, the two databases would be on separate servers (doesn't make sense in production to have them on the same machine) for extra processing power.

I assumed they were testing a multi-database setup on a test server.

A reader, September 01, 2011 - 8:22 am UTC

=======
In real life, the two databases would be on separate servers (doesn't make sense in production to have them on the same machine) for extra processing power.
=======

It just will not work on different databases running on different servers. The listener running on one server will not aware of the load on database running on second server so there will be no server side load balancing. Secondly, how will the TNS entry be constructed? If TNS entry has two host names, and I have not tested this scenario, at the most we might get randomly redirected connections.

What about changes to data? The changes will be randomly applied to any of the two databases. Unless someone really cannot afford to pay for RAC AND they can still afford two servers AND they can run two databases on these servers just for querying data, I do not see any use for such configuration.

The documentation has no mention of such a case. It covers the following scenarios:

One Instance for Each Database
Multiple Instances Associated with an Oracle RAC Database

One Service for Each Database
Multiple Services Associated with One Database

There is no mention of one service for multiple databases running on different servers.

Anyways, your forum, your rules. There is nothing more I can say.

Thanks...

September 01, 2011 - 8:33 am UTC

It just will not work on different databases running on different servers. The
listener running on one server will not aware of the load on database running
on second server so there will be no server side load balancing. Secondly, how
will the TNS entry be constructed? If TNS entry has two host names, and I have
not tested this scenario, at the most we might get randomly redirected
connections.

pmon notifies a listener of its load. It does this in RAC all of the time as you know (how do you think we load balance on RAC?)

But, not that it matters. It would/could use simple round robin - which is better than everyone going to a single database if you actually have two of them and consider them equivalent. People use round robin in RAC too - it doesn't have to be load based.

What about changes to data?

The two databases are equivalent - they are *reporting instances* (as stated previously). You (the implementer) have said "you may run the query here or there or even maybe over there".

Maybe they are fed by a logical standby - they are very close in time to production and very close to each other - and both are closer than close enough for the reporting needs.

Maybe they are a duplicated data mart database - stamped out at the same time.

Maybe they are using replication.

Maybe ....

The documentation has no mention of such a case.

I quoted the documentation above, I'll do it again:

http://docs.oracle.com/docs/cd/E11882_01/network.112/e10836/concepts.htm#CIACABHJ

A service name can identify multiple database instances, and an instance can belong to multiple services. For this reason, the listener acts as a mediator between the client and instances and routes the connection request to the appropriate instance. Clients connecting to a service need not specify which instance they require.

Where does it say "and this is exclusively for RAC"?

The absence of something doesn't mean it

a) doesn't work
b) shouldn't work
c) is nonsensical.

We do not tell you in the documentation that you can use Oracle to store Questions and answers and reviews and followups. Does that mean you should not? That you could not?

There is no mention of multiple databases because the discussion centers around INSTANCES

ORA-12520

A reader, September 02, 2011 - 2:44 pm UTC

Tom:

Can you clarify this:

according to oracle description for ORA012520 it seems different from what describe above as there is a server overload and the PROCESSES prameter is not large enough.

------------------------------------------------------
ORA-12520: TNS:listener could not find available handler for requested type of server
Cause: The type of service handler requested by the client is incorrect or not registered for the requested SERVICE_NAME/INSTANCE_NAME, or the database instance is not registered with the listener.
Action: If you suspect the problem is the wrong type of service handler, perform these steps:
1. If (server=value) is set is in the connect descriptor, ensure that the value is set to the appropriate service handler type for the database, that is, dedicated for dedicated server or shared for dispatchers. You can use the Listener Control utility SERVICES command to see what service handlers are currently registered with the listener.

See Also:
"Monitoring Services of a Listener"

2. If USE_DEDICATED_SERVER is set to ON in the sqlnet.ora file, then ensure the database is configured to use dedicated servers. If it is not, set this parameter to off.
3. Ensure that the database instance is running. If the instance not running, start it so that it can register with the listener.
------------------------------------------------------

Would this be resolved by increasing processes parameter or SGA if there is enough memory on server

September 03, 2011 - 11:09 am UTC

I'll say what I said before regarding this error message:

see support note: 240710.1

Not sure how that is "different" from what is described anywhere? Not sure what you mean by your comment.

listener

A reader, December 19, 2011 - 3:25 pm UTC

Tom:

is it true that in 11g the default setting for this

USE_SHARED_SOCKET

allows all communication to stay on port 1521?

is this the only port he firewall needs to have open

in previous versions the listener was handling the connection between client to oracle server process on ports

`021 - 65835.

December 19, 2011 - 5:47 pm UTC

if you are using dedicated sever - yes, if you are using shared server, then it is different - you need to open up for the dispatchers.

ports

A reader, December 20, 2011 - 11:20 am UTC

Tom:

I am getting confused now. I want to set up 11g database in dedicated mode on RHEL.

Does not this cause problems when all connections are using same port 1521.

I want to tell the firewall guss to open 1521 and ports 1021 - 65336. Do i need the range or not?

According to oracle, if a port is busy it will get the other port in the range for service.

http://docs.oracle.com/cd/B28359_01/install.111/b28264/ports.htm

Most port numbers are assigned during installation. Every component and service has an allotted port range, which is the set of port numbers Oracle Database attempts to use when assigning a port. Oracle Database starts with the lowest number in the range and performs the following checks:

•Is the port used by another Oracle Database installation on the host?

The installation may be up or down at the time; Oracle Database can still detect if the port is used.

•Is the port used by a process that is currently running?

This could be any process on the host, including processes other than Oracle Database processes.

If the answer to any of the preceding questions is yes, then Oracle Database moves to the next highest port in the allotted port range and continues checking until it finds a free port.

December 20, 2011 - 11:35 am UTC

Does not this cause problems when all connections are using same port 1521.

nope

I want to tell the firewall guss to open 1521 and ports 1021 - 65336

why do you want to do that. why not just open 1 to 64k if you are going to pretty much open them all up??????

According to oracle, if a port is busy it will get the other port in the range
for service.

it is not "busy" as in "too much traffic" sam - it is "busy" as in "something else is using it so you CANNOT use it"

a service only needs one port. A port can only be used by one service. (service being a generic term, not a database service - but a service like smtp, ftp, telnet, etc).

But once that service has that port, they can support everyone through it.

ports

A reader, December 20, 2011 - 11:54 am UTC

Tom:

interesting!

<<it is not "busy" as in "too much traffic" sam - it is "busy" as in "something else is using it so you CANNOT use it" >>

it seems like a one dentist using (one chair) to service all his clients and having other 10 chairs in the office wont help because he is busy and can only service one person at a time until completion.

so having a port range or other ports available does not speed things up or improve scalability? just wondering why would oracle have port range sometimes.

so here is my last question (I wont bug you after it)

Do you recommend using this setting (USE_SHARED_SOCKET) for dedicated server? and what ports range you would ask the firewall admin to open up for database if you were setting this 11g database on RHEL?

thanks a lot for you help.

December 20, 2011 - 12:03 pm UTC

Sam -

you should get a book on TCP/IP and learn about sockets and TCP/IP in general.

there isn't just one physical circuit everyone on the same port is going through. A port is just an address of something that provides a service, you can have many sockets open on that port at the same time, no problem. They all have their own end points. Typically the only thing that would fit your analogy is the fact that you have a single network card in most cases. That is the shared thing. Sockets are a dime a dozen - to get one started you need an "endpoint" - that is what the port represents. We connect to that port and get a socket open and can start talking. But we are not taking bandwidth away from others on that same socket - we are in a way taking bandwidth away from everyone on every socket because we are now using the same network interface.

use_shared_socket was for windows, you said you are on RHEL, it isn't relevant to you. It is defaulted to true on the only platform that cares about it - windows

http://docs.oracle.com/cd/E11882_01/win.112/e10845/ap_net.htm#NTQRF667

it should not be set explicitly.

just wondering why would oracle have port range sometimes.

because we sometimes use dynamically assigned ports - you can limit us to a range for firewall purposes.

because we sometimes have multiple processes set up on a range of ports (like dispatchers, like pooled dedicated servers).

has nothing to do with the scalability of a socket on a port though.

listener

A reader, December 20, 2011 - 12:18 pm UTC

TOm:

Ok, great that confirms my thinking it is a window thing.

You seemed to laugh when i said i need ports 1521 and 1021 - 65336.

what shall ia ask for

1521 and 1021 - 1200 or what?

let us say 150 concurrent sessions mixed (client/server and oracle 10g HTTP server).

December 20, 2011 - 12:26 pm UTC

how about just 1521 Sam?

just 1521.

why do you need, want, desire the others - it isn't like we are going to use them.

We use 1521 if you tell us to use 1521.

If you set up something to use 1021 - 1200 - go for it, and then configure your firewall.

ports

A reader, December 20, 2011 - 1:06 pm UTC

Tom:

Thanks, that is what i need to hear.

I was brain washed by a DBA before on how things work.

He told me oracle uses 1521 only for listener (inital connection) but then hands off the connection between client and server process to another port in the range of 1021 - 65336)

it seems this only applies for windows and not unix.

I will only ask now to open up port 1521 now.

I will only ask for the OEM default ports and range using TCP or HTTP as listed in this this document

http://docs.oracle.com/cd/B28359_01/install.111/b28264/ports.htm

I will also get a primer on how TCP/IP internally works.

December 20, 2011 - 2:14 pm UTC

He told me oracle uses 1521 only for listener (inital connection) but then
hands off the connection between client and server process to another port in
the range of 1021 - 65336)

that sounds like shared server with dispatchers. Or database resident connection pooling (DRCP) with dedicated servers.

Using our normal bequeath protocol - the client inherits the tcp/ip connection from the listener, there is no port switching.

it seems this only applies for windows and not unix.

it is true on windows and unix.

listener ports

A reader, December 21, 2011 - 10:48 am UTC

Tom:

quick check on reconfiguring ports.

oracle HTTP server normally uses by default HTTP 7777, 7778 and ports range for 7777 - 7877 for unencrypted traffic and HTTPS 4443, 4444 and ports range 4443 - 4543 for secure traffic.

We would like to switch the application to use 80 and 443 since some users seem to block other ports in their firewalls.

Can those be changed to use default ports HTTP 80 and HTTPS 4443 or you are stuck with oracle ports range?

December 21, 2011 - 11:07 am UTC

are you using port 80 for anything else?

listener port

A reader, December 21, 2011 - 10:02 pm UTC

Tom:

no, we are not using port 80 or 443 on the oracle HTTP server. the application is secure so we should use 443.

we have another plain apache web server that serves files using URLs on port 80.

I was wondering if the oracle HTTP server can be configured to serve the same function of that web server too (i know it is apache based) without using mod_plsql or any database work so users can download files directly from the filesystem.

December 22, 2011 - 10:03 am UTC

so go ahead and use them Sam. read the manuals and set them up any way you like. they are not hard coded.

ports

A reader, December 26, 2011 - 12:11 pm UTC

Tom:

Ok, thanks i will configure oracle HTTP server for 80 and 443. I appreciate you opinion for these two questions:

1) do you normally recommend (for new 11g DB installations) to change the default listener port from 1521 to something else like 1525 for extra security - or the firewall should take care of it and it is more of a false sense of security.
port 1521 is only open for web servers and report server in the DMZ but people can hack these machines and get into the DB server using 1521.

2) can oracle http server show file in a directory and download files to users just like a plain Apache web server or you always have to do some DB work for file downloads. I do not want to use mod_plsql for this particular case or load files to database.

December 26, 2011 - 1:50 pm UTC

1) it would not hurt and would help defeat a "standard" attack tool - one that is programmed to find weakness in default configurations. If you have your firewall properly configured - short of a bug in the firewall (which is entirely possible) - you technically don't need to do this, but it certainly doesn't hurt.

2) yes, you can enable directory indexing if you want. It is not advisable, it would be better to create a page that lists explicitly the files you want people to download.

listener

A reader, December 26, 2011 - 6:32 pm UTC

Thanks Tom. God Bless!

Hope to see you in top shape next year :-)

A reader, January 26, 2012 - 2:49 pm UTC

hi tom,

I have 2 machine and i am on linux , 10g.

192.168.7.32 (server1)(db)
192.168.7.15 (server2)(remote listener)


I am trying to connect to the remote listener to access db@server1.

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

on server 1, i have

- alter system set remote_listener = listener_intr;
- edited tnsname.ora

listener_intr=

(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.15)(PORT=1522)))


on server 2, i have
- added a new listener

listener_intr =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.15)(PORT = 1522))
)

- added a new tnsname entry as well

starhubstag =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.15)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME=starhub)
(SERVER = DEDICATED)
)
)

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

On Server 1
- alter system register

On Server 2

[oracle@localhost admin]$ lsnrctl
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 27-JAN-2012 03:01:10
Copyright (c) 1991, 2005, Oracle. All rights reserved.

Welcome to LSNRCTL, type "help" for information.
LSNRCTL> status listener_intr

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.7.15)(PORT=1522) ))

STATUS of the LISTENER

Alias listener_intr
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 26-JAN-2012 04:01:07
Uptime 0 days 23 hr. 0 min. 8 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/list ener.ora
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listen er_intr.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.15)(PORT=1522)))
Services Summary...
Service "starhub" has 1 instance(s).
Instance "starhub", status READY, has 1 handler(s) for this service...


I have also managed to tnsping

[oracle@localhost admin]$ tnsping starhubstag
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 27-JAN-2012 03:03:08
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.15)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME=starhub) (SERVER = DEDICATED)))

OK (0 msec)

[oracle@localhost admin]$

This show that the remote listener is able to detect the db service on server1.

However, here come the problem
->

SQL> conn starhub/abc123@starhubstag

ERROR:

ORA-12545: Connect failed because target host or object does not exist

SQL> conn starhub/abc123@192.168.7.15:1522/starhub

ERROR:

ORA-12545: Connect failed because target host or object does not exist


What's wrong with my setup ?

Regards,
Noob

detect service but cannot connect to db

A reader, January 26, 2012 - 2:49 pm UTC

hi tom,

I have 2 machine and i am on linux , 10g.

192.168.7.32 (server1)(db)
192.168.7.15 (server2)(remote listener)


I am trying to connect to the remote listener to access db@server1.

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

on server 1, i have

- alter system set remote_listener = listener_intr;
- edited tnsname.ora

listener_intr=

(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.15)(PORT=1522)))


on server 2, i have
- added a new listener

listener_intr =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.15)(PORT = 1522))
)

- added a new tnsname entry as well

starhubstag =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.15)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME=starhub)
(SERVER = DEDICATED)
)
)

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

On Server 1
- alter system register

On Server 2

[oracle@localhost admin]$ lsnrctl
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 27-JAN-2012 03:01:10
Copyright (c) 1991, 2005, Oracle. All rights reserved.

Welcome to LSNRCTL, type "help" for information.
LSNRCTL> status listener_intr

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.7.15)(PORT=1522) ))

STATUS of the LISTENER

Alias listener_intr
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 26-JAN-2012 04:01:07
Uptime 0 days 23 hr. 0 min. 8 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/list ener.ora
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listen er_intr.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.15)(PORT=1522)))
Services Summary...
Service "starhub" has 1 instance(s).
Instance "starhub", status READY, has 1 handler(s) for this service...


I have also managed to tnsping

[oracle@localhost admin]$ tnsping starhubstag
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 27-JAN-2012 03:03:08
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.15)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME=starhub) (SERVER = DEDICATED)))

OK (0 msec)

[oracle@localhost admin]$

This show that the remote listener is able to detect the db service on server1.

However, here come the problem
->

SQL> conn starhub/abc123@starhubstag

ERROR:

ORA-12545: Connect failed because target host or object does not exist

SQL> conn starhub/abc123@192.168.7.15:1522/starhub

ERROR:

ORA-12545: Connect failed because target host or object does not exist


What's wrong with my setup ?

Regards,
Noob

A reader, January 27, 2012 - 1:38 pm UTC

hi tom,

please ignore my thread, question above. it is my pure stupidness that i post this question.

---------

the remote listener needs a local listener to spawn a process to access the local db.

so i do is.

alter system set local_listener = 'xxx' (in tnsname.ora add xxx connect description)

and it works like magic now~

thanks!

listener with multiple connect description

A reader, February 21, 2012 - 1:29 pm UTC

hi tom,

i am on 10g linux.

All this while, my listener is on the same server where by db lies.

So my listener.ora would always be

listener_name=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=myserver.domain.com)(PORT=1521))

wheremy myserver.domain.com is the where the instance lies as well.
---------------------------------------------

But today, i came across these from the oracle doc and i am confused on my understanding of listener.

listener_name=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=hr-server)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)))
--------------------------------------------

i mean normally, be it a remote or local listener, i always configure the listener.ora with the host this listener is listening on.

e.g

- I have a instance & db at hr-server
- I have a local listener at hr-server whereby the listener.ora =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=hr-server)(PORT=1521))

- I have a remote listener at sales-server whereby the listener.ora =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))

and i will start this 2 listener.

- so people connecting to sales-server will use the sales-server listener and people to hr-server will use the hr-server listener

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

I mean, the listener must be located on the host in order to listen to connection to the host isn't it ?

so with the configuration above, what it meant ?

listener_name=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=hr-server)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)))

can this "listener_name" listener be on hr-server or sales-server or another machine ? e.g ops-server

but then again, when client connect, they specify a hostname in the connection descriptor that match to only 1 host. so what does having multiple hostname in the listener means when the listener can only listen on the host it is situated at , isn't it ?

Sorry if i sound confused.

Hope to hear your advice.

Regards,
Noob

February 22, 2012 - 10:22 pm UTC

how about you give the reference into the documentation so that I can read things in context.

listener with multiple connect description

A reader, February 28, 2012 - 1:08 pm UTC

hi tom,

so sorry for the late reply. fallen really sick during the last weekend.

anyway here is my source.

over here

->
http://docs.oracle.com/cd/B19306_01/network.102/b14212/listenercfg.htm

A listener is configured with one or more listening protocol addresses, information about supported services, and parameters that control its runtime behavior. The listener configuration is stored in a configuration file named listener.ora.

->
http://docs.oracle.com/cd/B19306_01/network.102/b14213/listener.htm -> under protocol access section

Example
listener_name=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=hr-server)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)))

-----------
So i am confuse, am i controlling 2 remote listeners ? , is this configuration for the local server ? or what ? why would we need 2 listener connection description in the listener.ora ? so that we can have a central control to shutdown all listener at once ?

I believe we can't startup listener remotely.

Regards,
Noob

February 28, 2012 - 3:28 pm UTC

machines can have multiple host names, machines can have more than one IP address. They could be the same machine (hr-server and sales-server)

I've often set up the localhost AND the 'public' hostname - so you can connect over 127.0.0.1 and whatever the 'real' IP address was.

TNS:listener does not currently know of service...

Ankit, June 04, 2012 - 12:17 am UTC

Hi Tom.
Thanks a lot for all the good stuff and guidance that you impart to so many beings here...
Well I have an instance named ORCL on my local machine. I am using TNS connection to connect to it.
Every day when I try to connect, it gives me error "TNS:listener does not currently know of service...", then I start tnslsnr, stop and start the listener and then it connects successfully. (Before re-starting the listener, ORCL is not displayed under services listed). 2 queries :-
1) I am not able to understand why it fails at first when it has to always start upon listener re-starting ?
2) Can you please tell me a way so that it starts everytime successfully without re-starting the listener.

June 04, 2012 - 9:01 am UTC

this is very confusing:

Every day when I try to connect, it gives me error "TNS:listener does not
currently know of service...", then I start tnslsnr, stop and start the
listener and then it connects successfully.

if you get the message "listener does not currently...." that means the listener is up.

but you say "then I start tnslsnr" - which means it wasn't up, but if it weren't up - you wouldn't get "listener does not...."

?????

are you really shutting down your database at night? everynight? really? why?

Re:TNS:listener does not currently know of service...

Ankit, June 05, 2012 - 1:00 am UTC

Hi Tom,
I made a little mistake, in place of "then I start tnslsnr", its "then I start lsnrctl". I apologize.
Yes, I know listener is up when i try to connect, but still it gives error "TNS:listener does not currently know of service...".
But when I re-start the listener via lsnrctl, the database connects successfully.
Actually the DB is on my local machine, i use it for practice purpose Only and it goes down every time when I shut down my machine.

1) I am not able to understand why it fails at first when it has to always start upon listener
re-starting ?
2) Can you please tell me a way so that it starts everytime successfully without re-starting the
listener.

Thanks a lot.

June 05, 2012 - 8:17 am UTC

I knew what you meant by "tnslnsr", but the order of operations here makes no sense.

1) you get a message indicating the listener is UP
2) then you start the listener????

How can you start the listener if the listener is already running???!?!?!?

I'm just trying to figure out what is really going on here...

Re:TNS:listener does not currently know of service...

Ankit, June 05, 2012 - 11:22 pm UTC

Hi Tom

Here I am mentioning the events in chronological manner...

1) Today morning I turned machine on and tried to connect to instance ORCL.
2) Received error "TNS-12514: TNS:listener does not currently know of service requested in connect descriptor".
3) I went to cmd, started lsnrctl, executed "services" command and got following output. ORCL is not present:

LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
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
LSNRCTL>

At this point, listener.log has following lines:

TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production on 06-JUN-2012 09:24:21

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

System parameter file is C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Log messages written to C:\oracle\product\10.2.0\db_1\network\log\listener.log
Trace information written to C:\oracle\product\10.2.0\db_1\network\trace\listener.trc
Trace level is currently 0

Started with pid=2092
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
06-JUN-2012 09:31:42 * (CONNECT_DATA=(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=IBM_ADMIN))(SERVER=dedicated)(SERVICE_NAME=orcl)) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=49442)) * establish * orcl * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

4) So, just like that, I thought if I re-start the listener, then may be ORCL gets noticed by listener.
So I fired stop command followed by start in lsnrctl.
5) Now, "services" command gave following output, which has ORCL also :

LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
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 "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER <machine: IBM-5PFV8RF3JTK, pid: 4292>
(ADDRESS=(PROTOCOL=tcp)(HOST=IBM-5PFV8RF3JTK)(PORT=49163))
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
LSNRCTL>

6) Now, when I tried connecting to ORCL, it connected successfully.

This is the situation that I face everyday. I hope I have put forward my problem clearly.

Thanks a lot.

June 06, 2012 - 5:36 am UTC

sounds like you might have two listener.ora's possibly - can you search your machine.

and/or your database isn't starting as fast as everything else (the database registers with the listener after it starts). That is, if you boot your machine, the listener might come up, you try to connect - but the database is still starting - so you twiddle around - restart the listener and by then the database is up and registers. Please verify the database is actually started (look at the alert log)

post the output of:
select name || ' = ' || value from v$parameter where isdefault = 'FALSE';

from your database as well.

Re:TNS:listener does not currently know of service...

Ankit, June 06, 2012 - 11:48 pm UTC

Hi Tom

1) I found only 1 listener.ora file, that too in ORACLE_HOME/NETWORK/ADMIN directory.
There is one LISTENER>ORA in ORACLE_HOME/NETWORK/ADMIN/SAMPLE directory (which is of course just a sample).

2) In Alert.log, i found following recurring error :

Thu Jun 07 09:45:26 2012
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
Thu Jun 07 09:45:36 2012
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !

I believe its due 32 bit oracle running on 64 bit windows.
Can this be the reason for the problem ?
I also found from alert.log that DB is started.

3) output of select name || ' = ' || value from v$parameter where isdefault = 'FALSE'; is as follows :

processes = 150
spfile = C:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\SPFILEORCL.ORA
sga_target = 293601280
control_files = C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL, C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL, C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
db_block_size = 8192
compatible = 10.2.0.1.0
db_file_multiblock_read_count = 16
db_recovery_file_dest = C:\oracle\product\10.2.0\db_1/flash_recovery_area
db_recovery_file_dest_size = 2147483648
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile = EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=orclXDB)
smtp_out_server = pop.gmail.com:995
job_queue_processes = 10
audit_file_dest = C:\ORACLE\PRODUCT\10.2.0\DB_1\ADMIN\ORCL\ADUMP
background_dump_dest = C:\ORACLE\PRODUCT\10.2.0\DB_1\ADMIN\ORCL\BDUMP
user_dump_dest = C:\ORACLE\PRODUCT\10.2.0\DB_1\ADMIN\ORCL\UDUMP
core_dump_dest = C:\ORACLE\PRODUCT\10.2.0\DB_1\ADMIN\ORCL\CDUMP
db_name = orcl
open_cursors = 300
pga_aggregate_target = 96468992

Thanks.

June 07, 2012 - 6:45 am UTC

Game over player one. You either need to get a 32bit OS or a 64bit database.

You cannot use 32bit Oracle on 64bit OS's, it just doesn't work and leads to all kinds of strange things.

Re:

Ankit, June 07, 2012 - 12:24 pm UTC

Thanks a lot Tom for assistance.

Thanks.

Load Balancing

Al Ricafort, December 19, 2012 - 3:17 am UTC

Hi Tom,

I am a bit of confused regarding the type of load balancing used in Oracle. I've read that you can do a client connection load balancing, server side connection pool load balancing and run-time connection load balancing. I think I have a good idea about the client side but a little lost on the 'run-time' and 'server side'. So my questions now are:

1. Is 'run-time' a jdbc thing and 'server-side' a listener thing?

2. For the 'server-side' it seems that the load balancing only happens during connection. So once the connection is made all requests will go to a fixed instance. If that is true then for a connection pool the connections may be balanced but the load may not unlike in the 'run-time'. Is this assumption correct?

Thanks

December 19, 2012 - 12:08 pm UTC

1) http://docs.oracle.com/cd/E11882_01/java.112/e16548/rlb.htm#JJDBC28748 it is done in the connection pool in the application server.

2) yes, once the connection is made, it is to an instance and will be bound to that instance.

listener

A reader, December 19, 2012 - 2:07 pm UTC

Tom:

I installed the grid software for 11g to use oracle restart (no ASM) and then installed database software.

When I tried to create a listener on port 1521, it told me it is already used. I found that Grid install automatically creates a listener running under the GRID_HOME .

However, there are no listener.ora or sqlnet.ora files to change the configuration.

I do not like the idea of having no config files.

Do you recommend deleting this listener and creating one using netca using oracle database software under ORACLE_HOME? I am not sure if this will affect anything for oracle restart though.

Please advise.

networking problem

Jean Michel Vial, August 12, 2016 - 3:00 pm UTC

Hi, my posible answer is a realy basic, so.. you check a network, firewall rules, telnet to port 1527, is posible... this port is a blocked in your network enviroment.

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Listener Question

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Status unknown

web browser direct connection

how to configure listener?

wow! Great stuff

ipc protocol

ipc

external procedures?

More Question on Status Unknown

lsnrctl status unknown

Great Help

No listener, but can connect to the database!!

as for july 09, 2003

j.

what privileges does kris have?

has kris got more?

there must be another difference between SYS and kris

Could you please give me a hand on how to achieve the goal the RIGHT way?

listener port

question on SID_LIST in listener.ora

Listner port

nt-service for oracle-http-server doesn't start anymore ...

nt-event-log-entries for failing http-server-startups

issue "solved"

Use one listener in either oracle 8i or 9i in PC for two servers to work

Need more explanation

listener.ora empty?

Question regarding the "virtual" Listener

Tom is right

Thanks a lot.

Authenticating Users

Password Verfification using Oracle

Authentication

Listener for external procedure

updatexml not available in pl/sql

Oracle Developer

Oracle Developer

Need Help

listener not available.

8i Listener for 9i Database

I upgraded

duplicate value in Listener !

not useful for me

Listener

Listener

What's PLSExtProc instance

do we need listener.ora to configure everytime?

listener

how to know the port number from both server and client side

dedicated server

Check the listener.log file for the port numbers used by the server process

Unix permissions.

do dedicated servers share one port(i.e. 1521)?

how multiple processes share one port

dynamic registration

How

Thank You

Problem running Sean's example

"Better" way to change ports (?)

URGENT: Listener Problem

load balance

load balance

help

of prev review

multiple LISTENER for same database

can u explain more

queue

ORA-27140: attach to post/wait facility failed

reader

reader

reader

6 databases on a server?

missing features

reader