Just learn about listener on RAC (grid)

On 11gR2 RAC (Grid Infrastructure), I tests use LISTENER_SCAN* on port 1521 and LISTENER on port 1522. I would like to check what service names are registered in LISTENER. Umm...

[oracle@db01 ~]$ cat /grid/network/admin/listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent

[oracle@db01 ~]$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521

[oracle@db01 ~]$ srvctl config listener
Name: LISTENER
Network: 1, Owner: oracle
Home: /grid
End points: TCP:1522

[oracle@db01 ~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): db01,db02

[oracle@db01 ~]$ srvctl status service -d db
Service service1 is running on instance(s) db1,db2

[oracle@db01 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 18-JUL-2011 17:28:27

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused

So, that is not the problem. I was on db01, then used ...

[oracle@db01 ~]$ lsnrctl status "(ADDRESS=(PROTOCOL=tcp)(HOST=db01-vip)(PORT=1522))"

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 18-JUL-2011 17:28:36

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=db01-vip)(PORT=1522))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 18-JUL-2011 17:11:33
Uptime 0 days 0 hr. 17 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /grid/network/admin/listener.ora
Listener Log File log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db01)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db01-vip)(PORT=1522)))
Services Summary...
Service "service1" has 1 instance(s).
Instance "db1", status READY, has 1 handler(s) for this service...
The command completed successfully

OK... I learned to use "lsnrctl" different... I used. "Security = ON: Local OS Authentication", so i can use " lsnrctl status "(ADDRESS=(PROTOCOL=tcp)(HOST=db01-vip)(PORT=1522))". Or

[oracle@db01 ~]$ lsnrctl status db01-vip:1522

If I am on db01 but ask other nodes... it'll error: TNS-01189: The listener could not authenticate the user

It's just something, I learned.

ORA-12526: TNS:listener: all appropriate instances are in restricted mode

Nothing special... just tested some about Database in Restricted Mode.
Opening the Database in Restricted Mode,that will only allow users with special privileges (RESTRICTED SESSION)

SQL> select * from dba_sys_privs where privilege='RESTRICTED SESSION';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA RESTRICTED SESSION YES
SYS RESTRICTED SESSION NO

On Server: just opened instance in restricted mode.

SQL> startup restrict

On client: TNSNAMES.ORA file
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

DBA role: connected by using user who has DBA role.

$ sqlplus system@orcl

Enter password:
ERROR:
ORA-12526: TNS:listener: all appropriate instances are in restricted mode

Found!!! ORA-12526: TNS:listener: all appropriate instances are in restricted mode
Then solved it by changed TNSNAMES.ORA: using "(UR=A)"

orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))
(CONNECT_DATA =
(UR=A)
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

Tested for connection again.

$ sqlplus system@orcl

Enter password:

SQL>

OK... It's work. In case with NONE-DBA role:

$ sqlplus nondba@orcl

Enter password:
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

What did I learn? When instance is restricted mode.

$ lsnrctl status
.
.
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status RESTRICTED, has 1 handler(s) for this service...

PMON updates the listener with that information and blocks new connection(establish).

The (UR=A) clause for TNS connect strings in TNSNAMES.ORA file. This clause insert into:
(CONNECT_DATA =
(UR=A)
It allows a privileged or administrative user to connect via the listener even when the service handler is blocking connections for non-privileged users.

However, On database LISTENER.ORA file, what can we do? Static service!!! by using SID_DISC=
Example:

$ lsnrctl status
.
.
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status RESTRICTED, has 1 handler(s) for this service...

$ sqlplus system@orcl

Enter password:
ERROR:
ORA-12526: TNS:listener: all appropriate instances are in restricted mode

Then change listener.ora file:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC = (ORACLE_HOME = /oracle/11gR2) (SID_NAME = orcl ))
)

$ lsnrctl reload

$ lsnrctl status
Services Summary...
Service "orcl" has 2 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl", status RESTRICTED, has 1 handler(s) for this service...

What did we see? "status UNKNOWN" and "status RESTRICTED" ... then tested...

$ sqlplus system@orcl

Enter password:

SQL>

It's work... however, test with some user (NON-DBA role)

$ sqlplus nondba@orcl

Enter password:
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

^ ^ Now!!! We can use "DBA" role to do something with database by using LISTENER, when the Database open in Restricted Mode.

Make Sure! RAC VIP configure, right?

when i implemented Oracle RAC and i used Virtual IP for connecting "but network ;) told me... find packets on both Virtual IP and Public IP"

Example:

service1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac01-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac02-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = service1)
)
)

why? I connected by virtual IPs;)

while connection from client; so i tested "tcpdump" on client and rac01/rac02 servers, and i found traffics/packets on Public IP and Virtual IP.

As "tcpdump" told me client connected by Virtual IP... But used Public IP to work.

client -> rac01-vip:1521
.
.
client -> rac01:1521.
.
.

What wrong?
So, checked on oracle rac "lsnrctl services":

$ lsnrctl services
.
.
.
Service "service1" has 2 instance(s).
Instance "db1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=rac01)(PORT=1521))
Instance "db2", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=rac02)(PORT=1521))
"DEDICATED" established:8316 refused:0 state:ready
LOCAL SERVER

A mistake configuration about rac vip???
after I read "Oracle RAC — VIP Configuration Mistakes: by Alex Gorbache, i changed configuration:

-> listener.ora

rac01:
LISTENER_rac01 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac01-vip)(PORT = 1521)(IP = FIRST)))
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac01)(PORT = 1521)(IP = FIRST)))
)
)

rac02:
LISTENER_rac02 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac02-vip)(PORT = 1521)(IP = FIRST)))
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac02)(PORT = 1521)(IP = FIRST)))
)
)

-> tnsnames.ora on both nodes

LISTENERS_DB =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac01-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac02-vip)(PORT = 1521))
)

LISTENERS_rac01 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac01-vip)(PORT = 1521))
)

LISTENERS_rac02 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac02-vip)(PORT = 1521))
)

-> DATABASE

rac01:
SQL> show parameter listener

NAME TYPE VALUE
-----------------------------------------------------------------
local_listener string LISTENERS_rac01
remote_listener string LISTENERS_DB

rac02:
SQL> show parameter listener

NAME TYPE VALUE
-----------------------------------------------------------------
local_listener string LISTENERS_rac02
remote_listener string LISTENERS_DB

(just example)

and then checked:

$ lsnrctl services
.
.
Service "service1" has 2 instance(s).
Instance "db1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=rac01-vip)(PORT=1521))
Instance "db2", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=rac02-vip)(PORT=1521))
"DEDICATED" established:8316 refused:0 state:ready
LOCAL SERVER

after that, I tested connection from client by Virtual IPs again, and "tcpdump" dumped traffics/packets.

client -> rac01-vip:1521
.
.
client -> rac01-vip:1521.
.
.

I've not found traffics/packets on Public IP anymore.

CRS-1028: Dependency analysis failed because of: CRS-0223: Resource...

After I changed hostname on my RAC

and then run $ORA_CRS_HOME/install/rootconfig command.

I reconfigured listener, But can not start by RAC (srvctl command-line).

I tried to stop/start, remove, recreate or use netca to recreate.
But can not start by srvctl command-line.

$ srvctl start listener -n test01

CRS-1028: Dependency analysis failed because of:
CRS-0223: Resource 'ora.test01.LISTENER_TEST01.lsnr' has placement error.


So, I cleaned up OCR and Voting Disks ,then run "$ORA_CRS_HOME/install/rootconfig" and then run netca again to solve it.

A single listener with multiple listening addresses

- Modified listener.ora file.

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


- Added alias name in tnsnames.ora file on the same location listener.ora file.

TESTLISTENER=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost )(PORT = 1529))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost )(PORT = 1521))
)

- altered LOCAL_LISTENER parameter to alias name.

SQL> ALTER SYSTEM SET LOCAL_LISTENER='TESTLISTENER' SCOPE=BOTH;

System altered.

- stopped / started listener.

$ lsnrctl stop

$ lsnrctl start

$ lsnrctl status

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 02-NOV-2008 02:50:58

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1529)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date 02-NOV-2008 02:49:00
Uptime 0 days 0 hr. 1 min. 58 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File ORACLE_HOME/network/admin/listener.ora
Listener Log File ORACLE_HOME/log/diag/tnslsnr/hostname/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=1529)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=1521)))
Services Summary...
Service "db" has 1 instance(s).
Instance "db", status READY, has 1 handler(s) for this service...
Service "db2XDB" has 1 instance(s).
Instance "db", status READY, has 1 handler(s) for this service...
Service "db_XPT" has 1 instance(s).
Instance "db", status READY, has 1 handler(s) for this service...
The command completed successfully

- Tested multiple listening addresses.

SQL> conn scott@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=db)))
Enter password:
Connected.

SQL> conn scott@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1529))(CONNECT_DATA=(SID=db)))
Enter password:
Connected.

.
.
.

ORA-12514, After change port listener

Found Error, After changed port from default(1521) to new port

Example: changed to 1529

$ netstat -ltn | grep 1529
tcp 0 0 0.0.0.0:1529 0.0.0.0:* LISTEN

$ lsnrctl status

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 02-NOV-2008 01:22:55

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1529)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date 02-NOV-2008 01:21:09
Uptime 0 days 0 hr. 1 min. 46 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File ORACLE_HOME/network/admin/listener.ora
Listener Log File ORACLE_HOME/log/diag/tnslsnr/host/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=1529)))
The listener supports no services
The command completed successfully


SQL> connect scott@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1529))(CONNECT_DATA=(SID=db)))
ORA-12514: TNS:listener does not currently know of service requested in connect

*** after you changed the default listener port number, the database instances will not be able to register themselves with this new listener. ***

Solve:

1. modified LOCAL_LISTENER parameter to point to the address of the local listener.

SQL> ALTER SYSTEM SET LOCAL_LISTENER="(address=(protocol=TCP)(host=localhost)(port=1529))" SCOPE=BOTH;
System altered.

$ lsnrctl status

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 02-NOV-2008 02:05:26

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1529)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date 02-NOV-2008 02:05:04
Uptime 0 days 0 hr. 0 min. 22 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File ORACLE_HOME/network/admin/listener.ora
Listener Log File ORACLE_HOME/log/diag/tnslsnr/host/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=1529)))
Services Summary...
Service "db" has 1 instance(s).
Instance "db", status READY, has 1 handler(s) for this service...
Service "db2XDB" has 1 instance(s).
Instance "db", status READY, has 1 handler(s) for this service...
Service "db_XPT" has 1 instance(s).
Instance "db", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> conn scott@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1529))(CONNECT_DATA=(SERVICE_NAME=db)))
Enter password:
Connected

2. Edited tnsnames.ora file (for alias) in the same location as listener.ora file and then modified LOCAL_LISTENER parameter to point to that alias.

TNSNAMES.ORA

TESTLISTENER=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host )(PORT = 1529))
)

SQL> ALTER SYSTEM SET LOCAL_LISTENER='TESTLISTENER' SCOPE=BOTH;

System altered.


$ lsnrctl status

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 02-NOV-2008 02:24:18

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1529)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date 02-NOV-2008 02:05:04
Uptime 0 days 0 hr. 19 min. 14 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File ORACLE_HOME/network/admin/listener.ora
Listener Log File ORACLE_HOME/log/diag/tnslsnr/host/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=1529)))
Services Summary...
Service "db" has 1 instance(s).
Instance "db", status READY, has 1 handler(s) for this service...
Service "db2XDB" has 1 instance(s).
Instance "db", status READY, has 1 handler(s) for this service...
Service "db_XPT" has 1 instance(s).
Instance "db", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> conn scott@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1529))(CONNECT_DATA=(SERVICE_NAME=db)))
Enter password:
Connected

...

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

Oracle Net Services - Version: 9.2.0.5 to 10.2.0.1
This problem can occur on any platform.
Database may be configured as DEDICATED or Shared Server (MTS).

Error in listener Log File.
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 32: Broken pipe

Cause:
There are two possible causes:
If using DEDICATED connections -->
* With 10g, the default PROCESSES value in the database is set to 150. This may be too low for a production system.
If using SHARED SERVER (pre-9i MTS) -->
* The Dispatcher has reached maximum connections and its refusing the newer ones.
* You can verify the the Dispatcher's status by querying the Listener services as follows:
For Ex:
LSNRCTL> services
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521)))
Services Summary...
Service "test" has 1 instance(s).
Instance "test", status READY, has 4 handler(s) for this service...
Handler(s):
"D001" established:5515 refused:7 current:245 max:2026 state:ready
DISPATCHER , pid:
(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=))

Solution:
DEDICATED:
* Increase the PROCESSES parameter to handle the number of processes that may be needed. Monitor for the ORA-12518 errors in the listener log and increase the value again if necessary. See the Oracle Database Administrator's Guide 10.2 for these and database tuning details. PROCESSES is a static parameter so the database has to be bounced.

SHARED SERVER:
* Shutdown the Dispatcher using the following command:
SQL> alter system shutdown immediate 'D001';
Later on add new dispatchers as follows:
SQL> alter system set DISPATCHERS = '(protocol=tcp)(dispatchers=3)';
Note : This syntax depends on the way the Dispatcher value is given in the init.ora or Spfile.ora
While increasing the DISPATCHERS value, also check shared servers ratio.

SQL> select name, (busy / (busy + idle))*100 "Dispatcher % busy Rate" From V$DISPATCHER;
NAME Dispatcher % busy Rate
---- ----------------------
D000 .000021769
D001 .006658056
D002 .025966763
These dispatchers show little busy time. If dispatchers are busy more than 50 percent of the time, consider starting more dispatchers.

[Oracle NetService]WARNING: inbound connection timed out (ORA-3136)

We upgraded Oracle 10.1 to 10.2, and We found WARNING: 'inbound connection timed out (ORA-3136)' in alert.log.

When we found this error, So we opened metalink for finding what it is.

This problem can occur on any platform, When you use Oracle Net services - Version 10.2 up.

Because; The Oracle Net 10G parameter "SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername default to 0 in 10.1.
To address Denial of Service (DOS) issues, the parameter were set to have a default of 60 (seconds) in 10.2

The following may be seen in the alert log: WARNING: inbound connection timed out (ORA-3136)

SQLNET.INBOUND_CONNECT_TIMEOUT is set to a value in seconds and determines how long a client has to provide the necessary authentication information to a database.

INBOUND_CONNECT_TIMEOUT_listenername is set to a value in seconds and determines how long a client has to complete its connect request to the listener after the network connection has been established.

To protect both the listener and the database server, Oracle Corporation recommends setting INBOUND_CONNECT_TIMEOUT_listenername in combination with the SQLNET.INBOUND_CONNECT_TIMEOUT parameter.

Cause:
Whenever default timeouts are assigned to a parameter, there may be cases where this default does not work well with a particular application. However, some type of timeout on the connection establishment is necessary to combat Denial of Service attacks on the database. In this case, SQLNET.INBOUND_CONNECT__TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername were given default values of 60 seconds in Oracle 10.2. It is these timeout values that can cause the errors described in this note.

Also note that it is possilbe the reason the database is slow to authenticate, may be due to an overloaded Oracle database or node.

Solution:
Set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername to 0 (indefinite) or to an approprate value for the application yet still combat DOS attacks (120 for example).

These parameters are set on the SERVER side:
listener.ora: INBOUND_CONNECT_TIMEOUT_listenername
sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT

Further tuning of these parameters may be needed is the problem persists.

[Example] Shell Script for rotate listener log

#!/bin/sh
# use for default listener name(LISTENER), if 10.2 , support every listener name

export ORACLE_HOME=/oracle/product/10.2.0/db

DATE=`/bin/date +'%Y%m%d'`
#Configure OHOME
OHOME=/oracle/product/10.2.0/db
DIR==${OHOME}/network/log
ODIR=${OHOME}/network/log/old


FILE=${OHOME}/network/admin/listener.ora
if [ -f ${FILE} ]
then
NDIR=`${OHOME}/bin/lsnrctl show log_directory | /bin/grep log_directory | /bin/awk '{print $6}'`
NFILE=`${OHOME}/bin/lsnrctl show log_file | /bin/grep log_file | /bin/awk '{print $6}'`
LOGFILE=${NDIR}/${NFILE}


if [ ! -d ${ODIR} ]
then
/bin/mkdir ${ODIR}
fi

if [ -f ${LOGFILE} ]
then

/bin/mv ${LOGFILE} ${ODIR}/${NFILE}.${DATE}


${OHOME}/bin/lsnrctl set log_status off
${OHOME}/bin/lsnrctl set log_status on
/bin/gzip ${ODIR}/${NFILE}.${DATE}

fi

fi

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

PREFER_LEAST_LOADED_NODE

version: 10.1 ...

when you want to connect distribution on RAC

set PREFER_LEAST_LOADED_NODE_listenername to OFF
at listener.ora file


PREFER_LEAST_LOADED_NODE_listenername=OFF

listener.log log hung

if you found listener.log, that hung.
you can stop and start but you don't need down time.

so, you can force listener off and on

#lsnrctl
lsnrctl> set log_status off
lsnrctl> set log_status on