| Shutdown immediate hangs with message "WARNING: aiowait timed out x times" [message #632252] |
Wed, 28 January 2015 06:36  |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
Hi,
I have a database that has some important data in it. In this DB , by mistake I ran an import of a huge 69 million rows table. It ran out of UNDO tablespace. I killed import. But then I saw that the table was shown as 'locked' in the v$locked_object. Thereafter I checked the os 'spid' of this session and killed that spid on Solaris. That freed up the object. But then I found that my instance had terminated. When I tried to open the instance, it gave ORA-600 and it did not open. So I tried 'shutdown immediate'. On this command it is hanging for more than one hour with following messages in alert log:
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process MMNL
Stopping background process MMON
License high water mark = 33
Wed Jan 28 05:52:35 2015
Active call for process 5424 user 'oracle' program 'oracle@db4 (P004)'
Active call for process 5426 user 'oracle' program 'oracle@db4 (P005)'
Active call for process 5455 user 'oracle' program 'oracle@db4 (P019)'
Active call for process 5459 user 'oracle' program 'oracle@db4 (P021)'
Active call for process 5467 user 'oracle' program 'oracle@db4 (P025)'
Active call for process 5477 user 'oracle' program 'oracle@db4 (P030)'
SHUTDOWN: waiting for active calls to complete.
Wed Jan 28 05:56:08 2015
WARNING: aiowait timed out 1 times
Wed Jan 28 06:06:08 2015
WARNING: aiowait timed out 2 times
Wed Jan 28 06:16:08 2015
WARNING: aiowait timed out 3 times
Wed Jan 28 06:26:08 2015
WARNING: aiowait timed out 4 times
Wed Jan 28 06:36:08 2015
WARNING: aiowait timed out 5 times
Can someone please suggest on how to fix/troubleshoot the issue? I don't have any backup unluckily!!
Thanks,
OrauserN
[Updated on: Wed, 28 January 2015 06:37] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Shutdown immediate hangs with message "WARNING: aiowait timed out x times" [message #632257 is a reply to message #632256] |
Wed, 28 January 2015 07:16  |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
Here is the issue: The last time when I gave startup command , it gave this message:
"Completed: ALTER DATABASE MOUNT
Wed Jan 28 05:10:52 2015
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
parallel recovery started with 31 processes
Started redo scan
Wed Jan 28 05:11:05 2015
Completed redo scan
read 88313 KB redo, 16155 data blocks need recovery"
"
But Oracle was not able to do this instance recovery!!!
as can be seen below:
Beginning crash recovery of 1 threads<---------------<--started crash recovery
parallel recovery started with 31 processes
Started redo scan
Wed Jan 28 05:11:05 2015
Completed redo scan
read 88313 KB redo, 16155 data blocks need recovery
Wed Jan 28 05:46:07 2015
Errors in file /apps/home/oracle/diag/rdbms/tst11gdb/tst11gdb/trace/tst11gdb_p030_5477.trc:
Wed Jan 28 05:46:09 2015
Errors in file /apps/home/oracle/diag/rdbms/tst11gdb/tst11gdb/trace/tst11gdb_ora_5414.trc (incident=37054):
ORA-00600: internal error code, arguments: [15709], [29], [1], [], [], [], [], [], [], [], [], []
ORA-12805: parallel query server died unexpectedly
Incident details in: /apps/home/oracle/diag/rdbms/tst11gdb/tst11gdb/incident/incdir_37054/tst11gdb_ora_5414_i37054.trc
Aborting crash recovery due to error 600
Errors in file /apps/home/oracle/diag/rdbms/tst11gdb/tst11gdb/trace/tst11gdb_ora_5414.trc:
ORA-00600: internal error code, arguments: [15709], [29], [1], [], [], [], [], [], [], [], [], []
ORA-12805: parallel query server died unexpectedly
Errors in file /apps/home/oracle/diag/rdbms/tst11gdb/tst11gdb/trace/tst11gdb_ora_5414.trc:
ORA-00600: internal error code, arguments: [15709], [29], [1], [], [], [], [], [], [], [], [], []
ORA-12805: parallel query server died unexpectedly<---------------<--issues
ORA-600 signalled during: ALTER DATABASE OPEN...<---------------<--issues
Wed Jan 28 05:46:15 2015
Trace dumping is performing id=[cdmp_20150128054615]
Wed Jan 28 05:46:17 2015
Sweep [inc][37054]: completed
Sweep [inc2][37054]: completed
Wed Jan 28 05:47:32 2015
Shutting down instance (immediate)<---------------<--could not shut down either!
Shutting down instance: further logons disabled
Stopping background process MMNL
Stopping background process MMON
License high water mark = 33
Wed Jan 28 05:52:35 2015
Active call for process 5424 user 'oracle' program 'oracle@css-db4 (P004)'
Active call for process 5426 user 'oracle' program 'oracle@css-db4 (P005)'
Active call for process 5455 user 'oracle' program 'oracle@css-db4 (P019)'
Active call for process 5459 user 'oracle' program 'oracle@css-db4 (P021)'
Active call for process 5467 user 'oracle' program 'oracle@css-db4 (P025)'
Active call for process 5477 user 'oracle' program 'oracle@css-db4 (P030)'
SHUTDOWN: waiting for active calls to complete.
Please suggest! And thanks a lot for the help!!
[Updated on: Wed, 28 January 2015 07:17] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Shutdown immediate hangs with message "WARNING: aiowait timed out x times" [message #632260 is a reply to message #632259] |
Wed, 28 January 2015 08:03 |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
To update: I gave shutdown abort and the instance did shut down. Now I am trying with:
startup and it is showing same message in alert log and is hanging (fingers crossed!!)
Aborting crash recovery due to slave death, attempting serial crash recovery
Beginning crash recovery of 1 threads
Started redo scan
Wed Jan 28 07:59:37 2015
Completed redo scan
read 88313 KB redo, 16155 data blocks need recovery
Started redo application at
Thread 1: logseq 52326, block 56194
Recovery of Online Redo Log: Thread 1 Group 2 Seq 52326 Reading mem 0
Mem# 0: /apps/home/oracle/oradata/tst11gdb/redo02.log
Wed Jan 28 08:00:05 2015
Recovery of Online Redo Log: Thread 1 Group 1 Seq 52327 Reading mem 0
Mem# 0: /apps/home/oracle/oradata/tst11gdb/redo01.log
Recovery of Online Redo Log: Thread 1 Group 3 Seq 52328 Reading mem 0
Mem# 0: /apps/home/oracle/oradata/tst11gdb/redo03.log
Completed redo application of 23.05MB <--hanging here for last 10 minutes<---several files created at this time 8:00 on trace directory!!
|
|
|
|
|
|
|
|
|
|
| Re: Shutdown immediate hangs with message "WARNING: aiowait timed out x times" [message #632270 is a reply to message #632265] |
Wed, 28 January 2015 09:27 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
If your database is stuck in mount mode, you can still find out what is going on if you connect as sysdba, and run queries such asselect * from v$recovery_progress;
select * from v$session_wait where wait_time=0 and wait_class <> 'Idle';
Usually, yes, raise a TAR - but you are using 11.2.0.1, which is way out of support. The only 11g release that is still being patched is 11.2.0.4. So you may not get much assistance.
|
|
|
|
|
|
| Re: Shutdown immediate hangs with message "WARNING: aiowait timed out x times" [message #632275 is a reply to message #632272] |
Wed, 28 January 2015 09:57 |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
Hi John,
Sorry it is Solaris 10 and it is all licensed!
Thank you for the two views! Here is the output (I have attached as text file which is more readable:
It shows that it is waiting on the event "checkpoint completed " and TIME_REMAINING_MICRO is 3343904. I don't know what it means!
SQL> select * from v$recovery_progress;
START_TIM TYPE ITEM UNITS SOFAR TOTAL TIMESTAMP COMMENTS
--------- ---------------------------------------------------------------- -------------------------------- -------------------------------- ---------- ---------- --------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
28-JAN-15 Crash Recovery Redo Blocks Blocks 102400 102400
28-JAN-15 Crash Recovery Redo Blocks Blocks 102400 102400
28-JAN-15 Crash Recovery Log Files Files 1 1
SQL> select * from v$session_wait where wait_time=0 and wait_class <> 'Idle';
SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE WAIT_TIME_MICRO TIME_REMAINING_MICRO TIME_SINCE_LAST_WAIT_MICRO
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ------------- ----------- ---------------------------------------------------------------- ---------- --------------- ------------------- --------------- -------------------- --------------------------
243 1955 checkpoint completed 0 00 0 00 0 00 3290255840 2 Configuration 0 2 WAITING 1656096 3343904 0
SQL>
SQL>
SQL> spo off
-
Attachment: 1.txt
(Size: 7.98KB, Downloaded 2401 times)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
