环境：ORACLE 11.2.0.4.0
系统: Oracle Linux Server release 6.5

需求：数据库日志查看数据库redo log，建议每个实例的redo不少于6组，每组日志成员个数不限，每个日志文件不少于500MB；
知识点：
1、每个实例必须至少有两个日志组；
2、当一个组处于ACTIVE或者CURRENT的状态时不可删除；
3、删除日志组的操作只对数据库进行更改，操作系统的文件尚未删除，需要手工删除；
4、当删除时适用DROP LOGFILE GROUP N语句时，此时GROUP N内的所有成员都将被删除。

查询当前日志文件大小：
SQL> select group#,thread#,status,bytes/1024/1024 MB from v$log;

GROUP# THREAD# STATUS MB
---------- ---------- ---------------- ----------
11 1 ACTIVE 300
12 1 ACTIVE 300
13 1 ACTIVE 300
14 1 ACTIVE 300
15 1 CURRENT 300
16 1 INACTIVE 300
21 2 CURRENT 300
22 2 INACTIVE 300
23 2 ACTIVE 300
24 2 ACTIVE 300
25 2 ACTIVE 300
26 2 ACTIVE 300

12 rows selected.

添加新日志组每个数据文件设置500MB：
alter database add logfile thread 1 group 1('+REDODG/hlwdsj/onlinelog/redo1a.log','+REDODG/hlwdsj/onlinelog/redo1b.log') size 500M;
alter database add logfile thread 1 group 2('+REDODG/hlwdsj/onlinelog/redo2a.log','+REDODG/hlwdsj/onlinelog/redo2b.log') size 500M;
alter database add logfile thread 1 group 3('+REDODG/hlwdsj/onlinelog/redo3a.log','+REDODG/hlwdsj/onlinelog/redo3b.log') size 500M;
alter database add logfile thread 1 group 4('+REDODG/hlwdsj/onlinelog/redo4a.log','+REDODG/hlwdsj/onlinelog/redo4b.log') size 500M;
alter database add logfile thread 1 group 5('+REDODG/hlwdsj/onlinelog/redo5a.log','+REDODG/hlwdsj/onlinelog/redo5b.log') size 500M;
alter database add logfile thread 1 group 6('+REDODG/hlwdsj/onlinelog/redo6a.log','+REDODG/hlwdsj/onlinelog/redo6b.log') size 500M;
alter database add logfile thread 2 group 7('+REDODG/hlwdsj/onlinelog/redo7a.log','+REDODG/hlwdsj/onlinelog/redo7b.log') size 500M;
alter database add logfile thread 2 group 8('+REDODG/hlwdsj/onlinelog/redo8a.log','+REDODG/hlwdsj/onlinelog/redo8b.log') size 500M;
alter database add logfile thread 2 group 9('+REDODG/hlwdsj/onlinelog/redo9a.log','+REDODG/hlwdsj/onlinelog/redo9b.log') size 500M;
alter database add logfile thread 2 group 10('+REDODG/hlwdsj/onlinelog/redo10a.log','+REDODG/hlwdsj/onlinelog/redo10b.log') size 500M;
alter database add logfile thread 2 group 11('+REDODG/hlwdsj/onlinelog/redo11a.log','+REDODG/hlwdsj/onlinelog/redo11b.log') size 500M;
alter database add logfile thread 2 group 12('+REDODG/hlwdsj/onlinelog/redo12a.log','+REDODG/hlwdsj/onlinelog/redo12b.log') size 500M;

查询添加后的状态
select group#,thread#,bytes/1024/1024 MB,archived,status,members from v$log;
select * from v$logfile order by 1;

切换日志组，删除原有日志组
alter system switch logfile;
select group#,thread#,bytes/1024/1024,archived,status,members from v$log;

alter database drop logfile group 11;
alter database drop logfile group 12;
alter database drop logfile group 13;
alter database drop logfile group 14;
alter database drop logfile group 15;
alter database drop logfile group 16;
alter database drop logfile group 21;
alter database drop logfile group 22;
alter database drop logfile group 23;
alter database drop logfile group 24;
alter database drop logfile group 25;
alter database drop logfile group 26;

删除原有的日志文件
$asmcmd
thread1:
rm +REDODG/HLWDSJ/ONLINELOG/group_11.276.913480051
rm +REDODG/HLWDSJ/ONLINELOG/group_11.277.913480051
rm +REDODG/HLWDSJ/ONLINELOG/group_12.278.913480157
rm +REDODG/HLWDSJ/ONLINELOG/group_12.279.913480157
rm +REDODG/HLWDSJ/ONLINELOG/group_13.280.913480159
rm +REDODG/HLWDSJ/ONLINELOG/group_13.281.913480159
rm +REDODG/HLWDSJ/ONLINELOG/group_14.282.913480159
rm +REDODG/HLWDSJ/ONLINELOG/group_14.283.913480159
rm +REDODG/HLWDSJ/ONLINELOG/group_15.284.913480161
rm +REDODG/HLWDSJ/ONLINELOG/group_15.285.913480161
rm +REDODG/HLWDSJ/ONLINELOG/group_16.286.913480161
rm +REDODG/HLWDSJ/ONLINELOG/group_16.287.913480163

thread2
rm +REDODG/HLWDSJ/ONLINELOG/group_21.288.913480167
rm +REDODG/HLWDSJ/ONLINELOG/group_21.289.913480167
rm +REDODG/HLWDSJ/ONLINELOG/group_22.290.913480169
rm +REDODG/HLWDSJ/ONLINELOG/group_22.291.913480169
rm +REDODG/HLWDSJ/ONLINELOG/group_23.292.913480169
rm +REDODG/HLWDSJ/ONLINELOG/group_23.293.913480169
rm +REDODG/HLWDSJ/ONLINELOG/group_24.294.913480171
rm +REDODG/HLWDSJ/ONLINELOG/group_24.295.913480171
rm +REDODG/HLWDSJ/ONLINELOG/group_25.296.913480171
rm +REDODG/HLWDSJ/ONLINELOG/group_25.297.913480171
rm +REDODG/HLWDSJ/ONLINELOG/group_26.298.913480173
rm +REDODG/HLWDSJ/ONLINELOG/group_26.299.913480173

至此完成，添加日志组时有可能报错，提示日志文件已存在，这时说明日志组已删除，系统级别的日志文件没有删除，需要手工删除，注意先后顺序
1、切换日志组；
2、删除INACTIVE状态日志组；
3、删除日志组物理文件。

OK,完成。

ITPub博客

【Oracle】 Oracle11gR2 扩展数据库联机日志文件

最新文章