Para relembrar, nosso Data Guard com Logical Standby replicar\u00e1 todos os objetos, exceto em 3 situa\u00e7\u00f5es: <\/p>\n\n\n\n
\nSQL> SELECT DISTINCT(OWNER) FROM DBA_LOGSTDBY_SKIP WHERE STATEMENT_OPT='INTERNAL SCHEMA';\n \nOWNER\n--------------------------------------------------------------------------------\nAPPQOSSYS\nDBSNMP\nGSMCATUSER\nSYS\nSYSTEM\nXS$NULL\nANONYMOUS\nDBSFWUSER\nGGSYS\nGSMUSER\nREMOTE_SCHEDULER_AGENT\n \nOWNER\n--------------------------------------------------------------------------------\nSYSBACKUP\nCTXSYS\nGSMROOTUSER\nSYSRAC\nAUDSYS\nDVF\nDVSYS\nOJVMSYS\nSI_INFORMTN_SCHEMA\nDIP\nGSMADMIN_INTERNAL\n \nOWNER\n--------------------------------------------------------------------------------\nORDPLUGINS\nLBACSYS\nMDSYS\nOLAPSYS\nORDDATA\nSYSKM\nORACLE_OCM\nOUTLN\nSYS$UMF\nORDSYS\nSYSDG\n \nOWNER\n--------------------------------------------------------------------------------\nWMSYS\nXDB\n \n35 rows selected.\n<\/pre><\/div>\n\n\n\nSQL> SELECT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED;\n \nOWNER\n--------------------------------------------------------------------------------\nTABLE_NAME\n--------------------------------------------------------------------------------\nSOE\nBSS\n<\/pre><\/div>\n\n\nSQL> SELECT * FROM LOGSTDBY_UNSUPPORTED_TABLES;\n \nOWNER\n--------------------------------------------------------------------------------\nTABLE_NAME\n--------------------------------------------------------------------------------\nSOE\nBSS\n<\/pre><\/div>\n\n\n
- Regras criadas de forma expl\u00edcita pelo DBA, por vari\u00e1veis motivos. \u00c9 sobre essa terceita categoria que irei explorar neste artigo.<\/li><\/ul>\n\n\n\n
Para isso, vamos criar uma tabela de testes no ambiente primary, vide abaixo:<\/p>\n\n\n
\n[oracle@fornix1 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 04:47:12 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> CREATE TABLE SOE.MERCURIO ( ID NUMBER PRIMARY KEY, NOME VARCHAR2(30)) ;\n \nTable created.\n \nSQL> INSERT INTO SOE.MERCURIO VALUES (1, 'STRING A') ;\n \n1 row created.\n \nSQL> INSERT INTO SOE.MERCURIO VALUES (2, 'STRING B') ;\n \n1 row created.\n \nSQL> INSERT INTO SOE.MERCURIO VALUES (3, 'STRING C') ;\n \n1 row created.\n \nSQL> COMMIT;\n \nCommit complete.\n \nSQL> ALTER SYSTEM SWITCH LOGFILE;\n \nSystem altered.\n<\/pre><\/div>\n\n\nConfirmando que o SQL Apply j\u00e1 replicou o objeto para o Logical Standby:<\/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:48:41 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 OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_NAME='MERCURIO';\n \nOBJECT_NAME\n--------------------------------------------------------------------------------\nOBJECT_TYPE\n-----------------------\nMERCURIO\nTABLE\n<\/pre><\/div>\n\n\nCriando regra de Skipp desta tabela no Primary:<\/p>\n\n\n
\n[oracle@fornix1 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 04:53:53 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> exec DBMS_LOGSTDBY.SKIP(stmt=>'DML',schema_name=>'SOE',object_name =>'MERCURIO');\n \nPL\/SQL procedure successfully completed.\n \nSQL> exec DBMS_LOGSTDBY.SKIP(stmt=>'SCHEMA_DDL',schema_name=>'SOE',object_name =>'MERCURIO');\n \nPL\/SQL procedure successfully completed.\n<\/pre><\/div>\n\n\nCriando a regra tamb\u00e9m no Standby (realizando o stop e start do SQL Apply):<\/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:54:48 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 \nSQL> exec DBMS_LOGSTDBY.SKIP(stmt=>'DML',schema_name=>'SOE',object_name =>'MERCURIO');\n \nPL\/SQL procedure successfully completed.\n \nSQL> exec DBMS_LOGSTDBY.SKIP(stmt=>'SCHEMA_DDL',schema_name=>'SOE',object_name =>'MERCURIO');\n \nPL\/SQL procedure successfully completed.\n \nSQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;\n \nDatabase altered.\n<\/pre><\/div>\n\n\nCom a query abaixo \u00e9 poss\u00edvel listarmos todos os objetos e regras que est\u00e3o criados para obedecer o skipp:<\/p>\n\n\n
\nSQL> select OWNER, STATEMENT_OPT , NAME, USE_LIKE, ESC from DBA_LOGSTDBY_SKIP WHERE OWNER='SOE';\n \nOWNER\n--------------------------------------------------------------------------------\nSTATEMENT_OPT\n--------------------------------------------------------------------------------\nNAME\n--------------------------------------------------------------------------------\nU E\n- -\nSOE\nDML\nMERCURIO\nY\n \n \nOWNER\n--------------------------------------------------------------------------------\nSTATEMENT_OPT\n--------------------------------------------------------------------------------\nNAME\n--------------------------------------------------------------------------------\nU E\n- -\nSOE\nSCHEMA_DDL\nMERCURIO\nY\n<\/pre><\/div>\n\n\nAdicionando 2 registros na tabela no primary:<\/p>\n\n\n
\nSQL> INSERT INTO SOE.MERCURIO VALUES (4, 'STRING D') ;\n \n1 row created.\n \nSQL> INSERT INTO SOE.MERCURIO VALUES (5, 'STRING E') ;\n \n1 row created.\n \nSQL> COMMIT;\n \nCommit complete.\n \nSQL> ALTER SYSTEM SWITCH LOGFILE;\n \nSystem altered.\n<\/pre><\/div>\n\n\nImportante salientar que a regra de Skipp n\u00e3o impede que os dados sejam emitidos do primary para o standby, mas sim que o Standby n\u00e3o aplique o SQL Apply que contempla a regra. Mesmo aguardando um tempo, percebemos que os 2 novos registros n\u00e3o s\u00e3o replicados ao standby (o que era esperado):<\/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 05:01: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> select count(*) from SOE.MERCURIO;\n \n COUNT(*)\n----------\n 3\n<\/pre><\/div>\n\n\nComo criamos regras tamb\u00e9m para opera\u00e7\u00f5es de DDL nesta tabela, farei um truncate na mesma no primary:<\/p>\n\n\n
\n[oracle@fornix1 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 05:02:37 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> TRUNCATE TABLE SOE.MERCURIO;\n \nTable truncated.\n \nSQL> ALTER SYSTEM SWITCH LOGFILE;\n \nSystem altered.\n<\/pre><\/div>\n\n\nEsta opera\u00e7\u00e3o tamb\u00e9m n\u00e3o foi replicada ao Standby:<\/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 05:04:48 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 count(*) from SOE.MERCURIO;\n \n COUNT(*)\n----------\n 3\n<\/pre><\/div>\n\n\nO UN-SKIPP requer a cria\u00e7\u00e3o de um DB_LINK no standby apontando para o primary, que permitir\u00e1 o “instantiate” da tabela, que nada mais seria que a sua replica\u00e7\u00e3o total ao destino:<\/p>\n\n\n
\n[oracle@fornix2 admin]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 05:10:05 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> CREATE PUBLIC DATABASE LINK BRUNO_LINK CONNECT TO system IDENTIFIED BY oracle USING 'CORTEX';\n \nDatabase link created.\n \nSQL> select sysdate from dual@BRUNO_LINK ;\n \nSYSDATE\n-------------------\n2021-04-02:05:10:14\n<\/pre><\/div>\n\n\nRemovendo a regra no primary:<\/p>\n\n\n
\n[oracle@fornix1 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 05:11:37 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> exec DBMS_LOGSTDBY.UNSKIP(stmt=>'DML',schema_name=>'SOE',object_name =>'MERCURIO');\n \nPL\/SQL procedure successfully completed.\n \nSQL> exec DBMS_LOGSTDBY.UNSKIP(stmt=>'SCHEMA_DDL',schema_name=>'SOE',object_name =>'MERCURIO');\n \nPL\/SQL procedure successfully completed.\n<\/pre><\/div>\n\n\nRemovendo a regra no Standby:<\/p>\n\n\n
\n[oracle@fornix2 admin]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 05:12:15 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 \nSQL> exec DBMS_LOGSTDBY.UNSKIP(stmt=>'DML',schema_name=>'SOE',object_name =>'MERCURIO');\n \nPL\/SQL procedure successfully completed.\n \nSQL> exec DBMS_LOGSTDBY.UNSKIP(stmt=>'SCHEMA_DDL',schema_name=>'SOE',object_name =>'MERCURIO');\n \nPL\/SQL procedure successfully completed.\n<\/pre><\/div>\n\n\nComo havia truncado a tabela na origem, vou adicionar algumas linhas no primary:<\/p>\n\n\n
\n[oracle@fornix1 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 05:13:32 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> INSERT INTO SOE.MERCURIO VALUES (1, 'STRING A') ;\nINSERT INTO SOE.MERCURIO VALUES (2, 'STRING B') ;\nINSERT INTO SOE.MERCURIO VALUES (3, 'STRING C') ;\n1 row created.\n \nSQL>\n1 row created.\n \nSQL>\n \n1 row created.\n \nSQL> COMMIT;\n \nCommit complete.\n<\/pre><\/div>\n\n\nNo standby, realizando o instantiate da tabela:<\/p>\n\n\n
\n[oracle@fornix2 admin]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 05:14:42 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> EXEC DBMS_LOGSTDBY.INSTANTIATE_TABLE (SCHEMA_NAME => 'SOE', TABLE_NAME => 'MERCURIO', DBLINK => 'BRUNO_LINK');\n \nPL\/SQL procedure successfully completed.\n<\/pre><\/div>\n\n\nInicializando o SQL Apply e confirmando que a tabela foi replicada com sucesso:<\/p>\n\n\n
\nSQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;\n \nDatabase altered.\n \nSQL> SELECT COUNT(*) FROM SOE.MERCURIO;\n \n COUNT(*)\n----------\n 3\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":"
Para relembrar, nosso Data Guard com Logical Standby replicar\u00e1 todos os objetos, exceto em 3 situa\u00e7\u00f5es: Objetos de schemas internos (como SYS e SYSTEM): \u00e9 poss\u00edvel checarmos essa lista usando a query abaixo no primary: Objetos que possuam algum datatype incompat\u00edvel (que podem ser coletados nas views DBA_LOGSTDBY_UNSUPPORTED e LOGSTDBY_UNSUPPORTED_TABLES): Regras criadas de forma expl\u00edcita […]<\/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-3447","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\/3447","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=3447"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3447\/revisions"}],"predecessor-version":[{"id":9191,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3447\/revisions\/9191"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=3447"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=3447"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=3447"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}