ORACLE 在线移动redo log路径、删除无效的redo log

最新推荐文章于 2021-04-16 00:19:30 发布

林震南

最新推荐文章于 2021-04-16 00:19:30 发布

阅读量1.1w

点赞数

分类专栏： oracle developer oracle problem

本文链接：https://blog.csdn.net/csdnhsh/article/details/95783165

版权

oracle problem 同时被 2 个专栏收录

61 篇文章 1 订阅

订阅专栏

oracle developer

15 篇文章 0 订阅

订阅专栏

移动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文件地址：
         [oracle@hch_test_dbm1_121_63 ~]$ mv /oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_3_dfkstobl_.log /home/oradata/powerdes/redo03.log
         [oracle@hch_test_dbm1_121_63 ~]$ mv /oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_2_dfksto61_.log /home/oradata/powerdes/redo02.log
         [oracle@hch_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>