Instantiating and Starting Oracle GoldenGate Replication

About the Instantiation Process

During the initialization of the Oracle GoldenGate environment, you will be doing an initial data synchronization and starting the Oracle GoldenGate processes for the first time. In conjunction with those procedures, you will be creating the process groups for which you created parameter files in Configuring Oracle GoldenGate for DB2 for i.

To create an Extract process group, an initial start position for data capture must be established. This initial position will be based on a transaction boundary that is based on either of the following:

a timestamp
the end of the journal(s)
A specific system sequence number
A specific sequence number in the journal(s)

When Extract starts for the first time to begin capturing data, it captures all of the transaction data that it encounters after the specified start point, but none of the data that occurred before that point. To ensure that Extract does not start in the middle of ongoing transactions that would be discarded, set the tables that are to be captured to an inactive state. You can either put the system into a restricted state by using the ALCOBJ command to lock the objects or libraries, or you can force all of the current transactions on those tables to stop at a certain point.

After initialization is complete, remember to unlock any objects that you locked. To do so, log off of the session that locked the objects or use the DLCOBJ command from the OS/400 command line.

Parent topic: Instantiating and Starting Oracle GoldenGate Replication

Overview of Basic Oracle GoldenGate Instantiation Steps

These instructions show you how to instantiate the basic replication environment that you configured in Chapter 4. These steps are:

Parent topic: Instantiating and Starting Oracle GoldenGate Replication

Satisfying Prerequisites for Instantiation

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

Parent topic: Instantiating and Starting Oracle GoldenGate Replication

Configure Change Capture and Delivery

By the time you are ready to instantiate the replication environment, all of your Extract and Replicat process groups must be configured with completed parameter files as directed in "Configuring Oracle GoldenGate for DB2 for i".

In addition, all of the other setup requirements in this manual must be satisfied.

Parent topic: Satisfying Prerequisites for Instantiation

Add Collision Handling

If the source database will remain active during the initial load, 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:

INSERT operations for which the row already exists.
UPDATE and DELETE operations for which the row does not exist.

For more information about this parameter, see the Oracle GoldenGate Windows and UNIX Reference Guide.

Parent topic: Satisfying Prerequisites for Instantiation

Prepare 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.
Constraints: If you have not done so already, disable foreign-key constraints and check constraints. Foreign-key constraints can cause errors, and check constraints can slow down the loading process.
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 significantly. You can add back the indexes after the load is finished.
Keys: 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's purposes. If you cannot create keys, the affected source table must be quiesced for the load.

Parent topic: Satisfying Prerequisites for Instantiation

Making the Instantiation Procedure More Efficient

The following are some suggestions for making the instantiation process move more efficiently.

Parent topic: Instantiating and Starting Oracle GoldenGate Replication

Share Parameters Between Process Groups

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.

Parent topic: Making the Instantiation Procedure More Efficient

Use Parallel Processes

You can configure parallel initial-load processes to perform the initial load more quickly. It is important to keep tables with foreign-key relationships within the same set of processes. You can isolate large tables from smaller ones by using different sets of processes, or simply apportion the load across any number of process sets. To configure parallel processes correctly, see Administering Oracle GoldenGate for Windows and UNIX.

Parent topic: Making the Instantiation Procedure More Efficient

Configuring the Initial Load

Oracle GoldenGate supports the following load methods specifically for Oracle:

Parent topic: Instantiating and Starting Oracle GoldenGate Replication

Configuring an Initial Load from File to Replicat

Description of initsyncreplicat.jpg follows

Description of the illustration initsyncreplicat.jpg

To use Replicat to establish the target data, you use an initial-load Extract to extract source records from the source tables and write them to an extract file in canonical format. From the file, an initial-load Replicat loads the data using the database interface. During the load, the change-synchronization groups extract and replicate incremental changes, which are then reconciled with the results of the load.

During the load, the records are applied to the target database one record at a time, so this method may be considerably slower than using a native DB2 for i load utility. This method permits data transformation to be done on either the source or target system.

To Configure a Load from File to Replicat

On the source and target systems, run GGSCI and start Manager.
```
START MANAGER
```
On the source system, issue the following command to create an initial-load Extract parameter file. This Extract should have a different name from the Extract groups that capture the transactional data.
```
EDIT PARAMS initial-load Extract name
```

Enter the parameters listed in the following table in the order shown, starting a new line for each parameter statement.

Parameter	Description
SOURCEISTABLE	Designates Extract as an initial load process extracting records directly from the source tables.
SOURCEDB `database` USERID `user id`, PASSWORD `password`, BLOWFISH ENCRYPTKEY `keyname`	Specifies database connection information. `SOURCEDB` specifies the name of the source database. `USERID` specifies the Extract database user profile. `PASSWORD` specifies the user's password that was encrypted with the `ENCRYPT PASSWORD` command (see "Encrypting the Extract and Replicat Passwords"). Enter or paste the encrypted password after the `PASSWORD` keyword. `BLOWFISH ENCRYPTKEY` `keyname` specifies the name of the lookup key in the local `ENCKEYS` file.
RMTHOST `hostname`, MGRPORT `portnumber`,[`encryption options`]	`RMTHOST` specifies the name or IP address of the target system. `MGRPORT` specifies the port number where Manager is running on the target. `encryption options` specifies optional encryption of data across TCP/IP. For additional options and encryption details, see Reference for Oracle GoldenGate for Windows and UNIX.
ENCRYPTTRAIL BLOWFISH KEYNAME `keyname`	Encrypts the remote file with Blowfish encryption. For more information about security, see Administering Oracle GoldenGate for Windows and UNIX.
RMTFILE `path name`, [MEGABYTES `n`] `path name` is the relative or fully qualified name of the file. `MEGABYTES` designates the size of each file.	Specifies the remote file to which the load data will be written. Oracle GoldenGate creates this file during the load. Note: The size of an extract file cannot exceed 2GB.
TABLE `owner.table`; `owner` is the library or schema name. `table` is the name of the table or a group of tables defined with wildcards. To exclude tables from a wildcard specification, use the `TABLEEXCLUDE` parameter.	Specifies a source table or tables for initial data extraction.

Enter any appropriate optional Extract parameters listed in Reference for Oracle GoldenGate for Windows and UNIX.
Save and close the parameter file.
On the target system, issue the following command to create an initial-load Replicat parameter file. This Replicat should have a different name from the Replicat group that applies the transactional data.
```
EDIT PARAMS initial-load Replicat name
```

Enter the parameters listed in Table 7-1 in the order shown, starting a new line for each parameter statement.

Table 7-1 Initial Load Replicat Parameters for Loading Data from File to Replicat

Parameter	Description
SPECIALRUN	Implements the initial-load Replicat as a one-time run that does not use checkpoints.
END RUNTIME	Directs the initial-load Replicat to terminate when the load is finished.
TARGETDB `database`, USERID `user` `id`, PASSWORD `pw`, `algorithm` ENCRYPTKEY `keyname`	Specifies database connection information. TARGETDB specifies the Data Source Name that is defined for the DB2 for i target database through the ODBC interface on the Windows or Linux system. `USERID` specifies the Replicat database user profile. `PASSWORD` specifies the user's password that was encrypted with the `ENCRYPT PASSWORD` command (see "Encrypting the Extract and Replicat Passwords"). Enter or paste the encrypted password after the `PASSWORD` keyword. `algorithm` `ENCRYPTKEY` `keyname` specifies the encryption method and keyname that was specified in the `ENCRYPT PASSWORD` command.
`DECRYPTTRAIL` `BLOWFISH` `KEYNAME` `keyname`	Decrypts the input trail. `BLOWFISH` is required because this is the algorithm that is supported to encrypt the file from DB2 for i.
EXTFILE `path name` \| EXTTRAIL `path name` `path name` is the relative or fully qualified name of the file or trail. Use `EXTTRAIL` only if you used the `MAXFILES` option of the `RMTFILE` parameter in the Extract parameter file.	Specifies the input extract file specified with the Extract parameter `RMTFILE`.
SOURCEDEFS `file name` \| ASSUMETARGETDEFS Use `SOURCEDEFS` if the source and target tables have different definitions. Specify the relative or fully qualified name of the source-definitions file generated by the `DEFGEN` utility. Use `ASSUMETARGETDEFS` if the source and target tables have the same definitions.	Specifies how to interpret data definitions.
MAP `owner.table`, TARGET `owner.table`; `owner` is the schema name. `table` is the name of a table or a wildcard definition for multiple tables. To exclude tables from a wildcard specification, use the `MAPEXCLUDE` parameter.	Specifies a relationship between a source and target table or tables.

Enter any appropriate optional Replicat parameters listed in the Reference for Oracle GoldenGate for Windows and UNIX.
Save and close the file.

Parent topic: Configuring the Initial Load

Configuring an initial load with a database utility

Description of initsync_copyutil.jpg follows

Description of the illustration initsync_copyutil.jpg

This graphic shows the parallel flows of the initial load and the ongoing capture and replication of transactional changes during the load period. The copy utility writes the data to a file, which is loaded to the target. Meanwhile, an Extract process captures change data and sends it to a trail on the target for Replicat to read and apply to the target.

For an initial load between two DB2 for i source and target systems, you can use the DB2 for i system utilities to establish the target data. To do this, you save the file(s) that you want to load to the target by using the SAVOBJ or SAVLIB commands, and then you restore them on the target using the RSTOBJ or RSTLIB commands.

Another alternative is to use the DB2 for i commands CPYTOIMPF (Copy to Import File) and CPYFRMIMPF (Copy from Import File) to create files that can be used with the bulk load utilities of other databases. See the DB2 for i Information Center documentation for more details on "Copying between different systems."

In both cases, no special configuration of any Oracle GoldenGate initial-load processes is needed. You use the change-synchronization process groups that you configured in Configuring Oracle GoldenGate for DB2 for i. You start a change-synchronization Extract group to extract ongoing data changes while you are making the copy and loading it. When the copy is finished, you start the change-synchronization Replicat group to re-synchronize rows that were changed while the copy was being applied. From that point forward, both Extract and Replicat continue running to maintain data synchronization. See "Adding Change-Capture and Change-Delivery processes".

Parent topic: Configuring the Initial Load

Adding Change-Capture and Change-Delivery processes

Note:

Perform these steps at or close to the time that you are ready to start the initial load and change capture.

These steps establish the Oracle GoldenGate Extract, data pump, and Replicat processes that you configured in Configuring Oracle GoldenGate for DB2 for i. Collectively known as the "change-synchronization" processes, these are the processes that:

capture and apply ongoing source changes while the load is being performed on the target
reconcile any collisions that occur

Note:

Perform these steps as close as possible to the time that you plan to start the initial load processes. You will start these processes during the initial load steps.

Parent topic: Instantiating and Starting Oracle GoldenGate Replication

Add the Primary Extract

These steps add the primary Extract that captures change data.

Parent topic: Adding Change-Capture and Change-Delivery processes

Understanding the Primary Extract Start Point

When you add the primary Extract group, you establish an initial start position for data capture. This initial position can be a transaction boundary that is based on either of the following:

a timestamp
the end of the journal(s)
a specific system sequence number
a specific journal sequence number (per journal)

The options that are available to you assume a global start point and optional journal-specific start points.

To position by a timestamp, at the end of the journals, or at a system sequence number, you will use the ADD EXTRACT command with the appropriate option. This command establishes a global start point for all journals and is a required first step.
After issuing the ADD EXTRACT command, you can then optionally position any specific journal at a specific journal sequence number by using the ALTER EXTRACT command with an appropriate journal option.

Parent topic: Add the Primary Extract

Establishing the Required and Optional Extract Start Points

These steps include the ADD EXTRACT and ALTER EXTRACT commands to enable you to establish your desired start points.

Run GGSCI.
Issue the ADD EXTRACT command to add the primary Extract group and establish the global start point.
```
ADD EXTRACT group name, TRANLOG
{
, BEGIN {NOW | yyyy-mm-dd[hh:mi:[ss[.cccccc]]]} |
, EOF |
, SEQNO seqno
}
```
Where:
- group name is the name of the primary Extract group that captures the transactional changes.
- TRANLOG specifies the journals as the data source.
- BEGIN specifies to begin capturing data as of a specific time. Select one of two options: NOW starts at the first record that is timestamped at the same time that BEGIN is issued. yyyy-mm-dd[hh:mi:[ss[.cccccc]]] starts at an explicit timestamp. Logs from this timestamp must be available.
- SEQNO seqno specifies to begin capturing data at, or just after, a system sequence number, which is a decimal number up to 20 digits in length.
(Optional) Issue the following command to alter any ADD EXTRACT start position to set the start position for a specific journal in the same Extract configuration. A specific journal position set with ALTER EXTRACT does not affect any global position that was previously set with ADD EXTRACT or ALTER EXTRACT; however a global position set with ALTER EXTRACT overrides any specific journal positions that were previously set in the same Extract configuration.
```
ALTER EXTRACT group name,
{
ALTER EXTRACT {BEGIN {NOW | yyyy-mm-dd [hh:mi:[ss[.cccccc]]] [JOURNAL journal_library/journal_name [[JRNRCV receiver_library/receiver_name]] | 
, EOF [JOURNAL journal_library/journal_name [[JRNRCV receiver_library/receiver_name]] |
, SEQNO seqno [JOURNAL journal_library/journal_name [[JRNRCV receiver_library/receiver_name]]
}
```
Note:

SEQNO, when used with a journal in ALTER EXTRACT, is the journal sequence number that is relative to that specific journal, not the system sequence number that is global across journals.

Example 7-1 Timestamp Start Point

ADD EXTRACT finance, TRANLOG, BEGIN 2011-01-01 12:00:00.000000

Example 7-2 NOW Start Point

ADD EXTRACT finance, TRANLOG, BEGIN NOW

Example 7-3 System Sequence Number Start Point

ADD EXTRACT finance, TRANLOG, SEQNO 2952

Example 7-4 Journal Start Point

ALTER EXTRACT finance, SEQNO 1234  JOURNAL accts/acctsjrn

Example 7-5 Journal and Receiver Start Point

ALTER EXTRACT finance, SEQNO 1234 JOURNAL accts/acctsjrn JRNRCV accts/jrnrcv0005

Parent topic: Add the Primary Extract

Add the Local Trail

This step adds the local trail to which the primary Extract writes captured data.

In GGSCI on the source system, issue the ADD EXTTRAIL command:

ADD EXTTRAIL pathname, EXTRACT group name

Where:

EXTTRAIL specifies that the trail is to be created on the local system.
pathname is the relative or fully qualified name of the trail, including the two-character name.
EXTRACT group name is the name of the primary Extract group.

Example 7-6

ADD EXTTRAIL /ggs/dirdat/lt, EXTRACT finance

Parent topic: Adding Change-Capture and Change-Delivery processes

Add the Data Pump Extract Group

This step adds the data pump that reads the local trail and sends the data to the target.

In GGSCI on the source system, issue the ADD EXTRACT command.

ADD EXTRACT group name, EXTTRAILSOURCE trail name

Where:

group name is the name of the data-pump Extract group.
EXTTRAILSOURCE trail name is the relative or fully qualified name of the local trail.

Example 7-7

ADD EXTRACT financep, EXTTRAILSOURCE c:\ggs\dirdat\lt

Parent topic: Adding Change-Capture and Change-Delivery processes

Add the Remote Trail

This step adds the remote trail. Although it is read by Replicat, this trail must be associated with the data pump, so it must be added on the source system, not the target.

In GGSCI on the source system, issue the following command:

ADD RMTTRAIL pathname, EXTRACT group name

Where:

RMTTRAIL specifies that the trail is to be created on the target system, and pathname is the relative or fully qualified name of the trail, including the two-character name.
EXTRACT group name is the name of the data-pump Extract group.

Example 7-8

ADD RMTTRAIL /ggs/dirdat/rt, EXTRACT financep

Parent topic: Adding Change-Capture and Change-Delivery processes

Add the Replicat Group

These steps add the Replicat group that reads the remote trail (which gets created automatically on the target) and applies the data changes to the target Oracle database.

Run GGSCI on the target system.
Issue the ADD REPLICAT command.
ADD REPLICAT group name, EXTTRAIL pathname

Where:
- group name is the name of the Replicat group.
- EXTTRAIL pathname is the relative or fully qualified name of the remote trail, including the two-character name.

Example 7-9

ADD REPLICAT financer, EXTTRAIL c:\ggs\dirdat\rt

Parent topic: Adding Change-Capture and Change-Delivery processes

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.

Parent topic: Instantiating and Starting Oracle GoldenGate Replication

To Perform Instantiation from File to Replicat

Make certain that you have addressed the requirements in Satisfying Prerequisites for Instantiation.
On the source and target systems, run GGSCI and start the Manager process.

START MANAGER
On the source system, start the primary and data pump Extract groups to start change extraction.
```
START EXTRACT primary Extract group name
START EXTRACT data pump Extract group name
```
From the directory where Oracle GoldenGate is installed on the source system, start the initial-load Extract as follows:
```
$ /GGS directory/extract paramfile dirprm/initial-load Extract name.prm reportfile path name
```
Where: initial-load Extract name is the name of the initial-load Extract that you used when creating the parameter file, and path name is the relative or fully qualified name of the Extract report file (by default the dirrpt sub-directory of the Oracle GoldenGate installation directory).
Verify the progress and results of the initial extraction by viewing the Extract report file using the operating system's standard method for viewing files.
Wait until the initial extraction is finished.
On the target system, start the initial-load Replicat.
```
$ /GGS directory/replicat paramfile dirprm/initial-load Replicat name.prm reportfile path name
```
Where: initial-load Replicat name is the name of the initial-load Replicat that you used when creating the parameter file, and path name is the relative or fully qualified name of the Replicat report file (by default the dirrpt sub-directory of the Oracle GoldenGate installation directory).
When the initial-load Replicat is finished running, verify the results by viewing the Replicat report file using the operating system's standard method for viewing files.
On the target system, start change replication.
```
START REPLICAT Replicat group name
```
On the target system, issue the following command to verify the status of change replication.
```
INFO REPLICAT Replicat group name
```
Continue to issue the INFO REPLICAT command until you have verified 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 point.
On the target system, issue the following command to turn off the HANDLECOLLISIONS parameter and disable the initial-load error handling.
```
SEND REPLICAT Replicat group name, NOHANDLECOLLISIONS
```
On the target system, edit the Replicat parameter file to remove the HANDLECOLLISIONS parameter. This prevents HANDLECOLLISIONS from being enabled again the next time Replicat starts.

Caution:

Do not use the VIEW PARAMS or EDIT PARAMS command to view or edit an existing parameter file that is in a character set other than that of the local operating system (such as one where the CHARSET option was used to specify a different character set). View the parameter file from outside GGSCI if this is the case; otherwise, the contents may become corrupted.
Save and close the parameter file.

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

Parent topic: Performing the Target Instantiation

To Perform Instantiation with a Database Utility

Make certain that you have addressed the requirements in "Satisfying Prerequisites for Instantiation".
On the source and target systems, run GGSCI and start the Manager process.
```
START MANAGER
```
On the source system, start the primary and data pump Extract groups to start change extraction.
```
START EXTRACT primary Extract group name
START EXTRACT data pump Extract group name
```
On the source system, start making the copy.
Wait until the copy is finished and record the time of completion.
View the Replicat parameter file to make certain that the HANDLECOLLISIONS parameter is listed. If not, edit the file and add the parameter to the file.
```
EDIT PARAMS Replicat group name
```
Note:

Do not use the VIEW PARAMS or EDIT PARAMS command to view or edit an existing parameter file that is in a character set other than that of the local operating system (such as one where the CHARSET option was used to specify a different character set). View the parameter file from outside GGSCI if this is the case; otherwise, the contents may become corrupted.
On the target system, start change replication.
```
START REPLICAT Replicat group name
```
On the target system, issue the following command to verify the status of change replication.
```
INFO REPLICAT Replicat group name
```
Continue to issue the INFO REPLICAT command until you have verified that change replication has posted all of the change data that was generated during the initial load. Reference the time of completion that you recorded. For example, if the copy stopped at 12:05, make sure change replication has posted data up to that point.
On the target system, issue the following command to turn off the HANDLECOLLISIONS parameter and disable the initial-load error handling.
```
SEND REPLICAT Replicat group name, NOHANDLECOLLISIONS
```
On the target system, edit the Replicat parameter file to remove the HANDLECOLLISIONS parameter. This prevents HANDLECOLLISIONS from being enabled again the next time Replicat starts.

Caution:

Do not use the VIEW PARAMS or EDIT PARAMS command to view or edit an existing parameter file that is in a character set other than that of the local operating system (such as one where the CHARSET option was used to specify a different character set). View the parameter file from outside GGSCI if this is the case; otherwise, the contents may become corrupted.
Save and close the parameter file.

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

Parent topic: Performing the Target Instantiation

Monitoring Processing after the Instantiation

After the target is instantiated and replication is in effect, you should view the status, lag, and overall health of the replication environment to ensure that processes are running properly, that there are no warnings in the Oracle GoldenGate error log, and that lag is at an acceptable level. You can view Oracle GoldenGate processes from:

GGSCI: For information about monitoring processes, see Administering Oracle GoldenGate for Windows and UNIX.
Oracle GoldenGate Monitor: See the administration documentation and online help for that product. Oracle GoldenGate Monitor provides a graphical-based monitoring environment for all of your Oracle GoldenGate instances.

You also should verify that capture and delivery is being performed for all of the tables in the Oracle GoldenGate configuration, and that the source and target data are synchronized. You can use the Oracle GoldenGate Veridata product for this purpose.

Parent topic: Instantiating and Starting Oracle GoldenGate Replication

Backing up Your 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. This directory contains critical sub-directories, files and programs. The most critical working files in this directory consume the vast majority of backup space; therefore it makes sense just to back up the entire installation directory for fast, simple recovery.

Parent topic: Instantiating and Starting Oracle GoldenGate Replication

Positioning Extract After Startup

You may at some point, over the life of an Extract run, need to set the position of Extract in the data stream manually. To reposition Extract, use the ALTER EXTRACT command in GGSCI. To help you identify any given Extract read position, the INFO EXTRACT command shows the positions for each journal in an Extract configuration, including the journal receiver information. For more information about these commands, see Reference for Oracle GoldenGate for Windows and UNIX.

Note:

Because the extract will be synchronizing all of the journals in the extract by system sequence number because it is possible for a transaction to be split across them, if a given journal is independently repositioned far into the past, the resulting latency from reprocessing the entries will cause the already-read journals to stall until the reading of the latent journal catches up.

Parent topic: Instantiating and Starting Oracle GoldenGate Replication