Database hangs on startup

[SpiritOfLennon]

Hi,
We recently lost power unexpectedly to our server.
Now when we try and start it we receive the following.

> SQL> startup
> ORACLE instance started.
> Total System Global Area 110033940 bytes
> Fixed Size 76820 bytes
> Variable Size 76230656 bytes
> Database Buffers 33554432 bytes
> Redo Buffers 172032 bytes
> Database mounted.
>
> It is missing the "Database opened" message

We are running 8.1.7 on HP-UX 11.

We run cold backups with no archiving.

Any suggestions as to anything we can try that might get this database backup short of a full recovery would be appreciated.
Regards SOL

SOL
I'm only guessing but my guess work generally works for me.

 

[KenCunningham]

SOL, Have you tries issuing an alter database open after the mounted message? Anything in the alert log?

I want to be good, is that not enough?

 

[SpiritOfLennon]

Hi,
It hangs so it isn't possible to issue an open statement.

I can't see anything in the alert log, but then I'm not an expert at interpreting them.
Here are the last few lines, is that helps at all.
Regards SOL

Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Mon Jul 2 18:48:39 2007
ALTER DATABASE OPEN
Tue Jul 3 07:23:37 2007
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
LICENSE_MAX_USERS = 0
Starting up ORACLE RDBMS Version: 8.1.7.0.0.
System parameters with non-default values:
processes = 150
shared_pool_size = 73400320
java_pool_size = 32768
control_files = /oracle/oradata/live01/control01.ctl, /ord1/oracle/oradata/live01/control02.ctl, /ord2/oracle/oradata/live01/control03.ctl
db_block_buffers = 4096
db_block_size = 8192
compatible = 8.1.0
log_buffer = 163840
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
max_enabled_roles = 30
remote_login_passwordfile= EXCLUSIVE
instance_name = live
service_names = live
audit_trail = DB
sort_area_size = 65536
sort_area_retained_size = 65536
db_name = live
open_cursors = 12000
os_authent_prefix =
background_dump_dest = /oracle/8.1.7/admin/live01/bdump
user_dump_dest = /oracle/8.1.7/admin/live01/udump
core_dump_dest = /oracle/8.1.7/admin/live01/cdump
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
Tue Jul 3 07:23:41 2007
ALTER DATABASE MOUNT
Tue Jul 3 07:23:46 2007
Successful mount of redo thread 1, with mount id 3250112834.
Tue Jul 3 07:23:46 2007
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Tue Jul 3 07:23:46 2007
ALTER DATABASE OPEN




SOL
I'm only guessing but my guess work generally works for me.

 

[KenCunningham]

As you say it's a strange one. So you're not even getting a prompt from which you could issue a shutdown abort (possibly followed by a startup and shutdown immediate to try to reset to normality)?

I want to be good, is that not enough?

 

[SpiritOfLennon]

No, it just hangs, presumabley trying to open and getting nowhere!


SOL
I'm only guessing but my guess work generally works for me.

 

[engineer2100]

no master of administration... but this is what we call the "General (under)Pressure Methodology" - something you do when everything fails

Shutdown normal
and
startup

again.

 

[SpiritOfLennon]

Hi engineer2100.
This has been tried, bringing it up and down to various states. The problem is it never gets to the open state.
Regards Sol

SOL
I'm only guessing but my guess work generally works for me.

 

[KenCunningham]

...But he's not getting a prompt.

Perhaps try killing the Oracle processes that the system thinks are running and try the startup, shutdown immediate and startup again. If that fails I think I'd be looking to my backups.

I want to be good, is that not enough?

 

[engineer2100]

is there a possibility that somethings corrupted and the db is unable to get its hands on the resource... read is on asktom site few months back..

 

[SantaMufasa]

SOL,

How long do you wait following the "MOUNT" before you "give up" on the "OPEN"?

The reason I ask is because when MOUNT occurs with a delayed OPEN, it is often because Oracle System Monitor (SMON) process is recovering the database from a previous abnormal shutdown of the instance. If you have very large on-line redo logs and if there were several long-running transactions that were "in-flight" at the time that the instance was last running, then I have heard of cases where the OPEN could take dozens of minutes while SMON does its thing.

Since you are running on Unix, you could do a combination of "ps -ef | grep smon" and "top" to during this apparent "hanging" period to determine if SMON is in the process of recovering from a previous abnormal termination.

Let us know if any of my scenario, above, could account for your Oracle appearing to "hang".

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SpiritOfLennon]

Thanks, SantaMafusa.
That rings a bell, I think I've seen a database do that as well a long time ago.
The database has been left trying to open for about 20 minutes, however given the work involved if we have to do a tape restore then it's probably worth leaving it a while longer.


SOL
I'm only guessing but my guess work generally works for me.

 

[SantaMufasa]

Yes, considering the amount of work involved in restoring to a (possibly waaay) earlier point in time from your tape backups, I'd let it run for 90 minutes if I had to before giving up.

Again, what do your Unix performance diagnostics ("sar" and "top") say about your Oracle instance?

Hopefully, your initials, SOL, will continue to mean SpiritOfLennon, and not S---OuttaLuck on this particular issue. <hopeful smile>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SpiritOfLennon]

Top is showing no activity for any of the Oracle processes, so I'm not particularly hopeful. It's looking like a bad day.


SOL
I'm only guessing but my guess work generally works for me.

 

[SantaMufasa]

SOL,

I'm sorry to hear that nothing Oracle appears to be doing any work for you.

At this point, and for future reference, I would create a Priority 1 Service Request (SR) with Oracle Tech Support via MetaLink (first), then (second, 10 minutes later) a phone call to 800-223-1711 to speak with the technician assigned to your case. (Since this sounds like a downed production database, you qualify for a "Priority 1" request.)

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[engineer2100]

i too am eagerly waiting to understand whats causing this!

 

[KenCunningham]

Sorry nothing appears to be of any help here, but at least you do have a backup, so often not the case. That said, you might consider turning on archivelogs too, just to provide the additional facility to restore to a reasonable approximation of the system as it was when it done gone hung.

Good luck.

I want to be good, is that not enough?

 

[kb5994]

We have a similar situation on Windows 2003 servers, oracle 10.2.0.3.

Did you find a solution or workaround?

 

[Beilstwh]

I would connect to the database in a second session, issue a shutdown abort, then do a startup normal and see if this solves your problem. Also before you do that, look in the alert log to see if any database errors are being reported.

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Spirit,

Can you give us an update of how this July incident played out to completion?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[kb5994]

Late Friday we discovered a ton of items in teh security log with event id of 576. It looks like our problem is related to auditing. We were able to open the database if we moved the datafiles to a different disk. So it wasn't a database problem after all.

Reference Microsoft Kb Article 822774