Dispon\u00edvel para ambientes do tipo “Physical Standby”, o Active Data Guard permite que o processo de Redo Apply fique ativo, mesmo que o o banco Standby esteja aberto. Essa op\u00e7\u00e3o exige um licenciamento espec\u00edfico para ser utilizada. Desse modo, conseguimos realizar no Standby: consultas (Select), rodar procedures e functions PL\/SQL (desde que n\u00e3o executem nenhuma manipula\u00e7\u00e3o de dados), utilizar DB_LINKS, usar o “SET ROLE, ALTER SESSION, ALTER SYSTEM” e ainda DMLs em tabelas do tipo Global Temporary (o que ajuda naqueles tipos de aplica\u00e7\u00f5es de relat\u00f3rios que criam objetos tempor\u00e1rios). Por\u00e9m, n\u00e3o conseguimos fazer DMLs, DDLs, acessar local sequences e DMLs em tabelas do tipo local temporary. Neste artigo vamos explorar como habilitar o Active Data Guard usando o utilit\u00e1rio SQL *Plus.<\/p>\n\n\n\n
Checando os bancos de dados envolvidos:<\/p>\n\n\n
\n[oracle@fornix1 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 1 04:40:03 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 04:40:36 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 MOUNTED PHYSICAL STANDBY\n<\/pre><\/div>\n\n\nVamos parar o Redo Apply no Standby:<\/p>\n\n\n
\n[oracle@fornix2 trace]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 1 04:42:59 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 RECOVER MANAGED STANDBY DATABASE CANCEL;\n \nDatabase altered.\n<\/pre><\/div>\n\n\nAbrindo o banco do Standby:<\/p>\n\n\n
\nSQL> ALTER DATABASE OPEN;\n \nDatabase altered.\n<\/pre><\/div>\n\n\nIniciando processo de Redo Apply:<\/p>\n\n\n
\nSQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;\n \nDatabase altered.\n<\/pre><\/div>\n\n\nPercebemos que o Open_Mode do Standby \u00e9 alterado:<\/p>\n\n\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 \nSQL> SELECT INSTANCE_NAME,STATUS FROM V$INSTANCE;\n \nINSTANCE_NAME STATUS\n---------------- ------------\nCORTEXDR OPEN\n<\/pre><\/div>\n\n\nUma vez habilitado, \u00e9 poss\u00edvel monitorarmos o “Apply Lag” neste tipo de ambiente tamb\u00e9m conhecido como Real-Time Query environment:<\/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 04:52:05 06\/01\/2021 04:52:07\n<\/pre><\/div>\n\n\nPara realizarmos um teste, vamos criar uma tabela no banco Primary com um registro:<\/p>\n\n\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 ambiente Standby, j\u00e1 podemos notar que a tabela e seu registro j\u00e1 est\u00e3o dispon\u00edveis para consumo:<\/p>\n\n\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 \nSQL> SELECT * FROM SOE.TESTE1;\n \nDESCRICAO\n--------------------\nACTIVE DATA GUARD\n<\/pre><\/div>\n\n\nDropando tabela do Primary:<\/p>\n\n\n
\nSQL> DROP TABLE SOE.TESTE1;\n \nTable dropped.\n<\/pre><\/div>\n\n\nNo standby j\u00e1 vemos o reflexo imediato:<\/p>\n\n\n
\nSQL> SELECT * FROM SOE.TESTE1;\nSELECT * FROM SOE.TESTE1\n *\nERROR at line 1:\nORA-00942: table or view does not exist\n<\/pre><\/div>\n\n\nPara voltarmos o ambiente para o Physical Standby tradicional, basta pararmos o Redo Apply, monstarmos o banco e ligarmos novamente o Redo Apply:<\/p>\n\n\n
\nSQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;\n \nDatabase altered.\n \nSQL> SHU IMMEDIATE;\nDatabase closed.\nDatabase dismounted.\nORACLE instance shut down.\nSQL> STARTUP MOUNT;\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.\nSQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;\n \nDatabase altered.\n<\/pre><\/div>\n\n\nValidando Standby:<\/p>\n\n\n
\nSQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;\n \nNAME OPEN_MODE DATABASE_ROLE\n--------- -------------------- ----------------\nCORTEX MOUNTED PHYSICAL STANDBY\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":"
Dispon\u00edvel para ambientes do tipo “Physical Standby”, o Active Data Guard permite que o processo de Redo Apply fique ativo, mesmo que o o banco Standby esteja aberto. Essa op\u00e7\u00e3o exige um licenciamento espec\u00edfico para ser utilizada. Desse modo, conseguimos realizar no Standby: consultas (Select), rodar procedures e functions PL\/SQL (desde que n\u00e3o executem nenhuma […]<\/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-5089","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\/5089","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=5089"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5089\/revisions"}],"predecessor-version":[{"id":9117,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5089\/revisions\/9117"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=5089"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=5089"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=5089"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}