Two interesting REDOLOGERROR Handling Methods

Two interesting REDOLOGERROR Handling Methods System Environment: Operating System: AIX-5300 Database: Oracle10g (10.2.0.1.0) Case Description: Database (archive mode) Non-current log group is damaged, but due to the failure mode is not

Two interesting REDOLOGERROR Handling Methods System Environment: Operating System: AIX-5300 Database: Oracle10g (10.2.0.1.0) Case Description: Database (archive mode) Non-current log group is damaged, but due to the failure mode is not

Two interesting redo log error handling methods

System Environment:

Operating System: AIX-5300

Database: Oracle 10g (10.2.0.1.0)

Case Description:

The database (archive mode) is not damaged by the current log group. However, due to the different destruction methods, the solution to 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

Bytes --------------------------------------------------------------------------------------------------------

/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 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

Close 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: (Prompt: the 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 a non-current log group and has been archived:

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

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