Oracle as Sourcelink
Capturelink
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 Typeslink
HVR supports capture from the following table types in Oracle:
- Ordinary (heap-organized) tables
- Partitioned tables
- Index-organized tables
Capture Methodslink
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 Capturelink
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 thecreate 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-levelselect
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 Logginglink
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 identifier (table, column, primary key) names to 30 characters. Oracle database versions 12.2 and higher have a limit of 128 characters. To enable the supplemental logging for identifier names exceeding 30 characters, you must set the parameter ENABLE_GOLDENGATE_REPLICATION to true on the source Oracle database.
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;
- If the table does not have a primary key or unique index, or if action ColumnProperties is defined with parameter TimeKey:
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
Supplemental Log Data Subset Database Replicationlink
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 ROWIDlink
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 Logginglink
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 thealter 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 thesealter table
statements.
Accessing Redo and Archivelink
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 a time.
HVR supports capturing changes made by Oracle's Direct-Load INSERT feature (e.g. using
insert
statements with 'append hints' (insert /*+ append */ into
)). For HVR to capture these changes:
- A table/tablespace must not be in the NOLOGGING mode, because in this mode, data is inserted without redo logging.
- Archive log mode must be enabled.
Archiving of redo log files can be enabled by running the following statement as sysdba against a mounted but unopened database:
alter database archivelog
The current state of archiving can be checked using query:
select log_mode from v$database
The current archive destination can be checked with the query select destination, status from v$archive_dest
. By default, this query will return values USE_DB_RECOVERY_FILE_DEST, VALID, which means that HVR will read changes from within the flashback recovery area. Alternatively, an archive destination can be defined with the following statement: alter system set log_archive_dest_1='location=/disk1/arch'
and then restart the instance.
Often Oracle's RMAN will be configured to delete archive files after a certain time. But if they are deleted too quickly then HVR may fail if it falls behind or it is suspended for a time. This can be resolved by re-configuring RMAN so that archive files are guaranteed to be available for a specific longer period (e.g. 2 days).
If HVR is restarted it will need to go back to the start oldest transaction that was still open, so if the system has long running transactions then archive files will need to be retained for longer period.
Managing Archive/Redo Log fileslink
If log-based capture is defined for an Oracle database then HVR may need to go back to reading the Oracle archive/redo files. But each site has an existing backup/recovery regime (normal RMAN) that periodically deletes these Oracle archive files. To ensure that these archive files are available for HVR, configure RMAN so that the archive files are always available for sufficient time for the HVR capture job(s). The 'sufficient time' depends on the replication environment; how long could replication be interrupted for, and after what period of time would the system be reset using a Refresh.
Capturing from SAP Sourcelink
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.
The SAP Unpack license is required for adding the pool, cluster, and long text (STXL) tables to a channel using the Table Selection dialog.
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 Conversionlink
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 Zonelink
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 Oraclelink
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 Oraclelink
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 Locationlink
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.