19 Replicating Data to the Autonomous Database
You can replicate data to Oracle Autonomous Data Warehouse Cloud and Autonomous Transaction Processing using Oracle GoldenGate On Premises and Oracle GoldenGate Cloud Service.
Topics:
- About Replicating Data to Autonomous Databases
You can configure the Autonomous Database instance as a target database for Oracle GoldenGate On Premises. - Understanding What is Supported While Replicating to the Autonomous Database
Review the supported data types and limitations before replicating data to the Autonomous Database. - How Do I Replicate Data to the Autonomous Database?
You need to configure non-integrated Replicats to deliver data to the Autonomous Database.
About Replicating Data to Autonomous Databases
You can configure the Autonomous Database instance as a target database for Oracle GoldenGate On Premises.
-
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 Replicats 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 source systems.
-
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 can’t set up Oracle Autonomous Data Warehouse Cloud database as a source database for Oracle GoldenGate On Premises.Use Case for Replicating to the Autonomous Database with Oracle GoldenGate On Premises
-
Real-time data warehousing: Replicate on-premises data to the Autonomous Database 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 the Autonomous Database for creating reports.
Parent topic: Replicating Data to the Autonomous Database
Understanding What is Supported While Replicating to the Autonomous Database
Review the supported data types and limitations before replicating data to the Autonomous Database.
Understanding Limitations
Oracle Autonomous Data Warehouse Cloud is a fully-managed data warehouse designed to support all standard SQL and business intelligence (BI) tools and deliver scalable analytic query performance. Oracle 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 Oracle Autonomous Data Warehouse Cloud.
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.
For more information on Autonomous Transaction Processing, see Getting Started wtih Autonomous Transaction Processing
The Oracle Database data types that are supported by Oracle GoldenGate can be replicated to Oracle Autonomous Data Warehouse Cloud. For a complete list of supported data types, see Details of Support for Oracle Data Types. The support limitations mentioned for replicating data to Oracle Database using Oracle GoldenGate apply to replicating data to Oracle Autonomous Data Warehouse Cloud too. There are additional limitations when replicating data into Oracle Autonomous Data Warehouse Cloud as listed in the following section.
Oracle GoldenGate Replicat Limitations for Oracle Autonomous Data Warehouse Cloud
Currently, only non-integrated Replicats are supported with Oracle Autonomous Data Warehouse Cloud.
For the best compression ratio in your target tables in Oracle 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 Replication
The following data types are not supported while replicating data to Oracle 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
Only Non-integrated Replicats are supported.
DDL replication is supported depending on the restrictions in Oracle Autonomous Data Warehouse Cloud and Autonomous Transaction Processing.
Parent topic: Replicating Data to the Autonomous Database
How Do I Replicate Data to the Autonomous Database?
You need to configure non-integrated Replicats to deliver data to the Autonomous Database.
Prerequisites:
You should have the following details available with you:-
Oracle GoldenGate On Premises instance details.
-
Oracle Autonomous Data Warehouse Cloud or Autonomous Transaction Processing) .
-
Your source database with Oracle GoldenGate On Premises Extract processes configured.
-
Configure the Autonomous Database for Replication
-
Unlock the pre-created Oracle GoldenGate database user
ggadmin
in the Autonomous Database. -
Create schema and target tables for which DDL replication is not enabled. See Understanding What is Supported While Replicating to the Autonomous Database.
-
Create new application user
user_target
.
-
-
Obtain the Autonomous Database client credentials.
-
Configure Oracle GoldenGate On Premises for replication.
-
Transfer client credentials ZIP file that you downloaded from the Autonomous Database.
-
Configure
sqlnet.ora
file. -
Configure
tnsnames.ora
file. -
Create
useridalias
for theggadmin
user.
-
-
Configure Oracle GoldenGate Manager and non-integrated Replicats to deliver to the Autonomous Database.
Configure the Autonomous Database for Replication
The steps to complete the configuration tasks are:
-
The Autonomous Database has a pre-existing user created for Oracle GoldenGate On Premises called
ggadmin
. Theggadmin
user has been granted the right set of privileges for Oracle GoldenGate On Premises Replicat to work. By default, this user is locked. To unlock theggadmin
user, connect to your Oracle Autonomous Data Warehouse Cloud database as theADMIN
user using any SQL client tool. See About Connecting to Autonomous Data Warehouse Cloud. -
Run the
alter user
command to unlock theggadmin
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 your replication tables.
Obtain Oracle Autonomous Data Warehouse Cloud Client Credentials
To establish connection to your the Autonomous Database, you download client credentials files from the Autonomous Database service console. See Downloading Client Credentials (Wallets).
Note:
If you do not have administrator access to the Autonomous Database you should ask your service administrator to download and provide the credentials files to you.
-
Log into your Oracle Autonomous Data Warehouse Cloud account.
-
From the Instance page, click the menu option for the Autonomous Database 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 credentials zip file and click Download.
-
Save the credentials ZIP file to your local system.
-
cwallet.sso
-
ewallet.p12
-
keystore.jks
-
ojdbc.properties
-
sqlnet.ora
-
tnsnames.ora
-
truststore.jks
sqlnet.ora
and tnsnames.ora
files while configuring Oracle GoldenGate On Premises to work with the Autonomous Database.
Configure Oracle GoldenGate On Premises for Replication
In the Oracle GoldenGate On Premises instance, you need to complete the following:
-
Log into your Oracle GoldenGate On Premises Oracle Database.
-
Create a new Oracle GoldenGate On Premises user.
CREATE user user_src IDENTIFIED BY user_src_password; grant dba, connect, resource to user_src;
-
Create your tables and primary key.
DROP TABLE user_src.dwcs_key; CREATE TABLE user_src.dwcs_key (n number, vc varchar2(10),PRIMARY KEY (n)); alter database add supplemental log data;
-
Create your Extract.
extract ext1 userid user_src, password user_src_password exttrail ./dirdat/rs table user_src.dwcs_key;
-
Create your Extract data pump.
extract ext1pump userid user_src, password user_src_password RMTHOST host_IP_address, MGRPORT manager_port, SOCKSPROXY socksproxy_IP_address rmttrail ./dirdat/rt table user_src.dwcs_key;
-
Add your Extracts.
add extract ext1,tranlog begin now add exttrail ./dirdat/rs,extract ext1 add extract ext1pump,exttrailsource ./dirdat/rs add rmttrail ./dirdat/rt,extract ext1pump dblogin user_src, password user_src_password add trandata user_src.dwcs_key
-
Connect to your Oracle GoldenGate On Premises instance.
ssh -i private_key -v -f -N -D port opc@IP_address_of_your_instance
-
Once you are connected to your Oracle GoldenGate On Premises instance, change user to
oracle
.sudo su - oracle
-
Transfer the credentials ZIP file that you downloaded from Oracle Autonomous Data Warehouse Cloud to your Oracle GoldenGate On Premises instance.
-
In the Oracle GoldenGate On Premises instance, unzip the credentials file into a new directory
/u02/data/adwc_credentials
. This is your key directory. -
To configure the connection details, open your
tnsnames.ora
file from the oracle client location in the Oracle GoldenGate On Premises instance.Note:
If your Oracle GoldenGate On Premises is associated with Oracle Database Cloud Service, thetnsnames.ora
file will exist. Otherwise, you need to create thetnsnames.ora
file and thenetwork/admin
directories under/u02/data/oci
.cd /u02/data/oci/network/admin ls tnsnames.ora
-
Edit the
tnsnames.ora
file in the Oracle GoldenGate On Premises instance to include the connection details that is available in thetnsnames.ora
file in your key directory (the directory where you unzipped the credentials ZIP 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:
Thetnsnames.ora
file provided with the credentials file contains three database service names identifiable as:
For Oracle GoldenGate replication, useADWC_Database_Name_low ADWC_Database_Name_medium ADWC_Database_Name_high
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 GoldenGate On Premises instance.cd /u02/data/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="
/u02/data/adwc_credentials
"))) SSL_SERVER_DN_MATCH=yes -
To create a
useridalias
, startGGSCI
.cd $GGHOME ./ggsci
-
Create the Oracle GoldenGate wallet and add the
useridalias
to the credential store.GGSCI> Create Wallet 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 GoldenGate On Premises instance, connect using the
ssh
command.ssh -i private_key opc@IP_address_of_your_VM
-
Once you are connected to your Oracle GoldenGate On Premises instance, change the user to
oracle
.sudo su - oracle
-
From your Oracle GoldenGate On Premises instance, test the connection to your the Autonomous Database instance using
sqlplus
.sqlplus ggadmin/password@connection_name
-
Create a new user for replication.
drop user user_target cascade; create user user_target identified by password_target; alter user user_target; grant create session, resource, create view, create table to user_target;
-
Log in as your new replication user.
sqlplus user_target/password_target@databasename_low
-
Create your replication tables.
create table dwcs_keytest (n number, vc varchar2(10)); alter table dwcs_keytest add constraint pk_dwcs_key primary key (n);
-
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 yourGLOBALS
file. -
Stop and start the manager and confirm that it started.
GGSCI>stop mgr GGSCI>start mgr GGSCI>info mgr
-
Configure your Replicat file. For a complete list of Replicat parameters, see Oracle GoldenGate Parameters in Oracle GoldenGate Reference.
replicat repdwcs USERIDALIAS ggadmin_databasename_low map user_src.dwcs_key, target user_target.dwcs_key;
-
Add your Replicat to create a Replicat group.
ADD CREDENTIALSTORE ALTER CREDENTIALSTORE ADD USER ggadmin@databasename_low PASSWORD complex_password alias ggadmin_databasename_low DBLOGIN USERIDALIAS ggadmin_databasename_low add replicat repdwcs, exttrail ./dirdat/rt
See Understanding What is Supported While Replicating to the Autonomous Database for
nodbcheckpoint
limitations.You have successfully configured a classic Replicat. You can now start your Replicat and test data replication to the Autonomous Database.
Note:
Oracle Autonomous Data Warehouse Cloud times out and disconnects the Replicat when 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. Oracle recommends that you configure Oracle GoldenGate On Premises with
AUTORESTART
to avoid having to manually restart a Replicat when it times out. -
Insert records into your source database, and then ensure that the data is replicated into your Oracle Autonomous Data Warehouse Cloud table using the
stats REPDWCS
command.GGSCI> STATS REPDWCS Sending STATS request to REPLICAT REPDWCS ... Start of Statistics at 2018-01-18 07:21:26. Replicating from user_src.dwcs_key to databasename .user_target.DWCS_KEY: *** Total statistics since 2018-01-18 06:28:30 *** Total inserts 3.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 3.00
Parent topic: Replicating Data to the Autonomous Database