Stage and merge data into Autonomous Data Warehouse using OCI GoldenGate

Before you begin

You must have the following in order to proceed:

An existing source database.
An existing target Autonomous Database.
An existing OCI Object Storage Bucket which will be used as a temporary staging area.
Before configuring ADW Stage & Merge replication, target schemas and tables should be created in the target ADW instance.
You can download Archive.zip and follow Task 0 to set up source and target databases using Autonomous Database.

Task 0: Set up the source & target Autonomous Databases

Download and unzip the sample database schema.
Set up the source Autonomous Database:
1. In the Oracle Cloud console, select your ATP instance from the Autonomous Databases page to view its details and access Database Actions.
2. Click Database Actions.
3. Enable the GGADMIN user:
  1. 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 disable Account is Locked.
  4. Click Apply Changes.
4. Load the 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;
```
Set up the target Autonomous Data Warehouse:
1. In the Oracle Cloud console, select your ADW instance from the Autonomous Databases page to view its details and access DB tools.
2. Click Database Actions.
3. In the Database Actions menu, under Development, select SQL.
4. Copy and paste the script from previously downloaded OCIGGLL_OCIGGS_SETUP_USERS_ADW.sql into the SQL worksheet.
5. Click Run Script. The Script Output tab displays confirmation messages.
6. Clear the SQL worksheet and then copy and paste the SQL script from OCIGGLL_OCIGGS_SRC_MIRROR_USER_SEED_DATA.sql
7. Click Run Script.

Task 1: Create 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 Autonomous Data Warehouse.
Create a connection for the source Autonomous Transaction Processing instance.
Create a connection for the target Autonomous Data Warehouse instance.
Create connection for OCI Object Storage.
Create a connection to GoldenGate, and then assign this connection to the source Oracle deployment.
Assign the ATP connection to the source Oracle deployment.
Assign the ADW connection the target Big Data 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 a Distribution Path

If using GoldenGate credential store, 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 OCI GoldenGate deployment console using the Administrator username and password.
4. In the OCI GoldenGate deployment console, open the navigation menu, and then click User Administration.
5. On the Users page, click Add New User (plus icon).
6. Complete the fields as follows, and then click Submit.
  - From the Authenticated By dropdown, select Password.
  - From the Role dropdown, select Operator.
  - For Username, enter a name, such as ggsnet.
  - Enter a password twice for verification.
The new user appears in the Users list.
In the source ATP deployment console, add a credential for the user created in Step 1.
1. In the source ATP 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:
  - For Credential Domain, enter GGSNetwork.
  - For Credential Alias, enter dpuser
  - For User ID, enter the name of the user created in Step 1 (ggsnet)
  - Enter the user's password twice for verification.
3. Click Submit.
The credential appears in the Credentials list.
Click Distribution Service, and then click Add Distribution Path (plus icon).
Add a Distribution Path with the following values, and then click Create Path and Run:
1. On the Path Information page, for Path Name, enter a name.
2. 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.
3. On the Target Options page:
  - For Target Host, enter the host domain of the target deployment.
    
    Note:
    You can copy and paste the URL of the target OCI GoldenGate deployment console and remove the https:// and any trailing slashes.
  - 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 (dpuser).
You're returned to the Distribution Service Overview page where you can view the status of the created path.
In the target Big Data deployment console, review the Receiver Path.
1. In the target Big Data OCI GoldenGate deployment console, click Receiver Service.
2. Review the Receiver Path details. This path was created as a result of the Receiver Path created in the previous step.

Task 4: Add and run the Replicat

In the target Big Data deployment console, click Administrator Service, and then click Add Replicat (plus icon).
Add a Replicat with the following values:
1. On the Replicat Information page, under Replicat type, select Classic Replicat, and enter a Process Name.
2. On the Replicat Options page:
  - For Name, enter the name of the Trail from Task 2.
  - For Domain, select a domain.
  - For Alias, select the OCI Object Storage connection and the Autonomous Data Warehouse connection created in Task 1.
  - For Checkpoint Table, select the checkpoint table you created for the target deployment.
3. On the Managed Options page, leave the fields as they are, and click Next.
4. On the Replicat Parameters page, change the MAP line to the following:
```
MAP SRC_OCIGGLL.*, TARGET SRCMIRROR_OCIGGLL.*;
```
Click Create and Run.

You're returned to the Overview page, where youc an review the Replicat details.

Task 5: Verify the replication

In the Oracle Cloud console, from 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.

In the source GoldenGate OCI GoldenGate deployment console, select the Extract name, and then click Statistics. Verify that SRC_OCIGGLL.SRC_CUSTOMER has 7 inserts.

Insert into SRC_OCIGGLL.SRC_CUSTOMER (CUSTID,DEAR,LAST_NAME,FIRST_NAME,ADDRESS,CITY_ID,PHONE,AGE,SALES_PERS_ID) values (1001,0,'Brendt','Paul','10 Jasper Blvd.',107,'(212) 555 2146',19,10);
Insert into SRC_OCIGGLL.SRC_CUSTOMER (CUSTID,DEAR,LAST_NAME,FIRST_NAME,ADDRESS,CITY_ID,PHONE,AGE,SALES_PERS_ID) values (1002,0,'McCarthy','Robin','27 Pasadena Drive',11,'(214) 555 3075',29,11);
Insert into SRC_OCIGGLL.SRC_CUSTOMER (CUSTID,DEAR,LAST_NAME,FIRST_NAME,ADDRESS,CITY_ID,PHONE,AGE,SALES_PERS_ID) values (1003,0,'Travis','Peter','7835 Hartford Drive',12,'(510) 555 4448',34,12);
Insert into SRC_OCIGGLL.SRC_CUSTOMER (CUSTID,DEAR,LAST_NAME,FIRST_NAME,ADDRESS,CITY_ID,PHONE,AGE,SALES_PERS_ID) values (1004,0,'Larson','Joe','87 Carmel Blvd.',13,'(213) 555 5095',45,13);
Insert into SRC_OCIGGLL.SRC_CUSTOMER (CUSTID,DEAR,LAST_NAME,FIRST_NAME,ADDRESS,CITY_ID,PHONE,AGE,SALES_PERS_ID) values (1005,0,'Goldschmidt','Tony','91 Torre drive',14,'(619) 555 6529',55,20);
Insert into SRC_OCIGGLL.SRC_CUSTOMER (CUSTID,DEAR,LAST_NAME,FIRST_NAME,ADDRESS,CITY_ID,PHONE,AGE,SALES_PERS_ID) values (1006,0,'Baker','William','2890 Grant Avenue',15,'(312) 555 7040',64,21);
Insert into SRC_OCIGGLL.SRC_CUSTOMER (CUSTID,DEAR,LAST_NAME,FIRST_NAME,ADDRESS,CITY_ID,PHONE,AGE,SALES_PERS_ID) values (1007,0,'Swenson','Jack','64 Imagination Drive',19,'(202) 555 8125',74,22);

In the target Big Data OCI GoldenGate deployment console, select the Replicat name, and then click Statistics. Verify that SRC_OCIGGLL.SRC_CUSTOMER has 7 inserts.
In target Autonomous Data Warehouse Cloud SQL console, execute the following command to validate the data replicated:
```
select * from SRCMIRROR_OCIGGLL.SRC_CUSTOMER;
```