Recovery methods after the loss of active or current online log files in Oracle

The online log file status is active or current indicates that the data modifications that the log contains have not been fully synchronized to the data file, and that the redo record in which the instance will need to be read repeats itself, so if it is corrupted, data loss is unavoidable.

1) Simulate disaster

First look at the log status:

Sql> select Group#,sequence#,status from V$log;

group# sequence# STATUS

------ ---- ---------- ----------------

2 5 Current

3 3 INACTIVE

4 4 INACTIVE

The group#2 state is current, and we find that the disk file that it corresponds to is:

Sql> Select Group#,member from V$logfile where group#=2;

group# Member

------ ---- ---------------------------------------------------------

2 J:\INTEL_LOG\O04DMS0\REDO02. O04dms0

If the database shuts down normally, a data file synchronization is done before the shutdown, so in order to simulate the loss of the current file, we shut down the database abnormally:

sql> shutdown abort;

Then the file corresponding to the group#2 Redo02.o04dms0 is deleted at the operating system level.

2 Locating the problem according to the error information

The following error occurred while starting the database

Sql> Startup

ORACLE instance started.

Total System Global area 281018368 bytes

Fixed Size 1296292 bytes

Variable Size 251660380 bytes

Database buffers 25165824 bytes

Redo buffers 2895872 bytes

Database mounted.

Ora-00313:open failed for members of log Group 2 of thread 1

Ora-00312:online Log 2 thread 1: ' J:\INTEL_LOG\O04DMS0\REDO02. O04dms0 '

Ora-27041:unable to open File

Osd-04002:unable to open File

O/s-error: (OS 2) The system cannot find the file specified.

Because a set of log files is missing, the database can only mount and cannot open to see what state the missing log files are:

Sql> Select Group#,sequence#,status from V$log where group#=2;

group# sequence# STATUS

---------- ---------- ----------------

2 5CURRENT