Dataguard环境修改主库和standby庫online redo log&standby redo log大小
当前环境：
Oracle 11.0.2.4 primary+standby
3組redo log group(每組200M)，4组standby redo log
因为业务增加日誌切換頻繁，计划修改redo logfile 到250M
select * from v$logfile
1 ONLINE /data/mes/redo01.log
2 ONLINE /data/mes/redo02.log
3 ONLINE /data/mes/redo03.log
11 STANDBY /data/mes/standbylog11.log
12 STANDBY /data/mes/standbylog12.log
13 STANDBY /data/mes/standbylog13.log
14 STANDBY /data/mes/standbylog14.log
分析說明：
1> 因為redo logfile不能直接修改大小，計劃先新增log group后，再刪除原log group
2> 因為dataguard環境涉及到主庫和standby的redo log、standby redo log相對複雜，分為下面8個步驟
具體步驟：
1. Standby 上增加新standby redo log
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database add standby logfile group 31 ('/data/mes/standbylog31.log') size 250M;
Database altered.
SQL> alter database add standby logfile group 32 ('/data/mes/standbylog32.log') size 250M;
Database altered.
SQL> alter database add standby logfile group 33 ('/data/mes/standbylog33.log') size 250M;
Database altered.
SQL> alter database add standby logfile group 34 ('/data/mes/standbylog34.log') size 250M;
Database altered.
2. Standby 上刪除原standby redo log
SQL> alter database drop logfile group 11;
Database altered.
SQL> alter database drop logfile group 12;
Database altered.
SQL> alter database drop logfile group 13;
Database altered.
SQL> alter database drop logfile group 14;
Database altered.
3. 主庫上創建新redo log
SQL> alter database add logfile group 21 ('/data/mes/standbylog21.log') size 250M;
Database altered.
SQL> alter database add logfile group 22 ('/data/mes/standbylog22.log') size 250M;
Database altered.
SQL> alter database add logfile group 23 ('/data/mes/standbylog23.log') size 250M;
Database altered.
4.主庫上刪除原redo log
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance mes (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/data/mes/redo03.log'
注：切換主庫redo logfile，待log group 3不為current和active時再刪除
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile group 3;
Database altered.
5. Standby上增加新redo log
SQL> show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO
SQL> alter system set standby_file_management=manual;
System altered.
SQL> alter database add logfile group 21 ('/data/mes/standbylog21.log') size 250M;
Database altered.
SQL> alter database add logfile group 22 ('/data/mes/standbylog22.log') size 250M;
Database altered.
SQL> alter database add logfile group 23 ('/data/mes/standbylog23.log') size 250M;
Database altered.
6.Standby上刪除原redo log
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance mes (thread 1) - cannot drop
ORA-00312: online log 3 thread 1:
'/u01/product/fast_recovery_area/MESDG/onlinelog/o1_mf_3_f46lgbqp_.log'
在standby中查詢 v$log group 3為CURRENT狀態固不能drop 所以報錯
在主庫和standby分別執行下列SQL：
SELECT DISTINCT THREAD#,max(SEQUENCE#) OVER(PARTITION BY THREAD#) A FROM V$ARCHIVED_LOG;
主庫SEQUENCE#： 67，standby SEQUENCE#：65
standby運用下新產生的日誌以便switch current狀態的group 3
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
查看standby中v$log 3 不為CURRENT 了，取消MRP再次drop group 3成功
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
7.主庫增加新standby redo log
SQL> alter database add standby logfile group 31 ('/data/mes/standbylog31.log') size 250M;
Database altered.
SQL> alter database add standby logfile group 32 ('/data/mes/standbylog32.log') size 250M;
Database altered.
SQL> alter database add standby logfile group 33 ('/data/mes/standbylog33.log') size 250M;
Database altered.
SQL> alter database add standby logfile group 34 ('/data/mes/standbylog34.log') size 250M;
Database altered.
8.主庫刪除原standby redo log
SQL> alter database drop logfile group 11;
Database altered.
SQL> alter database drop logfile group 12;
Database altered.
SQL> alter database drop logfile group 13;
Database altered.
SQL> alter database drop logfile group 14;
Database altered.
最後，standby中開啟MRP后，所有操作完成
SQL> alter system set standby_file_management=auto;
System altered.
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
select * from v$logfile
21 ONLINE /data/mes/standbylog21.log
22 ONLINE /data/mes/standbylog22.log
23 ONLINE /data/mes/standbylog23.log
31 STANDBY /data/mes/standbylog31.log
32 STANDBY /data/mes/standbylog32.log
33 STANDBY /data/mes/standbylog33.log
34 STANDBY /data/mes/standbylog34.log
