维护在线redo log 

Oracle Database - Enterprise Edition - Version 11.2.0.4 to 11.2.0.4  
 
 
1. 首先查看当前redo log的大小:             
[oracle@oracle ~]$ sqlplus / as sysdba
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

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

    GROUP#      BYTES STATUS
---------- ---------- ----------------
         1   52428800 CURRENT
         2   52428800 INACTIVE
         3   52428800 INACTIVE                        

    当前redo log 是50M，现在扩展到100M                               
                                  

2. 查看所有的在线日子组成员                                         
                                                    
   SQL> select group#, member from v$logfile;                                    
                                                       
    GROUP# MEMBER
---------- --------------------------------------------------
         3 /oracle/oradata/orcl/redo03.log
         2 /oracle/oradata/orcl/redo02.log
         1 /oracle/oradata/orcl/redo01.log
                      
                                                                
3. 新增加group# 4/5/6大小为100M的redolog
                           
                            
   SQL> alter database add logfile group 4                              
           '/oracle/oradata/orcl/log4PROD.dbf' size 100M;                                 
                                 
   SQL> alter database add logfile group 5                                  
           '/oracle/oradata/orcl/log5PROD.dbf' size 100M;     
        
   SQL> alter database add logfile group 6          
           '/oracle/oradata/orcl/log6PROD.dbf' size 100M;   
        

4. 查询当前所有redo log的状态:                                   
                                                                         
   SQL> select group#, status from v$log;                                       

      GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE
         4 UNUSED
         5 UNUSED
         6 UNUSED      
      
  
 当前redolog状态，group 1是当前的在线日志组，group2、3为inactive，我们要删除的是50M的group 1、2、3
   group 1 是当前在线日志组，不可以删除，目前可以删除inactive的group 2、3
   
   SQL> alter database drop logfile group 2;  
      Database altered.   
                            
   SQL> alter database drop logfile group 3;
      Database altered.
      
5. 多次switch log切换日志,直至group 1为inactive状态，:                                  

    SQL> alter system checkpoint; 

    System altered.

 
    SQL> alter system switch logfile;

    System altered.


   SQL> select group#, status from v$log;                                       

      GROUP# STATUS
---------- ----------------
         1 INACTIVE
         4 UNUSED
         5 UNUSED
         6 UNUSED 
         
  SQL> alter database drop logfile group 1;  
      Database altered.          

注:如果checkpoint，还是不能切换到inactive，那就做几次全局checkpoint

    SQL> alter system checkpoint global;

    System altered
                                                             


6.再次查看在线日志所有状态:   
                                                         
                                                                
SQL> select group#, bytes, status from v$log;

    GROUP#      BYTES STATUS
---------- ---------- ----------------
         4  104857600 INACTIVE
         5  104857600 CURRENT
         6  104857600 UNUSED
 
   
9.  操作系统层面删除旧的redolog
    
[oracle@oracle orcl]$rm /oracle/oradata/orcl/redo01.log
[oracle@oracle orcl]$rm /oracle/oradata/orcl/redo02.log  
[oracle@oracle orcl]$rm /oracle/oradata/orcl/redo03.log                                                
                                                  

10.删除redo log 最好做一次数据库的备份，维护reodlog的时候最好在业务低峰的时候操作