Oracle redo日志管理

Posted by Harid七月 - 16 - 2014 Leave comments

今天发现数据库日志切换非常频繁:

alert

如上图所示,基本上5分钟左右就需要切换一次重做日志。于是,顺手检查了一下日志文件,执行:

1
SELECT a.group#, (a.bytes/1024/1024) AS MB, a.status , b.MEMBER FROM V$log a, V$logfile b WHERE a.group#=b.group#;

结果发现有6组日志文件其大小只有100MB!

sys@ora11g> col member format A50;
sys@ora11g> select a.group#, (a.bytes/1024/1024) as MB, a.status , b.MEMBER from V$log a, V$logfile b where a.group#=b.group#;

GROUP# MB STATUS MEMBER
———- ———- —————- ————————————————–
1 100 INACTIVE /opt/oracle/oradata/ora11g/redo01.log
2 100 INACTIVE /opt/oracle/oradata/ora11g/redo02.log
3 100 INACTIVE /opt/oracle/oradata/ora11g/redo03.log
4 100 INACTIVE /opt/oracle/oradata/ora11g/redo04.log
5 100 INACTIVE /opt/oracle/oradata/ora11g/redo05.log
6 100 INACTIVE /opt/oracle/oradata/ora11g/redo06.log
7 1024 CURRENT /opt/oracle/oradata/ora11g/redo07.log
8 1024 INACTIVE /opt/oracle/oradata/ora11g/redo08.log
9 1024 INACTIVE /opt/oracle/oradata/ora11g/redo09.log

9 rows selected.

Elapsed: 00:00:00.00

日志文件都是在/opt/oracle/oradata/ora11g目录下面,这个目录虽然是用挂载的磁阵LUN做的文件系统,但是9组日志有点多,而且都是在同一个目录下面,意义不大。那么果断删除1~6组日志:

1
2
3
ALTER DATABASE DROP logfile GROUP 1;
ALTER DATABASE DROP logfile GROUP 2;
...

logfile

由于1~6组日志当前都是“INACTIVE”状态,因此可以直接drop,若是非“INACTIVE”状态,需要先执行:

1
2
ALTER system switch logfile;
ALTER system checkpoint;

通过不断地切换,将待删除的日志文件状态变为“INACTIVE”,这时就可以删除了。执行删除操作后,手动删除磁盘上的重做日志文件。

同时,添加两组大小为1024M的日志文件:

sys@ora11g> alter database add logfile group 1 ‘/opt/oracle/oradata/ora11g/redo01.log’ size 1024M ;

Database altered.

Elapsed: 00:00:17.66
sys@ora11g> alter database add logfile group 2 ‘/opt/oracle/oradata/ora11g/redo02.log’ size 1024M ;

Database altered.

Elapsed: 00:00:18.99
sys@ora11g> select a.group#, (a.bytes/1024/1024) as MB, a.status , b.MEMBER from V$log a, V$logfile b where a.group#=b.group#;

GROUP# MB STATUS MEMBER
———- ———- —————- ————————————————–
1 1024 UNUSED /opt/oracle/oradata/ora11g/redo01.log
2 1024 UNUSED /opt/oracle/oradata/ora11g/redo02.log
7 1024 CURRENT /opt/oracle/oradata/ora11g/redo07.log
8 1024 INACTIVE /opt/oracle/oradata/ora11g/redo08.log
9 1024 INACTIVE /opt/oracle/oradata/ora11g/redo09.log

Elapsed: 00:00:00.01

另外,还有一些与redo相关的可能会用到的操作。如:

清空redo file:

1
ALTER DATABASE clear logfile GROUP 1;

这条命令清除 group 1的数据文件,这个时候清除前会进行自动归档。若需要清除未归档的redo log,则执行:

1
ALTER DATABASE clear unarchieved logfile GROUP 1;

   声明:本文采用 BY-NC-SA 协议进行授权 | 星期九
   原创文章转载请注明:转自《Oracle redo日志管理


分享按钮