OGG参数详解

一直以来对oracle Goldengate许多参数比较疑惑，正好在MOS看到这个文章，转载到BLOG，以备参考

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

一、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 <port number>

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

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

-- processes.

PURGEOLDEXTRACTS <path to the trail file>, USECHECKPOINTS, MINKEEPHOURS <"x" hours> MINKEEPFILES <"y" number of files>

-- 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 <x>, WAITMINUTES <y>, RESETMINUTES <z>

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

LAGCRITICALMINUTES <y>

二、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 <extract name>, TRANLOG, BEGIN NOW

-- Runcmd: ADD EXTTRAIL <extract trail path/two character trail id> ,

-- EXTRACT <extract name>, Megabytes <n>

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

EXTRACT <Extract name>

-- DB environment settings

SETENV (ORACLE_HOME = "<Oracle home path>" )

SETENV (ORACLE_SID="<Oracle sid>")

-- OGG database user login

USERID <username> passWord <encrypted password>, encrypTKEy default

-- Local trail info

EXTTRAIL <extract trail path/two character trail id>

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

-- configuration

TRANLOGOPTIONS EXCLUDEUSER <Replicat username>

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

TRANLOGOPTIONS ASMUSER sys@<connect string>, ASMPASSWORD <encrypted password>, 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 "<schema name>.<object name> "

DDLOPTIONS ADDTRANDATA

--Discard file location.

DISCARDFILE <diacard file path/<discard file name.dsc>, APPEND Megabytes <n>

-- Use the DISCARDROLLOVER parameter to set a schedule for aging discard --files.

DISCARDROLLOVER AT <hh:mi> on <day of the week>

-- Use the REPORTROLLOVER parameter to force report files to age on a

-- regular schedule, instead of when a process starts

REPORTROLLOVER AT <hh:mi> on <day of the week>

-- Use the REPORTCOUNT parameter to report a count of transaction

-- records that Extract or Replicat processed since startup

REPORTCOUNT EVERY <n> HOURS, RATE

-- Use the FETCHOPTIONS parameter to control certain aspects of the way -- that GoldenGate fetches data

FETCHOPTIONS, USESNAPSHOT, NOUSELATESTVERSION, MISSINGROW REPORT

STATOPTIONS REPORTFETCH

-- Warn for long running txns

WARNLONGTRANS <n>H, CHECKINTERVAL <n>m

-- List of tables

Table <source schema name>.<table name>;

..

Pump (Extract) Parameter file (Sample)

-- Runcmd: ADD EXTRACT <pump name>, EXTTRAILSOURCE <extract trail

-- Path/two character trail id>

-- Runcmd: ADD RMTTRAIL <pump trail path/two character trail id> ,

-- EXTRACT <pump name>, Megabytes <n>

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

EXTRACT <Pump name>

-- Oracle environment settings

SETENV (ORACLE_HOME = "<Oracle home path>" )

SETENV (ORACLE_SID="<Oracle sid>")

-- In passthru mode GoldenGate pump process cascades captured data from -- source to target without logging in to the source database

Passthru

-- Remote host and remort manager port to write trail

RMTHOST <Remote hostname>, MGRPORT <Target manager port number>

-- Remote trail info

RMTTRAIL <extract trail path/two character trail id>

--Discard file location.

DISCARDFILE <diacard file path/<discard file name.dsc>, APPEND Megabytes <n>

-- Use the DISCARDROLLOVER parameter to set a schedule for aging discard --files.

DISCARDROLLOVER AT <hh:mi> on <day of the week>

-- Use the REPORTROLLOVER parameter to force report files to age on a

-- Regular schedule, instead of when a process starts

REPORTROLLOVER AT <hh:mi> on <day of the week>

-- Use the REPORTCOUNT parameter to report a count of transaction

-- Records that Extract or Replicat processed since startup

REPORTCOUNT EVERY <n> HOURS, RATE

-- List of tables

Table <source schema name>.<table name>;

三、Process name: Replicat

Description: The Replicat process reads data extracted by the Extract process and applies it to target tables or prepares it for use by another application, such as a load application.

Replicat parameter file

###################################################################

-- Runcmd: ADD REPLICAT <REPLICAT name>, EXTTRAIL <trail file path/two -- character trail id>

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

REPLICAT <Replicat name>

-- Oracle environment settings

SETENV (ORACLE_HOME = "<Oracle home path>" )

SETENV (ORACLE_SID= "<Oracle sid>")

SETENV (NLS_LANG = ="<Target db charecterset>")

-- OGG database user login

USERID <username> password <encrypted password>, encryptkey default

--Discard file location.

DISCARDFILE <diacard file path/<discard file name.dsc>, APPEND Megabytes <n>

--DDL replication parameters

DDL INCLUDE ALL, EXCLUDE OBJNAME "<schema name>.<object name>"

DDLOPTIONS REPORT

-- The following parameter speeds up replicat processing rate. The

-- parameter alters the replicat oracle session to not wait for commits -- to be persisted to the redo.

sqlEXEC "ALTER SESSION SET COMMIT_WRITE = NOWAIT"

-- Use the BATCHSQL parameter to increase the performance of Replicat. -- BATCHSQL causes Replicat to organize similar SQL statements into arrays and apply -- them at an accelerated rate.

BATCHSQL

-- Use the DISCARDROLLOVER parameter to set a schedule for aging discard --files.

DISCARDROLLOVER AT <hh:mi> on <day of the week>

-- Use the REPORTROLLOVER parameter to force report files to age on a

-- regular schedule, instead of when a process starts

REPORTROLLOVER AT <hh:mi> on <day of the week>

-- Use the REPORTCOUNT parameter to report a count of transaction

-- records that Extract or Replicat processed since startup

REPORTCOUNT EVERY <n> HOURS, RATE

-- List of tables (MAP statements)

MAP <source schema name>.<table name>, TARGET <target schema name>.<tablename>;

..

..

GLOBALS file

The GLOBALS file stores parameters that relate to the GoldenGate instance as a whole, as opposed to runtime parameters for a specific process.

Globals parameter file

-- Specifies the name of the Manager process when it is installed as a windows service.

MGRSERVNAME <mgr service name>

-- Specifies a default checkpoint table

CHECKPOINTTABLE <owner.tablename>

-- Specifies the name of the schema that contains the database objects that support DDL

-- synchronization for Oracle

GGSCHEMA <schema name>

-- Specifies a non-default name for the DDL history table that supports DDL

-- synchronization for Oracle.

DDLTABLE <table name>

-- Specifies a non-default name for the DDL marker table that supports DDL

-- synchronization for Oracle

MARKERTABLE <table name>

四、GoldenGate 基本参数含义

1. edit params命令

2. DBLOGIN USERID <user>, PASSWORD <password>

3. ADD TRANDATA命令

4. ADD EXTRACT 命令

5. ADD EXTTRAIL命令

6. EXTFILESOURCE <file name>意思

7. ADD RMTTRAIL命令

8. ADD REPLICAT命令

9. PURGEOLDEXTRACTS

10. AUTOSTART参数

11. DYNAMICRESOLUTION

12. SOURCEDB

13. PASSTHRU

14. ASSUMETARGETDEFS

15. REPERROR

16. 使用通配符

(1) Overview of Extract

The Extract process runs on the source system and is the capture mechanism of

GoldenGate. You can configure Extract in one of the following ways:

● Initial loads: For initial data loads, Extract extracts a current set of data directly from

their source objects.

● Change synchronization: To keep source data synchronized with another set of data,

Extract extracts transactional changes made to data (inserts, updates, and deletes)

after the initial synchronization has taken place. DDL changes and sequences are also

extracted, if supported for the type of database being used.

(2) Overview of Replicat

The Replicat process runs on the target system. Replicat reads extracted data changes and

DDL changes (if supported) that are specified in the Replicat configuration, and then it

replicates them to the target database. You can configure Replicat in one of the following

ways:

(3) Overview of trails

To support the continuous extraction and replication of supported database changes,

GoldenGate stores those changes temporarily on disk in a series of files called a trail. A

trail can exist on the source or target system, or on an intermediary system, depending on

how you configure GoldenGate. On the local system it is known as an extract trail (or local

trail). On a remote system it is known as a remote trail.

By using a trail for storage, GoldenGate supports data accuracy and fault tolerance (see

“Overview of checkpoints” on page 28). The use of a trail also allows extraction and

replication activities to occur independently of each other. With these processes separated,

you have more choices for how data is delivered. For example, instead of extracting and

replicating changes continuously, you could extract changes continuously but store them

in the trail for replication to the target later, whenever the target application needs them.

1. edit params命令

==================

Use EDIT PARAMS to create or change a parameter file. By default,

this command launches Microsoft Notepad on Windows or the vi editor

on UNIX systems. You can change the editor with the SET EDITOR

command.

Syntax:

EDIT PARAMS {MGR | <group> | <file name>}

MGR

Opens a parameter file for the Manager process.

<group>

Opens a parameter file for the specified Extract or Replicat group.

<file name>

Opens the specified file. Use the full path name.

Example 1: EDIT PARAMS finance

Example 2: EDIT PARAMS c:\lpparms\replp.prm

GGSCI (testdb11.zhyhl.com) 6>

PORT <port_number>

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 <max retries>]

[, WAITMINUTES <wait minutes>]

[, RESETMINUTES <reset minutes>]

2. DBLOGIN USERID <user>, PASSWORD <password>

=============================================

dblogin userid test,password test

DBLOGIN USERID <user>, PASSWORD <password>

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

supplemental logging, and <password> is that user’s password.

3. ADD TRANDATA命令

===================

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 <table> [, COLS <columns>] [, NOKEY] [, USETRIGGER]

Where:

 <table> is the owner and name of the table. You can use a wildcard for the table

name but not the owner name.

 COLS <columns> 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 命令

===================

add extract extzq, tranlog, begin now

解释：

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 <group name>

{, SOURCEISTABLE |

 , GGSLOG <ggslog table> |

 , TRANLOG [bsds name> |

 , VAM |

 , EXTFILESOURCE <file name> |

 , EXTTRAILSOURCE <trail name> |

 , VAMTRAILSOURCE <VAM trail name>}

{, BEGIN <begin spec> |

 , EXTSEQNO <seqno>, EXTRBA <relative byte address> |

 , LOGNUM <log number>, LOGPOS <byte offset> |

 , LSN <value> |

 , EXTRBA <relative byte address> |

 , EOF | LSN <value> |

 , PAGE <data page>, ROW <row>}

[, THREADS <n>]

[, PASSIVE]

[, PARAMS <parameter file>]

[, REPORT <report file>]

[, DESC <description> ]

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 <begin spec>

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命令

====================

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 <trail name>, EXTRACT <group name> [,MEGABYTES <n>]

<trail name>

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.

<group name>

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

one Extract process can write data to a trail.

MEGABYTES <n>

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 <file name>意思

================================

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

EXTFILESOURCE <file name>

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 <file name>,

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

c:\ggs\dirdat\extfile.

7. ADD RMTTRAIL命令

===================

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 <trail name>, EXTRACT <group name> [, MEGABYTES <n>]

<trail name>

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.

<group name>

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

Extract process can write data to a trail.

MEGABYTES <n>

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命令

===================

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 <group name>

{, SPECIALRUN |

 , EXTFILE <full path name> |

 , EXTTRAIL <full path name>}

[, BEGIN <start point> |

 , EXTSEQNO <seqno>, EXTRBA <relative byte address>]

[, CHECKPOINTTABLE <owner.table> | NODBCHECKPOINT]

[, PARAMS <parameter file>]

[, REPORT <report file>]

[, DESC <description> ]

<group name>

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 <full path name>

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

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

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

to 2147. The default is 1 MB.

16. 使用通配符

==============

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*