Home » RDBMS Server » Server Administration » Shutdown immediate hangs with message "WARNING: aiowait timed out x times" (Oracle 11.2.0.1 on Solaris 8)
Shutdown immediate hangs with message "WARNING: aiowait timed out x times" [message #632252] Wed, 28 January 2015 06:36 Go to next message
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 #632253 is a reply to message #632252] Wed, 28 January 2015 06:47 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
SQL> SHUTDOWN ABORT;
SQL> STARTUP;

Post here what Oracle says.
Re: Shutdown immediate hangs with message "WARNING: aiowait timed out x times" [message #632254 is a reply to message #632253] Wed, 28 January 2015 06:49 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Yes but only i was scared of what might happen....is that OK to do, or can it pose any risk?

actually I got this message:


[b]Instance shutdown cancelled[/b]
So I started another 'shutdown immediate' command! So I will wait for this one to fail again and then try the abort option.


Thanks a lot for your review!!!
Re: Shutdown immediate hangs with message "WARNING: aiowait timed out x times" [message #632255 is a reply to message #632254] Wed, 28 January 2015 06:52 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
It will cause Oracle to stop what it is doing and kill off all its processes. On startup, Oracle will realise that it was shut down inconsistently and will perform instance recovery.
Re: Shutdown immediate hangs with message "WARNING: aiowait timed out x times" [message #632256 is a reply to message #632255] Wed, 28 January 2015 07:05 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Table will have been locked to roll it back. You may have kill the smon PID.

Good luck, Oracle will probably be smart enough to dig you out of this hole!
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 Go to previous messageGo to next message
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 #632258 is a reply to message #632257] Wed, 28 January 2015 07:31 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
What is the value of parallel_max_servers in your PFILE/SPFILE file?

[Edit: typo]

[Updated on: Wed, 28 January 2015 07:33]

Report message to a moderator

Re: Shutdown immediate hangs with message "WARNING: aiowait timed out x times" [message #632259 is a reply to message #632258] Wed, 28 January 2015 07:35 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
This is the message in the alert log:

Adjusting the default value of parameter parallel_max_servers
from 640 to 185 due to the value of parameter processes (200)
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 Go to previous messageGo to next message
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 #632261 is a reply to message #632260] Wed, 28 January 2015 08:06 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Instance recovery is underway. Sit tight for now Smile
Re: Shutdown immediate hangs with message "WARNING: aiowait timed out x times" [message #632265 is a reply to message #632261] Wed, 28 January 2015 08:57 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
FYI...no indication of further progress since last hour...

$ls -ltr alert_db4.log
-rw-r--r--   1 oracle   dba      16112085 Jan 28 08:00 alert_db4.log  <--08:00

#now it is:
$date
Wednesday, January 28, 2015  9:04:14 AM EST <--09:04 <--


I hope some indication will crop up in another hour!!!

OrauserN
Re: Shutdown immediate hangs with message "WARNING: aiowait timed out x times" [message #632266 is a reply to message #632265] Wed, 28 January 2015 09:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
submit Service Request to MOS
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 as
select * 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 #632272 is a reply to message #632270] Wed, 28 January 2015 09:33 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
It gets worse: I just noticed that you are using Solaris 8. No 11g release was ever certified on 8. So there is no way Oracle Support is going to help you. Are you even using Oracle legally? Or is this a bootleg installation?
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 Go to previous message
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 1872 times)
Previous Topic: Startup force and shutdown abort
Next Topic: differences between local and dictionary mangaged tablespace
Goto Forum:
  


Current Time: Thu Mar 28 03:58:30 CDT 2024