Capture From Oracle Using Archive Only

In this capture method (Capture_Method=ARCHIVE_ONLY), Fivetran HVR reads changes from redo archive files available in the directory specified in ARCHIVE WATCH DIRECTORY (Archive_Log_Path). Also, HVR can be configured to consider only the files that match the format defined in LOG ARCHIVE FORMAT (Archive_Log_Format).

HVR does not read anything from online redo files or the 'primary' archive destination. This allows the HVR process to reside on a different machine than the Oracle DBMS and read changes from files that are sent to it by some remote file copy mechanism (e.g. FTP). The capture job still needs an SQL connection to the database for accessing dictionary tables, but this can be a regular connection.

Replication in this mode can have longer delays in comparison with the 'online' mode. To control the delays, it is possible to force Oracle to issue an archive once per predefined period of time.

HVR supports cross-platform capture of archived redo files. For example, on a Linux machine where Capture_Method=ARCHIVE_ONLY is defined, HVR can capture archived redo files coming from an AIX machine.

On RAC systems, delays are defined by the slowest or the less busy node. This is because archives from all threads have to be merged by SCNs in order to generate replicated data flow.

To avoid high-latency issues, schedule a log switch of the active threads on the source system by running the following command:

alter system switch logfile;

Archive Watch Directory

HVR will search for the log archives in the directory specified in this field.

Any process could be copying log archive files to this directory; the Oracle archiver (if another LOG_ARCHIVE_DEST_N is defined), RMAN, or a simple shell script. So, it should be ensured that the files in this directory are purged periodically, otherwise the directory will fill up.

Log Archive Format

If the names of the compressed archive log files differ from the original names of the archive log files, you may describe the filename format (template) of the transaction log archive files stored in the ARCHIVE WATCH DIRECTORY.

This field accepts the following format variables:

• %d - match numbers (zero or more decimal digits). Numbers matched using this variable are ignored by HVR.
• %r or %R - resetlogs ID
• %s or %S - log sequence number
• %t or %T - thread number
• %z or %Z - match alphanumeric characters. Characters matched using this variable are ignored by HVR.

Wildcard character * is not supported.

For more information about the format variables, refer to the article LOG_ARCHIVE_FORMAT in Oracle documentation.

If LOG ARCHIVE FORMAT is not defined, then by default HVR will query the database for Oracle's initialization parameter LOG_ARCHIVE_FORMAT.

The following are used by HVR if LOG ARCHIVE FORMAT field is not defined,

• For Oracle ASM system, the default name pattern used is thread_%t_seq_%s.%d.%d.

• Non-ASM system,

  • if Fast-Recovery-Area (FRA) is used, then the default name pattern used is o1_mf_%t_%s_%z_.arc

  • if FRA is not used, then HVR uses the following SQL query:

    SELECT value
    FROM v$parameter
    WHERE name = 'log_archive_format';
    

    HVR picks up the first valid archive destination and then finds the format as described above.

To determine whether the destination uses FRA, HVR uses the following query:

SELECT destination
FROM v$archive_dest
WHERE dest_name='LOG_ARCHIVE_DEST_[n]';

For example, for destination 1, the query is as follows:

SELECT destination
FROM v$archive_dest
WHERE dest_name='LOG_ARCHIVE_DEST_1';

If the query returns USE_DB_RECOVERY_FILE_DEST, it indicates the destination uses FRA.

Capturing from Compressed Archive Log Files

HVR supports capturing changes from compressed archive log files that are moved from a 'primary' archive log directory to a custom directory. HVR automatically detects the compressed files, decompresses them, and reads data from them.

This feature is activated when the directory defined in ARCHIVE WATCH DIRECTORY is set to the custom directory.

Only gzip compressed files are supported.

Compressed Archive Log File Format

If the names of the compressed archive log files differ from the original names of the archive log files, then the relevant naming format should be defined in LOG ARCHIVE FORMAT field.

Examples

Example 1:

Suppose an archive log file is named o1_mf_1_41744_234324343.arc according to a certain Oracle archive log format pattern o1_mf_<thread>_<sequence>_<some_number>.arc. The archive file is copied to some custom directory and compressed to o1_mf_1_41744_234324343.arc.gz with the .gz extension added to its name. In such a case, LOG ARCHIVE FORMAT should be defined with the following pattern o1_mf_%t_%s_%z.arc.gz.

Example 2:

Suppose the compressed archive log file is named CQ1arch1_142657_1019376160.dbf.Z with the .Z extension added to its name. In such a case, LOG ARCHIVE FORMAT should be defined with the following pattern CQ1arch%t_%s_%r.dbf.Z.