在今天的文章中，我将讨论禁用和启用 Data Guard Broker 传输服务。
下面是如何通过使用 SQL 命令延迟 LOG_ARCHIVE_DEST_STATE_n 参数来从代理禁用重做传输服务。
1.我们在禁用重做传输服务之前学习thread#和sequence#。
[Primary-1] SQL> select max(sequence#), thread# from v$archived_log group by thread#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
379 1
242 2
[Physical-1] SQL> select max(sequence#), thread# from v$archived_log group by thread#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
379 1
242 2
[Logical-1] SQL> select max(sequence#), thread#, applied from dba_logstdby_log group by thread#, applied;
MAX(SEQUENCE#) THREAD# APPLIED
-------------- ---------- --------
379 1 YES
242 2 YES
2.重做传输服务被禁用。
DGMGRL> edit database primary set state='TRANSPORT-OFF';
Succeeded.
[Primary-1]--> ALERT LOG
ALTER SYSTEM SET log_archive_dest_state_2='RESET' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_state_3='RESET' SCOPE=BOTH;
3.我们执行Log Switch操作。
[Primary-1] SQL> alter system switch logfile;
System altered.
[Primary-1] SQL> alter system switch logfile;
System altered.
[Primary-1] SQL> alter system switch logfile;
System altered.
- 我们检查Archive Redo Logs 是否进入Standby 端。
[Primary-1] SQL> select max(sequence#), thread# from v$archived_log group by thread#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
382 1
243 2
[Physical-1] SQL> select max(sequence#), thread# from v$archived_log group by thread#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
380 1
243 2
[Logical-1] SQL> select max(sequence#), thread#, applied from dba_logstdby_log group by thread#, applied;
MAX(SEQUENCE#) THREAD# APPLIED
-------------- ---------- --------
380 1 CURRENT
379 1 YES
242 2 YES
243 2 CURRENT
- 我们再次执行调试过程。
DGMGRL> edit database primary set state='TRANSPORT-ON';
Succeeded
[Primary-1]--> ALERT LOG
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_state_3='ENABLE' SCOPE=BOTH;
- 我们检查Archive Redo Logs 是否进入Standby 端。
[Primary-1] SQL> select max(sequence#), thread# from v$archived_log group by thread#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
383 1
244 2
[Physical-1] SQL> select max(sequence#), thread# from v$archived_log group by thread#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
383 1
244 2
[Logical-1] SQL> select max(sequence#), thread#, applied from dba_logstdby_log group by thread#, applied;
MAX(SEQUENCE#) THREAD# APPLIED
-------------- ---------- --------
383 1 YES
244 2 YES
[Logical-1] SQL> select sequence#, applied from dba_logstdby_log order by sequence#;
SEQUENCE# APPLIED
---------- --------
186 YES
187 YES
188 YES
189 YES
190 YES
191 YES
192 YES
193 YES
194 YES
195 YES
196 YES
SEQUENCE# APPLIED
---------- --------
197 YES
198 YES
199 YES
200 YES
201 YES
202 YES
203 YES
204 YES
205 YES
206 YES
207 YES
SEQUENCE# APPLIED
---------- --------
208 YES
209 YES
210 YES
211 YES
212 YES
213 YES
214 YES
215 YES
216 YES
217 YES
218 YES
SEQUENCE# APPLIED
---------- --------
219 YES
220 YES
221 YES
222 YES
223 YES
224 YES
225 YES
226 YES
227 YES
228 YES
229 YES
SEQUENCE# APPLIED
---------- --------
230 YES
231 YES
232 YES
233 YES
234 YES
235 YES
236 YES
237 YES
238 YES
239 YES
240 YES
SEQUENCE# APPLIED
---------- --------
241 YES
242 YES
243 YES
244 YES
324 YES
325 YES
326 YES
327 YES
328 YES
329 YES
330 YES
SEQUENCE# APPLIED
---------- --------
331 YES
332 YES
333 YES
334 YES
335 YES
336 YES
337 YES
338 YES
339 YES
340 YES
341 YES
SEQUENCE# APPLIED
---------- --------
342 YES
343 YES
344 YES
345 YES
346 YES
347 YES
348 YES
349 YES
350 YES
351 YES
352 YES
SEQUENCE# APPLIED
---------- --------
353 YES
354 YES
355 YES
356 YES
357 YES
358 YES
359 YES
360 YES
361 YES
362 YES
363 YES
SEQUENCE# APPLIED
---------- --------
364 YES
365 YES
366 YES
367 YES
368 YES
369 YES
370 YES
371 YES
372 YES
373 YES
374 YES
SEQUENCE# APPLIED
---------- --------
375 YES
376 YES
377 YES
378 YES
379 YES
380 YES
381 YES
382 YES
383 YES
119 rows selected.
- Redo Transport在特定的standby中停止，而不是在所有的standby中，如下。
a.停止到逻辑备用的重做传输。
DGMGRL> edit database logical set property LogShipping=OFF;
Property "logshipping" updated
b.我们检查参数是否为 DEFER。
[Primary-1] SQL> show parameter log_archive_dest_state_3
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_3 string RESET
c.使用 Log Switch，执行测试以了解重做是否在逻辑端。 为此，我们首先学习 Primary 和 Logical 中可用的 SEQUENCE。
[Primary-1] SQL> select max(sequence#),thread#,archived from v$archived_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#,archived order by thread#, max(sequence#);
MAX(SEQUENCE#) THREAD# ARC
-------------- ---------- ---
394 1 YES
230 2 YES
[Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#, applied order by thread#, max(sequence#);
MAX(SEQUENCE#) THREAD# APPLIED
-------------- ---------- --------
393 1 YES
394 1 CURRENT
229 2 YES
230 2 CURRENT
d.我们进行 Log Switch 操作。
[Primary-1] SQL> alter system switch logfile;
System altered.
[Primary-1] SQL> alter system switch logfile;
System altered.
f.我们检查序列。
[Primary-1] SQL> select max(sequence#),thread#,archived from v$archived_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#,archived order by thread#, max(sequence#);
MAX(SEQUENCE#) THREAD# ARC
-------------- ---------- ---
396 1 YES
230 2 YES
[Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#, applied order by thread#, max(sequence#);
MAX(SEQUENCE#) THREAD# APPLIED
-------------- ---------- --------
393 1 YES
394 1 CURRENT
229 2 YES
230 2 CURRENT
g.在特定 Standby 中停止的重做流被启动。
DGMGRL> edit database logical set property LogShipping=ON;
Property "logshipping" updated
i.检查参数是否为 ENABLE。
[Primary-1] SQL> show parameter log_archive_dest_state_3
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_3 string ENABLE
h.检查重做是否传递到逻辑备用。
[Primary-1] SQL> select max(sequence#),thread#,archived from v$archived_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#,archived order by thread#, max(sequence#);
MAX(SEQUENCE#) THREAD# ARC
-------------- ---------- ---
397 1 YES
231 2 YES
[Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#, applied order by thread#, max(sequence#);
MAX(SEQUENCE#) THREAD# APPLIED
-------------- ---------- --------
397 1 YES
231 2 YES
原文标题：Disabling And Enabling Data Guard Broker Transport Services
原文作者：Onur ARDAHANLI
原文地址：https://dbtut.com/index.php/2022/04/19/disabling-and-enabling-data-guard-broker-transport-services/
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
