Shulou(Shulou.com)06/01 Report--

I have been confused about many parameters of oracle goldengate all the time. I happened to see this article in MOS and reproduced it to BLOG for reference.

Objective: This paper provides sample example of Oracle GoldenGate parameter files that can be used to configure Oracle GoldenGate replication setup inline with recommended best practices. The example is generic and can serve as a starting point for a more customized Oracle GoldenGate implementation

1. Process name: Manager

Description: Manager is GoldenGate's parent process and is responsible for the management of GoldenGate processes, resources, user interface, and the reporting of thresholds and errors.

Manager parameter file (Sample)

-- Manager port number

PORT

-- As a Manager parameter, PURGEOLDEXTRACTS allows to manage trail

-- files in a centralized fashion and take into account multiple

-- processes.

PURGEOLDEXTRACTS, USECHECKPOINTS, MINKEEPHOURS MINKEEPFILES

-Start one or more Extract and Replicat processes automatically after-they fail. AUTORESTART provides fault tolerance when something

-- temporary interferes with a process, such as intermittent network

-- outages or programs that interrupt access to transaction logs.

AUTORESTART EXTRACT *, RETRIES, WAITMINUTES, RESETMINUTES

-- This is to specify a lag threshold that is considered critical

-- and to force a warning message to the error log. Lagreport parameter

Specifies the interval at which manager checks for extract / replicat-- lag.

LAGREPORTMINUTES

LAGCRITICALMINUTES

2. Process name: Extract

Description: The Extract process captures either full data records or transactional data changes, depending on configuration parameters, and then sends the data to a target system to be applied to target tables or processed further by another process, such as a load utility.

Extract parameter file (Sample)

-- #

-- Runcmd: ADD EXTRACT, TRANLOG, BEGIN NOW

-- Runcmd: ADD EXTTRAIL

-- EXTRACT, Megabytes

-- Name of the extract process. Limited to 8 charecters.

EXTRACT

-- DB environment settings

SETENV (ORACLE_HOME = "")

SETENV (ORACLE_SID= "")

-- OGG database user login

USERID password, encryptkey default

-- Local trail info

EXTTRAIL

-- Prevent data looping. This is generally used in bi-directional

-- configuration

TRANLOGOPTIONS EXCLUDEUSER

-ASM login info (Oracle only. If db is using ASM)

TRANLOGOPTIONS ASMUSER sys@, ASMPASSWORD, encryptkey default

-DBLOGREADER enables Extract to use a read buffer size of up to 4 MB-in size. A larger buffer may improve the performance of Extract when-- redo rate is high. The db has to be 10.2.0.5 or higher to use

-- this feature. If DBLOGREADER parameter is in place then the above

-- ASMUSER parameter should not be used.

TRANLOGOPTIONS DBLOGREADER, DBLOGREADER BUFSIZE [x], BUFSIZE [y]

-- DDL replication parameters

DDL INCLUDE ALL, EXCLUDE OBJNAME "."

DDLOPTIONS ADDTRANDATA

-- Discard file location.

DISCARDFILE

PORT

PORT defines the port number on which Manager runs on the local system. The default port

Is 7809. You must specify either the default port or another port. The port must be

Unreserved and unrestricted. GGSCI uses this port to request Manager to start processes.

The Extract process uses this port to request Manager to start a remote Collector process

Or an initial-load Replicat process. PORT is the only required Manager parameter.

Autostart parameters

Use the AUTOSTART parameter to start Extract and Replicat processes when Manager starts.

This can be useful, for example, if you want GoldenGate activities to begin immediately

When you start the system, assuming Manager is part of the startup routine. You can use

Multiple AUTOSTART statements in the same parameter file.

AUTOSTART {ER | EXTRACT | REPLICAT} {group name | wildcard}

Use the AUTORESTART parameter to start Extract and Replicat processes again after

Abnormal termination.

AUTORESTART {ER | EXTRACT | REPLICAT} {group name | wildcard}

[, RETRIES]

[, WAITMINUTES]

[, RESETMINUTES]

2. DBLOGIN USERID, PASSWORD

=

Dblogin userid test,password test

DBLOGIN USERID, PASSWORD

Where: is a database user who has privilege to create triggers or enable tablelevel

Supplemental logging, and is that user's password.

3. ADD TRANDATA command

=

Use the ADD TRANDATA command in GGSCI to configure the database to log the key values

Whenever it logs a row change, so that they are available to GoldenGate in the redo record.

By default, the database only logs column values that are changed.

ADD TRANDATA must be performed before you start GoldenGate processing.

Add trandata test.*

To capture key values with ADD TRANDATA

ADD TRANDATA [, COLS] [, NOKEY] [, USETRIGGER]

Where:

Is the owner and name of the table. You can use a wildcard for the table

Name but not the owner name.

COLS logs non-key columns that are specified with KEYCOLS.

NOKEY prevents the logging of the primary key or unique key. Requires using a

KEYCOLS clause in TABLE or MAP and logging the KEYCOLS columns with COL.

USETRIGGER forces GoldenGate to install an update trigger instead of a supplemental

Log group. Required only if 8i compatibility is enabled on a 9i or later database.

4. ADD EXTRACT command

=

Add extract extzq, tranlog, begin now

Explanation:

Use ADD EXTRACT to create an Extract group. Unless a SOURCEISTABLE

Task is specified, this command creates checkpoints so that

Processing continuity is maintained from run to run.

ADD EXTRACT

{, SOURCEISTABLE |

, GGSLOG |

, TRANLOG [bsds name > |

, VAM |

, EXTFILESOURCE |

, EXTTRAILSOURCE |

, VAMTRAILSOURCE}

{, BEGIN |

, EXTSEQNO, EXTRBA |

, LOGNUM, LOGPOS |

, LSN |

, EXTRBA |

, EOF | LSN |

, PAGE, ROW}

[, THREADS]

[, PASSIVE]

[, PARAMS]

[, REPORT]

[, DESC]

TRANLOG

Specifies the transaction log as the data source. Use this option

For log-based extraction. TRANLOG requires either the BEGIN or EXTSEQNO

And EXTRBA options.

The following are service options:

BEGIN

Specifies a timestamp in the data source at which to begin processing.

Valid values:

* NOW

* A date and time in the format of:

Yyyy-mm-dd [hh:mi: [ss[.cccccc]

Example 2:

The following creates an Extract group named finance that extracts

Database changes from the transaction logs. Extraction starts with

Records generated at the time when the group was created.

ADD EXTRACT finance, TRANLOG, BEGIN NOW

5. ADD EXTTRAIL command

=

Add exttrail / home/test/ggs95/dirdat/zq,extract extzq,megabytes 50

Use ADD EXTTRAIL to create a trail for online processing on the local

System and:

* Associate it with an Extract group.

* Assign a maximum file size.

Syntax:

ADD EXTTRAIL, EXTRACT [, MEGABYTES]

The fully qualified path name of the trail. The actual trail name

Can contain only two characters. GoldenGate appends this name with

A six-digit sequence number whenever a new file is created. For

Example, a trail named / ggs/dirdat/tr would have files named

/ ggs/dirdat/tr000001, / ggs/dirdat/tr000002, and so forth.

The name of the Extract group to which the trail is bound. Only

One Extract process can write data to a trail.

MEGABYTES

The maximum size, in megabytes, of a file in the trail. The

Default is 10.

Example: ADD EXTTRAIL c:\ ggs\ dirdat\ aa, EXTRACT finance, MEGABYTES 20

6. EXTFILESOURCE means

= =

Add extract dpezq,exttrailsource / home/test/ggs95/dirdat/zq

EXTFILESOURCE

Specifies an extract file as the data source. Use this option with a

Secondary Extract group (data pump) that acts as an intermediary

Between a primary Extract group and the target system. For

Specify the fully qualified path name of the file, for example

C:\ ggs\ dirdat\ extfile.

7. ADD RMTTRAIL command

=

Add rmttrail d:\ ggs95\ dirdat\ zq, extract dpezq,megabytes 50

Use ADD RMTTRAIL to create a trail for online processing on a remote

System and:

* Associate it with an Extract group.

* Assign a maximum file size.

In the parameter file, specify a RMTHOST entry before any RMTTRAIL

Entries to identify the remote system and TCP/IP port for the Manager

Process.

Syntax:

ADD RMTTRAIL, EXTRACT [, MEGABYTES]

The fully qualified path name of the trail. The actual trail name can

Contain only two characters. GoldenGate appends this name with a six-

Digit sequence number whenever a new file is created. For example, a

Trail named / ggs/dirdat/tr would have files named / ggs/dirdat/tr000001

/ ggs/dirdat/tr000002, and so forth.

The name of the Extract group to which the trail is bound. Only one

Extract process can write data to a trail.

MEGABYTES

The maximum size, in megabytes, of a file in the trail. The default

Is 10.

Example: ADD RMTTRAIL c:\ ggs\ dirdat\ aa, EXTRACT finance, MEGABYTES 20

8. ADD REPLICAT command

=

Add replicat repzq, exttrail / home/gdora/ggs/dirdat/rt, nodbcheckpoint, begin now

Use ADD REPLICAT to create a Replicat group. Unless a special run is

Specified, ADD REPLICAT creates checkpoints so that processing

Continuity is maintained from run to run. See the GoldenGate

Operations Guide for Windows and UNIX for procedures that include

Creating Replicat groups.

Syntax:

ADD REPLICAT

{, SPECIALRUN |

, EXTFILE |

, EXTTRAIL}

[, BEGIN |

, EXTSEQNO, EXTRBA]

[, CHECKPOINTTABLE | NODBCHECKPOINT]

[, PARAMS]

[, REPORT]

[, DESC]

The name of the Replicat group. Follow these naming conventions:

* You can use up to eight ASCII characters, including nonalphanumeric

Characters such as the underscore (_). Any ASCII character can be

Used, so long as the operating system allows that character to be

In a filename. This is because a group is identified by its

Associated checkpoint file.

* Group names are not case-sensitive.

* Use only one word.

* Do not use the word port as a group name. However, you can use

The string port as part of the group name.

* Do not place a numeric value at the end of a group name, such as

Fin1, fin10, and so forth. You can place a numeric value at the

Beginning of a group name, such as 1_fin, 1fin, and so forth.

Ext_1

Ex+2t

Ex!2t

EXTTRAIL

Specifies a trail that was created with the ADD RMTTRAIL or ADD

EXTTRAIL command.

NODBCHECKPOINT

Specifies that this Replicat group will not write checkpoints to a

Checkpoint table. This argument overrides the default CHECKPOINTTABLE

Specification in the GLOBALS file.

9. PURGEOLDEXTRACTS

=

Trail maintenance parameter

Use the PURGEOLDEXTRACTS parameter in a Manager parameter file to purge trail files when

GoldenGate has finished processing them. Without using PURGEOLDEXTRACTS, no purging is

Performed, and trail files can consume significant disk space.

Using PURGEOLDEXTRACTS as a Manager parameter is preferred over using the Extract or

Replicat version of PURGEOLDEXTRACTS. As a Manager parameter, PURGEOLDEXTRACTS allows

You to manage trail files in a centralized fashion and take into account multiple processes.

NOTE When using this parameter, do not permit trail files to be deleted by any user or

Program other than GoldenGate. It will cause PURGEOLDEXTRACTS to function

Improperly

Purgeoldextracts / home/gdora/ggs/dirdat/*, usecheckpoints, minkeepdays 2

Use USECHECKPOINTS to purge when all processes are finished with a file as indicated by

Checkpoints.

Use the MINKEEP rules to set a minimum amount of time to keep unmodified data:

Use MINKEEPHOURS or MINKEEPDAYS to keep data for hours or days.

10. AUTOSTART parameters

=

To auto-start a process

● Use AUTOSTART in the Manager parameter file to start one or more processes when

Manager starts.

● Use AUTORESTART in the Manager parameter file to restart a process after a failure.

Both of these parameters reduce the need to start a process manually with the START

Command.

Syntax AUTOSTART

Specify one of the following:

EXTRACT

REPLICAT

ER (Extract and Replicat)

A group name or wildcard specification for multiple groups. When wildcarding

Is used, GoldenGate starts all groups of the specified that satisfy

The wildcard on the local system, except those in PASSIVE mode.

Example AUTOSTART ER *

11. DYNAMICRESOLUTION

=

DYNAMICRESOLUTION | NODYNAMICRESOLUTION

Valid for Extract and Replicat

Use the DYNAMICRESOLUTION and NODYNAMICRESOLUTION parameters to control how table

Names are resolved.

Use DYNAMICRESOLUTION to make processing start sooner when there is a large number of

Tables specified in TABLE or MAP statements. By default, whenever a process starts

GoldenGate queries the database for the attributes of the tables and then builds an object

Record for them. The record is maintained in memory and on disk, and the process of

Building it can be time-consuming if the database is large.

DYNAMICRESOLUTION causes the object record to be built one table at a time, instead of all at

Once. A table's attributes are added to the record the first time its object ID enters the

Transaction log, which occurs with the first extracted transaction on that table. Recordbuilding

For other tables is deferred until activity occurs. DYNAMICRESOLUTION is the same as

WILDCARDRESOLVE DYNAMIC.

NODYNAMICRESOLUTION causes the object record to be built at startup. This option is not

Supported for Teradata. NODYNAMICRESOLUTION is the same as WILDCARDRESOLVE IMMEDIATE.

For more information about WILDCARDRESOLVE, see page 368.

Default DYNAMICRESOLUTION

Syntax DYNAMICRESOLUTION

12. SOURCEDB

=

Valid for Manager, Extract, DEFGEN, and DDLGEN

Use the SOURCEDB parameter for databases that require a data source name as part of the

Connection information. Tables specified in TABLE statements that follow SOURCEDB are

Assumed to be from the specified data source.

In fact, it doesn't work, and it will report that the parameter is not recognized.

13. PASSTHRU

=

There are two options for data-pump mode: PASSTHRU and NOPASSTHRU

PASSTHRU: do not interact with the database, as long as the source and target database objects must be consistent.

Use the PASSTHRU and NOPASSTHRU parameters to control whether a data-pump Extract

Processes tables in pass-through mode or normal mode. In pass-through mode, the Extract

Process does not look up table definitions, either from the database or from a datadefinitions

File. Normally, the Extract process logs into the database to retrieve data

Definitions and, if the target is NonStop, reads a data-definitions file. The definitions are

Used to perform mapping and conversion functions.

To use PASSTHRU mode, the names of the source and target objects must be

Identical. No column mapping, filtering, SQLEXEC functions, transformation, or other

Functions that require data manipulation can be specified in the parameter file. You

Can combine normal processing with pass-through processing by pairing PASSTHRU

And NOPASSTHRU with different TABLE statements.

14. ASSUMETARGETDEFS

=

Do not query the table structure information of the main library, provided that when using MAP statement, the field structure of the table of the original library and the target library must be the same.

Valid for Replicat

Use the ASSUMETARGETDEFS parameter when the source and target tables specified with a

MAP statement have identical column structure, such as when synchronizing a hot site. It

Directs GoldenGate not to look up source structures from a source-definitions file.

For structures to be identical, they must contain identical column names (including case

If applicable) and data types, and they must appear in the same order in each table. If

Source and target tables do not have the same structure, use the SOURCEDEFS parameter

Instead of ASSUMETARGETDEFS. See "SOURCEDEFS" on page 291.

Default None

Syntax ASSUMETARGETDEFS

15. REPERROR

=

REPERROR

Valid for Replicat

Use the REPERROR parameter to control how Replicat responds to errors. You can use one

REPERROR statement to handle most errors in a default manner, while using one or more

Other REPERROR statements to handle specific errors differently. For example, you can ignore

Duplicate-record errors but abort processing in all other cases.

DEFAULT Sets a global response to all errors except those for which explicit

REPERROR statements are specified.

DISCARD Log the error to the discard file but continue processing the transaction

And subsequent transactions.

DISCARDFILE

Valid for Extract and Replicat

Use the DISCARDFILE parameter to generate a discard file to which GoldenGate can log

Records that it cannot process. Records can be discarded for several reasons. For example

A record is discarded if the underlying table structure changed since the record was written

To the trail. You can use the discard file to help you identify the cause of processing errors.

Each entry in the discard file contains the discarded record buffer and an error code

Indicating the reason. GoldenGate creates the specified discard file in the dirrpt subdirectory

Of the GoldenGate installation directory. You can view it with a text editor or by

Using the following command in GGSCI.

VIEW REPORT

Where: is the fully qualified name of the discard file.

GoldenGate creates the specified discard file in the dirrpt subdirectory

Of the GoldenGate installation directory.

/ * the storage path of discard file * /

APPEND Adds new content to existing content if the file already exists.

MEGABYTES Sets the maximum size of the file in megabytes. The valid range is from 1

To 2147. The default is 1 MB.

16. Use wildcards

=

Using wildcards in command arguments

You can use wildcards with certain GoldenGate commands to control multiple Extract and

Replicat groups as a unit. The wildcard symbol that is supported by GoldenGate is the

Asterisk (*). An asterisk represents any number of characters. For example, to start all

Extract groups whose names contain the letter X, issue the following command.

START EXTRACT * X*

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.