Oracle 重做日志調(diào)整 SQL語句
2021-09-13 10:21:23

Oracle 數(shù)據(jù)庫安裝之后，重做日志默認(rèn)為 3組，如果需要新增刪除，可以通過以下方式！

查詢：

set line222
col member for a60
select a.group#,b.member member,a.bytes/1024/1024 "size(M)" from v$log a,v$logfile b where a.group#=b.group#;

新增：

單機數(shù)據(jù)庫：

alter database add logfile group 4 '/oradata/orcl/redo04.log' size 50M;
alter database add logfile group 5 '/oradata/orcl/redo05.log' size 50M;
alter database add logfile group 16 '/oradata/orcl/redo16.log' size 1024M;
alter database add logfile group 17 '/oradata/orcl/redo17.log' size 1024M;
alter database add logfile group 18 '/oradata/orcl/redo18.log' size 1024M;

RAC集群數(shù)據(jù)庫：

alter database add logfile thread 1 
group 4 '/oradata/orcl/redo04.log' size 50M,
group 5 '/oradata/orcl/redo05.log' size 50M,
group 6 '/oradata/orcl/redo06.log' size 50M;

alter database add logfile thread 2
group 7 '/oradata/orcl/redo07.log' size 50M,
group 8 '/oradata/orcl/redo08.log' size 50M,
group 9 '/oradata/orcl/redo09.log' size 50M;

刪除：

alter database drop logfile group 16; 
alter database drop logfile group 17; 
alter database drop logfile group 18; 

???? 注意：如果當(dāng)前要刪除的 logfile 狀態(tài)不是 INACTIVE，那么刪除會報錯，需要執(zhí)行 ALTER SYSTEM SWITCH LOGFILE; 來切換日志，直到狀態(tài)為 INACTIVE 才可以刪除！

本次分享到此結(jié)束啦~

如果覺得文章對你有幫助，點贊、收藏、關(guān)注、評論，一鍵四連支持，你的支持就是我創(chuàng)作最大的動力。

?? 技術(shù)交流可以 關(guān)注公眾號：Lucifer三思而后行 ??