Note:
- This tutorial requires access to Oracle Cloud. To sign up for a free account, see Get started with Oracle Cloud Infrastructure Free Tier.
- It uses example values for Oracle Cloud Infrastructure credentials, tenancy, and compartments. When completing your lab, substitute these values with ones specific to your cloud environment.
Replicate data from Amazon RDS for Oracle to OCI Object Storage using OCI GoldenGate
Introduction
Oracle Cloud Infrastructure (OCI) GoldenGate is a fully managed, native cloud service that moves data in real-time, at scale. OCI GoldenGate processes data as it moves from one or more data management systems to target databases. You can also design, run, orchestrate, and monitor data replication tasks without having to allocate or manage any compute environments.
Oracle is responsible for provisioning resources and for periodically releasing patches and upgrades for OCI GoldenGate. The customer is responsible for initiating provisioning requests that specify configuration characteristics of the resource being provisioned. The customer is also responsible for initiating upgrade requests.
At Oracle CloudWorld, Oracle has announced 30+ new data platforms for OCI GoldenGate. These new connectors expand the existing Oracle Database replication to non-Oracle replication and extend connectivity to open source and 3rd party technologies, on the major cloud provider platforms. This new connectivity has already started to roll out in OCI data centers and will continue through Q1 2023.
This tutorial provides the steps to replicate data from Amazon RDS for Oracle to OCI Object Storage using Oracle Cloud Infrastructure GoldenGate.
Objective
- Learn how to provision OCI GoldenGate Extract and Replicat.
- Learn how to use OCI GoldenGate to replicate data from Amazon RDS to OCI Object Storage.
Prerequisites
- Sign up or Sign in to your Oracle Cloud account
- A source Amazon RDS for Oracle instance
Task 1: Set up the source Amazon RDS for Oracle
Provision an Amazon RDS for Oracle instance and carry out following steps to setup a source database for use with Oracle GoldenGate.
-
Turn on supplemental logging on the source database.
-
Set ENABLE_GOLDENGATE_REPLICATION initialization parameter to
true
. -
Enable archiving on Source database and retain archived redo logs.
-
Create an Oracle GoldenGate user account on the source database.
-
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);
-
Download and unzip the sample database schema.
-
Load the source sample schema and data:
a. Connect to Amazon RDS for Oracle instance from SQL Developer as user
SRC_OCIGGLL
.b. Copy and paste the script from OCIGGLL_OCIGGS_SETUP_USERS_ATP.sql into the SQL worksheet.
c. Click Run Script. The Script Output tab displays confirmation messages.
d. 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.
e. 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.
Task 2: 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 Amazon RDS for Oracle instance.
-
Create a Big Data deployment for the target OCI Object Storage bucket.
-
Create a connection for the source Amazon RDS for Oracle instance.
-
(Optional) 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.
-
Assign the Amazon RDS for Oracle connection to the source Oracle deployment.
-
Assign the OCI Object Storage connection to the target Big Data deployment.
Task 3: Add and run the Extract
-
On the Deployments page, select the source Amazon RDS for Oracle deployment.
-
On the Deployment details page, click Launch Console.
-
Log in with the source deployment’s administrator user name and password.
Task 4: Add and run a Distribution Path
-
Create a user for the Distribution Path in the target Big Data deployment.
a. On the Deployments page, select the target deployment to view details.
b. On the Deployment details page, click Launch Console.
c. Sign in to the target OCI GoldenGate deployment console using the Administrator user name and password.
d. In the OCI GoldenGate deployment console, open the navigation menu, and then click Administrator.
e. On the Users page, click Add New User (plus icon).
f. Complete the fields as follows, and then click Submit.
- Username, enter a name, such as
ggsnet
. - From the Role drop-down list, select Operator.
- Enter a password twice for verification.
The new user displays in the Users list.
- Username, enter a name, such as
-
In the source Amazon RDS for Oracle GoldenGate deployment console, add a credential for the user created in Step 1.
a. In the source Amazon RDS for Oracle GoldenGate deployment console, click Administration Service, open the navigation menu, and then select Configuration.
b. On the Credentials page, click Add Credential, and then complete the fields as follows:
- Credential Domain, enter
GGSNetwork
. - Credential Alias, enter
dpuser
- User ID, enter the name of the user created in Step 1 (
ggsnet
) - Enter the user’s password twice for verification.
c. Click Submit.
The credential displays in the Credentials list.
- Credential Domain, enter
-
Click Distribution Service, and then click Add Path (plus icon).
-
Complete the Add Path form fields as follows, and then click Create and Run:
a. Path Name, enter a name.
b.Source Extract, select the Extract created in Task 2.
c. Source Trail Name, select the Extract Trail from Task 2.
d. Target Authentication Method, select UserID Alias.
e. Target protocol, select wss.
f. Target Host, enter the host domain of the target deployment.
g. Port Number, enter
443
.h. Trail Name, enter a two-character name, such as E1.
i. Domain, enter the name of the Credential Domain created in Step 2 (
GGSNetwork
).j. Alias, enter the Credential Alias created in Step 2 (
dpuser
).The Distribution Service Overview page displays, where you can view the status of the created path.
-
In the target Big Data deployment console, click Receiver Service to review the Receiver Path details. This path was created as a result of the Distribution Path created in the previous step.
Task 5: Add and run the Replicat
-
In the target Big Data deployment console, click Administrator Service, and then click Add Replicat (plus icon).
-
On the Add Replicat page, under Replicat type, select Classic Replicat, and then click Next.
-
On the Replicat Options page, complete the following form fields, and then click Next:
- Process Name, enter a name.
- Trail Name, enter the name of the Trail from Task 2.
- Target, select OCI Object Storage.
- Alias, select the OCI Object Storage connection created in Task 2.
-
On the Replicat Parameters page, add the following mapping, and then click Next:
MAP SRC_OCIGGLL.*, TARGET *.*;
-
On the Properties page, configure OCI Event Handler properties as needed, and then click Next.
Required properties:
gg.eventhandler.oci.compartmentID
: The compartment OCID in which the OCI Object Storage bucket resides.gg.handler.oci.fileNameMappingTemplate
: Generates file 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:Tip: To use the formatting property for OCI Object Storage, replace
name
withoci
. For example,gg.handler
.name.format
becomesgg.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.
-
Click Create and Run.
The Overview page displays, where you can review the Replicat details.
Task 6: Verify the replication
Perform some updates to the source Amazon RDS for Oracle instance to verify replication to OCI Object Storage.
-
Connect to Amazon RDS for Oracle instance from SQL Developer as user
SRC_OCIGGLL
. -
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 OCI GoldenGate 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 the contents.
Task 7: Monitor and maintain processes
Related Links
Acknowledgments
- Author - Vivek Verma (Master Principal Cloud Architect, North America Cloud Engineering)
More Learning Resources
Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.
For product documentation, visit Oracle Help Center.
Replicate data from Amazon RDS for Oracle to OCI Object Storage using OCI GoldenGate
F74730-01
December 2022
Copyright © 2022, Oracle and/or its affiliates.