【OGG】常用参数、函数

常用extract 参数文件

GGSCI (EPCDB1) 2> view param extgh

 

extractextgh

useridggs, password ggs

TRANLOGOPTIONS CONVERTUCS2CLOBS               

--本地字符集为UTF，同步数据类型有CLOB，需要加此参数（OGG11版本以后就不用了）

warnlongtrans2h, checkinterval 3m

fetchoptionsnousesnapshot

THREADOPTIONSMAXCOMMITPROPAGATIONDELAY  30000IOLATENCY 30000

tranlogoptionsaltarchivelogdest primary instance epcdb /arch

discardfile  ./dirrpt/extgh.dsc,append,megabytes 10

getupdatebefores

ignorereplicates

exttrail/u01/ggsdmp/ext/gh/e1

numfiles4000

dynamicresolution

DDLINCLUDE MAPPED

 

tableGH.APP_INFO;

 

常用datapump参数文件

GGSCI (EPCDB1) 2> view param dpgh

 

extractdpgh

rmthost192.168.131.44, mgrport 7809

rmttrail/u01/ggsdmp/gh/r2

passthru       

dynamicresolution

tableGH.APP_INFO;

 

 

常用replicat 参数文件

GGSCI (testrac2) 2> view param repgh

 

replicatrepgh

useridggs, password ggs

sqlexec"alter session set constraints=deferred"

discardfile./dirrpt/repgh.dsc,append, megabytes 100

numfiles4000

assumetargetdefs

dynamicresolution

APPLYNOOPUPDATES

checksequencevalue

allownoopupdates

batchsql

DDLINCLUDE MAPPED

DDLERRORdefault ABEND

REPERROR(-1403, discard)

 

mapGH.APP_INFO, target GUARD.APP_INFO;

 

附录—列转换函数列表

ColumnConversion Functions

. . . . .. . . . . . . . . .

Using the columnconversion functions of Oracle GoldenGate, you can manipulate source valuesinto the appropriate format for target columns. These functions enable you to manipulatenumbers and characters, perform tests, extract parameter values, return environmentinformation, and more.

Summary ofcolumn-conversion functions

This summary isorganized according to the types of processing that can be performed with theOracle GoldenGate functions. An alphabetical reference begins on page 431.

 

Table 43 Performing tests

Function           Description

CASE                Selects a valuedepending on a series of value tests.

EVAL                 Selects a value based ona series of independent tests.

IF                        Selectsone of two values depending on whether a conditional statement

returns TRUE or FALSE.

 

Table 44   Handling missing columns

Function           Description

COLSTAT         Returns an indicatorthat a column is MISSING, NULL, or INVALID.

COLTEST         Performs conditionalcalculations to test whether a column is PRESENT,

                     MISSING, NULL,or INVALID.

 

Table 45 Working with dates

Function           Description

DATE                Returns a date and timebased on the format passed into the source column.

DATEDIFF        Returns the difference between two dates ordatetimes.

DATENOW       Returnsthe current date and time.

 

Table 46 Performing arithmeticcalculations

Function           Description

COMPUTE       Returnsthe result of an arithmetic expression.

 

Table 47 Working with strings

Function                    Description

NUMBIN                    Converts a binary stringinto a number.

NUMSTR                   Converts a string into anumber.

STRCAT                     Concatenates one or morestrings.

STRCMP                    Compares two strings.

STREXT                     Extracts a portion of astring.

STREQ                       Determines whether ornot two strings are equal.

STRFIND                   Finds the occurrence ofa string within a string.

STRLEN                     Returns the length of astring.

STRLTRIM                 Trims leading spaces.

STRNCAT                  Concatenates one or morestrings to a maximum length.

STRNCMP                 Compares two stringsbased on a specified number of characters.

STRNUM                   Converts a number into astring.

STRRTRIM                 Trims trailing spaces.

STRSUB                    Substitutes one stringfor another.

STRTRIM            Trimsleading and trailing spaces.

STRUP                       Changes a string to uppercase.

VALONEOF               Comparesa string or string column to a list of values.

 

Table 48 Other functions

Function                             Description

BINARY                               Maintainssource binary data as binary data in the target column

                                   when the sourcecolumn is defined as a character column.

BINTOHEX                Convertsa binary string to a hexadecimal string.

GETENV                                      Returnsenvironmental information.

GETVAL                              Extractsparameters from a stored procedure as input to a FILTER

or COLMAP clause.

HEXTOBIN                         Converts a hexadecimal stringto a binary string.

HIGHVAL | LOWVAL        Constrains a value to a high or low value.

RANGE                                Dividesrows into multiple groups of data for parallel processing.

TOKEN                                Retrievestoken data from a trail record header.

 

1. FETCHCOLS--抓取所需要的列

Use FETCHCOLS and FETCHCOLSEXCEPT to fetch column values from the database when the valuesare not present in the transaction log record. Use this option if the databaseuses compressed updates (where column values are not logged unless theychanged), but you need to ensure that other columnvalues required for FILTER operationsare available.

● FETCHCOLSfetchesthe specified column(s).

● FETCHCOLSEXCEPTfetchesall columns except those specified. For tables with numerous columns, FETCHCOLSEXCEPT may be more efficient than listing eachcolumn with FETCHCOLS.

 

Syntax     TABLE <table spec>, {FETCHCOLS | FETCHCOLSEXCEPT}(<column> [, ...]) ;

 

TABLEGUARD.TEST, FETCHCOLS (col_1, col_2, col_3)

 

2. SQLEXEC--执行sql、procedure

Use SQLEXEC to execute aSQL stored procedure or query from within a MAPstatement

during Oracle GoldenGate processing. SQLEXEC enables OracleGoldenGate to communicate directly with the database to perform any functionsupported by the database. The database function can be part of thesynchronization process, such as retrieving values for column conversion, or itcan be independent of extracting or replicating data.

When used within a MAP statement, theprocedure or query that is executed can accept input parameters from source ortarget rows and pass output parameters.

NOTE A query or procedure must be structuredcorrectly when executing a SQLEXEC statement. If Replicat encounters a problemwith the query or procedure, the process will immediately abend, regardless ofany error-handling rules that are in place.

 

Syntax Procedures:

SQLEXEC (

SPNAME<sp name>

[, ID<logical name>]

{, PARAMS<param spec> | NOPARAMS}

[,<option>] [, ...]

)

Syntax Queries:

SQLEXEC (

ID<logical name>

, QUERY“<sql query>”

{, PARAMS<param spec>| NOPARAMS}

[,<option>] [, ...]

)

 

--源端获得id，通过tokens写入trail，一同传到目标端

, sqlexec(id ifid , query "select gh. SEQ_CMS_INFOS_ID.nextval id from dual" ,noparams)

, tokens(tk_ifid = ifid.id)

 

--目标端执行sql获得id，映射到列

, sqlexec(id icid , query "select GUARD.SEQ_CMSBIZTAG_ID.nextval id fromdual", noparams)

, colmap(id = icid.id)

 

--带参数的sqlexec

, sqlexec(id ctimeb , query "select create_time from epb.v_booking where epb_id =:veidb" , params (veidb=epb_id))

 

3. FILTER--条件筛选

Use FILTER to select orexclude records based on a numeric value. Afilter expression can use conditional operators, Oracle GoldenGatecolumn-conversion functions, or both.

NOTETo filter based on a string, use a string function or usethe WHERE option.

Separate all FILTER componentswith commas.

filter后面的条件只能为数字，即与数字作比较。若是其他如字符类型，可以用where来进行筛选。

Syntax     MAP <table spec>, TARGET <tablespec> , FILTER (

[, ON INSERT | ON UPDATE| ON DELETE]

[, IGNORE INSERT | IGNORE UPDATE |IGNORE DELETE]

, <filter clause>

[, RAISEERROR <error>]

);

 

--选出info_type中以MOBILE开头的数据（@STRFIND查找数据位置）

, FILTER(@STRFIND(info_type,"MOBILE") = 1)

 

4. WHERE—条件筛选

Use WHEREto selectrecords based on a conditional statement. For more information on using a WHERE clause and thecolumn data that can be used, see the Oracle GoldenGateWindows and UNIXAdministrator’s Guide.

Oracle GoldenGate does not support WHERE for columnsthat have a multi-byte character set or a character set that is incompatiblewith the character set of the local operating system.

Literal strings used in WHERE must be enclosed within double quotesunless the USEANSISQLQUOTES parameter is used in the GLOBALSfile. Thisparameter enforces SQL-92 rules for identifiers and literals.

 

Syntax     MAP <table spec>, TARGET <table spec>,

WHERE (<where clause>);

 

-- where后面可以直接进行column test，@NULL, @PRESENT, @ABSENT (column isnull, present or absent in the record).

 

--选出info_type中以MOBILE开头的数据（@STRFIND查找数据位置）

, where (@STRFIND (info_type, "MOBILE") = 1)

 

--选出info_status为9的数据

, where (info_status = "9" );

 

5. assumetargetdefs—源端与目标端表结构相同

Valid for Replicat

Use the ASSUMETARGETDEFSparameter whenthe source and target tables specified with a MAPstatement haveidentical column structure, such as when synchronizing a hot site. It directsOracle GoldenGate not to look up source structures from a source-definitionsfile.

If source and target tables do nothave the same structure, use the SOURCEDEFSparameter instead of ASSUMETARGETDEFS. See“SOURCEDEFS” on page 330.

To find out what makes source andtarget tables identical or different in Oracle GoldenGate terms, see“Associating replicated data with metadata” in the Oracle GoldenGateWindowsand UNIX Administrator’s Guide.

Default    None

Syntax     ASSUMETARGETDEFS

 

在replicat中配置，目标端与源端的表结构完全相同，用了此参数就不会去寻找源定义文件了。

如果源端与目标端的表结构不同，那么就需要使用SOURCEDEFS参数。

 

6. SOURCEDEFS--源端与目标端的表结构异构

Valid for Extract data pump and Replicat.

Use the SOURCEDEFS parameter to specify the name of a file thatcontains definitions of source tables or files. Source definitions are requiredwhen using Oracle GoldenGate to replicate data between heterogeneous source andtargets. Use SOURCEDEFS for one or more of thefollowing processes, depending on your Oracle GoldenGate configuration:

● A Replicat process on the target system

● A data pump on a source or intermediarysystem.

To generate thesource-definitions file, use the DEFGEN utility.Transfer the file to the intermediary or target system before starting a datapump or Replicat.

You can have multiple SOURCEDEFS statements in the parameter file if more thanone source-definitions file will be used, for example if each SOURCEDEFS file holds the definitions for a distinctapplication.

See also ASSUMETARGETDEFS on page 127.

For more informationabout how Oracle GoldenGate make use of metadata, see the Oracle GoldenGateWindowsand UNIX Administrator’s Guide.

Default    None

Syntax     SOURCEDEFS <file name>

 

SOURCEDEFS/u01/goldengate/dirdef/gh.def

 

1). 生成def文件步骤

1.        ggsci输入edit paramsdefgen

--格式如下

CHARSET <character set> --DEFGEN读取文件时采用的字符集，默认为local OS字符集

DEFSFILE <full_pathname> [APPEND |PURGE] [CHARSET <character set>] --指定data-def文件名，charset标注产生的def文件字符集，默认为local os字符集

[{SOURCEDB | TARGETDB} <dsn>] USERID<user>[, PASSWORD <password> [<encryption options>]]

TABLE <owner>.<table> [, {DEF |TARGETDEF} <template name>]; --指定候选表，而def/targetdef据此表产生def模板

 

GGSCI(EPCDB1) 3> edit param defgen     

 

DEFSFILEgh.def

useridggs, password ggs

tableGH.APP_INFO;

 

2.        运行（OGG安装目录下运行defgen命令）

defgen paramfile dirprm/defgen.prm[reportfile dirrpt/defgen.rpt] [NOEXTATTR] [UPDATECS UTF-8]

默认DEFGEN使用本地locale写def文件，可使用defgen修改def文件字符集

defgen paramfile ./dirdef/source.defUPDATECS UTF-8

 

3.        传输

默认以ASCII模式FTP到remote，如果def文件以 remote的字符集创建，则使用binary模式，避免出现不必要的换行；

 

2). 使用SOURCEDEFS定义的表不能复制DDL操作

否则报错：

ERROR   OGG-00513 Oracle GoldenGate Delivery for Oracle, repgh.prm:  Table with SOURCEDEF cannot have DDLoperations (table [GUARD.APP_INFO]). Either remove SOURCEDEF or filter outtable from DDL operations.

--过滤掉DDL操作

DDLINCLUDE MAPPED, EXCLUDE OBJNAME"GUARD.APP_INFO"

 

3). 同时使用assumetargetdefs/sourcedefs

REPLICATacctrep

USERIDogg, PASSWORD AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC, &

AES128KEYNAME mykey1

SOURCEDEFS c:\ggs\dirdef\record.def

MAPacct.cust*, TARGET acct.cust*, DEF custdef;

MAPord.prod, TARGET ord.prod;

MAPord.parts, TARGET ord.parts;

MAPhr.emp, TARGET hr.emp;

MAPhr.salary, TARGET hr.salary;

ASSUMETARGETDEFS

MAPrpt.stock, TARGET rpt.stock;

 

7. REPERROR—replicat错误控制

Use REPERRORto specify anerror and a response that together control how Replicat responds to the errorwhen executing the MAP statement. You can use REPERROR at the MAP level to overrideand supplement global error handling rules set with the REPERROR parameter at theroot level of the parameter file. Multiple REPERRORstatements canbe applied to the same MAP statement toenable automatic, comprehensive management of errors and interruption-freereplication processing.

Syntax     MAP <object spec>, TARGET <object spec>,

REPERROR (

{DEFAULT | DEFAULT2 | <SQL error> |<user-defined error>},

{ABEND | DISCARD | EXCEPTION | IGNORE |

RETRYOP [MAXRETRIES <n>] |

TRANSABORT [, MAXRETRIES] [, DELAYSECS <n>| DELAYCSECS <n>] |

TRANSDISCARD | TRANSEXCEPTION }

)

[, ...];

 

该参数可以全局指定，也可以为特定的map语句指定，在具体map后指定的优先级高于全局。

REPLICAT<group>

REPERROR (<error1> , <response1>)

MAP<src1>, TARGET <tgt1>, REPERROR(<error1>, <response2>);

--出现error1，将做response2而不是reponse1

 

--遇到错误1403，则将错误记入discard文件，继续执行事务

REPERROR(-1403, discard)

 

8. COLMAP—列映射

Use COLMAPto explicitlymap source columns to target columns that have different names or to specifydefault column mapping when source and target names are identical. COLMAP providesinstructions for selecting, translating, and moving column data.

NOTETo create global rules for column mapping acrossall tables in subsequent MAP statements, use the COLMATCH parameter.

 

--字段名不同、转换、插入常量等都可以使用colmap实现

, COLMAP(USEDEFAULTS, INFO_ID = @token ("tk_ifid"), TITLE = INFO_TITLE ,C_TIME = @DATENOW () , CREATOR = "ghapp" , EDITOR = "ghapp", E_TIME = @DATENOW () , IS_VALID = 1 , STATUS = 16 , P_TIME = INFO_PUB_TIME,  CONTENT = INFO_CONTENT , CLASS_ID ="mobile");

USEDEFAULTS——字段名相同的自动匹配

 

9. TOKENS/@TOKEN

Use TOKENSto define auser token and associate it with data. Tokens enable you to extract and storedata within the user token area of a trail record header. Token data can be retrievedand used in many ways to customize the way that Oracle GoldenGate delivers

data. For example, you can use token data in column maps,stored procedures called by SQLEXEC, or macros.

To use the defined token data intarget tables, use the @TOKEN column-conversionfunction in the COLMAP clause of a Replicat MAP statement. The @TOKEN function mapsthe name of a token to a target column.

Do not use this option for tablesbeing processed in pass-through mode by a data-pump Extract group.

 

Syntax     TABLE <table spec>, TOKENS (<tokenname> = <token data> [, ...]) ;

                   @TOKEN (“<token name>”)

--EXTRACT

TABLE ora.oratest, TOKENS (

TK-OSUSER= @GETENV ("GGENVIRONMENT" , "OSUSERNAME"),

TK-HOST= @GETENV ("GGENVIRONMENT" , "HOSTNAME"));

 

--REPLICAT

mapora.oratest, target ora.oratest

, colmap(USEDEFAULTS, USER = @token ("TK-OSUSER "), HOST = @token ("TK-HOST ");

 

10. @CASE--条件判断

--如果PRODUCT_CODE为CAR就返回 A car; 如果PRODUCT_CODE为TRUCK就返回 A truck；其他则返回Avehicle。

@CASE(PRODUCT_CODE, “CAR”, “A car”, “TRUCK”, “A truck”, “A vehicle”)

 

11. @IF--条件判断

--如果amt大于0，就返回amt，否则就返回0

@IF (AMT> 0, AMT, 0)

 

12. @ EVAL--条件判断

--如果amount大于10000，则返回highamount；如果amount大于5000，则返回somewhat high；其他则返回lower。

@EVAL(AMOUNT > 10000, “high amount”, AMOUNT > 5000, “somewhat high”, “lower”)

 

13. @DATENOW ()—获取当前本地时间

时间格式为YYYY-MM-DD HH:MI:SS。

Syntax     @DATENOW ()

 

14. @DATE—以指定格式返回日期

Syntax     @DATE (“<output descriptor>”, “<inputdescriptor>”, <source col> [, “<input descriptor>”, <sourcecol>] [, ...])

 

--返回”1000-01-01 00:00:00”

@DATE("YYYY-MM-DD:HH:MI:SS","YYYYMMDDHHMISS", "10000101000000")

 

15. @GETENV--返回OGG环境

Use the @GETENV functionto return information about the Oracle GoldenGate environment.

You can use the information as input into thefollowing:

● Stored procedures or queries (with SQLEXEC)

● Column maps (with the COLMAP option of TABLE or MAP)

● User tokens (defined with the TOKENS option of TABLE andmapped to target columns by

means of the @TOKENS function)

● The GET_ENV_VALUEuserexit function (see page 505)

 

--返回当前时间

@getenv("GGHEADER", "COMMITTIMESTAMP")

 

--返回OGG的操作系统用户

@GETENV("GGENVIRONMENT", "OSUSERNAME"),

 

16. @COLTEST--列测试，一般与@IF一起用

Use the @COLTEST function toenable conditional calculations by testing for one or more column conditions.If a condition is satisfied, @COLTEST returns TRUE. To performthe conditional calculation, use the @IF function.

 

Syntax     @COLTEST (<source column>, <testitem> [, <test item>] [, ...])

-- <test item> Valid values: PRESENT, NULL, MISSING, INVALID

 

--如果ctimeb.create_time存在，则返回ctimeb.create_time，不存在则返回”1000-01-0100:00:00”

, colmap(USEDEFAULTS, create_time = @if (@COLTEST (ctimeb.create_time,PRESENT) , ctimeb.create_time, @DATE ("YYYY-MM-DD:HH:MI:SS","YYYYMMDDHHMISS", "10000101000000")))

 

17. @COLSTAT--返回状态值给列

Use the @COLSTAT function toreturn an indicator to Extract or Replicat that a column is missing, null, or invalid.The indicator can be used as part of a larger manipulation formula that usesadditional conversion functions.

 

Syntax      @COLSTAT ({MISSING | NULL | INVALID})

 

--如果price、quantity都小于0的话，order_total就为null，否则ORDER_TOTAL= PRICE * QUANTITY

ORDER_TOTAL= PRICE * QUANTITY, @IF (PRICE < 0 AND QUANTITY <0, @COLSTAT(NULL))

--上面的语句等价于

ORDER_TOTAL= @IF (PRICE > 0 AND QUANTITY > 0, PRICE * QUANTITY, @COLSTAT (NULL))

18. @STRFIND--查找字符串的起始位置

Use the @STRFINDfunction todetermine the position of a string within a string column or else return zeroif the string is not found. Optionally, @STRFINDcan accept astarting position within the string.

This function does not support NCHARorNVARCHAR data types.

 

Syntax     @STRFIND (<string>, “search string>” [,< begin position>])

 

--查找info_type中以MOBILE开头的数据

, FILTER(@STRFIND(info_type,"MOBILE") = 1)

 

Example Assuming thestring for the ACCT column is ABC123ABC, the followingare possible results.

Function statement                            Result

@STRFIND (ACCT,"23")           5

@STRFIND (ACCT,"ZZ")           0

@STRFIND (ACCT,"ABC", 2)          7 (because thesearch started at the second character)

 

 最近OGG使用的比较多，罗列了下我最近常用的参数以及函数功能，以后再慢慢更新。

Sharon

20140508

-----------------------------------------------------------------------------------------------------

转载须注明出处与链接！！！！

转载须注明出处与链接！！！！

转载须注明出处与链接！！！！

转载须注明出处与链接！！！！