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.

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

• 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 Big Data Autonomous Data Warehouse (ADW) apply doesn't support replication of partial LOB columns in the trail file.

• 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 Big Data 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:

  Oracle GoldenGate for Big Data 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.

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 Big Data.

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

• 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.
• End-to-End Configuration