Perform the following steps to prepare and use an Oracle source for replication:
Oracle source preparation
•Define the following system environment variables on the Linux or Windows system where the Secure Agent runs:
- ORACLE_HOME environment variable. Points to the Oracle client installation directory, one level up from the bin directory on Windows or lib directory on Linux. This environment variable is not required. However, if you do not define it, you'll need to specify the full Oracle client installation path when you define other environment variables or Secure Agent properties that specify a path to a subdirectory.
- TNS_ADMIN environment variable. If you specify a TNS name in the Database Connect String property of the Oracle Database Ingestion connection properties, use this environment variable to point to the directory location of the tsnnames.ora file when the file is not in the default $ORACLE_HOME/network/admin directory. The tnsnames.ora file, along with the Oracle Call Interface (OCI), is used to communicate with the Oracle source database.
•Make sure the user has the Oracle privileges that are required for the database ingestion load type to be performed.
Note: For combined initial and incremental loads, make sure that the GRANT FLASHBACK privilege is issued for each selected source table or use the ANY TABLE option. Replication tasks use an Oracle Flashback Query, which consists of a SELECT AS OF scn statement, to query for row data from source tables in the Oracle database. Oracle requires the GRANT FLASHBACK privilege for this query to be used.
•Replication tasks require read access to Oracle online and archive redo logs to read incremental change data. If the redo logs are remote from the on-premises system where the Secure Agent runs, make sure that read access to the logs is provided, for example, by using Oracle Automatic Storage Management (ASM), mounting the logs to a network file system (NFS), or configuring BFILE access to logs that are on the Oracle file system.
•If you plan to read data from redo log files in Oracle ASM, set the sqlnet.recv_timeout parameter in the local sqlnet.ora file to less than 5 minutes. This parameter specifies the duration of time that the Oracle client waits for a response from ASM before a query times out. Network interrupts and other factors can occasionally make Oracle connections unresponsive. Setting this value ensures that the reader can respond and recover from any such situation in a timely manner.
•If you use Oracle 11.2.04, set the Oracle COMPATIBLE initialization parameter to 11.2.04 to ensure that Oracle has all of the most current Redo Logs fixes for that release.
•Ensure that the Oracle Database Client or Instant Client is installed and configured on the Secure Agent server for the Secure Agent to communicate with Oracle. If you do not already have an Oracle client installed, you can download a client and access installation information from the Oracle web site, or ask your Oracle DBA to download and configure an Oracle client.
•For incremental load or combined initial and incremental load operations, perform the following prerequisite tasks in Oracle:
- Enable ARCHIVELOG mode for the Oracle database. If the database is not in an Amazon RDS environment, issue the following SQL statements:
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; SHUTDOWN IMMEDIATE; STARTUP;
For an Amazon RDS for Oracle databases, set the backup retention period to place the database in ARCHIVELOG mode and enable automated backups.
- Define an archive log destination.
- Enable Oracle minimal global supplemental logging on the source database.
- If your Oracle source tables have primary keys, ensure that supplemental logging is enabled for all primary key columns. For source tables that do not have primary keys, ensure that supplemental logging is enabled for all columns from which change data will be captured.
- Ensure that the Oracle MAX_STRING_SIZE initialization parameter is not set to EXTENDED. If it is set to EXTENDED, replication tasks will not be able to replicate inserts and updates for tables containing columns defined with large (extended size) VARCHAR2, NVARCHAR2, or RAW columns.
If you do not have the authority to perform these tasks, ask your Oracle database administrator to perform them. For more information, see the Oracle documentation.
•Ensure that the Oracle Database Client or Instant Client is installed and configured on the Secure Agent server for the Secure Agent to communicate with Oracle. If you do not already have an Oracle client installed, you can download a client and access installation information from the Oracle web site, or ask your Oracle DBA to download and configure an Oracle client.
•Amazon Relational Database Service (RDS) for Oracle source preparation:
1Create the ONLINELOG_DIR and ARCHIVELOG_DIR directories that will hold the online and archive redo logs, respectively, on the RDS file system. Use the following exec statements:
2Grant the Oracle privileges that are required for the Amazon RDS for Oracle source type to the replication user. For more information, see Oracle privileges.
3Define an appropriate retention time for the archived redo logs. Use the following exec statement:
4In the Amazon RDS console, set the backup retention period for the source database to a value greater than zero to enable automated backups of the database instance.
Note: This step enables ARCHIVELOG mode for the database.
5Ensure that supplemental logging is enabled at the database level. Use the following statement:
6Optionally, in the Amazon RDS console, you can create a parameter group and define the cache sizes of the default buffer pool. The default buffer pool holds buffers that use the primary database block size. Use the following DB_CACHE_SIZE parameter values:
▪ DB_2K_CACHE_SIZE
▪ DB_4K_CACHE_SIZE
▪ DB_16K_CACHE_SIZE
▪ DB_32K_CACHE_SIZE
Then select the parameter group for the source database.
Usage notes
•For each source table row to be unique, ensure that each table has a primary key. Do not use unique indexes in place of a primary key. If no primary key is specified, all columns are treated as if they are part of the primary key.
•For Oracle sources that use the multitenant architecture, the source tables must reside in a single pluggable database (PDB) within a multitenant container database (CDB).
•You can use Oracle Transparent Data Encryption (TDE) to encrypt data in tablespaces that contain Oracle source tables for incremental load processing. You can store the master encryption key in a TDE keystore that is in a file system, in ASM, or in an external hardware security module (HSM) that supply PKCS11 interfaces, such as Oracle Key Vault (OKV).
•Replication tasks support Oracle Data Guard logical and physical standby databases and far sync instances as sources.
•Replication tasks can process data across a RESETLOGS boundary. To avoid the source and targets becoming out of sync, you can stop capture processing before performing a RESETLOGS and then restart capture processing after the RESETLOGS event. Otherwise, the capture process might send data to the target that is subsequently reverted by the RESETLOGS event, causing the source and target to become out of sync.
•If an incremental load or combined initial and incremental load task contains an Oracle source table name or one or more column names that are longer than 30 characters, Oracle suppresses supplemental logging for the entire table, including primary keys and foreign keys. As a result, most operations on the table fail. This problem is caused by an Oracle restriction. In this situation, exclude the table from capture processing or rename the long table and column names to names of 30 characters or less.
•Replication tasks do not support the following Oracle source data types with any target type or any load type:
- "ANY types" such as ANYTYPE, ANYDATA, ANYDATASET
- Extended types
- INTERVAL
- JSON
- LOBs, except for BLOBs, CLOBs, and NCLOBs in initial load jobs that do not have Amazon S3, Google Cloud Storage, or Microsoft Azure Data Lake Storage Gen2 targets that use the CSV output format
- TIMESTAMP WITH LOCAL TIME ZONE
- UROWID
- XML-defined types such as XMLTYPE, URI types, URIFactory package subtypes
- Spatial types such as SDO_GEOMETRY
- User-defined types such as OBJECT, REF, VARRAY, nested table types
Source columns that have unsupported data types are excluded from the target definition.
•Replication tasks do not support invisible columns in Oracle source columns. For these columns, incremental load and combined initial and incremental load tasks propagate nulls to the corresponding target columns.
•If you update the primary key value in the Oracle source table for a record that does not exist in the target table, the record is not replicated to the target. However, the data is replicated to the target if the record already exists in the target table before the update of the primary key value.
•If an Update to an Oracle table does not change an existing column value, the Update row is ignored and not applied to the target.
•Supplemental logging settings for tables might be ignored by Oracle if the table name or any table column name is longer than 30 characters. In this case, incremental load or combined load tasks might have unpredictable results.
Oracle privileges
To deploy and run a replication task that has an Oracle source, the source connection must specify a user who has the required privileges.
Privileges for incremental load processing
For a replication task that performs an incremental load or combined initial and incremental load, ensure that the replication user (rep_user) has been granted the following privileges:
Note: If the Oracle logs are managed by ASM, the user must have SYSASM or SYSDBA authority.
GRANT CREATE SESSION TO <rep_user>;
GRANT SELECT ON table TO <rep_user>; -- For each source table created by user GRANT EXECUTE ON DBMS_FLASHBACK TO <rep_user>;
-- The following grant is required for combined initial and incremental loads only. Do not -- use ANY TABLE unless your security policy allows it. GRANT FLASHBACK ON table|ANY TABLE TO <rep_user>;
GRANT SELECT ON DBA_CONSTRAINTS TO <rep_user>; GRANT SELECT ON DBA_CONS_COLUMNS TO <rep_user>; GRANT SELECT ON DBA_INDEXES TO <rep_user>; GRANT SELECT ON DBA_LOG_GROUPS TO <rep_user>; GRANT SELECT ON DBA_LOG_GROUP_COLUMNS TO <rep_user>; GRANT SELECT ON DBA_OBJECTS TO <rep_user>; GRANT SELECT ON DBA_OBJECT_TABLES TO <rep_user>; GRANT SELECT ON DBA_TABLES TO <rep_user>; GRANT SELECT ON DBA_TABLESPACES TO <rep_user>; GRANT SELECT ON DBA_USERS TO <rep_user>;
GRANT SELECT ON "PUBLIC".V$ARCHIVED_LOG TO <rep_user>; GRANT SELECT ON "PUBLIC".V$CONTAINERS TO <rep_user>; -- For Oracle multitenant environments GRANT SELECT ON "PUBLIC".V$DATABASE TO <rep_user>; GRANT SELECT ON "PUBLIC".V$DATABASE_INCARNATION TO <rep_user>; GRANT SELECT ON "PUBLIC".V$ENCRYPTION_WALLET TO <rep_user>; -- For Oracle TDE access GRANT SELECT ON "PUBLIC".V$LOG TO <rep_user>; GRANT SELECT ON "PUBLIC".V$LOGFILE TO <rep_user>; GRANT SELECT ON "PUBLIC".V$PARAMETER TO <rep_user>; GRANT SELECT ON "PUBLIC".V$PDBS TO <rep_user>; -- For Oracle multitenant environments GRANT SELECT ON "PUBLIC".V$SPPARAMETER TO <rep_user>; GRANT SELECT ON "PUBLIC".V$STANDBY_LOG TO <rep_user>; GRANT SELECT ON "PUBLIC".V$THREAD TO <rep_user>; GRANT SELECT ON "PUBLIC".V$TRANSACTION TO <rep_user>; GRANT SELECT ON "PUBLIC".V$TRANSPORTABLE_PLATFORM TO <rep_user>; GRANT SELECT ON "PUBLIC".V$VERSION TO <rep_user>;
GRANT SELECT ON SYS.ATTRCOL$ TO <rep_user>; GRANT SELECT ON SYS.CCOL$ TO <rep_user>; GRANT SELECT ON SYS.CDEF$ TO <rep_user>; GRANT SELECT ON SYS.COL$ TO <rep_user>; GRANT SELECT ON SYS.COLTYPE$ TO <rep_user>; GRANT SELECT ON SYS.IDNSEQ$ TO <rep_user>; GRANT SELECT ON SYS.IND$ TO <rep_user>; GRANT SELECT ON SYS.INDPART$ TO <rep_user>; GRANT SELECT ON SYS.OBJ$ TO <rep_user>; GRANT SELECT ON SYS.PARTOBJ$ TO <rep_user>; GRANT SELECT ON SYS.RECYCLEBIN$ TO <rep_user>; GRANT SELECT ON SYS.TAB$ TO <rep_user>; GRANT SELECT ON SYS.TABCOMPART$ TO <rep_user>; GRANT SELECT ON SYS.TABPART$ TO <rep_user>; GRANT SELECT ON SYS.TABSUBPART$ TO <rep_user>;
-- Also ensure that the user has access to the following ALL_* views: ALL_CONSTRAINTS ALL_CONS_COLUMNS ALL_ENCRYPTED_COLUMNS ALL_INDEXES ALL_IND_COLUMNS ALL_OBJECTS ALL_TABLES ALL_TAB_COLS ALL_TAB_PARTITIONS ALL_USERS
Privileges for initial load processing
For a replication task that performs an initial load, ensure that the user has the following privileges at minimum:
GRANT CREATE SESSION TO <rep_user>;
GRANT SELECT ON DBA_INDEXES TO <rep_user>; GRANT SELECT ON DBA_OBJECT_TABLES TO <rep_user>; GRANT SELECT ON DBA_OBJECTS TO rep_user; GRANT SELECT ON DBA_TABLES TO <rep_user>; GRANT SELECT ON DBA_USERS TO <rep_user>; GRANT SELECT ON DBA_VIEWS TO <rep_user>; -- Only if you unload data from views
GRANT SELECT ANY TABLE TO <rep_user>; -or- GRANT SELECT ON table TO <rep_user>; -- For each source table created by user
GRANT SELECT ON ALL_CONSTRAINTS TO <rep_user>; GRANT SELECT ON ALL_CONS_COLUMNS TO <rep_user>; GRANT SELECT ON ALL_ENCRYPTED_COLUMNS TO <rep_user>; GRANT SELECT ON ALL_IND_COLUMNS TO <rep_user>; GRANT SELECT ON ALL_INDEXES TO <rep_user>; GRANT SELECT ON ALL_OBJECTS TO <rep_user>; GRANT SELECT ON ALL_TAB_COLS TO <rep_user>; GRANT SELECT ON ALL_USERS TO <rep_user>;
GRANT SELECT ON "PUBLIC"."V$DATABASE" TO rep_user; GRANT SELECT ON "PUBLIC"."V$CONTAINERS" TO rep_user; GRANT SELECT ON SYS.ATTRCOL$ TO <rep_user>; GRANT SELECT ON SYS.CCOL$ TO <rep_user>; GRANT SELECT ON SYS.CDEF$ TO <rep_user>; GRANT SELECT ON SYS.COL$ TO <rep_user>; GRANT SELECT ON SYS.COLTYPE$ TO <rep_user>; GRANT SELECT ON SYS.IND$ TO <rep_user>; GRANT SELECT ON SYS.IDNSEQ$ TO rep_user; GRANT SELECT ON SYS.OBJ$ TO <rep_user>; GRANT SELECT ON SYS.RECYCLEBIN$ TO <rep_user>; GRANT SELECT ON SYS.TAB$ TO <rep_user>;
Privileges for Amazon RDS for Oracle sources
Important: You must log in to Amazon RDS under the master username to run GRANT statements and procedures.
Grant the SELECT privilege, at minimum, on objects and system tables that are required for change data capture processing to the replication user (rep_user). Some additional grants are required in certain situations.
Use the following GRANT statements:
GRANT SELECT ON "PUBLIC"."V$ARCHIVED_LOG" TO "cmid_user";
GRANT SELECT ON "PUBLIC"."V$DATABASE" TO "rep_user"; GRANT SELECT ON "PUBLIC"."V$LOG" TO "rep_user"; GRANT SELECT ON "PUBLIC"."V$LOGFILE" TO "rep_user"; GRANT SELECT ON "PUBLIC"."V$TRANSPORTABLE_PLATFORM" TO "rep_user"; GRANT SELECT ON "PUBLIC"."V$THREAD" TO "rep_user"; GRANT SELECT ON "PUBLIC"."V$DATABASE_INCARNATION" TO "rep_user"; GRANT SELECT ON "PUBLIC"."V$TRANSACTION" TO "rep_user";
GRANT SELECT ON "SYS"."DBA_CONS_COLUMNS" TO "rep_user"; GRANT SELECT ON "SYS"."DBA_CONSTRAINTS" TO "rep_user"; GRANT SELECT ON DBA_INDEXES TO "rep_user"; GRANT SELECT ON "SYS"."DBA_LOG_GROUP_COLUMNS" TO "rep_user"; GRANT SELECT ON "SYS"."DBA_TABLESPACES" TO "rep_user";
GRANT SELECT ON "SYS"."OBJ$" TO "rep_user"; GRANT SELECT ON "SYS"."TAB$" TO "rep_user"; GRANT SELECT ON "SYS"."IND$" TO "rep_user"; GRANT SELECT ON "SYS"."COL$" TO "rep_user";
GRANT SELECT ON "SYS"."PARTOBJ$" TO "rep_user"; GRANT SELECT ON "SYS"."TABPART$" TO "rep_user"; GRANT SELECT ON "SYS"."TABCOMPART$" TO "rep_user"; GRANT SELECT ON "SYS"."TABSUBPART$" TO "rep_user"; COMMIT;
/* For combined load jobs:*/ GRANT EXECUTE ON DBMS_FLASHBACK TO "rep_user";
/*To provide read access to the Amazon RDS online and archived redo logs:*/ GRANT READ ON DIRECTORY ONLINELOG_DIR TO "rep_user"; GRANT READ ON DIRECTORY ARCHIVELOG_DIR TO "rep_user";
Additionally, log in as the master user and run the following Amazon RDS procedures to grant the SELECT privilege on some more objects: