Orchestrating Initial Load and Replication with OCI Data Integration and OCI GoldenGate

OCI Data Integration is a powerful ETL tool for batch integration, while OCI GoldenGate excels at data replication. Although OCI GG can perform initial loads, it tends to be slow for large datasets. This project aims to streamline both the initial load and ongoing replication by leveraging OCI Data Integration for the initial load and orchestrating replication processes also leveraging the parameters of OCI GoldenGate.

The orchestration template has already been built. All that remains for users is to populate the required parameters with their values.

Note: The goal is to simplify the initial load and the process of starting Change Data Capture replication in OCI GoldenGate. However, users should have a basic understanding of OCI Data Integration and OCI GoldenGate.

The orchestration process is the following:

1. Creation of an API Gateway: This is created only the first time you run the process. On next runs, if the API Gateway already exists, it will be reused.
2. Deployment Creation: After the API Gateway is created or confirmed to exist, a deployment is created. This follows the same logic: created once and reused.
3. Extract Creation in OCI GoldenGate: OCI Data Integration, through a REST API task, creates and starts an Integrated Extract in OCI GG to begin data capture.
4. Checkpoint Table and Initial Load: Simultaneously, a checkpoint table is created, and the initial load is triggered by the Data Loader task.
5. Replicat Creation: While the initial load is running, a replicat is created in OCI GoldenGate but is not yet started.
6. Starting Replicat: Starting the replicat requires obtaining the SCN number, which is done by a procedure in the source database, and waiting for confirmation of a successful initial load.

Now, let’s take it step by step and understand the tasks.

STEP DESCRIPTION

It might look complex but there are just 9 steps that are orchestrated. Other operators, like MERGE or EXPRESSION is just to perform some checks and pass parameters from one operator to another.

Create API Gateway deployment

First operator is CREATEDEPLOYMENTAUTH. This is a REST API task that creates an API Gateway deployment in your OCI environment.

Why should we create an API Gateway deployment? Well, there are multiple advantages. An API Gateway allows you to use a single endpoint to access multiple services or applications, such as OCI GG, compute instances, load balancers, databases, etc. Another advantage of it, is that it allows a totally different level of customization, like authentication, routing policies, timeouts, methods used for in and out, etc.

The CREATEDEPLOYMENTAUTH is created in a way that it will create a deployment if there is none provided. If you already have one created, you could pass the deployment display name to its parameter so it could be reused. There you will also pass information like Compartment OCID, API Gateway OCID, GG URL, etc.

Initially task will try to create a new deployment. If there is already one with the same name, task will fail and will move on to the next step.

List deployments in your API Gateway
Next step is to identify if there are any deployments that could be reused with the name that you provided in previous step. The goal is to get the endpoint URL with the right path and pass it to the next tasks.

Creating the OCI GG Extract
This task CREATEEXTRACT is concentrating on switching from auxiliary tasks to provisioning objects from OCI GoldenGate. This step is concentrating on creating of the extract. Based on the URL that was extracted on previous step and a bit of new information that you will provide, it will access OCI GoldenGate and will create an integrated extract for you.

Starting OCI GG Extract
Task STARTEXTRACT will take the name of the extract from previous step and will start it.
At the same time while the extract is being created and started, a parallel task will create a checkpoint table in the target DB using task ADD_CHECKPOINT_TABLE. If the checkpoint table already exists, it will ignore the task and will move on with the next step.

Getting SCN
Task that has to get the SCN number as soon as the extract has started is called GET_SCN. It is a simple SQL procedure that is going to be created on your source database and invoked later via SQL Procedure task. Its only goal is to get the SCN number and via the output variable pass it to the next step of the process.

Initial Load
As soon as the extract started and the status is also successful, it will trigger the initial load through DATA_LOADER_TASK which will do the initial load. That will be the most time-consuming step. Depending on the number of the tables and the volume you want to migrate to the target DB — it could take from several minutes to several hours.

Creation of the Replicat
To create the replicat, 2 previous steps should be executed successfully: Initial load and creation of the checkpoint table. If assuming that checkpoint table is a one-time thing, it could be eliminated later from objects to check. This task will create the replicat only. It will not start yet. To start a replicat we need to get a success condition from: getting SCN number and the initial load.

Starting Replicat
As soon as we get a confirmation that initial load has finalized successfully, we also got the SCN number, STARTREPLICAT task will start replicat from that exact SCN number. And with that the orchestration will be finalized.

Pre-requisites:

1. Download file called Initial_Load_CDC.project.zip and upload it into an OCI Object Storage Bucket

2. Navigate to OCI Data Integration and import the downloaded file into your OCI DI Project Page: Create Project > Import

3. Provide the details so that OCI DI can access the downloaded zip file and start the import. It will take a few moments the import to finalize.

4. In OCI DI: Create Data Asset to your source and target.

5. In OCI GG: Create connection your source and target and assign them to your OCI GG deployment.

6. Open the imported project> Task

7. Open the first task > Data_Loader_task. Provide connection details and select data entities you would like to replicate to your target DB.

8. Procedure to get SCN Number. Firstly, create a procedure in your source database using code from below. Then in the OCI DI project, find a task called: GET_SCN, open it and point the task to the newly created procedure.

create or replace PROCEDURE ADMIN.get_active_or_current_scn (
  p_start_scn OUT NUMBER
) AS
  CURSOR c_scn IS
    SELECT T.START_SCN, T.STATUS TSTATUS
      FROM gv$transaction T
      INNER JOIN gv$session S ON S.SADDR = T.SES_ADDR
    UNION ALL
    SELECT current_scn, 'CURRENT'
      FROM v$database
    ORDER BY 1;
  v_start_scn NUMBER;
  v_status VARCHAR2(20);
BEGIN
  p_start_scn := NULL;
  FOR rec IN c_scn LOOP
    IF rec.TSTATUS = 'ACTIVE' THEN
      p_start_scn := rec.START_SCN;
      EXIT; 
    END IF;
  END LOOP;
  IF p_start_scn IS NULL THEN
    FOR rec IN c_scn LOOP
      IF rec.TSTATUS = 'CURRENT' THEN
        p_start_scn := rec.START_SCN;
        EXIT;
      END IF;
    END LOOP;
  END IF;
  IF p_start_scn IS NULL THEN
    RAISE_APPLICATION_ERROR(-20001, 'No SCN found from active transactions or current SCN.');
  END IF;
END get_active_or_current_scn;

11. Create an API Gateway.

Get Alexandru Porcescu’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

- Navigate to OCI Data Integration
- Menu > Developer Services > under API Management click on Gateways
- Click Create Gateway
- Give it a Name > Type: Private > Network: Select appropriate VCN and private Subnet that connectivity to source DB, target DB, OCI DI and OCI GG.
- Click Create Gateway

12. OCI GoldenGate pre-requisites. You would still verify documentation to make sure that all pre-requisites for OCI GoldenGate were met.
Also, the desired schema or tables should be manually added into TRANDATA in OCI GoldenGate.

Step — by — Step & How to use it

Once you have completed the pre-requisities, publish the pipeline task to the Application and Run it.

You will be required to provide some values for the parameters. To modify the pre-defined value, click on Edit to customize:

• COMPARTMENT_ID — The OCID of the compartment where you previously created the API Gateway.
• DISPLAY_NAME — The name of the API Gateway endpoint.
• EXTRACT_NAME — The name of the extract. It should be 8 or fewer characters, using only capital letters and numbers.
• TRAIL_NAME — The trail file name. Use capital letters only. Examples: AA, B1, CD.
• REPLICAT_NAME — The name of the replicat. It should be 5 or fewer characters, using only capital letters and numbers.
• USER_ID_ALIAS_SRC — User ID alias of the source connection attached to your OCI GoldenGate deployment.
• USER_ID_ALIAS_TRG — User ID alias of the target connection attached to your OCI GoldenGate deployment.
• CHECKPOINT_TABLE_NAME — Checkpoint table name. A default name is already provided; change it only if necessary.
• GG_USER — GoldenGate user created in the database. The default is usually ggadmin or c##ggadmin.
• API_GATEWAY_OCID — OCID of the API Gateway you created.
• GG_URL — The URL or IP address of your OCI GoldenGate instance.
• BASE64_USER_PASSWORD — Username and password encoded in base64. The format should be <username>:<password>. To get the encoded string, use the following commands:

- For UNIX:
echo -n 'ggadmin:this_is_my_password' | base64

- For Windows:
powershell "[convert]::ToBase64String([Text.Encoding]::UTF8.GetBytes('ggadmin:this_is_my_password))"

• SOURCE_TABLE — Source schema and table name used for replication and initial load. It should follow the GoldenGate parameter file template

TABLE SCHEMA_NAME.TABLE_NAME;

You could place multiple tables as well

TABLE SCHEMA_NAME.TABLE_NAME1;
TABLE SCHEMA_NAME.TABLE_NAME2;
TABLE SCHEMA_NAME.TABLE_NAME3;
...
...
...

• TARGET_TABLE — Target schema and table name used for replication and initial load. It should follow the GoldenGate parameter file template. Also here you could add any parameters of OCI GG.

# HANDLECOLISSIONS
# MAP_PARALLELISM 4
# MAXGROUPS 1500

MAP SOURCE_SCHEMA.TABLE_NAME TABLE SCHEMA_NAME.TABLE_NAME;

For using multiple tables, when for example target table name is different from the source:

MAP SOURCE_SCHEMA.TABLE_NAME_1 TABLE SCHEMA_NAME.TABLE_NAME_X1;
MAP SOURCE_SCHEMA.TABLE_NAME_2 TABLE SCHEMA_NAME.TABLE_NAME_X2;
MAP SOURCE_SCHEMA.TABLE_NAME_3 TABLE SCHEMA_NAME.TABLE_NAME_X3;
...
...
...

• REGION — Region of your API Gateway, e.g., us-phoenix-1.

An example of passing values to parameters:

Once you provide all that information, you click on RUN and the only thing that left to be done — is to wait for the intial load to complete and monitor OCI GoldenGate to make sure that all processes work well.

Achieving full automation from OCI Data Integration to OCI GoldenGate is a powerful way to streamline your data workflows, combining initial loads, transformations, and real-time replication into a seamless pipeline. By leveraging OCI GoldenGate pipelines, REST APIs, and SQL procedures, you can orchestrate and automate the entire data movement process without manual intervention. This approach not only simplifies management but also ensures data consistency and reduces latency between source and target systems.

If you have any questions or need guidance on setting up this automation, feel free to reach out to me directly or leave your comments here — I’m happy to help!