12 Instantiating Oracle GoldenGate Replication

This chapter contains instructions for configuring and performing an instantiation of the replication environment to establish and maintain a synchronized state between two or more databases. In a synchronized state, the source and target objects contain identical or appropriately corresponding values, depending on whether any conversion or transformation is performed on the data before applying it to the target objects.

Topics:

• Overview of the Instantiation Process
  In the instantiation procedure, you make a copy of the source data and load the copy to the target database.
• Prerequisites for Instantiation
  The following steps must be taken before starting any Oracle GoldenGate processes or native database load processes.
• Configuring the Initial Load
  Oracle GoldenGate supports theses load methods in this section specifically for Oracle Database.
• Performing the Target Instantiation
  This procedure instantiates the target tables while Oracle GoldenGate captures ongoing transactional changes on the source and stores them until they can be applied on the target.
• Monitoring and Controlling Processing After the Instantiation
  After the target is instantiated and replication is in effect, you can control processes and view the overall health of the replication environment.
• Verifying Synchronization
  To verify that the source and target data are synchronized, you can use the Oracle GoldenGate Veridata product or use your own scripts to select and compare source and target data.
• Backing up the Oracle GoldenGate Environment
  After you start Oracle GoldenGate processing, an effective backup routine is critical to preserving the state of processing in the event of a failure. Unless the Oracle GoldenGate working files can be restored, the entire replication environment must be re-instantiated, complete with new initial loads.

Overview of the Instantiation Process

In the instantiation procedure, you make a copy of the source data and load the copy to the target database.

The initial load captures a point-in-time snapshot of the data, while Oracle GoldenGate maintains that consistency by applying transactional changes that occur while the static data is being loaded. After instantiation is complete, Oracle GoldenGate maintains the synchronized state throughout ongoing transactional changes.

When you instantiate Oracle GoldenGate processing, it is recommended that you do so first in a test environment before deploying live on your production machines. This is especially important in an active-active or high availability configuration, where trusted source data may be touched by the replication processes. Testing enables you to find and resolve any configuration mistakes or data issues without the need to interrupt user activity for re-loads on the target or other troubleshooting activities. Testing also ensures that your instantiation process is configured properly. Parameter files can be copied to the production equipment after successful testing, and then you can perform a predictable instantiation with production data.

Prerequisites for Instantiation

The following steps must be taken before starting any Oracle GoldenGate processes or native database load processes.

• Configuring and Adding Change Synchronization Groups
  
• Disabling DDL Processing
  
• Adding Collision Handling
  
• Preparing the Target Tables
  

Configuring and Adding Change Synchronization Groups

To perform an instantiation of the target database and the replication environment, the online change capture and apply groups must exist and be properly configured. See:

• Configuring Capture in Integrated Mode

• Configuring Capture in Classic Mode

• Configuring Oracle GoldenGate Apply

• Creating Process Groups

Disabling DDL Processing

You must disable DDL activities before performing an instantiation. You can resume DDL after the instantiation is finished. See Disabling DDL Processing Temporarily for instructions.

Adding Collision Handling

This prerequisite applies to the following instantiation methods:

• Configuring a Direct Bulk Load to SQL*Loader

• Configuring a Load from an Input File to SQL*Loader

This prerequisite does not apply to the instantiation method described in Configuring a Load with an Oracle Data Pump.

If the source database will remain active during one of those initial load methods, collision-handling logic must be added to the Replicat parameter file. This logic handles conflicts that occur because static data is being loaded to the target tables while Oracle GoldenGate replicates transactional changes to those tables.

To handle collisions, add the HANDLECOLLISIONS parameter to the Replicat parameter file to resolve these collisions:

• INSERT operations for which the row already exists

• UPDATE and DELETE operations for which the row does not exist

HANDLECOLLISIONS should be removed from the Replicat parameter file at the end of the instantiation steps (as prompted in the instructions).

To use the HANDLECOLLISIONS function to reconcile incremental data changes with the load, each target table must have a primary or unique key. If you cannot create a key through your application, use the KEYCOLS option of the TABLE and MAP parameters to specify columns as a substitute key for Oracle GoldenGate to use. If you cannot create keys, the affected source table must be quiesced for the load.

Preparing the Target Tables

The following are suggestions that can make the load go faster and help you to avoid errors.

• Data: Make certain that the target tables are empty. Otherwise, there may be duplicate-row errors or conflicts between existing rows and rows that are being loaded.

• Indexes: Remove indexes from the target tables. Indexes are not necessary for the inserts performed by the initial load process and will slow it down. You can add back the indexes after the load is finished.

Configuring the Initial Load

Oracle GoldenGate supports theses load methods in this section specifically for Oracle Database.

Select a method and follow its configuration steps to create the load processes and parameter files. To work with parameter files, see Using Oracle GoldenGate Parameter Files in Administering Oracle GoldenGate.

• Configuring a Load with an Oracle Data Pump
  
• Configuring a Direct Bulk Load to SQL*Loader
  
• Configuring a Load from an Input File to SQL*Loader
  

Configuring a Load with an Oracle Data Pump

This method uses the Oracle Data Pump utility to establish the target data. You start Extract, the data pumps, and Replicat at the SCN at which the copy stopped. Transactions that were included in the copy are skipped to avoid collisions from integrity violations. From the process start point, Oracle GoldenGate maintains data synchronization.

No initial-load Oracle GoldenGate processes are required for this method.

Description of the illustration init_sync_copy_util.png

Configuring a Direct Bulk Load to SQL*Loader

With this method, you configure and run an Oracle GoldenGate initial-load Extract to extract complete source records and send them directly to an initial-load Replicat task. The initial-load Replicat task communicates with SQL*Loader to load data as a direct-path bulk load. Data mapping and transformation can be done by either the initial-load Extract or initial-load Replicat, or both. During the load, the change-synchronization groups that you configured in Configuring Capture in Integrated Mode or Configuring Capture in Classic Mode and Configuring Oracle GoldenGate Apply replicate incremental changes, which are then reconciled with the results of the load.

The following diagram shows configuring a direct bulk load to SQL*Loader.

Description of the illustration init-sync-dirbulk.png

Limitations:

• This method does not support extraction of LOB or LONG data. As an alternative, see Performing Instantiation From an Input File to SQL*Loader.

• This method does not support materialized views that contain LOBs, regardless of their size. It also does not support data encryption.

To Configure a Direct Bulk Load to SQL*Loader

1. Grant LOCK ANY TABLE to the Replicat database user on the target Oracle Database.
2. On the source and target systems, run GGSCI.
3. Start Manager on both systems.

   START MANAGER
   

4. On the source system, create the initial-load Extract.

   ADD EXTRACT initial-load_Extract, SOURCEISTABLE
   

   Where:

   • initial-load_Extract is the name of the initial-load Extract, up to eight characters.

   • SOURCEISTABLE directs Extract to read complete records directly from the source tables.

5. On the source system, create the initial-load Extract parameter file.

   EDIT PARAMS initial-load_Extract
   

6. Enter the initial-load Extract parameters in the order shown, starting a new line for each parameter statement. This example shows a three-part table name associated with a multitenant container database.

   EXTRACT initext
   USERIDALIAS tiger1
   RMTHOST fin1, MGRPORT 7809 ENCRYPT AES192, KEYNAME securekey2
   RMTTASK replicat, GROUP initrep
   TABLE hq.hr.*;

   Parameter	Description
   EXTRACT initial-load_Extract	Specifies the name of the initial-load Extract, as stated with ADD EXTRACT.
   USERIDALIAS 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
   RMTHOST hostname, MGRPORT portnumber[, ENCRYPT algorithm KEYNAME keyname]	Specifies the target system, the port where Manager is running, and optional encryption of data across TCP/IP.
   RMTTASK REPLICAT, GROUP initial-load_Replicat	Specifies the process type (must be REPLICAT) and the name of the initial-load Replicat. Directs Manager on the target system to dynamically start the initial-load Replicat as a one-time task.
   TABLE [container.]schema.table;	Specifies the tables to capture. container is the name of the pluggable database, if this is a multitenant container database. You can use the SOURCECATALOG parameter to specify a default pluggable database instead of using three-part names. schema is the schema name. table is the table name.

7. Save and close the file.
8. On the target system, create the initial-load Replicat.

   ADD REPLICAT initial-load Replicat, SPECIALRUN
   

   Where:

   • initial-load Replicat is the name of the initial-load Replicat task.

   • SPECIALRUN identifies the initial-load Replicat as a one-time task, not a continuous process.

9. On the target system, create the initial-load Replicat parameter file.

   EDIT PARAMS initial-load Replicat
   

10. Enter the initial-load Replicat parameters in the order shown, starting a new line for each parameter statement. This example shows a three-part source table name associated with a multitenant container database.

    REPLICAT initrep
    USERIDALIAS tiger2
    BULKLOAD
    ASSUMETARGETDEFS
    MAP hq.hr.*, TARGET hr2.*;
    

    Parameter	Description
    REPLICAT initial-load Replicat	Specifies the name of the initial-load Replicat task, as stated with ADD REPLICAT.
    USERIDALIAS alias	Specifies the alias of the database login credential that is assigned to Replicat. This credential must exist in the Oracle GoldenGate credential store.
    BULKLOAD	Directs Replicat to interface directly with the Oracle SQL*Loader interface.
    ASSUMETARGETDEFS	Assumes the source and target tables are identical, including semantics. If source and target definitions are different, you must create and specify a source-definitions file that both the change-synchronization and initial-load processes will use.
    MAP [container.]schema.table, TARGET schema.table;	Specifies a relationship between a source and target table or tables. If the source is a multitenant container database, container is the name of the pluggable database that contains the source objects specified with this MAP statement. You can use the SOURCECATALOG parameter to specify a default source pluggable database instead of using three-part names. schema is the schema name. table is the table name.

Configuring a Load from an Input File to SQL*Loader

With this method, an initial-load Extract extracts source records from the source tables and writes them to an extract file in external ASCII format. The files are read by SQL*Loader. During the load, the change-synchronization groups that you configured in Chapter 4 replicate incremental changes, which are then reconciled with the results of the load. As part of the load procedure, Oracle GoldenGate uses the initial-load Replicat to create run and control files required by the database utility. Any data transformation must be performed by the initial-load Extract on the source system because the control files are generated dynamically and cannot be pre-configured with transformation rules.

Description of the illustration init_sync_bulk.png

To Configure a Load from File to SQL*Loader

1. On the source and target systems, run GGSCI.
2. Start Manager on both systems.

   START MANAGER
   

3. On the source system, create the initial-load Extract parameter file.

   EDIT PARAMS initial-load Extract
   

4. Enter the initial-load Extract parameters in the order shown, starting a new line for each parameter statement. This example shows a three-part table name associated with a multitenant container database.

   SOURCEISTABLE
   USERIDALIAS tiger1
   RMTHOST fin1, MGRPORT 7809 ENCRYPT AES192, KEYNAME securekey2
   ENCRYPTTRAIL AES192
   FORMATASCII, SQLLOADER
   RMTFILE /ggs/dirdat/ie
   TABLE hq.hr.*;
    

   Parameter	Description
   SOURCEISTABLE	Designates Extract as an initial load process that extracts records directly from the source tables.
   USERIDALIAS alias	Specifies the alias of the database login credential that is assigned to Extract. This credential must exist in the Oracle GoldenGate credential store, see Establishing Oracle GoldenGate Credentials
   RMTHOST hostname, MGRPORT portnumber[, ENCRYPT algorithm KEYNAME keyname]	Specifies the target system, the port where Manager is running, and optional encryption of data across TCP/IP.
   ENCRYPTTRAIL algorithm	Encrypts the data in the remote file. For more information.
   FORMATASCII, SQLLOADER	Produces a fixed-length, ASCII-formatted remote file that is compatible with SQL*Loader. This parameter must be listed before RMTFILE.
   RMTFILE path	Specifies the absolute or full path name of an extract file that Extract creates and to which it writes the load data.
   TABLE [container.]schema.table;	Specifies the tables to capture. container is the name of the pluggable database, if this is a multitenant container database. You can use the SOURCECATALOG parameter to specify a default pluggable database instead of using three-part names. schema is the schema name. table is the table name.

5. Save and close the parameter file.
6. On the target system, create the initial-load Replicat parameter file.

   EDIT PARAMS initial-load Replicat
   

7. Enter the initial-load Replicat parameters in the order shown, starting a new line for each parameter statement. This example shows a three-part source table name associated with a multitenant container database.

   GENLOADFILES sqlldr.tpl 
   USERIDALIAS tiger2
   EXTFILE /ggs/dirdat/ie
   ASSUMETARGETDEFS
   MAP hq.hr.*, TARGET hr2.*;
    

   Parameter	Description
   GENLOADFILES template	Generates run and control files for the database utility.
   USERIDALIAS alias	Specifies the alias of the database login credential of the user that is assigned to Replicat. This credential must exist in the Oracle GoldenGate credential store, see Establishing Oracle GoldenGate Credentials
   EXTFILE path	Specifies the extract file that you specified with the Extract parameter RMTFILE.
   ASSUMETARGETDEFS	Assumes the source and target tables are identical, including semantics. If source and target definitions are different, you must create and specify a source-definitions file that both the change-synchronization and initial-load processes will use.
   MAP [container.]schema.table, TARGET schema.table;	Specifies a relationship between a source and target table or tables. If the source is a multitenant container database, container is the name of the pluggable database that contains the source objects specified with this MAP statement. You can use the SOURCECATALOG parameter to specify a default source pluggable database instead of using three-part names. schema is the schema name. table is the table name.

8. Save and close the parameter file.

Performing the Target Instantiation

This procedure instantiates the target tables while Oracle GoldenGate captures ongoing transactional changes on the source and stores them until they can be applied on the target.

By the time you perform the instantiation of the target tables, the entire Oracle GoldenGate environment should be configured for change capture and delivery, as should the initial-load processes if using Oracle GoldenGate as an initial-load utility.

Note:

The first time that Extract starts in a new Oracle GoldenGate configuration, any open source transactions will be skipped. Only transactions that begin after Extract starts are captured.

• Performing Instantiation with Oracle Data Pump
  
• Performing Instantiation with Direct Bulk Load to SQL*Loader
  
• Performing Instantiation From an Input File to SQL*Loader
  

Performing Instantiation with Oracle Data Pump

To perform instantiation with Oracle Data Pump, see My Oracle Support document 1276058.1. To obtain this document, do the following:

1. Go to http://support.oracle.com.
2. Under Sign In, select your language and then log in with your Oracle Single Sign-On (SSO).
3. On the Dashboard, expand the Knowledge Base heading.
4. Under Enter Search Terms, paste or type the document ID of 1276058.1 and then click Search.
5. In the search results, select Oracle GoldenGate Best Practices: Instantiation from an Oracle Source Database [Article ID 1276058.1].
6. Click the link under Attachments to open the article.

Performing Instantiation with Direct Bulk Load to SQL*Loader

1. On the source system, run GGSCI.
2. Start the primary change-capture Extract group.

   START EXTRACT group
   

3. Start the data-pump Extract group.

   START EXTRACT data_pump
   

4. If replicating sequence values:

   • Issue the DBLOGIN command with the alias of a user in the credential store who has EXECUTE privilege on update.Sequence.

     DBLOGIN USERIDALIAS alias
     

   • Issue the following command to update each source sequence and generate redo. From the redo, Replicat performs initial synchronization of the sequences on the target.

     FLUSH SEQUENCE [container.]schema.sequence
     

5. Start the initial-load Extract.

   START EXTRACT initial-load_Extract
   

   WARNING:

   Do not start the initial-load Replicat. The Manager process starts it automatically and terminates it when the load is finished.

6. On the target system, run GGSCI.
7. Issue the VIEW REPORT command to determine when the initial load to SQL*Loader is finished.

   VIEW REPORT initial-load_Extract
   

8. When the load is finished, start the change-data Replicat group.

   START REPLICAT group
   

9. Issue the INFO REPLICAT command, and continue to issue it until it shows that Replicat posted all of the change data that was generated during the initial load. For example, if the initial-load Extract stopped at 12:05, make sure Replicat posted data up to that time.

   INFO REPLICAT group
   

10. Turn off HANDLECOLLISIONS for the change-delivery Replicat to disable initial-load error handling.

    SEND REPLICAT group, NOHANDLECOLLISIONS
    

11. Edit the change-delivery Replicat parameter file to remove the HANDLECOLLISIONS parameter.

    EDIT PARAMS group
    

12. Save and close the parameter file.

From this point forward, Oracle GoldenGate continues to synchronize data changes.

Performing Instantiation From an Input File to SQL*Loader

Note:

The SQL*Loader method is not recommended if the data has multibyte characters, especially when the character set of the operating system is different from the database character set.

1. On the source system, run GGSCI.
2. Start the primary change-capture Extract group.

   START EXTRACT group
   

3. Start the data-pump Extract group.

   START EXTRACT data_pump

4. If replicating sequence values:

   • Issue the DBLOGIN command with the alias of a user in the credential store who has EXECUTE privilege on update.Sequence.

     DBLOGIN USERIDALIAS alias
     

   • Issue the following command to update each source sequence and generate redo. From the redo, Replicat performs initial synchronization of the sequences on the target.

     FLUSH SEQUENCE [container.]schema.sequence
     

5. From the Oracle GoldenGate installation directory on the source system, start the initial-load Extract from the command line of the operating system (not GGSCI).

   UNIX and Linux:

   $ /OGG_directory/extract paramfile dirprm/initial-load_Extract.prm reportfile path
   

   Windows:

   C:\> OGG_directory\extract paramfile dirprm\initial-load_Extract.prm reportfile path
   

   Where: initial-load_Extract is the name of the initial-load Extract and path is the relative or fully qualified path where you want the Extract report file to be created.

6. Wait until the initial extraction from the source is finished. Verify its progress and results by viewing the Extract report file from the command line.
7. On the target system, start the initial-load Replicat.

   UNIX and Linux:

   $ /OGG directory/replicat paramfile dirprm/initial-load_Replicat name.prm reportfile path
   

   Windows:

   C:\> OGG directory\replicat paramfile dirprm\initial-load_Replicat.prm reportfile path
   

   Where: initial-load Extract is the name of the initial-load Replicat and path is the relative or fully qualified path where you want the Replicat report file to be created.

8. When the initial-load Replicat stops, verify its results by viewing the Replicat report file from the command line.
9. Using the ASCII-formatted file and the run and control files that the initial-load Replicat created, load the data with SQL*Loader.
10. When the load is finished, start the change-delivery Replicat group.

    START REPLICAT group
    

11. Issue the INFO REPLICAT command, and continue to issue it until it shows that Replicat posted all of the change data that was generated during the initial load. For example, if the initial-load Extract stopped at 12:05, make sure Replicat posted data up to that time.

    INFO REPLICAT group
    

12. Turn off HANDLECOLLISIONS for the change-delivery Replicat to disable initial-load error handling.

    SEND REPLICAT group, NOHANDLECOLLISIONS
    

13. Edit the change-delivery Replicat parameter file to remove the HANDLECOLLISIONS parameter.

    EDIT PARAMS group
    

14. Save and close the parameter file.

From this point forward, Oracle GoldenGate continues to synchronize data changes.

Monitoring and Controlling Processing After the Instantiation

After the target is instantiated and replication is in effect, you can control processes and view the overall health of the replication environment.

If you configured Replicat in integrated mode, you can use the STATS REPLICAT command to view statistics on the number of transactions that are applied in integrated mode as compared to those that are applied in direct apply mode.

STATS REPLICAT group

The output of this command shows the number of transactions applied, the number of transactions that were redirected to direct apply, and the direct transaction ratio, among other statistics. The statistics help you determine whether integrated Replicat is performing as intended. If the environment is satisfactory and there is a high ratio of direct apply operations, consider using nonintegrated Replicat. You can configure parallelism with nonintegrated Replicat.

Note:

To ensure realistic statistics, view apply statistics only after you are certain that the Oracle GoldenGate environment is well established, that configuration errors are resolved, and that any anticipated processing errors are being handled properly.

You can also view runtime statistics for integrated Replicat in the V$views for each of the inbound server components.

• The reader statistics are recorded in V$GG_APPLY_READER and include statistics on number of messages read, memory used, and dependency counts.

• The apply coordinator statistics are recorded in V$GG_APPLY_COORDINATOR and record statistics at the transaction level.

• The apply server statistics are recorded in V$GG_APPLY_SERVER. This view records information for each of the apply server processes (controlled by parallelism and max_parallelism parameters) as separate rows. The statistics for each apply server are identified by the SERVER_ID column. If a SERVER_ID of 0 exists, this represents an aggregate of any apply servers that exited because the workload was reduced.

• Statistics about the number of messages received by the database from Replicat are recorded in the V$GG_APPLY_RECEIVER table.

To control processes, see Controlling Oracle GoldenGate Processes in Administering Oracle GoldenGate.

To ensure that all processes are running properly and that errors are being handled according to your error handling rules, see Handling Processing Errors in Administering Oracle GoldenGate. Oracle GoldenGate provides commands and logs to view process status, lag, warnings, and other information.

To know more about querying the following views, see Oracle Database Reference.

• V$GOLDENGATE_TABLE_STATS to see statistics for DML and collisions that occurred for each replicated table that the inbound server processed.

• V$GOLDENGATE_TRANSACTION to see information about transactions that are being processed by Oracle GoldenGate inbound servers.

Verifying Synchronization

To verify that the source and target data are synchronized, you can use the Oracle GoldenGate Veridata product or use your own scripts to select and compare source and target data.

Backing up the Oracle GoldenGate Environment

After you start Oracle GoldenGate processing, an effective backup routine is critical to preserving the state of processing in the event of a failure. Unless the Oracle GoldenGate working files can be restored, the entire replication environment must be re-instantiated, complete with new initial loads.

As a best practice, include the entire Oracle GoldenGate home installation in your backup routines. There are too many critical sub-directories, as well as files and programs at the root of the directory, to keep track of separately. In any event, the most critical files are those that consume the vast majority of backup space, and therefore it makes sense just to back up the entire installation directory for fast, simple recovery.