A formal response for Oracle DB server hanging......
subject: Possible Causes and solution of BA live Database server hang on dated 26/04/2012.
Dear sir,
Assalamu alaikum.
As you requested that, what were the causes of that hanging the DB sever
on date Thu Apr 26 14:10:15 2012.
Here i have quoted the related log file of the Database as well as timing
of occurrence.I have also described here the possible causes, solutions
and have attached technical details (reliable) .
Contents of Oracle Database alert log
=======================================
Thu Apr 26 13:02:17 2012
Thread 1 advanced to log sequence 122529 (LGWR switch)
Current log# 2 seq# 122529 mem# 0: /d01/oracle/oradata/orcl/redo02.log
Thu Apr 26 13:02:58 2012
Thread 1 advanced to log sequence 122530 (LGWR switch)
Current log# 3 seq# 122530 mem# 0: /d01/oracle/oradata/orcl/redo03.log
Thu Apr 26 13:19:03 2012
Thread 1 advanced to log sequence 122531 (LGWR switch)
Current log# 4 seq# 122531 mem# 0: /d01/oracle/oradata/orcl/redo04.log
Thu Apr 26 13:42:48 2012
Thread 1 advanced to log sequence 122532 (LGWR switch)
Current log# 5 seq# 122532 mem# 0:
/d01/oracle/oradata/orcl/redo05.log----------------------------Database
is running well.
Thu Apr 26 14:10:08 2012
WARNING: aiowait timed out 1 times------------------------DB server has
hanged on this time.
Thu Apr 26 14:10:15 2012
WARNING: aiowait timed out 1 times
Thu Apr 26 14:10:35 2012
WARNING: aiowait timed out 1 times
Thu Apr 26 14:10:08 2012
WARNING: aiowait timed out 1 times
Thu Apr 26 14:10:11 2012
WARNING: aiowait timed out 1 times
Thu Apr 26 14:10:11 2012
WARNING: aiowait timed out 1 times
Thu Apr 26 14:43:48 2012
WARNING: aiowait timed out 1 times
Thu Apr 26 14:43:49 2012
WARNING: aiowait timed out 1 times
Thu Apr 26 14:53:48 2012
WARNING: aiowait timed out 2 times
Thu Apr 26 14:53:48 2012
WARNING: aiowait timed out 2 times
Thu Apr 26 14:53:48 2012
WARNING: aiowait timed out 2 times
Thu Apr 26 15:03:48 2012
WARNING: aiowait timed out 3 times
Thu Apr 26 15:03:48 2012
WARNING: aiowait timed out 3 times
............................
.................
etc.
-------------------------------------------------------------------
In a Summary(timing):-
DB server has hanged on this time.----------- Apr 26 14:10:15 2012
Server force shutdown time------------------- Apr 26 15:04:29
Host has been powered off-------------------- Apr 26 15:22:28
Starting ORACLE instance (normal)------------ Apr 26 15:23:50 2012
Completed: DATABASE OPEN -------------------- Apr 26 15:24:33 2012
=============================================================
Cause and solution of (WARNING: aiowait timed out 1 times)
=============================================================
After a details analysis we have found that...
Oracle signals a (WARNING: aiowait timed out x times) in the alert log
file, if after waiting a reasonable amount of time the Operating System is
not completing async I/O requests made by Oracle.
If this call times out it indicates a severe problem with asynchronous I/Os
at the system/os level. For example, it can mean that an I/O was lost
(Oraclehad issued it and was waiting for it to be completed but the O/S
has no record
of it).
This particular situation is an abnormal condition usually caused by the
Operating System I/O subsystem or in some cases the related volume manager.
As a result of this condition the database may hang or crash(depends on
oracle version).
A reboot may be the only way to clear the problem for this
moment(temprorary).
Note also that a recovery may be necesary on subsequent startup of the
database.(Database may be crashed also)
so possible causes may be:-
============================
1) severe aynchronous I/O problems at the O/S level.
2) Network request timeout/latency.
3) poor comunication with Storage.
4) High load on Memory/shortness of memory to oracle SGA,PGA .
possible solution will be:-
===================
1) Need to investigate the aynchronous I/O problems at the O/S level by
the O/S vendor(may be need to be apply O/S patches to fix known
Asynchronous I/O problems.)
2) Need to investigate the networking (request timeout/latency problem) etc.
3) checking Asynchronous I/O is correctly configured at OS level as well
as with storage.
4) Allocate more memory to the Database SGA, PGA (Buffer Cache and Sort
Areas to reduce I/O from/to datafiles and temporary tablespaces).
for details please see on metalink the Attached technical documents.........
1) Warning aiowait timed out x times- Id 222989.1
2) Warning Aiowait Timed Out 1 Times - Id 743425.1
3) WARNING aiowait timed out- ID 760968.1
Regards
Muhammad Abdul Halim
Database Administrator
subject: Possible Causes and solution of BA live Database server hang on dated 26/04/2012.
Dear sir,
Assalamu alaikum.
As you requested that, what were the causes of that hanging the DB sever
on date Thu Apr 26 14:10:15 2012.
Here i have quoted the related log file of the Database as well as timing
of occurrence.I have also described here the possible causes, solutions
and have attached technical details (reliable) .
Contents of Oracle Database alert log
=======================================
Thu Apr 26 13:02:17 2012
Thread 1 advanced to log sequence 122529 (LGWR switch)
Current log# 2 seq# 122529 mem# 0: /d01/oracle/oradata/orcl/redo02.log
Thu Apr 26 13:02:58 2012
Thread 1 advanced to log sequence 122530 (LGWR switch)
Current log# 3 seq# 122530 mem# 0: /d01/oracle/oradata/orcl/redo03.log
Thu Apr 26 13:19:03 2012
Thread 1 advanced to log sequence 122531 (LGWR switch)
Current log# 4 seq# 122531 mem# 0: /d01/oracle/oradata/orcl/redo04.log
Thu Apr 26 13:42:48 2012
Thread 1 advanced to log sequence 122532 (LGWR switch)
Current log# 5 seq# 122532 mem# 0:
/d01/oracle/oradata/orcl/redo05.log----------------------------Database
is running well.
Thu Apr 26 14:10:08 2012
WARNING: aiowait timed out 1 times------------------------DB server has
hanged on this time.
Thu Apr 26 14:10:15 2012
WARNING: aiowait timed out 1 times
Thu Apr 26 14:10:35 2012
WARNING: aiowait timed out 1 times
Thu Apr 26 14:10:08 2012
WARNING: aiowait timed out 1 times
Thu Apr 26 14:10:11 2012
WARNING: aiowait timed out 1 times
Thu Apr 26 14:10:11 2012
WARNING: aiowait timed out 1 times
Thu Apr 26 14:43:48 2012
WARNING: aiowait timed out 1 times
Thu Apr 26 14:43:49 2012
WARNING: aiowait timed out 1 times
Thu Apr 26 14:53:48 2012
WARNING: aiowait timed out 2 times
Thu Apr 26 14:53:48 2012
WARNING: aiowait timed out 2 times
Thu Apr 26 14:53:48 2012
WARNING: aiowait timed out 2 times
Thu Apr 26 15:03:48 2012
WARNING: aiowait timed out 3 times
Thu Apr 26 15:03:48 2012
WARNING: aiowait timed out 3 times
............................
.................
etc.
-------------------------------------------------------------------
In a Summary(timing):-
DB server has hanged on this time.----------- Apr 26 14:10:15 2012
Server force shutdown time------------------- Apr 26 15:04:29
Host has been powered off-------------------- Apr 26 15:22:28
Starting ORACLE instance (normal)------------ Apr 26 15:23:50 2012
Completed: DATABASE OPEN -------------------- Apr 26 15:24:33 2012
=============================================================
Cause and solution of (WARNING: aiowait timed out 1 times)
=============================================================
After a details analysis we have found that...
Oracle signals a (WARNING: aiowait timed out x times) in the alert log
file, if after waiting a reasonable amount of time the Operating System is
not completing async I/O requests made by Oracle.
If this call times out it indicates a severe problem with asynchronous I/Os
at the system/os level. For example, it can mean that an I/O was lost
(Oraclehad issued it and was waiting for it to be completed but the O/S
has no record
of it).
This particular situation is an abnormal condition usually caused by the
Operating System I/O subsystem or in some cases the related volume manager.
As a result of this condition the database may hang or crash(depends on
oracle version).
A reboot may be the only way to clear the problem for this
moment(temprorary).
Note also that a recovery may be necesary on subsequent startup of the
database.(Database may be crashed also)
so possible causes may be:-
============================
1) severe aynchronous I/O problems at the O/S level.
2) Network request timeout/latency.
3) poor comunication with Storage.
4) High load on Memory/shortness of memory to oracle SGA,PGA .
possible solution will be:-
===================
1) Need to investigate the aynchronous I/O problems at the O/S level by
the O/S vendor(may be need to be apply O/S patches to fix known
Asynchronous I/O problems.)
2) Need to investigate the networking (request timeout/latency problem) etc.
3) checking Asynchronous I/O is correctly configured at OS level as well
as with storage.
4) Allocate more memory to the Database SGA, PGA (Buffer Cache and Sort
Areas to reduce I/O from/to datafiles and temporary tablespaces).
for details please see on metalink the Attached technical documents.........
1) Warning aiowait timed out x times- Id 222989.1
2) Warning Aiowait Timed Out 1 Times - Id 743425.1
3) WARNING aiowait timed out- ID 760968.1
Regards
Muhammad Abdul Halim
Database Administrator
No comments:
Post a Comment