Redo log file Recovery

1. 실습 환경 설정

실습을 위해 아래와 같이 Redo log group 및 member 을 다중화 한다.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status

  2  from v$logfile a, v$log b

  3  where a.group# = b.group#

  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS

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

         1 /home/oracle/disk4/redo01_a.log                             5 NO  CURRENT

         1 /home/oracle/disk5/redo01_b.log                             5 NO  CURRENT

         2 /home/oracle/disk4/redo02_a.log                             5 YES UNUSED

         2 /home/oracle/disk5/redo02_b.log                             5 YES UNUSED

         3 /home/oracle/disk4/redo03_a.log                             5 YES UNUSED

         3 /home/oracle/disk5/redo03_b.log                             5 YES UNUSED

6 rows selected.

2. Group의 한 Member 1개 장애 (Active, Inactive 공통)

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status

  2  from v$logfile a, v$log b

  3  where a.group# = b.group#

  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS

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

         1 /home/oracle/disk4/redo01_a.log                             5 NO  CURRENT

         1 /home/oracle/disk5/redo01_b.log                             5 NO  CURRENT

         2 /home/oracle/disk4/redo02_a.log                             5 YES UNUSED

         2 /home/oracle/disk5/redo02_b.log                             5 YES UNUSED

         3 /home/oracle/disk4/redo03_a.log                             5 YES UNUSED

         3 /home/oracle/disk5/redo03_b.log                             5 YES UNUSED

6 rows selected.

SQL> !rm -rf /home/oracle/disk5/redo01_b.log

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status

  2  from v$logfile a, v$log b

  3  where a.group# = b.group#

  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS

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

         1 /home/oracle/disk4/redo01_a.log                             5 YES INACTIVE

         1 /home/oracle/disk5/redo01_b.log                             5 YES INACTIVE

         2 /home/oracle/disk4/redo02_a.log                             5 NO  CURRENT

         2 /home/oracle/disk5/redo02_b.log                             5 NO  CURRENT

         3 /home/oracle/disk4/redo03_a.log                             5 YES INACTIVE

         3 /home/oracle/disk5/redo03_b.log                             5 YES INACTIVE

6 rows selected.

SQL> alter database drop logfile member '/home/oracle/disk5/redo01_b.log';

Database altered.

SQL> alter database add logfile member '/home/oracle/disk5/redo01_b.log' to group 1;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status

  2  from v$logfile a, v$log b

  3  where a.group# = b.group#

  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS

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

         1 /home/oracle/disk4/redo01_a.log                             5 NO  CURRENT

         1 /home/oracle/disk5/redo01_b.log                             5 NO  CURRENT

         2 /home/oracle/disk4/redo02_a.log                             5 YES ACTIVE

         2 /home/oracle/disk5/redo02_b.log                             5 YES ACTIVE

         3 /home/oracle/disk4/redo03_a.log                             5 YES ACTIVE

         3 /home/oracle/disk5/redo03_b.log                             5 YES ACTIVE

6 rows selected.

SQL> 

※ alert_testdb.log 내용

Fri Mar 12 11:26:22 2010

Thread 1 advanced to log sequence 7

  Current log# 2 seq# 7 mem# 0: /home/oracle/disk4/redo02_a.log

  Current log# 2 seq# 7 mem# 1: /home/oracle/disk5/redo02_b.log

Fri Mar 12 11:26:22 2010

Errors in file /home/oracle/admin/testdb/bdump/testdb_arc0_3194.trc:

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

ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Fri Mar 12 11:26:22 2010

Errors in file /home/oracle/admin/testdb/bdump/testdb_arc0_3194.trc:

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

ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Thread 1 advanced to log sequence 8

  Current log# 3 seq# 8 mem# 0: /home/oracle/disk4/redo03_a.log

  Current log# 3 seq# 8 mem# 1: /home/oracle/disk5/redo03_b.log

Thread 1 cannot allocate new log, sequence 9

Checkpoint not complete

  Current log# 3 seq# 8 mem# 0: /home/oracle/disk4/redo03_a.log

  Current log# 3 seq# 8 mem# 1: /home/oracle/disk5/redo03_b.log

Fri Mar 12 11:26:32 2010

Thread 1 advanced to log sequence 9

  Current log# 1 seq# 9 mem# 0: /home/oracle/disk4/redo01_a.log

  Current log# 1 seq# 9 mem# 1: /home/oracle/disk5/redo01_b.log

Fri Mar 12 11:27:18 2010

Thread 1 advanced to log sequence 10

  Current log# 2 seq# 10 mem# 0: /home/oracle/disk4/redo02_a.log

  Current log# 2 seq# 10 mem# 1: /home/oracle/disk5/redo02_b.log

Fri Mar 12 11:27:18 2010

Errors in file /home/oracle/admin/testdb/bdump/testdb_arc1_3196.trc:

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

ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Fri Mar 12 11:27:18 2010

Errors in file /home/oracle/admin/testdb/bdump/testdb_arc1_3196.trc:

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

ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Thread 1 advanced to log sequence 11

  Current log# 3 seq# 11 mem# 0: /home/oracle/disk4/redo03_a.log

  Current log# 3 seq# 11 mem# 1: /home/oracle/disk5/redo03_b.log

Thread 1 cannot allocate new log, sequence 12

Checkpoint not complete

  Current log# 3 seq# 11 mem# 0: /home/oracle/disk4/redo03_a.log

  Current log# 3 seq# 11 mem# 1: /home/oracle/disk5/redo03_b.log

Thread 1 advanced to log sequence 12

  Current log# 1 seq# 12 mem# 0: /home/oracle/disk4/redo01_a.log

  Current log# 1 seq# 12 mem# 1: /home/oracle/disk5/redo01_b.log

Fri Mar 12 11:27:30 2010

Thread 1 advanced to log sequence 13

  Current log# 2 seq# 13 mem# 0: /home/oracle/disk4/redo02_a.log

  Current log# 2 seq# 13 mem# 1: /home/oracle/disk5/redo02_b.log

Fri Mar 12 11:27:30 2010

Errors in file /home/oracle/admin/testdb/bdump/testdb_arc0_3194.trc:

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

ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Fri Mar 12 11:27:30 2010

Errors in file /home/oracle/admin/testdb/bdump/testdb_arc0_3194.trc:

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

ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Fri Mar 12 11:27:30 2010

Expanded controlfile section 11 from 28 to 205 records

Requested to grow by 177 records; added 7 blocks of records

Fri Mar 12 11:27:52 2010

alter database drop logfile member '/home/oracle/disk5/redo01_b.log'

Fri Mar 12 11:27:52 2010

Completed: alter database drop logfile member '/home/oracle/disk5/redo01_b.log'

Fri Mar 12 11:28:12 2010

alter database add logfile member '/home/oracle/disk5/redo01_b.log' to group 1

Fri Mar 12 11:28:12 2010

Completed: alter database add logfile member '/home/oracle/disk5/redo01_b.log' to group 1

Fri Mar 12 11:28:27 2010

Thread 1 advanced to log sequence 14

  Current log# 3 seq# 14 mem# 0: /home/oracle/disk4/redo03_a.log

  Current log# 3 seq# 14 mem# 1: /home/oracle/disk5/redo03_b.log

Thread 1 advanced to log sequence 15

  Current log# 1 seq# 15 mem# 0: /home/oracle/disk4/redo01_a.log

  Current log# 1 seq# 15 mem# 1: /home/oracle/disk5/redo01_b.log

3. Inactive한 Group 장애

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status

  2  from v$logfile a, v$log b

  3  where a.group# = b.group#

  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS

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

         1 /home/oracle/disk4/redo01_a.log                             5 NO  CURRENT

         1 /home/oracle/disk5/redo01_b.log                             5 NO  CURRENT

         2 /home/oracle/disk4/redo02_a.log                             5 YES INACTIVE

         2 /home/oracle/disk5/redo02_b.log                             5 YES INACTIVE

         3 /home/oracle/disk4/redo03_a.log                             5 YES INACTIVE

         3 /home/oracle/disk5/redo03_b.log                             5 YES INACTIVE

6 rows selected.

SQL> !rm -rf /home/oracle/disk4/redo03_a.log

SQL> !rm -rf /home/oracle/disk5/redo03_b.log

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status

  2  from v$logfile a, v$log b

  3  where a.group# = b.group#

  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS

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

         1 /home/oracle/disk4/redo01_a.log                             5 NO  CURRENT

         1 /home/oracle/disk5/redo01_b.log                             5 NO  CURRENT

         2 /home/oracle/disk4/redo02_a.log                             5 YES INACTIVE

         2 /home/oracle/disk5/redo02_b.log                             5 YES INACTIVE

         3 /home/oracle/disk4/redo03_a.log                             5 NO  INACTIVE

         3 /home/oracle/disk5/redo03_b.log                             5 NO  INACTIVE

6 rows selected.

SQL> alter database drop logfile group 3;

alter database drop logfile group 3

*

ERROR at line 1:

ORA-00350: log 3 of instance testdb (thread 1) needs to be archived

ORA-00312: online log 3 thread 1: '/home/oracle/disk4/redo03_a.log'

ORA-00312: online log 3 thread 1: '/home/oracle/disk5/redo03_b.log'

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status

  2  from v$logfile a, v$log b

  3  where a.group# = b.group#

  4  order by 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS

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

         1 /home/oracle/disk4/redo01_a.log                             5 NO  CURRENT

         1 /home/oracle/disk5/redo01_b.log                             5 NO  CURRENT

         2 /home/oracle/disk4/redo02_a.log                             5 NO  INACTIVE

         2 /home/oracle/disk5/redo02_b.log                             5 NO  INACTIVE

         3 /home/oracle/disk4/redo03_a.log                             5 NO  INACTIVE

         3 /home/oracle/disk5/redo03_b.log                             5 NO  INACTIVE

6 rows selected.

SQL>