The disk that is holding the most up-to-date redo log files experienced a fatal failure, and the database wouldn't come up any more. It complains about missing redo logs. So, you copy the redo log files from a recent backup; but still the database won't come up with error messages like ORA-00314: log 1 of thread 1, expected sequence# doesn't match
SQL> startupHow to bring up the database with no redo logs?
ORACLE instance started.
Total System Global Area 4294967296 bytes
Fixed Size 1306568 bytes
Variable Size 2534150200 bytes
Database Buffers 1728053248 bytes
Redo Buffers 31457280 bytes
ORA-00314: log 1 of thread 1, expected sequence# doesn't match
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata_4/RedoLogFiles/log1.dbf'
In production environments, usually archive logging will be turned on. If that's the case, consult the Oracle system administration documentation to restore the lost transactions.
In all other cases it is very unlikely to recover the lost transactions. So the easiest way to bring up the database is to rebuild the control file. The steps to rebuild the control file with new redo log files are as follows:
- Start the database up.
SQL> startup <- let it complain about online redo log
- Run the following command to dump the control file into a trace file.
SQL> alter database backup controlfile to trace;
Go to udump location and locate the file which contains the necessary SQL text to build the control file. The easiest way is to grep for text like 'CREATE CONTROLFILE REUSE DATABASE'. Once you locate the trace file, change the extension from trc to sql and change NORESETLOGS to RESETLOGS. That is, a SQL like:
CREATE CONTROLFILE REUSE DATABASE "XYZ" NORESETLOGS NOARCHIVELOG
should be changed to
CREATE CONTROLFILE REUSE DATABASE "XYZ" RESETLOGS NOARCHIVELOG
Remove the lines starting from the line "RECOVER DATABASE USING BACKUP CONTROLFILE" to the end.
- Shut down the database
- Add the parameter
_allow_resetlogs_corruption=TRUE to the init.ora file.
- Run the script that you just modified -- it creates a new control file.
- Shut down the database one more time
- Start the database instance by running
startup command as sysdba. It may fail with the error
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
- Open the database with by running
alter database open resetlogs;. Redo log files will be automatically created in this step.
- Shutdown the database, remove the parameter
_allow_resetlogs_corruption=TRUE from the init.ora file and restart the database instance. If the instance comes up fine, shut it down and take a backup. Otherwise, check the alert messages carefully and act accordingly to fix the things up.