{"id":3433,"date":"2021-04-02T07:14:29","date_gmt":"2021-04-02T07:14:29","guid":{"rendered":"https:\/\/swiv.com.br\/starting-and-stoping-data-guard-sql-apply-using-sql-plus\/"},"modified":"2026-05-27T20:02:49","modified_gmt":"2026-05-27T19:02:49","slug":"starting-and-stoping-data-guard-sql-apply-using-sql-plus","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/04\/02\/starting-and-stoping-data-guard-sql-apply-using-sql-plus\/","title":{"rendered":"STARTING AND STOPING DATA GUARD SQL APPLY USING SQL *PLUS"},"content":{"rendered":"\n

Quando temos um Data Guard configurado em nosso ambiente, com o Standby do tipo Logical, podemos inicializar o SQL Apply conforme op\u00e7oes abaixo. Antes disso, checando se o primary e standby est\u00e3o com as inst\u00e2ncias e listeners inicializados:<\/p>\n\n\n

\n[oracle@fornix1 ~]$ ps -ef | grep pmon\ngrid      3312     1  0 03:54 ?        00:00:00 asm_pmon_+ASM\noracle    3497     1  0 03:54 ?        00:00:00 ora_pmon_cortex\noracle    4512  3687  0 04:02 pts\/0    00:00:00 grep --color=auto pmon\n[oracle@fornix1 ~]$ ps -ef | grep lsnr\ngrid      2966     1  0 03:53 ?        00:00:00 \/grid\/19.3.0\/product\/bin\/tnslsnr LISTENER -no_crs_notify -inherit\noracle    4589  3687  0 04:04 pts\/0    00:00:00 grep --color=auto lsnr\n<\/pre><\/div>\n\n
\n[oracle@fornix2 ~]$ ps -ef | grep pmon\ngrid      3451     1  0 03:54 ?        00:00:00 asm_pmon_+ASM\noracle    3590     1  0 03:55 ?        00:00:00 ora_pmon_CORTEXDR\noracle    4252  3721  0 04:02 pts\/0    00:00:00 grep --color=auto pmon\n[oracle@fornix2 ~]$ ps -ef | grep lsnr\ngrid      3083     1  0 03:54 ?        00:00:00 \/grid\/19.3.0\/product\/bin\/tnslsnr LISTENERDR -no_crs_notify -inherit\ngrid      3084     1  0 03:54 ?        00:00:00 \/grid\/19.3.0\/product\/bin\/tnslsnr LISTENER -no_crs_notify -inherit\noracle    4258  3721  0 04:03 pts\/0    00:00:00 grep --color=auto lsnr\n<\/pre><\/div>\n\n\n
No standby:<\/p>\n\n\n
\n[oracle@fornix2 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 04:04:52 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\n \n \nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n \nSQL> select instance_name,status from v$instance;\n \nINSTANCE_NAME    STATUS\n---------------- ------------\nCORTEXDR         MOUNTED\n \nSQL> ALTER DATABASE OPEN;\n \nDatabase altered.\n \nSQL> ALTER DATABASE START LOGICAL STANDBY APPLY;\n \nDatabase altered.\n<\/pre><\/div>\n\n\n
No alert.log do standby, j\u00e1 \u00e9 poss\u00edvel ver o Oracle iniciando o LogMiner dos archived redologs necess\u00e1rios:<\/p>\n\n\n
\n2021-04-02T04:05:46.408641-03:00\nALTER DATABASE START LOGICAL STANDBY APPLY (CORTEXDR)\n2021-04-02T04:05:46.408684-03:00\nNo optional part\nAttempt to start background Logical Standby process\nStarting background process LSP0\n2021-04-02T04:05:46.605863-03:00\nLSP0 started with pid=79, OS id=4650\n2021-04-02T04:05:47.114169-03:00\nCompleted: ALTER DATABASE START LOGICAL STANDBY APPLY\n2021-04-02T04:05:48.557930-03:00\nLOGMINER: Parameters summary for session# = 1\nLOGMINER: Number of processes = 3, Transaction Chunk Size = 201\nLOGMINER: Memory Size = 100M, Checkpoint interval = 500M\nLOGMINER: SpillScn 4986069, ResetLogScn 1920977\n2021-04-02T04:05:48.560288-03:00\nLOGMINER: summary for session# = 1\nLOGMINER: StartScn: 0 (0x0000000000000000)\nLOGMINER: EndScn: 0 (0x0000000000000000)\nLOGMINER: HighConsumedScn: 4981628 (0x00000000004c037c)\nLOGMINER: PSR flags: 0x0\nLOGMINER: Session Flags: 0xb2110dc\nLOGMINER: Session Flags2: 0x4000\nLOGMINER: Read buffers: 16\nLOGMINER: Region Queue size: 512\nLOGMINER: Redo Queue size: 8192\nLOGMINER: Memory LWM: limit 10M, LWM 90M, 90%\nLOGMINER: Memory Release Limit: 1M\nLOGMINER: Max Decomp Region Memory: 1M\nLOGMINER: Transaction Queue Size: 1024\n2021-04-02T04:05:49.782808-03:00\nLOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=80 OS id=4660 sid=95 started\n2021-04-02T04:05:49.804493-03:00\nLOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=81 OS id=4662 sid=73 started\n<\/pre><\/div>\n\n\n
Para interromper o SQL Apply, podemos utilizar o comando abaixo:<\/p>\n\n\n
\n[oracle@fornix2 trace]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 04:09:54 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\n \n \nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n \nSQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;\n \nDatabase altered.\n<\/pre><\/div>\n\n\n
No alert do Standby:<\/p>\n\n\n
\nALTER DATABASE STOP LOGICAL STANDBY APPLY\n2021-04-02T04:10:18.292237-03:00\nLOGSTDBY Apply process AS01 server id=1 pid=95 OS id=4721 stopped\n2021-04-02T04:10:18.292820-03:00\nLOGSTDBY Apply process AS02 server id=2 pid=96 OS id=4723 stopped\n2021-04-02T04:10:18.293128-03:00\nLOGSTDBY Apply process AS03 server id=3 pid=97 OS id=4725 stopped\n2021-04-02T04:10:18.293590-03:00\nLOGSTDBY Apply process AS04 server id=4 pid=98 OS id=4727 stopped\n2021-04-02T04:10:18.294228-03:00\nLOGSTDBY Apply process AS05 server id=5 pid=99 OS id=4729 stopped\n<\/pre><\/div>\n\n\n
Outro maneira de inicializar o SQL Apply \u00e9 utilizando no comando a op\u00e7\u00e3o IMMEDIATE, conforme abaixo:<\/p>\n\n\n
\n[oracle@fornix2 trace]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 04:12:25 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\n \n \nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n \nSQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;\n \nDatabase altered.\n<\/pre><\/div>\n\n\n
No alert:<\/p>\n\n\n
\n2021-04-02T04:12:33.965248-03:00\nLSP0 started with pid=62, OS id=5222\nCompleted: ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE\n2021-04-02T04:12:34.547907-03:00\nLOGMINER: Parameters summary for session# = 1\nLOGMINER: Number of processes = 3, Transaction Chunk Size = 201\nLOGMINER: Memory Size = 100M, Checkpoint interval = 500M\nLOGMINER: SpillScn 5047808, ResetLogScn 1920977\n2021-04-02T04:12:34.548329-03:00\nLOGMINER: summary for session# = 1\nLOGMINER: StartScn: 0 (0x0000000000000000)\nLOGMINER: EndScn: 0 (0x0000000000000000)\nLOGMINER: HighConsumedScn: 5047795 (0x00000000004d05f3)\nLOGMINER: PSR flags: 0x1\nLOGMINER: Session Flags: 0xba110dc\nLOGMINER: Session Flags2: 0x4000\nLOGMINER: Read buffers: 16\nLOGMINER: Region Queue size: 512\nLOGMINER: Redo Queue size: 8192\nLOGMINER: Memory LWM: limit 10M, LWM 90M, 90%\nLOGMINER: Memory Release Limit: 1M\nLOGMINER: Max Decomp Region Memory: 1M\nLOGMINER: Transaction Queue Size: 1024\n2021-04-02T04:12:34.641219-03:00\nLOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=63 OS id=5225 sid=107 started\n2021-04-02T04:12:34.652602-03:00\nLOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=64 OS id=5227 sid=73 started\n2021-04-02T04:12:34.664954-03:00\nLOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=65 OS id=5229 sid=75 started\n2021-04-02T04:12:34.871126-03:00\nLOGSTDBY Analyzer process AS00 started with server id=0 pid=66 OS id=5231\n2021-04-02T04:12:34.887789-03:00\nLOGSTDBY Apply process AS04 started with server id=4 pid=70 OS id=5239\n2021-04-02T04:12:34.890309-03:00\nLOGSTDBY Apply process AS01 started with server id=1 pid=67 OS id=5233\n2021-04-02T04:12:34.891321-03:00\n<\/pre><\/div>\n\n\n
Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laborat\u00f3rio pessoal para fins de aprendizado.<\/p>\n","protected":false},"excerpt":{"rendered":"
Quando temos um Data Guard configurado em nosso ambiente, com o Standby do tipo Logical, podemos inicializar o SQL Apply conforme op\u00e7oes abaixo. Antes disso, checando se o primary e standby est\u00e3o com as inst\u00e2ncias e listeners inicializados: No standby: No alert.log do standby, j\u00e1 \u00e9 poss\u00edvel ver o Oracle iniciando o LogMiner dos archived […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-3433","post","type-post","status-publish","format-standard","hentry","category-high-availability"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3433","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=3433"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3433\/revisions"}],"predecessor-version":[{"id":9192,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3433\/revisions\/9192"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=3433"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=3433"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=3433"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}