Replicate data from Autonomous Transaction Processing to Azure Data Lake Storage Gen 2

This quickstart demonstrates how to replicate data from Autonomous Transaction Processing to Azure Data Lake Storage Gen 2 using OCI GoldenGate.

Related Topics

Before you begin

To successfully complete this quickstart, you must have the following:

Environment set up: Autonomous Transaction Processing

If you don't already have a source database set up for replication, you can follow these steps to load a sample schema to use for this quickstart. This quickstart uses Autonomous Transaction Processing for the source database.

To set up the source Autonomous Database:

  1. Download and unzip the sample database schema.
  2. In the Oracle Cloud console, select your Autonomous Transaction Processing (ATP) instance from the Autonomous Databases page to view its details and access Database Actions.
  3. On the Database details page, click Database actions.
  4. Unlock the GGADMIN user:
    1. On the Database actions page, under Administration, click Database Users.
    2. Locate GGADMIN and then click its ellipsis menu (three dots) and select Edit.
    3. In the Edit User panel, enter the GGADMIN password, confirm the password, and then deselect Account is Locked.
    4. Click Apply Changes.
  5. Load the source sample schema and data:
    1. From the Database Actions Selector menu, under Development, select SQL.
    2. Copy and paste the script from OCIGGLL_OCIGGS_SETUP_USERS_ATP.sql into the SQL worksheet.
    3. Click Run Script. The Script Output tab displays confirmation messages.
    4. Clear the SQL worksheet and then copy and paste the SQL script from OCIGGLL_OCIGGS_SRC_USER_SEED_DATA.sql.

      Tip:

      You may need to run each statement separately for the SQL tool to execute the scripts successfully.
    5. To verify that the tables were created successfully, close the SQL window and reopen it again. In the Navigator tab, look for the SRC_OCIGGLL schema and then select tables from their respective dropdowns.
  6. Enable supplemental logging:
    1. Clear the SQL Worksheet.
    2. Enter the following statement, and then click Run Statement:
      ALTER PLUGGABLE DATABASE ADD SUPPLEMENTAL LOG DATA;

Task 1: Create the OCI GoldenGate resources

This quickstart example requires deployments and connections for both the source and target.
  1. Create an Oracle deployment for the source Autonomous Transaction Processing instance.
  2. Create a Big Data deployment for the target Azure Data Lake Storage.
  3. Create a connection for the source Autonomous Transaction Processing instance.
  4. Create connection for Azure Data Lake Storage.
  5. (Optional) If your Big Data deployment doesn't have a public endpoint, then create a connection to GoldenGate, and then assign this connection to the source Oracle deployment.
  6. Assign the Autonomous Transaction Processing connection to the source Oracle deployment.
  7. Assign Azure Data Lake Storage connection to the target Big Data deployment.

Task 2: Add the Extract

  1. On the Deployments page, select the source Autonomous Transaction Processing deployment.
  2. On the deployment details page, click Launch Console.
  3. Log in with the source deployment's administrator username and password.
  4. Add transaction information.
  5. Add an Extract

Task 3: Add and run the Distribution Path

  1. Create a user for the Distribution Path in the target Big Data deployment.
    1. On the Deployments page, select the target deployment to view its details.
    2. On the deployment details page, click Launch Console.
    3. Sign in to the target Big Data deployment console using the Administrator username and password.
    4. In the Big Data deployment console, open the navigation menu, and then click Administrator.
    5. On the Users page, click Add New User (plus icon).
    6. Complete the fields as follows, and then click Submit.
      1. For Username, enter a name, such as ggsnet.
      2. From the Role dropdown, select Operator.
      3. Enter a password twice for verification.

      The new user appears in the Users list.

  2. In the source ATP GoldenGate deployment console, add a credential for the user created in Step 1.
    1. In the source ATP GoldenGate deployment console, click Administration Service, open the navigation menu, and then select Configuration.
    2. On the Credentials page, click Add Credential, and then complete the fields as follows:
      1. For Credential Domain, enter GGSNetwork.
      2. For Credential Alias, enter dpuser.
      3. For User ID, enter the name of the user created in Step 1 (ggsnet).
      4. Enter the user's password twice for verification.
    3. Click Submit.

      The credential appears in the Credentials list.

  3. Click Distribution Service, and then click Add Path (plus icon).
  4. Complete the Add Path form fields as follows, and then click Create and Run:
    1. For Path Name, enter a name.
    2. For Source Extract, select the Extract created in Task 2.
    3. For Source Trail Name, select the Extract Trail from Task 2.
    4. For Target Authentication Method, select UserID Alias.
    5. For Target protocol, select wss.
    6. For Target Host, enter the host domain of the target deployment.

      Note:

      You can copy and paste the URL of the target Big Data deployment console and remove the https:// and any trailing slashes.
    7. For Port Number, enter 443.
    8. For Trail Name, enter a two-character name, such as E1.
    9. For Domain, enter the name of the Credential Domain created in Step 2 (GGSNetwork)..
    10. For Alias, enter the Credential Alias created in Step 2 (dpuser).

      You return to the Distribution Service Overview page where you can view the status of the created path.

  5. In the target Big Data deployment console, review the Receiver Path.
    1. In the target Big Data deployment console, click Receiver Service.
    2. Review the path details. This path was created as a result of the Distribution Path created in the previous step.

Task 4: Add and run the Replicat

  1. In the target Big Data deployment console, click Administrator Service, and then click Add Replicat (plus icon).
  2. On the Add Replicat page, under Replicat type, select Classic Replicat, and then click Next.
  3. On the Replicat Options page, complete the following form fields, and then click Next:
    • For Process Name, enter a name.
    • For Trail Name, enter the name of the Trail from Task 3.
    • For Target, select Azure Data Lake Storage.
    • For Alias, select the Azure Data Lake Storage connection created in Task 1.
  4. On the Replicat Parameters page, leave the default, and then click Next:
    MAP SRC_OCIGGLL.*, TARGET *.*;
  5. On the Properties page, configure Azure Data Lake Storage properties.
    Required Properties:
    • gg.eventhandler.abs.bucketMappingTemplate: Name of the Azure Data Lake Storage Container. If container is pre-configured, a static container name can be provided. If Azure authentication method permissions are provided, Template Keywords can be used for auto container creation by OCI GoldenGate.
    (Optional) Additional properties you may consider adding:
  6. Click Create and Run.
    You return to the Overview page, where you can review the Replicat details.

Task 5: Verify the replication

Perform some updates to the source Autonomous Transaction Processing instance to verify replication to Azure Data Lake Storage.
  1. In the Oracle Cloud console, open the navigation menu, select Oracle Database, and then select Autonomous Transaction Processing.
  2. In the list of Autonomous Transaction Processing instances, select your source instance to view its details.
  3. On the database details page, click Database actions.

    Note:

    You should be automatically logged in. If not, log in with the database credentials.
  4. On the Database actions home page, select SQL.
  5. Enter the following into the worksheet and click Run Script.
    Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1000,'Houston',20,743113);
Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1001,'Dallas',20,822416);
Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1002,'San Francisco',21,157574);
Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1003,'Los Angeles',21,743878);
Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1004,'San Diego',21,840689);
Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1005,'Chicago',23,616472);
Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1006,'Memphis',23,580075);
Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1007,'New York City',22,124434);
Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1008,'Boston',22,275581);
Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1009,'Washington D.C.',22,688002);
  6. In the source ATP deployment console, select the Extract name, and then click Statistics. Verify that SRC_OCIGGLL.SRC_CITY has 10 inserts.
  7. In the target Big Data OCI GoldenGate deployment console, select the Replicat name, and then click Statistics. Verify that SRC_OCIGGLL.SRC_CUSTOMER has 10 inserts.
  8. In Azure console, navigate to Azure BLOB Storage container and check its contents.

Task 6: Monitor and maintain processes

  1. Monitor performance.
  2. Manage Trail files.