Oracle DataGuard環境主備庫日誌組數和大小調整
Oracle DataGuard主備庫日誌組數和大小調整：
檢查當前主備的redo日誌檔案和standby日誌檔案：
redo log:
select l.group#,l.bytes/1024/1024 mb,lf.member from v$log l,v$logfile lf where l.group#=lf.group# order by group#;
select group#,status from v$log;
standby log:
select sl.group#,sl.bytes/1024/1024 mb,lf.member from v$standby_log sl,v$logfile lf where sl.group#=lf.group# order by group#;
select group#,status from v$standby_log;
一、增減日誌檔案：
1、在主庫和備庫都設定standby_file_management為manual
alter system set standby_file_management='manual';
2、備庫上停止恢復：
alter database recover managed standby database cancel;
3、在主庫和備庫上增加或者減少online和standby日誌：
增加：
alter database add logfile group 2 ('/u02/app/oracle/oradata/ncf/redo02a.log','/u02/app/oracle/oradata/ncf/redo02b.log') size 200m;
alter database add standby logfile group 13 ('/u02/app/oracle/oradata/ncf/standby13_1.log','/u02/app/oracle/oradata/ncf/standby13_2.log') size 200m;
減少：
如果顯示狀態為clearing，則先clear，再刪除日誌：
alter database clear logfile group 8;
alter database drop logfile group 8;
host rm -rf /u02/app/oracle/oradata/ncf/redo08a.log
host rm -rf /u02/app/oracle/oradata/ncf/redo08b.log
4、在主庫和備庫設定standby_file_management為auto
alter system set standby_file_management='auto';
5、備庫上啟動同步：
alter database recover managed standby database using current logfile disconnect;
6、檢查狀態：
主庫多次執行：alter system switch logfile;
主庫：select max(sequence#) from v$archived_log;
備庫：select max(sequence#) from v$archived_log where applied='YES';
SELECT name,value,time_computed FROM V$DATAGUARD_STATS WHERE NAME like '%lag%';
二、修改日誌檔案大小：
1、在主庫和備庫都設定standby_file_management為manual
alter system set standby_file_management='manual';
2、備庫上停止恢復：
alter database recover managed standby database cancel;
3、主庫操作：
處理主庫的redo log：
select l.group#,l.bytes/1024/1024 mb,lf.member from v$log l,v$logfile lf where l.group#=lf.group# order by group#;
GROUP# MB MEMBER
---------- ---------- --------------------------------------------------
2 50 /u02/app/oracle/oradata/ncf/redo02a.log
2 50 /u02/app/oracle/oradata/ncf/redo02b.log
3 50 /u02/app/oracle/oradata/ncf/redo03a.log
3 50 /u02/app/oracle/oradata/ncf/redo03b.log
4 50 /u02/app/oracle/oradata/ncf/redo04a.log
4 50 /u02/app/oracle/oradata/ncf/redo04b.log
select group#,status from v$log;
GROUP# STATUS
---------- ----------------
2 CURRENT
3 INACTIVE
4 INACTIVE
alter database drop logfile group 4;
host rm -rf /u02/app/oracle/oradata/ncf/redo04a.log
host rm -rf /u02/app/oracle/oradata/ncf/redo04b.log
alter database add logfile group 4 ('/u02/app/oracle/oradata/ncf/redo04a.log','/u02/app/oracle/oradata/ncf/redo04b.log') size 100m;
alter database drop logfile group 3;
host rm -rf /u02/app/oracle/oradata/ncf/redo03a.log
host rm -rf /u02/app/oracle/oradata/ncf/redo03b.log
alter database add logfile group 3 ('/u02/app/oracle/oradata/ncf/redo03a.log','/u02/app/oracle/oradata/ncf/redo03b.log') size 100m;
alter system switch logfile;
select group#,status from v$log;
GROUP# STATUS
---------- ----------------
2 ACTIVE
3 CURRENT
4 UNUSED
alter system checkpoint;
select group#,status from v$log;
GROUP# STATUS
---------- ----------------
2 INACTIVE
3 CURRENT
4 UNUSED
alter database drop logfile group 2;
host rm -rf /u02/app/oracle/oradata/ncf/redo02a.log
host rm -rf /u02/app/oracle/oradata/ncf/redo02b.log
alter database add logfile group 2 ('/u02/app/oracle/oradata/ncf/redo02a.log','/u02/app/oracle/oradata/ncf/redo02b.log') size 100m;
處理主庫的standby log：
SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ----------
10 UNASSIGNED
11 UNASSIGNED
12 UNASSIGNED
13 UNASSIGNED
alter database drop logfile group 10;
alter database drop logfile group 11;
alter database drop logfile group 12;
alter database drop logfile group 13;
host rm -rf /u02/app/oracle/oradata/ncf/standby10a.log
host rm -rf /u02/app/oracle/oradata/ncf/standby10b.log
host rm -rf /u02/app/oracle/oradata/ncf/standby11a.log
host rm -rf /u02/app/oracle/oradata/ncf/standby11b.log
host rm -rf /u02/app/oracle/oradata/ncf/standby12a.log
host rm -rf /u02/app/oracle/oradata/ncf/standby12b.log
host rm -rf /u02/app/oracle/oradata/ncf/standby13a.log
host rm -rf /u02/app/oracle/oradata/ncf/standby13b.log
alter database add standby logfile group 10 ('/u02/app/oracle/oradata/ncf/standby10a.log','/u02/app/oracle/oradata/ncf/standby10b.log') size 100m;
alter database add standby logfile group 11 ('/u02/app/oracle/oradata/ncf/standby11a.log','/u02/app/oracle/oradata/ncf/standby11b.log') size 100m;
alter database add standby logfile group 12 ('/u02/app/oracle/oradata/ncf/standby12a.log','/u02/app/oracle/oradata/ncf/standby12b.log') size 100m;
alter database add standby logfile group 13 ('/u02/app/oracle/oradata/ncf/standby13a.log','/u02/app/oracle/oradata/ncf/standby13b.log') size 100m;
4、備庫操作：
處理備庫的redo log：
select l.group#,l.bytes/1024/1024 mb,lf.member from v$log l,v$logfile lf where l.group#=lf.group# order by group#;
GROUP# MB MEMBER
---------- ---------- --------------------------------------------------
2 50 /u02/app/oracle/oradata/ncf/redo02a.log
2 50 /u02/app/oracle/oradata/ncf/redo02b.log
3 50 /u02/app/oracle/oradata/ncf/redo03a.log
3 50 /u02/app/oracle/oradata/ncf/redo03b.log
4 50 /u02/app/oracle/oradata/ncf/redo04a.log
4 50 /u02/app/oracle/oradata/ncf/redo04b.log
select group#,status from v$log;
GROUP# STATUS
---------- ----------------
2 CLEARING
3 CURRENT
4 CLEARING
alter database clear logfile group 4;
alter database drop logfile group 4;
host rm -rf /u02/app/oracle/oradata/ncf/redo04a.log
host rm -rf /u02/app/oracle/oradata/ncf/redo04b.log
alter database add logfile group 4 ('/u02/app/oracle/oradata/ncf/redo04a.log','/u02/app/oracle/oradata/ncf/redo04b.log') size 100m;
alter database clear logfile group 2;
alter database drop logfile group 2;
host rm -rf /u02/app/oracle/oradata/ncf/redo02a.log
host rm -rf /u02/app/oracle/oradata/ncf/redo02b.log
alter database add logfile group 2 ('/u02/app/oracle/oradata/ncf/redo02a.log','/u02/app/oracle/oradata/ncf/redo02b.log') size 100m;
針對current的日誌，需要先在主庫上切換日誌，變為clearing再處理：
select group#,status from v$log;
GROUP# STATUS
---------- ----------------
2 CURRENT
3 CLEARING
4 UNUSED
alter database clear logfile group 3;
alter database drop logfile group 3;
host rm -rf /u02/app/oracle/oradata/ncf/redo03a.log
host rm -rf /u02/app/oracle/oradata/ncf/redo03b.log
alter database add logfile group 3 ('/u02/app/oracle/oradata/ncf/redo03a.log','/u02/app/oracle/oradata/ncf/redo03b.log') size 100m;
處理備庫的standby log：
select sl.group#,sl.bytes/1024/1024 mb,lf.member from v$standby_log sl,v$logfile lf where sl.group#=lf.group# order by group#;
GROUP# MB MEMBER
---------- ---------- --------------------------------------------------
10 50 /u02/app/oracle/oradata/ncf/standby10a.log
10 50 /u02/app/oracle/oradata/ncf/standby10b.log
11 50 /u02/app/oracle/oradata/ncf/standby11a.log
11 50 /u02/app/oracle/oradata/ncf/standby11b.log
12 50 /u02/app/oracle/oradata/ncf/standby12a.log
12 50 /u02/app/oracle/oradata/ncf/standby12b.log
13 50 /u02/app/oracle/oradata/ncf/standby13a.log
13 50 /u02/app/oracle/oradata/ncf/standby13b.log
select group#,status from v$standby_log;
GROUP# STATUS
---------- ----------
10 ACTIVE
11 UNASSIGNED
12 UNASSIGNED
13 UNASSIGNED
alter database drop logfile group 11;
alter database drop logfile group 12;
alter database drop logfile group 13;
host rm -rf /u02/app/oracle/oradata/ncf/standby11a.log
host rm -rf /u02/app/oracle/oradata/ncf/standby11b.log
host rm -rf /u02/app/oracle/oradata/ncf/standby12a.log
host rm -rf /u02/app/oracle/oradata/ncf/standby12b.log
host rm -rf /u02/app/oracle/oradata/ncf/standby13a.log
host rm -rf /u02/app/oracle/oradata/ncf/standby13b.log
alter database add standby logfile group 11 ('/u02/app/oracle/oradata/ncf/standby11a.log','/u02/app/oracle/oradata/ncf/standby11b.log') size 100m;
alter database add standby logfile group 12 ('/u02/app/oracle/oradata/ncf/standby12a.log','/u02/app/oracle/oradata/ncf/standby12b.log') size 100m;
alter database add standby logfile group 13 ('/u02/app/oracle/oradata/ncf/standby13a.log','/u02/app/oracle/oradata/ncf/standby13b.log') size 100m;
針對active的日誌，需要先在主庫上切換日誌再處理
SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ----------
10 UNASSIGNED
11 ACTIVE
12 UNASSIGNED
13 UNASSIGNED
alter database drop logfile group 10;
host rm -rf /u02/app/oracle/oradata/ncf/standby10a.log
host rm -rf /u02/app/oracle/oradata/ncf/standby10b.log
alter database add standby logfile group 10 ('/u02/app/oracle/oradata/ncf/standby10a.log','/u02/app/oracle/oradata/ncf/standby10b.log') size 100m;
5、在主庫和備庫設定standby_file_management為auto
alter system set standby_file_management='auto';
6、備庫上啟動同步：
alter database recover managed standby database using current logfile disconnect;
7、檢查狀態：
主庫多次執行：alter system switch logfile;
主庫：select max(sequence#) from v$archived_log;
備庫：select max(sequence#) from v$archived_log where applied='YES';
SELECT name,value,time_computed FROM V$DATAGUARD_STATS WHERE NAME like '%lag%';
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26194851/viewspace-2083912/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle11g調整redo日誌大小、組數和每組成員數Oracle
- Oracle調整redo log日誌大小Oracle
- RAC和Dataguard環境下主備庫切換演練模板
- Dataguard環境修改主庫和standby庫online redo log&standby redo log大小
- oracle線上調整重做日誌Oracle
- Oracle重做日誌調整技巧Oracle
- dg庫日誌應用慢引數調整
- oracle rac+adg調整redo日誌組導致adg備庫ogg抽取程式abendOracle
- 【DATAGUARD】手工恢復備庫日誌中斷
- Oracle10g RAC環境下DataGuard備庫搭建例項Oracle
- 19c ADG環境中主庫PDB調整SGA_TARGET自動同步到備庫
- RMAN備份整庫和歸檔日誌的方法
- oracle效能調優:管理oracle日誌之調整線上日誌檔案Oracle
- Oracle10G Dataguard 多個備庫 - 主庫和物理備庫的切換Oracle
- Oracle DataGuard環境中主庫收到ORA-16198報錯Oracle
- Oracle 11g Dataguard 暫停物理備庫的日誌傳輸Oracle
- Oracle10g RAC環境下DataGuard備庫搭建例項-eygleOracle
- 管理oracle日誌之調整檢查點Oracle
- RAC環境下的redo日誌組重建
- Oracle日誌組新增冗餘檔案和日誌組Oracle
- ORACLE RAC資料庫配置Dataguard環境(3)Oracle資料庫
- ORACLE RAC資料庫配置Dataguard環境(2)Oracle資料庫
- ORACLE RAC資料庫配置Dataguard環境(1)Oracle資料庫
- RAC環境備份歸檔日誌和RMAN恢復啟動資料庫資料庫
- DataGuard備庫刪除已經應用的日誌
- oracle 之dataguard主庫系統崩潰之物理備庫切主庫Oracle
- Oracle 11g Dataguard環境下資料檔案、日誌檔案管理（下）Oracle
- Oracle 11g Dataguard環境下資料檔案、日誌檔案管理（上）Oracle
- 新增日誌組以及調整日誌的大小
- DATAGUARD主庫重做日誌不能同步至備庫 alert日中報ORA-12170故障處理
- Oracle 資料庫引數調整Oracle資料庫
- Oracle10g RAC環境下 DataGuard備庫搭建例項-3-eygleOracle
- Oracle10g RAC環境下 DataGuard備庫搭建例項-2-eygleOracle
- Oracle10g RAC環境下 DataGuard備庫搭建例項-1-eygleOracle
- dataguard 主備庫出現gap
- 【DataGuard】使用Grid Control調整Oracle物理Data Guard備庫為Read OnlyOracle
- ORACLE 12C DATAGUARD環境搭建和主從切換Oracle
- Oracle資料庫9i在AIX環境下的效能調整Oracle資料庫AI