Oracle Database -- Troubleshooting Listener connectivity and network issue

 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 

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

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;

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