调整redo log 日志大小

朋友的一数据库redo log 日志太小，导致日志切换很频繁。

 1.查看当前日志组成员

SQL>  select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/oracle/oradata/orcl/redo03.log
/u01/oracle/oradata/orcl/redo02.log
/u01/oracle/oradata/orcl/redo01.log

2. 查看当前日志组状态：

SQL> select group#,members,bytes/1024/1024,status from v$log;

    GROUP#    MEMBERS BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
         1          1 50 ACTIVE
         2          1 50 CURRENT
         3          1 50 INACTIVE

现在有三个日志成员，大小为50M，欲更改为100M

增加日志组

SQL>alter database add logfile group 4 ('/u01/oracle/oradata/orcl/redo04.log') size 100M;
SQL>alter database add logfile group 5 ('/u01/oracle/oradata/orcl/redo05.log') size 100M;
SQL>alter database add logfile group 6 ('/u01/oracle/oradata/orcl/redo06.log') size 100M;

3.切换到新增的日志组上

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.
SQL> select group#,members,bytes/1024/1024,status from v$log

    GROUP#    MEMBERS BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
         1          1              50 INACTIVE
         2          1              50 INACTIVE
         3          1              50 ACTIVE
         4          1             100 CURRENT
         5          1             100 UNUSED
         6          1             100 UNUSED

a. CURRENT指当前的日志文件，在进行实例恢复时是必须的；
b. ACTIVE是指活动的非当前日志，在进行实例恢复时会被用到。Active状态意味着，Checkpoint尚未完成，因此该日志文件不能被覆盖。
c. INACTIVE是非活动日志，在实例恢复时不再需要，但在介质恢复时可能需要。
d. UNUSED表示该日志从未被写入，可能是刚添加的，或RESETLOGS后被重置。

4.删除旧的日志组

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2
  2  /

Database 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 dbserver (thread 1)
ORA-00312: online log 3 thread 1: '/u01/oracle/oradata/orcl/redo03.log'

由于log 3 日志成员还出去active 状态，所以不能drop掉的，再次执行 alter system switch logfile; 

SQL> select group#,members,bytes/1024/1024,status from v$log;

    GROUP# MEMBERS BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
         3 1 50 INACTIVE
         4 1 100 ACTIVE
         5 1 100 CURRENT
         6 1 100 UNUSED
SQL> alter database drop logfile group 3;

Database altered.


SQL> select group#,members,bytes/1024/1024,status from v$log;

    GROUP#    MEMBERS BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
         4          1             100 ACTIVE
         5          1             100 ACTIVE
         6 1 100 CURRENT

在操作系统下删除掉redolog 日志文件

mv  /u01/oracle/oradata/orcl/redo0[1-3].log /tmp