Recovery method for Oracle inactive online log file loss

Online log file inacitve state indicates that the log contains data modifications that have been synchronized to the data file and that the instance is not needed when it is recovered, so its loss does not cause any data loss, but can cause the database to not open, and the solution is to remove the missing inactive, Re-add a new online log.

1) Simulate disaster

First look at the log status:

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

group# sequence# STATUS

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

1 1 INACTIVE

2 2 INACTIVE

3 3 Current

Group#1 's status is inactive, we find the disk file that it corresponds to:

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

group# Member

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

1 I:\INTEL_DATA\O04DMS0\REDO01. O04dms0

To close the database:

sql> shutdown immediate;

Remove the Redo01.o04dms0 file 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 1 of thread 1

Ora-00312:online Log 1 Thread 1: ' I:\INTEL_DATA\O04DMS0\REDO01. O04dms0 '

From the error message above, you can see that the online log file loss caused the database to not open and check the status of the Lost log:

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

group# sequence# STATUS

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

1 1 INACTIVE

Luckily, the lost online log status is inactive, so we don't need to do anything, just delete it, but it's a good idea to add a set of online logs before deleting it:

sql> ALTER DATABASE Add logfile Group 4 (' I:\INTEL_DATA\O04DMS0\REDO04. O04dms0 ') size 100M;

Database altered.