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