Transferring data from an Oracle source endpoint

To prepare the source for the Snapshot transfer:

1. Create a user account the transfer will use to connect to the source:

   CREATE USER <username> IDENTIFIED BY <password>;
   GRANT CREATE SESSION TO <username>;
   

2. Grant privileges to the created user:

   GRANT SELECT ON V$DATABASE TO <username>;
   GRANT SELECT ON DBA_EXTENTS TO <username>;
   GRANT SELECT ON DBA_OBJECTS TO <username>;
   GRANT FLASHBACK ANY TABLE TO <username>;
   

   If required, you can only grant the FLASHBACK privileges to the tables you need to copy rather than to ANY TABLE.

3. Grant the user the privilege to read the tables to be copied.

To prepare the source for the Replication transfer:

1. Create a user account the transfer will use to connect to the source:

   CREATE USER <username> IDENTIFIED BY <password>;
   ALTER USER <username> DEFAULT tablespace USERS TEMPORARY tablespace TEMP;
   ALTER USER <username> quote unlimited on USERS;
   
   GRANT
       CREATE SESSION,
       execute_catalog_role,
       SELECT ANY TRANSACTION,
       SELECT ANY DISCTIONARY,
       CREATE PROCEDURE,
       LOGMINING
   TO <username>;
   

2. Grant privileges to the created user:

   GRANT SELECT ON V$DATABASE TO <username>;
   GRANT SELECT ON V$LOG TO <username>;
   GRANT SELECT ON V$LOGFILE TO <username>;
   GRANT SELECT ON V$ARCHIVED_LOG TO <username>;
   
   GRANT SELECT ON dba_objects TO <username>;
   GRANT SELECT ON dba_extents TO <username>;
   
   GRANT EXECUTE ON SYS.DBMS_LOGMNR TO <username>;
   GRANT SELECT ON SYSTEM.LOGMNR_COL$ TO <username>;
   GRANT SELECT ON SYSTEM.LOGMNR_OBJ$ TO <username>;
   GRANT SELECT ON SYSTEM.LOGMNR_USER$ TO <username>;
   GRANT SELECT ON SYSTEM.LOGMNR_UID$ TO <username>;
   

3. Grant the user the privilege to read the tables to be replicated.

4. Enable Minimal Supplemental Logging with primary keys as follows:

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
   

If you are using the CDB environment, configure the following settings:

1. Create a user Common User:

   CREATE USER C##<username> IDENTIFIED BY <password> CONTAINER=all;
   ALTER USER C##<username> DEFAULT TABLESPACE USERS temporary tablespace TEMP CONTAINER=all;
   ALTER USER C##<username> quota unlimited on USERS CONTAINER=all;
   ALTER USER C##<username> SET container_data = (cdb$root, <your_PCB_name>) CONTAINER=current;
   
   GRANT
       CREATE SESSION,
       execute_catalog_role,
       SELECT ANY TRANSACTION,
       SELECT ANY DICTIONALY,
       CREATE PROCEDURE,
       LOGMINING,
       SET CONTAINER
   TO C##<username> CONTAINER=ALL;
   

   If required, you can only specify the cdb$root container and the container with the tables you need to transfer.

2. To allow the user to switch to the cdb$root container, grant them the ALTER SESSION privileges:

   GRANT ALTER SESSION TO C##<username>;
   

3. Grant privileges to the created user:

   GRANT SELECT ON V$DATABASE TO C##<username> CONTAINER=ALL;
   GRANT SELECT ON V$LOG TO C##<username> CONTAINER=ALL;
   GRANT SELECT ON V$LOGFILE TO C##<username> CONTAINER=ALL;
   GRANT SELECT ON V$ARCHIVED_LOG TO C##<username> CONTAINER=ALL;
   
   GRANT SELECT ON dba_objects TO C##<username> CONTAINER=ALL;
   GRANT SELECT ON dba_extents TO C##<username> CONTAINER=ALL;
   
   GRANT EXECUTE ON SYS.DBMS_LOGMNR TO C##<username> CONTAINER=ALL;
   GRANT SELECT ON SYSTEM.LOGMNR_COL$ TO C##<username> CONTAINER=ALL;
   GRANT SELECT ON SYSTEM.LOGMNR_OBJ$ TO C##<username> CONTAINER=ALL;
   GRANT SELECT ON SYSTEM.LOGMNR_USER$ TO C##<username> CONTAINER=ALL;
   GRANT SELECT ON SYSTEM.LOGMNR_UID$ TO C##<username> CONTAINER=ALL;
   

Connection type: Select the Oracle cluster connection type.

For the Instance connection type:

• Host: Enter the IP address or FQDN of the host you want to connect to.

• Port: Set the number of the port that Data Transfer will use for the connection.

• Type: Specify Oracle SID or Service Name in the respective field below.

For the TNS connect string connection type, specify the Oracle RDBMS connection string in the respective field below.

User: Specify the username that Data Transfer will use to connect to the database.

Password: Enter the user's password to the database.

PDB container: Specify the name of the PDB container to connect to. Leave the field empty if you are not using the CDB environment.

Included tables: Data is only transferred from listed tables. This option is specified using regular expressions.

Excluded tables: Data from the listed tables is not transferred. This option is specified using regular expressions.

The lists include the name of the schema (description of DB contents, structure, and integrity constraints) and the table name. Both lists support expressions in the following format:

• <schema_name>.<table_name>: Fully qualified table name.
• <schema_name>.*: All tables in the specified schema.
• <table_name>: Table in the default schema.

Included and excluded table names must meet the ID naming rules in Oracle. For more information, see the Oracle documentation. Double quotes within a table name are not supported. Escaping outer double quotes is not required.

Convert the "NUMBER" type to "Int64": Enable this option to convert the type from NUMBER (without parameters) to INT64. Parameterized NUMBER (precision, scale) types are automatically converted to integer types whenever possible.