Preparing Oracle Source Database for Attunity Replication

Right off the bat, Oracle Database or any other Database Management System isn't ready to start replication of data, there are some configuration required to make it ready to replicate data. This article guides you to configure Oracle Database (Source) to make it ready to replicate using Attunity Replicate tool. This is a replication tool by offered by Qlik which is easy to setup, mange, monitor replication task and administer it. The configuration we are going to enable in Oracle database is also similar to Golden Gate replication but there are few difference. Below are the list of configuration required for Oracle to make it Attunity Replication ready. This configuration is only for Oracle Database which will  be a SOURCE for data replication.

1. Oracle User Access with permission
2. Enable ARCHIVE LOG Mode on
3. Enable Supplemental Logging on Database
4. Enable Supplemental Logging on Table Level
5. Enable Automatic Backup

Next, I am going to go into detail as what has to happen for each bullet point and the Oracle script are provided for each steps to accomplish. The codes are tested on Oracle version 12c and Attunity 6.4

Oracle User Access with permission: You need to create Attunity user in Oracle database with privilege to  write, read database dictionary, log miner, ASM etc.

• Create Table space
• Create User Attunity
• Grant Access

##TableSpace Creation
CREATE TABLESPACE cdc_data DATAFILE '+CONSOLDATED_DATE' SIZE 50M autoextend on maxsize 5G;

## User Creation
CREATE USER attunity identified by attunity default tablespace cdc_data temporary tablespace temp;

## Access to Attunity
GRANT CONNECT TO attunity;
GRANT CREATE SESSION TO attunity;
GRANT SELECT ANY TABLE TO attunity;
GRANT SELECT ANY TRANSACTION TO attunity;
GRANT SELECT on ALL_CATALOG TO attunity;
GRANT SELECT on ALL_CONS_COLUMNS TO attunity;
GRANT SELECT on ALL_CONSTRAINTS TO attunity;
GRANT SELECT on ALL_IND_COLUMNS TO attunity;
GRANT SELECT on ALL_INDEXES TO attunity;
GRANT SELECT on ALL_LOG_GROUPS TO attunity;
GRANT SELECT on ALL_OBJECTS TO attunity;
GRANT SELECT on ALL_TAB_COLS TO attunity;
GRANT SELECT on ALL_TABLES TO attunity;
GRANT SELECT on ALL_USERS TO attunity;
GRANT SELECT on DBA_OBJECTS TO attunity;
GRANT SELECT on SYS.DBA_REGISTRY TO attunity;
GRANT SELECT on V_$ARCHIVED_LOG TO attunity;
GRANT SELECT on V_$DATABASE TO attunity;
GRANT SELECT on V_$LOG TO attunity;
GRANT SELECT on V_$LOGFILE TO attunity;
GRANT SELECT on V_$NLS_PARAMETERS TO attunity;
GRANT SELECT on V_$PARAMETER TO attunity;
GRANT SELECT on V_$THREAD TO attunity;
GRANT SELECT on V_$TIMEZONE_NAMES TO attunity;
GRANT SELECT on V_$TRANSACTION TO attunity;
GRANT EXECUTE on DBMS_LOGMNR TO attunity;
GRANT SELECT on V_$LOGMNR_CONTENTS TO attunity;
GRANT SELECT on V_$LOGMNR_LOGS TO attunity;
GRANT CREATE ANY DIRECTORY TO attunity;
GRANT SELECT ANY DICTIONARY TO attunity;
GRANT LOGMINING TO attunity;
GRANT SELECT on V$TRANSPORTABLE_PLATFORM TO attunity;

Note: You will need to create ASM user if your database uses ASM to store data.

Enable ARCHIVE LOG Mode on: Oracle can be run in two different modes: The ARCHIVELOG mode and the NOARCHIVELOG mode. To use the Oracle logs with Attunity Replicate, run the database in ARCHIVELOG mode. If the log is not set to ARCHIVELOG mode, then execute the following query:

ALTER database ARCHIVELOG;

Archive log mode put the database in for creating a backup of all transaction that have occurred in the database so you can recover to any point in time. NOARCHIVELOG Mode: You cannot recover db to any point in time. The only advantage is it will increases the database performance.

Enable Supplemental Logging on Database: Redo log files are generally used for instance recovery and media recovery. The data needed for such operations is automatically recorded in the redo log files. However, a redo-based application may require that additional columns be logged in the redo log files. The process of logging these additional columns is called supplemental logging. By default, Oracle Database does not provide any supplemental logging, which means that by default LogMiner is not usable. Therefore, you must enable at least minimal supplemental logging before generating log files which will be analyzed by Log Miner.

Set up supplemental logging as described in the steps below

Check that supplemental logging is enable for the database.if not use the below ALTER script to enable database logging.

SELECT name, value, description FROM v$parameter WHERE name ='compatible';
SELECT supplemental_log_data_min FROM v$database;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Note: You can automatically set up supplemental logging in the Advanced tab of the Oracle database dialog box. If you select this option, you do not have to carry out the above procedure. 

Enable Supplemental Logging on Table: A table level logging is required for each table that are going to get replicated. This will ensure the DDL and DML are captures on redo log along with the Key and values.

1. If a Primary Key exists, supplemental logging must be added for the Primary Key either by using the format to add supplemental logging on the Primary Key, or by adding supplemental logging on the Primary Key columns.
2. If no Primary Key exists and the table has a single Unique Index, then all of the Unique Index’s columns must be added to the supplemental log. Using SUPPLEMENTAL LOGDATA (UNIQUE INDEX) COLUMNS does not add the Unique Index columns to the log.
3. If no Primary Key exists and the table has multiple Unique Indexes, Attunity Replicate will select the first Unique Index. Attunity Replicate will use the first index in an alphabetically ordered ascending list. Supplemental logging must be added on the selected index's columns. Using SUPPLEMENTAL LOG DATA (UNIQUE INDEX) COLUMNS does not add the Unique Index columns to the log.
4. If there is no Primary Key and no Unique Index, supplemental logging must be added on all columns. When the target table Primary Key/Unique is different than the source table Primary Key/Unique Index, the user needs to add supplemental logging manually on the source table columns that comprise the target table Primary Key/Unique Index.
5. If you change the target table Primary Key, the supplemental logging must be added on the selected index's columns instead of the columns of the original Primary key/Unique.

Note:  If ALL COLUMNS supplemental logging has been added to the table, there is no need to add any additional logging. I use this to ensure I don't have any issue if the table structure changes later. When the Insert the missing target record Apply Conflicts option is selected, supplemental logging must be enabled for ALL the source table columns.

ALTER TABLE hr.employee add supplemental log data (all) columns ; 

Enabling Automatic backups: Management Options of setting up your Oracle database instance, set the Enabled Automatic Backups option to Yes. To retain archived redo logs of your Oracle database instance (which will allow Attunity

Replicate to retrieve the log information using Oracle Log Miner), execute the following command (example 24 hours):

exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',24);

Make sure there is enough storage available for archive redo logs during the specified period. If  you enable backups, archive logs are retrained on your Oracle Source database for 24 hours. Just incase your target is down for 24 hours, you can resume Attunity replicate without any issue. If it is down for over 24 hours, then Attunity replicate wont start because the archive redo log is missing and needs to be archived. Your database is now replication ready for Attunity Replicate.