Geekly Articles each Day
📜 ⬆️ ⬇️

Configuring Oracle Two-Way Database Synchronization (Oracle Streams)



What to do if there is no disk shelf under RAC, and you need to ensure the fault tolerance of the Oracle database in real time? Customize Oracle Data Streams (Oracle Streams)!

Much has been written about data streams. For example, here , here and here , including in the official documentation . This article describes a configuration option without using redo-logs, which works for Oracle Standard Edition One and Oracle Standard Edition 11gR2 editions (the Enterprise license makes setting up easier, right from the Enterprise Manager Console, see restrictions ).

So, we strive for automatic interception of DML operations in all tables of the HR schema and data synchronization in both directions:
')

  1. Setting the database system parameters:

    sqlplus / as sysdba ALTER SYSTEM SET global_names = true scope= both; ALTER SYSTEM SET shared_pool_size = 256M scope= both; ALTER SYSTEM SET streams_pool_size = 256M scope= both; ALTER SYSTEM SET java_pool_size = 100M scope= both; ALTER SYSTEM SET parallel_max_servers= 10 scope= both;
  2. Configuring Oracle Net Services
    tnsnames.ora:
     ORCLA = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = host_a)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcla.local) ) ) ORCLB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = host_b)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclb.local) ) )

  3. Creating a Streams admin table space:

     mkdir C:\Oracle\app\oradata\strems sqlplus / as sysdba --    CREATE TABLESPACE streams_tbs DATAFILE 'C:\Oracle\app\oradata\strems\streams_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
  4. Creating administrator streams with Oracle Streams privileges:

     sqlplus / as sysdba --   Streams CREATE USER strmadmin IDENTIFIED BY "<>" DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs; --    GRANT CONNECT TO strmadmin; GRANT DBA TO strmadmin; BEGIN --   Streams DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE( grantee => 'strmadmin', grant_privileges => TRUE); END; / execute MGMT_USER.MAKE_EM_USER('STRMADMIN');
  5. Creating database links (database links):

     sqlplus strmadmin@orcla CREATE DATABASE LINK orclb CONNECT TO strmadmin IDENTIFIED BY "<>" USING 'orclb'; sqlplus strmadmin@orclb CREATE DATABASE LINK orcla CONNECT TO strmadmin IDENTIFIED BY "<>" USING 'orcla'; --     SELECT * FROM ALL_DB_LINKS; --     SELECT * FROM DUAL@ORCLB; SELECT * FROM TAB@ORCLB;
  6. Creating Streams queues to be used to store changes in the database:

     sqlplus strmadmin@orcla begin --   (4 ANYDATA queues at each database) dbms_streams_adm.set_up_queue@orcla( queue_table=> 'strmadmin.apply_queue_table_a', queue_name => 'strmadmin.apply_queue_a', queue_user => 'strmadmin'); dbms_streams_adm.set_up_queue@orcla( queue_table=> 'strmadmin.capture_queue_table_a', queue_name => 'strmadmin.capture_queue_a', queue_user => 'strmadmin'); dbms_streams_adm.set_up_queue@orclb( queue_table=> 'strmadmin.apply_queue_table_b', queue_name => 'strmadmin.apply_queue_b', queue_user => 'strmadmin'); dbms_streams_adm.set_up_queue@orclb( queue_table=> 'strmadmin.capture_queue_table_b', queue_name => 'strmadmin.capture_queue_b', queue_user => 'strmadmin'); end; /
  7. Creating application processes (Apply process):

     sqlplus strmadmin@orcla begin --    dbms_apply_adm.create_apply@orcla( queue_name => 'strmadmin.apply_queue_a', apply_name => 'apply_a', source_database=> 'orclb', apply_captured => false); dbms_apply_adm.create_apply@orclb( queue_name => 'strmadmin.apply_queue_b', apply_name => 'apply_b', source_database=> 'orcla', apply_captured => false); end; /
  8. Setting additional application parameters ( details ):

     sqlplus strmadmin@orcla begin --       dbms_apply_adm.set_parameter@orcla( apply_name => 'apply_a', parameter => 'disable_on_error', value => 'n'); dbms_apply_adm.set_parameter@orclb( apply_name => 'apply_b', parameter => 'disable_on_error', value => 'n'); --       dbms_apply_adm.set_parameter@orcla( apply_name => 'apply_a', parameter => 'compare_key_only', value => 'y'); dbms_apply_adm.set_parameter@orclb( apply_name => 'apply_b', parameter => 'compare_key_only', value => 'y'); end; /
  9. Creating synchronous capture table synchronization processes (Synchronized capture):

     sqlplus strmadmin@orcla SET SERVEROUTPUT ON DECLARE V_SN VARCHAR2(255); --      CURSOR GET_TABLES_CUR IS SELECT table_name FROM all_tables WHERE owner = '<   >' ORDER BY table_name; BEGIN DBMS_OUTPUT.ENABLE; --   V_SN := '<   >'; FOR V_GT IN GET_TABLES_CUR LOOP --        (Synchronous capture) dbms_streams_adm.add_table_rules@orcla( table_name => V_SN || '.' || V_GT.table_name, streams_type => 'sync_capture', streams_name => 'sca' || V_GT.table_name, queue_name => 'strmadmin.capture_queue_a', include_dml => true, include_ddl => false, source_database=> 'orcla'); dbms_streams_adm.add_table_rules@orclb( table_name => V_SN || '.' || V_GT.table_name, streams_type => 'sync_capture', streams_name => 'scb' || V_GT.table_name, queue_name => 'strmadmin.capture_queue_b', include_dml => true, include_ddl => false, source_database=> 'orclb'); END LOOP; COMMIT; END; /
  10. Propagation process:

     sqlplus strmadmin@orcla SET SERVEROUTPUT ON DECLARE V_SN VARCHAR2(255); CURSOR GET_TABLES_CUR IS SELECT table_name FROM all_tables WHERE owner = '<   >' ORDER BY table_name; BEGIN DBMS_OUTPUT.ENABLE; --   V_SN := '<   >'; FOR V_GT IN GET_TABLES_CUR LOOP --    dbms_streams_adm.add_table_propagation_rules@orcla( table_name => V_SN || '.' || V_GT.table_name, streams_name => 'prop_a', source_queue_name => 'strmadmin.capture_queue_a', destination_queue_name => 'strmadmin.apply_queue_b@orclb', source_database => 'orcla', queue_to_queue => true); dbms_streams_adm.add_table_propagation_rules@orclb( table_name => V_SN || '.' || V_GT.table_name, streams_name => 'prop_b', source_queue_name => 'strmadmin.capture_queue_b', destination_queue_name => 'strmadmin.apply_queue_a@orcla', source_database => 'orclb', queue_to_queue => true); END LOOP; COMMIT; END; /
  11. Initial table synchronization (if necessary):

     --   drop table "repl_user"."<  >"@orclb cascade constraints; --       «b» create or replace directory b_dir as 'c:\oracle\app\oradata'; --  SCN   «a» select owner, directory_name, directory_path from all_directories; --     select dbms_flashback.get_system_change_number@orcla() from dual; impdp strmadmin/<>@orclb directory=b_dir network_link=orcla tables=<   >.< > flashback_scn=2025750
  12. Setting control points (necessary to start a change capture report):

     sqlplus strmadmin@orcla SET SERVEROUTPUT ON DECLARE V_SN VARCHAR2(255); CURSOR GET_TABLES_CUR IS SELECT table_name FROM all_tables WHERE owner = '<   >' ORDER BY table_name; iscn_a number; iscn_b number; BEGIN DBMS_OUTPUT.ENABLE; --   V_SN := '<   >'; iscn_a := dbms_flashback.get_system_change_number@orcla(); iscn_b := dbms_flashback.get_system_change_number@orclb(); FOR V_GT IN GET_TABLES_CUR LOOP --  SCN   dbms_apply_adm.set_table_instantiation_scn@orcla( source_object_name => V_SN || '.' || V_GT.table_name, source_database_name => 'orclb', instantiation_scn => iscn_b); dbms_apply_adm.set_table_instantiation_scn@orclb( source_object_name => V_SN || '.' || V_GT.table_name, source_database_name => 'orcla', instantiation_scn => iscn_a); END LOOP; COMMIT; END; /
  13. Launch application and transfer processes:

     sqlplus strmadmin@orcla begin --    dbms_apply_adm.stop_apply@orcla( apply_name => 'apply_a'); dbms_propagation_adm.stop_propagation@orcla( propagation_name=> 'prop_a', force => false); dbms_apply_adm.stop_apply@orclb( apply_name => 'apply_b'); dbms_propagation_adm.stop_propagation@orclb( propagation_name=> 'prop_b', force => false); --    dbms_apply_adm.start_apply@orcla( apply_name => 'apply_a'); dbms_propagation_adm.start_propagation@orcla( propagation_name=> 'prop_a'); dbms_apply_adm.start_apply@orclb( apply_name => 'apply_b'); dbms_propagation_adm.start_propagation@orclb( propagation_name=> 'prop_b'); end; /
  14. Setup is complete.
  15. A description of exceptions is given in “Troubleshooting Streams Apply Errors ORA-1403, ORA-26787 or ORA-26786, Conflict Resolution (Doc ID 265201.1)” and in “Streams Conflict Resolution (Doc ID 230049.1)”

Source: https://habr.com/ru/post/238521/

More articles:


All Articles