Replicate data from Amazon RDS to OCI Object Storage

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

• Sign up or Sign in to your Oracle Cloud account.
• A source Amazon RDS for Oracle instance, explained in Environment setup.

1. Turn on supplemental logging on the source database.
2. Set ENABLE_GOLDENGATE_REPLICATION initialization parameter to true.
3. Enable archiving on Source database and retain archived redo logs.
4. Create an Oracle GoldenGate user account on the source database.
5. Grant user account privileges on the source database.

   GRANT CREATE SESSION, ALTER SESSION TO GGADMIN;
   GRANT RESOURCE TO GGADMIN;
   GRANT SELECT ANY DICTIONARY TO GGADMIN;
   GRANT FLASHBACK ANY TABLE TO GGADMIN;
   GRANT SELECT ANY TABLE TO GGADMIN;
   GRANT EXECUTE ON DBMS_FLASHBACK TO GGADMIN;
   GRANT SELECT ON SYS.V_$DATABASE TO GGADMIN;
   GRANT ALTER ANY TABLE TO GGADMIN;
   EXEC rdsadmin.rdsadmin_dbms_goldengate_auth.grant_admin_privilege (
   grantee                 => 'GGADMIN',
   privilege_type          => 'capture',
   grant_select_privileges => true,
   do_grants               => TRUE);
   

6. Download and unzip the sample database schema.
7. Load the source sample schema and data:
   1. Connect to Amazon RDS for Oracle instance from SQL Developer as user SRC_OCIGGLL.
   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 drop-down lists.

1. Create an Oracle deployment for the source Amazon RDS for Oracle instance.
2. Create a Big Data deployment target OCI Object Storage bucket.
3. Create a connection for the source Amazon RDS for Oracle instance.
4. Create a connection for the target OCI Object Storage.
5. If your Big Data deployment does not have a public endpoint, then create a connection to GoldenGate, and assign this connection to the source Oracle deployment.
6. Assign the Amazon RDS for Oracle connection to the source Oracle deployment.
7. Assign the OCI Object Storage connection to the target Big Data deployment.
8. Assign the GoldenGate connection to the source Oracle deployment.

1. On the Deployments page, select the source Autonomous AI 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.

1. If using GoldenGate credential store, create a user for the Distribution Path in the target Big Data deployment, otherwise skip to Step 3.
2. In the source ATP GoldenGate deployment console, add a Path Connection for the user created in Step 1.
   1. In the source ATP GoldenGate deployment console, click Path Connections in the left navigation.
   2. Click Add Path Connection (plus icon), and then complete the following:
      1. For Credential Alias, enter GGSNetwork.
      2. For User ID, enter the name of the user created in Step 1.
      3. Enter the user's password twice for verification.
   3. Click Submit.

      The path connection appears in the Path Connections list.

3. In the source ATP deployment console, add a Distribution Path with the following values:
   1. On the Source Options page:
      • For Source Extract, select the Extract created in Task 2.
      • For Trail Name, enter a two-character name, such as E1.
   2. On the Target Options page:
      • For Target Host, enter the host domain of the target deployment.
      • For Port Number, enter 443.
      • For Trail Name, enter a two-character name, such as E1.
      • For Alias, enter the Credential Alias created in Step 2.
4. In the target Big Data deployment console, review the Receiver Path created as a result of the Distribution 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.

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 2.
   • 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:
   • gg.handler.abs.format: Select how to format the output. JSON is the default setting. Available options include:
     • delimitedtext
     • json
     • json_row
     • xml
     • avro_row_ocf
     • avro_op_ocf
6. Click Create and Run.
   You're returned to the Overview page, where you can review the Replicat details.

1. Connect to Amazon RDS for Oracle instance from SQL Developer as user SRC_OCIGGLL.
2. 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);

3. In the source GoldenGate OCI GoldenGate deployment console, select the Extract name, and then click Statistics. Verify that SRC_OCIGGLL.SRC_CITY has 10 inserts.
4. In the target Big Data OCI GoldenGate deployment console, select the Replicat name, and then click Statistics. Verify that SRC_OCIGGLL.SRC_CITY has 10 inserts.

1. Monitor performance.
2. Manage Trail files.