Cloud Data Ingestion and Replication

• Ingestion and replication from DB and Mainframe sources with no footprint on the source database
• Simple and easy way to build and manage ingestion and replication pipeline with wizard-driven interface
• No breakage of pipelines in case of schema changes at the source

Database Ingestion and Replication can be used to address multiple inclusions. Some of the most common use cases include:

• Offline reporting: Move user reporting activity from a mission-critical production database system to a separate reporting system to avoid degrading database performance.
• Machine Learning and Generative AI: Help build data warehouses and data lakes by transferring data from multiple databases, including on-premises databases. Keep them current by continuously replicating data after the initial load. This data warehouse or data lake can then be used to power enterprise Machine Learning and Generative AI flows.
• Migration to cloud-based systems: Migrate data from on-premises database systems to cloud-based systems.

Change data capture (CDC) allows users to detect and manage incremental changes at the data source. Data consumers can absorb changes in real time with minimal impact on the data source or the transport system between the data source and the consumer. CDC captures changes from database transaction logs which are then published to a destination such as a cloud data lake, cloud data warehouse, or message hub.

The benefits of CDC include:

• Greater efficiency: With CDC, only data that has changed is synchronized which saves time and enhances the accuracy of data and analytics
• Lower impact on production databases: CDC has minimal impact on the source. This facilitates high-volume data transfers to the analytics target.
• Improved time to value and lower TCO: CDC helps build data pipelines faster, saving time for data engineers and architects thereby reducing total cost of ownership (TCO).

Types of CDC

• Timestamp-based CDC: Leverages a table timestamp column and retrieves only those rows that have changed since the data was last extracted. This is the simplest method to extract incremental data with CDC but can slow down production performance by consuming source CPU cycles
• Trigger-based CDC: It defines triggers that fire before or after INSERT, UPDATE, or DELETE commands and are used to create change logs in a change table. This increases processing overheads and slows down source production operations.
• Log-based CDC: Transactional databases store all changes in a transaction log that helps the database to recover in the event of a crash. With log-based CDC, new database transactions (inserts, updates, and deletes) are read from source databases’ transactions without making application-level changes and without having to scan operational tables.

Benefits of Log-based CDC:

• This is the most preferred and fastest CDC method
• It is non-intrusive and least disruptive for production database sources
• No overhead on the database server performance

Before configuring a Database Ingestion task for initial, incremental, or combined initial and incremental operations, you need to prepare the source databases and targets to ensure they are ready for the Database Ingestion and Replication task and to avoid any unexpected results. Informatica Database Ingestion supports a wide variety of sources and targets. Detailed steps to prepare these sources and targets for a database ingestion job can be found below.

Configuration of a Database Ingestion task requires the following steps:

• Preliminary Checks
• Configuring Basic Task Information
• Configuring Source Information
• Configuring Target Information
• Configuring the Runtime Options

Click on the below resources to get detailed information about how to configure the Database Ingestion task for your choice of source and targets:

Working with Oracle as a Source for your DB Ingestion Job with incremental or combined load?

The methods for accessing Oracle redo logs for Change Data Capture (CDC) processing in Database ingestion and replication jobs, specifically incremental load and combined initial and incremental load jobs, can vary based on your specific environment and requirements. Here are some alternative ways:

• Direct Log Access: Jobs can directly access the physical Oracle redo logs on the on-premises source system to read change data. This method can provide the best performance if you store the logs on a solid-state disk (SSD).
• NFS-Mounted Logs: Jobs can access Oracle database logs from a shared disk using a Network File Sharing (NFS) mount or another method such as Network Attached Storage (NAS) or clustered storage.
• ASM-Managed Logs: 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.
• ASM-Managed Logs with a Staging Directory: Jobs can access ASM-managed redo logs from a staging directory in the ASM environment. This method can provide faster access to the log files and reduce I/O on the ASM system.
• 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.

Please note that the specific method to be used depends on your environment and requirements. Reference videos below:

Schema drift occurs when changes in the source schema need to be detected and handled automatically on the target during data ingestion and replication. With Database Ingestion and Replication, you can configure how these changes are managed to maintain seamless data flows.

Key Highlights:

Supported Schema Changes: Database Ingestion and Replication can automatically detect and handle these changes:

• Adding a column
• Modifying a column
• Dropping a column

Configurable Options: During task setup, you can decide how to manage schema drift. Options include ignoring the changes, replicating them, or stopping the task when a change is detected.

Default Behavior: Schema change handling may vary depending on the target type, ensuring compatibility and stability.

Top Considerations for Schema Drift Management:

• If a schema change is unsupported on the target, the replication job ends with an error.
• Changes to primary or unique key constraints are not replicated. In such cases, the target tables must be resynchronized.
• For Oracle or SQL Server sources, new columns are assumed to be added at the end of the table. If added elsewhere, the system might misinterpret the change, leading to alerts.

Pro Tips:

• Always apply schema changes one by one, followed by at least one DML operation, to ensure accurate detection.
• For detailed configurations, explore Configuring Schedule and Runtime Options in the product documentation.

There are some best practices to follow when handling schema drift in Database Ingestion jobs:

Overriding schema drift options when resuming a database ingestion and replication job: You can override the schema drift options when you resume a database ingestion and replication job that is in the Stopped, Aborted, or Failed state. The overrides affect only those tables that are currently in the Error state because of the Stop Table or Stop Job Schema Drift option. Use the overrides to correct or resolve these errors. You can override schema drift options and resume an incremental load job or a combined initial and incremental load job from the All Jobs tab on the Data Ingestion and Replication page in Operational Insights.

Schema Drift Options: There are several schema drift options you can select when resuming a job with an override. Each option has a different impact on how the job handles schema changes, so choose the one that best suits your needs.

Here are the different schema drift options and their impacts when resuming a database ingestion and replication job:

• Ignore: This option does not replicate DDL changes that occur on the source database to the target.
• Stop Table: This option stops processing the source table on which the DDL change occurred. The job cannot retrieve the data changes that occurred on the source table after the job stopped processing it. Consequently, data loss might occur on the target. To avoid data loss, you will need to resynchronize the source and target objects that the job stopped processing.
• Resync: This option resynchronizes the target tables with the latest source table definitions, including any DDL changes that the schema drift ignored. Use this option for tables that the job stopped processing because of the Stop Table setting for a Schema Drift option. This option is available only for combined initial and incremental load jobs.
• Resync (refresh): For database ingestion and replication combined load jobs that have an Oracle or SQL Server source, use this option to resynchronize the target tables with the latest source table definitions, including any DDL changes that schema drift ignored. After the target tables are refreshed, the structure of the source and target tables match. This option mimics the behavior of the Resync option.
• Resync (retain): For database ingestion and replication combined load jobs that have an Oracle or SQL Server source, use this option to resynchronize the same columns that have been processed for CDC, retaining the current structure of the source and target tables. No checks for changes to the source or target table definitions are performed. If source DDL changes affect the source table structure, those changes are not processed.
• Replicate: This option allows the database ingestion and replication job to replicate the DDL change to the target. If you specify the Replicate option for Rename Column operations on Microsoft Azure Synapse Analytics targets, the job will end with an error.

Deploying a Database Ingestion task

After defining a database ingestion task and saving it, you can deploy the task to create an executable job instance on the on-premises system that contains the Secure Agent and the Database Ingestion agent service and packages. You must deploy the task before you can run the job. The deployment process also validates the task definition. If the deployment process fails, the job status switches to Failed. You can then Undeploy the job and review the error logs from the Operational Insights page. After you resolve the problem, deploy the task again from the database ingestion and replication task wizard.

Running a Database Ingestion task

You can run a deployed database ingestion and replication job from one of the monitoring interfaces, or when you create a database ingestion and replication initial load task, you can specify a schedule for running the job instances associated with a task. Please refer to the articles below:

For step-by-step guidance on the most common replication scenarios, choose one of the links below that best fits your needs.