| Database Lock Up without any traces [message #56458] |
Tue, 01 April 2003 10:23  |
Thomas G
Messages: 58
Registered: April 2003
|
Member |
|
|
Hi,
I have this Oracle 8.1.7.3.0 database on AIX 4.3. The last few days it just stopped responding sometime in the afternoon. No connect with sqlplus possible.
I could connect with svrmgrl, but even a "select name from v$database" resulted in an lock up. Only "shutdown abort" worked.
Nothin in the alert_log untill the shutdown :
Thread 1 advanced to log sequence 229674
Current log# 3 seq# 229674 mem# 0: /pro1/oradata/PFK2/redo03.log
Thread 1 advanced to log sequence 229675
Current log# 1 seq# 229675 mem# 0: /pro1/oradata/PFK2/redo01.log
Thread 1 advanced to log sequence 229676
Current log# 2 seq# 229676 mem# 0: /pro1/oradata/PFK2/redo02.log
Mon Mar 24 16:15:56 2003
Thread 1 advanced to log sequence 229677
Current log# 3 seq# 229677 mem# 0: /pro1/oradata/PFK2/redo03.log
Thread 1 advanced to log sequence 229678
Current log# 1 seq# 229678 mem# 0: /pro1/oradata/PFK2/redo01.log
Mon Mar 24 16:16:12 2003
Thread 1 advanced to log sequence 229679
Current log# 2 seq# 229679 mem# 0: /pro1/oradata/PFK2/redo02.log
Mon Mar 24 16:38:45 2003
Shutting down instance (abort)
License high water mark = 192
Instance terminated by USER, pid = 113032
Mon Mar 24 16:40:32 2003
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
I have checked for any processes started around the crash time, but nothing suspicious there.
The vmstat I did during the crash looked somewhat funny, though. Especially the CPU User / System / Idle /Wait of 25 0 75 0. (4 CPU's in the machine). Don't know if that might be a cause or the result of the lock up.
vmstat
kthr memory page faults cpu
----- ----------- ------------------------ ------------ -----------
r b avm fre re pi po fr sr cy in sy cs us sy id wa
0 0 238309 4959 0 0 0 0 0 0 119 301 48 1 1 98 1
1 2 238309 4959 0 0 0 0 0 0 458 930 159 25 0 74 0
1 2 238309 4959 0 0 0 0 0 0 459 325 102 25 0 74 0
1 2 238309 4959 0 0 0 0 0 0 458 285 127 25 0 75 0
1 2 238309 4959 0 0 0 0 0 0 464 379 142 25 0 75 0
1 2 238309 4959 0 0 0 0 0 0 548 449 130 25 0 75 0
1 2 238309 4959 0 0 0 0 0 0 457 368 126 25 0 75 0
1 2 238309 4959 0 0 0 0 0 0 470 338 143 25 0 75 0
1 2 238634 4616 0 0 0 0 0 0 475 1025 153 25 2 73 0
1 2 238634 4616 0 0 0 0 0 0 461 352 131 25 0 75 0
Has anyone experienced anything like that, or has any clues where else to look ?
|
|
|
|
| Re: Database Lock Up without any traces [message #56460 is a reply to message #56458] |
Tue, 01 April 2003 12:31  |
Paul
Messages: 164
Registered: April 1999
|
Senior Member |
|
|
Hello,
I'm having the same problem on two of our databases for months and can't find any way to resolve this. BTW, the problem went away on one of the databases when I took the initiative to compute statistics for all the major tables with large volume of data. It happens atleast once or twice a month. I have posted similar messages before, and no response, or atleast nothing that really helped. I can't see anything on the trace either. At this time, I can't use any tools to connect, including sqlplus. Not even from the server itself. If I use svrmgrl, I can use it only to shutdown the database with an abort. All SQL statements from svrmgrl hangs. My e-mail is paulantony@sbcglobal.net. If you by any chance get a clue or resolved this problem, please let me know. I'd be very thankful. I'm running Oracle 7.3.4 on Solaris ver. 5.8. I also have a 8.1.7 server, but no such problems there yet.
Thanks
Paul
|
|
|
|
| Re: Database Lock Up without any traces [message #56467 is a reply to message #56458] |
Wed, 02 April 2003 07:19 |
Michel Bartov
Messages: 35
Registered: February 2003
|
Member |
|
|
Usually this problem is caused when a long query (multiple tables join) requiring sorting by a user is getting all the resources.
Another cause is when the there are too many sessions opened or too many processes running.
My suggestion:
- Check the maximum number of sessions allowed by Oracle.
- Check the maximum number of processes allowed by Unix. (Each Oracle session will spawn a Unix process).
- Check that there is no bad query running.
- Increase the number of DML_LOCKS and the size of the SGA
I have developed a GUI tool to monitor the user’s activity an in particular to catch all bad queries. The link is http://www.barsoft.net/ Please email me at michelbartov@yahoo.com if you need more help.
Good Luck
|
|
|
|
| Thanks [message #56471 is a reply to message #56458] |
Wed, 02 April 2003 10:34 |
Thomas G
Messages: 58
Registered: April 2003
|
Member |
|
|
I'll try that.
The bad query part might be a little hard to find out, but I now have a loggin running which user are logged on, to find that out after the db has crashed.
We have seen in the last two crashes that indeed one oracle client process keeps running with about 100% of one of the 4 CPU's. When we find out what that user has started, we might be able to reproduce it.
I will keep you all posted about the progress. (or lack thereof ;-) )
|
|
|
|
| The Solution [message #56589 is a reply to message #56458] |
Thu, 10 April 2003 03:24 |
Thomas G
Messages: 58
Registered: April 2003
|
Member |
|
|
Ok, after increasing both the dml_locks and the shared_pool_size in init.ora the Problem went away.
Thanks, Michael.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
