Generic Oracle (Edge)

Prerequisites

• Oracle database version is 12c 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%';
  

• SELECT permissions are granted to the database user.

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

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

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 administrator (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 CREATE SESSION TO <username>;
GRANT SELECT ON V_$DATABASE TO <username>;
GRANT FLASHBACK ANY TABLE TO <username>;
GRANT SELECT ANY TABLE TO <username>;
GRANT SELECT ANY TRANSACTION TO <username>;

# Grant Roles to the Database User
GRANT SELECT_CATALOG_ROLE TO <username>;
GRANT EXECUTE_CATALOG_ROLE TO <username>;

# Grant Permission to run LogMiner
GRANT LOGMINING TO <username>;
GRANT EXECUTE ON DBMS_LOGMNR TO <username>;
GRANT EXECUTE ON DBMS_LOGMNR_D TO <username>;
GRANT SELECT ON V_$LOG TO <username>;
GRANT SELECT ON V_$LOG_HISTORY TO <username>;
GRANT SELECT ON V_$LOGMNR_LOGS TO <username>;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO <username>;
GRANT SELECT ON V_$LOGMNR_PARAMETERS TO <username>;
GRANT SELECT ON V_$LOGFILE TO <username>;
GRANT SELECT ON V_$ARCHIVED_LOG TO <username>;
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO <username>;
GRANT SELECT ON V_$TRANSACTION TO <username>;
GRANT SELECT ON V_$MYSTAT TO <username>;
GRANT SELECT ON V_$STATNAME 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

A redo log is a collection of log files that record information about modifications made to data objects on an Oracle server instance. Oracle LogMiner uses redo logs to track these modifications and determine the rows requiring updates in the Destination system.

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 Fast Recovery Area

The fast recovery area in Oracle serves as a centralized storage space for all database recovery-related files. Consolidating these files into a single location simplifies backup and recovery management.

Set appropriate values for the following parameters:

• DB_RECOVERY_FILE_DEST_SIZE: Sets the size of the fast recovery area.

• DB_RECOVERY_FILE_DEST: Sets the location of the fast recovery area. This area contains the redo logs.

• ARCHIVE_LAG_TARGET: Sets a non-zero target value in seconds for the maximum acceptable delay between log switches.

Configure these parameters using the following commands:

1. Enter the command below to specify the size of the fast recovery area:

   ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = <size>;
   

2. Enter the command below to specify the location of the fast recovery area:

   ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '<path>';
   

3. Enter the command below to specify a non-zero value in seconds to force a log switch:

   ALTER SYSTEM SET ARCHIVE_LAG_TARGET = <value>;
   

Note: Replace the placeholder values in the commands above with your own. For example, <size> with 10G.

3. Configure the Retention Period

The Recovery Manager (RMAN) settings determine how long the database holds backlogs and archive logs.

1. Enter the command below to connect to the RMAN:

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

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

2. Configure the log retention policy to 3 days (72 hours):

   CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
   

   Note: The minimum value for archive log retention hours is 3 days (72 hours). This avoids any data loss that may occur due to downtimes in the Source database.

4. 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 one of the following values:

   • YES: Indicates that supplemental logging is enabled.

   • IMPLICIT: Indicates that supplemental logging is automatically enabled by the database based on certain conditions or configurations.

   • NO: Indicates that supplemental logging is disabled.

2. If the result of the above query is NO, enable supplemental logging with one of the following commands:

   • Enable supplemental logging at the database level for all columns:

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
   

   Note: Enabling supplemental logging at the database level will increase redo log data.

   • Enable minimal supplemental logging at the database level:

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
   

   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 table in your Source database which you want to replicate:

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

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.

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

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

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

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

  • K represents kilobytes.

  • M represents megabytes.

  • G represents gigabytes.

Retrieve the Database Name

The database name represents the unique Oracle database to which Hevo connects for replicating data. To retrieve it, connect to your Oracle server in any SQL client tool as a masteruser and enter the following command:

SELECT NAME FROM V$DATABASE;