Oracle Database 11g -- Configure a Downstream Mining Database for Real-Time Data Capture

Problem

You want to setup a downstream mining database for real-time data capture

The source and the downstream mining databases are Oracle Database 11.2.0.4 version

Solution

1) Ensure network connectivity between the source and mining databases. Configure the TNSNAMES.ORA file to include tns entries, template:

<alias> =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <host>)(PORT = <port>))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = <service_name>)
    )
  )

2) Copy password file from the source host to the mining database host

3) At the mining database, configure standby redo logs, the size must match the size of the redo logs of the source database, and the number of standby redo log groups must be one more the number of groups of each redo thread of the source database

SQL> alter database add standby logfile thread <thread#> group <group#> '<path>' size <size> [reuse];

4) At the mining database, configure archiving the standby redo logs locally:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=<path> VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)';

5) At the mining database, enable redo shipping configuration:

SQL> alter system set log_archive_config='dg_config=(<db_unique_name_source>, <db_unique_name_target>)'; 

6) At the source database, add the mining database into log shipping configuration:

SQL> alter system set log_archive_config='dg_config=(<db_unique_name_source>, <db_unique_name_target>)';

7) At the source database, configure a destination for sending the redo data:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=<db_unique_name_target> LGWR ASYNC NOREGISTER VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) REOPEN=5 DB_UNIQUE_NAME=<db_unique_name_target>';

Note. v$standby_log view will not populate data until capturing process is started on the mining database