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 

Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more