Capture From Oracle Using BFile

Since v6.1.0/2

In the BFile capture method, Fivetran HVR reads Oracle's redo and archive log files through directory objects. HVR first tries to access the redo or archive log files through the existing directory objects or creates a new directory object if it does not exist.

This method is comparable in speed with the DIRECT and LOGMINER capture methods.

The BFile method does not require the HVR Agent to be installed on the source database machine.

Accessing Predefined Directories

Directories for online redo and archive files are predefined. A DBA must create directory objects and grant read access to the HVR user. HVR will automatically find and use the created directory objects.

Permissions for Accessing the Predefined Directories

• Oracle minimal permission on directory objects: READ
• OS minimal permission for Oracle user: READ

Log Directories Mapping

The LOG DIRECTORIES MAPPING fields in the user interface (equivalent to location property Oracle_BFile_Dirs_Mapping) should only be filled in if the file paths of the online logs or archive directories include symbolic links. This is necessary because Oracle does not allow access through the BFile interface to a directory that has symbolic links in its path.

For example, there is a log directory /var/foo/xxx, where /var/foo/ is a symbolic link to path /yyy/zzz. Thus, the real path to the wallet directory is /yyy/zzz/xxx. In this case, /var/foo should be specified in the Symbolic link field and /yyy/zzz should be specified in the Target field.

Fast Recovery Area Access

The Oracle Fast Recovery Area is a set of directories for storing files related to the recovery process. It includes the directory for saving archive files. However, since Oracle creates a new sub-directory for archives every day, it would be necessary to create a new directory object and grant read access to the HVR user every day. To avoid this, HVR automatically creates a new directory object and drops the existing ones.

For HVR to automatically rotate directory objects, the following grants must be provided to the HVR user:

grant create any directory to username;

grant drop any directory to username;

Reduced Permissions for BFile Capture Method

In cases where the above permissions are deemed excessive, you can reduce them by using a custom package HVR_BFILE, specifically designed to operate with owner privileges. The DBA is required to run the package script and grant execution to the HVR user.

Here is the body of the HVR_BFILE package:

CREATE OR REPLACE PACKAGE HVR_BFILE AUTHID DEFINER AS
    PROCEDURE dir_create(dirobj_name VARCHAR2, directory_name VARCHAR2);
    PROCEDURE dir_remove(dirobj_name VARCHAR2);
END HVR_BFILE;
/

CREATE OR REPLACE PACKAGE BODY HVR_BFILE AS
PROCEDURE dir_create(dirobj_name VARCHAR2, directory_name VARCHAR2) IS
    user_id VARCHAR2(256);
    BEGIN
      execute immediate 'CREATE OR REPLACE DIRECTORY '  dir_create.dirobj_name  ' AS '''  dir_create.directory_name  '''';
      user_id := USER;
-- If you are not on the ASM system, you can reduce the permission to 'GRANT READ on DIRECTORY'.
      execute immediate 'GRANT READ, WRITE on DIRECTORY '  dir_create.dirobj_name  ' to ' user_id;
    END;
PROCEDURE dir_remove(dirobj_name VARCHAR2) IS
    BEGIN
      execute immediate 'DROP DIRECTORY'  dir_remove.dirobj_name;
    END;
END HVR_BFILE;
/

CREATE OR REPLACE PUBLIC SYNONYM PKG_HVR_BFILE FOR SYS.HVR_BFILE;

Grant the necessary execution privilege to the HVR user:

grant execute on PKG_HVR_BFILE to username;

Amazon RDS for Oracle

To access your online and archived redo log files, create directory objects that provide read-only access to the physical file paths.

rdsadmin.rdsadmin_master_util.create_archivelog_dir

rdsadmin.rdsadmin_master_util.create_onlinelog_dir

To capture from Amazon RDS for Oracle, provide the following grants to the HVR user:

grant read on directory onlinelog_dir to username;

grant read on directory archivelog_dir to username;

To enable supplemental logging on Amazon RDS for Oracle, perform the following steps:

grant select_catalog_role to username with admin option;

grant execute_catalog_role to username with admin option;

begin
rdsadmin.rdsadmin_util.grant_sys_object(
p_obj_name => 'V_$SESSION',
p_grantee => 'username',
p_privilege => 'SELECT');
end

begin
rdsadmin.rdsadmin_util.alter_supplemental_logging(
p_action => 'ADD');
end;
/

alter table BRANDS add supplemental log data (primary key) columns;

alter table CATEGORIES add supplemental log data (primary key) columns

Capturing Archive Logs on Oracle with ASM Storage

HVR supports capture from Oracle database using ASM to store archive files remotely through Bfile. The files are copied to the local file system on the database server, read, and then deleted. To copy and delete the files, the following grants are required:

grant execute on sys.dbms_file_transfer to username;

grant execute on sys.utl_file to username;

If you used granular dictionary privileges instead of the grant select any dictionary privilege, then the following additional privileges are also required:

grant select on sys.v_$session_longops to username;

grant select on sys.v_$mystat to username;

To capture from Oracle with ASM storage using BFile capture method, perform the following steps:

BFILE_PATH

• A DBA must create or provide an existent directory on a file system and grant read and write access to the HVR database user. On a RAC system, this location can be local to the node, irrespective of whether connectivity is directly to the node or through the scan listener. The path to the local directory on a RAC system must be identical on all nodes.

ASM_DST_ALIAS

• A DBA can create a directory object for the BFILE_PATH directory and grant read and write access to the HVR user. HVR will automatically create a directory object if one has not been provided. In this case, the CREATE ANY DIRECTORY privilege must also be granted. This alias must be unique for each HVR channel, for each database instance. In the case of a single channel, this step can be omitted and HVR will use the default generated directory name.

ASM_SRC_ALIAS

• In order to access the archived log files directly in the respective daily folders, the HVR database user must have been granted the CREATE AND DROP ANY DIRECTORY privilege. The directory alias must be unique for each HVR channel, for each database instance.

Action Definition

The following actions must be defined:

Capturing Online Logs on Oracle with ASM Storage

Online logs are not currently supported directly by BFile capture method. Capturing from online logs requires DIRECT method with configured access to an underlying ASM system, local on the database server or remote over TNS. Also it is possible to configure a short interval of log switching on the database side to limit capture latency.

BFile Capture Method Limitations

• Archived Log Only mode is not supported when using the BFile capture method (except implicitly when capturing from an Oracle Database using ASM storage)