DataGuard
物理
备库，正常为 mount 状态，如果想要进行查询，需要转为 ADG，即 read only 状态！

在
开启
为 read only 之前，需要给备库添加 n+1 组的 standby redo log！

查询主库的 redolog ：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
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.

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16
* 17
* 18
* 19
* 20
* 21
* 22
* 23

需要注意：

  • stanby log日志大小至少要和redo log日志一样大小，不能小于
  • stanby log数量： standby logfile=(1+logfile组数)=(1+2)=3组,每个thread需要加3组standby logfile.
  • thread要与redo log保持一致，如果是rac，需要增加多个thread对应的standby log

关闭备库同步进程：

1
2
3
4
5
alter database recover managed standby database cancel;

AI写代码sql

* 1

备库添加standby redo log：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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;

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16
* 17
* 18
* 19

重启开启备库同步进程：

1
2
3
4
5
6
7
alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;

AI写代码sql

* 1
* 2

检查日志同步情况：

1
2
3
4
5
6
7
8
9
10
11
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;

AI写代码sql

* 1
* 2
* 3
* 4
1
2
3
4
5
select process,group#,thread#,sequence# from v$managed_standby;

AI写代码sql

* 1

Oracle 物理 DG 转 ADG 步骤 - 图3

1
2
3
4
5
select database_role,open_mode from v$database;

AI写代码sql

* 1

至此，ADG 已经转换完成！

本次分享到此结束啦~

如果觉得文章对你有帮助，点赞、收藏、关注、评论，一键四连支持，你的支持就是我创作最大的动力。

❤️
技术
交流可以 关注公众号：Lucifer三思而后行 ❤️