Oracle redo日志管理

Posted by Harid2014 - Jul - 16 留个言

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

alert

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

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组日志:

alter database drop logfile group 1;
alter database drop logfile group 2;
...

logfile

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

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:

alter database clear logfile group 1;

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

alter database clear unarchieved logfile group 1;

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

No comments yet.
94 + 90 =  (required)
comment_ad

 NOTICE1: You should type some Chinese word (like “你好”) in your comment to pass the spam-check, thanks for your patience!

 NOTICE2: 请申请gravatar头像(http://en.gravatar.com),木有头像的会显示为“小怪物”头像,将难以通过审核!

分享按钮