DataGuard 物理備庫，正常為 mount 狀態(tài)，如果想要進(jìn)行查詢，需要轉(zhuǎn)為 ADG，即 read only 狀態(tài)！

在開啟為 read only 之前，需要給備庫添加 n+1 組的 standby redo log！

查詢主庫的 redolog ：

set line222
set pagesize1000
col member for a60
select t2.thread#,t1.group#,t1.member,t2.bytes/1024/1024 from v$logfile t1,v$log t2 where t1.group#=t2.group# order by 1,2;

   THREAD#     GROUP# MEMBER   T2.BYTES/1024/1024
---------- ---------- ------------------------------------------------------------ ------------------
 1    1 +DATA/mesdb/onlinelog/group_1.257.1034181431  200
 1    2 +DATA/mesdb/onlinelog/group_2.258.1034181431  200
 1    5 +DATA/mesdb/onlinelog/group_5.268.1034182623  200
 1    7 +DATA/mesdb/onlinelog/group_7.270.1034182623  200
 1    9 +DATA/mesdb/onlinelog/group_9.272.1034182623  200
 1   11 +DATA/mesdb/onlinelog/group_11.274.1034182623  200
 1   13 +DATA/mesdb/onlinelog/group_13.276.1034182625  200
 2    3 +DATA/mesdb/onlinelog/group_3.265.1034182261  200
 2    4 +DATA/mesdb/onlinelog/group_4.266.1034182263  200
 2    6 +DATA/mesdb/onlinelog/group_6.269.1034182623  200
 2    8 +DATA/mesdb/onlinelog/group_8.271.1034182623  200
 2   10 +DATA/mesdb/onlinelog/group_10.273.1034182623  200
 2   12 +DATA/mesdb/onlinelog/group_12.275.1034182623  200
 2   14 +DATA/mesdb/onlinelog/group_14.277.1034182627  200

14 rows selected.

需要注意：

• stanby log日志大小至少要和redo log日志一樣大小，不能小于
• stanby log數(shù)量： standby logfile=(1+logfile組數(shù))=(1+2)=3組,每個(gè)thread需要加3組standby logfile.
• thread要與redo log保持一致，如果是rac，需要增加多個(gè)thread對(duì)應(yīng)的standby log

關(guān)閉備庫同步進(jìn)程：

alter database recover managed standby database cancel;

備庫添加standby redo log：

ALTER DATABASE ADD STANDBY LOGFILE thread 1 
group 21 ('/data/MESSTB/onlinelog/standby_group_21') SIZE 200M,
group 22 ('/data/MESSTB/onlinelog/standby_group_22') SIZE 200M,
group 23 ('/data/MESSTB/onlinelog/standby_group_23') SIZE 200M,
group 24 ('/data/MESSTB/onlinelog/standby_group_24') SIZE 200M,
group 25 ('/data/MESSTB/onlinelog/standby_group_25') SIZE 200M,
group 26 ('/data/MESSTB/onlinelog/standby_group_26') SIZE 200M,
group 27 ('/data/MESSTB/onlinelog/standby_group_27') SIZE 200M,
group 28 ('/data/MESSTB/onlinelog/standby_group_28') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE thread 2
group 31 ('/data/MESSTB/onlinelog/standby_group_31') SIZE 200M,
group 32 ('/data/MESSTB/onlinelog/standby_group_32') SIZE 200M,
group 33 ('/data/MESSTB/onlinelog/standby_group_33') SIZE 200M,
group 34 ('/data/MESSTB/onlinelog/standby_group_34') SIZE 200M,
group 35 ('/data/MESSTB/onlinelog/standby_group_35') SIZE 200M,
group 36 ('/data/MESSTB/onlinelog/standby_group_36') SIZE 200M,
group 37 ('/data/MESSTB/onlinelog/standby_group_37') SIZE 200M,
group 38 ('/data/MESSTB/onlinelog/standby_group_38') SIZE 200M;

重啟開啟備庫同步進(jìn)程：

alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;

檢查日志同步情況：

set line222
set pagesize1000
col member for a60
select t2.thread#,t1.group#,t1.member,t2.STATUS,t2.ARCHIVED,t2.bytes/1024/1024 from v$logfile t1,v$standby_log t2 where t1.group#=t2.group# order by 1,2;

select process,group#,thread#,sequence# from v$managed_standby;

select database_role,open_mode from v$database;

至此，ADG 已經(jīng)轉(zhuǎn)換完成！

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

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