Oracle listner getting stopped when number of proceeses increases - unknown reason

[Binukuttan MM]

Hi all

Pls give your inputs :

My landscape is

2 x rx 4640 itanium in MC Service Guard with Active - Active Cluster Environment

Database is Oracle 10g

Problem Description :-

Oracle listner is getting stopped when number of processes is increased -but Normally it happens when users are logged ouy - Most Probably at mid night tome.Then we need to restart the listner in the next business day.

We used "top" to find out the number of processed.At business times The range is from 400- 500 only but at night time it reaches about 1500.

Top outputs of Node 1
System: anderpb Thu Jan 12 12:35:52 2006
Load averages: 0.08, 0.08, 0.07
281 processes: 250 sleeping, 29 running, 2 zombies
Cpu states:
CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0 0.08 4.6% 0.0% 2.2% 93.2% 0.0% 0.0% 0.0% 0.0%
1 0.08 4.0% 0.0% 3.6% 92.4% 0.0% 0.0% 0.0% 0.0%
--- ---- ----- ----- ----- ----- ----- ----- ----- -----
avg 0.08 4.2% 0.0% 2.8% 93.0% 0.0% 0.0% 0.0% 0.0%

Memory: 2267632K (1466884K) real, 3909132K (2780448K) virtual, 2678216K free Pa
ge# 1/24

CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
0 ? 1101 oracle 154 20 2495M 5788K sleep 0:32 1.67 1.67 oracleem
1 ? 51 root 152 20 3240K 2880K run 8:50 1.56 1.56 vxfsd
1 ? 10454 oracle 154 20 2496M 6552K sleep 0:00 2.08 0.61 oracleem
1 ? 2024 root 152 20 118M 43768K run 4:55 0.57 0.56 crsd.bin
1 ? 9026 oracle 152 20 815M 154M run 10:23 0.50 0.50 java
1 ? 8470 oracle 152 20 375M 77848K run 7:28 0.38 0.38 java
1 ? 8274 oracle 152 20 68776K 10236K run 13:57 0.34 0.34 ocssd.bi
0 ? 1629 root 154 10 7704K 756K sleep 0:51 0.31 0.31 diagmond
1 ? 8444 oracle 154 20 44436K 2660K sleep 57:19 0.29 0.29 tnslsnr
1 ? 2018 oracle 152 20 77512K 16244K run 2:02 0.26 0.26 evmd.bin
0 ? 8073 root -27 20 35868K 27952K run 7:58 0.22 0.22 cmcld


Top output of Node 2

System: anderpb Thu Jan 12 12:36:58 2006
Load averages: 0.08, 0.08, 0.07
278 processes: 247 sleeping, 29 running, 2 zombies
Cpu states:
CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0 0.09 2.2% 0.0% 0.6% 97.2% 0.0% 0.0% 0.0% 0.0%
1 0.08 0.8% 0.0% 1.4% 97.8% 0.0% 0.0% 0.0% 0.0%
--- ---- ----- ----- ----- ----- ----- ----- ----- -----
avg 0.08 1.4% 0.0% 1.0% 97.6% 0.0% 0.0% 0.0% 0.0%

Memory: 2301496K (1393068K) real, 3953060K (2696860K) virtual, 2670996K free
ge# 1/24

CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
0 ? 1101 oracle 154 20 2495M 5788K sleep 0:32 1.45 1.45 oracleemc
1 ? 2024 root 152 20 118M 43768K run 4:56 0.58 0.58 crsd.bin
1 ? 9026 oracle 152 20 815M 154M run 10:23 0.51 0.51 java
1 ? 51 root 152 20 3240K 2880K run 8:51 0.50 0.50 vxfsd
0 ? 29015 oracle 154 20 2496M 6800K sleep 0:04 0.41 0.41 oracleemc
1 ? 8470 oracle 152 20 375M 77848K run 7:28 0.35 0.35 java
1 ? 8444 oracle 154 20 44436K 2660K sleep 57:19 0.33 0.33 tnslsnr
1 ? 8274 oracle 152 20 68776K 10236K run 13:57 0.32 0.32 ocssd.bin
1 ? 2018 oracle 152 20 77512K 16244K run 2:02 0.30 0.30 evmd.bin
0 ? 8073 root -27 20 35868K 27952K run 7:58 0.23 0.23 cmcld
1 ? 9368 oracle 154 20 2497M 8512K sleep 3:51 0.23 0.23 oracleemc



Pls share your ideas - it wil be highly appreciated

Thanks in advance
Binu

[Joseph Loo]

hi,

any error or warning from the Oracle alert.log file with regards to this problem?

regards.

[Binukuttan MM]

Thanks for you immediate response.Hope alert.log is a binary file.

Last few records from alert.log file is following.
SourceComputer
SourceProvider
UserDefinedPropset
VeritasAlertRecord
GenericAlertPropset
AlertFlag
AlertGUID
AlertMsg
FormatSpec
Catalog
Message
Name
$File System Capacity Exhausted on %1
Insertions
/dev/vg00/lvol6
AlertSeverity
AlertTopic
*event.alert.vrts.fs.capmon.space.exhausted
ClassificationName
Catalog
Message
Name
File System Capacity Monitoring
Description
Catalog
Message
Name
FriendlyAlertName
Catalog
Message
Name
%File System Capacity Monitoring Alert
RecommendedAction
FormatSpec
Catalog
Message
Name
!Increase the File System Capacity
Insertions
TimeStampSeconds
SourceComputer
SourceProvider
UserDefinedPropset
VeritasAlertRecord
GenericAlertPropset
AlertFlag
AlertGUID
AlertMsg
FormatSpec
Catalog
Message
Name
$File System Capacity Exhausted on %1
Insertions
/dev/vg00/lvol6
AlertSeverity
AlertTopic
*event.alert.vrts.fs.capmon.space.exhausted
ClassificationName
Catalog
Message
Name
File System Capacity Monitoring
Description
Catalog
Message
Name
FriendlyAlertName
Catalog
Message
Name
%File System Capacity Monitoring Alert
RecommendedAction
FormatSpec
Catalog
Message
Name
!Increase the File System Capacity
Insertions
TimeStampSeconds
SourceComputer
SourceProvider
UserDefinedPropset
VeritasAlertRecord
GenericAlertPropset
AlertFlag
AlertGUID
AlertMsg
FormatSpec
Catalog
Message
Name
$File System Capacity Exhausted on %1
Insertions
/dev/vg00/lvol6
AlertSeverity
AlertTopic
*event.alert.vrts.fs.capmon.space.exhausted
ClassificationName
Catalog
Message
Name
File System Capacity Monitoring
Description
Catalog
Message
Name
FriendlyAlertName
Catalog
Message
Name
%File System Capacity Monitoring Alert
RecommendedAction
FormatSpec
Catalog
Message
Name
!Increase the File System Capacity
Insertions
TimeStampSeconds
SourceComputer
SourceProvider
UserDefinedPropset
VeritasAlertRecord
GenericAlertPropset
AlertFlag
AlertGUID
AlertMsg
FormatSpec
Catalog
Message
Name
$File System Capacity Exhausted on %1
Insertions
/dev/vg00/lvol6
AlertSeverity
AlertTopic
*event.alert.vrts.fs.capmon.space.exhausted
ClassificationName
Catalog
Message
Name
File System Capacity Monitoring
Description
Catalog
Message
Name
FriendlyAlertName
Catalog
Message
Name
%File System Capacity Monitoring Alert
RecommendedAction
FormatSpec
Catalog
Message
Name
!Increase the File System Capacity
Insertions
TimeStampSeconds
SourceComputer
SourceProvider
UserDefinedPropset
VeritasAlertRecord
GenericAlertPropset
AlertFlag
AlertGUID
AlertMsg
FormatSpec
Catalog
Message
Name
$File System Capacity Exhausted on %1
Insertions
/dev/vg00/lvol6
AlertSeverity
AlertTopic
*event.alert.vrts.fs.capmon.space.exhausted
ClassificationName
Catalog
Message
Name
File System Capacity Monitoring
Description
Catalog
Message
Name
FriendlyAlertName
Catalog
Message
Name
%File System Capacity Monitoring Alert
RecommendedAction
FormatSpec
Catalog
Message
Name
!Increase the File System Capacity
Insertions
TimeStampSeconds
SourceComputer
SourceProvider
UserDefinedPropset

I am not able to make out any thing from this output

regds

[Devender Khatana]

Hi,

Alongwith alertSID.log it will be worth checking the current setting and utilization if nproc parameter as it is related to processes. This can be done by running "sar -v 1 11111111" and if it is at a time you can not see the output then let the output be directed to a file.

HTH,
Devender

[Binukuttan MM]

Hi all

Thanks for immediat response.Whether anybody can comment on the alert.log file of oracle. I hav pasted last few lines only.

sar -v is initiated and kept on monitoring

Thanks in advance

[Joseph Loo]

hi,

noticed something from the alert.log:

"File System Capacity Exhausted on "
"!Increase the File System Capacity"

please reply the amt of free disk space:

# bdf

regards.

[Binukuttan MM]

Node 1
# bdf
Filesystem kbytes used avail %used Mounted on
/dev/vg00/lvol3 10485760 228600 10177056 2% /
/dev/vg00/lvol1 2097152 132656 1949200 6% /stand
/dev/vg00/lvol10 5242880 1369120 3848320 26% /var
/dev/vg00/lvol9 5242880 2625688 2596776 50% /usr
/dev/vg00/lvol8 5242880 599784 4606856 12% /tmp
/dev/vg00/lvol7 3145728 17335 2932875 1% /rman
/dev/vg00/lvol6 10485760 7726681 2586647 75% /oracle
/dev/vg00/lvol5 5242880 2423384 2797496 46% /opt
/dev/vg00/lvol4 1048576 17000 1023576 2% /home
/dev/vgarch/lvol1 31449088 25331966 5734962 82% /archive


node 2
â ¥# bdf
Filesystem kbytes used avail %used Mounted on
/dev/vg00/lvol3 10485760 246016 10159800 2% /
/dev/vg00/lvol1 2097152 199376 1882992 10% /stand
/dev/vg00/lvol10 5242880 2762864 2466352 53% /var
/dev/vg00/lvol9 5242880 2626592 2595920 50% /usr
/dev/vg00/lvol8 5242880 155360 5048104 3% /tmp
/dev/vg00/lvol7 3145728 1297772 1732462 43% /rman
/dev/vg00/lvol6 10485760 7674548 2636498 74% /oracle
/dev/vg00/lvol5 5242880 2423432 2797448 46% /opt
/dev/vg00/lvol4 1048576 17312 1023256 2% /home
/dev/erpdump/lvol1 139657216 120005925 18423100 87% /erpdump
/dev/vgarch/lvol1 31449088 7894083 22083041 26% /archive

regds
#

[Binukuttan MM]

Hope Filesystem is not Full - as per bdf ouputs

[Binukuttan MM]

nproc value is set to 4096 in both the custer nodes

[Joseph Loo]

hi,

difficult to trace the problem without a proper alert_.log, but how abt listener.log? any clues from there?

regards.

[RK_7]

Dear binu,

What is the oracle setup there? Have they implemented any new features of 10g?. Whether they are killing any processes at mid-night? There may have a chance of getting killed some oracle child processes and as a result listener goes down.Better you contact with oracle support also.

Regards
Reni

[Jeff Schussele]

Hi Binu,

You should also check the kernel parameter

maxuprc

This limits the number of processes tied to any one username - like "oracle".

HTH,
Jeff

[Yogeeraj_1]

hi Binu,

can you post the output of:

show parameter dump

in sqlplus


this will help locate the location of the alert.log file.

the alert.log can give us some clues on what is happening...


kind regards
yogeeraj

[Wim Rombauts]

We are talking about a listener issue, right ? Not a database issue. I mean, the database is still running, only the listener process has stopped for some unknown reason.

If so, stop looking at alert.log as no listener info will be there.
Look at $ORACLE_HOME/network/log/listener.log (when logging is enabled) and at $ORACLE_HOME/network/trace (when tracing is enabled).
Tot enable logging and tracing, edit file $ORACLE_HOME/network/admin/listener.ora and set LISTENER_LOGGING = ON and/or set TRACE_LEVEL_LISTENER = ADMIN.

It may be a good idea to activae these settings in the evening, because they will slow down the listener and generate a ot of trace/logging information.

From this point on, you can only hope that the last lines in the logging or tracing file give a hint about why the listener is stopping.