Shulou(Shulou.com)05/31 Report--

This article mainly introduces "production-side analysis of Oracle GoldenGate configuration parameters". In daily operation, I believe that many people have doubts about the production-side analysis of Oracle GoldenGate configuration parameters. The editor has consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "production-side analysis of Oracle GoldenGate configuration parameters". Next, please follow the editor to study!

The version of OGG is 11.2.1.0.4

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.4 14636914 OGGCORE_11.2.1.0.4_PLATFORMS_121007.2020

HP/UX, IA64, 64bit (optimized), Oracle 10g on Oct 8 2012 02:26:37

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

Initiated process

GGSCI > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING DPEYA 00:00:00 00:00:02

EXTRACT RUNNING EXTYA 00:00:02 00:00:05

EXTYA is the extraction process, and DPEYA is the delivery process

Manage process configuration

GGSCI > view params mgr

PORT 7839

DYNAMICPORTLIST 7840-7914

-- AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 7

PURGEOLDEXTRACTS. / dirdat/*, USECHECKPOINTS, MINKEEPDAYS 10

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

PORT 7839

Communication port 7839. The production side and the disaster recovery side need to be consistent.

DYNAMICPORTLIST 7840-7914

The dynamic port list ranges from 7840 to 7914. When the established port is occupied or a communication failure occurs, the management process will select the next port from the list to try to connect to avoid a single point of failure of the communication port.

-- AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 7

Try to restart automatically after the extraction process is interrupted, try to start every 7 minutes, try 5 times. OGG operators usually comment out this configuration and prefer to restart it manually.

PURGEOLDEXTRACTS. / dirdat/*, USECHECKPOINTS, MINKEEPDAYS 10

Periodically clean up the local queue (local trail) under the dirdat path. The retention period is 10 days and will be deleted automatically when it expires. As a result, the directory that controls the queue files will not grow too large.

LAGREPORTHOURS 1

Check the transmission delay every other hour

LAGINFOMINUTES 30

A transfer delay of more than 30 minutes will be written to the error log

LAGCRITICALMINUTES 45

If the transmission delay exceeds 45 minutes, the warning log will be written.

Usually, the phenomenon of transmission delay is easy to occur in the partition table of some massive data. It is recommended to start multiple replication processes (replicat) on the disaster recovery side to open dedicated channels for these large tables.

Extraction process configuration

GGSCI 3 > view params extya

EXTRACT extya

SETENV (NLS_LANG= "AMERICAN_AMERICA.UTF8")

USERID goldengate, PASSWORD AACAAAAAAAAAAAJAPJYATHVIMGMAOFHIUCUEPFYGGJBFJGIH, ENCRYPTKEY default

REPORTCOUNT EVERY 30 MINUTES, RATE

DISCARDFILE. / dirrpt/extya.dsc, APPEND, MEGABYTES 1024

DISCARDROLLOVER AT 3:00

WARNLONGTRANS 2h, CHECKINTERVAL 3m

EXTTRAIL. / dirdat/ya

DYNAMICRESOLUTION

DBOPTIONS ALLOWUNUSEDCOLUMN

FETCHOPTIONS NOUSESNAPSHOT

FETCHOPTIONS FETCHPKUPDATECOLS

-- CACHEMGR CACHESIZE 8000MB, CACHEDIRECTORY / goldengate/temp, CACHEDIRECTORY / goldengate/temp2

TRANLOGOPTIONS CONVERTUCS2CLOBS

-- tranlogoptions asmuser sys@asm, asmpassword AACAAAAAAAAAAGAIF, ENCRYPTKEY default

TRANLOGOPTIONS altarchivelogdest primary instance sgpmdb1 / sgpmdb/arch altarchivelogdest instance sgpmdb2 / sgpmdb/arch

THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000 IOLATENCY 60000

-Schema name involves sensitive characters. Here, Schema is used instead of

-table name involves sensitive characters. Here, table is used instead of

Mapexclude Schema1.table1

Mapexclude Schema1.table2

Mapexclude Schema2.table1

Mapexclude Schema2.table1

TABLE Schema1.*

TABLE Schema2.*

TABLE Schema3.*

TABLE Schema4.*

TABLE Schema5.*

Sequence Schema1.*

Sequence Schema2.*

Sequence Schema3.*

Sequence Schema4.*

Sequence Schema5.*

SETENV (NLS_LANG= "AMERICAN_AMERICA.UTF8")

Set the character set environment variable to UTF8

If there are multiple databases in the system, ORACLE_HOME, ORACLE_SID, etc., are sometimes set with the parameter SETENV, for example:

SETENV (ORACLE_HOME = "/ home/oracle/product/10.2.0/db)

SETENV (ORACLE_SID = "PROD")

REPORTCOUNT EVERY 30 MINUTES, RATE

Report the number of transaction records from the beginning of the program to the present extraction process or replication process every 30 minutes, and report the statistical information of the process

DISCARDFILE. / dirrpt/extya.dsc, APPEND, MEGABYTES 1024

Save the record of the failed execution in discard file, which is located in. / dirrpt/extya.dsc and the size is 1024MB. If a record is already included in the file, it will be appended later without deleting the previous record.

DISCARDROLLOVER AT 3:00

To prevent the discard file from being full, set the file expiration at 3:00 every day.

WARNLONGTRANS 2h, CHECKINTERVAL 3m

Check major transactions every 3 minutes and report those that are not finished for more than 2 hours.

EXTTRAIL. / dirdat/ya

Queue file path

DYNAMICRESOLUTION

Sometimes it is slow to start the OGG process, perhaps because there are too many tables that need to be synchronized. Before starting the process, OGG will establish a record of the tables that need to be synchronized and save them to disk, which will take a lot of time. Use this parameter to resolve this problem.

DBOPTIONS ALLOWUNUSEDCOLUMN

Only a warning is generated when the extraction process encounters an unused field, and the process continues execution without being abnormally terminated (abend)

FETCHOPTIONS NOUSESNAPSHOT

No data is obtained from the flashback log

FETCHOPTIONS FETCHPKUPDATECOLS

Use this parameter when using HANDLECOLLISIONS.

When a missing update record (missing update) occurs in the replication process and the primary key is updated, the update is converted to insert. Since the inserted record may not be a complete row, you need to add this parameter to ensure integrity

-- CACHEMGR CACHESIZE 8000MB, CACHEDIRECTORY / goldengate/temp, CACHEDIRECTORY / goldengate/temp2

Set virtual memory in the / goldengate/temp,/goldengate/temp2 directory

TRANLOGOPTIONS CONVERTUCS2CLOBS

Specify the special parameters required when parsing database logs

This parameter is only used on the extract side of the UTF character type, and is required to process CLOB before 11.1.1.

TRANLOGOPTIONS altarchivelogdest primary instance rac1 / arch altarchivelogdest instance rac2 / arch

Specify the archive log address of each node in the RAC environment

THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000 IOLATENCY 60000

All nodes in the RAC cluster must synchronize the system clock. GoldenGate makes key decisions by comparing the time of the local system with the point in time when the transaction was committed. You can use NTP to vary the system time. The COMPATIBLE parameter settings must also be the same on all nodes.

This parameter is no longer used after the Oracle11.2 version.

Delivery process configuration

GGSCI 6 > view params dpeya

EXTRACT dpeya

RMTHOST 10.10.10.1, MGRPORT 7839, COMPRESS

PASSTHRU

NUMFILES 5000

RMTTRAIL. / dirdat/yb

DYNAMICRESOLUTION

-list of tables to copy

TABLE Schema1.*

TABLE Schema2.*

TABLE Schema3.*

TABLE Schema4.*

TABLE Schema5.*

RMTHOST 10.10.10.1, MGRPORT 7839, COMPRESS

Disaster recovery host IP, manage process port number, compress queue files before delivery

NUMFILES 5000

Handle up to 5000 tables

RMTTRAIL. / dirdat/yb

The directory where the queue file is saved on the disaster recovery side

PASSTHRU

Using pass-through mode to process tables

DYNAMICRESOLUTION

Dynamic resolution of table names

At this point, the study on the "production-side analysis of Oracle GoldenGate configuration parameters" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.