MySQL通过本文主要向大家介绍了oracle,删除无效的redolog等相关知识,希望本文的分享对您有所帮助
移动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
-------------------------------------------------------