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

This chapter includes the following sections:

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

15.2 Satisfying Prerequisites for Instantiation

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

15.2.1 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:

Chapter 7, "Configuring Capture in Integrated Mode"

Chapter 8, "Configuring Capture in Classic Mode"

Chapter 9, "Configuring Oracle GoldenGate Apply"

Chapter 14, "Creating Process Groups"

15.2.2 Disabling DDL Processing

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

15.2.3 Adding Collision Handling

This prerequisite applies to the following instantiation methods:

This prerequisite does not apply to the instantiation method described in Section 15.3.1, "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). For more information about HANDLECOLLISIONS, see Reference for Oracle GoldenGate for Windows and UNIX.

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. See Reference for Oracle GoldenGate for Windows and UNIX for more information about KEYCOLS.

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

15.3 Configuring the Initial Load

Oracle GoldenGate supports the following load methods specifically for Oracle:

Select a method and follow its configuration steps to create the load processes and parameter files.

Some of the parameters that you use in a change-synchronization parameter file also are required in an initial-load Extract and initial-load Replicat parameter file. To take advantage of the commonalities, you can use any of the following methods:

  • Copy common parameters from one parameter file to another.

  • Store the common parameters in a central file and use the OBEY parameter in each parameter file to retrieve them.

  • Create an Oracle GoldenGate macro for the common parameters and then call the macro from each parameter file with the MACRO parameter.

For more information about working with parameter files, see Administering Oracle GoldenGate for Windows and UNIX.

15.3.1 Configuring a Load with an Oracle Data Pump

Figure 15-1 Configuring a Load with the Oracle Data Pump Utility

Description of Figure 15-1 follows
Description of "Figure 15-1 Configuring a Load with the Oracle Data Pump Utility"

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.

15.3.2 Configuring a Direct Bulk Load to SQL*Loader

Figure 15-2 shows configuring a direct bulk load to SQL*Loader.

Figure 15-2 Configuring a Direct Bulk Load to SQL*Loader

Description of Figure 15-2 follows
Description of "Figure 15-2 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 Chapter 7, "Configuring Capture in Integrated Mode" or Chapter 8, "Configuring Capture in Classic Mode" and Chapter 9, "Configuring Oracle GoldenGate Apply" replicate incremental changes, which are then reconciled with the results of the load.

Limitations:

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. Refer to Table 15-1 for descriptions.

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

    Table 15-1 Initial-load Extract Parameters to Direct Bulk Load to SQL*Loader

    Parameter Description

    EXTRACT initial-load_Extract

    Specifies the name of the initial-load Extract, as stated with ADD EXTRACT. See Reference for Oracle GoldenGate for Windows and UNIX.

    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 Chapter 4, "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. See Reference for Oracle GoldenGate for Windows and UNIX.

    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. See Reference for Oracle GoldenGate for Windows and UNIX.

    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.

    See Administering Oracle GoldenGate for Windows and UNIX for important information about specifying object names.


  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. See Table 15-2 for descriptions.

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

    Table 15-2 Initial-load Replicat Parameters to Direct Bulk Load to SQL*Loader

    Parameter Description

    REPLICAT initial-load Replicat

    Specifies the name of the initial-load Replicat task, as stated with ADD REPLICAT. See Reference for Oracle GoldenGate for Windows and UNIX.

    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. For more information, see Chapter 4, "Establishing Oracle GoldenGate Credentials"

    BULKLOAD

    Directs Replicat to interface directly with the Oracle SQL*Loader interface. See Reference for Oracle GoldenGate for Windows and UNIX

    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. See Reference for Oracle GoldenGate for Windows and UNIX.

    For more information about data-definitions files, see Administering Oracle GoldenGate for Windows and UNIX.

    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.

    See Administering Oracle GoldenGate for Windows and UNIX for important information about specifying object names.


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

Figure 15-3 Configuring a Load from an Input File to SQL*Loader

Description of Figure 15-3 follows
Description of "Figure 15-3 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.

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. Refer to Table 15-3 for descriptions.

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

    Table 15-3 Initial-load Extract Parameters to Load From File to SQL*Loader

    Parameter Description

    SOURCEISTABLE

    Designates Extract as an initial load process that extracts records directly from the source tables. See Reference for Oracle GoldenGate for Windows and UNIX.

    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 Chapter 4, "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. See Reference for Oracle GoldenGate for Windows and UNIX.

    ENCRYPTTRAIL algorithm

    Encrypts the data in the remote file. For more information, see Reference for Oracle GoldenGate for Windows and UNIX.

    FORMATASCII, SQLLOADER

    Produces a fixed-length, ASCII-formatted remote file that is compatible with SQL*Loader. This parameter must be listed before RMTFILE. See Reference for Oracle GoldenGate for Windows and UNIX.

    RMTFILE path

    Specifies the absolute or full path name of an extract file that Extract creates and to which it writes the load data. See Reference for Oracle GoldenGate for Windows and UNIX.

    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.

    See Administering Oracle GoldenGate for Windows and UNIX for important information about specifying object names.


  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. See Table 15-4 for descriptions.

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

    Table 15-4 Initial-load Replicat parameters to Load from File to SQL*Loader

    Parameter Description

    GENLOADFILES template

    Generates run and control files for the database utility. See Reference for Oracle GoldenGate for Windows and UNIX.

    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. For more information, see Chapter 4, "Establishing Oracle GoldenGate Credentials"

    EXTFILE path

    Specifies the extract file that you specified with the Extract parameter RMTFILE. See Reference for Oracle GoldenGate for Windows and UNIX.

    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. See Reference for Oracle GoldenGate for Windows and UNIX.

    For more information about data-definitions files, see Administering Oracle GoldenGate for Windows and UNIX.

    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.

    See Administering Oracle GoldenGate for Windows and UNIX for important information about specifying object names.


  8. Save and close the parameter file.

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

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

15.4.2 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. For more information about this command, see Reference for Oracle GoldenGate for Windows and UNIX.

      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.

15.4.3 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. For more information about this command, see Reference for Oracle GoldenGate for Windows and UNIX.

      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.

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

Additionally, 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. For more information, see Oracle Database Reference.

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

Additionally, you can query the following views. For more information about this view, 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.

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

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