Oracle如何維護online redo log

這篇文章給大家分享的是有關Oracle如何維護online redo log的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

維護在線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的時候最好在業務低峰的時候操作

感謝各位的閱讀！關于“Oracle如何維護online redo log”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！