Replicate data from Autonomous Transaction Processing to OCI Object Storage

Before you begin

To successfully complete this quickstart, you must have:

An API key created and downloaded from the user details page
A source Autonomous Transaction Processing instance
Your compartment OCID

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:

Download and unzip the sample database schema.
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.
Unlock the GGADMIN user:
1. Click Database actions, then 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.
Load the source sample schema and data:
1. From the Database actions 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.
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.

Create an Oracle deployment for the source Autonomous Transaction Processing instance.
Create a Big Data deployment for the target OCI Object Storage bucket.
Create a connection for the source Autonomous Transaction Processing instance.
Create connection for the target OCI Object Storage.
Create a connection to GoldenGate, and then assign this connection to the source Oracle deployment.
Assign the Autonomous Transaction Processing connection to the source Oracle deployment.
Assign the OCI Object Storage connection to the target Big Data deployment.

Task 2: Add the Extract

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

Task 3: Add and run the Distribution Path

If using GoldenGate credential store, create a user for the Distribution Path in the target Big Data deployment, otherwise skip to Step 3.
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.
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.
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.

Task 4: Add and run the Replicat

In the target Big Data deployment console navigation menu, click Replicats.
On the Replicats page, click Add Replicat (plus icon), and then complete the form as follows:
1. On the Replicat Information page:
  1. For Replicat Type, select Classic Replicat.
  2. For Process Name, enter a name, such as REP
2. On the Replicat Options page:
  1. For Replicat Trail Name, enter a two-character name, such as E1.
  2. For Target, select OCI Object Storage
3. On the Parameter File page, replace MAP *.*, TARGET *.*; with:
```
MAP SRC_OCIGGLL.*, TARGET *.*;
```
4. On the Properties File page, configure OCI Event Handler properties as needed.
  Required properties:
  - gg.eventhandler.oci.compartmentID: The compartment OCID in which the OCI Object Storage bucket resides.
  - gg.handler.oci.fileNameMappingTemplate: Generates files names dynamically using Template Keywords.
  Properties you may consider modifying include:
  - gg.handler.oci.format: Select how to format the output. json_row is the default setting. Available options include:
    
    delimitedtext
    
    json
    
    json_row
    
    xml
    
    avro_row_ocf
    
    avro_op_ocf
    
    Tip:
    To use the formatting property for OCI Object Storage, replace name with oci. For example, gg.handler.name.format becomes gg.handler.oci.format.
  - gg.handler.oci.inactivityRollInterval: GoldenGate creates a file and keeps it open for writing. This property closes the file after the designated period of inactivity (no incoming transactions), and then loads it into OCI Object Storage. By default, it is set to 5 seconds. You can specify a time in milliseconds (ms), seconds (s), minutes (m), or hours (h). For example, gg.handler.oci.inactivityRollInterval=10m.
  - gg.handler.oci.maxFileSize: File Writer Handler opens the file and keeps it open until it reaches the maximum file size, assuming there are no metadata changes. By default, the maximum file size is 1 GB, however you can change it using this property. When the size is reached, file is closed, and a new file is generated. For example, gg.handler.oci.maxFileSize=500m.
  - gg.handler.oci.rollOnShutdown: The default value is true. When set to true, GoldenGate shuts the open file when you stop the Replicat process. By default, File Writer Handler keeps the file open even if the Replicat stops and continues writing to the same file when the Replicat restarts. For example, gg.handler.oci.rollOnShutdown=false
  - gg.handler.oci.fileRollInterval: Designates the amount of time to keep the file open before it's closed and rolls over to a new file. By default it is set to 7 minutes. You can specify a time in milliseconds (ms), seconds (s), minutes (m), or hours (h). For example, gg.handler.oci.fileRollInterval=10m.
  - gg.eventhandler.oci.bucketMappingTemplate: Enter the Object Storage bucket name.
5. Click Create and Run.

Task 5: Verify the replication

Perform some updates to the source Autonomous Transaction Processing instance to verify replication to OCI Object Storage.

In the Oracle Cloud console, open the navigation menu, select Oracle Database, and then select Autonomous Transaction Processing.
In the list of Autonomous Transaction Processing instances, select your source instance to view its details.
On the database details page, click Database actions.

Note:
You should be automatically logged in. If not, log in with the database credentials.
On the Database actions home page, select SQL.

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);

In the source ATP deployment console, select the Extract name, and then click Statistics. Verify that SRC_OCIGGLL.SRC_CITY has 10 inserts.
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.
In the Oracle Cloud console, navigate to the OCI Object Storage bucket and check its contents.

Task 6: Monitor and maintain processes

Learn more

Learn more about configuration details and supported file formats.