Configuring Oracle to use Oracle Reader

Using Oracle Reader requires the following configuration changes in Oracle:

enable archivelog, if not already enabled
enable supplemental log data, if not already enabled
set up LogMiner
create a user account for OracleReader and grant it the privileges necessary to use LogMiner

Basic Oracle configuration tasks

The following tasks must be performed regardless of which Oracle version or variation you are using.

Enable archivelog

Log in to SQL*Plus as the sys user.
Enter the following command:
```
select log_mode from v$database;
```
If the command returns ARCHIVELOG, it is enabled. Skip ahead to Enabling supplemental log data.
If the command returns NOARCHIVELOG, enter: shutdown immediate
Wait for the message ORACLE instance shut down, then enter: startup mount
Wait for the message Database mounted, then enter:
```
alter database archivelog;
alter database open;
```
To verify that archivelog has been enabled, enter select log_mode from v$database; again. This time it should return ARCHIVELOG.

Enable supplemental log data

If you are using Amazon RDS for Oracle, see the instructions below.

Enter the following command:
```
select supplemental_log_data_min, supplemental_log_data_pk from v$database;
```
If the command returns YES or IMPLICIT, supplemental log data is already enabled. For example,
```
SUPPLEME SUP
-------- ---
YES      NO
```
indicates that supplemental log data is enabled, but primary key logging is not. If it returns anything else, enter:
```
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
```
To enable primary key logging for all tables in the database enter:
```
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
```
Alternatively, to enable primary key logging only for selected tables (do not use this approach if you plan to use wildcards in the OracleReader Tables property to capture change data from new tables):
```
ALTER TABLE <schema name>.<table name> ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
```
If replicating Oracle data to one of the following
- Azure Synapse with Mode set to MERGE with Optimized Merge disabled
- BigQuery with Optimized Merge disabled
- Fabric Data Warehouse with Optimized Merge disabled
- Redshift
- Snowflake with Optimized Merge disabled
Enable supplemental logging on all columns for all tables in the source database:
```
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
```
Alternatively, to enable only for selected tables:
```
ALTER TABLE <schema>.<table name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
```
To activate your changes, enter:
```
alter system switch logfile;
```

If using Amazon RDS for Oracle, use the following commands instead:

exec rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action => 'ADD');
exec rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action => 'ADD', p_type => 'PRIMARY KEY');
exec rdsadmin.rdsadmin_util.switch_logfile;
select supplemental_log_data_min, supplemental_log_data_pk from v$database;

Create an Oracle user with LogMiner privileges

You may use LogMiner with any supported Oracle version.

Log in as sysdba and enter the following commands to create a role with the privileges required by the Striim OracleReader adapter and create a user with that privilege. You may give the role and user any names you like. Replace ******** with a strong password.

If using Oracle 11g, or 12c, 18c, or 19c without CDB

Enter the following commands:

create role STRIIM_PRIVS;
grant create session,
  execute_catalog_role,
  select any transaction,
  select any dictionary
  to STRIIM_PRIVS;
grant select on SYSTEM.LOGMNR_COL$ to STRIIM_PRIVS;
grant select on SYSTEM.LOGMNR_OBJ$ to STRIIM_PRIVS;
grant select on SYSTEM.LOGMNR_USER$ to STRIIM_PRIVS;
grant select on SYSTEM.LOGMNR_UID$ to STRIIM_PRIVS;
create user STRIIM identified by ******** default tablespace users;
grant STRIIM_PRIVS to STRIIM;
alter user STRIIM quota unlimited on users;

For Oracle 12c or later, also enter the following command:

grant LOGMINING to STRIIM_PRIVS;

If using Database Vault, omit execute_catalog_role, and also enter the following commands:

grant execute on SYS.DBMS_LOGMNR to STRIIM_PRIVS;
grant execute on SYS.DBMS_LOGMNR_D to STRIIM_PRIVS;
grant execute on SYS.DBMS_LOGMNR_LOGREP_DICT to STRIIM_PRIVS;
grant execute on SYS.DBMS_LOGMNR_SESSION to STRIIM_PRIVS;

If using Oracle 12c, 18c, or 19c with PDB

Enter the following commands. Replace <PDB name> with the name of your PDB.

create role C##STRIIM_PRIVS;
grant create session,
execute_catalog_role,
select any transaction,
select any dictionary,
logmining
to C##STRIIM_PRIVS;
grant select on SYSTEM.LOGMNR_COL$ to C##STRIIM_PRIVS;
grant select on SYSTEM.LOGMNR_OBJ$ to C##STRIIM_PRIVS;
grant select on SYSTEM.LOGMNR_USER$ to C##STRIIM_PRIVS;
grant select on SYSTEM.LOGMNR_UID$ to C##STRIIM_PRIVS;
create user C##STRIIM identified by ******* container=all;
grant C##STRIIM_PRIVS to C##STRIIM container=all;
alter user C##STRIIM set container_data = (CDB$ROOT, <PDB name>) container=current;

If using Database Vault, omit execute_catalog_role, and also enter the following commands:

grant execute on SYS.DBMS_LOGMNR to C##STRIIM_PRIVS;
grant execute on SYS.DBMS_LOGMNR_D to C##STRIIM_PRIVS;
grant execute on SYS.DBMS_LOGMNR_LOGREP_DICT to C##STRIIM_PRIVS;
grant execute on SYS.DBMS_LOGMNR_SESSION to C##STRIIM_PRIVS;

Creating the QUIESCEMARKER table for Oracle Reader

To allow Striim to quiesce (see QUIESCE) an application that uses Oracle Reader, you must create a quiescemarker table in Oracle. (This is not necessary when Reading from a standby or using OJet.)

The DDL for creating the table is:

CREATE TABLE QUIESCEMARKER (source varchar2(100), 
  status varchar2(100),
  sequence NUMBER(10),
  inittime timestamp, 
  updatetime timestamp default sysdate, 
  approvedtime timestamp, 
  reason varchar2(100), 
  CONSTRAINT quiesce_marker_pk PRIMARY KEY (source, sequence));
ALTER TABLE QUIESCEMARKER ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

The Oracle user specified in Oracle Reader's Username property must have SELECT, INSERT, and UPDATE privileges on this table.

Reading from a standby

Oracle Reader can read from a standby rather than a primary database.

Requirements

Create an Active Data Guard standby. (Striim cannot read from a regular Data Guard standby.)
Open the standby in read-only mode.
On the primary, perform all steps in Basic Oracle configuration tasks and Create an Oracle user with LogMiner privileges. No quiescemarker table is required when reading from a standby.

Limitations

Oracle Reader will read only from the archive log, not from redo logs.
Bidirectional replication is not supported.
Oracle Reader will reject QUIESCE if there are any open transactions.

Create the dictionary file

On the primary, use SQL Plus or another client to create a dictionary file.
For Oracle 11g or 12.1.0.2, enter the following commands, replacing <path> in the second command with the path returned by the first command. If the first command does not return a path, you must set UTL_FILE_DIR.
```
show parameter utl_file_dir;
execute dbms_logmnr_d.build('dict.ora', '<path>');
```
For Oracle 12.2.0.1.0 or later, enter the following commands.
```
CREATE DIRECTORY "dictionary_directory" AS '/opt/oracle/dictionary';
EXECUTE dbms_logmnr_d.build(dictionary_location=>'dictionary_directory', 
dictionary_filename=>'dict.ora',
options => dbms_logmnr_d.store_in_flat_file);
```
Copy dict.ora to a directory on the standby.

Configure Oracle Reader properties in your application

Set Dictionary Mode to ExternalDictionaryFileCatalog.
Set Database Role to PHYSICAL_STANDBY.
Set External Dictionary File to the fully qualified name of the dictionary file you copied to the standby, for example, /home/oracle/dict.ora

Handling DDL changes

When DDL changes must be made to the tables being read by Oracle Reader, do the following:

On the primary, stop DML activity and make sure there are no open transactions.
On the primary, force a log switch.
In Striim, quiesce the application (see QUIESCE). If Oracle Reader refuses the quiesce, wait a few minutes and try again.
On the primary, perform the DDL changes.
Repeat the procedure in "Create the dictionary file," above, replacing the old file on the standby with the new one.
Start the Striim application.

In this section:

Striim Platform 5.0 documentation