恢復(fù)測(cè)試數(shù)據(jù)庫(kù)時(shí)遇到了日志組不讓刪的情況。
因?yàn)檫@個(gè)測(cè)試數(shù)據(jù)庫(kù)是從RAC克隆而來(lái)，每個(gè)節(jié)點(diǎn)都有獨(dú)立的REDO LOG FILE，變成單實(shí)例后，
只有節(jié)點(diǎn)1的REDO LOG FILE再用，那就把節(jié)點(diǎn)2的刪掉吧，再刪節(jié)點(diǎn)2的REDO LOG FILE時(shí)，遇到了ORA-01567錯(cuò)誤。
SQL> select b.thread#,a.group#,a.member,bytes/1024/1024,b.members from v$logfile a,v$log b where a.group#=b.group#
THREAD# GROUP# MEMBER BYTES/1024/1024 MEMBERS
---------- ----------
-------------------------------------------------------------------------------- --------------- ----------
1 1
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_kk0nkxxb_.log 500 1
1 3
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_kk0nkyjm_.log 500 1
2 13
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_13_kk0nkzw0_.log 500 1
2 16
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_16_kk0nl2xt_.log 500 1
SQL> select group#,thread#,sequence#,members,status from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 1 7 1 INACTIVE
3 1 8 1 CURRENT
13 2 1 1 INACTIVE
16 2 0 1 UNUSED
嘗試直接刪除THREAD=2對(duì)應(yīng)的日志文件組13,16
SQL> alter database drop logfile group 13;
alter database drop logfile group 13
*
ERROR at line 1:
ORA-01567: dropping log 1 would leave less than 2 log files for instance orcl (thread 2)
ORA-00312: online log 13 thread 2: '
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_13_kk0nkzw0_.log'
在節(jié)點(diǎn)2的REDO LOG FILE就剩2個(gè)的時(shí)候，就不讓刪了，這是REDO的機(jī)制，每個(gè)節(jié)點(diǎn)不能少于2個(gè)日志組，如果少于2個(gè)日志無(wú)法切換了。
解決方法很簡(jiǎn)單，把節(jié)點(diǎn)2給踢出去就行了。
SQL> alter database disable THREAD 2;
Database altered.
SQL> alter database drop logfile group 13;
Database altered.
SQL> alter database drop logfile group 16;
Database altered.
然后還得去操作系統(tǒng)上把這些刪除掉的REDO LOG FILE刪掉。