题目:
7. In your database, online redo log files are multiplexed and one of the
members in a group is lost due to media failure?
How would you recover the lost redo log member?
A.import the database from the last export
B.restore all the members in the group from the last backup
C.drop the lost member from the database and then add a new member to the
group
D.restore all the database files from the backup and then perform a
complete recovery
E.restore all the database files from the backup and then perform an
incomplete recovery
Answer: C
题目翻译:数据库中online redo log 是multiplexed的,一个group其中的一个member发生media failure,怎么恢复呢?
答案解释:一个group做了multiplex,那么就有多个member,这些member是完全相同的,所以先drop掉坏的再add一个新的就可以了。如下操作:
第一步:模拟删除正在运行中的数据库的在线日志文件;
[oracle@centos oradata]$ ll
total 1134676
-rw-r----- 1 oracle oinstall 31465472 Nov 8 06:15 app101.dbf
-rw-r----- 1 oracle oinstall 9846784 Nov 9 05:47 ora_control1
-rw-r----- 1 oracle oinstall 9846784 Nov 9 05:47 ora_control2
-rw-r----- 1 oracle oinstall 52429312 Nov 9 05:46 redo01a.log
-rw-r----- 1 oracle oinstall 52429312 Nov 9 05:46 redo01b.log
-rw-r----- 1 oracle oinstall 52429312 Nov 9 05:37 redo02a.log
-rw-r----- 1 oracle oinstall 52429312 Nov 9 05:37 redo02b.log
-rw-r----- 1 oracle oinstall 209723392 Nov 9 05:43 sysaux01.dbf
-rw-r----- 1 oracle oinstall 270344192 Nov 9 05:43 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 Nov 8 04:54 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 Nov 9 05:43 undotbs01.dbf
-rw-r----- 1 oracle oinstall 104865792 Nov 9 05:37 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 Oct 15 08:04 users01.dbf.bak
[oracle@centos oradata]$ rm redo02b.log
[oracle@centos oradata]$ ll
total 1083472
-rw-r----- 1 oracle oinstall 31465472 Nov 8 06:15 app101.dbf
-rw-r----- 1 oracle oinstall 9846784 Nov 9 05:48 ora_control1
-rw-r----- 1 oracle oinstall 9846784 Nov 9 05:48 ora_control2
-rw-r----- 1 oracle oinstall 52429312 Nov 9 05:48 redo01a.log
-rw-r----- 1 oracle oinstall 52429312 Nov 9 05:48 redo01b.log
-rw-r----- 1 oracle oinstall 52429312 Nov 9 05:37 redo02a.log
-rw-r----- 1 oracle oinstall 209723392 Nov 9 05:47 sysaux01.dbf
-rw-r----- 1 oracle oinstall 270344192 Nov 9 05:47 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 Nov 8 04:54 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 Nov 9 05:47 undotbs01.dbf
-rw-r----- 1 oracle oinstall 104865792 Nov 9 05:37 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 Oct 15 08:04 users01.dbf.bak
[oracle@centos oradata]$
第二步:日志中发现错误,因为少了一个member
执行几次日志切换,以便于在日志中找到问题
SQL> startup open;
ORACLE instance started.
Total System Global Area 801701888 bytes
Fixed Size 2217632 bytes
Variable Size 503318880 bytes
Database Buffers 289406976 bytes
Redo Buffers 6758400 bytes
Database mounted.
Database opened.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL>
查看alert_mercury.log(mercury是我数据库的SID)会发现如下错误,但数据库正确运行是没有任何影响:
Errors in file /oracle/mercury/diag/diag/rdbms/mercury/mercury/trace/mercury_arc0_1910.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oracle/mercury/oradata/redo02b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /oracle/mercury/diag/diag/rdbms/mercury/mercury/trace/mercury_arc0_1910.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oracle/mercury/oradata/redo02b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Archived Log entry 22 added for thread 1 sequence 2 ID 0x212ba1b7 dest 1:
Wed Nov 09 05:49:54 2011
Errors in file /oracle/mercury/diag/diag/rdbms/mercury/mercury/trace/mercury_m000_2014.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oracle/mercury/oradata/redo02b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Checker run found 1 new persistent data failures
第三步:drop并add对应的member
SQL> desc v$logfile;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
SQL> col member for a30
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
2 ONLINE /oracle/mercury/oradata/redo02 NO
a.log
2 ONLINE /oracle/mercury/oradata/redo02 NO
b.log
1 ONLINE /oracle/mercury/oradata/redo01 NO
a.log
1 ONLINE /oracle/mercury/oradata/redo01 NO
b.log
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
SQL> ALTER DATABASE DROP LOGFILE MEMBER '/oracle/mercury/oradata/redo02b.log'
2 ;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
2 ONLINE /oracle/mercury/oradata/redo02 NO
a.log
1 ONLINE /oracle/mercury/oradata/redo01 NO
a.log
1 ONLINE /oracle/mercury/oradata/redo01 NO
b.log
SQL> col member for a40
SQL> ALTER DATABASE ADD LOGFILE MEMBER '/oracle/mercury/oradata/redo02b.log' TO GROUP 2;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
2 ONLINE /oracle/mercury/oradata/redo02a.log NO
2 INVALID ONLINE /oracle/mercury/oradata/redo02b.log NO
1 ONLINE /oracle/mercury/oradata/redo01a.log NO
1 ONLINE /oracle/mercury/oradata/redo01b.log NO
SQL>
Note:
Fully specify the filenames of new log members to indicate where the operating system files should be created. Otherwise, the files will be created in either the default or current directory of the database server, depending upon your operating system. You may also note that the status of the new log member is shown asINVALID
. This is normal and it will change to active (blank) when it is first used.
至此就可以使在线日志文件恢复到正常状态了;
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
2 ONLINE /oracle/mercury/oradata/redo02a.log NO
2 ONLINE /oracle/mercury/oradata/redo02b.log NO
1 ONLINE /oracle/mercury/oradata/redo01a.log NO
1 ONLINE /oracle/mercury/oradata/redo01b.log NO
SQL>
alert_mercury.log也不提示错误了
Wed Nov 09 06:06:24 2011
Thread 1 advanced to log sequence 4 (LGWR switch)
Current log# 2 seq# 4 mem# 0: /oracle/mercury/oradata/redo02a.log
Current log# 2 seq# 4 mem# 1: /oracle/mercury/oradata/redo02b.log
Wed Nov 09 06:06:24 2011
Archived Log entry 23 added for thread 1 sequence 3 ID 0x212ba1b7 dest 1:
Thread 1 cannot allocate new log, sequence 5
Checkpoint not complete
Current log# 2 seq# 4 mem# 0: /oracle/mercury/oradata/redo02a.log
Current log# 2 seq# 4 mem# 1: /oracle/mercury/oradata/redo02b.log
Thread 1 advanced to log sequence 5 (LGWR switch)
Current log# 1 seq# 5 mem# 0: /oracle/mercury/oradata/redo01a.log
Current log# 1 seq# 5 mem# 1: /oracle/mercury/oradata/redo01b.log
Wed Nov 09 06:06:29 2011
Archived Log entry 24 added for thread 1 sequence 4 ID 0x212ba1b7 dest 1: