Oracle 11g RAC 添加删除日志组(修改日志组大小)
1.查看日志文件位置
col status for a10
col type for a10
col member for a50
select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_REC
---------- ---------- ---------- -------------------------------------------------- ------
2 ONLINE +DATA/prod/onlinelog/group_2.262.951055519 NO
2 ONLINE +FRC/prod/onlinelog/group_2.258.951055519 YES
1 ONLINE +DATA/prod/onlinelog/group_1.261.951055517 NO
1 ONLINE +FRC/prod/onlinelog/group_1.257.951055517 YES
3 ONLINE +DATA/prod/onlinelog/group_3.265.951055699 NO
3 ONLINE +FRC/prod/onlinelog/group_3.259.951055699 YES
4 ONLINE +DATA/prod/onlinelog/group_4.266.951055699 NO
4 ONLINE +FRC/prod/onlinelog/group_4.260.951055701 YES
2.根据现有日志组信息,添加新的日志组
alter database add logfile thread 1 group 5 ('+DATA/prod/onlinelog/group5','+FRC/prod/onlinelog/group5') size 200M;
alter database add logfile thread 2 group 6 ('+DATA/prod/onlinelog/group6','+FRC/prod/onlinelog/group6') size 200M;
alter database add logfile thread 1 group 7 ('+DATA/prod/onlinelog/group7','+FRC/prod/onlinelog/group7') size 200M;
alter database add logfile thread 2 group 8 ('+DATA/prod/onlinelog/group8','+FRC/prod/onlinelog/group8') size 200M;
alter database add logfile thread 2 group 8 ('+DATA/prod/onlinelog/group8','+FRC/prod/onlinelog/group8') size 200M;
3.查看日志组详细信息
select thread#,group#,archived,status from v$log;
4.切换日志
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
select thread#,group#,archived,status from v$log;
6.再次查看日志文件具体位置及信息
select * from v$logfile;
select thread#,group#,archived,status from v$log;
7.删除原有日志组
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
8.查看删除后的日志组文件信息
select * from v$logfile;
select * from v$logfile;
select thread#,group#,archived,status from v$log;
9.切换日志进行测试
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
10.查看切换后新添加日志组文件信息
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_REC
---------- ---------- ---------- -------------------------------------------------- ------
5 ONLINE +DATA/prod/onlinelog/group5 NO
5 ONLINE +FRC/prod/onlinelog/group5 NO
6 ONLINE +DATA/prod/onlinelog/group6 NO
6 ONLINE +FRC/prod/onlinelog/group6 NO
7 ONLINE +DATA/prod/onlinelog/group7 NO
7 ONLINE +FRC/prod/onlinelog/group7 NO
8 ONLINE +DATA/prod/onlinelog/group8 NO
8 ONLINE +FRC/prod/onlinelog/group8 NO
SQL> select thread#,group#,archived,status from v$log;
THREAD# GROUP# ARCHIV STATUS
---------- ---------- ------ ----------
1 5 NO CURRENT
2 6 NO CURRENT
1 7 YES INACTIVE
2 8 YES INACTIVE