Oracle as a Target

Overview

This article describes the setup process using Oracle as a target system in a replication using Refresh or Mirroring mode.

User Permissions

When setting up replications that use Oracle as a source database, you need to be sure that the user ID used for making connections to the database has sufficient privileges to complete all the operations required for Syniti Replicate to perform a replication.

The following user permissions are required when using Oracle as a Source in Refresh (Snapshot) mode:

• AUTHORITY TO CONNECT TO DATABASE: This should already be granted when the user is created.

• 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.)

• AUTHORITY TO UPDATE TABLES, CREATE TABLES: To create a target table in the Management Center (as part of the Create Table Wizard), Syniti Replicate requires the permissions below. You first need to grant a quota on the tablespace in which you want to create a table or index. You can either grant unlimited tablespace to the user:

  grant unlimited tablespace to <uid>; 

• AUTHORITY TO DROP TABLES, ALTER TABLES: The use of these commands from within Syniti Replicate is entirely optional (i.e. not necessary for running a refresh replication.) They are used if you choose to remove a table from Oracle or change the table via the Management Center SQL Query tab.

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
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;
grant create on <table> to replicate_user;
grant insert, update, delete on <table> to replicate_user;
grant alter on <table> to replicate_user;
grant drop on <table> to replicate_user;

Data Type Mapping

When Oracle is used as a target system, the following is the mapping of Syniti Replicate data types to the corresponding data type in Oracle.