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.

Introduction

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:

• Go to the cmd prompt.
• Start sqlplus.
• Connect as sysdba.
• Once connected, type:

  Shutdown Immediate;

• After shutdown completes, type:

  Startup Mount;

• Continue with this topic and follow the steps in each section.

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.

• Go to the cmd prompt.
• Start sqlplus.
• At the sqlplus prompt, connect to the database (i.e., connect sys/password as sysdba).
• Run the following query:

  Select * from v$backup where status=’ACTIVE’;

• If there are no rows returned here, then go to the section Check for Data Files that Need Recovery.
• If there are any rows returned, note each file number (the first column data returned) to use later. If there are rows returned in the query from v$backup, then this means that the database crashed while there was a database backup running. To get the database back online, we need to take the files out of backup mode, and bring them online.
• Multiple commands can be used to take data files out of backup mode.

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;

• After all datafiles have been taken out of backup mode, skip to the section titled Bring All Data Files Online.

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.

• Go to the cmd prompt.
• Start sqlplus.
• Connect sys/password as sysdba.
• Run the following query:

  Select * from v$recover_file;

  This query shows all data files that are not up to date with the rest of the database (status of ‘recover’ in v$datafile as well)and require recovery.

• Note all file# numbers that require recovery. The following commands will assist in the recovery of each of the data files. The examples assume datafiles 12 and 15 need recovery.
• Connect sys/password as sysdba in sqlplus and type the following SQL command for each datafile:

  Recover datafile 12;

  Recover datafile 15;

• Each of these SQL statements should return the message “Media Recovery Complete.”
• If this prompts for more log files, then the database will need further recovery work done. Contact OPERA Support to get further assistance to perform the database recovery.
• If all files return “Media Recovery Complete,” then continue on to the Bring All Data Files Online section.

Bring All Data Files Online

• After Media Recovery is complete, some data files may be offline.
• Connect sys/password as sysdba in sqlplus and run the following query to find which datafiles are offline:

  Select file# from v$datafile where status = ’OFFLINE’;

• Note all file# numbers that are returned. Then, while in sqlplus connected to the database as sys/password as sysdba. Type the following SQL (examples use file# 12 and 15):

  Alter database datafile 12 online;

  Alter database datafile 15 online;

• After all files are back online, continue to the next step Open the Database.

Open the Database

• To open the database after media recovery is completed, while in sqlplus connect to the database as sys/password as sysdba and type:

  Alter database open;

• If this fails, try to shutdown and restart the database by typing:

  Shutdown immediate;

  Startup;

• The database should open at this point. If the database does not open, go to the next step, Recover Database.

Recover Database

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

• Connect sys/password as sysdba in sqlplus.
• Type:

  Shutdown immediate;

  Startup mount;

  Recover database;

• Once these steps are complete, go back to the section Bring All Data Files Online and follow the steps there and in the Open the Database section.
• If these steps fail, contact your Support Channel to get assistance in the database recovery, and to bring the database back up.

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.