Generic Oracle

Oracle database is a cross-platform Relational Database Management System (RDBMS) that can run on various hardware across operating systems including Windows Server, Unix, and various distributions of GNU/Linux. It is commonly referred to as Oracle database, OracleDB, or simply Oracle. The database software endorses transaction processing, business intelligence and different analytics applications, and used by both small and large enterprises to store and manage data.

Prerequisites

• Oracle database version is 11 or above.

• Redo Log-based replication is enabled, for Pipeline mode as Redo Log, along with SYSDBA privileges for the database user.
• SELECT permissions are granted to the database user.

Setting up Redo Logs for Replication

Note: You must have SYSDBA privileges to perform the following steps for setting up Redo logs.

Connect to your Oracle server in SQL Developer or any SQL client tool and run the following commands:

Step 1. Enable ARCHIVELOG archival mode

1. Verify that the current archiving mode is ARCHIVELOG:

   SELECT LOG_MODE FROM V$DATABASE

   If the archive mode is NOARCHIVELOG, enable it using the following commands:

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

Step 2. Configure Recovery Manager

Recovery Manager (RMAN) settings determine how long the database holds backlogs and archive logs.

1. Connect to the RMAN by executing the following commands on the command prompt:

   RMAN

   CONNECT TARGET <username> (to connect to your database)

   SHOW ALL; (to check the default settings)

2. Configure the log retention policy to 7 days:

   CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;`

Step 3. Enable supplemental logging

Supplemental logging ensures that columns are logged in Redo log files which are required by LogMiner to read the activity history for a database.

• Enable supplemental logging at a database level:

  ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

• Enable supplemental logging at a table level:

  ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Step 4. Grant permissions to run LogMiner

LogMiner is used to read the archived Redo logs.

Run the following commands to grant the required permissions to it:

 GRANT SELECT ON SYS.V_$DATABASE TO <username>;
 GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO <username>;
 GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO <username>;
 GRANT EXECUTE ON DBMS_LOGMNR TO <username>;
 GRANT EXECUTE ON DBMS_LOGMNR_D TO <username>;
 GRANT SELECT ANY TRANSACTION TO <username>;
 GRANT EXECUTE_CATALOG_ROLE TO <username>;

If you are using Oracle database version 12, grant the following additional permission:

GRANT LOGMINING TO <username>

Granting Privileges to the User

Connect to your Oracle server in SQL Developer or any SQL client tool and run the following commands to grant privileges to the database user to read data from the Oracle database:

1. Check current privileges assigned to the user:

   select * from DBA_TAB_PRIVS where grantee = 'username';

2. Grant access to tables

   • To grant access to all tables:

     GRANT SELECT ANY TABLE TO <username>;

   • To grant access to selected tables:

     GRANT SELECT ON <schema_name>.<table_name> to <username>

Limitations

None.