Em ambientes que possuam a op\u00e7\u00e3o de Active Data Guard habilitada, temos um recurso importante para controle de toler\u00e2ncia de Lag (de apply log), materializado em um par\u00e2metro de sess\u00e3o chamado STANDBY_MAX_DATA_DELAY. Neste artigo, vamos explorar em termos pr\u00e1ticos esse item.<\/p>\n\n\n\n
Validando ambiente que ser\u00e1 utilizado para testes:<\/p>\n\n\n
\n[oracle@fornix1 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 1 06:14:22 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 NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;\n \nNAME OPEN_MODE DATABASE_ROLE\n--------- -------------------- ----------------\nCORTEX READ WRITE PRIMARY\n<\/pre><\/div>\n\n\n[oracle@fornix2 trace]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 1 06:15:20 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 NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;\n \nNAME OPEN_MODE DATABASE_ROLE\n--------- -------------------- ----------------\nCORTEX READ ONLY WITH APPLY PHYSICAL STANDBY\n<\/pre><\/div>\n\n\nAtrav\u00e9s do teste abaixo, no banco Standby, constatamos que este par\u00e2metro n\u00e3o \u00e9 habilitado para usu\u00e1rios administrativos:<\/p>\n\n\n
\nSQL> SHOW USER;\nUSER is "SYS"\nSQL> ALTER SESSION SET STANDBY_MAX_DATA_DELAY=30;\nERROR:\nORA-03174: STANDBY_MAX_DATA_DELAY does not apply to SYS users\n<\/pre><\/div>\n\n\nO par\u00e2metro em quest\u00e3o possui 3 valores poss\u00edveis, sendo que o seu valor padr\u00e3o \u00e9 NONE, que significa que a consulta ser\u00e1 exibida no Standby independente se h\u00e1 delay ou n\u00e3o em rela\u00e7\u00e3o ao Primary. Vamos realizar o teste abaixo, criando um tabela e populando-a no Primary:<\/p>\n\n\n
\nSQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;\n \nNAME OPEN_MODE DATABASE_ROLE\n--------- -------------------- ----------------\nCORTEX READ WRITE PRIMARY\n \nSQL> CREATE TABLE SOE.TESTE1 (DESCRICAO VARCHAR2(20));\n \nTable created.\n \nSQL> INSERT INTO SOE.TESTE1 (DESCRICAO) VALUES ('ACTIVE DATA GUARD');\n \n1 row created.\n \nSQL> COMMIT;\n \nCommit complete.\n<\/pre><\/div>\n\n\nNo standby, logado com um usu\u00e1rio n\u00e3o administrativo, vamos definir o valor do par\u00e2metro para NONE e consultar a tabela recentemente criada:<\/p>\n\n\n
\nSQL> conn SOE\/soe\nConnected.\nSQL> SHOW USER;\nUSER is "SOE"\nSQL> ALTER SESSION SET STANDBY_MAX_DATA_DELAY=NONE;\n \nSession altered.\n \nSQL> SELECT * FROM SOE.TESTE1;\n \nDESCRICAO\n--------------------\nACTIVE DATA GUARD\n<\/pre><\/div>\n\n\nPor\u00e9m percebemos que n\u00e3o existe Lag no ambiente no momento:<\/p>\n\n\n
\nSQL> conn \/ as sysdba\nConnected.\nSQL> SELECT value "Lag", datum_time "Received Time", Time_Computed "Time Computed " FROM V$DATAGUARD_STATS WHERE name like 'apply lag';\n \nLag\n----------------------------------------------------------------\nReceived Time Time Computed\n------------------------------ ------------------------------\n+00 00:00:00\n06\/01\/2021 06:24:32 06\/01\/2021 06:24:33\n<\/pre><\/div>\n\n\nVamos parar o processo de Redo Apply no Standby:<\/p>\n\n\n
\nSQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;\n \nDatabase altered.\n<\/pre><\/div>\n\n\nInserindo mais um registro na tabela do Primary:<\/p>\n\n\n
\nSQL> INSERT INTO SOE.TESTE1 (DESCRICAO) VALUES ('SEM REDO APPLY');\n \n1 row created.\n \nSQL> COMMIT;\n \nCommit complete.\n<\/pre><\/div>\n\n\nRealizando novamente a consulta no Standby, percebemos que s\u00f3 1 registro \u00e9 exibido:<\/p>\n\n\n
\nSQL> SELECT * FROM SOE.TESTE1;\n \nDESCRICAO\n--------------------\nACTIVE DATA GUARD\n<\/pre><\/div>\n\n\nConstatamos que com o valor NONE, as consultas no Standby continuar\u00e3o a funcionar, mesmo se houver LAG entre o primary e standby (e mesmo que isso signifique diferen\u00e7a de dados):<\/p>\n\n\n
\nSQL> conn \/ as sysdba\nConnected.\nSQL> SELECT value "Lag", datum_time "Received Time", Time_Computed "Time Computed " FROM V$DATAGUARD_STATS WHERE name like 'apply lag';\n \nLag\n----------------------------------------------------------------\nReceived Time Time Computed\n------------------------------ ------------------------------\n+00 00:02:43\n06\/01\/2021 06:28:02 06\/01\/2021 06:28:03\n<\/pre><\/div>\n\n\nOutra possibilidade de nosso par\u00e2metro seria um valor que representa a quantidade de segundos aceit\u00e1veis para o Lag. Para realizar este teste, vou habilitar novamente o Redo Apply, definir 180 segundos de Delay, e desligar o Redo Apply e acompanhar o desfecho:<\/p>\n\n\n
\nSQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;\n \nDatabase altered.\n \nSQL> SELECT * FROM SOE.TESTE1;\n \nDESCRICAO\n--------------------\nACTIVE DATA GUARD\nSEM REDO APPLY\n<\/pre><\/div>\n\n\nParando Redo Apply:<\/p>\n\n\n
\nSQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;\n \nDatabase altered.\n<\/pre><\/div>\n\n\nDefinindo valor do par\u00e2metro em 180 segundos. Ou seja, conseguiremos consultar informa\u00e7\u00f5es que possuam at\u00e9 esse limite:<\/p>\n\n\n
\nSQL> conn SOE\/soe\nConnected.\nSQL> ALTER SESSION SET STANDBY_MAX_DATA_DELAY=180;\n \nSession altered.\n \nSQL> SELECT * FROM SOE.TESTE1;\n \nDESCRICAO\n--------------------\nACTIVE DATA GUARD\nSEM REDO APPLY\n \nSQL> conn \/ as sysdba\nConnected.\nSQL> SELECT value "Lag", datum_time "Received Time", Time_Computed "Time Computed " FROM V$DATAGUARD_STATS WHERE name like 'apply lag';\n \nLag\n----------------------------------------------------------------\nReceived Time Time Computed\n------------------------------ ------------------------------\n+00 00:01:58\n06\/01\/2021 06:33:51 06\/01\/2021 06:33:53\n<\/pre><\/div>\n\n\nEm paralelo vou criar o terceiro registro no Primary:<\/p>\n\n\n
\nSQL> INSERT INTO SOE.TESTE1 (DESCRICAO) VALUES ('BSS');\n \n1 row created.\n \nSQL> COMMIT;\n \nCommit complete.\n<\/pre><\/div>\n\n\nNo standby, como o Redo Apply est\u00e1 desligado, s\u00f3 conseguimos ver 2 registros (pois o Lag ainda n\u00e3o atingiu 180 segundos):<\/p>\n\n\n
\nSQL> SELECT * FROM SOE.TESTE1;\n \nDESCRICAO\n--------------------\nACTIVE DATA GUARD\nSEM REDO APPLY\n<\/pre><\/div>\n\n\nPor\u00e9m, uma vez o limite excedido, a mensagem abaixo \u00e9 reportada:<\/p>\n\n\n
\nSQL> conn SOE\/soe\nConnected.\nSQL> ALTER SESSION SET STANDBY_MAX_DATA_DELAY=180;\n \nSession altered.\n \nSQL> SELECT * FROM SOE.TESTE1;\nSELECT * FROM SOE.TESTE1\n*\nERROR at line 1:\nORA-03172: STANDBY_MAX_DATA_DELAY of 180 seconds exceeded\n<\/pre><\/div>\n\n\nO \u00faltimo valor poss\u00edvel de nosso par\u00e2metro seria 0, onde o objetivo \u00e9 garantir que a consulta no Standby esteja id\u00eantica caso fosse executada no primary, a menos que haja algum Lag, o que causaria erro. No exemplo abaixo, como a tabela no primary possui 3 registros e no standby apenas 2 (por conta do Redo Apply estar parado), uma mensagem \u00e9 reportada:<\/p>\n\n\n
\nPRIMARY:\n \nSQL> select * from SOE.TESTE1;\n \nDESCRICAO\n--------------------\nACTIVE DATA GUARD\nSEM REDO APPLY\nBSS\n \nSTANDBY:\n \nSQL> SELECT value "Lag", datum_time "Received Time", Time_Computed "Time Computed " FROM V$DATAGUARD_STATS WHERE name like 'apply lag';\n \nLag\n----------------------------------------------------------------\nReceived Time Time Computed\n------------------------------ ------------------------------\n+00 13:01:11\n06\/01\/2021 19:33:10 06\/01\/2021 19:33:10\n \n \nSQL> conn SOE\/soe\nConnected.\nSQL> ALTER SESSION SET STANDBY_MAX_DATA_DELAY=0;\n \nSession altered.\n \nSQL> SELECT * FROM SOE.TESTE1;\nSELECT * FROM SOE.TESTE1\n*\nERROR at line 1:\nORA-03172: STANDBY_MAX_DATA_DELAY of 0 seconds exceeded\n<\/pre><\/div>\n\n\nVamos habilitar o Redo Apply:<\/p>\n\n\n
\nSQL> conn \/ as sysdba\nConnected.\nSQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;\n \nDatabase altered.\n<\/pre><\/div>\n\n\nRealizando novamente a consulta no Standby. Mesmo assim o erro ainda \u00e9 reportado, nos dando a impress\u00e3o que ainda h\u00e1 algum lag (mesmo que nossa consulta de refer\u00eancia mostre que n\u00e3o):<\/p>\n\n\n
\nSQL> SELECT value "Lag", datum_time "Received Time", Time_Computed "Time Computed " FROM V$DATAGUARD_STATS WHERE name like 'apply lag';\n \nLag\n----------------------------------------------------------------\nReceived Time Time Computed\n------------------------------ ------------------------------\n+00 00:00:00\n06\/01\/2021 19:45:30 06\/01\/2021 19:45:31\n \n \nSQL> conn SOE\/soe\nConnected.\nSQL> ALTER SESSION SET STANDBY_MAX_DATA_DELAY=0;\n \nSession altered.\n \nSQL> SELECT * FROM SOE.TESTE1;\nSELECT * FROM SOE.TESTE1\n *\nERROR at line 1:\nORA-00604: error occurred at recursive SQL level 1\nORA-03172: STANDBY_MAX_DATA_DELAY of 0 seconds exceeded\n \n \nSQL> alter session sync with primary;\nERROR:\nORA-03173: Standby may not be synced with primary\n<\/pre><\/div>\n\n\nLogo percebemos que o “SYNC WITH PRIMARY” s\u00f3 \u00e9 permitido quando o Transport Service est\u00e1 no modo SYNC, que n\u00e3o \u00e9 o nosso caso:<\/p>\n\n\n
\nlog_archive_dest_2 string SERVICE=cortexDR ASYNC VALID_F\n OR=(ONLINE_LOGFILES,PRIMARY_RO\n LE) DB_UNIQUE_NAME=cortexDR\n<\/pre><\/div>\n\n\nVou parar o Redo Apply e alterar este par\u00e2metro no prim\u00e1rio:<\/p>\n\n\n
\nSTANDBY:\n \nSQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;\n \nDatabase altered.\n \nPRIMARY:\n \nSQL> ALTER SYSTEM SET log_archive_dest_2='SERVICE=cortexDR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cortexDR';\n \nSystem altered.\n<\/pre><\/div>\n\n\nAo iniciar Redo Apply no Standby e fazer o teste novamente, o mesmo erro \u00e9 reportado. Desse modo, alteramos o par\u00e2metro “archive_lag_target” no banco Primary para o valor 0:<\/p>\n\n\n
\nSQL> ALTER SYSTEM SET archive_lag_target=0;\n \nSystem altered.\n \nSQL> shu immediate;\nDatabase closed.\nDatabase dismounted.\nORACLE instance shut down.\nSQL> startup;\nORACLE instance started.\n \nTotal System Global Area 2583690520 bytes\nFixed Size 8899864 bytes\nVariable Size 553648128 bytes\nDatabase Buffers 2013265920 bytes\nRedo Buffers 7876608 bytes\nDatabase mounted.\nDatabase opened.\n<\/pre><\/div>\n\n\nTestando novamente no Standby:<\/p>\n\n\n
\nSQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;\n \nDatabase altered.\n \nSQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;\n \nDatabase altered.\n \nSQL> conn SOE\/soe\nConnected.\nSQL> ALTER SESSION SET STANDBY_MAX_DATA_DELAY=0;\n \nSession altered.\n \nSQL> SELECT * FROM SOE.TESTE1;\n \nDESCRICAO\n--------------------\nACTIVE DATA GUARD\nSEM REDO APPLY\nBSS\n<\/pre><\/div>\n\n\nObs: 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":"
Em ambientes que possuam a op\u00e7\u00e3o de Active Data Guard habilitada, temos um recurso importante para controle de toler\u00e2ncia de Lag (de apply log), materializado em um par\u00e2metro de sess\u00e3o chamado STANDBY_MAX_DATA_DELAY. Neste artigo, vamos explorar em termos pr\u00e1ticos esse item. Validando ambiente que ser\u00e1 utilizado para testes: Atrav\u00e9s do teste abaixo, no banco Standby, […]<\/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-5109","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\/5109","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=5109"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5109\/revisions"}],"predecessor-version":[{"id":9116,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5109\/revisions\/9116"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=5109"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=5109"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=5109"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}