How to Get Data Files Back Online

This topic is intended to assist OPERA Support and Installers in recovering Database files in the event of a database crash. This is intended for OPERA Support and Installers who are familiar with basic Oracle Database programs and terms, including sqlplus and archive logs. It pertains to errors ORA-00376, ORA-1110, as well as other Oracle Recovery errors.

Note: This topic has been updated for use with Oracle 10g databases.


Oracle has a highly reliable recovery mechanism built into the database, and transaction loss due to database crash can be prevented if the correct steps are followed. This text provides information to troubleshoot why a database isn't starting up, or why ORA-00376 errors are occurring in the application.

Generally, if a database will not startup after a power failure or backup failure, the database can be recovered without going to tape backup.

Note: This document does not cover recovery from a tape backup. If recovery from tape is required, please refer to Backup/Recovery documentation, or contact your Support Channel.

Shutdown the Database, then Start Up Mount

This is accomplished by:

Check for Data Files Stuck in Recover Mode

These steps will assist in recovering data files after a crash while the database is getting backed up. The following commands are down with the database mounted but not open as noted above.

While in sqlplus, connect to the database as sys/password as sysdba. Using the file numbers from the query above, take each file out of backup mode (this example assumes that datafiles 12 and 15 listed in v$backup):

Alter database datafile 12 end backup;

Alter database datafile 15 end backup;

Alter database end backup;

Check for Data Files That Need Recovery

This type of recovery will be required for systems that have a sudden loss of power or database crash while not running a tape backup. This is only required if the database does not open automatically after a Startup command.

Bring All Data Files Online

Open the Database

Recover Database

If the alter database open command failed in the previous step, then try to recover the database with the following steps:

Preventing Crashes

The system needs to be reviewed to determine the cause of the database crash. Many times, it is due to insufficient power to the database server, causing it to crash during a brownout or blackout. To guard against this, a sufficient UPS is required.

If a cause is not clear, such as a power failure or a database shutdown during the backup process, then review the alert logs, Windows Event Viewer, or other OS System log files.