ORACLE 線上移動redo log路徑、刪除無效的redo log

標籤：logfile   ase   ash   automatic   clear   dism   status   限制   redo   

 

 

移動redo檔案路徑，預設的redo log檔案的路徑和archivelog的路徑一樣，在閃回區，因為閃回區有大小限制，所以redo log和archive log特別是archive log越來越多後，會撐爆閃回區。規範起見，需要將redo log以及archive log放到單獨的路徑地區裡面去。

 

1，查看現有的redo檔案路徑

查看現有的redo檔案路徑，看到預設的在閃回區路徑下面：

         SQL> select member from v$logfile;          MEMBER         --------------------------------------------------------------------------------         /oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_3_dfkstobl_.log          /oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_2_dfksto61_.log          /oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_1_dfksto1q_.log          /home/oradata/powerdes/redo_dg_021.log         /home/oradata/powerdes/redo_dg_022.log          MEMBER         --------------------------------------------------------------------------------         /home/oradata/powerdes/redo_dg_023.log          6 rows selected.          SQL>

 

2，關閉資料庫，copy redo檔案到新地址   

關閉資料庫後，copy檔案：

         SQL> shutdown immediate;         ORA-01109: database not open           Database dismounted.         ORACLE instance shut down.         SQL> cp檔案地址：         [[email protected]_test_dbm1_121_63 ~]$ mv /oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_3_dfkstobl_.log /home/oradata/powerdes/redo03.log         [[email protected]_test_dbm1_121_63 ~]$ mv /oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_2_dfksto61_.log /home/oradata/powerdes/redo02.log         [[email protected]_test_dbm1_121_63 ~]$ mv /oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_1_dfksto1q_.log /home/oradata/powerdes/redo01.log                 cp  /home/oradata/powerdes/redo03.log /oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_3_dfkstobl_.log         cp  /home/oradata/powerdes/redo02.log /oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_2_dfkstobl_.log         cp  /home/oradata/powerdes/redo01.log /oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_1_dfkstobl_.log 

 

3,  資料庫啟動mount，執行rename切換redo日誌路徑

執行命令：         alter database rename file ‘/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_3_dfkstobl_.log‘ to ‘/home/oradata/powerdes/redo03.log‘;         alter database rename file ‘/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_2_dfksto61_.log‘ to ‘/home/oradata/powerdes/redo02.log‘;         alter database rename file ‘/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_1_dfksto1q_.log‘ to ‘/home/oradata/powerdes/redo01.log‘;                 執行報錯         SQL> alter database rename file ‘/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_3_dfkstobl_.log‘ to ‘/home/oradata/powerdes/redo03.log‘;         alter database rename file ‘/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_3_dfkstobl_.log‘ to ‘/home/oradata/powerdes/redo03.log‘         *         ERROR at line 1:         ORA-01511: error in renaming log/data files         ORA-01275: Operation RENAME is not allowed if standby file management is         automatic.

         命令執行報錯，提示說standbyfile maangement參數為自動，自動情況下不允許修改，好吧，聽它的，修改成手動的，這樣我們就可以移動它的目錄位址了

         SQL> show parameter standby;          NAME                                        TYPE       VALUE         ------------------------------------ ----------- ------------------------------         standby_archive_dest                   string     ?/dbs/arch         standby_file_management         string     AUTO         SQL> alter system set standby_file_management = MANUAL;          System altered.          SQL>                        SQL> alter database rename file ‘/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_3_dfkstobl_.log‘ to ‘/home/oradata/powerdes/redo03.log‘;          Database altered.          SQL>

 

執行第一個成功，但是執行第二個報錯，記錄如下：

        SQL> alter database rename file ‘/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_2_dfkstobl_.log‘ to ‘/home/oradata/powerdes/redo02.log‘;         alter database rename file ‘/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_2_dfkstobl_.log‘ to ‘/home/oradata/powerdes/redo02.log‘         *         ERROR at line 1:         ORA-01511: error in renaming log/data files         ORA-01516: nonexistent log file, data file, or temporary file         "/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_2_dfkstobl_.         log"           SQL>                 看提示，這個檔案non existstent log file，看是檔案不存在，check下，修改成正確的檔案名稱，再次執行。                 再次執行         SQL> alter database rename file ‘/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_2_dfksto61_.log‘ to ‘/home/oradata/powerdes/redo02.log‘;          Database altered.          SQL> alter database rename file ‘/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_1_dfksto1q_.log‘ to ‘/home/oradata/powerdes/redo01.log‘;          Database altered.          SQL>

 

4，檢查查看當前路徑：

        SQL> show parameter db_file_name_convert;          NAME                                        TYPE       VALUE         ------------------------------------ ----------- ------------------------------         db_file_name_convert                   string         SQL>         SQL>  select member from v$logfile;          MEMBER         --------------------------------------------------------------------------------         /home/oradata/powerdes/redo03.log         /home/oradata/powerdes/redo02.log         /home/oradata/powerdes/redo01.log         /home/oradata/powerdes/redo_dg_021.log         /home/oradata/powerdes/redo_dg_022.log         /home/oradata/powerdes/redo_dg_023.log          6 rows selected.          SQL> select name from v$datafile;          NAME         --------------------------------------------------------------------------------         /home/oradata/powerdes/system01.dbf         /home/oradata/powerdes/sysaux01.dbf         /home/oradata/powerdes/undotbs01.dbf         /home/oradata/powerdes/users01.dbf         /home/oradata/powerdes/powerdesk01.dbf         /home/oradata/powerdes/plas01.dbf         /home/oradata/powerdes/pl01.dbf         /home/oradata/powerdes/help01.dbf         /home/oradata/powerdes/adobelc01.dbf         /home/oradata/powerdes/sms01.dbf         /home/oradata/powerdes/plcrm01.dbf          NAME         --------------------------------------------------------------------------------         /home/oradata/powerdes/powerdesk02.dbf         /home/oradata/powerdes/datagm01.dbf         /home/oradata/powerdes/plimp01.DBF         /home/oradata/powerdes/dwetl01.DBF         /home/oradata/powerdes/dw02.DBF         /home/oradata/powerdes/timdba01.DBF          17 rows selected.          SQL>

         重啟查看新的路徑是否生效

         SQL> shutdown immediate;         ORA-01109: database not open           Database dismounted.         ORACLE instance shut down.         SQL> startup mount;         ORACLE instance started.          Total System Global Area 2.6991E+10 bytes         Fixed Size              2213976 bytes         Variable Size              1.9059E+10 bytes         Database Buffers    7784628224 bytes         Redo Buffers               145174528 bytes         Database mounted.         SQL>         SQL> select member from v$logfile;          MEMBER         --------------------------------------------------------------------------------         /home/oradata/powerdes/redo03.log         /home/oradata/powerdes/redo02.log         /home/oradata/powerdes/redo01.log         /home/oradata/powerdes/redo_dg_021.log         /home/oradata/powerdes/redo_dg_022.log         /home/oradata/powerdes/redo_dg_023.log          6 rows selected.          SQL>

 

 

5，清除多餘的redo日誌組

查看現在的記錄檔

        SQL> select * from v$log;                    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE          MEMBERS ARC         ---------- ---------- ---------- ---------- ---------- ---------- ---         STATUS              FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME         ---------------- ------------- ------------ ------------ ------------                    1         1            139   52428800     512              1 YES         CURRENT            1.4120E+10 31-MAR-17      2.8147E+14                     3         1              0   52428800     512              1 YES         CLEARING            1.4120E+10 31-MAR-17      2.8147E+14                     2         1              0   52428800     512              1 YES         UNUSED                1.4120E+10 31-MAR-17      1.4120E+10 31-MAR-17           SQL> 

       

        

         查看現在的redo記錄檔

         SQL> select * from v$logfile;                    GROUP# STATUS  TYPE         ---------- ------- -------         MEMBER         --------------------------------------------------------------------------------         IS_         ---                    3        ONLINE         /home/oradata/powerdes/redo01.log         NO                     2        ONLINE         /home/oradata/powerdes/redo02.log         NO                    GROUP# STATUS  TYPE         ---------- ------- -------         MEMBER         --------------------------------------------------------------------------------         IS_         ---                     1        ONLINE         /home/oradata/powerdes/redo03.log         NO                     4        STANDBY         /oracle/app/oracle/fast_recovery_area/POWERDES_S2/onlinelog/o1_mf_4_dfw3zf9v_.lo                    GROUP# STATUS  TYPE         ---------- ------- -------         MEMBER         --------------------------------------------------------------------------------         IS_         ---         g         YES                     5        STANDBY         /oracle/app/oracle/fast_recovery_area/POWERDES_S2/onlinelog/o1_mf_5_dfw3zg5r_.lo         g         YES                    GROUP# STATUS  TYPE         ---------- ------- -------         MEMBER         --------------------------------------------------------------------------------         IS_         ---                     6        STANDBY         /oracle/app/oracle/fast_recovery_area/POWERDES_S2/onlinelog/o1_mf_6_dfw3zh1q_.lo         g         YES           6 rows selected.          SQL>

 

保守一些，select * from v$log;裡面查不到的log檔案，都可以刪除掉；甚至徹底些不在CURRENT裡面的，也可以刪除掉。

        SQL> alter database drop logfile group 5;          Database altered.          SQL> alter database drop logfile group； 6;          Database altered.          SQL> alter database drop logfile group 4;          Database altered.          SQL>                 check，已經刪除了多餘的redo檔案         SQL> select member from v$logfile;          MEMBER         --------------------------------------------------------------------------------         /home/oradata/powerdes/redo01.log         /home/oradata/powerdes/redo02.log         /home/oradata/powerdes/redo03.log          SQL> 

 
 

 

ORACLE 線上移動redo log路徑、刪除無效的redo log