10 Additional Configuration Steps for Using Classic Capture

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 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.

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

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.

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.

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 Installing Trigger-Based DDL Capture. To configure Oracle GoldenGate DDL support, see Configuring DDL Support. For tablespace-level encryption, the Oracle GoldenGate DDL support is not required.

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";

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

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

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)
  )
)

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.

• 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.

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

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 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 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.

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.

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.

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

• NFS-mount the archive files.

• Map the file structure to the structure of the source system by using the LOGSOURCE and PATHMAP options of the Extract parameter TRANLOGOPTIONS. For more information, see Reference for Oracle GoldenGate.

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.

To configure Extract for ALO mode, follow these steps as part of the overall process for configuring Oracle GoldenGate, as documented in Configuring Capture in Classic 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.

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

You must have your parameter files, checkpoint files, bounded recovery files, and trail files 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 mine 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:

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.

ext1.prm
DBLOGIN USERID userid@prisys, PASSWORD password

ext1.prm
DBLOGIN USERID userid@stansys, PASSWORD password
TRANLOGOPTIONS MINEFROMACTIVEDG