Documentation

Setting up LogMiner for Oracle

To replicate your Oracle tables via LogMiner, you'll need to prepare your database by performing the following steps.

Note

BLOB and CLOB data types are limited to a maximum size of 4k.

Note

Alooma does not support LOB data types (BLOB, CLOB, NCLOB) on Oracle 12c when using log replication / LogMiner.

  1. Your database must be running in ARCHIVELOG mode (learn more about this mode).

    To check the current database mode, run:

    Copy
    SELECT LOG_MODE FROM V$DATABASE;
    • If the result is ARCHIVELOG - success! Move on to step 2.

    • If the result is NOARCHIVELOG, you'll need to enable it by running:

      For self-hosted deployments:

      Copy
      SHUTDOWN IMMEDIATE;
      STARTUP MOUNT;
      ALTER DATABASE;
      ALTER DATABASE ARCHIVELOG;
      ALTER DATABASE OPEN;

      Note that the above sequence will restart your instance since that's required for the changes to take effect.

      For RDS-hosted deployments:

      Copy
      exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',96);
  2. Supplemental log data must be enabled for the database (learn more about supplemental logging).

    • To turn this logging on, for self-hosted deployments, run:

      Copy
      ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (all) COLUMNS;

      You can also enable supplemental logging on Primary Key columns only, but note that this may cause extra load on the database.

    • For RDS-hosted deployments, at the SQL prompt, run:

      Copy
      exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','ALL');
      SELECT supplemental_log_data_all FROM v$database;

      The results should be, respectively:

      PL/SQL procedure successfully completed.

      then:

      SUP --- YES

  3. The user you will be using to connect to Oracle must have sufficient privileges granted.

    To grant the privileges, run the following command (replacing <user> with the correct username):

    Copy
    GRANT SELECT ANY DICTIONARY TO <user>;
    GRANT EXECUTE_CATALOG_ROLE TO <user>;
    GRANT SELECT ANY TRANSACTION TO <user>;
    GRANT EXECUTE ON SYS.DBMS_LOGMNR TO <user>;
    GRANT SELECT ON SYS.V_$DATABASE TO <user>;
    GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO <user>;
    GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO <user>;
    GRANT EXECUTE ON DBMS_LOGMNR TO <user>;
    GRANT EXECUTE ON DBMS_LOGMNR_D TO <user>;
    GRANT CONNECT TO <user>;
    GRANT CREATE SESSION TO <user>;
    GRANT SELECT ANY TABLE TO <user>;
    GRANT UNLIMITED TABLESPACE TO <user>;
    ALTER USER <user> QUOTA UNLIMITED ON DEVELOPMENT;
    GRANT CREATE JOB TO <user>;
    GRANT CREATE MINING MODEL TO <user>;
    GRANT CREATE PROCEDURE TO <user>;
    GRANT CREATE SEQUENCE TO <user>;
    GRANT CREATE SESSION TO <user>;
    GRANT CREATE SYNONYM TO <user>;
    GRANT CREATE TABLE TO <user>;
    GRANT CREATE TYPE TO <user>;
    GRANT CREATE VIEW TO <user>;
    GRANT SELECT ANY TABLE TO <user>;
    GRANT EXECUTE ON SYS.DBMS_LOGMNR_D TO <user>;
    GRANT EXECUTE ON SYS.DBMS_LOGMNR_INTERNAL TO <user>;
    GRANT EXECUTE ON SYS.DBMS_LOGMNR_LOGREP_DICT TO <user>;
    GRANT EXECUTE ON SYS.DBMS_LOGMNR_SESSION TO <user>;
    GRANT CREATE ANY TABLE TO <user>;
    GRANT FLASHBACK ANY TABLE TO <user>;
    GRANT FLASHBACK ARCHIVE ADMINISTER TO <user>;
    GRANT SELECT ON V_$LOGMNR_CONTENTS TO <user>;
    GRANT EXECUTE ON DBMS_FLASHBACK TO <user>;
    GRANT LOGMINING TO <user>;
    GRANT SELECT ON V_$LOGMNR_CONTENTS TO <user>;
    GRANT SELECT ON V_$LOG TO <user>;
    GRANT SELECT ON V_$LOGFILE TO <user>;
    GRANT SELECT ON V_$ARCHIVED_LOG TO <user>;
    GRANT SELECT ON V_$LOGMNR_LOGS TO <user>;
    GRANT SELECT ON DBA_REGISTRY TO <user>;
    GRANT SELECT ON V_$PARAMETER TO <user>;
    GRANT SELECT ON V_$DATABASE TO <user>;
    GRANT SELECT ON V_$LOGMNR_DICTIONARY TO <user>;
    GRANT SELECT ON V_$LOGMNR_PARAMETERS TO <user>;
    GRANT SELECT ON V_$LOGMNR_LOGS TO <user>;
    

    This will grant privileges to read from v$database (which is required for validation), will start the log miner, and will grant privileges to select from v$logmnr_content.

    For Oracle 12, grant the following additional permission:

    Copy
    GRANT LOGMINING TO <user>;
  4. Configure RMAN backups. Alooma recommends you retain backups and archive logs for as long as possible, with a minimum of 4 days (7 days recommended). See Oracle's documentation for more information.

    Copy
    RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;

    Be mindful that archived log files do consume disk space, so you'll want to configure DB_RECOVERY_FILE_DEST_SIZE to help manage the trade off between information availability and disk space.

  5. Finally, run the following command to ensure all supplemental logging data is available after sufficient privileges have been granted:

    Copy
    SELECT SUPPLEMENTAL_LOG_DATA_ALL, SUPPLEMENTAL_LOG_DATA_PK FROM V$DATABASE;

Your Oracle database should now be good to go, Continue to connecting to Oracle.

Search results

    No results found