重慶分公司
重慶分公司，新征程啟航
為企業(yè)提供網(wǎng)站建設(shè)、域名注冊、服務(wù)器等服務(wù)
Dataguard環(huán)境修改主庫和standby庫onlineredolog&standbyredolog大小
當(dāng)前環(huán)境：
目前成都創(chuàng)新互聯(lián)已為成百上千家的企業(yè)提供了網(wǎng)站建設(shè)、域名、網(wǎng)站空間、網(wǎng)站托管、企業(yè)網(wǎng)站設(shè)計(jì)、滑縣網(wǎng)站維護(hù)等服務(wù)，公司將堅(jiān)持客戶導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長，共同發(fā)展。
Oracle 11.0.2.4 primary+standby
3組redo log group(每組200M)，4組standby redo log
因?yàn)闃I(yè)務(wù)增加日誌切換頻繁，計(jì)劃修改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> 因?yàn)閞edo logfile不能直接修改大小，計(jì)劃先新增log group后，再刪除原log group
2> 因?yàn)閐ataguard環(huán)境涉及到主庫和standby的redo log、standby redo log相對複雜，分為下面8個(gè)步驟
具體步驟：
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. 主庫上創(chuàng)建新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時(shí)再刪除
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狀態(tài)固不能drop 所以報(bào)錯(cuò)
在主庫和standby分別執(zhí)行下列SQL：
SELECT DISTINCT THREAD#,max(SEQUENCE#) OVER(PARTITION BY THREAD#) A FROM V$ARCHIVED_LOG;
主庫SEQUENCE#： 67，standby SEQUENCE#：65
standby運(yùn)用下新產(chǎn)生的日誌以便switch current狀態(tài)的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
網(wǎng)站標(biāo)題：Dataguard環(huán)境修改主庫和standby庫onlineredolog&standbyredolog大小
鏈接分享：http://www.xueling.net.cn/article/jhcios.html