Generic Oracle

Prerequisites

• Oracle database version is 11 or above. You can retrieve the version of your Oracle database with the following command:

  SELECT BANNER_FULL FROM V$VERSION WHERE BANNER_FULL LIKE 'Oracle Database%';
  

• Redo Log-based replication is enabled for ingestion mode as Redo Log, along with SYSDBA privileges for the database user.

• SELECT permissions are granted to the database user.

• A valid service name is available for the Oracle database.

• You are assigned the Team Administrator, Team Collaborator, or Pipeline Administrator role in Hevo, to create the Pipeline.

Perform the following steps to configure your Generic Oracle Source:

Create a Database User and Grant Privileges

Connect to your Oracle server as a database adminstrator (DBA) using SQL Developer or any other SQL client tool and run the following script. This script creates a new database user in your Oracle database with access to the metadata tables and LogMiner. Keeping your privacy in mind, it grants only the necessary permissions required by Hevo to ingest data from your Oracle database.

-- Create a Database User
CREATE USER <username> IDENTIFIED BY <password>; 

-- Grant Privileges to the Database User
GRANT SELECT ANY DICTIONARY to <username>;
GRANT CREATE SESSION, ALTER SESSION TO <username>;
GRANT SELECT ON ALL_VIEWS TO <username>;
GRANT SELECT ON <schema_name>.<table_name> TO <username>;

-- Grant Privileges on Metadata tables
GRANT SELECT ON DATABASE_PROPERTIES TO <username>;
GRANT SELECT ON ALL_OBJECTS TO <username>;
GRANT SELECT ON ALL_TABLES TO <username>; 
GRANT SELECT ON ALL_TAB_COLUMNS TO <username>; 
GRANT SELECT ON ALL_CONSTRAINTS TO <username>; 
GRANT SELECT ON ALL_CON_COLUMNS TO <username>;

-- Grant Permission to run LogMiner
GRANT LOGMINING TO <username>
GRANT SELECT ON SYS.V_$DATABASE TO <username>;
GRANT SELECT ON SYS.V_$LOG TO <username>; 
GRANT SELECT ON SYS.V_$LOGFILE TO <username>;  
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO <username>; 
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO <username>; 
GRANT SELECT ON SYS.V_$ARCHIVE_DEST_STATUS TO <username>; 
GRANT EXECUTE ON SYS.DBMS_LOGMNR TO <username>; 
GRANT EXECUTE ON SYS.DBMS_LOGMNR_D TO <username>;

Note: Replace the placeholder values in the commands above with your own. For example, <username> with hevo.

Refer to the table below for more information about these commands:

Set up Redo Logs for Replication

Connect to your Oracle server as a user with SYSDBA privileges using SQL Developer or any other SQL client tool and perform the following steps:

1. Enable Archive Log

Archive logs are essential for backup and recovery processes of a database management system.

Enter the command below to verify the current archive log mode:

SELECT LOG_MODE FROM V$DATABASE;

This query returns either of the following values:

• ARCHIVELOG: Indicates that the archive log is enabled.

• NOARCHIVELOG: Indicates that the archive redo is disabled.

If the archive mode is NOARCHIVELOG, enable it using the following commands:

1. Enter the command below to initiate the shutdown process without waiting for active sessions or transactions to complete. It forcefully terminates existing connections.

   SHUTDOWN IMMEDIATE;
   

2. Enter the command below to start the Oracle database instance in a mounted state:

   STARTUP MOUNT;
   

3. Enter the command below to alter the database and enable archive log mode. This command instructs the database to start archiving the redo log files:

   ALTER DATABASE ARCHIVELOG;
   

4. Enter the command below to open the database for operations after enabling ARCHIVELOG mode:

   ALTER DATABASE OPEN;
   

2. Configure the Retention Policy

The Recovery Manager (RMAN) retention policy determines how long the database holds backlogs and archive logs.

1. Run the following command to connect to the RMAN:

   RMAN 
   CONNECT TARGET <database_username> -- (to connect to your database)
   

   Note: Replace the placeholder values in the commands above with your own. For example, <database_username> with jacobs.

2. Run the following command to view the log retention policy:

   SELECT VALUE FROM V$RMAN_CONFIGURATION WHERE NAME = 'RETENTION POLICY';
   

3. If the log retention policy is less than 3 days, run the following command to configure it to 3 days (72 hours):

   CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
   

   Note: We recommend setting the archive log retention policy to at least 3 days (72 hours). This avoids any data loss that may occur due to downtimes in the Source database.

3. Enable Supplemental Logging

Supplemental logging ensures that any changes in columns are logged in Redo log files, which is essential for LogMiner to access the activity history of a database.

1. Check if supplemental logging is enabled:

   SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM "V$DATABASE";
   

   This query returns either of the following values:

   • YES: Indicates that supplemental logging is enabled.

   • NO: Indicates that supplemental logging is disabled.

2. If the result of query above is NO, enable supplemental logging at the database level with the following command:

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
   

   1. Enter the following command to retrieve the status of table-level supplemental logging:

      SELECT COUNT(*) FROM ALL_LOG_GROUPS
          WHERE LOG_GROUP_TYPE='ALL COLUMN LOGGING'
          AND OWNER= '<group_name>'
          AND TABLE_NAME='<table_name>';
      

      Note: Replace the placeholder values in the commands above with your own. For example, <group_name> with jacobs.

      This returns one of the following values:

      • <number>: The number of log groups for which supplemental logging is enabled.

      • zero: This represents that the supplemental logging is disabled.

   2. If the result of the above query is zero, enable supplemental logging for all columns of a specific table present in your Source database:

      ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
      

4. Check PGA/SGA Memory Settings (Recommended)

In Oracle, the PGA memory settings allow you to manage and optimize memory usage for individual user processes involved in SQL execution.

1. Enter the command below to retrieve information about the initialization parameters ‘pga_aggregate_limit’ and ‘pga_aggregate_target’ from the V$PARAMETER view:

   SELECT NAME, VALUE/1024/1024 as VALUE_MB 
       FROM V$PARAMETER 
       WHERE NAME IN ('pga_aggregate_limit', 'pga_aggregate_target');
   

   The following is an explanation of the command above:

   • SELECT NAME, VALUE/1024/1024 as VALUE_MB: This part retrieves data for the columns NAME and VALUE from the dynamic performance view V$PARAMETER. It retrieves the NAME column as is and calculates the VALUE column divided by 1024 twice to convert the value from bytes to megabytes. It aliases the result as VALUE_MB.

   • WHERE NAME IN ('pga_aggregate_limit', 'pga_aggregate_target'): This part filters the results to include only the rows where the NAME column is either pga_aggregate_limit or pga_aggregate_target.

2. Enter the command below to monitor the current PGA memory usage in your Oracle database:

   SELECT NAME, VALUE, UNIT 
       FROM V$PGASTAT 
       WHERE NAME IN ('total PGA inuse','total PGA allocated');
   

   The following is an explanation of the command above:

   • SELECT NAME, VALUE, UNIT: This part retrieves data for the columns NAME, VALUE, and UNIT from the dynamic performance view V$PGASTAT. It retrieves the PGA statistic name, its value, and the unit for the value.

   • WHERE NAME IN ('total PGA inuse', 'total PGA allocated'): This part filters the results to include only the rows where the NAME column is either total PGA inuse or total PGA allocated.

Oracle Buffers

In Oracle, buffers refer to memory storage for caching data to enhance database performance. When LogMiner reads data from redo log files, it utilizes the native in-memory Oracle buffer to cache ongoing transactions (ones that have not been committed or rolled back).

Data Manipulation Language (DML) operations within a transaction are buffered until a commit or rollback is detected. A long-running transaction can have a negative impact on the database performance. It can lead to increased lag in processing change events and memory usage on the database server.

This accumulation of data in the Oracle LogMiner buffers can lead to increased Program Global Area (PGA) memory consumption in your database. Therefore, it is essential to appropriately set the PGA memory size based on your database workload. Read Check PGA/SGA Memory Settings to review the memory settings and configure the PGA_AGGREGATE_LIMIT to prevent server OutOfMemory (OOM) errors.

Additionally, you can modify the Long Transaction Window while configuring your Source in Hevo based on the available PGA memory in your database. Any transactions exceeding this window are discarded entirely, which may lead to a data mismatch. Further, setting an excessively large value may cause OOM issues. Only your DBA can determine if your database has long-running transactions with a large number of changes. If so, set the PGA_AGGREGATE_LIMIT to an appropriate value to process these transactions.

5. Configure the PGA Aggregate Limit (Recommended)

It is important to set up the PGA aggregate limit for managing and controlling memory usage in individual user sessions and queries. Depending on your database workload, you can set the pga_aggregate_limit parameter to prevent out-of-memory errors. To do this, enter the following command:

ALTER SYSTEM SET pga_aggregate_limit = <new value> SCOPE=BOTH;

Note:

• You can set the above value in one of the following units of measurement:

  • K represents kilobytes.

  • M represents megabytes.

  • G represents gigabytes.

• Replace the placeholder value in the command above with your own. For example, <new value> with 1G.

Retrieve the Service Name

Service name represents an alias of the unique Oracle database to which Hevo connects.

To retrieve the Service Name, open your Oracle server in any SQL client tool as a database user with SYSDBA privilege and enter the following command:

select name from v$database;