oracle 線上修改online redo logfiles size 大小

線上修改online redo logfiles size 大小  
 
oracle redolog size 過小有時候會導致效能問題，現在我們線上修改redolog，一般在業務量比較小的時候進行此操作  
 
1. 首先檢視當前的日誌組大小，路徑名             
                 
[oracle@tora10 ~]$ sqlplus / as sysdba  
 
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 23 17:08:33 2013  
 
SQL> select group#, bytes, status from v$log;   
 
    GROUP#      BYTES STATUS  
---------- ---------- ----------------  
         1  524288000 INACTIVE  
         2  524288000 CURRENT  
         3  524288000 INACTIVE  
           
    當前的日誌組大小是 512MB 檔案系統，每個組成員只有1個member                                
                                    
   SQL> select group#, member from v$logfile;                                      
                                                         
    GROUP# MEMBER  
---------- ----------------------------------------  
         1 /opt/oracle/oradata/test/redo01.log  
         2 /opt/oracle/oradata/test/redo02.log  
         3 /opt/oracle/oradata/test/redo03.log                                   
                                                                 
                                                                  
2. 現在建立新的redolog group 4, 5, and 6 每個log大小為1024MB                            
                              
   SQL> alter database add logfile group 4                                
           '/opt/oracle/oradata/test/redo04.log ' size 1024M;                                   
                                   
   SQL> alter database add logfile group 5                                    
           '/opt/oracle/oradata/test/redo05.log ' size 1024M;       
          
   SQL> alter database add logfile group 6            
           '/opt/oracle/oradata/test/redo06.log ' size 1024M;     
          
 
3. 建立完新的日誌組後，查詢所有日誌組狀態:                                     
                                                                           
   SQL> select group#, status from v$log;                                         
 
      GROUP# STATUS  
   --------- ----------------  
           1 INACTIVE  
           2 CURRENT  
           3 INACTIVE              
           4 UNUSED  
           5 UNUSED  
           6 UNUSED           
                         
   當前使用的是group 2 日誌組，現在透過“多次”切日誌換命令將group 1,2,3狀態變為INACTIVE，  
讓oracle使用新建的group4,5,6，然後將小的日誌組group 1,2,3刪除              
                                
      
   SQL> alter system switch logfile;            
                                        
   SQL> alter system checkpoint;                                                        
                                                                                                                         
4.切換完成後，再次查詢當前的日誌組狀態，當前使用的日誌組是group 5  
                                                                 
   SQL> select group#, status from v$log;                                         
                                                                           
             
      GROUP# STATUS  
     -------- ------  
         1 INACTIVE  
         2 INACTIVE  
         3 INACTIVE  
         4 INACTIVE  
         5 CURRENT  
         6 INACTIVE  
    當redolog group 1,2,3狀態為active時，意味著不能刪除當前redo,直至用"alter system switch log file" ，"alter system checkpoint"   
命令將其切換為“inactive”狀態才能將其刪除  
                         
                                                    
5.刪除group 1,2,3日誌組      
 
   SQL> alter database drop logfile group 1;    
        Database altered.                                    
   SQL> alter database drop logfile group 2;    
        Database altered.                                    
   SQL> alter database drop logfile group 3;    
        Database altered.                                    
                                     
 
   SQL> select group#, bytes, status from v$log;  
 
      GROUP#     BYTES STATUS  
   --------- --------- ----------------  
           4  10485760 CURRENT  
           5  10485760 UNUSED  
           6  10485760 UNUSED              
 
 
     
6.  整個操作完成後，最好做一個backup  
 
7.在作業系統層面刪除原來的redolog  
      
[oracle@tora10 ~]$ cd /opt/oracle/oradata/test/  
[oracle@tora10 test]$ ll -al redo*  
-rw-r----- 1 oracle oinstall  524288512 Feb 23 17:15 redo01.log  
-rw-r----- 1 oracle oinstall  524288512 Feb 23 17:15 redo02.log  
-rw-r----- 1 oracle oinstall  524288512 Feb 23 17:15 redo03.log  
-rw-r----- 1 oracle oinstall 1073742336 Feb 23 17:16 redo04.log   
-rw-r----- 1 oracle oinstall 1073742336 Feb 23 17:16 redo05.log   
-rw-r----- 1 oracle oinstall 1073742336 Feb 23 17:17 redo06.log   
 
刪除小的redolog  
 
[oracle@tora10 test]$ rm -rf redo01.log   
[oracle@tora10 test]$ rm -rf redo02.log   
[oracle@tora10 test]$ rm -rf redo03.log                                              
                                                    
 
值得注意的是，加完新的redolog group 後，要監控下alert log 檢視新的redolog的切換頻率與原來情況相比較