Recovering from Loss of a Redo Log File

好久没更新自己的blog了,准备继续...

[@more@]

A database instance stays up as long as at least one member of a redo log group is available. The alert log records the loss of a redo log group member; as with most database status information, the EM Database Control allows you to easily review the contents of the alert log.

The dynamic performance view V$LOGFILE provides the status of each member of each redo log file member of each redo log group; the STATUS column is defined as follows:

INVALID The file is corrupted or missing.

STALE This redo log file member is new and has never been used.

DELETED The file is no longer being used.

The redo log file is in use and is not corrupted.

When you are aware of a missing or deleted redo log file group member, follow these three steps to ensure that you maintain a maximum level of redundancy. Losing the remaining member(s) of the redo log group will cause the instance to fail.

1. Verify which redo log file group member is missing.

2. Archive the log file group’s contents; if you clear this log file group before archiving it, you must back up the full database to ensure maximum recoverability of the database in the case of the loss of a datafile. Use the command ALTER SYSTEM ARCHIVE LOG GROUP groupnum; to force the archive operation. (groupnum refers to the redo log group that you want to archive.)

3. Clear the log group to re-create the missing redo log file members using the command

ALTER DATABASE CLEAR LOGFILE GROUP groupnum;. Alternatively, you can replace the missing member by copying one of the good group members to the location of the missing member; using ALTER DATABASE CLEAR LOGFILE GROUP has the advantage of being platform independent.

In this example, you lose a redo log file group member and check the status of the redo log file groups using V$LOGFILE:

SQL> select * from v$logfile

2 order by group#;

( 下面的查询结果显示了 如何正确的设置redo log, 3个group,每个group分别有两个members,分别放在不同的磁盘上,可以看出每个group的member 1都放在u07磁盘上,而member 2 都放在u08上)

GROUP# STATUS TYPE MEMBER IS_

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

1 ONLINE /u07/oradata/ord/redo01.log NO

1 ONLINE /u08/oradata/ord/redo01.log NO

2 ONLINE /u07/oradata/ord/redo02.log NO

2 ONLINE /u08/oradata/ord/redo02.log NO

3 ONLINE /u07/oradata/ord/redo03.log NO

3 ONLINE /u08/oradata/ord/redo03.log NO

6 rows selected.

SQL> ! rm /u08/oradata/ord/redo01.log

SQL> select * from v$logfile order by group#;

GROUP# STATUS TYPE MEMBER IS_

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

1 ONLINE /u07/oradata/ord/redo01.log NO

1 INVALID ONLINE /u08/oradata/ord/redo01.log NO

2 ONLINE /u07/oradata/ord/redo02.log NO

2 ONLINE /u08/oradata/ord/redo02.log NO

3 ONLINE /u07/oradata/ord/redo03.log NO

3 ONLINE /u08/oradata/ord/redo03.log NO

6 rows selected.

It appears that group number 1 has a missing member, so you want to archive group

number 1 using the ALTER SYSTEM ARCHIVE command:

SQL> alter system archive log group 1;

Finally, you can re-create the missing redo log file group member using the ALTER DATABASE command mentioned in step 3:

SQL> alter database clear logfile group 1;

Database altered.

Checking the view V$LOGFILE again, you can see that the redo log group member is no longer invalid:

SQL> select * from v$logfile order by group#;

GROUP# STATUS TYPE MEMBER IS_

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

1 ONLINE /u07/oradata/ord/redo01.log NO

1 ONLINE /u08/oradata/ord/redo01.log NO

2 ONLINE /u07/oradata/ord/redo02.log NO

2 ONLINE /u08/oradata/ord/redo02.log NO

3 ONLINE /u07/oradata/ord/redo03.log NO

3 ONLINE /u08/oradata/ord/redo03.log NO

6 rows selected.

By reviewing the contents of the alert log using either the EM Database Control interface

by clicking the Alert Log Content link at the bottom of the Database Control home page or by

reviewing the file $ORACLE_BASE/admin/ord/bdump/alert_ord.log, you can see the failures

associated with the missing redo log group member:

Sun Sep 12 17:31:43 2004

ARC1: Evaluating archive log 1 thread 1 sequence 2500

Sun Sep 12 17:31:43 2004

Errors in file /u01/app/oracle/admin/ord/bdump/ord_arc1_3717.trc:

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

ORA-00312: online log 1 thread 1: '/u08/oradata/ord/redo01.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7185924/viewspace-919373/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7185924/viewspace-919373/