维护在线redo log Oracle Database - Enterprise Edition - Version 11.2.0.4 to 11.2.0.4 1. 首先查看当前redo log的大小: [oracle@oracle ~]$ sqlplus / as sysdba Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production SQL> select group#, bytes, status from v$log; GROUP# BYTES STATUS ---------- ---------- ---------------- 1 52428800 CURRENT 2 52428800 INACTIVE 3 52428800 INACTIVE 当前redo log 是50M，现在扩展到100M 2. 查看所有的在线日子组成员 SQL> select group#, member from v$logfile; GROUP# MEMBER ---------- -------------------------------------------------- 3 /oracle/oradata/orcl/redo03.log 2 /oracle/oradata/orcl/redo02.log 1 /oracle/oradata/orcl/redo01.log 3. 新增加group# 4/5/6大小为100M的redolog SQL> alter database add logfile group 4 ‘/oracle/oradata/orcl/log4PROD.dbf‘ size 100M; SQL> alter database add logfile group 5 ‘/oracle/oradata/orcl/log5PROD.dbf‘ size 100M; SQL> alter database add logfile group 6 ‘/oracle/oradata/orcl/log6PROD.dbf‘ size 100M; 4. 查询当前所有redo log的状态: SQL> select group#, status from v$log; GROUP# STATUS ---------- ---------------- 1 CURRENT 2 INACTIVE 3 INACTIVE 4 UNUSED 5 UNUSED 6 UNUSED 当前redolog状态，group 1是当前的在线日志组，group2、3为inactive，我们要删除的是50M的group 1、2、3 group 1 是当前在线日志组，不可以删除，目前可以删除inactive的group 2、3 SQL> alter database drop logfile group 2; Database altered. SQL> alter database drop logfile group 3; Database altered. 5. 多次switch log切换日志,直至group 1为inactive状态，: SQL> alter system checkpoint; System altered. SQL> alter system switch logfile; System altered. SQL> select group#, status from v$log; GROUP# STATUS ---------- ---------------- 1 INACTIVE 4 UNUSED 5 UNUSED 6 UNUSED SQL> alter database drop logfile group 1; Database altered. 注:如果checkpoint，还是不能切换到inactive，那就做几次全局checkpoint SQL> alter system checkpoint global; System altered 6.再次查看在线日志所有状态: SQL> select group#, bytes, status from v$log; GROUP# BYTES STATUS ---------- ---------- ---------------- 4 104857600 INACTIVE 5 104857600 CURRENT 6 104857600 UNUSED 9. 操作系统层面删除旧的redolog [oracle@oracle orcl]$rm /oracle/oradata/orcl/redo01.log [oracle@oracle orcl]$rm /oracle/oradata/orcl/redo02.log [oracle@oracle orcl]$rm /oracle/oradata/orcl/redo03.log 10.删除redo log 最好做一次数据库的备份，维护reodlog的时候最好在业务低峰的时候操作
本文出自 “O Record” 博客，请务必保留此出处http://evils798.blog.51cto.com/8983296/1420916