{"id":3540,"date":"2021-04-06T07:35:18","date_gmt":"2021-04-06T07:35:18","guid":{"rendered":"https:\/\/swiv.com.br\/logical-standby-using-triggers-to-replicate-unsupported-tables\/"},"modified":"2026-05-27T20:02:33","modified_gmt":"2026-05-27T19:02:33","slug":"logical-standby-using-triggers-to-replicate-unsupported-tables","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/04\/06\/logical-standby-using-triggers-to-replicate-unsupported-tables\/","title":{"rendered":"Logical Standby: using triggers to replicate Unsupported Tables"},"content":{"rendered":"

Este artigo reproduz uma implementa\u00e7\u00e3o muito simples, que \u00e9 dada de exemplo na documenta\u00e7\u00e3o da Oracle, para os casos onde temos tabelas n\u00e3o suportadas nativamente pelo SQL Apply, e que precisam de uma interven\u00e7\u00e3o manual para que seus dados sejam replicados. Essa opera\u00e7\u00e3o consiste basicamente na cria\u00e7\u00e3o de uma tabela paralela, e as triggers que far\u00e3o o trabalho de replica\u00e7\u00e3o em cada evento de DML na tabela de origem. O exemplo foi extra\u00eddo DESTE<\/a> material oficial.<\/p>\n\n\n

Criando um objeto do tipo TYPE no primary e uma tabela que o utiliza em uma de suas colunas:<\/p>\n\n\n

\n[oracle@fornix1 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 4 04:09: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> create or replace type SOE.PERSON as object\n(\nFIRSTNAME varchar2(50), LASTNAME varchar2(50), BIRTHDATE Date );\n\/  2    3    4\n \nType created.\n \nSQL> create table SOE.PERSONS\n(\nIDNUMBER varchar2(10) , DEPARTMENT varchar2(50), INFO SOE.PERSON );  2    3\n \nTable created.\n<\/pre><\/div>\n\n\n
Criando a “shadow table” no primary que ser\u00e1 populada via trigger:<\/p>\n\n\n
\nSQL> create table SOE.PERSONS_LOG\n(\nT_IDNUMBER varchar2(10),\nT_DEPARTMENT varchar2(50),\nT_FIRSTNAME varchar2(50),\nT_LASTNAME varchar2(50),\nT_BIRTHDATE Date\n);  2    3    4    5    6    7    8\n \nTable created.\n<\/pre><\/div>\n\n\n
Criando a trigger no primary que popular\u00e1 a shadow table:<\/p>\n\n\n
\nSQL> CONN SOE\nEnter password:\nConnected.\nSQL> SHOW USER;\nUSER is "SOE"\nSQL> create or replace trigger flatten_persons\n        after insert on persons for each row\ndeclare\nbegin\n        insert into persons_log\n         (t_IdNumber, t_Department, t_FirstName, t_LastName, t_BirthDate)\n        values\n         (:new.IdNumber, :new.Department,\n        :new.Info.FirstName,:new.Info.LastName, :new.Info.BirthDate);\nend;\n\/  2    3    4    5    6    7    8    9   10   11\n \nTrigger created.\n<\/pre><\/div>\n\n\n
Criando a trigger no primary que popular\u00e1 a tabela no ambiente standby de destino:<\/p>\n\n\n
\nSQL> create or replace trigger reconstruct_persons\n        after insert on persons_log for each row\nbegin\n        insert into Persons (IdNumber, Department, Info)\n        values (:new.t_IdNumber, :new.t_Department,\n        Person(:new.t_FirstName, :new.t_LastName, :new.t_BirthDate));\nend;\n\/  2    3    4    5    6    7    8\n \nTrigger created.\n<\/pre><\/div>\n\n\n
Rodando o comando abaixo no primary, deixamos configurado para que a trigger possa ser disparada e alimente o standby:<\/p>\n\n\n
\nSQL> exec dbms_ddl.set_trigger_firing_property( trig_owner => 'SOE', trig_name => 'RECONSTRUCT_PERSONS', property => dbms_ddl.apply_server_only, setting => TRUE);\n \nPL\/SQL procedure successfully completed.\n<\/pre><\/div>\n\n\n
Gerando archives no primary:<\/p>\n\n\n
\nSQL> CONN \/ AS SYSDBA\nConnected.\nSQL> ALTER SYSTEM SWITCH LOGFILE;\n \nSystem altered.\n \nSQL> \/\n \nSystem altered.\n \nSQL> \/\n \nSystem altered.\n<\/pre><\/div>\n\n\n
Confirmando que os objetos foram replicados para o standby database:<\/p>\n\n\n
\n[oracle@fornix2 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 6 04:23:21 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 dba_objects where object_name in ('PERSONS', 'PERSONS_LOG');\n \n  COUNT(*)\n----------\n         2\n<\/pre><\/div>\n\n\n
Para testar a replica\u00e7\u00e3o, vamos inserir 1 registro na tabela do primary:<\/p>\n\n\n
\n[oracle@fornix1 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 6 04:25:38 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.Persons (IdNumber, Department, Info)\nvalues (1, 'Database' ,\nSOE.Person('Bruno', 'Silva', trunc(sysdate)));  2    3\n \n1 row created.\n \nSQL> COMMIT;\n \nCommit complete.\n \nSQL> ALTER SYSTEM SWITCH LOGFILE;\n \nSystem altered.\n<\/pre><\/div>\n\n\n
No logical standby, a tabela e conte\u00fado j\u00e1 foram replicados com sucesso:<\/p>\n\n\n
\nSQL> select count(*) from SOE.persons;\n \n  COUNT(*)\n----------\n         2\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":"
Este artigo reproduz uma implementa\u00e7\u00e3o muito simples, que \u00e9 dada de exemplo na documenta\u00e7\u00e3o da Oracle, para os casos onde temos tabelas n\u00e3o suportadas nativamente pelo SQL Apply, e que precisam de uma interven\u00e7\u00e3o manual para que seus dados sejam replicados. Essa opera\u00e7\u00e3o consiste basicamente na cria\u00e7\u00e3o de uma tabela paralela, e as triggers que […]<\/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-3540","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\/3540","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=3540"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3540\/revisions"}],"predecessor-version":[{"id":9187,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3540\/revisions\/9187"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=3540"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=3540"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=3540"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}