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>
 


 
 

 


win7 oracle 冷恢复

Ghost了系统oracle 数据库肯定没了,重装。不过还好什么控制文件、表空间还在,(归档我给停了没用下次试试用归档备份还原)就像到了冷备份。报着试试看的态度,一开始以为冷备份怎么和以前的表空间相联...
  • lchen1983
  • lchen1983
  • 2012年07月26日 11:46
  • 1026

修改redo log files路径

如果可以关闭数据库,可以采用下述步骤。如果不能关闭数据库,只能采用新建redo log files的方式来实现修改该redo 的路径了。 一、shutdown the database 二、...
  • lwei_998
  • lwei_998
  • 2011年11月14日 12:29
  • 6144

DG环境下 redo和 standby redo的增加和删除--及相关遇到的问题

起因是alert.log 里经常报 Thread 1 cannot allocate new log, sequence 37255 Checkpoint not complete 相关原因,t...
  • hkyw000
  • hkyw000
  • 2015年11月18日 11:34
  • 656

ORACLE数据库ARCHIVELOG,NOARCHIVELOG模式转换简单总结,Redo Log(重做日志)Archive Log(归档日志)

ORACLE数据库ARCHIVELOG,NOARCHIVELOG模式转换简单总结  Oracle9i中有2种日志,一种称为 Redo Log(重做日志),另一种叫做Archive Log(归档...
  • lenovouser
  • lenovouser
  • 2016年10月27日 11:38
  • 810

Standby Redo Log 的设定原则、创建、删除、查看、归档位置

设定: 1.每个standby redo log file 至少要和primary database的redo log 一样大,为了方便管理,Oracle 建议主备库的redo log 设置成一样...
  • zhang123456456
  • zhang123456456
  • 2017年03月10日 23:20
  • 496

oracle学习笔记----在线修改redo.log文件的大小

1.查看当前日志组成员: SQL> select member from v$logfile; MEMBER ----------------------------------------...
  • zxlyx
  • zxlyx
  • 2016年06月21日 23:47
  • 2033

系统警告说REDO LOG Writer经常时间长 经常200多ms

redo log writer 繁忙 Redo Log writer (LGWR) The Redo log writer writes the redo buffer blocks from SGA...
  • S630730701
  • S630730701
  • 2016年09月23日 23:59
  • 836

oracle dg坏境主库redolog修改大小

dagaguard环境修改在线日志redolog大小
  • lihaiwenshu
  • lihaiwenshu
  • 2014年10月31日 11:05
  • 1114

oracle的redo log在各场景下的恢复

oracle的redo log非常重要,redo log损坏将导致数据库开法开启或数据丢失,针对redo log在各种场景下如何打开或恢复数据库,特别模拟测试说明: 各场景包括如下(共6个场景): ...
  • zengxuewen2045
  • zengxuewen2045
  • 2016年09月08日 09:20
  • 507

Oracle redo log 大小及组数的设置(在线修改日志组数目及组成员个数)

1.查看当前系统现有的redo log状况(组数/大小/名称/状态) desc">SYS@ORCL>desc v$log  Name                                  ...
  • demonson
  • demonson
  • 2014年09月17日 16:02
  • 981
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ORACLE 在线移动redo log路径、删除无效的redo log
举报原因:
原因补充:

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