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.
This is accomplished by:
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.
Select * from v$backup where status=’ACTIVE’;
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;
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.
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.
Recover datafile 12;
Recover datafile 15;
Select file# from v$datafile where status = ’OFFLINE’;
Alter database datafile 12 online;
Alter database datafile 15 online;
Alter database open;
alter database open command failed in the previous step, then try to recover the database with the following steps:
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.