Database Ingestion and Replication > Database Ingestion and Replication sources - preparation and usage > Oracle sources
  

Oracle sources

To use Oracle sources in database ingestion and replication tasks, first prepare the source database and review the usage considerations.

Source preparation

Amazon Relational Database Service (RDS) for Oracle source preparation:
  1. 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:
    2. exec rdsadmin.rdsadmin_master_util.create_archivelog_dir;
    exec rdsadmin.rdsadmin_master_util.create_onlinelog_dir;
  2. 2Grant the Oracle privileges that are required for the Amazon RDS for Oracle source type to the Database Ingestion and Replication user.
    3. For more information about the privileges required for an Amazon RDS for Oracle source, see Oracle privileges for Amazon RDS for Oracle sources.
  3. 3Define an appropriate retention time for the archived redo logs. Use the following exec statement:
    4. exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention days',number_of_days);
  4. 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.
    5. Note: This step enables ARCHIVELOG mode for the database.
  5. 5Ensure that supplemental logging is enabled at the database level. Use the following statement:
    6. exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');
    When you create a database ingestion and replication task, you can generate a script to enable supplemental logging for the selected source tables.
  6. 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:
    7. Then select the parameter group for the source database.

Usage considerations

Gathering Information About the Database Ingestion and Replication environment

Before you start creating database ingestion and replication tasks, gather the following information:

General Information

What Oracle version do you use?
Answer: ___________________________________________________
Do you run Oracle on premises or in a cloud-based Amazon RDS for Oracle environment?
Answer: ___________________________________________________
What is the target type?
Answer: ___________________________________________________
What type of load operation do you plan to perform: an initial load (point-in-time bulk load), incremental load (only the changes), or combined initial and incremental load (initial load followed by incremental load)?
Answer: ___________________________________________________
What are the number of cores, memory, and disk space on the system where the Secure Agent will run?
Answer: ___________________________________________________

Oracle environment

What are the host name and port number of the Oracle source database server?
Answer: ___________________________________________________
What is the Oracle system identifier (SID) for the database?
Answer: ___________________________________________________
What are the Oracle database user name and password to use for connecting to the database?
Answer: ___________________________________________________
Is the Oracle Database Client or Instant Client installed on the system where the Secure Agent will run?
Answer: ___________________________________________________
Does the database run in an Oracle Real Application Cluster (RAC)? What's the maximum number of RAC members, including inactive nodes?
Answer: ___________________________________________________
Do you need to capture change data from an Oracle Data Guard logical or physical standby database?
Answer: ___________________________________________________
Do you need to capture change data from tables in a pluggable database (PDB) in an Oracle multitenant environment?
Answer: ___________________________________________________
Do you need to capture change data from tablespaces that use Oracle Transparent Data Encryption (TDE)? If yes, what are the TDE wallet directory and password?
Answer: ___________________________________________________
What is the typical size of units of work (UOWs) for the source tables?
Answer: ___________________________________________________

Oracle redo logs

Are the redo logs in an Oracle Automatic Storage Management (ASM) environment? If you plan to connect to an ASM instance to read redo logs, are you allowed to create a login user ID for ASM that has SYSDBA or SYSASM authority?
Answer: ___________________________________________________
Are ARCHIVELOG mode and minimal global supplemental logging enabled for the Oracle source database? If not, can they be enabled?
Answer: ___________________________________________________
What are the primary and secondary archive log destinations for the archived redo logs from which you want to read change data?
Answer: ___________________________________________________
What is the average amount of archived redo log that is created per hour during peak and non-peak periods for the Oracle database?
Answer: ___________________________________________________
Do you have read access to the redo logs in your environment?
Answer: ___________________________________________________
If you do not have the authority to read the redo logs directly, can the archived redo log files be copied to shared disk or to a file system from which you can access them?
Answer: ___________________________________________________
Do you want Database Ingestion and Replication to read change data from the online log as well as the archived logs?
Answer: ___________________________________________________
Can you make your archived redo logs available for diagnostic use by Informatica Global Customer Support, if necessary, to diagnose an error or anomaly during CDC processing?
Answer: ___________________________________________________

More details for configuring database ingestion

What is the schema name for the source tables from which to replicate data?
Answer: ___________________________________________________
Do you want to replicate data from all tables in the schema or a subset of those tables? If a subset, create a list of them.
Answer: ___________________________________________________
Do the source tables have primary keys? Can supplemental logging be enabled for all of the primary keys?
Answer: ___________________________________________________
Do you have any unkeyed source tables?
Answer: ___________________________________________________
Do the source tables contain columns that have unsupported data types? To determine which data types are not supported for your source types, see the source-specific topics under "Database Ingestion and Replication source considerations" in the Database Ingestion and Replication help.
Answer: ___________________________________________________
Is the default code page of UTF-8 acceptable? If not, which code page do you want to use?
Answer: ___________________________________________________
Do you want to use SSL to encrypt data exchanged between the Secure Agent and database server? Which encryption SSL or TLS protocol do you use?
Answer: ___________________________________________________
Are you allowed to create a new Oracle user and assign the privileges that Database Ingestion and Replication requires to that user? Determine the user name to use.
Answer: ___________________________________________________
Do the source tables contain any Oracle data types that Database Ingestion and Replication does not support?
Answer: ___________________________________________________
Do you want to capture schema drift changes on the source, including add, drop, modify, and rename column operations?
Answer: ___________________________________________________

Oracle privileges

To deploy and run a database ingestion and replication task that has an Oracle source, the source connection must specify a Database Ingestion and Replication user who has the privileges required for the ingestion load type.
Privileges for incremental load processing with log-based CDC
Note: If the Oracle logs are managed by ASM, Informatica recommends that the user has the SYSDBA privilege, which is the minimum system privilege that allows read only access to the ASM file system.
For a database ingestion and replication task that performs an incremental load or combined initial and incremental load using the log-based CDC method, ensure that the Database Ingestion and Replication user (cmid_user) has been granted the following privileges:
GRANT CREATE SESSION TO <cmid_user>;

GRANT SELECT ON table TO <cmid_user>; -- For each source table created by user

-- Include the following grant only if you want to Execute the CDC script for enabling
-- supplemental logging from the user interface. If you manually enable supplemental
-- logging, this grant is not needed.
GRANT ALTER table|ANY TABLE TO <cmid_user>;

GRANT SELECT ON DBA_CONSTRAINTS TO <cmid_user>;
GRANT SELECT ON DBA_CONS_COLUMNS TO <cmid_user>;
GRANT SELECT ON DBA_INDEXES TO <cmid_user>;
GRANT SELECT ON DBA_LOG_GROUPS TO <cmid_user>;
GRANT SELECT ON DBA_LOG_GROUP_COLUMNS TO <cmid_user>;
GRANT SELECT ON DBA_OBJECTS TO <cmid_user>;
GRANT SELECT ON DBA_OBJECT_TABLES TO <cmid_user>;
GRANT SELECT ON DBA_TABLES TO <cmid_user>;
GRANT SELECT ON DBA_TABLESPACES TO <cmid_user>;
GRANT SELECT ON DBA_USERS TO <cmid_user>;

GRANT SELECT ON "PUBLIC".V$ARCHIVED_LOG TO <cmid_user>;
GRANT SELECT ON "PUBLIC".V$CONTAINERS TO <cmid_user>;
GRANT SELECT ON "PUBLIC".V$DATABASE TO <cmid_user>;
GRANT SELECT ON "PUBLIC".V$DATABASE_INCARNATION TO <cmid_user>;
GRANT SELECT ON "PUBLIC".V$ENCRYPTION_WALLET TO <cmid_user>; -- For Oracle TDE access
GRANT SELECT ON "PUBLIC".V$LOG TO <cmid_user>;
GRANT SELECT ON "PUBLIC".V$LOGFILE TO <cmid_user>;
GRANT SELECT ON "PUBLIC".V$PARAMETER TO <cmid_user>;
GRANT SELECT ON "PUBLIC".V$PDBS TO <cmid_user>;
GRANT SELECT ON "PUBLIC".V$SPPARAMETER TO <cmid_user>;
GRANT SELECT ON "PUBLIC".V$STANDBY_LOG TO <cmid_user>;
GRANT SELECT ON "PUBLIC".V$THREAD TO <cmid_user>;
GRANT SELECT ON "PUBLIC".GV$TRANSACTION TO <cmid_user>;
GRANT SELECT ON "PUBLIC".V$TRANSPORTABLE_PLATFORM TO <cmid_user>;
GRANT SELECT ON "PUBLIC".V$VERSION TO <cmid_user>;

GRANT SELECT ON SYS.ATTRCOL$ TO <cmid_user>;
GRANT SELECT ON SYS.CCOL$ TO <cmid_user>;
GRANT SELECT ON SYS.CDEF$ TO <cmid_user>;
GRANT SELECT ON SYS.COL$ TO <cmid_user>;
GRANT SELECT ON SYS.COLTYPE$ TO <cmid_user>;
GRANT SELECT ON SYS.IDNSEQ$ TO <cmid_user>;
GRANT SELECT ON SYS.IND$ TO <cmid_user>;
GRANT SELECT ON SYS.INDPART$ TO <cmid_user>;
GRANT SELECT ON SYS.OBJ$ TO <cmid_user>;
GRANT SELECT ON SYS.PARTOBJ$ TO <cmid_user>;
GRANT SELECT ON SYS.RECYCLEBIN$ TO <cmid_user>;
GRANT SELECT ON SYS.TAB$ TO <cmid_user>;
GRANT SELECT ON SYS.TABCOMPART$ TO <cmid_user>;
GRANT SELECT ON SYS.TABPART$ TO <cmid_user>;
GRANT SELECT ON SYS.TABSUBPART$ TO <cmid_user>;

-- Also ensure that you have 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 incremental load processing with query-based CDC
For a database ingestion and replication task that performs an incremental load or combined initial and incremental load using the query-based CDC method, ensure that the user has the following privileges at minimum:
GRANT CREATE SESSION TO <cmid_user>;

GRANT SELECT ON DBA_INDEXES TO <cmid_user>;
GRANT SELECT ON DBA_OBJECT_TABLES TO <cmid_user>;
GRANT SELECT ON DBA_OBJECTS TO cmid_user;
GRANT SELECT ON DBA_TABLES TO <cmid_user>;
GRANT SELECT ON DBA_USERS TO <cmid_user>;
GRANT SELECT ON DBA_VIEWS TO <cmid_user>; -- Only if you unload data from views

GRANT SELECT ANY TABLE TO <cmid_user>;
-or-
GRANT SELECT ON table TO <cmid_user>; -- For each source table created by user

GRANT SELECT ON ALL_CONSTRAINTS TO <cmid_user>;
GRANT SELECT ON ALL_CONS_COLUMNS TO <cmid_user>;
GRANT SELECT ON ALL_ENCRYPTED_COLUMNS TO <cmid_user>;
GRANT SELECT ON ALL_IND_COLUMNS TO <cmid_user>;
GRANT SELECT ON ALL_INDEXES TO <cmid_user>;
GRANT SELECT ON ALL_OBJECTS TO <cmid_user>;
GRANT SELECT ON ALL_TAB_COLS TO <cmid_user>;
GRANT SELECT ON ALL_USERS TO <cmid_user>;

GRANT SELECT ON "PUBLIC"."V$DATABASE" TO cmid_user;
GRANT SELECT ON "PUBLIC"."V$CONTAINERS" TO cmid_user;
GRANT SELECT ON SYS.ATTRCOL$ TO <cmid_user>;
GRANT SELECT ON SYS.CCOL$ TO <cmid_user>;
GRANT SELECT ON SYS.CDEF$ TO <cmid_user>;
GRANT SELECT ON SYS.COL$ TO <cmid_user>;
GRANT SELECT ON SYS.COLTYPE$ TO <cmid_user>;
GRANT SELECT ON SYS.IND$ TO <cmid_user>;
GRANT SELECT ON SYS.IDNSEQ$ TO cmid_user;
GRANT SELECT ON SYS.OBJ$ TO <cmid_user>;
GRANT SELECT ON SYS.RECYCLEBIN$ TO <cmid_user>;
GRANT SELECT ON SYS.TAB$ TO <cmid_user>;
Privileges for initial load processing
For a database ingestion and replication task that performs an initial load, ensure that the user has the following privileges at minimum:
GRANT CREATE SESSION TO <cmid_user>;

GRANT SELECT ON DBA_INDEXES TO <cmid_user>;
GRANT SELECT ON DBA_OBJECT_TABLES TO <cmid_user>;
GRANT SELECT ON DBA_OBJECTS TO cmid_user;
GRANT SELECT ON DBA_TABLES TO <cmid_user>;
GRANT SELECT ON DBA_USERS TO <cmid_user>;
GRANT SELECT ON DBA_VIEWS TO <cmid_user>; -- Only if you unload data from views

GRANT SELECT ANY TABLE TO <cmid_user>;
-or-
GRANT SELECT ON table TO <cmid_user>; -- For each source table created by user

GRANT SELECT ON ALL_CONSTRAINTS TO <cmid_user>;
GRANT SELECT ON ALL_CONS_COLUMNS TO <cmid_user>;
GRANT SELECT ON ALL_ENCRYPTED_COLUMNS TO <cmid_user>;
GRANT SELECT ON ALL_IND_COLUMNS TO <cmid_user>;
GRANT SELECT ON ALL_INDEXES TO <cmid_user>;
GRANT SELECT ON ALL_OBJECTS TO <cmid_user>;
GRANT SELECT ON ALL_TAB_COLS TO <cmid_user>;
GRANT SELECT ON ALL_USERS TO <cmid_user>;

GRANT SELECT ON "PUBLIC"."V$DATABASE" TO cmid_user;
GRANT SELECT ON "PUBLIC"."V$CONTAINERS" TO cmid_user;
GRANT SELECT ON SYS.ATTRCOL$ TO <cmid_user>;
GRANT SELECT ON SYS.CCOL$ TO <cmid_user>;
GRANT SELECT ON SYS.CDEF$ TO <cmid_user>;
GRANT SELECT ON SYS.COL$ TO <cmid_user>;
GRANT SELECT ON SYS.COLTYPE$ TO <cmid_user>;
GRANT SELECT ON SYS.IND$ TO <cmid_user>;
GRANT SELECT ON SYS.IDNSEQ$ TO cmid_user;
GRANT SELECT ON SYS.OBJ$ TO <cmid_user>;
GRANT SELECT ON SYS.RECYCLEBIN$ TO <cmid_user>;
GRANT SELECT ON SYS.TAB$ TO <cmid_user>;

Oracle privileges for Amazon RDS for Oracle sources

If you have an Amazon RDS for Oracle source, you must grant certain privileges to the Database Ingestion and Replication user.
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 CDC processing to the Database Ingestion and Replication user (cmid_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 "cmid_user";
GRANT SELECT ON "PUBLIC"."V$LOG" TO "cmid_user";
GRANT SELECT ON "PUBLIC"."V$LOGFILE" TO "cmid_user";
GRANT SELECT ON "PUBLIC"."V$TRANSPORTABLE_PLATFORM" TO "cmid_user";
GRANT SELECT ON "PUBLIC"."V$THREAD" TO "cmid_user";
GRANT SELECT ON "PUBLIC"."V$DATABASE_INCARNATION" TO "cmid_user";
GRANT SELECT ON "PUBLIC".GV$TRANSACTION TO "cmid_user";

GRANT SELECT ON "SYS"."DBA_CONS_COLUMNS" TO "cmid_user";
GRANT SELECT ON "SYS"."DBA_CONSTRAINTS" TO "cmid_user";
GRANT SELECT ON DBA_INDEXES TO "cmid_user";
GRANT SELECT ON "SYS"."DBA_LOG_GROUP_COLUMNS" TO "cmid_user";
GRANT SELECT ON "SYS"."DBA_TABLESPACES" TO "cmid_user";

GRANT SELECT ON "SYS"."OBJ$" TO "cmid_user";
GRANT SELECT ON "SYS"."TAB$" TO "cmid_user";
GRANT SELECT ON "SYS"."IND$" TO "cmid_user";
GRANT SELECT ON "SYS"."COL$" TO "cmid_user";

GRANT SELECT ON "SYS"."PARTOBJ$" TO "cmid_user";
GRANT SELECT ON "SYS"."TABPART$" TO "cmid_user";
GRANT SELECT ON "SYS"."TABCOMPART$" TO "cmid_user";
GRANT SELECT ON "SYS"."TABSUBPART$" TO "cmid_user";
COMMIT;

/*To provide read access to the Amazon RDS online and archived redo logs:*/
GRANT READ ON DIRECTORY ONLINELOG_DIR TO "cmid_user";
GRANT READ ON DIRECTORY ARCHIVELOG_DIR TO "cmid_user";
Additionally, log in as the master user and run the following Amazon RDS procedures to grant the SELECT privilege on some more objects:
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'DBA_TABLES',
p_grantee => 'cmid_user',
p_privilege => 'SELECT');
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'DBA_OBJECTS',
p_grantee => 'cmid_user',
p_privilege => 'SELECT');
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'DBA_OBJECT_TABLES',
p_grantee => 'cmid_user',
p_privilege => 'SELECT');
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'DBA_VIEWS',
p_grantee => 'cmid_user',
p_privilege => 'SELECT');
end;
/
/begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'DBA_USERS',
p_grantee => 'cmid_user',
p_privilege => 'SELECT',
p_grant_option => false);
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'V_$CONTAINERS',
p_grantee => 'cmid_user',
p_privilege => 'SELECT');
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'V_$PARAMETER',
p_grantee => 'cmid_user',
p_privilege => 'SELECT');
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'V_$SPPARAMETER',
p_grantee => 'cmid_user',
p_privilege => 'SELECT');
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'V_$STANDBY_LOG',
p_grantee => 'cmid_user',
p_privilege => 'SELECT');
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'V_$VERSION',
p_grantee => 'cmid_user',
p_privilege => 'SELECT');
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'ALL_CONS_COLUMNS',
p_grantee => 'cmid_user',
p_privilege => 'SELECT');
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'ALL_CONSTRAINTS',
p_grantee => 'cmid_user',
p_privilege => 'SELECT');
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'ALL_OBJECTS',
p_grantee => 'cmid_user',
p_privilege => 'SELECT');
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'ALL_TABLES',
p_grantee => 'cmid_user',
p_privilege => 'SELECT',
p_grant_option => false);
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'ALL_TAB_PARTITIONS',
p_grantee => 'cmid_user',
p_privilege => 'SELECT',
p_grant_option => false);
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'ALL_USERS',
p_grantee => 'cmid_user',
p_privilege => 'SELECT');
end;
/
begin
rdsadmin.rd 'ALL_TABLES',
p_grantee => 'sadmin_util.grant_sys_object(
p_obj_name => 'cmid_user',
p_privilege => 'SELECT',
p_grant_option => false);
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'ALL_TAB_PARTITIONS',
p_grantee => 'cmid_user',
p_privilege => 'SELECT',
p_grant_option => false);
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'ATTRCOL$',
p_grantee => 'cmid_user',
p_privilege => 'SELECT');
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'CCOL$',
p_grantee => 'cmid_user',
p_privilege => 'SELECT');
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'COLTYPE$',
p_grantee => 'cmid_user',
p_privilege => 'SELECT');
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'INDPART$',
p_grantee => 'cmid_user',
p_privilege => 'SELECT');
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'IDNSEQ$',
p_grantee => 'cmid_user',
p_privilege => 'SELECT');
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'CDEF$',
p_grantee => 'cmid_user',
p_privilege => 'SELECT');
end;
/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'RECYCLEBIN$',
p_grantee => 'cmid_user',
p_privilege => 'SELECT');
end;
/
/* Only required for RDS21 which supports PDB*/
begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'V_$PDBS',
p_grantee => 'cmid_user',
p_privilege => 'SELECT');
end;
/

Oracle log access methods for CDC

Database ingestion and replication incremental load and combined initial and incremental load jobs can access the Oracle redo logs for CDC processing in alternative ways, depending on your environment and requirements.

Direct log access

Database ingestion and replication jobs can directly access the physical Oracle redo logs on the on-premises source system to read change data.
Note: If you store the logs on solid-state disk (SSD), this method can provide the best performance.
The following image shows the data flow:
  1. 1The Oracle database writes change records to the database log files on disk.
  2. 2The Database Ingestion and Replication CDC Reader reads the physical log files and extracts change records from the log files for the source tables of CDC interest.
  3. 3The Database Ingestion and Replication CDC Writer reads the change records.
  4. 4The CDC Writer applies the change records to the target.

NFS-mounted logs

Database ingestion and replication jobs can access to Oracle database logs from shared disk by using a Network File Sharing (NFS) mount or another method such as Network Attached Storage (NAS) or clustered storage.
The following image shows the data flow:
  1. 1The Oracle database writes change records to database log files. The log files are written to shared disk.
    2. The shared disk can be on any system that allows the files to appear as local to both the database and Secure Agent hosts. This sharing can be achieved by using NFS, as shown above, or by using Network Attached Storage (NAS) or clustered storage.
  2. 2The Database Ingestion and Replication CDC Reader reads the log files from the NFS server over the network and extracts the change records for the source tables of CDC interest.
  3. 3The Database Ingestion and Replication CDC Writer reads the change records.
  4. 4The CDC Writer applies the change records to the target.

ASM-managed logs

Database ingestion and replication jobs can access Oracle redo logs that are stored in an Oracle Automatic Storage Management (ASM) system. To read change data from the ASM-managed redo logs, the ASM user must have SYSASM or SYSDBA authority on the ASM instance.
When you configure an Oracle Database Ingestion connection, complete the properties that include "ASM" in their names.
Also, Informatica recommends that you set the sqlnet.recv_timeout parameter in the local sqlnet.ora file to less than 5 minutes when reading data from redo log files in Oracle ASM. 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.
The following image shows the data flow:
  1. 1The Oracle database writes change records to the ASM-managed database log files.
  2. 2The Database Ingestion and Replication CDC Reader reads the ASM-managed log files and extracts the change records for the source tables of CDC interest.
  3. 3The Database Ingestion and Replication CDC Writer reads the change records.
  4. 4The CDC Writer applies the change records to the target.

ASM-managed logs with a staging directory

Database ingestion jobs can access ASM-managed redo logs from a staging directory in the ASM environment. In comparison to using ASM only, this method can provide faster access to the log files and reduce I/O on the ASM system. To read change data from the ASM-managed logs, the ASM user must have SYSASM or SYSDBA authority on the ASM instance.
The following image shows the data flow:
  1. 1The Oracle database writes change records to the ASM-managed log files.
  2. 2ASM copies the logs to a staging directory.
    3. The staging directory must be on shared disk, such as an NFS mount, so that ASM can write data to it and database ingestion and replication jobs can read data from it.
  3. 3The Database Ingestion and Replication CDC Reader reads the log files in the staging directory and extracts the change records for the source tables of CDC interest.
  4. 4The Database Ingestion and Replication CDC Writer reads the change records.
  5. 5The CDC Writer applies the change records to the target.

BFILE access to logs in the Oracle server file system by using directory objects

On an on-premises Oracle source system, you can configure Database Ingestion and Replication to read online and archived redo logs from the local Oracle server file system by using Oracle directory objects with BFILE locators. You must create Oracle directory objects named ARCHIVELOG_DIR and ONLINELOG_DIR that point to the locations of the Oracle redo log files.
The following image shows the data flow:
  1. 1The Oracle database writes change records to the redo log files in the local Oracle server file system. When a database ingestion and replication task needs to read log files, it connects to Oracle and issues a select request that references the ARCHIVELOG_DIR or ONLINELOG_DIR directory object to access the logs.
    2. Note: If you use BFILE access to Oracle data that is stored in multiple log locations, such as in an environment with a standby database, RDS database instance, or Fast Recovery Area, Database Ingestion and Replication can automatically find the logs by querying for all directory objects that begin with ARCHIVELOG_DIR and ONLINELOG_DIR and end with any suffix, for example, ARCHIVELOG_DIR_01, ARCHIVE_LOG_DIR_02, and so on. If database users who run database ingestion and replication jobs have the CREATE ANY DIRECTORY and DROP ANY DIRECTORY privileges, the directory objects can be created at runtime, as needed.
  2. 2The Database Ingestion and Replication CDC Reader reads the log file by using the OCI Client that is installed on the Secure Agent box and extracts the change records for the source tables of CDC interest.
  3. 3The CDC Reader sends the change records to the CDC Writer.
  4. 4The CDC Writer applies the change records to the target.
For information about configuring BFILE access, see Configuring BFILE access to Oracle redo logs in the Oracle file system.

Using copies of Oracle archive redo logs

Database ingestion and replication combined initial and incremental load jobs can read change data from copies of archive redo logs. You must set the Reader Mode property to ARCHIVECOPY in the Oracle Database Ingestion connection properties and also set the source custom property pwx.cdcreader.oracle.reader.additional with the dir and file parameters. The dir parameter points to the name of the base directory that the CDC log reader scans for the archive log copies, and the file parameter specifies a mask that is used to filter the log copies.

Configuring BFILE access to Oracle redo logs in the Oracle file system

If you store redo logs in the local Oracle server file system and want to access the logs by using Oracle directory objects with BFILEs, perform the following configuration tasks:
Complete the following usual Oracle source prerequisite tasks that are not specific to BFILE access:
For more information, see Oracle sources.
Additionally, for BFILE access, perform the following steps to configure an online log destination and archive log destination:
  1. 1Query the Oracle database for the online and archived redo log locations in the Oracle server file system. You can use the following example queries:
    2. To get location of the online redo logs:
    select * from v$logfile;
    To get the archive log destination:
    select dest_id, dest_name, destination, status from V$ARCHIVE_DEST;
  2. 2Create the ONLINELOG_DIR and ARCHIVELOG_DIR directory objects that point to the locations of log files from step 1. An Oracle directory object specifies a logical alias name for a physical directory on the Oracle server file system under which the log files to be accessed are located. For example:
    3. CREATE DIRECTORY ONLINELOG_DIR AS '/u01/oracle/data';
    CREATE DIRECTORY ARCHIVELOG_DIR AS '/u01/oracle/archivedata';
    If you plan to set the reader mode to ARCHIVEONLY in the Oracle Database Ingestion connection to read changes only from archive logs, you do not need to create an ONLINELOG_DIR directory or directory object.
    The Oracle database does not verify that the directories exist. Make sure that you specify valid directories that exist in the Oracle file system.
    Note: If you use a softlink in these paths, database ingestion and replication incremental load and combined load jobs with an Oracle version 18c or later source will fail with the error ORA-22288: file or LOB operation FILEOPEN failed. To avoid this error, specify an absolute path prefix in the Directory Substitution connection property or remove the softlink in the log paths. Also, when manually creating BFILE directory objects, you can use absolute paths.
  3. 3To verify that the directory objects were created with the correct file system paths for the redo logs, issue a select statement such as:
    4. select * from all_directories;
    OWNER DIRECTORY_NAME DIRECTORY_PATH
    -------- ------------------- ----------------------------------
    SYS ARCHIVELOG_DIR /u01/oracle/data/JO112DTL
    SYS ONLINELOG_DIR /u01/oracle/data/JO112DTL
  4. 4Grant read access on the ONLINELOG_DIR and ARCHIVELOG_DIR directory objects to the Database Ingestion and Replication user who is specified in the Oracle Database Ingestion connection properties. For example:
    5. grant read on directory "ARCHIVELOG_DIR" to "cmid_user";
    grant read on directory "ONLINELOG_DIR" to "cmid_user";
    Note: If the ONLINELOG_DIR path does not exist or match the path to the active redo logs, Database Ingestion and Replication tries to create the directory. If you do not have sufficient privileges to create the directory object, an error message is issued. In this case, ask your DBA to create the directory object with the correct path.
  5. 5In the Oracle Database Ingestion connection properties, select the BFILE Access check box.
Important: If you use BFILE access to Oracle data stored in multiple log locations, Database Ingestion and Replication can automatically find the logs by querying for all directory objects that begin with ARCHIVELOG_DIR and ONLINELOG_DIR and end with any suffix, for example, ARCHIVELOG_DIR_01, ARCHIVE_LOG_DIR_02, and so on. This capability enables Database Ingestion and Replication to support Oracle standby databases that use log locations different from those on the primary database, replica logs on Amazon RDS database instances, and archive logs in the Fast Recovery Area to which the USE_DB_RECOVERY_FILE_DEST parameter points.
You can override the ARCHIVELOG_DIR and ONLINELOG_DIR names by using the custom properties pwx.cdcreader.oracle.database.additional BFILEARCHIVEDIR=<directory_object> and pwx.cdcreader.oracle.database.additional BFILEONLINEDIR=<directory_object> if necessary.
If you grant the CREATE ANY DIRECTORY and DROP ANY DIRECTORY privileges to database users who run database ingestion and replication jobs, the directory objects can be created at runtime, as needed. For example, if multiple archive and online log destinations exist in a database, the directory objects could be created with the following naming convention:
Database Ingestion and Replication performs no cleanup processing on these generated directory objects.
If you use the USE_DB_RECOVERY_FILE_DEST parameter and the CREATE ANY DIRECTORY and DROP ANY DIRECTORY privileges have not been granted, your DBA must create the directory objects daily or weekly, before your database ingestion and replication jobs run, by using a script such as:
create or replace directory ARCHIVELOG_DIR_2024_08_19 as '<DB_RECOVERY_FILE_DEST>/2024_08_19'
If you use the USE_DB_RECOVERY_FILE_DEST parameter and the database user has the CREATE ANY DIRECTORY and DROP ANY DIRECTORY privileges, the directory objects are created as needed at runtime and dropped after 14 days. These directory objects have the naming convention <ARCHIVEDIRNAME>_YYYY_MM_DD.

Using BFILE Access to a TDE Wallet

Database Ingestion and Replication can use BFILE access to a remote file-based Transparent Data Encryption (TDE) wallet.
If you configure a file-based TDE wallet, Database Ingestion and Replication first checks if the ewallet.p12 file exists in the TDE wallet directory on the local machine. If the file exists, processing continues as normal. If the file does not exist there, Database Ingestion and Replication looks for a directory object to use for remote access, as follows:
  1. 1Database Ingestion and Replication queries the database for a directory object with a path that matches the TDE wallet directory. This matching is case-insensitive when the database runs on Windows. Any trailing separators are ignored.
  2. 2If no matching directory object is found by the query and you have the CREATE ANY DIRECTORY system privilege, Database Ingestion and Replication creates the directory object with the directory_name PWXTDEDIR.
  3. 3If you do not have the CREATE ANY DIRECTORY system privilege, you should ask your DBA to create the directory object. The directory object can have any name the DBA chooses. You must have READ access to the directory object.
After the directory object exists, the TDE wallet can be read remotely from the database during initialization. You do not need to copy the wallet to the local machine.

Oracle Data Guard databases or far sync instances as sources

Database Ingestion and Replication can capture change data from Oracle Data Guard primary databases, logical or physical standby databases, and far sync instances.
A far sync instance is a remote Oracle Data Guard destination that accepts redo from the primary database and then ships that redo to other members of the Oracle Data Guard configuration.
You can initially load a target with data either from the Oracle Data Guard primary database or from a standby database that is open in read mode.
Configuration
Oracle change capture configuration depends on the Oracle Data Guard database type.
Standby-to-primary role transitions
In an Oracle Data Guard environment, a physical standby database can transition to the primary role. Usually, the role transition occurs because of a failover or switchover. During the transition, all active connections to the physical standby database terminate.
To be able to resume CDC processing after the physical standby database transitions to the primary role, you might need to adjust some connection configuration properties on the original standby system for Database Ingestion and Replication to process past the transition point. After the transition, you can adjust the properties again for optimal performance in the new primary database environment.
The following table describes these connection properties by transition phase:
Connection Property
Before Transition
During Transition
After Transition
RAC Members
Specify the number of active threads on the primary database.
Specify the total number of active threads with unique thread IDs on both the standby database and primary database.
For example, if the primary database is a two-node RAC database that uses thread IDs 1 and 2 and the standby database is a 3-node RAC database that uses thread IDs 2, 3, and 4, specify a property value of 4.
After the restart point has progressed beyond the transition point, edit the property value, as needed, for optimal performance of change data capture from the new primary database.
Informatica recommends that you use the lowest value that is suitable for your environment to minimize the overhead of CDC thread tracking.
Reader Standby Log Mask
Standby Connect String
Standby User Name
Standby Password
Remove all standby properties. They're not applicable to physical standby databases open for read only access.
Properties remain removed.
Do not specify these properties. They're not used for a primary database.
Database Connect String
If the standby database is not open, define the connection string for the primary database.
If the standby database is open, define the connection string for the standby database.
Specify the connection string for the database that will have the primary role after the role transition.
Ensure that this connection property defines the connection string for the new primary database.

Oracle archive log retention considerations

Database ingestion and replication incremental load and combined initial and incremental load jobs must be able to access transaction data in Oracle online and archive redo logs. If the logs are not available, database ingestion and replication jobs end with an error.
Typically, the Oracle DBA sets the archive log retention period based on your oganization's particular business needs and Oracle environment. Make sure that the source archive logs are retained for the longest period for which you expect change capture to be stopped or latent, plus about 1 hour, so that the logs will be available for restart processing.
To determine if the current log retention policy in your environment is sufficient to accommodate database ingestion and replication change capture processing, consider the following factors:
If the archive logs are not available when you need to restart capture processing in the logs, you can ask your DBA to restore them and to modify the retention period if necessary. Otherwise, perform another initial load to re-materialize the target and then start incremental change data processing again. However, in this case, you might lose some changes.

Providing JDBC connection details in the tnsnames.ora file

You can optionally enter JDBC connection details for an Oracle source in the Oracle tnsnames.ora file for database ingestion and replication unload processing and Log-based CDC. If you need to make changes to the connection later, you can then update the details in the tnsnames.ora file only instead of in both the Oracle Database Ingestion connection properties and the tnsnames.ora file.
You can use the tnsnames.ora file with either the Native Oracle JDBC driver or Progress DataDirect Oracle JDBC driver. You'll need to create and configure the tnsnames.ora file. You'll also still need to define an Oracle Database Ingestion connection that specifies a database connect string, which specifies the TNS entry for the source database.
This feature does not apply to Oracle sources that use Query-based CDC or to Oracle targets. Also, when using the tnsnames.ora file only for connection details, you can’t configure SSL encryption and Kerberos authentication.
To set up use of the tnsnames.ora file, complete the following steps:
  1. 1Configure a tnsnames.ora file. Add a TNS entry for your source database. For example:.
    2. # Entry for the database 'MY_DB' on the server specified by the host name
    MY_DB_TNS_NAME =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = my_server_hostname)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = my_service_name.example.com)
    )
    )
  2. 2To define the location of the tnsnames.ora file, set one of the following system environment variables, listed in order of priority:
    3. If a task can’t find the tnsnames.ora in one of these locations, it fails with an appropriate error message.
  3. 3When you configure a database ingestion and replication task in the task configuration wizard, set the source custom property useOracleTnsnamesInJdbcUrl to true on the Task Details – Source Details page to enable use of the tnsnames.ora file for unload processing.
  4. 4Define an Oracle Database Ingestion connection with the following properties: