OCI Autonomous Data Warehouse

Oracle Autonomous Data Warehouse (ADW) is a fully managed database tuned and optimized for data warehouse workloads with the market-leading performance of Oracle Database.

• Detailed Functionality The ADW Event handler is used as a downstream Event handler connected to the output of the OCI Object Storage Event handler. The OCI Event handler loads files generated by the File Writer Handler into Oracle OCI Object storage. All the SQL operations are performed in batches providing better throughput.

• ADW Database Credential to Access OCI ObjectStore File

• ADW Database User Privileges ADW databases come with a predefined database role named DWROLE. If the ADW ‘admin’ user is not being used, then the database user needs to be granted the role DWROLE.

• Unsupported Operations/ Limitations

• Troubleshooting and Diagnostics

• Classpath ADW apply relies on the upstream File Writer handler and the OCI Event handler. Include the required jars needed to run the OCI Event handler in gg.classpath.

• Configuration

• Restrictions on Database Object Names

Detailed Functionality

The ADW Event handler is used as a downstream Event handler connected to the output of the OCI Object Storage Event handler. The OCI Event handler loads files generated by the File Writer Handler into Oracle OCI Object storage. All the SQL operations are performed in batches providing better throughput.

ADW Database Credential to Access OCI ObjectStore File

To access the OCI ObjectStore File:

1. A PL/SQL procedure needs to be run to create a credential to access Oracle Cloud Infrastructure (OCI) Object store files.

2. An OCI authentication token needs to be generated under User settings from the OCI console. For example:

   BEGIN  DBMS_CLOUD.create_credential
         (    credential_name =>
           'OGGBD-CREDENTIAL',    username => 'oci-user',    password =>
           'oci-user');
           END;
           /

3. The credential name can be configured using the followng property: gg.eventhandler.adw.objectStoreCredential. For example: gg.eventhandler.adw.objectStoreCredential=OGGBD-CREDENTIAL.

ADW Database User Privileges

ADW databases come with a predefined database role named DWROLE. If the ADW ‘admin’ user is not being used, then the database user needs to be granted the role DWROLE.

This role provides the privileges required for data warehouse operations. For example, the following command grants DWROLE to the user dbuser-1:

GRANT DWROLE TO dbuser-1;

Note: Ensure that you do not use Oracle-created database user ggadmin for ADW replication, because this user lacks the INHERIT privilege.

Unsupported Operations/ Limitations

• DDL changes are not supported.

• Replication of Oracle Object data types are not supported.

• If the GoldenGate trail is generated by Oracle Integrated capture, then for the UPDATE operations on the source LOB column, only the changed portion of the LOB is written to the trail file. Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) Autonomous Data Warehouse (ADW) apply doesn’t support replication of partial LOB columns in the trail file.

• ADW Event Handler and External Table Schema Constraints: There is a known limitation in the ADW event handler where external tables cannot be created within their own dedicated schema. This impacts replication configurations involving multiple distinct target schemas, for example, eb1, eb2, and eb

  1. As a result, each unique target schema requires its own dedicated replicat group to ensure proper handling and isolation.

  Current Configuration Example: The following mapping rules illustrate a multi-schema replication setup:

  MAP eb1.*, TARGET eb1.* ;
  MAP eb2.*, TARGET eb2.* ;
  MAP eb3.*, TARGET eb3.* ;

  In this scenario, due to the ADW limitation, you must configure separate replicat groups for each schema, for example, one for eb1 and one for eb2, rather than using a single replicat group for all mappings.

  Workaround: Use individual replicat groups per target schema to bypass the schema creation restriction for external tables. Note: OCI Autonomous Data Warehouse does not currently support multi-schema in Oracle GoldenGate replication due to dependency in external table creation under the admin user.

Troubleshooting and Diagnostics

• Connectivity Issues to ADW

  • Validate JDBC connection URL, user name and password.

  • Check if http/https proxy is enabled. See ADW proxy configuration: Prepare for Oracle Call Interface (OCI), ODBC, and JDBC OCI Connections in Using Oracle Autonomous Data Warehouse on Shared Exadata Infrastructure.

• DDL not applied on the target table: The ADW handler will ignore DDL.

• Target table existence: It is expected that the ADW target table exists before starting the apply process. Target tables need to be designed with appropriate primary keys, indexes and partitions. Approximations based on the column metadata in the trail file may not be always correct. Therefore, replicat will ABEND if the target table is missing.

• Diagnostic throughput information on the apply process is logged into the handler log file.

  For example:

  File Writer finalized 29525834 records
          (rate: 31714) (start time: 2020-02-10 01:25:32.000579) (end time: 2020-02-10
          01:41:03.000606).

  In this sample log message:

  • This message provides details about the end-end throughput of File Writer handler and the downstream event handlers (OCI Event handler and ADW event handler).

  • The throughput rate also takes into account the wait-times incurred before rolling over files.

  • The throughput rate also takes into account the time taken by the OCI event handler and the ADW event handler to process operations.

  • The above examples indicates that 29525834 operations were finalized at the rate of 31714 operations per second between start time: [2020-02-10 01:25:32.000579] and end time: [2020-02-10 01:41:03.000606].

  Example:

  
  INFO 2019-10-01 00:36:49.000490 [pool-8-thread-1] -- Begin DWH Apply stage and load statistics
  ********START*********************************
  
  INFO 2019-10-01 00:36:49.000490 [pool-8-thread-1] - Time spent for staging process [2074 ms]
  INFO 2019-10-01 00:36:49.000490 [pool-8-thread-1] - Time spent for merge process [992550 ms]
  INFO 2019-10-01 00:36:49.000490 [pool-8-thread-1] - [31195516] operations processed, rate[31,364]operations/sec.
  
  INFO 2019-10-01 00:36:49.000490 [pool-8-thread-1] -- End DWH Apply stage and load statistics
  ********END***********************************
  INFO 2019-10-01 00:37:18.000230 [pool-6-thread-1] -- Begin OCI Event handler upload statistics
  ********START*********************************
  INFO 2019-10-01 00:37:18.000230 [pool-6-thread-1] - Time spent loading files into ObjectStore [71789 ms]
  INFO 2019-10-01 00:37:18.000230 [pool-6-thread-1] - [31195516] operations processed, rate[434,545] operations/sec.
  INFO 2019-10-01 00:37:18.000230 [pool-6-thread-1] -- End OCI Event handler upload statistics
  ********END***********************************

  In this example:

  ADW Event handler throughput:

  • In the above log message, the statistics for the ADW event handler is reported as DWH Apply stage and load statistics. ADW is classified as a Data Ware House (DWH), and therefore, this name.

  • Here 31195516 operations from the source trail file were applied to ADW database at the rate of 31364 operations per second.

  • ADW uses stage and merge. The time spent on staging is 2074 milliseconds and the time spent on executing merge SQL is 992550 milliseconds.

  OCI Event handler throughput:

  • In the above log message, the statistics for the OCI event handler is reported as OCI Event handler upload statistics.

  • Here 31195516 operations from the source trail file were uploaded to the OCI object store at the rate of 434545 operations per second.

• Errors due to ADW credential missing grants to read OCI object store files:

  • A SQL exception indicating authorization failure is logged in the handler log file. For example:

    java.sql.SQLException: ORA-20401:
    Authorization failed for URI -
    https://objectstorage.us-ashburn-1.oraclecloud.com/n/some_namespace/b/some_bucket/o/ADMIN.NLS_AllTypes/ADMIN.NLS_AllTypes_2019-12-16_11-44-01.237.avro

• Errors in file format/column data:

  In case the ADW Event handler is unable to read data from the external staging table due to column data errors, the Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) handler log file provides diagnostic information to debug the issue.

  The following details are available in the log file:

  • JOB ID

  • SID

  • SERIAL #

  • ROWS_LOADED

  • START_TIME

  • UPDATE_TIME

  • STATUS

  • TABLE_NAME

  • OWNER_NAME

  • FILE_URI_LIST

  • LOGFILE_TABLE

  • BADFILE_TABLE

  The contents of the LOGFILE_TABLE and BADFILE_TABLE should indicate the specific record and the column(s) in the record which have error and the cause of the error. This information is also queried automatically by the ADW Event handler and logged into the OGGBD FW handler log file. Based on the root cause of the error, customer can take action. In many cases, customers would have to modify the target table definition based on the source column data types and restart replicat. In other cases, customers may also want to modify the mapping in the replicat prm file. For this, Oracle recommends that they re-position replicat to start from the beginning.

• Any other SQL Errors:

  In case there are any errors while executing any SQL, the entire SQL statement along with the bind parameter values are logged into the OGGBD handler log file.

• Co-existence of the components:

  The location/region of the machine where replicat process is running, OCI Objects storage bucket region and the ADW region would impact the overall throughput of the apply process. Data flow is as follows:GoldenGate  OCI Object store  ADW. For best throughput, the components need to located as close as possible.

• Debugging row count mismatch on the target table

  For better throughput, ADW event handler does not validate the row counts modified on the target table. We can enable row count matching by using the Java System property: disable.row.count.validation. To enable row count validation, provide this property in the jvm.bootoptions as follows: jvm.bootoptions=-Xmx512m -Xms32m -Djava.class.path=.:ggjava/ggjava.jar:./dirprm -Ddisable.row.count.validation=false

• Replicat ABEND due to partial LOB records in the trail file:

  GG for DAA ADW apply does not support replication of partial LOB. The trail file needs to be regenerated by Oracle Integrated capture using TRANLOGOPTIONS FETCHPARTIALLOB option in the extract parameter file.

• Throughput gain with uncompressed UPDATE trails:

  If the source trail files contain the full image (all the column values of the respective table) of the row being updated, then you can include the JVM boot option -Dcompressed.update=false in the configuration property jvm.bootoptions.

  For certain workloads and ADW instance shapes, this configuration may provide a better throughput. You may need to test the throughput gain on your environment.

Classpath

ADW apply relies on the upstream File Writer handler and the OCI Event handler. Include the required jars needed to run the OCI Event handler in gg.classpath.

ADW Event handler uses the Oracle JDBC driver and its dependencies. The Autonomous Data Warehouse JDBC driver and other required dependencies are packaged with Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA).

For example: gg.classpath=./oci-java-sdk/lib/*:./oci-java-sdk/third-party/lib/*

Configuration

• Automatic Configuration Autonomous Data Warehouse (ADW) replication involves configuring of multiple components, such as file writer handler, OCI event handler and ADW event handler.

• File Writer Handler Configuration File writer handler name is pre-set to the value adw. The following is an example to edit a property of file writer handler: gg.handler.adw.pathMappingTemplate=./dirout

• OCI Event Handler Configuration OCI event handler name is pre-set to the value ‘oci’.

• ADW Event Handler Configuration ADW event handler name is pre-set to the value adw.

• INSERTALLRECORDS Support

• In-Memory Operation Aggregation

• End-to-End Configuration

• Compressed Update Handling

Automatic Configuration

Autonomous Data Warehouse (ADW) replication involves configuring of multiple components, such as file writer handler, OCI event handler and ADW event handler.

The Automatic Configuration functionality helps to auto configure these components so that the user configuration is minimal. The properties modified by auto configuration will also be logged in the handler log file.

To enable auto configuration to replicate to ADW target we need to set the parameter

gg.target=adw

gg.target
Required
Legal Value: adw
Default:  None
Explanation: Enables replication to ADW target

When replicating to ADW target, customization of OCI event hander name and ADW event handler name is not allowed.

File Writer Handler Configuration

File writer handler name is pre-set to the value adw. The following is an example to edit a property of file writer handler: gg.handler.adw.pathMappingTemplate=./dirout

OCI Event Handler Configuration

OCI event handler name is pre-set to the value ‘oci’.

The following is an example to edit a property of the OCI event handler: gg.eventhandler.oci.profile=DEFAULT

ADW Event Handler Configuration

ADW event handler name is pre-set to the value adw.

The following are the ADW event handler configurations:

Property	Required/Optional	Legal Values	Default	Explanationtes
gg.eventhandler.adw.connectionURL	Required	ADW	None	Sets the ADW JDBC connection URL. Example: jdbc:oracle:thin:@adw20190410ns_medium?TNS_ADMIN=/home/sanav/projects/adw/wallet
gg.eventhandler.adw.UserName	Required	JDBC User name	None	Sets the ADW database user name.
gg.eventhandler.adw.Password	Required	JDBC Password	None	Sets the ADW database password.
gg.eventhandler.adw.maxStatements	Optional	Integer value between 1 to 250.	The default value is 250.	Use this parameter to control the number of prepared SQL statements that can be used.
gg.eventhandler.adw.maxConnnections	Optional	Integer value.	10	Use this parameter to control the number of concurrent JDBC database connections to the target ADW database.
gg.eventhandler.adw.dropStagingTablesOnShutdown	Optional	true | false	false	If set to true, the temporary staging tables created by the ADW event handler is dropped on replicat graceful stop.
gg.eventhandler.adw.objectStoreCredential	Required	A database credential name.	None	ADW Database credential to access OCI object-store files.
gg.initialLoad	Optional	true | false	false	If set to true, initial load mode is enabled. See INSERTALLRECORDS Support.
gg.compressed.update	Optional	true or false	true	If set the true, then this indicates that the source trail files contain compressed update operations. If set to true, then the source trail files are expected to contain uncompressed update operations.
gg.eventhandler.adw.connectionRetries	Optional	Integer Value	3	Specifies the number of times connections to the target data warehouse will be retried.
gg.eventhandler.adw.connectionRetryIntervalSeconds	Optional	Integer Value	30	Specifies the delay in seconds between connection retry attempts.
gg.handler.adw.fileRollInterval	Optional	The default unit of measure is milliseconds. You can stipulate ms, s, m, h to signify milliseconds, seconds, minutes, or hours respectively. Examples of legal values include 10000, 10000 ms, 10s, 10m, or 1.5h. Values of 0 or less indicate that file rolling on time is turned off.	3m (three minutes)	The parameter determines how often the data will be merged into ADW. Use with caution, the higher this value is the more data will need to be stored in the memory of the Replicat process. <div class="infoboxnote" id="GUID-E39DFEF0-6E3E-4857-9F8A-8BAB39AB8F29__GUID-7CB41CF5-2336-4D75-9BE4-91283A7E5247"> <p class="notep1">Note:</p>Use the parameter with caution. Increasing its default value (3m) will increase the amount of data stored in the internal memory of the Replicat. This can cause out of memory errors and stop the Replicat if it runs out of memory. </div> <div class="infoboxnote" id="GUID-E39DFEF0-6E3E-4857-9F8A-8BAB39AB8F29__GUID-802357CB-DEB9-4E99-99BB-9D3222EC28DD"> <p class="notep1">Note:</p>Starting with the 23ai release, the gg.aggregate.operations.flush.interval property is deprecated and no longer supported. For more information, see In-Memory Operation Aggregation. </div>
gg.eventhandler.adw.deleteInsert	Optional	true or false	false	If set to true, Replicat will merge records using SQL DELETE+INSERT statements instead of SQL MERGE statement. <div class="infoboxnote" id="GUID-E39DFEF0-6E3E-4857-9F8A-8BAB39AB8F29__GUID-41852781-4E2E-4610-94BC-FB48705033AC"> <p class="notep1">Note:</p>Applicable only if gg.compressed.update is set to false. </div>
gg.validate.keyupdate	Optional	true or false	false	If set to true, Replicat will validate key update operations (optype 115) and correct to normal update if no key values have changed. Compressed key update operations do not qualify for merge.

INSERTALLRECORDS Support

Stage and merge targets supports INSERTALLRECORDS parameter.

See INSERTALLRECORDS in Reference for Oracle GoldenGate. Set the INSERTALLRECORDS parameter in the Replicat parameter file (.prm). Set the INSERTALLRECORDS parameter in the Replicat parameter file (.prm)

Setting this property directs the Replicat process to use bulk insert operations to load operation data into the target table.

You can tune the batch size of bulk inserts using the File writer property gg.handler.adw.maxFileSize. The default value is set to 1GB. The frequency of bulk inserts can be tuned using the File writer property gg.handler.adw.fileRollInterval, the default value is set to 3m (three minutes).

To process initial load trail files, set the INSERTALLRECORDS parameter in the Replicat parameter file (.prm). Setting this property directs the Replicat process to use bulk insert operations to load operation data into the target table.

You can tune the batch size of bulk inserts using the File Writer property gg.handler.adw.maxFileSize. The default value is set to 1GB. The frequency of bulk inserts can be tuned using the File Writer property gg.handler.adw.fileRollInterval, the default value is set to 3m (three minutes).

In-Memory Operation Aggregation

• Operation records are aggregated in-memory by default.

• The gg.aggregate.operations.flush.interval property has been deprecated and is no longer supported. If gg.aggregate.operations.flush.interval is used in GG for DAA 26ai, then replicat will run; but add a warning to log file about the property being deprecated and not supported.

  To control the time window for aggregation, use gg.handler.adw.fileRollInterval property. By default, it is set to 3 minutes. Longer intervals will increase latency, and may increase memory usage. Shorter intervals will increase overhead in Oracle GoldenGate and the target database.

• Operation aggregation in-memory requires additional JVM memory configuration.

End-to-End Configuration

The following is an end-end configuration example which uses auto configuration for FW handler, OCI and ADW Event handlers. The sample properties file is available at the following location:

• In an Oracle GoldenGate Classic install: <oggbd_install_dir>/AdapterExamples/big-data/adw-via-oci/adw.props.

• In an Oracle GoldenGate Microservices install: <oggbd_install_dir>/opt/AdapterExamples/big-data/adw-via-oci/adw.props.

# Configuration to load GoldenGate trail operation records
# into Autonomous Data Warehouse (ADW) by chaining
# File writer handler -> OCI Event handler -> ADW Event handler.
# Note: Recommended to only edit the configuration marked as TODO
gg.target=adw
## The OCI Event handler
# TODO: Edit the OCI config file path.
gg.eventhandler.oci.configFilePath=<path/to/oci/config>
# TODO: Edit the OCI profile name.
gg.eventhandler.oci.profile=DEFAULT
# TODO: Edit the OCI namespace.
gg.eventhandler.oci.namespace=<OCI namespace>
# TODO: Edit the OCI region.
gg.eventhandler.oci.region=<oci-region>
# TODO: Edit the OCI compartment identifier.
gg.eventhandler.oci.compartmentID=<OCI compartment id>
gg.eventhandler.oci.pathMappingTemplate=${fullyQualifiedTableName}
# TODO: Edit the OCI bucket name.
gg.eventhandler.oci.bucketMappingTemplate=<ogg-bucket>
## The ADW Event Handler
# TODO: Edit the ADW JDBC connectionURL
gg.eventhandler.adw.connectionURL=jdbc:oracle:thin:@adw20190410ns_medium?TNS_ADMIN=/path/to/ /adw/wallet
# TODO: Edit the ADW JDBC user
gg.eventhandler.adw.UserName=<db user>
# TODO: Edit the ADW JDBC password
gg.eventhandler.adw.Password=<db password>
# TODO: Edit the ADW Credential that can access the OCI Object Store.
gg.eventhandler.adw.objectStoreCredential=<ADW Object Store credential>
# TODO:Set the classpath to include OCI Java SDK.
gg.classpath=./oci-java-sdk/lib/*:./oci-java-sdk/third-party/lib/*
#TODO: Edit to provide sufficient memory (at least 8GB).
jvm.bootoptions=-Xmx8g -Xms8g

Compressed Update Handling

A compressed update record contains values for the key columns and the modified columns.

An uncompressed update record contains values for all the columns.

Oracle GoldenGate trails may contain compressed or uncompressed update records. The default extract configuration writes compressed updates to the trails.

The parameter gg.compressed.update can be set to true or false to indicate compressed/uncompressed update records.

• MERGE Statement with Uncompressed Updates

MERGE Statement with Uncompressed Updates

In some use cases, if the trail contains uncompressed update records, then the MERGE SQL statement can be optimized for better performance by setting gg.compressed.update=false.

Restrictions on Database Object Names

• Catalog, Schema, and Table Names: Cannot contain period (.), asterisk (*), question mark (?), double quote ("), backslash (`), or control characters (ASCII 0-31).

• Specific limitation for GGDAA 21c and previous releases: Supports only names that comply with the Avro specification for identifier names: must start with [A-Z a-z_], followed by `[A-Za-z0-9_].

• Additionally, all object names are also limited by the supported list of characters for the respective target.