DataFlex Computer Services Making Computers Work for People for Over 20 Years

Remembering Doug Engelbart,
the inventor of the mouse.

Oracle database offline - Now what?

Posted by Admin  •  Filed under database support, Oracle

Friday morning, I got a call from an anxious customer. Last night's backup failed and just after 9:00 am, their Oracle database went offline. HELP!

First, let's take a look at some common causes of trouble before we try any recovery procedures. Disk space? Not a problem. Power failure? No. Time to dig a little deeper. The Windows Event Log showed at 9:10 am the Oracle database "terminated." For 10 more minutes the database "terminated" every 30 seconds. At 9:20 am a Backup Exec backup that was in progress was "canceled." After that the database was running, but not online.

Oracle keeps an event log of its own.
ORACLE-DRIVE:\oracle\admin\YOUR-DATABASE-NAME\bdump\alert_YOUR-DATABASE-NAME.log

The alert log showed lots of entries like this:
Fri May 11 09:09:31 2012
KCF: write/open error block=0x9e0df online=1
file=3 D:\ORACLE\ORADATA\MSIN\INDX01.DBF
error=27072 txt: 'OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 33) The process cannot access the file because another process has locked a portion of the file.'
Automatic datafile offline due to write error on
file 3: D:\ORACLE\ORADATA\MSIN\INDX01.DBF
Fri May 11 09:10:52 2012
Errors in file d:\oracle\admin\msin\bdump\msin_lgwr_2528.trc:
ORA-00345: redo log write error block 117300 count 2
ORA-00312: online log 3 thread 1: 'D:\ORACLE\ORADATA\MSIN\REDO03A.LOG'
ORA-27072: skgfdisp: I/O error
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 33) The process cannot access the file because another process has locked a portion of the file.

Fri May 11 09:12:55 2012
Errors in file d:\oracle\admin\msin\udump\msin_ora_2032.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\MSIN\UNDOTBS01.DBF'
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\MSIN\UNDOTBS01.DBF'

Here are the commands I used to bring the database back online.

From a command prompt:

>sqlplus /nolog

SQL*Plus: Release 9.2.0.6.0 - Production on Fri May 11 13:13:30 2012

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys/sys@YOUR-DATABASE-NAME as sysdba
Connected.

SQL> select * from v$recover_file;

This gives you a list of the database files and shows whether they are online or offline.

FILE# ONLINE ONLINE_ERROR CHANGE# TIME
-----------------------------------------------------

2 OFFLINE OFFLINE 112051434 10-MAY-12

3 OFFLINE OFFLINE 112051434 10-MAY-12

4 OFFLINE OFFLINE 112051434 10-MAY-12

Files that are offline need to be "recovered".

SQL> recover datafile 2;
Media recovery complete.
SQL> recover datafile 3;
Media recovery complete.
SQL> recover datafile 4;
Media recovery complete.

The recovered files then need to be brought back ONLINE.
SQL> alter database datafile 2,3,4 ONLINE;

Database altered.

SQL> Exit

Then I stopped and restarted the database service to make sure it started without any errors.

The database was back online and everybody went back to work.

Now what about the backup that had been running. Could it have caused the trouble with Oracle? It turns out that since the previous night's backup failed, the customer had tried to do a full backup using Backup Execs "wizard", accepting Backup Exec's defaults. When it got to the Oracle files, it placed locks on the files while it backed them up. Backup Exec was the "other process" referenced in the Oracle alert log. One of the Advanced options that can be changed when setting up a backup job in Backup Exec is how to handle "open" files. Choosing "Backup without locks" would have prevented the trouble in Oracle.

(Calling me for help when the backup failed, could have prevented the Oracle trouble too. The tape drive needed to be cleaned with a cleaning tape and I don't recommend doing backups during the day when people are using the database.)

A database server is not your home PC. The whole company shuts down when the database is offline. Call me for help at the first sign of trouble.

Tagged: Oracle, Backup Exec, Database server, Support

Comments (3)  •  May 11, 2012  •  Edit

3 Responses

Leave a Reply

1. Name (required)
2. Email Address (required)
3. Website
4. Your Message