常用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/,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/,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> [, ...]) ;
, 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 = )
--目标端执行sql获得id，映射到列
, sqlexec(id icid , query "select GUARD.SEQ_CMSBIZTAG_ID.nextval id fromdual", noparams)
, colmap(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 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/
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
useridggs, password ggs
tableGH.APP_INFO;
2. 运行（OGG安装目录下运行defgen命令）
defgen paramfile dirprm/[reportfile dirrpt/] [NOEXTATTR] [UPDATECS UTF-8]
默认DEFGEN使用本地locale写def文件，可使用defgen修改def文件字符集
defgen paramfile ./dirdef/ UTF-8
3. 传输
默认以ASCII模式FTP到remote，如果def文件以 remote的字符集创建，则使用binary模式，避免出现不必要的换行；
2). 使用SOURCEDEFS定义的表不能复制DDL操作
否则报错：
ERROR OGG-00513 Oracle GoldenGate Delivery for Oracle, : 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\
*, TARGET *, DEF custdef;
, TARGET ;
, TARGET ;
, TARGET ;
, TARGET ;
ASSUMETARGETDEFS
, TARGET ;
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 , TOKENS (
TK-OSUSER= @GETENV ("GGENVIRONMENT" , "OSUSERNAME"),
TK-HOST= @GETENV ("GGENVIRONMENT" , "HOSTNAME"));
--REPLICAT
, target
, 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 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 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
-----------------------------------------------------------------------------------------------------
转载须注明出处与链接！！！！
转载须注明出处与链接！！！！
转载须注明出处与链接！！！！
转载须注明出处与链接！！！！