Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, I will talk about Disabling And Enabling Data Guard Broker Transport Services.
Below is how to make Redo Transport Services disabled from the broker by deferring the LOG_ARCHIVE_DEST_STATE_n parameter with SQL commands.
1. We learn thread# and sequence# before disabling Redo Transport Services.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | [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. Redo Transport services are disabled.
1 2 3 4 5 6 | 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. We perform the Log Switch operation.
1 2 3 4 5 6 7 8 9 10 11 | [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. |
4. We check whether the Archive Redo Logs go to the Standby side.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | [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 |
5. We perform the commissioning process again.
1 2 3 4 5 6 | 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; |
6. We check whether the Archive Redo Logs go to the Standby side.
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 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 | [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. |
7. Redo Transport is stopped in a specific standby, not in all standbys, as follows.
a. Redo Transport to Logical Standby is stopped.
1 2 | DGMGRL> edit database logical set property LogShipping=OFF; Property "logshipping" updated |
b. We check if the parameter is DEFER.
1 2 3 4 5 | [Primary-1] SQL> show parameter log_archive_dest_state_3 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_3 string RESET |
c. With Log Switch, a test is performed to understand whether the Redos go on the Logical side. For this, we first learn the SEQUENCEs available in Primary and Logical.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [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. We do the Log Switch operation.
1 2 3 4 5 6 7 | [Primary-1] SQL> alter system switch logfile; System altered. [Primary-1] SQL> alter system switch logfile; System altered. |
f. We check the SEQUENCEs.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [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. The Redo stream that was stopped in the specific Standby is started.
1 2 | DGMGRL> edit database logical set property LogShipping=ON; Property "logshipping" updated |
i. It is checked whether the parameter is ENABLE or not.
1 2 3 4 5 | [Primary-1] SQL> show parameter log_archive_dest_state_3 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_3 string ENABLE |
h. It is checked whether the redos pass to Logical Standby.
1 2 3 4 5 6 7 8 9 10 11 12 13 | [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 |
