19 Replicating Data to Oracle Autonomous Data Warehouse Cloud

Use Case for Replicating to the Autonomous Database with Oracle GoldenGate On Premises

Oracle GoldenGate Replicat Limitations for Oracle Autonomous Data Warehouse Cloud

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

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 Replications

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.

• Configure Oracle Autonomous Data Warehouse Cloud for Replication

  • Unlock the pre-created Oracle GoldenGate database user ggadmin in Oracle Autonomous Data Warehouse Cloud.

  • 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 Oracle Autonomous Data Warehouse Cloud client credentials.

• Configure Oracle GoldenGate On Premises for replication.

  • Transfer client credentials ZIP file that you downloaded from Oracle Autonomous Data Warehouse Cloud.

  • Configure sqlnet.ora file.

  • Configure tnsnames.ora file.

  • Create useridalias for the ggadmin Oracle Autonomous Data Warehouse Cloud user.

• Configure Oracle GoldenGate Manager and non-integrated Replicats to deliver to Oracle Autonomous Data Warehouse Cloud.

Configure Oracle Autonomous Data Warehouse Cloud for Replication

The steps to complete the configuration tasks are:

1. Oracle Autonomous Data Warehouse Cloud has a pre-existing user created for Oracle GoldenGate On Premises called ggadmin. The ggadmin 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 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.

2. 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;
   

3. 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;
   

4. 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 Oracle Autonomous Data Warehouse Cloud database, you download client credentials files from Oracle Autonomous Data Warehouse Cloud service console. See Downloading Client Credentials (Wallets).

1. Log into your Oracle Autonomous Data Warehouse Cloud account.

2. From the Instance page, click the menu option for the Oracle Autonomous Data Warehouse Cloud instance and select Service Console.

3. Log into the service console using the admin username and its associated password.

4. In the service console, click the Administration tab.

5. Click Download Client Credentials.

6. Enter a password to secure your credentials zip file and click Download.

7. Save the credentials ZIP file to your local system.

Configure Oracle GoldenGate On Premises for Replication

In the Oracle GoldenGate On Premises instance, you need to complete the following:

1. Log into your Oracle GoldenGate On Premises Oracle Database.

2. Create a new Oracle GoldenGate On Premises user.

   CREATE user user_src IDENTIFIED BY user_src_password;
   grant dba, connect, resource to user_src;
   

3. 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;
   

4. Create your Extract.

   extract ext1
   userid user_src, password user_src_password
   exttrail ./dirdat/rs
   table user_src.dwcs_key;
   

5. 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;
   

6. 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
   

7. Connect to your Oracle GoldenGate On Premises instance.

   ssh -i private_key -v -f -N -D port opc@IP_address_of_your_instance
   

8. Once you are connected to your Oracle GoldenGate On Premises instance, change user to oracle.

   sudo su - oracle
   

9. Transfer the credentials ZIP file that you downloaded from Oracle Autonomous Data Warehouse Cloud to your Oracle GoldenGate On Premises instance.

10. In the Oracle GoldenGate On Premises instance, unzip the credentials file into a new directory /u02/data/adwc_credentials. This is your key directory.

11. 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, the tnsnames.ora file will exist. Otherwise, you need to create the tnsnames.ora file and the network/admin directories under /u02/data/oci.

    cd /u02/data/oci/network/admin
    ls
    tnsnames.ora
    

12. Edit the tnsnames.ora file in the Oracle GoldenGate On Premises instance to include the connection details that is available in the tnsnames.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:

    The tnsnames.ora file provided with the 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
13. 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
    

14. 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
    

15. To create a useridalias, start GGSCI.

    cd $GGHOME
    ./ggsci
    

16. 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

1. 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
   

2. Once you are connected to your Oracle GoldenGate On Premises instance, change the user to oracle.

   sudo su - oracle
   

3. From your Oracle GoldenGate On Premises instance, test the connection to your Oracle Autonomous Data Warehouse Cloud instance using sqlplus.

   sqlplus ggadmin/password@connection_name 
   

4. 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;
   

5. Log in as your new replication user.

   sqlplus user_target/password_target@databasename_low
   

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

7. Connect to GGSCI.

   cd $GGHOME
   ./ggsci
   

8. To configure, Oracle GoldenGate manager, open the mgr parameter file to edit it.

   GGSCI>edit param mgr
   

9. 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
   

10. Add GGSCHEMA ggadmin to your GLOBALS file.

11. Stop and start the manager and confirm that it started.

    GGSCI>stop mgr
    GGSCI>start mgr
    GGSCI>info mgr
    

12. 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;
    

13. 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 Oracle Autonomous Data Warehouse Cloud.

    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.

14. 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