Shulou(Shulou.com)06/01 Report--

Two interesting REDO LOG ERROR processing methods

System environment:

Operating system: AIX-5300

Database: Oracle 10g (10.2.0.1.0)

Case description:

The database (archive mode) is not the current log group is destroyed, but because of the different ways of destruction, the way to solve the problem is slightly different, which is very interesting.

Case 1: non-current log group files are deleted

SQL > select member from v$logfile

MEMBER

-

/ dsk1/oradata/prod/redo03a.log

/ dsk1/oradata/prod/redo02a.log

/ dsk1/oradata/prod/redo01a.log

/ dsk2/oradata/prod/redo01b.log

/ dsk2/oradata/prod/redo02b.log

/ dsk2/oradata/prod/redo03b.log

6 rows selected.

Delete a non-current log group:

[oracle@aix211 ~] $cd / dsk1/oradata/prod/

[oracle@aix211 prod] $ls

Control02.ctl redo01a.log redo02a.log redo03a.log

[oracle@aix211 prod] $rm redo01a.log

[oracle@aix211 prod] $cd / dsk2/oradata/prod/

[oracle@aix211 prod] $ls

Redo01b.log redo02b.log redo03b.log

[oracle@aix211 prod] $rm redo01b.log

Shut down the database and restart:

SQL > shutdown abort

ORACLE instance shut down.

SQL > startup

ORACLE instance started.

Total System Global Area 1258291200 bytes

Fixed Size 2020552 bytes

Variable Size 318769976 bytes

Database Buffers 922746880 bytes

Redo Buffers 14753792 bytes

Database mounted.

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1:'/ dsk1/oradata/prod/redo01a.log'

ORA-00312: online log 1 thread 1:'/ dsk2/oradata/prod/redo01b.log'

Alarm log: (hint: log group file cannot be found and cannot be read)

Errors in file / u01/app/oracle/admin/prod/udump/prod_ora_536600.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1:'/ dsk2/oradata/prod/redo01b.log'

ORA-27037: unable to obtain file status

IBM AIX RISC System/6000 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 1 thread 1:'/ dsk1/oradata/prod/redo01a.log'

ORA-27037: unable to obtain file status

IBM AIX RISC System/6000 Error: 2: No such file or directory

Additional information: 3

Completed: alter database clear logfile group 1

Mon May 26 10:02:36 2014

Alter database open

Mon May 26 10:02:36 2014

Block change tracking file is current.

Solution:

SQL > select * from v$log

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

-

1 1 452 52428800 2 YES INACTIVE 806225 26-MAY-14

3 1 451 52428800 2 YES INACTIVE 803970 26-MAY-14

2 1 453 52428800 2 NO CURRENT 806237 26-MAY-14

Because it is not the current log group, and the archiving has been completed:

SQL > alter database clear logfile group 1

Database altered.

Database opened successfully:

SQL > alter database open

Database altered.

SQL > select * from v$log

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

-

1 1 454 52428800 2 NO CURRENT 826578 26-MAY-14

2 1 453 52428800 2 YES INACTIVE 806237 26-MAY-14

3 1 451 52428800 2 YES INACTIVE 803970 26-MAY-14

SQL > alter system switch logfile

System altered.

Case 2: non-current log group file header is corrupted

[oracle@aix211 prod] $dd if=/dev/zero of=/dsk1/oradata/prod/redo03a.log bs=8192 count=3

3'0 records in

3'0 records out

[oracle@aix211 prod] $dd if=/dev/zero of=/dsk2/oradata/prod/redo03b.log bs=8192 count=3

3'0 records in

3'0 records out

Close the library and start Instance:

SQL > shutdown abort

ORACLE instance shut down.

SQL > startup

ORACLE instance started.

Total System Global Area 1258291200 bytes

Fixed Size 2020552 bytes

Variable Size 318769976 bytes

Database Buffers 922746880 bytes

Redo Buffers 14753792 bytes

Database mounted.

ORA-00316: log 3 of thread 1, type in header is not log file

ORA-00312: online log 3 thread 1:'/ dsk1/oradata/prod/redo03a.log'

ORA-00312: online log 3 thread 1:'/ dsk2/oradata/prod/redo03b.log'

Alarm log: (hint: the log group file header is not readable)

Errors in file / u01/app/oracle/admin/prod/bdump/prod_lgwr_499718.trc:

ORA-00316: log 3 of thread 1, type 0 in header is not log file

ORA-00312: online log 3 thread 1:'/ dsk2/oradata/prod/redo03b.log'

ORA-00316: log 3 of thread 1, type 0 in header is not log file

ORA-00312: online log 3 thread 1:'/ dsk1/oradata/prod/redo03a.log'

Mon May 26 10:16:37 2014

Solution:

Clear non-current log group:

SQL > select * from v$log

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

-

1 1 457 52428800 2 YES INACTIVE 827041 26-MAY-14

3 1 458 52428800 2 YES INACTIVE 827053 26-MAY-14

2 1 459 52428800 2 NO CURRENT 827067 26-MAY-14

SQL > alter database clear logfile group 3

Database altered.

Direct open database failed:

SQL > alter database open

Alter database open

*

ERROR at line 1:

ORA-00327: log 3 of thread 1, physical size less than needed

ORA-00312: online log 3 thread 1:'/ dsk1/oradata/prod/redo03a.log'

ORA-00312: online log 3 thread 1:'/ dsk2/oradata/prod/redo03b.log'

Errors in file / u01/app/oracle/admin/prod/bdump/prod_lgwr_499718.trc:

ORA-00316: log 3 of thread 1, type 0 in header is not log file

ORA-00312: online log 3 thread 1:'/ dsk2/oradata/prod/redo03b.log'

ORA-00316: log 3 of thread 1, type 0 in header is not log file

ORA-00312: online log 3 thread 1:'/ dsk1/oradata/prod/redo03a.log'

Mon May 26 10:16:37 2014

ARC0: STARTING ARCH PROCESSES

Mon May 26 10:16:37 2014

ORA-316 signalled during: ALTER DATABASE OPEN...

Restart Instance, reload controlfile, and resolve the problem:

SQL > startup force

ORACLE instance started.

Total System Global Area 1258291200 bytes

Fixed Size 2020552 bytes

Variable Size 318769976 bytes

Database Buffers 922746880 bytes

Redo Buffers 14753792 bytes

Database mounted.

Database opened.

SQL >

The above two cases are aimed at the case that the redo log file is destroyed, the methods to solve the problem are slightly different, and the solutions are also different, which should be summarized and summarized in practice.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.