ORCLE REDO LOG文件管理

SQL> Select * from v$logfile ;

    GROUP# STATUS  TYPE    MEMBER                                                                                                                                IS_
---------- ------- ------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---
         3 STALE   ONLINE  /u01/oracle/oradata/orcl/redo03.log                                                                                                   NO
         2         ONLINE  /u01/oracle/oradata/orcl/redo02.log                                                                                                   NO
         1         ONLINE  /u01/oracle/oradata/orcl/redo01.log    

查看日志文件大小

#################################################################

SQL> ! du  -h /u01/oracle/oradata/orcl/redo01.log

51M     /u01/oracle/oradata/orcl/redo01.log

#################################################################

为当前存在的组增加成员

#################################################################

SQL> alter database add logfile member '/u01/oracle/oradata/orcl/redo011.log' to group 1 ;

Database altered.

SQL> alter database add logfile member '/u01/oracle/oradata/orcl/redo022.log' to group 2 ;

Database altered.

SQL> alter database add logfile member '/u01/oracle/oradata/orcl/redo033.log' to group 3 ;

Database altered.

增加新日志组

#################################################################

SQL> alter database add logfile group 4 '/u01/oracle/oradata/orcl/redo04.log' size 51M ;

Database altered.

SQL> alter database add logfile member '/u01/oracle/oradata/orcl/redo044.log' to group 4 ;

Database altered.

#################################################################

日志组切换

#################################################################

SQL> alter system switch logfile ;

System altered.

#################################################################

删除日志成员并删除日志组

#################################################################

SQL> alter database drop logfile member '/u01/oracle/oradata/orcl/redo044.log' ;

Database altered.

SQL> alter database drop logfile  group 4 ;
alter database drop logfile  group 4
*
ERROR at line 1:
ORA-01624: log 4 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 4 thread 1: '/u01/oracle/oradata/orcl/redo04.log'

报错了，因为目前数据库处于非归档模式，解决方法就是狂切日志

SQL> alter system switch logfile ;

System altered.

SQL> /

System altered.

SQL> /
/
/

System altered.

SQL> select * from v$log ;

System altered.

SQL>
System altered.

SQL>
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          9   52428800          2 NO  INACTIVE                593706 15-JUL-11
         2          1         11   52428800          2 NO  ACTIVE                  593711 15-JUL-11
         3          1         12   52428800          2 NO  CURRENT                 593713 15-JUL-11
         4          1         10   53477376          1 NO  ACTIVE                  593709 15-JUL-11

SQL> alter system switch logfile ;

System altered.

SQL> alter system switch logfile ;

System altered.

SQL> alter system switch logfile ;

System altered.

SQL> select * from v$log ;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         13   52428800          2 NO  INACTIVE                593717 15-JUL-11
         2          1         15   52428800          2 NO  CURRENT                 593721 15-JUL-11
         3          1         12   52428800          2 NO  INACTIVE                593713 15-JUL-11
         4          1         14   53477376          1 NO  INACTIVE                593719 15-JUL-11

SQL> alter database drop logfile group  4 ;

Database altered.

#################################################################

注：
ORACLE 进行数据库级存储迁移时，可以使用以上方法，转移redolog