Capture From Oracle Using BFile

<b>Since</b> v6.1.0/2

In the BFile capture method, Data Source Solutions DSS reads Oracle's redo and archive log files through directory objects. DSS 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 DSS 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 DSS user. DSS 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 DSS user every day. To avoid this, DSS automatically creates a new directory object and drops the existing ones.

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

GRANT CREATE ANY DIRECTORY TO <em>username</em>;
GRANT DROP ANY DIRECTORY TO <em>username</em>;

Reduced Permissions for BFile Capture Method

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

Here is the body of the DSS_BFILE package:

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

CREATE OR REPLACE PACKAGE BODY DSS_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 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 DSS_BFILE;
/

CREATE OR REPLACE PUBLIC SYNONYM PKG_DSS_BFILE FOR SYS.DSS_BFILE;

Grant the necessary execution privilege to the DSS user:

GRANT EXECUTE ON PKG_DSS_BFILE TO <em>username</em>;

Amazon RDS for Oracle

1. To access online and archived redo log files, create Oracle directory objects using the RDS administration package. Run the following commands as a privileged user (such as rdsadmin). These will create onlinelog_dir and archivelog_dir directories used to reference physical redo and archive log files:

   BEGIN
     rdsadmin.rdsadmin_master_util.create_onlinelog_dir; 
     rdsadmin.rdsadmin_master_util.create_archivelog_dir;
   END;
   / 
   

2. Grant read access on the created directories to your DSS database user by running the following commands:

Replace username with your actual DSS database username, matching its exact case - use uppercase if the user was created without quotes, or preserve the exact case if created with double quotes.