Listing down handy troubleshooting steps to check listener connection issues
1) Check if Listener is Up and Running
ps –ef | grep lsnr
2) Check if Listener is listening to Service and database service is up
lsnrctl services LISTENER_NAME | grep -i service_name
If database is having service configured check if database service is up
srvctl config service -d db_+name
srvctl status service -d db_name -s service
3) Check if port and host is reachable from client machine
telnet database_host_name 1524
4) Using netstat on database server to see if post is open and listening
netstat –a
netstat -a | wc -l
netstat -a | wc -l
netstat -anp | pg
netstat -anp | more
netstat -a | more
netstat -a | more
netstat -a | grep ESTAB See check established connection
netstat -a| grep LISTEN
netstat -an | grep 6100 | grep LISTE
netstat -an | grep 6100 connections on each port
netstat -an | grep ESTAB to check number of connection coming from each port
netstat -na |grep -i listen
netstat -anp | grep 1521
netstat -P tcp
Other tools used
tnsping
ping
telnet
netstat
traceroute
tracepath
tcpdump
ifconfig, ifup, ifdown
5) Check if process parameter threshold is reached in database .
6) Tracing lsnrctl commands
truss -o /tmp//xx -aefx lsnrctl status LISTENER_CODP01
truss -o /tmp//xx -aef -v all -w all lsnrctl status LISTENER_CODP0
To enable listener tracing use below
LSNRCTL> set cur LISTENER_TEST
LSNRCTL> status
LSNRCTL> set trc_level ADMIN
LSNRCTL> status
7) Check details of process
pwdx 24637 --> to check location from where process started from
pfiles 24637 --> files opened by each process
8) Check listener log for any errors reported
9) Check allowed host on database server.
check what is inside /etc/hosts.allow /etc/hosts.deny
10) Check database wait events and if database is hung . Check database alert log .
11) Unix team to check network speed
for en in `netstat -i | grep en | awk '{print $1}' | sort -u | cut -c3`
do
adapter=`echo ent${en}`
entstat -d ${adapter} | grep "Media Speed"
done
12) If listener log file size is huge try , rotating logfile .
[oracle@server1 ~]$ lsnrctl.
LSNRCTL> show current_listener.
LSNRCTL> set current_listener
LSNRCTL> set log_status off.
[oracle@server1 trace]$ mv listener.log listener_backup.log.
LSNRCTL> set log_status on.
13) Do a continuous ping to check for packet Drops
14) TNS-12505 TNS-12514 : local_listener parameter was removed after patching
alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))' scope=both; à no ip or localhost
alter system register;
15) To check session tns information from database
set lines 120
col NETWORK_SERVICE_BANNER format a75
select NETWORK_SERVICE_BANNER, count(*)
from v$session_connect_info
where NETWORK_SERVICE_BANNER like '%TCP/IP%' or NETWORK_SERVICE_BANNER like '%Bequeath%'
group by NETWORK_SERVICE_BANNER;
select sid,program,
case when program not like 'ora___@% (P%)' then
(select max(case
when NETWORK_SERVICE_BANNER like '%TCP/IP%'
then 'TCP'
when NETWORK_SERVICE_BANNER like '%Bequeath%'
then 'BEQUEATH'
when NETWORK_SERVICE_BANNER like '%IPC%'
then 'IPC'
when NETWORK_SERVICE_BANNER like '%SDP%'
then 'SDP'
when NETWORK_SERVICE_BANNER like '%NAMED P%'
then 'Named pipe'
when NETWORK_SERVICE_BANNER is null
then 'TCPS' end)
from
v$session_connect_info i
where
i.sid=s.sid) end protocol
from
v$session s;
16) Use tcpdump for analysis
Use tcpdump to view Packet Flows (Doc ID 2259297.1)
tcpdump -i <ethX> -C 20 -s 10000 -S -w /tmplog.log -ttt 'port 777' &
eth stands for network interface that carries traffic
17) Use unix time command to determine how much it is taking .
18) Checking incoming connections from which port
select sid, username,status,osuser, machine, port, program from v$session where type='USER';
netstat -tn | grep 1522
netstat -anp | grep 1522
19) Check if any locks in database
