oracle 在線調(diào)整redo

由于數(shù)據(jù)庫(kù)目前的redo配置為1G，從日志上看，redo切換過于頻繁，而且不能及時(shí)歸檔。這個(gè)表象會(huì)很大程度影響數(shù)據(jù)庫(kù)性能，所以進(jìn)行如下調(diào)優(yōu)

1：sqlplus / as sysdba

添加如下8組redo

 alter database add logfile thread 1 group 9 ('+DATA1/xxzxdb/NLINELOG/group9a','+DATA1/xxzxdb/NLINELOG/group9b') size 2000M;

alter database add logfile thread 2 group 10 ('+DATA1/xxzxdb/NLINELOG/group10a','+DATA1/xxzxdb/NLINELOG/group10b') size 2000M;

alter database add logfile thread 1 group 11 ('+DATA1/xxzxdb/NLINELOG/group11a','+DATA1/xxzxdb/NLINELOG/group11b') size 2000M;

alter database add logfile thread 2 group 12 ('+DATA1/xxzxdb/NLINELOG/group12a','+DATA1/xxzxdb/NLINELOG/group12b') size 2000M;

alter database add logfile thread 1 group 13 ('+DATA1/xxzxdb/NLINELOG/group13a','+DATA1/xxzxdb/NLINELOG/group13b') size 2000M;

alter database add logfile thread 2 group 14 ('+DATA1/xxzxdb/NLINELOG/group14a','+DATA1/xxzxdb/NLINELOG/group14b') size 2000M;

alter database add logfile thread 1 group 15 ('+DATA1/xxzxdb/NLINELOG/group15a','+DATA1/xxzxdb/NLINELOG/group15b') size 2000M;

alter database add logfile thread 2 group 16 ('+DATA1/xxzxdb/NLINELOG/group16a','+DATA1/xxzxdb/NLINELOG/group16b') size 2000M;

2：添加之后，執(zhí)行alter system switch logfile;--多執(zhí)行幾次。切換redo ，一直切換到新加的9這里來，讓9變成current

select a.members,

       a.thread#,

       a.status,

       a.bytes / 1024 / 1024,

       b.type,

       b.member,

       b.group#

  from v$log a, v$logfile b

 where a.group# = b.group#;

3：當(dāng)redo group 1-8 的狀態(tài)變成“INACTIVE”就可以進(jìn)行刪除

SQL>  alter database drop logfile group 1; 

Database altered

4：當(dāng)1-8組日志，還是處于active狀態(tài)，沒有歸檔完成，這個(gè)時(shí)候是不能刪除的， 

執(zhí)行  sql>alter system checkpoint; 

這樣就會(huì)將1-8組的日志逐個(gè)刪除即可。