修改在线重做日志遇到的问题

我们想给在线重做日志加大到2G一个。

同事采用了下面的方法。

SQL> SQL> alter database add logfile group 4 ('/slot/ems12614/oracle/app/ora12614/oradata/henryoiddb/redo04.log') size 2048M;

Database altered.

SQL> alter database add logfile group 5 ('/slot/ems12614/oracle/app/ora12614/oradata/henryoiddb/redo05.log') size 2048M;

Database altered.

SQL> alter database add logfile group 6 ('/slot/ems12614/oracle/app/ora12614/oradata/henryoiddb/redo06.log') size 2048M;

Database altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         3 NO  CURRENT--为当前正在使用的，肯定删除不了。
         4 YES UNUSED
         5 YES UNUSED
         6 YES UNUSED

SQL>  alter system switch logfile; 做个日志切换试试？

System altered.

SQL>  alter database drop logfile group 3;--还是删除不了。
 alter database drop logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance henryoiddb (thread 1)
ORA-00312: online log 3 thread 1:
'/slot/ems12614/oracle/app/ora12614/oradata/henryoiddb/redo03.log'

SQL> alter system switch logfile;再切换一次试试？

System altered.

SQL>  alter database drop logfile group 3;
 alter database drop logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance henryoiddb (thread 1)
ORA-00312: online log 3 thread 1:
'/slot/ems12614/oracle/app/ora12614/oradata/henryoiddb/redo03.log'

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         3 NO  ACTIVE--状态是活动状态，当然删除不了。日志里面有些数据是做实例恢复时要用到的。oracle在事务提交的时候只是写了日志文件就说事务完成了。
         4 NO  ACTIVE
         5 NO  CURRENT
         6 YES UNUSED

SQL> alter system switch logfile;

System altered.

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         3 NO  ACTIVE
         4 NO  ACTIVE
         5 NO  ACTIVE
         6 NO  CURRENT

SQL>  alter system switch logfile;

System altered.

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         3 NO  CURRENT----这里面的数据没有写到数据文件里永久保存。所以就是删除不了。大家可以想一下DBWR都是在什么情况下工作的？
         4 NO  INACTIVE
         5 NO  ACTIVE
         6 NO  ACTIVE

SQL>  alter database drop logfile group 3;
 alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance henryoiddb (thread 1) - cannot
drop
ORA-00312: online log 3 thread 1:
'/slot/ems12614/oracle/app/ora12614/oradata/henryoiddb/redo03.log'

SQL>  SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         3 NO  CURRENT
         4 NO  INACTIVE
         5 NO  ACTIVE
         6 NO  ACTIVE

SQL> alter system checkpoint;--手工发一个检查点让DBWR工作 。

System altered.

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         3 NO  CURRENT
         4 NO  INACTIVE
         5 NO  INACTIVE
         6 NO  INACTIVE

SQL> SQL> alter system switch logfile;

System altered.

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         3 NO  INACTIVE
         4 NO  CURRENT
         5 NO  INACTIVE
         6 NO  INACTIVE

SQL> alter database drop logfile group 3;删除成功

Database altered.