11 Additional Configuration Steps for Using Classic Capture

This chapter contains additional configuration and preparation requirements that are specific only to Extract when operating in classic capture mode.

This chapter includes the following sections:

These requirements supplement the basic configuration requirements documented in:

Chapter 8, "Configuring Capture in Classic Mode"

11.1 Configuring Oracle TDE Data in Classic Capture Mode

The following special configuration steps are required to support TDE when Extract is in classic capture mode. This section does not apply to Extract in integrated capture mode.

Note:

When in integrated mode, Extract leverages the database logmining server and supports TDE column encryption and TDE tablespace encryption without special setup requirements or parameter settings. For more information about integrated capture, see Chapter 5, "Choosing Capture and Apply Modes".

11.1.1 Overview of TDE Support in Classic Capture Mode

TDE support when Extract is in classic capture mode requires the exchange of two kinds of keys:

  • The encrypted key can be a table key (column-level encryption), an encrypted redo log key (tablespace-level encryption), or both. This key is shared between the Oracle database and Extract.

  • The decryption key is named ORACLEGG and its password is known as the shared secret. This key is stored securely in the Oracle and Oracle GoldenGate domains. Only a party that has possession of the shared secret can decrypt the table and redo log keys.

The encrypted keys are delivered to the Extract process by means of built-in PL/SQL code. Extract uses the shared secret to decrypt the data. Extract never handles the wallet master key itself, nor is it aware of the master key password. Those remain within the Oracle database security framework.

Extract never writes the decrypted data to any file other than a trail file, not even a discard file (specified with the DISCARDFILE parameter). The word "ENCRYPTED" will be written to any discard file that is in use.

The impact of this feature on Oracle GoldenGate performance should mirror that of the impact of decryption on database performance. Other than a slight increase in Extract startup time, there should be a minimal affect on performance from replicating TDE data.

11.1.2 Requirements for Capturing TDE in Classic Capture Mode

The following are requirements for Extract to support TDE capture:

  • To maintain high security standards, the Oracle GoldenGate Extract process should run as part of the oracle user (the user that runs the Oracle database). That way, the keys are protected in memory by the same privileges as the oracle user.

  • The Extract process must run on the same machine as the database installation.

  • Even if using TDE with a Hardware Security Module, you must use a software wallet. Instructions are provided in Section 11.1.4.3, "Oracle Security Officer Tasks" in the configuration steps for moving from an HSM-only to an HSM-plus-wallet configuration and configuring the sqlnet.ora file correctly.

  • Whenever the source database is upgraded, you must rekey the master key.

11.1.3 Required Database Patches for TDE Support

To support TDE on Oracle 11.2.0.2, refer to article 1557031.1 on the My Oracle Support website (https://support.oracle.com">>https://support.oracle.com).

11.1.4 Configuring Classic Capture for TDE Support

The following outlines the steps that the Oracle Security Officer and the Oracle GoldenGate Administrator take to establish communication between the Oracle server and the Extract process.

11.1.4.1 Agree on a Shared Secret that Meets Oracle Standards

Agree on a shared secret password that meets or exceeds Oracle password standards. This password must not be known by anyone else. For guidelines on creating secure passwords, see Oracle Database Security Guide.

11.1.4.2 Oracle DBA Tasks

  1. Log in to SQL*Plus as a user with the SYSDBA system privilege. For example:

    sqlplus sys/as sysdba
    Connected.
    Enter password: password
    
  2. Run the prvtclkm.plb file that is installed in the Oracle admin directory. The prvtclkm.plb file creates the DBMS_INTERNAL_CLKM PL/SQL package, which enables Oracle GoldenGate to extract encrypted data from an Oracle database.

    @?/app/oracle/product/orcl111/rdbms/admin/prvtclkm.plb
    
  3. Grant EXEC privilege on DBMS_INTERNAL_CLKM PL/SQL package to the Extract database user.

    GRANT EXECUTE ON DBMS_INTERNAL_CLKM TO psmith;
    
  4. Exit SQL*Plus.

11.1.4.3 Oracle Security Officer Tasks

  1. Oracle GoldenGate requires the use of a software wallet even with HSM. If you are currently using HSM-only mode, move to HSM-plus-wallet mode by taking the following steps:

    1. Change the sqlnet.ora file configuration as shown in the following example, where the wallet directory can be any location on disk that is accessible (rwx) by the owner of the Oracle database. This example shows a best-practice location, where my_db is the $ORACLE_SID.

      ENCRYPTION_WALLET_LOCATION=
       (SOURCE=(METHOD=HSM)(METHOD_DATA=
         (DIRECTORY=/etc/oracle/wallets/my_db)))
      
    2. Log in to orapki (or Wallet Manager) as the owner of the Oracle database, and create an auto-login wallet in the location that you specified in the sqlnet.ora file. When prompted for the wallet password, specify the same password as the HSM password (or HSM Connect String). These two passwords must be identical.

      cd /etc/oracle/wallets/my_db
      orapki wallet create -wallet . -auto_login[_local] 
      

      Note:

      The Oracle database owner must have full operating system privileges on the wallet.
    3. Add the following entry to the empty wallet to enable an 'auto-open' HSM:

      mkstore -wrl . -createEntry ORACLE.TDE.HSM.AUTOLOGIN non-empty-string 
      
  2. Create an entry named ORACLEGG in the wallet. ORACLEGG must be the name of this key. The password for this key must be the agreed-upon shared secret, but do not enter this password on the command line. Instead, wait to be prompted.

    mkstore -wrl ./ -createEntry ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG
    Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
    Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
    Your secret/Password is missing in the command line
    Enter your secret/Password: sharedsecret
    Re-enter your secret/Password: sharedsecret
    Enter wallet password: hsm/wallet_password
    
  3. Verify the ORACLEGG entry.

    mkstore -wrl . -list
    Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
    Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
    Enter wallet password: hsm/wallet_password
    Oracle Secret Store entries:
    ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG
    
  4. Log in to SQL*Plus as a user with the SYSDBA system privilege.

  5. Close and then re-open the wallet.

    SQL> alter system set encryption wallet close identified by "hsm/wallet_password";
    System altered.
    SQL> alter system set encryption wallet open identified by "hsm/wallet_password";
    System altered.
    

    This inserts the password into the auto-open wallet, so that no password is required to access encrypted data with the TDE master encryption key stored in HSM.

  6. Switch log files.

    alter system switch logfile;
    System altered.
    
  7. If this is an Oracle RAC environment and you are using copies of the wallet on each node, make the copies now and then reopen each wallet.

Note:

Oracle recommends using one wallet in a shared location, with synchronized access among all Oracle RAC nodes.

11.1.4.4 Oracle GoldenGate Administrator Tasks

  1. Run GGSCI.

  2. Issue the ENCRYPT PASSWORD command to encrypt the shared secret so that it is obfuscated within the Extract parameter file. This is a security requirement.

    ENCRYPT PASSWORD sharedsecret {AES128 | AES192 | AES256} ENCRYPTKEY keyname
    

    Where:

    • sharedsecret is the clear-text shared secret. This value is case-sensitive.

    • {AES128 | AES192 | AES256} specifies Advanced Encryption Standard (AES) encryption. Specify one of the values, which represents the desired key length.

    • keyname is the logical name of the encryption key in the ENCKEYS lookup file. Oracle GoldenGate uses this key to look up the actual key in the ENCKEYS file. To create a key and ENCKEYS file, see Administering Oracle GoldenGate for Windows and UNIX.

      Example:

      ENCRYPT PASSWORD sharedsecret AES256 ENCRYPTKEY mykey1
      
  3. In the Extract parameter file, use the DBOPTIONS parameter with the DECRYPTPASSWORD option. As input, supply the encrypted shared secret and the decryption key.

    DBOPTIONS DECRYPTPASSWORD sharedsecret {AES128 | AES192 | AES256} ENCRYPTKEY keyname
    

    Where:

    • sharedsecret is the encrypted shared secret.

    • {AES128 | AES192 | AES256} must be same value that was used for ENCRYPT PASSWORD.

    • keyname is the logical name of the encryption key in the ENCKEYS lookup file.

      Example:

      DBOPTIONS DECRYPTPASSWORD AACAAAAAAAAAAAIALCKDZIRHOJBHOJUH AES256 ENCRYPTKEY mykey1
      
  4. Log in to SQL*Plus as a user with the SYSDBA system privilege.

  5. Close and then re-open the wallet.

    SQL> alter system set encryption wallet close identified by "hsm/wallet_password";
    System altered.
    SQL> alter system set encryption wallet open identified by "hsm/wallet_password";
    System altered.
    

11.1.5 Recommendations for Maintaining Data Security after Decryption

Extract decrypts the TDE data and writes it to the trail as clear text. To maintain data security throughout the path to the target database, it is recommended that you also deploy Oracle GoldenGate security features to:

  • encrypt the data in the trails

  • encrypt the data in transit across TCP/IP

For more information, see Administering Oracle GoldenGate for Windows and UNIX.

11.1.6 Performing DDL while TDE Capture is Active

If DDL will ever be performed on a table that has column-level encryption, or if table keys will ever be re-keyed, you must either quiesce the table while the DDL is performed or enable Oracle GoldenGate DDL support. It is more practical to have the DDL environment active so that it is ready, because a re-key usually is a response to a security violation and must be performed immediately. To install the Oracle GoldenGate DDL environment, see Appendix D, "Installing Trigger-Based DDL Capture". To configure Oracle GoldenGate DDL support, see Chapter 13, "Configuring DDL Support". For tablespace-level encryption, the Oracle GoldenGate DDL support is not required.

11.1.7 Rekeying after a Database Upgrade

Whenever the source database is upgraded and Oracle GoldenGate is capturing TDE data, you must rekey the master key, and then restart the database and Extract. The commands to rekey the master key are:

alter system set encryption key identified by "mykey";

11.1.8 Updating the Oracle Shared Secret in the Parameter File

Use this procedure to update and encrypt the TDE shared secret within the Extract parameter file.

  1. Run GGSCI.

  2. Stop the Extract process.

    STOP EXTRACT group
    
  3. Modify the ORACLEGG entry in the Oracle wallet. ORACLEGG must remain the name of the key. For instructions, see Oracle Database Advanced Security Guide.

  4. Issue the ENCRYPT PASSWORD command to encrypt the new shared secret.

    ENCRYPT PASSWORD sharedsecret {AES128 | AES192 | AES256} ENCRYPTKEY keyname
    

    Where:

    • sharedsecret is the clear-text shared secret. This value is case-sensitive.

    • {AES128 | AES192 | AES256} specifies Advanced Encryption Standard (AES) encryption. Specify one of the values, which represents the desired key length.

    • keyname is the logical name of the encryption key in the ENCKEYS lookup file.

      Example:

      ENCRYPT PASSWORD sharedsecret AES256 ENCRYPTKEY mykey1
      
  5. In the Extract parameter file, use the DBOPTIONS parameter with the DECRYPTPASSWORD option. As input, supply the encrypted shared secret and the Oracle GoldenGate-generated or user-defined decryption key.

    DBOPTIONS DECRYPTPASSWORD sharedsecret {AES128 | AES192 | AES256} ENCRYPTKEY keyname
    

    Where:

    • sharedsecret is the encrypted shared secret.

    • {AES128 | AES192 | AES256} must be same value that was used for ENCRYPT PASSWORD.

    • keyname is the logical name of the encryption key in the ENCKEYS lookup file.

      Example:

      DBOPTIONS DECRYPTPASSWORD AACAAAAAAAAAAAIALCKDZIRHOJBHOJUH AES256 ENCRYPTKEY mykey1
      
  6. Log in to SQL*Plus as a user with the SYSDBA system privilege.

  7. Close and then re-open the wallet.

    SQL> alter system set encryption wallet close identified by "hsm/wallet_password";
    System altered.
    SQL> alter system set encryption wallet open identified by "hsm/wallet_password";
    System altered.
    
  8. Start Extract.

    START EXTRACT group
    

11.2 Using Classic Capture in an Oracle RAC Environment

The following general guidelines apply to Oracle RAC when Extract is operating in classic capture mode.

  • During operations, if the primary database instance against which Oracle GoldenGate is running stops or fails for any reason, Extract abends. To resume processing, you can restart the instance or mount the Oracle GoldenGate binaries to another node where the database is running and then restart the Oracle GoldenGate processes. Stop the Manager process on the original node before starting Oracle GoldenGate processes from another node.

  • Whenever the number of redo threads changes, the Extract group must be dropped and re-created. For the recommended procedure, see Administering Oracle GoldenGate for Windows and UNIX.

  • Extract ensures that transactions are written to the trail file in commit order, regardless of the RAC instance where the transaction originated. When Extract is capturing in archived-log-only mode, where one or more RAC instances may be idle, you may need to perform archive log switching on the idle nodes to ensure that operations from the active instances are recorded in the trail file in a timely manner. You can instruct the Oracle RDBMS to do this log archiving automatically at a preset interval by setting the archive_lag_target parameter. For example, to ensure that logs are archived every fifteen minutes, regardless of activity, you can issue the following command in all instances of the RAC system:

    SQL> alter system set archive_lag_target 900
    
  • To process the last transaction in a RAC cluster before shutting down Extract, insert a dummy record into a source table that Oracle GoldenGate is replicating, and then switch log files on all nodes. This updates the Extract checkpoint and confirms that all available archive logs can be read. It also confirms that all transactions in those archive logs are captured and written to the trail in the correct order.

The following table shows some Oracle GoldenGate parameters that are of specific benefit in Oracle RAC.

Table 11-1 Classic Extract Parameters for Oracle RAC

Parameter Description

THREADOPTIONS parameter with the INQUEUESIZE and OUTQUEUESIZE options

Sets the amount of data that Extract queues in memory before sending it to the target system. Tuning these parameters might increase Extract performance on Oracle RAC.

TRANLOGOPTIONS parameter with the PURGEORPHANEDTRANSACTIONS | NOPURGEORPHANEDTRANSACTIONS and TRANSCLEANUPFREQUENCY options

Controls how Extract handles orphaned transactions, which can occur when a node fails during a transaction and Extract cannot capture the rollback. Although the database performs the rollback on the failover node, the transaction would otherwise remain in the Extract transaction list indefinitely and prevent further checkpointing for the Extract thread that was processing the transaction. By default, Oracle GoldenGate purges these transactions from its list after they are confirmed as orphaned. This functionality can also be controlled on demand with the SEND EXTRACT command in GGSCI.


11.3 Mining ASM-stored Logs in Classic Capture Mode

This topic covers additional configuration requirements that apply when Oracle GoldenGate mines transaction logs that are stored in Oracle Automatic Storage Management (ASM).

11.3.1 Accessing the Transaction Logs in ASM

Extract must be configured to read logs that are stored in ASM. Depending on the database version, the following options are available:

11.3.1.1 Reading Transaction Logs Through the RDBMS

Use the TRANLOGOPTIONS parameter with the DBLOGREADER option in the Extract parameter file if the RDBMS is Oracle 11.1.0.7 or Oracle 11.2.0.2 or later 11g R2 versions.

An API is available in those releases (but not in Oracle 11g R1 versions) that uses the database server to access the redo and archive logs. When used, this API enables Extract to use a read buffer size of up to 4 MB in size. A larger buffer may improve the performance of Extract when redo rate is high. You can use the DBLOGREADERBUFSIZE option of TRANLOGOPTIONS to specify a buffer size.

Note:

DBLOGREADER also can be used when the redo and archive logs are on regular disk or on a raw device.

When using DBLOGREADER and using Oracle Data Vault, grant the DV_GOLDENGATE_REDO_ACCESS Role to the Extract database user in addition to the privileges that are listed in Chapter 4, "Establishing Oracle GoldenGate Credentials."

11.3.1.2 ASM Direct Connection

If the RDBMS version is not one of those listed in Section 11.3.1.1, "Reading Transaction Logs Through the RDBMS", do the following:

  1. Create a user for the Extract process to access the ASM instance directly. Assign this user SYS or SYSDBA privileges in the ASM instance. Oracle GoldenGate does not support using operating-system authentication for the ASM user. See Table 11-2 for additional details.

    Table 11-2 Extract Database Privileges — ASM Instance

    ASM password configurationFoot 1  Permitted user

    ASM instance and the database share a password file

    You can use the Oracle GoldenGate source database user if you grant that user SYSDBA, or you can use any other database user that has SYSDBA privileges.

    ASM instance and the source database have separate password files

    You can overwrite the ASM password file with the source database password file, understanding that this procedure changes the SYS password in the ASM instance to the value that is contained in the database password file, and it also grants ASM access to the other users in the database password file. Save a copy of the ASM file before overwriting it.


    Footnote 1 To view how the current ASM password file is configured, log on to the ASM instance and issue the following command in SQL*Plus:
    SQL> SELECT name, value FROM v$parameter
    WHERE name = 'remote_login_passwordfile';

  2. Add the ASM user credentials to the Oracle GoldenGate credential store by issuing the ALTER CREDENTIALSTORE command. See Reference for Oracle GoldenGate for Windows and UNIX for usage instructions and syntax.

  3. Specify the ASM login alias in the Extract parameter file by including the TRANLOGOPTIONS parameter with the ASMUSERALIAS option. For more information about TRANLOGOPTIONS, see Reference for Oracle GoldenGate for Windows and UNIX.

11.3.2 Ensuring ASM Connectivity

To ensure that the Oracle GoldenGate Extract process can connect to an ASM instance, list the ASM instance in the tnsnames.ora file. The recommended method for connecting to an ASM instance when Oracle GoldenGate is running on the database host machine is to use a bequeath (BEQ) protocol. The BEQ protocol does not require a listener. If you prefer to use the TCP/IP protocol, verify that the Oracle listener is listening for new connections to the ASM instance. The listener.ora file must contain an entry similar to the following.

SID_LIST_LISTENER_ASM =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ASM)
      (ORACLE_HOME = /u01/app/grid)
    (SID_NAME = +ASM1)
  )
)

Note:

A BEQ connection does not work when using a remote Extract configuration. Use TNSNAMES with the TCP/IP protocol.

11.4 Ensuring Data Availability for Classic Capture

To ensure the continuity and integrity of capture processing when Extract operates in classic capture mode, enable archive logging. The archive logs provide a secondary data source should the online logs recycle before Extract is finished with them. The archive logs for open transactions must be retained on the system in case Extract needs to recapture data from them to perform a recovery.

WARNING:

If you cannot enable archive logging, there is a high risk that you will need to completely resynchronize the source and target objects and reinstantiate replication should there be a failure that causes an Extract outage while transactions are still active. If you must operate this way, configure the online logs according to the following guidelines to retain enough data for Extract to capture what it needs before the online logs recycle. Allow for Extract backlogs caused by network outages and other external factors, as well as long-running transactions.

In a RAC configuration, Extract must have access to the online and archived logs for all nodes in the cluster, including the one where Oracle GoldenGate is installed.

11.4.1 Log Retention Requirements per Extract Recovery Mode

The following summarizes the different recovery modes that Extract might use and their log-retention requirements:

  • By default, the Bounded Recovery mode is in effect, and Extract requires access to the logs only as far back as twice the Bounded Recovery interval that is set with the BR parameter. This interval is an integral multiple of the standard Extract checkpoint interval, as controlled by the CHECKPOINTSECS parameter. These two parameters control the Oracle GoldenGate Bounded Recovery feature, which ensures that Extract can recover in-memory captured data after a failure, no matter how old the oldest open transaction was at the time of failure. For more information about Bounded Recovery, see Reference for Oracle GoldenGate for Windows and UNIX.

  • In the unlikely event that the Bounded Recovery mechanism fails when Extract attempts a recovery, Extract reverts to normal recovery mode and must have access to the archived log that contains the beginning of the oldest open transaction in memory at the time of failure and all logs thereafter.

11.4.2 Log Retention Options

Depending on the version of Oracle, there are different options for ensuring that the required logs are retained on the system.

11.4.2.1 Oracle Enterprise Edition 11g and Later

For these versions, Extract can be configured to work with Oracle Recovery Manager (RMAN) to retain the logs that Extract needs for recovery. You enable this feature when you issue the REGISTER EXTRACT command. See Chapter 14, "Creating Process Groups" for more information. To use this feature, the Extract database user must have the following privileges, in addition to the basic privileges listed in Chapter 4, "Establishing Oracle GoldenGate Credentials".

Table 11-3 Extract Database Privileges —Log Retention

Oracle EE version Privileges

11.1 and 11.2.0.1

  1. Run package to grant Oracle GoldenGate admin privilege.

    exec dbms_streams_auth.grant_admin_privilege('user')
    
  2. Grant the 'become user' privilege.

    grant become user to user;
    

11.2.0.3 and later

Run package to grant Oracle GoldenGate admin privilege.

exec dbms_goldengate_auth.grant_admin_privilege('user')

When log retention is enabled, Extract retains enough logs to perform a Bounded Recovery, but you can configure Extract to retain enough logs through RMAN for a normal recovery by using the TRANLOGOPTIONS parameter with the LOGRETENTION option set to SR. There also is an option to disable the use of RMAN log retention. Review the options of LOGRETENTION in the Reference for Oracle GoldenGate for Windows and UNIX before you configure Extract. If you set LOGRETENTION to DISABLED, see Section 11.4.3, "Determining How Much Data to Retain,".

Note:

To support RMAN log retention on Oracle RAC for Oracle versions prior to 11.2.0.3, you must download and install the database patch that is provided in BUGFIX 11879974 before you add the Extract groups.

The RMAN log retention feature creates an underlying (but non-functioning) Oracle Streams Capture process for each Extract group. The name of the Capture is based on the name of the associated Extract group. The log retention feature can operate concurrently with other local Oracle Streams installations. When you create an Extract group, the logs are retained from the current database SCN.

Note:

If the storage area is full, RMAN purges the archive logs even when needed by Extract. This limitation exists so that the requirements of Extract (and other Oracle replication components) do not interfere with the availability of redo to the database.

11.4.2.2 All Other Oracle Versions

For versions of Oracle other than Enterprise Edition, you must manage the log retention process with your preferred administrative tools. Follow the directions in Section 11.4.3, "Determining How Much Data to Retain".

11.4.3 Determining How Much Data to Retain

When managing log retention, try to ensure rapid access to the logs that Extract would require to perform a normal recovery (not a Bounded Recovery). See Section 11.4.1, "Log Retention Requirements per Extract Recovery Mode". If you must move the archives off the database system, the TRANLOGOPTIONS parameter provides a way to specify an alternate location. See Section 11.4.5, "Specifying the Archive Location".

The recommended retention period is at least 24 hours worth of transaction data, including both online and archived logs. To determine the oldest log that Extract might need at any given point, issue the SEND EXTRACT command with the SHOWTRANS option. You might need to do some testing to determine the best retention time given your data volume and business requirements.

If data that Extract needs during processing was not retained, either in online or archived logs, one of the following corrective actions might be required:

  • Alter Extract to capture from a later point in time for which log data is available (and accept possible data loss on the target).

  • Resynchronize the source and target data, and then start the Oracle GoldenGate environment over again.

11.4.4 Purging Log Archives

Make certain not to use backup or archive options that cause old archive files to be overwritten by new backups. Ideally, new backups should be separate files with different names from older ones. This ensures that if Extract looks for a particular log, it will still exist, and it also ensures that the data is available in case it is needed for a support case.

11.4.5 Specifying the Archive Location

If the archived logs reside somewhere other than the Oracle default directory, specify that directory with the ALTARCHIVELOGDEST option of the TRANLOGOPTIONS parameter in the Extract parameter file.

You might also need to use the ALTARCHIVEDLOGFORMAT option of TRANLOGOPTIONS if the format that is specified with the Oracle parameter LOG_ARCHIVE_FORMAT contains sub-directories. ALTARCHIVEDLOGFORMAT specifies an alternate format that removes the sub-directory from the path. For example, %T/log_%t_%s_%r.arc would be changed to log_%t_%s_%r.arc. As an alternative to using ALTARCHIVEDLOGFORMAT, you can create the sub-directory manually, and then move the log files to it.

11.4.6 Mounting Logs That are Stored on Other Platforms

If the online and archived redo logs are stored on a different platform from the one that Extract is built for, do the following:

11.5 Configuring Classic Capture in Archived Log Only Mode

You can configure Extract to read exclusively from the archived logs. This is known as Archived Log Only (ALO) mode. In this mode, Extract reads exclusively from archived logs that are stored in a specified location. ALO mode enables Extract to use production logs that are shipped to a secondary database (such as a standby) as the data source. The online logs are not used at all. Oracle GoldenGate connects to the secondary database to get metadata and other required data as needed. As an alternative, ALO mode is supported on the production system.

Note:

ALO mode is not compatible with Extract operating in integrated capture mode.

11.5.1 Limitations and Requirements for Using ALO Mode

Observe the following limitations and requirements when using Extract in ALO mode.

  • Log resets (RESETLOG) cannot be done on the source database after the standby database is created.

  • ALO cannot be used on a standby database if the production system is Oracle RAC and the standby database is non-RAC. In addition to both systems being Oracle RAC, the number of nodes on each system must be identical.

  • ALO on Oracle RAC requires a dedicated connection to the source server. If that connection is lost, Oracle GoldenGate processing will stop.

  • It is a best practice to use separate archive log directories when using Oracle GoldenGate for Oracle RAC in ALO mode. This will avoid any possibility of the same file name showing up twice, which could result in Extract returning an "out of order scn" error.

  • The LOGRETENTION parameter defaults to DISABLED when Extract is in ALO mode. You can override this with a specific LOGRETENTION setting, if needed.

11.5.2 Configuring Extract for ALO mode

To configure Extract for ALO mode, follow these steps as part of the overall process for configuring Oracle GoldenGate, as documented in Chapter 8, "Configuring Capture in Classic Mode".

  1. Enable supplemental logging at the table level and the database level for the tables in the source database. (See Section 3.2, "Configuring Logging Properties".)

  2. When Oracle GoldenGate is running on a different server from the source database, make certain that SQL*Net is configured properly to connect to a remote server, such as providing the correct entries in a TNSNAMES file. Extract must have permission to maintain a SQL*Net connection to the source database.

  3. Use a SQL*Net connect string for the name of the user in the credential store that is assigned to the process. Specify the alias of this user in the following:

    • The USERIDALIAS parameter in the parameter file of every Oracle GoldenGate process that connects to that database.

    • The USERIDALIAS portion of the DBLOGIN command in GGSCI.

    Note:

    If you have a standby server that is local to the server that Oracle GoldenGate is running on, you do not need to use a connect string for the user specified in USERIDALIAS. You can just supply the user login name.

    See Administering Oracle GoldenGate for Windows and UNIX for more information about using a credential store.

  4. Use the Extract parameter TRANLOGOPTIONS with the ARCHIVEDLOGONLY option. This option forces Extract to operate in ALO mode against a primary or logical standby database, as determined by a value of PRIMARY or LOGICAL STANDBY in the db_role column of the v$database view. The default is to read the online logs. TRANLOGOPTIONS with ARCHIVEDLOGONLY is not needed if using ALO mode against a physical standby database, as determined by a value of PHYSICAL STANDBY in the db_role column of v$database. Extract automatically operates in ALO mode if it detects that the database is a physical standby.

  5. Other TRANLOGOPTIONS options might be required for your environment. For example, depending on the copy program that you use, you might need to use the COMPLETEARCHIVEDLOGONLY option to prevent Extract errors.

  6. Use the MAP parameter for Extract to map the table names to the source object IDs. For more information, see Reference for Oracle GoldenGate for Windows and UNIX.

  7. Add the Extract group by issuing the ADD EXTRACT command with a timestamp as the BEGIN option, or by using ADD EXTRACT with the SEQNO and RBA options. It is best to give Extract a known start point at which to begin extracting data, rather than by using the NOW argument. The start time of NOW corresponds to the time of the current online redo log, but an ALO Extract cannot read the online logs, so it must wait for that log to be archived when Oracle switches logs. The timing of the switch depends on the size of the redo logs and the volume of database activity, so there might be a lag between when you start Extract and when data starts being captured. This can happen in both regular and RAC database configurations.

11.6 Configuring Classic Capture in Oracle Active Data Guard Only Mode

You can configure classic Extract to access both redo data and metadata in real-time to successfully replicate source database activities using Oracle Active Data Guard. This is known as Active Data Guard (ADG) mode. ADG mode enables Extract to use production logs that are shipped to a standby database as the data source. The online logs are not used as all. Oracle GoldenGate connects to the standby database to get metadata and other required data as needed.

This mode is useful in load sensitive environments where ADG is already in place or can be implemented. It can also be used as cost effective method to implement high availability using the ADG Broker role planned (switchover) and failover (unplanned) changes. In an ADG configuration, switchover and failover are considered roles. When either of the operations occur, it is considered a role change. For more information, see Oracle Data Guard Concepts and Administration and Oracle Data Guard Broker.

11.6.1 Limitations and Requirements for Using ADG Mode

Observe the following limitations and requirements when using Extract in ADG mode.

  • Extract in ADG mode will only apply redo data that has been applied to the standby database by the apply process. If Extract runs ahead of the standby database, it will wait for the standby database to catch up.

  • You must explicitly specify ADG mode in your classic Extract parameter file to run extract on the standby database.

  • You must specify the database user and password to connect to the ADG system because fetch and other metadata resolution occurs in the database.

  • The number of redo threads in the standby logs in the standby database must match the number of nodes from the primary database.

  • No new RAC instance can be added to the primary database after classic Extract has been created on the standby database. If you do add new instances, the redo data from the new thread will not be captured by classic Extract.

  • Archived logs and standby redo logs accessed from the standby database will be an exact duplicate of the primary database. The size and the contents will match, including redo data, transactional data, and supplemental data. This is guaranteed by a properly configured ADG deployment.

  • ADG role changes are infrequent and require user intervention in both cases.

  • With a switchover, there will be an indicator in the redo log file header (end of the redo log or EOR marker) to indicate end of log stream so that classic Extract on the standby can complete the RAC coordination successfully and ship all of the committed transactions to the trail file.

  • With a failover, a new incarnation is created on both the primary and the standby databases with a new incarnation ID, RESETLOG sequence number, and SCN value.

  • You must connect to the primary database from GGSCI to add TRANDATA or SCHEMATRANDATA because this is done on the primary database.

  • DDL triggers cannot be used on the standby database, in order to support DDL replication (except ADDTRANDATA). You must install the Oracle GoldenGate DDL package on the primary database.

  • DDL ADDTRANDATA is not supported in ADG mode; you must use ADDSCHEMATRANDATA for DDL replication.

  • When adding extract on the standby database, you must specify the starting position using a specific SCN value, timestamp, or log position. Relative timestamp values, such as NOW, become ambiguous and may lead to data inconsistency.

  • When adding extract on the standby database, you must specify the number of threads that will include all of the relevant threads from the primary database.

  • During or after failover or switchover, no thread can be added or dropped from either primary or standby databases.

  • Classic Extract will only use one intervening RESETLOG operation.

  • If you do not want to relocate your Oracle GoldenGate installation, then you must position it in a shared space where the Oracle GoldenGate installation directory can be accessed from both the primary and standby databases.

  • If you are moving capture off of an ADG standby database to a primary database, then you must point your net alias to the primary database and you must remove the TRANLOG options.

  • Only Oracle Database releases that are running with compatibility setting of 10.2 or higher (10g Release 2) are supported.

  • Classic Extract cannot use the DBLOGREADER option. Use ASMUSER (there is approximately a 20gb/hr read limit) or move the online and archive logs outside of the Application Security Manager on both the primary and the standby databases.

11.6.2 Configuring Extract for ADG Mode

To configure Extract for ADG mode, follow these steps as part of the overall process for configuring Oracle GoldenGate, as documented in Chapter 8, "Configuring Capture in Classic Mode."

  1. Enable supplemental logging at the table level and the database level for the tables in the primary database using the ADD SCHEMATRANDATA parameter. If necessary, create a DDL capture. (See Section 3.2, "Configuring Logging Properties".)

  2. When Oracle GoldenGate is running on a different server from the source database, make certain that SQL*Net is configured properly to connect to a remote server, such as providing the correct entries in a TNSNAMES file. Extract must have permission to maintain a SQL*Net connection to the source database.

  3. On the standby database, use the Extract parameter TRANLOGOPTIONS with the MINEFROMACTIVEDG option. This option forces Extract to operate in ADG mode against a standby database, as determined by a value of PRIMARY or LOGICAL STANDBY in the db_role column of the v$database view.

    Other TRANLOGOPTIONS options might be required for your environment. For example, depending on the copy program that you use, you might need to use the COMPLETEARCHIVEDLOGONLY option to prevent Extract errors.

  4. On the standby database, add the Extract group by issuing the ADD EXTRACT command specifying the number of threads active on the primary database at the given SCN. The timing of the switch depends on the size of the redo logs and the volume of database activity, so there might be a limited lag between when you start Extract and when data starts being captured. This can happen in both regular and RAC database configurations.

11.6.3 Migrating Classic Extract To and From an ADG Database

You must have your parameter files, checkpoint files, bounded recovery files, and trail files are stored in shared storage or copied to the ADG database before attempting to migrate a classic Extract to or from an ADG database. Additionally, you must ensure that there has not been any intervening role change or Extract will be mining the same branch of redo.

Use the following steps to move to an ADG database:

  1. Edit the parameter file ext1.prm to add the following parameters:

    DBLOGIN USERID userid@ADG PASSWORD password
    TRANLOGOPTIONS MINEFROMACTIVEDG
    
  2. Start Extract by issuing the START EXTRACT ext1 command.

Use the following steps to move from an ADG database:

  1. Edit the parameter file ext1.prm to remove the following parameters:

    DBLOGIN USERID userid@ADG PASSWORD password
    TRANLOGOPTIONS MINEFROMACTIVEDG
    
  2. Start Extract by issuing the START EXTRACT ext1 command.

11.6.4 Handling Role Changes In an ADG Configuration

In a role change involving a standby database, all sessions in the primary and the standby database are first disconnected including the connections used by Extract. Then both databases are shut down, then the original primary is mounted as a standby database, and the original standby is opened as the primary database.

The procedure for a role change is determined by the initial deployment of Classic Extract and the deployment relation that you want, database or role. The following table outlines the four possible role changes and is predicated on an ADG configuration comprised of two databases, prisys and stansys. The prisys system contains the primary database and the stansys system contains the standby database; prisys has two redo threads active, whereas stansys has four redo threads active.

Initial Deployment Primary (prisys) Initial Deployment ADG (stansys)
Original Deployment:
ext1.prm
DBLOGIN USERID userid@prisys, PASSWORD password
ext1.prm
DBLOGIN USERID userid@stansys, PASSWORD password
TRANLOGOPTIONS MINEFROMACTIVEDG
Database Related:
After Role Transition: Classic Extract to ADG
  1. Edit the ext1.prm file to add:

    TRANLOGOPTIONS MINEFROMACTIVEDG

  2. If a failover, add TRANLOGOPTIONS USEPREVRESETLOGSID.

  3. Start Extract:

    START EXTRACT ext1

    Extract will abend once it reaches the role transition point, then it does an internal BR_RESET and moves both the I/O checkpoint and current checkpoint to SCN s.

  4. If failover, edit the parameter file again and remove:

    TRANLOGOPTIONS USEPREVRESETLOGSID

  5. Execute ALTER EXTRACT ext1 SCN #, where # is the SCN value from role switch message.

  6. Based on the thread counts, do one of the following:

    If the thread counts are same between the databases, then execute the START EXTRACT ext1; command.

    or

    If thread counts are different between the databases, then execute the following commands:

    DROP EXTRACT ext1

    ADD EXTRACT ext1 THREADS t BEGIN SCN s

    START EXTRACT ext1

After Role Transition: ADG to classic Extract
  1. Edit ext1.prm and remove:

    TRANLOGOPTIONS MINEFROMACTIVEDG

  2. If a failover, add TRANLOGOPTIONS USEPREVRESETLOGSID.

  3. Start Extract:

    START EXTRACT ext1

    Extract will abend once it reaches the role transition point, then it does an internal BR_RESET and moves both the I/O checkpoint and current checkpoint to SCN s.

  4. If failover, edit the parameter file again and remove:

    TRANLOGOPTIONS USEPREVRESETLOGSID

  5. Execute ALTER EXTRACT ext1 SCN #, where # is the SCN value from role switch message.

  6. Based on the thread counts, do one of the following:

    If the thread counts are same between the databases, then execute the START EXTRACT ext1; command.

    or

    If thread counts are different between the databases, then execute the following commands:

    DROP EXTRACT ext1

    ADD EXTRACT ext1 THREADS t BEGIN SCN s

    START EXTRACT ext1

Role Related:
After Role Transition: Classic Extract to classic Extract
  1. Edit ext1.prm to change the database system to the standby system:

    DBLOGIN USERID userid@stansys, PASSWORD password

  2. If a failover, add TRANLOGOPTIONS USEPREVRESETLOGSID.

  3. Start Extract:

    START EXTRACT ext1

    Extract will abend once it reaches the role transition point, then it does an internal BR_RESET and moves both the I/O checkpoint and current checkpoint to SCN s.

  4. If failover, edit the parameter file again and remove:

    TRANLOGOPTIONS USEPREVRESETLOGSID

  5. Execute ALTER EXTRACT ext1 SCN #, where# is the SCN value from role switch message.

  6. Based on the thread counts, do one of the following:

    If the thread counts are same between the databases, then execute the START EXTRACT ext1; command.

    or

    If thread counts are different between the databases, then execute the following commands:

    DROP EXTRACT ext1

    ADD EXTRACT ext1 THREADS t BEGIN SCN s

    START EXTRACT ext1

After Role Transition: ADG to ADG
  1. Edit ext1.prm to change the database system to the primary system:

    DBLOGIN USERID userid@prisys, PASSWORD password

  2. If a failover, add TRANLOGOPTIONS USEPREVRESETLOGSID.

  3. Start Extract:

    START EXTRACT ext1

    Extract will abend once it reaches the role transition point, then it does an internal BR_RESET and moves both the I/O checkpoint and current checkpoint to SCN s.

  4. If failover, edit the parameter file again and remove:

    TRANLOGOPTIONS USEPREVRESETLOGSID

  5. Execute ALTER EXTRACT ext1 SCN #, where# is the SCN value from role switch message.

  6. Based on the thread counts, do one of the following:

    If the thread counts are same between the databases, then execute the START EXTRACT ext1; command.

    or

    If thread counts are different between the databases, then execute the following commands:

    DROP EXTRACT ext1

    ADD EXTRACT ext1 THREADS t BEGIN SCN s

    START EXTRACT ext1


11.7 Avoiding Log-read Bottlenecks in Classic Capture

When Oracle GoldenGate captures data from the redo logs, I/O bottlenecks can occur because Extract is reading the same files that are being written by the database logging mechanism. Performance degradation increases with the number of Extract processes that read the same logs. You can:

  • Try using faster drives and a faster controller. Both Extract and the database logging mechanism will be faster on a faster I/O system.

  • Store the logs on RAID 0+1. Avoid RAID 5, which performs checksums on every block written and is not a good choice for high levels of continuous I/O.