Oracle as Source

Capture

Fivetran HVR allows you to capture changes from Oracle database. This section describes the configuration requirements for capturing changes from Oracle database. For the list of supported Oracle versions from which HVR can capture changes, see Capture changes from location in Capabilities.

By default, HVR performs log-based capture.

Table Types

HVR supports capture from the following table types in Oracle:

Ordinary (heap-organized) tables
Partitioned tables
Index-organized tables

Capture Methods

HVR allows the following methods for capturing (Capture) changes from Oracle:

Direct Redo Access
Capture changes directly from Oracle's redo and archive file.
Archive Only
Capture changes from Oracle's transaction log backup files.
LogMiner
Capture changes using Oracle LogMiner over an SQL connection.
BFile
Capture changes using Oracle BFile method.

Grants for Log-Based Capture

This section lists the grants required for capturing changes from Oracle database.

HVR can either connect to the database as the owner of the replicated tables, or it can connect as a special user (e.g. username).

The HVR database User must be granted the create session privilege:
```
grant create session to username;
```
To improve the performance of Activate Replication (hvractivate) for channels with a large number of tables (more than 150), HVR creates a temporary table (hvr_sys_table) within a schema.
The HVR database User must be granted the create table privilege so that HVR can automatically create this temporary table:
If you do not wish to provide create table privilege to the HVR database User, the alternative method is to manually create this temporary table using the SQL statement:
create global temporary table hvr_sys_table (table_name varchar(128), table_owner varchar(128));
This temporary table is not used when capturing from a Data Guard standby database.
The HVR database User must be granted the select any table privilege to replicate tables that are owned by other schemas (using the parameter Schema in action TableProperties). Else, the HVR database User must be granted the individual table-level select privileges.
```
grant select any table to username;
```

The HVR database User must be granted the select any dictionary privilege to read the data dictionaries in Oracle's SYS schema.

grant select any dictionary to username;

Alternatively, the HVR database User may be granted select privilege only for the required data dictionary objects:

Click here for the specific grants

grant select on sys.v_$archive_dest to username;

grant select on sys.v_$archived_log to username;

grant select on sys.v_$database to username;

grant select on sys.v_$database_incarnation to username;




/* The following grant (sys.v_$dnfs_files) is required for identifying the redo files located on DirectNFS */

grant select on sys.v_$dnfs_files to username;




/* The following grant (sys.v_$encryption_wallet) is required for decryption */

grant select on sys.v_$encryption_wallet to username;

grant select on sys.v_$log to username;

grant select on sys.v_$logfile to username;




/* The following grant (sys.v_$logmnr_contents) is required for Oracle LogMiner capture method. */

grant select on sys.v_$logmnr_contents to username;

grant select on sys.v_$nls_parameters to username;

grant select on sys.v_$parameter to username;

grant select on sys.v_$pdbs to username;




/* The following grant (sys.v_$system_parameter) is required for reading the value of 'filesystemio_options' parameter which in turn is used for reading the redo logs */

grant select on sys.v_$system_parameter to username;

grant select on sys.all_cons_columns to username;

grant select on sys.all_constraints to username;

grant select on sys.all_ind_columns to username;

grant select on sys.all_indexes to username;

grant select on sys.all_lobs to username;

grant select on sys.all_log_groups to username;

grant select on sys.all_objects to username;

grant select on sys.all_tab_cols to username;

grant select on sys.all_tables to username;

grant select on sys.all_triggers to username;

grant select on sys.all_encrypted_columns to username;

grant select on sys.dba_tablespaces to username;

grant select on sys.obj$ to username;




/* The following grant (sys.ecol$) is required for Oracle Database 11.2 and above since default values for added columns are stored differently. */

grant select on sys.ecol$ to username;




grant select on sys.user$ to username;

grant select on sys.col$ to username;

grant select on sys.enc$ to username;

grant select on sys.indpart$ to username;

grant select on sys.tabpart$ to username;

grant select on sys.tabsubpart$ to username;




/* The following three grants are required for Refreshing Data (hvrrefresh with option -qrw) and action AdaptDDL */

grant select on sys.v_$locked_object to username;

grant select on sys.v_$transaction to username;

grant select on sys.dba_objects to username;

The HVR database User must be granted the following privilege to capture create table statements and add supplemental logging to the newly created table(s):
```
grant alter any table to username;
```
The HVR database User must be granted the following privileges to use action DbSequence:
```
grant select any sequence to username;

grant select on sys.seq$ to username;
```

An alternative to all of the above grants is to provide the sysdba privilege to the HVR database User (e.g. username):
On Unix and Linux, add the user name used by HVR to the line in /etc/group for the Oracle sysadmin group.
On Windows, right-click My Computer and select Manage ▶ Local Users and Groups ▶ Groups ▶ ora_dba ▶ Add to Group ▶ Add.

Supplemental Logging

HVR needs the Oracle supplemental logging feature enabled on replicate tables that it replicates. Otherwise, when an update is done, Oracle will only log the columns which are changed. But HVR also needs other data (e.g. the key columns) so that it can generate a full update statement on the target database. The Oracle supplemental logging can be set at the database level and on specific tables. In certain cases, this requirement can be dropped by defining action ColumnProperties with parameter CaptureFromRowId, for more information, see section Capturing from Oracle ROWID.

Oracle database versions prior to 12.2 limit the names of identifiers (such as tables, columns, and primary keys) to 30 characters.
Oracle database versions 12.2 and higher allow up to 128 characters. To enable the supplemental logging for identifiers (such as tables, columns, and primary keys) whose names exceed 30 characters in versions 12.2 and higher, you must set the ENABLE_GOLDENGATE_REPLICATION parameter to true on the source Oracle database. Ensure that you have the necessary license to use this parameter.

The very first time that Activate Replication (hvractivate) runs, it will check if the database allows any supplemental logging at all. If it is not, then Activate Replication will attempt statement alter database add supplemental log data (see Extra Grants for Supplemental Logging to execute this statement). Note that this statement will hang if other users are changing tables. This is called 'minimal supplemental logging'; it does not actually cause extra logging; that only happens once supplemental logging is also enabled on a specific table.

Following are the SQL statements for enabling/disabling the supplemental logging manually:

To enable minimal database-level supplemental logging:
```
 alter database add supplemental log data;
```
To enable table-level supplemental logging -
- If the table does not have a primary key or unique index, or if action ColumnProperties is defined with parameter TimeKey:
```
 alter table tablename add supplemental log data (all) columns;
```
- If the table has a primary key:
```
 alter table tablename add supplemental log data (primary key) columns;
```
- If the table does not have a primary key but has a unique index:
```
 alter table tablename add supplemental log group groupname (column_1, column_2,...) always;
```
To check the status of supplemental logging at database-level:
```
 select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all from v$database;
```
This query should return at least ['YES', 'NO', 'NO']

To check the status of supplemental logging at table-level:

 select log_group_type from all_log_groups where table_name='tablename';

To disable supplemental logging at database-level:
```
 alter database drop supplemental log data;
```

To disable supplemental logging at table-level -

If the table does not have a primary key or unique index:

 alter table tablename drop supplemental log data (all) columns;

If the table has a primary key:

 alter table tablename drop supplemental log data (primary key) columns;

Activate Replication will normally only enable supplemental logging for the key columns of each replicated table, using statement alter table tab1 add supplemental log data (primary key) columns. But in some cases, Activate Replication will instead perform alter table tab1 add supplemental log data (all) columns, this will happen if either of the following condition is met:

the key defined in the replication channel differs from the Oracle table's primary key or
a table has any type of compression enabled or
no key is defined in the replication channel and parameter NoDuplicateRows of action TableProperties is not defined on the table or
one of the following actions with the mentioned parameters is defined on the table:
- On the capture location:
  Location Action Parameter
  Source ColumnProperties CaptureExpression
  Source Restrict CaptureCondition, HorizColumn
- On the target location:
  Location Action Parameter
  Target FileFormat BeforeUpdateColumns
- On any location:
  Location Action Parameter
  * CollisionDetect
  * ColumnProperties IntegrateExpression, Key or TimeKey
  * Integrate DbProc or Resilient
  * Integrate RenameExpression
  * Restrict AddressTo or IntegrateCondition

Location	Action	Parameter
Source	ColumnProperties	CaptureExpression
Source	Restrict	CaptureCondition, HorizColumn

Location	Action	Parameter
Target	FileFormat	BeforeUpdateColumns

Location	Action	Parameter
*	CollisionDetect
*	ColumnProperties	IntegrateExpression, Key or TimeKey
*	Integrate	DbProc or Resilient
*	Integrate	RenameExpression
*	Restrict	AddressTo or IntegrateCondition

Supplemental Log Data Subset Database Replication

HVR does not support the 'Supplemental Log Data Subset Database Replication' option on Oracle 19c and higher versions. This feature must be disabled for your Oracle database when using HVR for replication.

To verify whether the database is enabled for subset database replication ('YES' or 'NO'), use the following query:

select supplemental_log_data_sr from v$database;

To disable this option, use the following query:

alter database drop supplemental log data subset database replication;

Capturing from Oracle ROWID

If none of the above requirements force HVR to enable supplemental logging on all columns, the requirement for supplemental logging on key columns can be removed by defining action ColumnProperties with parameters CaptureFromRowId and SurrogateKey to the channel. When these actions are defined, HVR will consider the Oracle rowid column as part of the table and will use it as the key column during replication, and integrate it into the target database. The following two actions should be defined to the channel to instruct HVR to capture rowid values and to use them as surrogate replication keys. Note that these actions should be added before adding tables to the channel.

Location	Action	Parameter	Annotation
Source	ColumnProperties	Name=hvr_rowid, CaptureFromRowId	This action should be defined for capture location(s) only.
*	ColumnProperties	Name=hvr_rowid, SurrogateKey	This action should be defined for both capture and integrate locations.

Grants for Supplemental Logging

The HVR database User must be granted the privileges mentioned in section Grants for Log-Based Capture and additionally the following grants for using supplemental logging:

To execute alter database add supplemental log data, the HVR database User must have the sysdba privilege. Otherwise, HVR will write an error message which requests that a different user (who does have this privilege) execute this statement.
If HVR needs to replicate tables that are owned by other schemas, then optionally the HVR user can also be granted alter any table privilege, so that Activate Replication (hvractivate) can enable supplemental logging on each of the replicated tables. If this privilege is not granted then Activate Replication will not be able to execute the alter table…add supplemental log data statements itself; instead, it will write all the statements that it needs to execute into a file and then write an error message which requests that a different user (who does have this privilege) execute these alter table statements.

Accessing and Managing Redo and Archive Log Files

For HVR to capture changes from Oracle, the Oracle instance must have archiving enabled. If archiving is not enabled, HVR will lose changes if it falls behind the redo logs or it is suspended for some time.

To enable archiving, execute the following command as sysdba against a mounted but unopened database:

alter database archivelog;

The current state of archiving can be checked with:

select log_mode from v$database;

Direct-Load Insert Capture

HVR supports capturing changes from Oracle's Direct-Load INSERT feature (e.g. using insert statements with 'append hints' such as insert /*+ append */ into). For HVR to capture these changes:

A table/tablespace must not be in the NOLOGGING mode, as this mode bypasses redo logging.
Archive log mode must be enabled.

Archive Destination Management

The current archive destination can be verified with:

select destination, status from v$archive_dest;

By default, this returns values like USE_DB_RECOVERY_FILE_DEST, VALID, indicating that HVR will read changes from the Flashback Recovery Area.

Alternatively, a custom archive destination can be defined:

alter system set log_archive_dest_1='location=/disk1/arch';

After changing the archive destination, restart the instance.

Retaining Archive Files for HVR

To prevent HVR from failing due to missing archive files, it is important to configure RMAN (Recovery Manager) to retain these files for an adequate period. HVR may need to access the oldest transaction that was still open, especially in the case of long-running transactions, or if the system falls behind in replication. Ensure that the retention period is sufficient to accommodate your system's configuration, typically requiring archive files to be available for a longer duration (e.g., 2 days).

In log-based capture scenarios, HVR may need to revert to reading Oracle archive or redo files. If your backup/recovery regime (such as RMAN) deletes these files too quickly, reconfigure it to guarantee that archive files remain available for the necessary period. The retention duration depends on factors like the maximum time replication might be interrupted and the potential need for a Refresh.

Capturing from SAP Source

HVR allows you to capture changes from an Oracle database which is used by an SAP ECC system. To enable capture using SAP dictionary, the location property SAP Source (SAP_Source_Schema) must be defined while creating a location or by editing the existing location's source and target properties. Then while adding tables to a channel, the Table Selection dialog will display the SAP tables defined in the SAP dictionaries.

For Usage-based Subscription, an additional SAP Unpack license is required to unpack the cluster and pool tables from the SAP database. Contact Fivetran Technical Support to obtain the necessary SAP Unpack license. For the Consumption-based model, a separate license is NOT required.

When SAP pool, cluster, and long text (STXL) tables are added to a channel using the Table Selection dialog, the following actions are automatically defined:

TableProperties with parameters PackedInside, CoerceErrorPolicy, and CoerceErrorType
- For each container (pool/cluster) table a separate action TableProperties is defined.
- This action is not defined for long text (STXL) tables.
Transform with parameter SapUnpack
Irrespective of the number of tables, only a single action Transform is defined.

SAP columns are non-nullable by default. They will be described as nullable in the hub's repository and thus as nullable in the target. This is valid for the non-key columns. Key columns will remain non-nullable.

SAP Data Types Conversion

Since v6.1.0/7

This option enables conversion/mapping of SAP specific data types (available in SAP dictionary meta-data) in source location to corresponding data type in the target location. The SAP specific data type will be localized with the source DB's data type and then mapped to HVR's Repository data type. For example, if you have an SAP system on Oracle, the DATS data type will be localized as Oracle's Date type, and then it is mapped to HVR Repository type ansidate.

This feature is supported for the following SAP specific data types:

DATS

If the SAP Data Types Conversion option is NOT selected, SAP specific data types are mapped to various other HVR Repositry data types. For more information, see Data Type Mapping for SAP NetWeaver (or SAP dictionary).

If the SAP Data Types Conversion option is selected during location creation, HVR will automatically define action TableProperties with parameters CoerceErrorPolicy and CoerceErrorType.

If the SAP Source (SAP_Source_Schema) location property/option is selected during location creation, by default, the SAP Data Types Conversion option also gets selected.

However, to enable SAP data type conversion for an existing location, select the SAP Data Types Conversion option by editing the location's source and target properties and then manually define action TableProperties with parameters CoerceErrorPolicy and CoerceErrorType.

Time Zone

The time zone of the HVR Agent and the Oracle database must be the same. If they are not same, the following environment variable TZ should be defined to make them same:

Group	Table	Action	Parameter(s)
ORACLE	*	Environment	Name=TZ Value=time_zone

Compare and Refresh from Oracle

HVR allows you to perform only Compare and Refresh from Oracle database (without using Capture). This section describes the configuration requirements for performing only Compare and Refresh from Oracle database.

Grants for Compare and Refresh from Oracle

This section lists the grants required for performing only Compare and Refresh from Oracle database.

The HVR database User must be granted the create session privilege:
```
grant create session to username;
```
The HVR database User must be granted the select any table privilege, if Compare and Refresh needs to read from tables which are owned by other schemas (using the parameter Schema in action TableProperties):
```
grant select any table to username;
```
The HVR database User must be granted the following privileges to use the option Select Moment (option -M in CLI) available in Compare and Refresh:
```
grant select flashback any table to username;

grant select any transaction to username;
```

Best Practice for Upgrading Oracle Database on Source Location

When upgrading Oracle source database to a next release version, e.g. from 11g to 12c, the compatible mode can still be set to 11g.

The best practice when upgrading an Oracle source database to ensure no data is lost would be as follows:

Stop the application making changes to the database.
Ensure all the changes made by the application are captured: anticipate the latency to be at zero. For more information on monitoring the replication latency, refer to the Statistics page.
Suspend all capture and integrate jobs.
Upgrade the database.
Run Activate Replication with the following options selected: Jobs, Table Enrollment, and Capture Time and Transaction Files.
Restart all the jobs.
Start the application.