Replicate Data to Oracle Autonomous Data Warehouse Cloud

12 Replicate Data to Oracle Autonomous Data Warehouse Cloud

This topic only applies to Data Integration Platform Cloud Classic.

You can replicate data to Oracle Autonomous Data Warehouse Cloud using Oracle Data Integration Platform Cloud .

Note:

In order to replicate data to Oracle Autonomous Data Warehouse Cloud using Data Integration Platform Cloud (previously known as Oracle-managed or Autonomous DIPC), you must install a DIPC agent on either a Cloud Linux VM or an on-premises Linux machine.

Topics:

About Replicating Data to Oracle Autonomous Data Warehouse Cloud

You can configure an Oracle Autonomous Data Warehouse Cloud instance as a target database for Oracle Data Integration Platform Cloud.

Autonomous Data Warehouse Cloud is a fully-managed data warehouse designed to support all standard SQL and business intelligence tools and deliver scalable analytic query performance. Autonomous Data Warehouse Cloud provides all of the performance of the market-leading Oracle Database in a fully-managed environment that is tuned and optimized for data warehouse workloads. Some data types, SQL commands, and database features are not available in Autonomous Data Warehouse Cloud.

To setup Oracle Autonomous Data Warehouse Cloud as a target database, you must use Oracle GoldenGate 12.3 in an Oracle Data Integration Platform Cloud instance that includes Oracle GoldenGate 12c (12.3.0.1.2). The source for the replication to Oracle Autonomous Data Warehouse Cloud can be:

Oracle GoldenGate On Premises releases 12.3.0.1.2 and later are certified with Oracle Autonomous Data Warehouse Cloud for remote delivery using the non-integrated Replicat only. However, any supported release of Oracle GoldenGate for any supported database and operating system combination that can send trail data to Oracle GoldenGate for Oracle Database release 12.3.0.1.2 and later, can be used as a source system.
Oracle Database Cloud Service on Oracle Cloud and Oracle Cloud at Customer
Oracle Exadata Cloud Service on Oracle Cloud and Oracle Cloud at Customer

Note:

You cannot setup Oracle Autonomous Data Warehouse Cloud database as a source database for Oracle Data Integration Platform Cloud.

Use Case for Replicating to Oracle Autonomous Data Warehouse Cloud with Oracle Data Integration Platform Cloud

Use Oracle Data Integration Platform Cloud to replicate data to Oracle Autonomous Data Warehouse Cloud for:

Real-time data warehousing: Replicate on-premises data to Oracle Autonomous Data Warehouse Cloud to set up a staging environment for downstream ETL or real-time data warehousing.
Operational reporting: Replicate real-time data from multiple on-premises data sources and deliver to Oracle Autonomous Data Warehouse Cloud for creating reports.

Understand What is Supported While Replicating to Autonomous Data Warehouse Cloud

Review the supported data types and limitations before replicating data to Autonomous Data Warehouse Cloud.

Understanding Limitations

For a complete list of database initialization parameter restrictions, database features restrictions, SQL commands restrictions, and data types restrictions, see Autonomous Data Warehouse Cloud for Experienced Oracle Database Users.

The Oracle Database data types that are supported by Oracle GoldenGate can be replicated to Autonomous Data Warehouse Cloud. For a complete list of supported data types, see Details of Support for Oracle Data Types in Using Oracle GoldenGate for Oracle Database. The support limitations mentioned for replicating data to Oracle Database using Oracle GoldenGate also apply to replicating data to Autonomous Data Warehouse Cloud. There are additional limitations when replicating data into Autonomous Data Warehouse Cloud as listed in the following section.

Oracle GoldenGate Replicat Limitations for Autonomous Data Warehouse Cloud

Currently, only non-integrated Replicats are supported with Autonomous Data Warehouse Cloud. Integrated and parallel Replicats are not supported.

For the best compression ratio in your target tables in Autonomous Data Warehouse Cloud, Oracle recommends replicating changes (including updates and deletes) from your source systems as inserts into staging tables and using in-database batch operations to merge the changes into your target tables.

Data Type Limitations for DDL and DML Replications

The following data types are not supported while replicating data to Autonomous Data Warehouse Cloud:

LONG
LONG RAW
XMLTYPE STORE AS OBJECT RELATIONAL
XMLTYPE STORE AS BINARY
BFILE
MEDIA
SPATIAL

The following data types are supported only when the trail file is generated through an integrated Extract.

ABSTRACT/USER DEFINED TYPE
UROWID
ANYDATA

How Do I Replicate Data to Oracle Autonomous Data Warehouse Cloud?

You need to configure non-integrated Replicats to deliver data to Oracle Autonomous Data Warehouse Cloud.

Prerequisites:

You should have the following details available with you:

Oracle Data Integration Platform Cloud instance details.
Oracle Autonomous Data Warehouse Cloud database account details.
Your source database with Oracle GoldenGate Extract processes configured.

Replication Procedure Overview

To deliver data to Oracle Autonomous Data Warehouse Cloud using Oracle Data Integration Platform Cloud, perform the following tasks:

Configure Oracle Autonomous Data Warehouse Cloud for Replication.
- Unlock the pre-created Oracle GoldenGate database user ggadmin in Oracle Autonomous Data Warehouse Cloud.
- Create new application user user_target.
- Create target database tables for replication.
Obtain Oracle Autonomous Data Warehouse Cloud client credentials file.
Configure Oracle Data Integration Platform Cloud for replication.
- Transfer the client credentials file that you downloaded from Oracle Autonomous Data Warehouse Cloud.
- Configure the sqlnet.ora file.
- Configure the tnsnames.ora file.
- Create a useridalias for the ggadmin Oracle Autonomous Data Warehouse Cloud user.
Configure Oracle GoldenGate Manager and non-integrated Replicats to deliver data to Oracle Autonomous Data Warehouse Cloud.

Configure Oracle Autonomous Data Warehouse Cloud for Replication

In the Oracle Autonomous Data Warehouse Cloud database, complete the following tasks:

Oracle Autonomous Data Warehouse Cloud has a pre-existing database user created for Oracle GoldenGate called ggadmin. The ggadmin user has been granted the right set of privileges for Oracle GoldenGate Replicat to work. By default, this user is locked. To unlock the ggadmin user, connect to your Oracle Autonomous Data Warehouse Cloud database as the ADMIN user using any SQL client tool. See About Connecting to Autonomous Data Warehouse Cloud.
Run the alter user command to unlock the ggadmin user and set the password for it. See Creating Users with Autonomous Data Warehouse Cloud.
```
alter user ggadmin identified by password account unlock;
```

Create the new application user user_target.

create user user_target identified by password;
grant create session, resource, create view, create table to user_target;

Connect to Oracle Autonomous Data Warehouse Cloud database as the user_target user and create schema and target tables for which DDL replication is not enabled.

Obtain Oracle Autonomous Data Warehouse Cloud Client Credentials File

To establish connection to your Oracle Autonomous Data Warehouse Cloud database, you download the client credentials file from the Oracle Autonomous Data Warehouse Cloud service console.

Note:

If you do not have administrator access to Oracle Autonomous Data Warehouse Cloud, you should ask your service administrator to download and provide the client credentials file to you.

Log into your Oracle Autonomous Data Warehouse Cloud account.
From the Instance page, click the menu option for the Oracle Autonomous Data Warehouse Cloud instance and select Service Console.
Log into the service console using the admin username and its associated password.
In the service console, click the Administration tab.
Click Download Client Credentials.
Enter a password to secure your client credentials file and click Download.
Save the client credentials file to your local system.

The client credentials file contains the following files:

cwallet.sso
ewallet.p12
keystore.jks
ojdbc.properties
sqlnet.ora
tnsnames.ora
truststore.jks

You refer to the sqlnet.ora and tnsnames.ora files while configuring Oracle Data Integration Platform Cloud to work with Oracle Autonomous Data Warehouse Cloud.

Configure Oracle Data Integration Platform Cloud for Replication

In the Oracle Data Integration Platform Cloud instance, complete the following tasks:

Connect to your Oracle Data Integration Platform Cloud instance.
```
ssh -i private_key opc@IP_address_of_your_instance
```
After you connect to your Oracle Data Integration Platform Cloud instance, change user to oracle.
```
sudo su - oracle
```
Transfer the client credentials file that you downloaded from Oracle Autonomous Data Warehouse Cloud to your Oracle Data Integration Platform Cloud instance.
In the Oracle Data Integration Platform Cloud instance, unzip the client credentials file into a new directory. For example, /u01/data/adwc_credentials. This will be your key directory.
To configure the connection details, open your tnsnames.ora file from the oracle client location in the Oracle Data Integration Platform Cloud instance.

Note:
Because your Oracle Data Integration Platform Cloud instance is associated with an Oracle Database Cloud instance, the tnsnames.ora file is located in thenetwork/admin directory under /u01/app/oracle/suite/oci.
```
cd /u01/app/oracle/suite/oci/network/admin
ls
tnsnames.ora
```
Edit the tnsnames.ora file in the Oracle Data Integration Platform Cloud instance to include the connection details that is available in the tnsnames.ora file in your key directory (the directory where you unzipped the client credentials file downloaded from Oracle Autonomous Data Warehouse Cloud).
```
Sample Connection String
connection_name = (description= (address=(protocol=tcps)(port=TNS Service port)(host=ADWC_IP))
(connect_data=(service_name=ADWC_Database_Name (security=(ssl_server_cert_dn="ADWC SSL certification")))
```
Note:
The tnsnames.ora file provided with the client credentials file contains three database service names identifiable as:
```
ADWC_Database_Name_low
ADWC_Database_Name_medium
ADWC_Database_Name_high
```
For Oracle GoldenGate replication, use ADWC_Database_Name_low. See Predefined Database Service Names for Autonomous Data Warehouse Cloud
To configure the wallet, create a sqlnet.ora file in the oracle client location in the Oracle Data Integration Platform Cloud instance.
```
cd /u01/app/oracle/suite/oci/network/admin
ls
sqlnet.ora tnsnames.ora
```

Edit this sqlnet.ora file to include your key directory.

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/u01/data/adwc_credentials"))) 
SSL_SERVER_DN_MATCH=yes

To create a useridalias, start GGSCI.
```
cd $GGHOME
./ggsci
```

Create the Oracle GoldenGate wallet and add the useridalias to the credential store.

GGSCI add credentialstore
GGSCI Alter credentialstore ADD USER ggadmin@connection_name PASSWORD password alias ggadmin_alias 
GGSCI DBLOGIN USERIDALIAS ggadmin_alias

Configure Oracle GoldenGate Manager and Classic Replicat to deliver to Oracle Autonomous Data Warehouse Cloud

If you are not already connected to your Oracle Data Integration Platform Cloud instance, connect using the ssh command.
```
ssh -i private_key opc@IP_address_of_your_host
```
After you connect to your Oracle Data Integration Platform Cloud instance, change user to oracle.
```
sudo su - oracle
```
From your Oracle Data Integration Platform Cloud instance, test the connection to your Oracle Autonomous Data Warehouse Cloud instance using sqlplus.
```
sqlplus ggadmin/password@connection_name 
```
After you are connected to the Oracle Autonomous Data Warehouse Cloud database in sqlplus from your Oracle Data Integration Platform Cloud instance, ensure that the target replication tables are available.
Before you run GGSCI, you must source the setup file:
```
$ source ~/.ggsetup
```

Find out the full path to GGSCI:

$ which ggsci
  alias ggsci='/u01/app/oracle/suite/gghome/ggsci'
               /u01/app/oracle/suite/gghome/ggsci

Connect to GGSCI.
```
cd $GGHOME
./ggsci
```
To configure, Oracle GoldenGate manager, open the mgr parameter file to edit it.
```
GGSCI edit param mgr
```

Ensure that the manager parameter file has the following information:

PORT port_number
Dynamicportlist port_1-port_n
ACCESSRULE, PROG COLLECTOR, IPADDR IP_Address_GGCS_host, ALLOW
PURGEOLDEXTRACTS path_to_the_trail_file, USECHECKPOINTS, MINKEEPHOURS n hours MINKEEPFILES n files
AUTORESTART ER *, RETRIES n, WAITMINUTES n, RESETMINUTES n

Add GGSCHEMA ggadmin to your GLOBALS file.
Stop and start the manager and confirm that it started.
```
GGSCI stop mgr
GGSCI start mgr
GGSCI info mgr
```

Configure your Replicat file. A sample Replicat file is listed here. For a complete list of Replicat file parameters, see Oracle GoldenGate Parameters in Oracle GoldenGate Reference.

replicat rep_name 
useridalias ggadmin_alias 
discardfile ./dirrpt/rep_name.dsc,purge 
REPORTCOUNT EVERY 5000 RECORDS
map SourceSchema.SourceTable, target TargetSchema.TargetTable [filter clauses];

In GGSCI, login to the database using useridalias and add the Replicat.
```
add replicat rep_name,exttrail ./dirdat/trail_file_name
```
Note:

Oracle Autonomous Data Warehouse Cloud times out and disconnects Replicat if it is idle for more than 60 minutes. When Replicat tries to apply changes (when it gets new changes) after being idle, it encounters a database error and abends. You must restart the Replicat unless you have configured AUTORESTART.

You have successfully configured a classic Replicat. You can now start the Replicat process and replicate data to Oracle Autonomous Data Warehouse Cloud.