5 Replication Utility
Replication Utility is a valuable tool for organizations that need to ensure data consistency and availability across multiple databases or systems, enabling efficient data replication and synchronization.
Note:
- To perform the Replication process, the Base table and Target table structure must be in sync, if not, you can not perform this activity.
A Replication Utility is a tool or software that is used to replicate or copy data from one source to one or more target destinations. It is commonly used in database management systems to ensure data consistency and availability across multiple locations or servers.
The Replication Utility typically works by capturing changes made to the source data and then applying those changes to the target destinations. This allows for real-time or near-real-time synchronization of data between different databases or systems.
Key features of Replication Utility may include:
- Data synchronization: It ensures that data changes made in the source database are replicated to the target databases, keeping them up to date.
- Replication modes: It supports different replication modes such as one-way replication (from source to target), two-way replication (bidirectional), or multi-master replication.
- Conflict resolution: In case of conflicting changes made to the same data in different locations, the Replication Utility may provide mechanisms to resolve conflicts and maintain data integrity.
- Performance optimization: It may include features to optimize replication performance, such as compression, filtering, or batching of data changes.
- Monitoring and management: Replication Utility often provides monitoring and management tools to track the replication process, view replication status, and troubleshoot any issues that may arise.
Using Replication Utility
The replication utility is a tool that is used to copy data from a source schema to a target schema. It is designed to identify any changes in the source data and update the target schema accordingly. In the context of the Oracle FCCM cloud reporting solution, this utility is specifically used to update the ADW reporting schema from the ATP application schema. The replication utility is primarily developed using PL/SQL, a procedural language used for Oracle database programming.
Description of the illustration replication-utility-flow.png
A database link is established in ADW (Autonomous Data Warehouse) to retrieve data from ATP (Autonomous Transaction Processing). This is achieved through a job in ADW, which utilizes the system global area (SGA), program global area (PGA), and other database resources of ADW. The purpose of this setup is to minimize the processing dependency on ATP.
There are several dependencies associated with using the database link:
- ADW Wallet: The ADW instance requires a wallet that contains the necessary credentials and certificates to establish a secure connection with ATP.
- ADW Access to Object Store: ADW needs access to an object store, which is a designated location for storing files and data. This access is necessary for retrieving any required files or data during the replication process.
- Timely Refresh of Named Credentials: The named credentials used for authentication with ATP need to be refreshed periodically to ensure the security of the connection. This involves updating the fingerprint associated with the credentials.
Table 5-1 fcc_replication_batch_run
| N_RUN_SKEY | D_MIS_DATE | N_RUN_STATUS | V_BATCH_RUN_ID | N_RUN_DATE |
|---|---|---|---|---|
| 1 | 28-06-2022 | 1 | ADW_1656420618626_20220628_1 | 28-06-2022 |
Table 5-2 fcc_replication_audit
| SOURCE_TABLE | TARGET_TABLE | RUN_SKEY | RECORD_INSERTED | RECORD_UPDATED | RECORD_DELETE | RUN_DATE |
|---|---|---|---|---|---|---|
| fcc_cust_dim | fccr_cust_dim | 1 | CUST0383883 | CUST838382 | CUST327722 | |
| fcc_cust_dim | fccr_cust_dim | 1 | CUST9787888 | CUST876127 | CUST176512 | 30-may-2023 |
- The replication audit table is designed to store the actual internal ID or sequence of the changed record, along with the new run ID. This information is crucial for updating dependent tables in the replication process.
- To optimize performance, the audit table is partitioned based on the target table name. This allows for efficient retrieval and management of the replicated data. Additionally, the audit table is further sub-partitioned based on either the run_skey or run_date, depending on the specific requirements of the replication process.
- As for the maintenance of the audit table, whether to truncate or purge the table is left to the discretion of the customer. This decision can be based on factors such as data retention policies, storage limitations, and performance considerations.
Table 5-3 fcc_replication_run_book
| SOURCE_TABLE | TARGET_TABLE | JOS_STATUS | RUN_SKEY | RUN DAE |
|---|---|---|---|---|
| fcc_cust_dim | fccr_cust_dim | Finished | 1 | 30-may-2023 |