首页 > 代码库 > ORACLE 在线移动redo log路径、删除无效的redo log
ORACLE 在线移动redo log路径、删除无效的redo log
移动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路径、删除无效的redo log
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。