About the INVALID status of Oracle log files

In Oracle databases, the three major files (DataFile, ControlFile, and OnlineRedoLog) play an extremely important role. RedoLog and Oracle

In Oracle databases, the three major files (Data File, Control File, and Online Redo Log) play an extremely important role. Redo Log and Oracle

In Oracle databases, the three major files (Data File, Control File, and Online Redo Log) play an extremely important role. The Redo Log and Oracle Redo Log mechanisms guarantee normal and safe operation of Oracle.

Oracle Online Redo Log adopts the Logfile Group and Group Member methods. The member content of each Log File Group must be the same as that of the image. Each Redo Log Entry generated must be written to each member. In principle, each log member is stored in a different storage location. In this way, other members can be used for restoration in case of media corruption.

Oracle Database supports statement operations on log group and log member management. You can easily add or delete a log group and add or delete member files to or from the group. However, in general, we still want to add all the members of the log group at one time. This can avoid many problems, one of which is the INVALID status of the log file.

1. experiment environment Introduction

In this article, select Oracle 11g for the experiment test.


SQL> select * from v $ version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
PL/SQL Release 11.2.0.1.0-Production
CORE 11.2.0.1.0 Production


The current system has three log groups, each of which has two members. The third Group is the Current Redo Log Group.


SQL> select group #, sequence #, members, status, first_change #, next_change # from v $ log;
GROUP # SEQUENCE # members status FIRST_CHANGE # NEXT_CHANGE #
-----------------------------------------------------------------------
1 73 2 INACTIVE 1560017 1580625
2 74 2 INACTIVE 1580625 1600939
3 75 2 CURRENT 1600939 281474976710

SQL> select group #, status, type, member from v $ logfile;
GROUP # STATUS TYPE MEMBER
--------------------------------------------------------------------------------------------------------
3 ONLINE/u01/oradata/WILSON/onlinelog/o1_mf_3_7xt456o0 _. log
3 ONLINE/u01/flash_recovery_area/WILSON/onlinelog/o1_mf_3_7xt45bvp _. log
2 ONLINE/u01/oradata/WILSON/onlinelog/o1_mf_2_7xt44w3k _. log
2 ONLINE/u01/flash_recovery_area/WILSON/onlinelog/o1_mf_2_7xt1_rv _. log
1 ONLINE/u01/oradata/WILSON/onlinelog/o1_mf_1_7xt44nq1 _. log
1 ONLINE/u01/flash_recovery_area/WILSON/onlinelog/ow.mf_20177xt44qt6 _. log
6 rows selected


In v $ logfile, there is a status column, which is empty by default.

2. Add non-Current log group members

If we want to add a member to the log group, Oracle provides corresponding statements. The current log group is group 3. We will add a non-current log group first.


SQL> alter database add logfile member '/u01/flash_recovery_area/WILSON/onlinelog/redolog01a. log' to group 1;
Database altered

SQL> select group #, status, type, member from v $ logfile;

GROUP # STATUS TYPE MEMBER
--------------------------------------------------------------------------------------------------------
3 ONLINE/u01/oradata/WILSON/onlinelog/o1_mf_3_7xt456o0 _. log
3 ONLINE/u01/flash_recovery_area/WILSON/onlinelog/o1_mf_3_7xt45bvp _. log
2 ONLINE/u01/oradata/WILSON/onlinelog/o1_mf_2_7xt44w3k _. log
2 ONLINE/u01/flash_recovery_area/WILSON/onlinelog/o1_mf_2_7xt1_rv _. log
1 ONLINE/u01/oradata/WILSON/onlinelog/o1_mf_1_7xt44nq1 _. log
1 ONLINE/u01/flash_recovery_area/WILSON/onlinelog/ow.mf_20177xt44qt6 _. log
1 invalid online/u01/flash_recovery_area/WILSON/onlinelog/redolog01a. log
7 rows selected

SQL> select group #, sequence #, members, status, first_change #, next_change # from v $ log;
GROUP # SEQUENCE # members status FIRST_CHANGE # NEXT_CHANGE #
-----------------------------------------------------------------------
1 73 3 INACTIVE 1560017 1580625
2 74 2 INACTIVE 1580625 1600939
3 75 2 CURRENT 1600939 281474976710


Note: We did add a file to group 1. However, the file status is INVALID. At the OS level, let's observe:


[Oracle @ bspdev onlinelog] $ pwd
/U01/flash_recovery_area/WILSON/onlinelog
[Oracle @ bspdev onlinelog] $ ls-l
Total 205040
-Rw-r ----- 1 oracle oinstall 52429312 Sep 10 o1_mf_1_7xt44qt6 _. log
-Rw-r ----- 1 oracle oinstall 52429312 Sep 10 o1_mf_2_7xt1_rv _. log
-Rw-r ----- 1 oracle oinstall 52429312 Sep 10 o1_mf_3_7xt45bvp _. log
-Rw-r ----- 1 oracle oinstall 52429312 Sep 10 redolog01a. log
[Oracle @ bspdev onlinelog] $


At the OS level, Oracle does create the redolog01a. log file, and the size is the same as that of other group members. So what does this INVALID state mean? If we switch the log group to a file, is there any problem?


SQL> alter system switch logfile;
System altered

SQL> select group #, sequence #, members, status, first_change #, next_change # from v $ log;
GROUP # SEQUENCE # members status FIRST_CHANGE # NEXT_CHANGE #
-----------------------------------------------------------------------
1 76 3 CURRENT 1601735 281474976710
2 74 2 INACTIVE 1580625 1600939
3 75 2 ACTIVE 1600939 1601735

SQL> select group #, status, type, member from v $ logfile;

GROUP # STATUS TYPE MEMBER
--------------------------------------------------------------------------------------------------------
(Space reasons, omitted ......)
2 ONLINE/u01/flash_recovery_area/WILSON/onlinelog/o1_mf_2_7xt1_rv _. log
1 ONLINE/u01/oradata/WILSON/onlinelog/o1_mf_1_7xt44nq1 _. log
1 ONLINE/u01/flash_recovery_area/WILSON/onlinelog/ow.mf_20177xt44qt6 _. log
1 ONLINE/u01/flash_recovery_area/WILSON/onlinelog/redolog01a. log

7 rows selected


After the logfile is switched, the status of the redolog01a. log File becomes null, indicating that everything is normal. It can be guessed that when the log is switched to a new log group, the original log content will be overwritten and refreshed. In this process, the members of Member are consistent. At this time, log files in Invalid state will be synchronized.

Further, although the size of the log file marked as Invalid is determined, it is not synchronized with other Group Member files. Therefore, Oracle marks it as Invalid.

According to Oracle's working rules, if the log file group in the Invalid status is not the Current log group, the content will be overwritten when the log is switched to this group. As a result, the Invalid status disappears.

If the Current Invalid is in a Current log group, Lgwr constantly writes content. How is this process?

3. Add members to the Current log Group

Then, what is the effect of adding a member to the Current log group in the above experiment results?


SQL> alter database add logfile member '/u01/flash_recovery_area/WILSON/onlinelog/redolog01b. log' to group 1;
Database altered

SQL> select group #, status, type, member from v $ logfile;

GROUP # STATUS TYPE MEMBER
--------------------------------------------------------------------------------------------------------
(Space reason, omitted ....)
1 ONLINE/u01/flash_recovery_area/WILSON/onlinelog/redolog01a. log
1 invalid online/u01/flash_recovery_area/WILSON/onlinelog/redolog01b. log

8 rows selected

SQL> select group #, sequence #, members, status, first_change #, next_change # from v $ log;

GROUP # SEQUENCE # members status FIRST_CHANGE # NEXT_CHANGE #
-----------------------------------------------------------------------
1 76 4 CURRENT 1601735 281474976710
2 74 2 INACTIVE 1580625 1600939
3 75 2 ACTIVE 1600939 1601735


After the Redo Entry is generated in the experiment, will the Lgwr be written into the Invalid log.


SQL> col name for a10;
SQL> select * from v $ statname where;

STATISTIC # name class STAT_ID
----------------------------------------
169 redo size 2 1236385760

SQL> select * from v $ mystat where STATISTIC #= 169;

Sid statistic # VALUE
------------------------------
43 169 8585072

SQL> delete t;
72591 rows deleted
SQL> commit;
Commit complete

SQL> select * from v $ mystat where STATISTIC #= 169;
Sid statistic # VALUE
------------------------------
43 169 36099756


Delete the data table T and generate the redo log (36099756-858072. What is the log file in Invalid status?


SQL> select group #, status, type, member from v $ logfile;

GROUP # STATUS TYPE MEMBER
--------------------------------------------------------------------------------------------------------
(Space reason, omitted part ......)
1 ONLINE/u01/flash_recovery_area/WILSON/onlinelog/ow.mf_20177xt44qt6 _. log
1 ONLINE/u01/flash_recovery_area/WILSON/onlinelog/redolog01a. log
1 invalid online/u01/flash_recovery_area/WILSON/onlinelog/redolog01b. log

8 rows selected

SQL> select group #, sequence #, members, status, first_change #, next_change # from v $ log;

GROUP # SEQUENCE # members status FIRST_CHANGE # NEXT_CHANGE #
-----------------------------------------------------------------------
1 76 4 CURRENT 1601735 281474976710
2 74 2 INACTIVE 1580625 1600939
3 75 2 INACTIVE 1600939 1601735


Although the redo log is generated and written to the file, the Invalid status remains unchanged. Note: even if there are logs in Invalid status in the current log group, the content will not be synchronized, and new logs will not be written to it.

Synchronization can be implemented only after the log group switches to the next loop.


SQL> alter system switch logfile;
System altered

SQL> alter system switch logfile;
System altered

SQL> alter system switch logfile; (long execution time)
System altered

SQL> select group #, status, type, member from v $ logfile;

GROUP # STATUS TYPE MEMBER
--------------------------------------------------------------------------------------------------------
(Space reason, omitted part ......)
1 ONLINE/u01/flash_recovery_area/WILSON/onlinelog/redolog01b. log

8 rows selected

SQL> select group #, sequence #, members, status, first_change #, next_change # from v $ log;

GROUP # SEQUENCE # members status FIRST_CHANGE # NEXT_CHANGE #
-----------------------------------------------------------------------
1 79 4 CURRENT 1603673 281474976710
2 77 2 ACTIVE 1603650 1603669
3 78 2 ACTIVE 1603669 1603673


It can be seen that the Invalid state is eliminated only after the loop. Next, we will discuss the impact of Invalid on the Oracle startup process.