Oracle online log group corruption handling

Oracle current online log group corrupt processing 1 oracle log Features Summary 1 Oracle log switching rules (switching from maximum sequence # to minimum sequence #) eg is as follows: the next log group will be sequenc

Oracle current online log group corrupt processing 1 oracle log Features Summary 1 Oracle log switching rules (switching from maximum sequence # to minimum sequence #) eg is as follows: the next log group will be sequenc

Oracle online log group corruption handling

1. Summary of oracle log features

1 oracle log switching rule (switching from the maximum sequence # To the minimum sequence)

For example, the next log group is the No. 5 log group with sequence #27.

SQL> select group #, archived, sequence #, status from v $ log;

GROUP # arc sequence # STATUS

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

1 NO 32 CURRENT

2 YES 28 INACTIVE

3 YES 29 INACTIVE

4 YES 30 INACTIVE

5 YES 27 INACTIVE <------ minimum sequence # In a public log Group

6 YES 31 INACTIVE

6 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> select group #, archived, sequence #, status from v $ log;

GROUP # arc sequence # STATUS

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

1 YES 32 ACTIVE

2 YES 28 INACTIVE

3 YES 29 INACTIVE

4 YES 30 INACTIVE

5 NO 33 CURRENT

6 YES 31 INACTIVE

6 rows selected.

2. quickly convert the oracle log group status to inactive.

After the log group is switched, the status of the previous log group changes from current to active on the Hong Kong virtual host, in fact, because the scn value of the current data file header is still in the low scn and next scn of the active log group, therefore, if we immediately initiate the alter sytem checkpoint command to promote the scn change of the data file header, the log status will change from active to inactive.

SQL> select group #, archived, sequence #, status from v $ log;

GROUP # arc sequence # STATUS

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

1 YES 32 INACTIVE

2 YES 34 INACTIVE

3 YES 35 INACTIVE

4 NO 36 CURRENT

5 YES 33 INACTIVE

6 YES 31 INACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select group #, archived, sequence #, status from v $ log;

GROUP # arc sequence # STATUS

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

1 YES 32 INACTIVE

2 YES 34 INACTIVE

3 YES 35 INACTIVE

4 YES 36 ACTIVE <------ logs with sequence number 36 are active

5 YES 33 INACTIVE

6 NO 37 CURRENT

6 rows selected.

Query the data file header scn as follows:

SQL> select hxfil, fhscn from x $ kcvfh;

HXFIL FHSCN

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

1 2781239221

2 2781239221

3 2781239221

4 2781239221

5 2781239221

6 2781239221

7 2781239221

8 2781239221

11 2781239221

12 2781239221

13 2781239221

SQL> select sequence #, first_change #, next_change # from v $ log_history;

SEQUENCE # FIRST_CHANGE # NEXT_CHANGE #

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

32 2781218249 2781218300

33 2781218300 2781219174

34 2781219174 2781219184

35 2781219184 2781239220

36 2781239220 2781239424

The scn: 2781239221 in the data file header is in the log scn range of 36 sequence: 2781239220 2781239424

SQL> alter system checkpoint;

System altered.

SQL> select hxfil, fhscn from x $ kcvfh;

HXFIL FHSCN

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

1 2781239456

2 2781239456

3 2781239456

4 2781239456

5 2781239456

6 2781239456

7 2781239456

8 2781239456

11 2781239456

12 2781239456

13 2781239456

11 rows selected.

SQL> select group #, archived, sequence #, status from v $ log;

GROUP # arc sequence # STATUS

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

1 YES 32 INACTIVE

2 YES 34 INACTIVE

3 YES 35 INACTIVE

4 YES 36 INACTIVE

5 YES 33 INACTIVE

6 NO 37 CURRENT

3. clear log group execution Conditions

The status of the clear log group cannot be acitve or the current log group.

Eg:

SQL> select group #, archived, sequence #, status from v $ log;

GROUP # arc sequence # STATUS

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

1 NO 38 CURRENT

2 YES 34 INACTIVE

3 YES 35 INACTIVE

4 YES 36 INACTIVE

5 YES 33 INACTIVE

6 YES 37 ACTIVE

SQL> alter database clear logfile group 6;

Alter database clear logfile group 6

*

ERROR at line 1:

ORA-01624: log 6 needed for crash recovery of instance CRM (thread 1)

ORA-00312: online log 6 thread 1: '/oracle/CRM2/CRM/redo06.log'

ORA-00312: online log 6 thread 1: '/oracle/CRM2/CRM/redo06b. Log'

Database 2 has been open. All members in the current online log group are damaged.

In this case, the log cannot be switched, and we can only find it when it affects the application of the database. The log status is as follows:

1. The database log status is as follows:

SQL> select group #, archived, sequence #, status from v $ log;

GROUP # arc sequence # STATUS

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

1 NO 44 INACTIVE

2 NO 40 INACTIVE

3 NO 41 INACTIVE

4 NO 42 INACTIVE

5 NO 45 CURRENT

6 NO 43 INACTIVE

6 rows selected.