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

原创 2017年04月04日 23:36:12

 

 

移动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>
 


 
 

 


版权声明:本文为博主原创文章,未经博主允许不得转载。 举报

相关文章推荐

Oracle 的Redo Log

Oracle 的Redo 机制DB的一个重要机制,理解这个机制对DBA来说也是非常重要,之前的Blog里也林林散散的写了一些,前些日子看老白日记里也有说明,所以结合老白日记里的内容,对oracle 的...

Oracle redo log recovery

Redolog状态分为unused,inactive,active和current几种状态.其中前2种状态的文件坏掉,不会影响数据库已经提交的数据. 当active和current的redo log出...

我是如何成为一名python大咖的?

人生苦短,都说必须python,那么我分享下我是如何从小白成为Python资深开发者的吧。2014年我大学刚毕业..

Redo Log

redo log的concept手册上的内容 Redo entries record data that you can use to reconstruct all changes madet...

redo log

redo log的文件有redo records组成,一个redo record由一组change vectors组成。改变xiangl

redo log -- MTTR

Recovery Performance Tuning: Fast-Start Fault RecoveryThis section describes instance recovery, and ...

有关 Oracle redo log

Redo的内容 Oracle通过Redo来实现快速提交,一方面是因为Redo Log File可以连续、顺序地快速写出,另一个方面也和Redo记录的精简内容有关。 两个概念: 改变向...

Overview of the Online Redo Log --- 在线重做日志概述

对于 oracle 恢复来说最重要的数据结构莫过于online redo log(在线重做日志), 在线重做日志包含至少两个预先分配好的,用来存储数据库变化的文件。在线重做日志记录着数据文件中的变化。...

ORACLE-基础五(online redo log)

Online Redo Log Files Online Redo log files have the following characteristics: Record all changes m...

调整redo log 日志大小

最近发现数据的库存在日志频繁切换,查看了才发现redo log日志太小,进行在线调整。 1.查看当前日志组成员 SQL> select member from v$logfile; MEMBER --...

oracle 11g归档日志研究_3

记录头(record head)格式: typedef struct rh0 { uint32_t recordlen; uint8_t VLD; //直接决定整个re...
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)