STARTING AND STOPING DATA GUARD SQL APPLY USING SQL *PLUS

High Availability / 14/07/2022

Quando temos um Data Guard configurado em nosso ambiente, com o Standby do tipo Logical, podemos inicializar o SQL Apply conforme opçoes abaixo. Antes disso, checando se o primary e standby estão com as instâncias e listeners inicializados:

[oracle@fornix1 ~]$ ps -ef | grep pmon
grid      3312     1  0 03:54 ?        00:00:00 asm_pmon_+ASM
oracle    3497     1  0 03:54 ?        00:00:00 ora_pmon_cortex
oracle    4512  3687  0 04:02 pts/0    00:00:00 grep --color=auto pmon
[oracle@fornix1 ~]$ ps -ef | grep lsnr
grid      2966     1  0 03:53 ?        00:00:00 /grid/19.3.0/product/bin/tnslsnr LISTENER -no_crs_notify -inherit
oracle    4589  3687  0 04:04 pts/0    00:00:00 grep --color=auto lsnr

[oracle@fornix2 ~]$ ps -ef | grep pmon
grid      3451     1  0 03:54 ?        00:00:00 asm_pmon_+ASM
oracle    3590     1  0 03:55 ?        00:00:00 ora_pmon_CORTEXDR
oracle    4252  3721  0 04:02 pts/0    00:00:00 grep --color=auto pmon
[oracle@fornix2 ~]$ ps -ef | grep lsnr
grid      3083     1  0 03:54 ?        00:00:00 /grid/19.3.0/product/bin/tnslsnr LISTENERDR -no_crs_notify -inherit
grid      3084     1  0 03:54 ?        00:00:00 /grid/19.3.0/product/bin/tnslsnr LISTENER -no_crs_notify -inherit
oracle    4258  3721  0 04:03 pts/0    00:00:00 grep --color=auto lsnr

No standby:

[oracle@fornix2 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 04:04:52 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> select instance_name,status from v$instance;
 
INSTANCE_NAME    STATUS
---------------- ------------
CORTEXDR         MOUNTED
 
SQL> ALTER DATABASE OPEN;
 
Database altered.
 
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
 
Database altered.

No alert.log do standby, já é possível ver o Oracle iniciando o LogMiner dos archived redologs necessários:

2021-04-02T04:05:46.408641-03:00
ALTER DATABASE START LOGICAL STANDBY APPLY (CORTEXDR)
2021-04-02T04:05:46.408684-03:00
No optional part
Attempt to start background Logical Standby process
Starting background process LSP0
2021-04-02T04:05:46.605863-03:00
LSP0 started with pid=79, OS id=4650
2021-04-02T04:05:47.114169-03:00
Completed: ALTER DATABASE START LOGICAL STANDBY APPLY
2021-04-02T04:05:48.557930-03:00
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
LOGMINER: Memory Size = 100M, Checkpoint interval = 500M
LOGMINER: SpillScn 4986069, ResetLogScn 1920977
2021-04-02T04:05:48.560288-03:00
LOGMINER: summary for session# = 1
LOGMINER: StartScn: 0 (0x0000000000000000)
LOGMINER: EndScn: 0 (0x0000000000000000)
LOGMINER: HighConsumedScn: 4981628 (0x00000000004c037c)
LOGMINER: PSR flags: 0x0
LOGMINER: Session Flags: 0xb2110dc
LOGMINER: Session Flags2: 0x4000
LOGMINER: Read buffers: 16
LOGMINER: Region Queue size: 512
LOGMINER: Redo Queue size: 8192
LOGMINER: Memory LWM: limit 10M, LWM 90M, 90%
LOGMINER: Memory Release Limit: 1M
LOGMINER: Max Decomp Region Memory: 1M
LOGMINER: Transaction Queue Size: 1024
2021-04-02T04:05:49.782808-03:00
LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=80 OS id=4660 sid=95 started
2021-04-02T04:05:49.804493-03:00
LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=81 OS id=4662 sid=73 started

Para interromper o SQL Apply, podemos utilizar o comando abaixo:

[oracle@fornix2 trace]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 04:09:54 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
 
Database altered.

No alert do Standby:

ALTER DATABASE STOP LOGICAL STANDBY APPLY
2021-04-02T04:10:18.292237-03:00
LOGSTDBY Apply process AS01 server id=1 pid=95 OS id=4721 stopped
2021-04-02T04:10:18.292820-03:00
LOGSTDBY Apply process AS02 server id=2 pid=96 OS id=4723 stopped
2021-04-02T04:10:18.293128-03:00
LOGSTDBY Apply process AS03 server id=3 pid=97 OS id=4725 stopped
2021-04-02T04:10:18.293590-03:00
LOGSTDBY Apply process AS04 server id=4 pid=98 OS id=4727 stopped
2021-04-02T04:10:18.294228-03:00
LOGSTDBY Apply process AS05 server id=5 pid=99 OS id=4729 stopped

Outro maneira de inicializar o SQL Apply é utilizando no comando a opção IMMEDIATE, conforme abaixo:

[oracle@fornix2 trace]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 04:12:25 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
 
Database altered.

No alert:

2021-04-02T04:12:33.965248-03:00
LSP0 started with pid=62, OS id=5222
Completed: ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
2021-04-02T04:12:34.547907-03:00
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
LOGMINER: Memory Size = 100M, Checkpoint interval = 500M
LOGMINER: SpillScn 5047808, ResetLogScn 1920977
2021-04-02T04:12:34.548329-03:00
LOGMINER: summary for session# = 1
LOGMINER: StartScn: 0 (0x0000000000000000)
LOGMINER: EndScn: 0 (0x0000000000000000)
LOGMINER: HighConsumedScn: 5047795 (0x00000000004d05f3)
LOGMINER: PSR flags: 0x1
LOGMINER: Session Flags: 0xba110dc
LOGMINER: Session Flags2: 0x4000
LOGMINER: Read buffers: 16
LOGMINER: Region Queue size: 512
LOGMINER: Redo Queue size: 8192
LOGMINER: Memory LWM: limit 10M, LWM 90M, 90%
LOGMINER: Memory Release Limit: 1M
LOGMINER: Max Decomp Region Memory: 1M
LOGMINER: Transaction Queue Size: 1024
2021-04-02T04:12:34.641219-03:00
LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=63 OS id=5225 sid=107 started
2021-04-02T04:12:34.652602-03:00
LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=64 OS id=5227 sid=73 started
2021-04-02T04:12:34.664954-03:00
LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=65 OS id=5229 sid=75 started
2021-04-02T04:12:34.871126-03:00
LOGSTDBY Analyzer process AS00 started with server id=0 pid=66 OS id=5231
2021-04-02T04:12:34.887789-03:00
LOGSTDBY Apply process AS04 started with server id=4 pid=70 OS id=5239
2021-04-02T04:12:34.890309-03:00
LOGSTDBY Apply process AS01 started with server id=1 pid=67 OS id=5233
2021-04-02T04:12:34.891321-03:00

Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish