Overview
Ensure that you read and validate the prerequisites before creating a trigger-based replication from Oracle.
A database trigger is code that is automatically executed in response to certain events on a database table. To define a trigger-based replication (mirroring or synchronization), you need to provide information in the Source and/or Target Connection wizards so that triggers can be created to log table changes for replication.
For each table involved in the replication, Syniti Replicate creates 3 triggers in the source table that fire when a specific event occurs on a record:
• INSERT trigger which fires when a new record is being inserted in the table
• UPDATE trigger which fires when a record is modified
• DELETE trigger which fires when a record is deleted
If the replication is later deleted, the triggers are removed by Syniti Replicate. However, note that if you change a replication from mirroring to refresh, the triggers on the source table are not deleted. All transactions will continue to be recorded in the log tables. If you are not planning to reset the replication to mirroring, it is better to delete the replication, so that the triggers are removed, and create a new refresh replication.
User Permissions
This section includes information on the minimum permissions needed to set up and run mirroring where triggers are used to identify changed data.
This section includes information on the minimum permissions needed to set up and run mirroring where triggers are used to identify changed data. For precise details on syntax for Oracle, check the following resources:
(OPTIONAL) CREATION OF TABLESPACE
The recommended approach is to assign a tablespace for the Master table and Log Tables so that it is easier to control log table sizes. Note that Syniti Replicate does not create a tablespace, but the user can specify access to a tablespace. If you want to have a table space named SYNITIDR, you must create it beforehand:
create tablespace <tablespace> datafile <data_file> size <size>CREATE USER FOR SYNITI REPLICATE OPERATIONS AND TABLESPACE PERMISSIONS
The DBA should create a user for Syniti Replicate operations on the machine, then grant privileges to a specific tablespace with the command below. The Oracle user ID you are planning to use should have sufficient permissions to complete all operations in Syniti Replicate: permissions to connect, select tables, insert/update/delete records and so on. Once a tablespace has been created, you can decide whether to grant unlimited quota to the user:
alter user <UID> quota unlimited on <TBLSPACE>; Alternatively, just define a quota limit on a specific tablespace:
alter user <UID> QUOTA 100M on <TBLSPACE>;CREATE USER FOR SYNITI REPLICATE OPERATIONS
The DBA should create a user for Syniti Replicate operations on the database. The user ID you are planning to use should have sufficient permissions to complete all operations in Syniti Replicate: permissions to connect, select tables, insert/update/delete records and so on.
1. AUTHORITY TO SELECT TABLES
Syniti Replicate runs a SELECT statement to identify records to replicate. Therefore, the user ID used to make a connection must have adequate authority to run a SELECT statement for tables involved in replication. The command is not needed if the user is the owner of the table (i.e., created the table.)
2. AUTHORITY TO CREATE AND DROP TRIGGERS
To set up transactional replications, you need permission to create triggers in the schema where the source data table resides.
3. AUTHORITY TO CREATE AND DROP SEQUENCES
You need permission to create sequences as part of the setup of the Syniti Replicate master table.
grant create session to replicate_user; or grant connect to replicate_user;
grant select on SYS.ALL_USERS to replicate_user;
grant select on SYS.ALL_TABLES to replicate_user;
grant select on SYS.ALL_TAB_COMMENTS to replicate_user;
grant select on SYS.ALL_OBJECTS to replicate_user;
grant select on SYS.ALL_VIEWS to replicate_user;
grant select on SYS.ALL_TAB_COLUMNS to replicate_user;
grant select on SYS.ALL_COL_COMMENTS to replicate_user;
grant select on SYS.ALL_CONSTRAINTS to replicate_user;
grant select on SYS.ALL_CONS_COLUMNS to replicate_user;
grant select on SYS.ALL_IND_COLUMNS to replicate_user;
grant select on SYS.ALL_INDEXES to replicate_user;
grant select on SYS.USER_CONSTRAINTS to replicate_user;
grant select on SYS.USER_CONS_COLUMNS to replicate_user;
grant select on <tables_name> to replicate_user;
- Transactional Permissions -
grant create any trigger to replicate_user;
grant drop any trigger to replicate_user;
grant select any trigger to replicate_user;
grant select on SYS.ALL_TABLES to replicate_user;
grant select on SYS.ALL_TRIGGERS to replicate_user;
grant select on SYS.ALL_SEQUENCES to replicate_user;
grant create any index to replicate_user;
grant create any sequence to replicate_user;
grant drop any sequence to replicate_user;
grant select any sequence to replicate_user;ACCESS TRIGGER CONFIG TABLE
You need to access the trigger configuration information stored in the table _DBM__TRG_OBJS. Note that if you revoke read access, the replication works but it is unable to read the last ID.
grant select on <DEFAULT_SCHEMA>."_DBM__TRG_OBJS" to <UID>;For more information on Triggers-based replication, refer to Triggers.
Using Oracle Autonomous Database
If using Oracle Autonomous Database (either as a target or as a source with triggers), the following settings are needed:
• tnsnames.ora and sqlnet.ora should be placed in the ORACLE_HOME folder. Sample of ORACLE_HOME path: C:\app\client\user_name\product\12.2.0\client_1\network\admin
• Sqlnet.ora should NOT have: “SSL_SERVER_DN_MATCH=yes”. Sample of sqlnet.ora text where “DIRECTORY” is the path where the cwallet.sso file is present
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="C:\app\client\user_name\product\12.2.0\client_1\network\admin")))
The client IP should be given access to the DB instance by adding it to the list in the Database’s “Access Control List”.