7 Configuring a Downstream Mining Database

Learn the details for preparing a downstream Oracle mining database to support Extract.

For examples of the downstream mining configuration, see the following:

Example 1: Capturing from One Source Database in Real-time Mode.

Example 2: Capturing from Multiple Sources in Archive-log-only Mode

Example 3: Capturing from Multiple Sources with Mixed Real-time and Archive-log-only Mode

Topics:

Evaluating Capture Options for a Downstream Deployment

Downstream deployment allows you to offload the source database.

A downstream mining database can accept both archived logs and online redo logs from a source database.

Multiple source databases can send their redo data to a single downstream database; however the downstream mining database can accept online redo logs from only one of those source databases. The rest of the source databases must ship archived logs.

When online logs are shipped to the downstream database, real-time capture by Extract is possible. Changes are captured as though Extract is reading from the source logs. In order to accept online redo logs from a source database, the downstream mining database must have standby redo logs configured.

When using a downstream mining configuration, the source database and mining database must be the same endian and same bitsize, which is 64 bits. For example, if the source database was on Linux 64-bit, you can have the mining database run on Windows 64-bit, because they have the same endian and bitsize.

Preparing the Source Database for Downstream Deployment

The source database ships its redo logs to a downstream database, and Extract uses the logmining server at the downstream database to mine the redo logs.

This section guides you in the process of:

Creating the Source User Account

There must be an Extract user on the source database. Extract uses the credentials of this user to do metadata queries and to fetch column values as needed from the source database.

The source user is specified by the USERIDALIAS parameter.

To assign the required privileges, follow the procedure in Establishing Oracle GoldenGate Credentials

Configuring Redo Transport from Source to Downstream Mining Database

To set up the transfer of redo log files from a source database to the downstream mining database, and to prepare the downstream mining database to accept these redo log files, perform the steps given in this topic.

The following summarizes the rules for supporting multiple sources sending redo to a single downstream mining database:

  • Only one source database can be configured to send online redo to the standby redo logs at the downstream mining database. The log_archive_dest_n setting for this source database should not have a TEMPLATE clause.

  • Source databases that are not sending online redo to the standby redo logs of the downstream mining database must have a TEMPLATE clause specified in the log_archive_dest_n parameter.

  • Each of the source databases that sends redo to the downstream mining database must have a unique DBID. You can select the DBID column from the v$database view of these source databases to ensure that the DBIDs are unique.

  • The FAL_SERVER value must be set to the downstream mining database. FAL_SERVER specifies the FAL (fetch archive log) server for a standby database. The value is a list of Oracle Net service names, which are assumed to be configured properly on the standby database system to point to the desired FAL servers. The list contains the net service name of any database that can potentially ship redo to the downstream database.

  • When using redo transport, there could be a delay in processing redo due to network latency. For Extract, this latency is monitored by measuring the delay between LCRs received from source database and reporting it. If the latency exceeds a threshold, a warning message appears in the report file and a subsequent information message appears when the lag drops to normal values. The default value for the threshold is 10 seconds.

Note:

The archived logs shipped from the source databases are called foreign archived logs. You must not use the recovery area at the downstream mining database to store foreign archived logs. Such a configuration is not supported by Extract. Foreign archived logs stored in the Flash Recovery Area (FRA) are not automatically deleted by RMAN jobs. These archived logs must be manually purged.

These instructions take into account the requirements to ship redo from multiple sources, if required. You must configure an Extract process for each of those sources.

To Configure Redo Transport

  1. Configure Oracle Net so that each source database can communicate with the mining database. For instructions, see Oracle Database Net Services Administrator's Guide.
  2. Configure authentication at each source database and at the downstream mining database to support the transfer of redo data. Redo transport sessions are authenticated using either the Secure Sockets Layer (SSL) protocol or a remote login password file. If a source database has a remote login password file, copy it to the appropriate directory of the mining database system. The password file must be the same at all source databases, and at the mining database. For more information about authentication requirements for redo transport, see Preparing the Primary Database for Standby Database Creation in Oracle Data Guard Concepts and Administration.
  3. At each source database, configure one LOG_ARCHIVE_DEST_n initialization parameter to transmit redo data to the downstream mining database. Set the attributes of this parameter as shown in one of the following examples, depending on whether real-time or archived-log-only capture mode is to be used.
    • Example for real-time capture at the downstream logmining server, where the source database sends its online redo logs to the downstream database:

      ALTER SYSTEM 
      SET LOG_ARCHIVE_DEST_2='SERVICE=DBMSCAP.EXAMPLE.COM ASYNC NOREGISTER
      VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=dbmscap'
      
    • Example for archived-log-only capture at the downstream logmining server:

      ALTER SYSTEM SET
      LOG_ARCHIVE_DEST_2='SERVICE=DMBSCAP.EXAMPLE.COM ASYNC NOREGISTER
      VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
      TEMPLATE=/usr/oracle/log_for_dbms1/dbms1_arch_%t_%s_%r.log
      DB_UNIQUE_NAME=dbmscap'

    Note:

    When using an archived-log-only downstream mining database, you must specify a value for the TEMPLATE attribute. Oracle also recommends that you use the TEMPLATE clause in the source databases so that the log files from all remote source databases are kept separated from the local database log files, and from each other.

  4. At the source database, set a value of ENABLE for the LOG_ARCHIVE_DEST_STATE_n initialization parameter that corresponds with the LOG_ARCHIVE_DEST_n parameter that corresponds to the destination for the downstream mining database, as shown in the following example.
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE
    
  5. At the source database, and at the downstream mining database, set the DG_CONFIG attribute of the LOG_ARCHIVE_CONFIG initialization parameter to include the DB_UNIQUE_NAME of the source database and the downstream database, as shown in the following example.
    ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbms1,dbmscap)'

Preparing the Downstream Mining Database

A downstream mining database can accept both archived logs and online redo logs from a source database.

The following sections explain how to prepare the downstream mining database:

Creating the Downstream Mining User Account

When using a downstream mining configuration, there must be an Extract mining user on the downstream database. The mining Extract process uses the credentials of this user to interact with the downstream logmining server. The downstream mining user is specified by the TRANLOGOPTIONS parameter with the MININGUSERALIAS option. See Establishing Oracle GoldenGate Credentials to assign the correct credentials for the version of your database.

Registering Extract with the Mining Database

You need to create a database logmining server to capture redo data when using a downstream database.

The creation of the logmining server captures a snapshot of the source database in the redo stream of the source database. In a source multitenant container database, you register Extract with each of the pluggable databases that you want to include for Extract.

WARNING:

Make certain that you know the earliest SCN of the log stream at which you want Extract to begin processing. Extract cannot have a starting SCN value that is lower than the first SCN that is specified when the underlying database capture process is created with the REGISTER EXTRACT command. You can use the SCN option

  1. Log into the mining database then use the commands appropriate to your environment. The use of DBLOGIN always refers to the source database.

    Command for source database deployment:

    DBLOGIN USERIDALIAS ggeast
    

    Command for downstream mining database deployment:

    DBLOGIN USERIDALIAS ggwest
    MININGDBLOGIN USERIDALIAS dbnorth
    

    Where: alias specifies the alias of the database login credential that is assigned to Extract. This credential must exist in the Oracle GoldenGate credential store. For more information, see Establishing Oracle GoldenGate Credentials. For more information about DBLOGIN and MININGDBLOGIN, see Reference for Oracle GoldenGate.

  2. Register the Extract process with the mining database.
    REGISTER EXTRACT group DATABASE [CONTAINER (container[, ...])] [SCN system_change_number]
    

    Where:

    • group is the name of the Extract group.

    • CONTAINER (container[, ...]) specifies a pluggable database (PDB) within a multitenant container database, or a list of PDBs separated with commas. The specified PDBs must exist before the REGISTER command is executed. Extract will capture only from the PDBs that are listed in this command. For example, the following command registers PDBs mypdb1 and mypdb4. Changes from any other PDBs in the multitenant container database are ignored by Oracle GoldenGate.

      REGISTER EXTRACT exte DATABASE CONTAINER (pdbeast, pdbwest, dbnorth)
      

      You can add or drop pluggable databases at a later date by stopping Extract, issuing a DBLOGIN command, and then issuing REGISTER EXTRACT with the {ADD | DROP} CONTAINER option of DATABASE.

      Note:

      Adding CONTAINERs at particular SCN on an existing Extract is not supported.

    • Registers Extract to begin capture at a specific SCN in the past. Without this option, capture begins from the time that REGISTER EXTRACT is issued. The specified SCN must correspond to the begin SCN of a dictionary build operation in a log file. You can issue the following query to find all valid SCN values:

      SELECT first_change#
         FROM v$archived_log 
         WHERE dictionary_begin = 'YES' AND 
            STANDBY_DEST = 'NO' AND
            NAME IS NOT NULL AND 
            STATUS = 'A';
      
  3. To register additional Extracts with a downstream database for the same source database, issue this REGISTER command.

    If you want to have more than one extract per source database, you can do that using the SHARE with REGISTER EXTRACT for better performance and metadata management. The specified SCN must correspond to the SCN where mining should begin in the archive logs.

    REGISTER EXTRACT group DATABASE [CONTAINER (container[, ...])] 
    [SCN system_change_number] SHARE

Note:

The register process may take a few to several minutes to complete, even though the REGISTER command returns immediately.

Configuring the Mining Database to Archive Local Redo Log Files

This procedure configures the downstream mining database to archive redo data in its online redo logs. These are redo logs that are generated at the downstream mining database.

Archiving must be enabled at the downstream mining database if you want to run Extract in real-time integrated capture mode, but it is also recommended for archive-log-only capture. Extract in integrated capture mode writes state information in the database. Archiving and regular backups will enable you to recover this state information in case there are disk failures or corruption at the downstream mining database.

To Archive Local Redo Log Files

  1. Alter the downstream mining database to be in archive log mode. You can do this by issuing the following DDL.
    STARTUP MOUNT;
    ALTER DATABASE ARCHIVELOG;
    ALTER DATABASE OPEN;
    
  2. At the downstream mining database, set the first archive log destination in the LOG_ARCHIVE_DEST_n initialization parameter as shown in the following example:
    ALTER SYSTEM SET
    LOG_ARCHIVE_DEST_1='LOCATION=/home/arc_dest/local
    VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)'
    

    Alternatively, you can use a command like this example:

    ALTER SYSTEM SET
    LOG_ARCHIVE_DEST_1='LOCATION='USE_DB_RECOVERY_FILE_DEST' valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE)'
    

    Note:

    The online redo logs generated by the downstream mining database can be archived to a recovery area. However, you must not use the recovery area of the downstream mining database to stage foreign archived logs or to archive standby redo logs. For information about configuring a fast recovery area, see the Oracle Database Backup and Recovery User’s Guide.

  3. Enable the local archive destination.
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE

For more information about these initialization parameters, see Set Primary Database Initialization Parameters in the Oracle Data Guard Concepts and Administration guide.

Configure the Wallet for the Downstream Mining Database

When TDE is enabled on source database and downstream database, then the source wallet or keys should be the same on the downstream mining database and the source database.

Follow these steps to copy the wallet directory from the source database to the downstream mining database:
  1. Shutdown the downstream database using the shutdown immediate command.

  2. Remove the wallet directory on downstream database view:
    rm $T_WORK/wallet/*
  3. Copy the $T_WORK/wallet/* from the source database view to the downstream database view.

  4. Restart the downstream database.

  5. Run checksum on the source database view and downstream database view to ensure that it matches:

    cksum $T_WORK/wallet/*

Preparing a Downstream Mining Database for Real-time Capture

This procedure is only required if you want to use real-time capture at a downstream mining database. It is not required to use archived-log-only capture mode. To use real-time capture, it is assumed that the downstream database has already been configured to archive its local redo data as shown in Configuring the Mining Database to Archive Local Redo Log Files.

Create the Standby Redo Log Files

The following steps outline the procedure for adding standby redo log files to the downstream mining database. The following summarizes the rules for creating the standby redo logs:

  • Each standby redo log file must be at least as large as the largest redo log file of the redo source database. For administrative ease, Oracle recommends that all redo log files at source database and the standby redo log files at the downstream mining database be of the same size.

  • The standby redo log must have at least one more redo log group than the redo log at the source database, for each redo thread at the source database.

The specific steps and SQL statements that are required to add standby redo log files depend on your environment. See Oracle Data Guard Concepts and Administration 11g Release 2 (11.2) for detailed instructions about adding standby redo log files to a database.

Note:

If there will be multiple source databases sending redo to a single downstream mining database, only one of those sources can send redo to the standby redo logs of the mining database. An Extract process that mines the redo from this source database can run in real-time mode. All other source databases must send only their archived logs to the downstream mining database, and the Extracts that read this data must be configured to run in archived-log-only mode.

To Create the Standby Redo Log Files

  1. In SQL*Plus, connect to the source database as an administrative user.
  2. Determine the size of the source log file. Make note of the results.
    SELECT BYTES FROM V$LOG;
    
  3. Determine the number of online log file groups that are configured on the source database. Make note of the results.
    SELECT COUNT(GROUP#) FROM V$LOG;
    
  4. Connect to the downstream mining database as an administrative user.
  5. Add the standby log file groups to the mining database. The standby log file size must be at least the size of the source log file size. The number of standby log file groups must be at least one more than the number of source online log file groups. This applies to each instance (thread) in a RAC installation. So if you have "n" threads at the source database, each having "m" redo log groups, you should configure n*(m+1) redo log groups at the downstream mining database.

    The following example shows three standby log groups.

    ALTER DATABASE ADD STANDBY LOGFILE GROUP 3
    ('/oracle/dbs/slog3a.rdo', '/oracle/dbs/slog3b.rdo') SIZE 500M;
    ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
    ('/oracle/dbs/slog4.rdo', '/oracle/dbs/slog4b.rdo') SIZE 500M;
    ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
    ('/oracle/dbs/slog5.rdo', '/oracle/dbs/slog5b.rdo') SIZE 500M;
    
  6. Confirm that the standby log file groups were added successfully.
    SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS
    FROM V$STANDBY_LOG;
    

    The output should be similar to the following:

    GROUP#     THREAD#    SEQUENCE#  ARC STATUS
    ---------- ---------- ---------- --- ----------
             3          0          0 YES UNASSIGNED
             4          0          0 YES UNASSIGNED
             5          0          0 YES UNASSIGNED
    
  7. Ensure that log files from the source database are appearing in the location that is specified in the LOCATION attribute of the local LOG_ARCHIVE_DEST_n that you set. You might need to switch the log file at the source database to see files in the directory.
Configure the Database to Archive Standby Redo Log Files Locally

This procedure configures the downstream mining database to archive the standby redo logs that receive redo data from the online redo logs of the source database. Keep in mind that foreign archived logs should not be archived in the recovery area of the downstream mining database.

To Archive Standby Redo Logs Locally

  1. At the downstream mining database, set the second archive log destination in the LOG_ARCHIVE_DEST_n initialization parameter as shown in the following example.
    ALTER SYSTEM SET
    LOG_ARCHIVE_DEST_2='LOCATION=/home/arc_dest/srl_dbms1
    VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)'
    

    Oracle recommends that foreign archived logs (logs from remote source databases) be kept separate from local mining database log files, and from each other. You must not use the recovery area of the downstream mining database to stage foreign archived logs..

  2. Enable the LOG_ARCHIVE_DEST_2 parameter you set in the previous step as shown in the following example.
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE 
    

Enabling Downstream Extract Registration Using ADG Redirection in Downstream Configuration

Oracle GoldenGate supports downstream Extract registration using ADG redirection in a downstream mining database configuration.

This approach uses an Active Dataguard (ADG) configured in a cascaded mode to transport redo logs to a downstream mining database to use with downstream Extract, which reduces the overhead on the source database.

Extract must be started using sourceless option so that it does not connect to source database instead connects to ADG using FETCHUSERID or FETCHUSERIDALIAS when it needs to fetch any non-native datatypes.

During register, Oracle GoldenGate connects to ADG as source database instead of the database where redo originates. ADG redirection is supported for the following commands and parameters in Admin Client and GGSCI:

Note:

SCHEMATRANDATA and TRANDATA, even though the command is executed on the Standby, the actual log groups are created and maintained on the primary database where the actual DML operations take place.
  • SCHEMATRANDATA

  • TRANDATA

  • FLUSH SEQUENCE

  • TRACETABLE

  • HEARTBEATTABLE

  • REGISTER EXTRACT

This feature is supported for CDB and supports wildcard registration. It is only supported when using Oracle Database 21c and higher.

How to Enable Downstream Extract Registration Using ADG Redirection

Here are the steps to enable downstream Extract to work with ADG Standby:
  1. Add an additional LOG_ARCHIVE_DESTINATION_N (LAD) on the ADG standby, as shown in the following example:
    alter system set log_archive_dest_3='service=service name mining db ASYNC NOREGISTER VALID_FOR(STANDBY_LOGFILES,STANDBY_ROLES) DB_UNIQUE_NAME=db unique name of 3rd db' scope=both

    This step transports and generates the standby_logfiles for an ADG Standby.

  2. Set the LOG_ARCHIVE_CONFIG on the ADG Standby to ship the logs to the mining database, as shown in the following example:
    ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=‘dg_config(db unique name of 1st db,db unique name of 2nd db,db unique name of 3rd db)’ scope=both;
  3. On the mining database, set up the location to store the incoming standby_logfiles on the mining database:
    alter system set log_archive_dest_2='location=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(STANDBY_LOGFILE,ALL_ROLES)' scope=both
    
  4. Run LOG_ARCHIVE_CONFIG on the mining database, so that the Extract process is able to read them on the mining database, as shown in the following example:
    ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=‘dg_config(db unique name of 1st db, db unique name of 2nd db, db unique name of 3rd db)’ scope=both
  5. For a downstream Extract, you need to ensure that the database connections are appropriately configured for GGSCI and Admin Client. When registering the Extract, you need to make sure that DBLOGIN connection is made to the ADG Standby, that is open for read only activity. To add the Extract and register it, use the following command:
    dblogin userid ggadmin@inst2, password ggadmin (inst2 is the ADG not primary)
            miningdblogin userid ggadmin@inst3, password ggadmin (inst3 is the mining database)
    
  6. Now, register an Extract that uses the NOUSERID parameter:
    add extract ext1, integrated tranlog, begin now register extract ext1 database
  7. After the Extract is registered, you can use this Extract to mine data and start the Extract normally.

Example 1: Capturing from One Source Database in Real-time Mode

This example captures changes from source database DBMS1 by deploying an Extract at a downstream mining database DBMSCAP.

Note:

The example assumes that you created the necessary standby redo log files as shown in Configuring a Downstream Mining Database.

This assumes that the following users exist:

  • User GGADM1 in DBMS1 whose credentials Extract will use to fetch data and metadata from DBMS1. This user has the alias of ggadm1 in the Oracle GoldenGate credential store and logs in as ggadm1@dbms1. It is assumed that the DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE() procedure was called to grant appropriate privileges to this user at the source database.

  • User GGADMCAP in DBMSCAP whose credentials Extract will use to retrieve logical change records from the logmining server at the downstream mining database DBMSCAP. This user has the alias of ggadmcap in the Oracle GoldenGate credential store and logs in as ggadmcap@dbmscap. It is assumed that the DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE() procedure was called to grant appropriate privileges to this user at the mining database.

Prepare the Mining Database to Archive its Local Redo

To prepare the mining database to archive its local redo:

  1. The downstream mining database must be in archive log mode. You can do this by issuing the following DDL.
    	STARTUP MOUNT;
    	ALTER DATABASE ARCHIVELOG;
    	ALTER DATABASE OPEN;
    
  2. At the downstream mining database, set log_archive_dest_1 to archive local redo.
    ALTER SYSTEM SET
    LOG_ARCHIVE_DEST_1='LOCATION=/home/arc_dest/local
    VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE)'
    
  3. Enable log_archive_dest_1.
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE 

Prepare the Mining Database to Archive Redo Received in Standby Redo Logs from the Source Database

To prepare the mining database to archive the redo received in standby redo logs from the source database:

  1. At the downstream mining database, set log_archive_dest_2 as shown in the following example.
    ALTER SYSTEM SET
    LOG_ARCHIVE_DEST_2='LOCATION=/home/arc_dest/srl_dbms1
    VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)'
    
  2. Enable log_archive_dest_2 as shown in the following example.
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE
    
  3. Set DG_CONFIG at the downstream mining database.
    ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbms1,dbmscap)'

Prepare the Source Database to Send Redo to the Mining Database

To prepare the source database to send redo to the mining database:

  1. Make sure that the source database is running with the required compatibility.
    select name, value from v$parameter where name = 'compatible';
    
    NAME            VALUE
    ---------       ---------------------
    compatible      11.1.0.7.0
    

    The minimum compatibility setting required from integrated capture is 11.1.0.0.0.

  2. Set DG_CONFIG at the source database.
    ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbms1,dbmscap)';
    
  3. Set up redo transport at the source database.
    ALTER SYSTEM
    SET LOG_ARCHIVE_DEST_2='SERVICE=DBMSCAP.EXAMPLE.COM ASYNC OPTIONAL NOREGISTER 
    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=dbmscap';
    
  4. Enable the downstream destination.
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

Set up Extract (ext1) on DBMSCAP

To set up Extract (ext1) on DBMSCAP:

  1. Register Extract with the downstream mining database. In the credential store, the alias name of ggadm1 is linked to a user connect string of ggadm1@dbms1. The alias name of ggadmcap is linked to a user connect string of ggadmcap@dbmscap.
    GGSCI> DBLOGIN USERIDALIAS ggadm1
    GGSCI> MININGDBLOGIN USERIDALIAS ggadmcap
    GGSCI> REGISTER EXTRACT ext1 DATABASE
    
  2. Create Extract at the downstream mining database.
    GGSCI> ADD EXTRACT ext1 INTEGRATED TRANLOG BEGIN NOW
    
  3. Edit Extract parameter file ext1.prm. The following lines must be present to take advantage of real-time capture. In the credential store, the alias name of ggadm1 is linked to a user connect string of ggadm1@dbms1. The alias name of ggadmcap is linked to a user connect string of ggadmcap@dbmscap.
    USERIDALIAS ggadm1
    TRANLOGOPTIONS MININGUSERALIAS ggadmcap
    TRANLOGOPTIONS INTEGRATEDPARAMS (downstream_real_time_mine Y)
    
  4. Start Extract.
    GGSCI> START EXTRACT ext1

Note:

You can create multiple Extracts running in real-time Extract mode in the downstream mining database, as long as they all are capturing data from the same source database, such as capturing changes for database DBMS1 in the preceding example.

Example 2: Capturing from Multiple Sources in Archive-log-only Mode

The following example captures changes from database DBMS1 and DBMS2 by deploying an Extract at a downstream mining database DBMSCAP.

It assumes the following users:

  • User GGADM1 in DBMS1 whose credentials Extract will use to fetch data and metadata from DBMS1. It is assumed that the DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE() procedure was called to grant appropriate privileges to this user at DBMS1.

  • User GGADM2 in DBMS2 whose credentials Extract will use to fetch data and metadata from DBMS2. It is assumed that the DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE() procedure was called to grant appropriate privileges to this user at DBMS2.

  • User GGADMCAP in DBMSCAP whose credentials Extract will use to retrieve logical change records from the logmining server at the downstream mining database. It is assumed that the DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE() procedure was called to grant appropriate privileges to this user at the downstream mining database DBMSCAP.

This procedure also assumes that the downstream mining database is configured in archive log mode.

Prepare the Mining Database to Archive its Local Redo

To prepare the mining database to archive its local redo:

  1. The downstream mining database must be in archive log mode. You can do this by issuing the following DDL.
    	STARTUP MOUNT;
    	ALTER DATABASE ARCHIVELOG;
    	ALTER DATABASE OPEN;
    
  2. At the downstream mining database, set log_archive_dest_1 to archive local redo.
    ALTER SYSTEM SET
    LOG_ARCHIVE_DEST_1='LOCATION=/home/arc_dest/local
    VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE)'
    
  3. Enable log_archive_dest_1.
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE 

Prepare the Mining Database to Archive Redo from the Source Database

Set DG_CONFIG at the downstream mining database.

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbms1,dbms2, dbmscap)'

Prepare the First Source Database to Send Redo to the Mining Database

To prepare the first source database to send redo to the mining database:

  1. Make certain that DBMS1 source database is running with the required compatibility.
    select name, value from v$parameter where name = 'compatible';
    
    NAME            VALUE
    ---------       ---------------------
    compatible      11.1.0.0.0
    

    The minimum compatibility setting required from capture is 11.1.0.0.0.

  2. Set DG_CONFIG at DBMS1 source database.
    ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbms1, dbmscap)'; 
    
  3. Set up redo transport at DBMS1 source database. The TEMPLATE clause is mandatory if you want to send redo data directly to foreign archived logs at the downstream mining database.
    ALTER SYSTEM
    SET LOG_ARCHIVE_DEST_2='SERVICE=DBMSCAP.EXAMPLE.COM ASYNC OPTIONAL NOREGISTER
    TEMPLATE='/usr/orcl/arc_dest/dbms1/dbms1_arch_%t_%s_%r.log VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=dbmscap';
    
  4. Enable the downstream destination.
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

Prepare the Second Source Database to Send Redo to the Mining Database

To prepare the second source database to send redo to the mining database:

  1. Make sure that DBMS2 source database is running with the required compatibility.
    select name, value from v$parameter where name = 'compatible';
    
    NAME                    VALUE
    ---------               ---------------------
    compatible              11.1.0.0.0
    

    The minimum compatibility setting required from capture is 11.1.0.0.0.

  2. Set DG_CONFIG at DBMS2 source database.
    ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbms2, dbmscap)'; 
    
  3. Set up redo transport at DBMS2 source database. The TEMPLATE clause is mandatory if you want to send redo data directly to foreign archived logs at the downstream mining database.
    ALTER SYSTEM
    SET LOG_ARCHIVE_DEST_2='SERVICE=DBMSCAP.EXAMPLE.COM ASYNC OPTIONAL NOREGISTER
    TEMPLATE='/usr/orcl/arc_dest/dbms2/dbms2_arch_%t_%s_%r.log VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=dbmscap';
    
  4. Enable the downstream destination.
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

Set up Extracts at Downstream Mining Database

These steps set up Extract at the downstream database to capture from the archived logs sent by DBMS1 and DBMS2.

Example 3: Capturing from Multiple Sources with Mixed Real-time and Archive-log-only Mode

The following example captures changes from database DBMS1, DBMS2 and DBMS3 by deploying an Extract at a downstream mining database DBMSCAP.

Note:

This example assumes that you created the necessary standby redo log files as shown in Configuring a Downstream Mining Database.

It assumes the following users:

  • User GGADM1 in DBMS1 whose credentials Extract will use to fetch data and metadata from DBMS1. It is assumed that the DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE() procedure was called to grant appropriate privileges to this user at DBMS1.

  • User GGADM2 in DBMS2 whose credentials Extract will use to fetch data and metadata from DBMS2. It is assumed that the DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE() procedure was called to grant appropriate privileges to this user at DBMS2.

  • User GGADM3 in DBMS3 whose credentials Extract will use to fetch data and metadata from DBMS3. It is assumed that the DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE() procedure was called to grant appropriate privileges to this user at DBMS3.

  • User GGADMCAP in DBMSCAP whose credentials Extract will use to retrieve logical change records from the logmining server at the downstream mining database. It is assumed that the DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE() procedure was called to grant appropriate privileges to this user at the downstream mining database DBMSCAP.

This procedure also assumes that the downstream mining database is configured in archive log mode.

In this example, the redo sent by DBMS3 will be mined in real time mode, whereas the redo data sent from DBMS1 and DBMS2 will be mined in archive-log-only mode.

Prepare the Mining Database to Archive its Local Redo

To prepare the mining database to archive its local redo:

  1. The downstream mining database must be in archive log mode. You can do this by issuing the following DDL.
    STARTUP MOUNT;
    ALTER DATABASE ARCHIVELOG;
    ALTER DATABASE OPEN;
    
  2. At the downstream mining database, set log_archive_dest_1 to archive local redo.
    ALTER SYSTEM SETLOG_ARCHIVE_DEST_1='LOCATION=/home/arc_dest/localVALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE)'
    
  3. Enable log_archive_dest_1.
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE 
    

Prepare the Mining Database to Accept Redo from the Source Databases

Because redo data is being accepted in the standby redo logs of the downstream mining database, the appropriate number of correctly sized standby redo logs must exist. If you did not configure the standby logs, see Configuring a Downstream Mining Database.

  1. At the downstream mining database, set the second archive log destination in the LOG_ARCHIVE_DEST_n initialization parameter as shown in the following example. This is needed to handle archive standby redo logs.
    ALTER SYSTEM SET
    LOG_ARCHIVE_DEST_2='LOCATION=/home/arc_dest/srl_dbms3
    VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)'
    
  2. Enable the LOG_ARCHIVE_DEST_STATE_2 initialization parameter that corresponds with the LOG_ARCHIVE_DEST_2 parameter as shown in the following example.
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE 
  3. Set DG_CONFIG at the downstream mining database to accept redo data from all of the source databases.
    ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbms1, dbms2, dbms3, dbmscap)' 

Prepare the First Source Database to Send Redo to the Mining Database

To prepare the first source database to send redo to the mining database:

  1. Make certain that DBMS1 source database is running with the required compatibility.
    select name, value from v$parameter where name = 'compatible';
    
    NAME            VALUE
    ---------       ---------------------
    compatible      11.1.0.0.0
    

    The minimum compatibility setting required from capture is 11.1.0.0.0.

  2. Set DG_CONFIG at DBMS1 source database.
    ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbms1, dbmscap)'; 
    
  3. Set up redo transport at DBMS1 source database. The TEMPLATE clause is mandatory if you want to send redo data directly to foreign archived logs at the downstream mining database.
    ALTER SYSTEM
    SET LOG_ARCHIVE_DEST_2='SERVICE=DBMSCAP.EXAMPLE.COM ASYNC OPTIONAL NOREGISTER
    TEMPLATE='/usr/orcl/arc_dest/dbms1/dbms1_arch_%t_%s_%r.log VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=dbmscap';
    
  4. Enable the downstream destination.
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

Prepare the Second Source Database to Send Redo to the Mining Database

To prepare the second source database to send redo to the mining database:

  1. Make sure that DBMS2 source database is running with the required compatibility.
    select name, value from v$parameter where name = 'compatible';
    
    NAME                    VALUE
    ---------               ---------------------
    compatible              11.1.0.0.0
    

    The minimum compatibility setting required from capture is 11.1.0.0.0.

  2. Set DG_CONFIG at DBMS2 source database.
    ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbms2, dbmscap)'; 
    
  3. Set up redo transport at DBMS2 source database. The TEMPLATE clause is mandatory if you want to send redo data directly to foreign archived logs at the downstream mining database.
    ALTER SYSTEM
    SET LOG_ARCHIVE_DEST_2='SERVICE=DBMSCAP.EXAMPLE.COM ASYNC OPTIONAL NOREGISTER
    TEMPLATE='/usr/orcl/arc_dest/dbms2/dbms2_arch_%t_%s_%r.log VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=dbmscap';
    
  4. Enable the downstream destination.
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

Prepare the Third Source Database to Send Redo to the Mining Database

To prepare the third source database to send redo to the mining database:

  1. Make sure that DBMS3 source database is running with the required compatibility.
    select name, value from v$parameter where name = 'compatible';
    
    NAME                    VALUE
    ---------               ---------------------
    compatible              11.1.0.0.0
    

    The minimum compatibility setting required from capture is 11.1.0.0.0.

  2. Set DG_CONFIG at DBMS3 source database.
    ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbms3, dbmscap)'; 
    
  3. Set up redo transport at DBMS3 source database. Because DBMS3 is the source that will send its online redo logs to the standby redo logs at the downstream mining database, do not specify a TEMPLATE clause.
    ALTER SYSTEM
    SET LOG_ARCHIVE_DEST_2='SERVICE=DBMSCAP.EXAMPLE.COM ASYNC OPTIONAL NOREGISTER
    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=dbmscap';
    
  4. Enable the downstream destination.
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

Set up Extracts at Downstream Mining Database

These steps set up Extract at the downstream database to capture from the archived logs sent by DBMS1 and DBMS2.

Set up Extract (ext1) to Capture Changes from Archived Logs Sent by DBMS1

Perform the following steps on the DBMSCAP downstream mining database.

  1. Register Extract with DBMSCAP for the DBMS1 source database. In the credential store, the alias name of ggadm1 is linked to a user connect string of ggadm1@dbms1.The alias name of ggadmcap is linked to a user connect string of ggadmcap@dbmscap.
    GGSCI> DBLOGIN USERIDALIAS ggadm1
    GGSCI> MININGDBLOGIN USERIDALIAS ggadmcap
    GGSCI> REGISTER EXTRACT ext1 DATABASE
    
  2. Add Extract at the mining database DBMSCAP.
    GGSCI> ADD EXTRACT ext1 INTEGRATED TRANLOG BEGIN NOW
    
  3. Edit the Extract parameter file ext1.prm. In the credential store, the alias name of ggadm1 is linked to a user connect string of ggadm1@dbms1. The alias name of ggadmcap is linked to a user connect string of ggadmcap@dbmscap.
    USERIDALIAS ggadm1
    TRANLOGOPTIONS MININGUSERALIAS ggadmcap
    TRANLOGOPTIONS INTEGRATEDPARAMS (downstream_real_time_mine N)
    
  4. Start Extract.
    GGSCI> START EXTRACT ext1
Set up Extract (ext2) to Capture Changes from Archived Logs Sent by DBMS2

Perform the following steps on the DBMSCAP downstream mining database.

  1. Register Extract with the mining database for source database DBMS2. In the credential store, the alias name of ggadm2 is linked to a user connect string of ggadm2@dbms2.The alias name of ggadmcap is linked to a user connect string of ggadmcap@dbmscap.
    GGSCI> DBLOGIN USERIDALIAS ggadm2
    GGSCI> MININGDBLOGIN USERIDALIAS ggadmcap
    GGSCI> REGISTER EXTRACT ext2 DATABASE
    
  2. Create Extract at the mining database.
    GGSCI> ADD EXTRACT ext2 INTEGRATED TRANLOG, BEGIN NOW
    
  3. Edit the Extract parameter file ext2.prm. In the credential store, the alias name of ggadm2 is linked to a user connect string of ggadm2@dbms2.The alias name of ggadmcap is linked to a user connect string of ggadmcap@dbmscap.
    USERIDALIAS ggadm2
    TRANLOGOPTIONS MININGUSERALIAS ggadmcap
    TRANLOGOPTIONS INTEGRATEDPARAMS (downstream_real_time_mine N)
    
  4. Start Extract.
    GGSCI> START EXTRACT ext2
Set up Extract (ext3) to Capture Changes in Real-time Mode from Online Logs Sent by DBMS3

Perform the following steps on the DBMSCAP downstream mining database.

  1. Register Extract with the mining database for source database DBMS3. In the credential store, the alias name of ggadm3 is linked to a user connect string of ggadm3@dbms3.The alias name of ggadmcap is linked to a user connect string of ggadmcap@dbmscap.
    GGSCI> DBLOGIN USERID ggadm3
    GGSCI> MININGDBLOGIN USERID ggadmcap
    GGSCI> REGISTER EXTRACT ext3 DATABASE
    
  2. Create Extract at the mining database.
    GGSCI> ADD EXTRACT ext3 INTEGRATED TRANLOG, BEGIN NOW
  3. Edit the Extract parameter file ext3.prm. To enable real-time mining, you must specify downstream_real_time_mine. In the credential store, the alias name of ggadm3 is linked to a user connect string of ggadm3@dbms3.The alias name of ggadmcap is linked to a user connect string of ggadmcap@dbmscap.
    USERIDALIAS ggadm3
    TRANLOGOPTIONS MININGUSERALIAS ggadmcap
    TRANLOGOPTIONS INTEGRATEDPARAMS (downstream_real_time_mine Y)
    
  4. Start Extract.
    GGSCI> START EXTRACT ext3

Note:

You can create multiple Extracts running in real-time integrated capture mode in the downstream mining database, as long as they all are capturing data from the same source database, such as all capturing for database DBMS3 in the preceding example.