OGG 技术解答


OGG 高级技术培训 雷鹏 2 计划安排 一、GoldenGate实现数据同步的原理 二、MGR进程参数详解 三、ogg目录结构及各个目录的功能 四、GoldenGate参数概览 五、GoldenGate内存处理机制 六、GoldenGate的检查点机制 七、单表同步 八、复制中断的处理 九、Logdump的使用 3 GoldenGate实现数据同步的原理 4 GoldenGate实现数据同步的原理 Source Oracle & Non-Oracle Database(s) Target Oracle & Non-Oracle Database(s) Capture: 从日志中读取已经提交的交易 5 GoldenGate实现数据同步的原理 Source Oracle & Non-Oracle Database(s) Target Oracle & Non-Oracle Database(s) Capture: 从日志中读取已经提交的交易 Trail: 存储抓取出来的交易 6 GoldenGate实现数据同步的原理 Source Oracle & Non-Oracle Database(s) Target Oracle & Non-Oracle Database(s) Capture:从日志中读取已经提交的交易 Trail: 存储抓取出来的交易 Pump: 通过网络分发数据 7 GoldenGate实现数据同步的原理 Source Oracle & Non-Oracle Database(s) Target Oracle & Non-Oracle Database(s) Capture:从日志中读取已经提交的交易 Trail:存储抓取出来的交易 Pump: 通过网络分发数据 Route: 数据可以被压缩和加密 8 GoldenGate实现数据同步的原理 Source Oracle & Non-Oracle Database(s) Target Oracle & Non-Oracle Database(s) Capture:从日志中读取已经提交的交易 Trail:存储抓取出来的交易. Pump:通过网络分发数据 Route: 数据可以被压缩和加密 Delivery: 应用队列中的数据到目标端数据 库中 9 Source Oracle & Non-Oracle Database(s) Target Oracle & Non-Oracle Database(s) Bi-directional GoldenGate实现数据同步的原理 Capture:从日志中读取已经提交的交易 Trail:存储抓取出来的交易. Pump:通过网络分发数据 Route: 数据可以被压缩和加密 Delivery: 应用队列中的数据到目标端数据 库中 10 GoldenGate实现数据同步的原理 •附加日志的作用 •数据库级别的附加日志 •最小附加日志 •表级别的附加日志 •相关数据字典视图 •OGG数据复制全景图 11 OGG数据复制全景图 Transaction Log Extract Server Collector Replicat Remote Trail Manager Manager Source Database Target Database Data Pump Local Trail Network (TCP/IP) 12 配置GoldenGate 3. Initial Load (various methods) 1. Prepare the Environment Target Database Transaction Log Source Database 2. Change Capture Extract Remote Trail Local Trail Data Pump 4. Change Delivery Replicat 13 GGSCI Commands MANAGER EXTRACT REPLICAT ER EXTTRAIL RMTTRAIL TRANDATA CHECKPOINT TABLE TRACE TABLE ADD X X X X X X X ALTER X X X X CLEANUP X X X DELETE X X X X X X X X INFO X X X X X X X X X KILL X X X LAG X X X REFRESH X SEND X X X X START X X X X STATS X X X STATUS X X X X STOP X X X X 14 GGSCI Commands (cont’d) Commands Parameters SET EDITOR, EDIT PARAMS, VIEW PARAMS Database DBLOGIN, ENCRYPT PASSWORD, LIST TABLES DDL DUMPDDL [SHOW] Miscellaneous !command, CREATE SUBDIRS, FC, HELP, HISTORY, INFO ALL, OBEY, SHELL, SHOW, VERSIONS, VIEW GGSEVT, VIEW REPORT 15 GGSCI Examples • Start a Manager process GGSCI> START MGR • Add an Extract group GGSCI> ADD EXTRACT myext, TRANLOG, BEGIN NOW • Add a local trail GGSCI> ADD EXTTRAIL /ggs/dirdat/rt, EXTRACT myext • Start an Extract group GGSCI> START EXTRACT myext 16 Using Obey Files • You can use an Obey file to perform a reusable sequence of commands • Save the commands in a text file, for example: • Then use the GGSCI OBEY command to run the file: GGSCI> OBEY .oby Note. An Obey file can have any file extension or none. START MGR ADD EXTRACT myext, TRANLOG, BEGIN NOW ADD EXTTRAIL /ggs/dirdat/rt, EXTRACT myext START EXTRACT myext 17 Change Capture - Tasks On the source system: • Add a primary Extract (reading from source transaction logs) with an associated parameter file • Optionally, add a local trail and a data pump Extract (reading from the local trail) with an associated parameter file • Add a remote trail • Start the Extract(s) 18 Change Capture - ADD EXTRACT Command Add the initial Extract checkpoint with the GGSCI command ADD EXTRACT: ADD EXTRACT , , [, ] The components of this command are discussed in subsequent slides. 19 Change Capture - ADD EXTRACT Source (and when used) SOURCEISTABLE Database table (initial data load) TRANLOG [] Transaction log (change capture) [DB2 z/OS] EXTFILESOURCE Extract file (data pump) EXTTRAILSOURCE Trail (data pump) 20 Change Capture - ADD EXTRACT Database BEGIN {NOW | } Any EXTSEQNO , EXTRBA Oracle, SQL/MX EXTRBA DB2 z/OS EOF | LSN DB2 LUW LSN SQL Server, Ingres LOGNUM , LOGPOS c-tree PAGE , ROW Sybase 21 Change Capture - ADD EXTRACT Specifies DESC “” Description of Extract group THREADS Number of redo threads when extracting from an Oracle RAC clustered database PARAMS Alternative parameter file name (fully qualified) PASSTHRU Used only in Data Pumps. Passes the data through without any transformation. REPORT Alternative report file name (fully qualified) 22 Change Capture - ADD EXTRACT Examples • Create an Extract group named “finance” that extracts database changes from the transaction logs. Start extracting with records generated at the time when you add the Extract group. ADD EXTRACT finance, TRANLOG, BEGIN NOW • Create an Extract group named “finance” that extracts database changes from the transaction logs. Start extracting with records generated at 8:00 on January 31, 2006. ADD EXTRACT finance, TRANLOG, BEGIN 2006-01-31 08:00 • Create a data-pump Extract group named “finance” that reads from the GoldenGate trail c:\ggs\dirdat\lt. ADD EXTRACT finance, EXTTRAILSOURCE c:\ggs\dirdat\lt • Create an initial-load Extract named “load”. ADD EXTRACT load, SOURCEISTABLE 23 Change Capture - Edit Extract Parameters • Create/edit an Extract parameter file with the GGSCI command: EDIT PARAMS EXTRACT ODS USERID GoldenUser, PASSWORD password RMTHOST serverx, MGRPORT 7809 RMTTRAIL /ggs/dirdat/rt TABLE SALES.ORDERS; TABLE SALES.INVENTORY; 24 Change Capture - Add a Local/Remote Trail • Add a local or remote trail with the GGSCI command: ADD EXTTRAIL | RMTTRAIL , EXTRACT [, MEGABYTES ] • If using a data pump: – The primary Extract needs a local trail (EXTTRAIL) – The data pump Extract needs a remote trail (RMTTRAIL) Examples: ADD EXTTRAIL c:\ggs\dirdat\aa, EXTRACT finance, MEGABYTES 10 ADD RMTTRAIL c:\ggs\dirdat\bb, EXTRACT parts, MEGABYTES 5 25 Change Capture - Start Extract • Start an Extract process with the GGSCI command: START EXTRACT • If the output trail is remote, this normally triggers the target Manager process to start a Server Collector process with default parameters • Users can start a Server Collector statically and modify the parameters, though rarely used. See the Oracle GoldenGate Reference Guide. 26 Change Capture – Primary Extract Configuration (Oracle) GGSCI> EDIT PARAMS ODS EXTRACT ODS USERID GoldenUser, PASSWORD password EXTTRAIL /ggs/dirdat/rt TABLE SALES.ORDERS; TABLE SALES.INVENTORY; GGSCI> ADD EXTRACT ODS, TRANLOG, BEGIN NOW GGSCI> ADD EXTTRAIL /ggs/dirdat/rt, EXTRACT ODS GGSCI> START EXTRACT ODS Source Database Transaction Log Extract Trail /ggs/dirdat/rt000000 /ggs/dirdat/rt000001 27 Change Capture – Primary Extract Configuration (DB2 and SQL Server) GGSCI> EDIT PARAMS ODS EXTRACT ODS SOURCEDB dsn, USERID login, PASSWORD pw EXTTRAIL /ggs/dirdat/rt TABLE SALES.ORDERS; TABLE SALES.INVENTORY; GGSCI> ADD EXTRACT ODS, TRANLOG, BEGIN NOW GGSCI> ADD EXTTRAIL ./dirdat/rt, EXTRACT ODS GGSCI> START EXTRACT ODS /ggs/dirdat/rt000000 /ggs/dirdat/rt000001 Extract Trail Source Database Transaction Log 28 Data Pumps - Overview • Data is stored in a local trail on the source system • A second Extract, the data pump: – Reads this trail and sends it to one or more targets – Manipulates the data or passes it through without change • Reasons for using – A safeguard against network and target failures – To break complex data filtering and transformation into phases – To consolidate data from many sources – To synchronize one source with multiple targets 29 Data Pumps – One to Many Trails Primary Extract Trail Data Pump Trail Trail Trail 30 Data Pumps – One to Many Target Systems Primary Extract Trail Data Pump 2 Trail Trail Trail Data Pump 1 Data Pump 3 31 Data Pumps - Configuration • Primary Extract parameters specify a local trail: EXTRACT EXTTRAIL ./dirdat/ ; • Data pumps are often configured for pass-through: EXTRACT PASSTHRU RMTHOST , MGRPORT RMTTRAIL ./dirdat/
; • Add a data pump (source is the local trail from the primary Extract ) ADD EXTRACT , EXTTRAILSOURCE ./dirdat/ 32 Initial Load • Or database-specific methods like: – Backup/Restore – Export/Import – SQL scripts – Break mirror – Transportable tablespaces (Oracle) Notes: – Run a test initial load early on for timing and sizing – Run the actual initial load after starting change capture on the source GoldenGate method Extract writes to Load method File to Replicat Trail (GoldenGate format) Replicat via SQL File to database utility Formatted text file Database utility Direct load Replicat (directly) Replicat via SQL Direct bulk load Replicat (directly) Replicat via SQL*Loader API . Can use GoldenGate TDM methods: 33 Initial Load: File to Replicat ADD EXTRACT Extract parameters: SOURCEISTABLE RMTTRAIL Files Manager Extract Replicat Source Database Target Database 34 Initial Load: File to Database Utility ADD EXTRACT Extract parameters: SOURCEISTABLE RMTFILE FORMATASCII BCP or SQLLOADER SQL* Loader BCP SSIS File File File Manager Extract Source Database Target Database 35 Initial Load: Direct Load Manager Extract Replicat Manager ADD EXTRACT , SOURCEISTABLE Extract parameters: RMTTASK REPLICAT, GROUP ADD REPLICAT , SPECIALRUN Source Database Target Database 36 Initial Load: Direct Bulk Load (to Oracle) Manager SQL*Loader API Manager ADD EXTRACT , SOURCEISTABLE Extract parameters: RMTTASK REPLICAT, GROUP ADD REPLICAT , SPECIALRUN Replicat parameters: BULKLOAD Extract Replicat Source Database Source Database Oracle Target 37 Change Delivery - Replicat Overview Replicat can: • Read data out of GoldenGate trails • Perform data filtering – Table, row, operation • Perform data transformation • Perform database operations just as your application performed them 38 Change Delivery - Tasks On the target system: – Create a checkpoint table in the target database (best practice) • DBLOGIN • ADD CHECKPOINTTABLE – Create a parameter file for Replicat • EDIT PARAMS – Add your initial Replicat checkpoint into GoldenGate trails • ADD REPLICAT – Start the Replicat process • START REPLICAT 39 Change Delivery – Sample Oracle Configuration GGSCI> DBLOGIN SOURCEDB mydb USERID login PASSWORD pw GGSCI> ADD CHECKPOINTTABLE ggs.checkpt GGSCI> EDIT PARAMS REPORD REPLICAT REPORD TARGETDB dsn USERID ggsuser PASSWORD ggspass -- USERID ggsuser, PASSWORD ggspass ASSUMETARGETDEFS DISCARDFILE /ggs/dirrpt/REPORD.dsc, APPEND MAP SALES.ORDERS, TARGET SALES.ORDERS; MAP SALES.INVENTORY, TARGET SALES.INVENTORY; GGSCI> ADD REPLICAT REPORD, EXTTRAIL /ggs/dirdat/rt GGSCI> START REPLICAT REPORD Replicat Trail Target Database 40 Change Delivery - Avoiding Collisions with Initial Load • If the source database remains active during an initial load, you must either avoid or handle any collisions when updating the target with interim changes • Avoiding Collisions If you can backup/restore or clone the database at a point in time, you can avoid collisions by starting Replicat to read trail records from a specific transaction Commit Sequence Number (CSN): START REPLICAT ATCSN | AFTERCSN 41 Change Delivery - Handling Collisions with Initial Load • If you cannot avoid collisions by the prior method, you must handle collisions • Use the Replicat HANDLECOLLISIONS parameter: – When Replicat encounters a duplicate-record error on an insert, it writes the change record over the initial data load record – When Replicat encounters a missing-record error for an update or delete, the change record is discarded 42 Data Selection - Overview • GoldenGate provides the ability to select or filter out data based on a variety of levels and conditions Parameter / Clause Selects TABLE or MAP Table WHERE Row FILTER Row, Operation, Range TABLE COLS | COLSEXCEPT Columns 43 Data Selection – WHERE Clause • The WHERE clause is the simplest form of selection • WHERE clause appears on either the MAP or TABLE parameter and must be surrounded by parenthesis • WHERE clause cannot: – perform arithmetic operations – refer to trail header and user token values • Use the FILTER clause for more complex selections with built-in functions 44 Data Selection – WHERE Clause WHERE can perform an evaluation for: Element Description Example Columns PRODUCT_AMT Comparison operators =, <>, >, <, >=, <= Numeric values -123, 5500.123 Literal strings "AUTO", "Ca" Field tests @NULL,@PRESENT,@ABSENT Conjunctive operators AND, OR 45 Data Selection – WHERE Clause Examples • Only rows where the state column has a value of CA are returned. WHERE (STATE = “CA”); • Only rows where the amount column has a value of NULL. Note that if amount was not part of the update, the result is false. WHERE (AMOUNT = @NULL); • Only rows where the amount was part of the operation and it has value that is not null. WHERE (AMOUNT @PRESENT AND AMOUNT <> @NULL); • Only rows where the account identifier is greater than CORP-ABC. WHERE (ACCOUNT_ID > “CORP-ABC”); 46 Selection – FILTER Clause • The FILTER clause provides complex evaluations to include or exclude data selection • FILTER clause appears on either the MAP or TABLE parameter and must be surrounded by parenthesis • With FILTER you can: – Deploy other GoldenGate built-in functions – Use multiple FILTERs on one statement • If any filter fails, the entire filter clause fails – Include multiple option clauses, for example (on insert/ update) – Raise a user-defined Error for exception processing 47 Data Selection – FILTER Clause Syntax FILTER (
[, TARGET
] [, DEF ] [, TARGETDEF ] [, COLMAP ()] [, {COLS | COLSEXCEPT} ()] [, EVENTACTIONS ] [, EXITPARAM “”] [, FETCHBEFOREFILTER] [, {FETCHCOLS | FETCHCOLSEXCEPT} (column specification)] [, {FETCHMODCOLS | FETCHMODCOLSEXCEPT} ()] [, FILTER ()] [, KEYCOLS ()] [, SQLEXEC ()] [, SQLPREDICATE “WHERE ”] [, TOKENS ()] [, TRIMSPACES | NOTRIMSPACES] [, WHERE ()] ; Note: You must use a semicolon to terminate the TABLE statement. 85 Extract – TRANLOGOPTIONS 参数 • 用于控制从什么地方读取日志,定义读取日志的格式 • Examples: Controlling the archive log TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT log_%t_%s_%r.arc 描述日志格式 TRANLOGOPTIONS ALTARCHIVELOGDEST /oradata/archive/log2 指定日志读取的位置 TRANLOGOPTIONS ARCHIVEDLOGONLY 定义只读取归档的模式 86 Extract – TRANLOGOPTIONS参数 • Examples: Loop prevention TRANLOGOPTIONS EXCLUDEUSER ggsrep 定义指定用户的操作不进行捕获 TRANLOGOPTIONS EXCLUDETRANS “ggs_repl” 定义指定名称的交易不进行捕获 TRANLOGOPTIONS FILTERTABLE 对检查点表做的操作不进行年捕获 87 Replicat 参数概览 Replicat 参数: • 进程名 – 和一个检查点文件关联 • 定义复制关系 – 用户对用户 – 表对表 – 随意的匹配字段 – 执行转换操作 – 可以执行SQL操作或是存储过程 • 错误处理 88 Replicat 参数 所有Replicat进程参数的默认状态: • 复制所有增、删、改操作 • 智能分组 – 100 个源端的操作被分进一个组里 • 发生故障的时候进程马上停止 – 回退操作到最近的检查点 – 可选的错误出来 – 自定义异常处理 89 Sample Replicat Parameter File REPLICAT SALESRPT USERID ggsuser, PASSWORD ggspass ASSUMETARGETDEFS DISCARDFILE /ggs/dirrpt/SALESRPT.dsc, APPEND MAP HR.STUDENT, TARGET HR.STUDENT WHERE (STUDENT_NUMBER < 400000); MAP HR.CODES, TARGET HR.CODES; MAP SALES.ORDERS, TARGET SALES.ORDERS, WHERE (STATE = “CA” AND OFFICE = “LA”); 90 Replicat Parameters Purpose Examples General SETENV, GETENV, OBEY Processing method BEGIN, END, SPECIALRUN Database login SOURCEDB, USERID Selecting, converting and mapping data COLMATCH, IGNOREUPDATES, MAP, SOURCEDEFS, ASSUMETARGETDEFS Routing data EXTFILE, EXTTRAIL Custom processing CUSEREXIT, DEFERAPPLYINTERVAL, INCLUDE, MACRO, SQLEXEC Reporting REPORT, REPORTCOUNT, STATOPTIONS Error handling DISCARDFILE, OVERRIDEDUPS, HANDLECOLLISIONS Tuning ALLOCFILES, BATCHSQL, GROUPTRANSOPS, DBOPTIONS Maintenance PURGEOLDEXTRACTS, REPORTROLLOVER Security DECRYPTTRAIL 91 Replicat – MAP Parameter MAP
, TARGET
[, DEF ] [, TARGETDEF ] [, COLMAP ()] [, EVENTACTIONS ] [, EXCEPTIONSONLY] [, EXITPARAM “”] [, FILTER ()] [, HANDLECOLLISIONS | NOHANDLECOLLISIONS] [, INSERTALLRECORDS] [, INSERTAPPEND | NOINSERTAPPEND] [, KEYCOLS ()] [, REPERROR ( , )] [, SQLEXEC ()] [, TRIMSPACES | NOTRIMSPACES] [, WHERE ()] ; Note: You must use a semicolon to terminate the MAP statement. 92 GoldenGate内存机制 93 内存管理机制 使用CACHEMGR来进行内存的控制,使用这个参数来控制未提交的事 务对虚拟内存和临时磁盘空间的占有。GoldenGate会根据实际的情况来 进行自适应和调整,一般不建议调整。 因为GoldenGate只会对提交的交易进行处理,未提交的交易存储在内存 单元中,内存单元的分配受CACHEMGR的控制。 每一个log reader使用一个子池来存储最多可以容纳的交易的数据 一个子池用来处理BLOG等大数据 参数说明: CACHEMGR { [, CACHESIZE ] [, CACHEDIRECTORY [] [, ...]] [, CACHEPAGEOUTSIZE ] } CACHEMGR CACHESIZE 500MB, CACHEDIRECTORY /ggs/temp 2GB, CACHEDIRECTORY /ggs2/temp 2GB 94 GoldenGate检查点机制 95 Checkpointing 95 96 Checkpointing - Extract • For change data capture, Extract and Replicat save checkpoints to a checkpoint file so they can recover in case of failure • Extract maintains – 2 input checkpoints – 1 output checkpoint for each trail it writes to Checkpoints Output: One or more GoldenGate Trails Input: Transaction Log Last record read from log Start of oldest uncommitted transaction in log End of last committed transaction written to trail 96 97 Checkpointing - Replicat • Best practice is to create a checkpoint table in the target database • Checkpoints are maintained in both the checkpoint table (if it exists) and a checkpoint file • Replicat maintains 2 input checkpoints Checkpoints Input: GoldenGate Trail Last record read from trail Start of current uncommitted transaction 97 98 GoldenGate单表同步 99 出现复制延迟或失败的处理,可以对部分表进 行单表同步 单表同步的步骤: 1、停止GoldenGate的replicate进程 2、编辑replicat的进程,增加参数排除需要同步的表 mapexclude 3、启动replicate进程 3、建立一个新的replicate,只同步排除的表 4、源端对排除的表基于scn进行导出 SQL> select min(START_TIME) from gv$transaction; SQL>col scn format 99999999999999999999 SQL>select dbms_flashback.get_system_change_number scn from dual; exp "'ogg/ogg'" owner= file=NMSI_HIN.dmp log=NMSI_HIN.log direct=y recordlength=65535 itriggers=y constraints=y statistics=none compress=n flashback_scn=10177211698471 5、 100 出现复制延迟或失败的处理,可以对部分表进 行单表同步 单表同步的步骤: 5、执行导入 imp "'ogg/ogg'" fromuser=nmsi_hin touser=nmsi_bin buffer=10240000 file=nmsi_bin.dmp log=nmsi_bin_imp.log 6、基于scn启动新建立的rep进程 7、等两个进程追平后,合并两个进程 101 GoldenGate复制中断的处理 102 复制中断的处理,可以配置忽略错误 引起复制中断的原因? 配置忽略错误的方法: Extract进程:更改时间点或是队列的位置 Replicate进程:增加冲突处理的参数 HANDLECOLLISIONS 或 REPERROR (DEFAULT, ABEND) REPERROR (-1, IGNORE) 103 如果在源端执行了DDL如何操作可以避免复制队列中断 只复制DML的时候,这个问题无法避免。 举例:在GoldenGate运行的时候,源端更改表的结构,会引起目标端 replicate进程异常终止。 分两种情况: 一种是表有主键; 另一种是无主键的表 避免的方式就是配置DDL复制 104 目标端的进程在什么情况下会停止, 如何配置遇到问题只是在日志中提示 1、表的结构定义不一致的时候 2、两端数据不一致的时候 3、复制DDL执行失败的时候 配置参考: DISCARDFILE [, APPEND | PURGE] [, MAXBYTES | MEGABYTES ] REPERROR (DEFAULT, DISCARD) 105 OGG对大事物处理机制,如何跳过大事务 处理建议 1、分开不同的复制链路进行数据复制 2、在目标端投递数据的时候,对大的数据库事务通过参数进行拆分 MAXTRANSOPS 10000 默认是100万事务 3、通过range的方式对大表进行拆分,增加并行度 MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (1, 3, ID)); 跳过大事务 源端:send ext1 ,showtrans,检查存在的长事务,记录下XID send ext1, SKIPTRANS [THREAD ] 目标端:使用logdump工具,跳过大事务 106 查找该事务正在运行的sql: select a.program,a.SID,b.spid,c.sql_text from v$session a,v$process b,v$sqlarea c where a.paddr=b.addr and a.sql_hash_value=c.hash_value and a.username is not null 一般这种情况都需要建立合适的索引 怎么查询并处理当前入库缓慢的问题 107 • Divides workload into multiple, randomly distributed groups of data • Guarantees the same row will always be processed by the same process • Determines which group that range falls in by computing a hash against the primary key or user defined columns Syntax @RANGE (, [, [, ...]]) Example TABLE SALES.ACCOUNT, FILTER (@RANGE (1,3)); Data Selection – RANGE Function 108 • For transaction volume beyond the capacity of a single Replicat, the example below shows three Replicat groups, each processing one-third of the data • Hashing each operation by primary key to a particular Replicat guarantees the original sequence of operations Replicat #1 MAP SALES.ACCOUNT, TARGET SALES.ACCOUNT, FILTER (@RANGE (1,3)); Replicat #2 MAP SALES.ACCOUNT, TARGET SALES.ACCOUNT, FILTER (@RANGE (2,3)); Replicat #3 MAP SALES.ACCOUNT, TARGET SALES.ACCOUNT, FILTER (@RANGE (3,3)); Data Selection – RANGE Function Examples 109 • Two tables, REP and ACCOUNT, related by REP_ID, require three Replicats to handle the transaction volumes • By hashing the REP_ID column, related rows will always be processed to the same Replicat RMTTRAIL /ggs/dirdat/aa TABLE SALES.REP, FILTER (@RANGE (1,3)); TABLE SALES.ACCOUNT, FILTER (@RANGE (1,3,REP_ID)); RMTTRAIL /ggs/dirdat/bb TABLE SALES.REP, FILTER (@RANGE (2,3)); TABLE SALES.ACCOUNT, FILTER (@RANGE (2,3,REP_ID)); RMTTRAIL /ggs/dirdat/cc TABLE SALES.REP, FILTER (@RANGE (3,3)); TABLE SALES.ACCOUNT, FILTER (@RANGE (3,3,REP_ID)); Data Selection – RANGE Function Examples 110 LOGDUMP的使用 111 Logdump • The Logdump utility allows you to: – Display or search for information that is stored in GoldenGate trails or files – Save a portion of a GoldenGate trail to a separate trail file 111 112 Logdump – Starting and Getting Online Help To start Logdump - from the GoldenGate installation directory: Shell> logdump To get help: Logdump 1 > help The Logdump utility is documented in the: Oracle GoldenGate Troubleshooting and Tuning Guide 112 113 Logdump – Opening a Trail Logdump> open dirdat/rt000000 Logdump responds with: Current LogTrail is /ggs/dirdat/rt000000 113 114 • To view the trail file header: Logdump 1> fileheader on • To view the record header with the data: Logdump 2> ghdr on • To add column information: Logdump 3> detail on • To add hex and ASCII data values to the column list: Logdump 4> detail data • To control how much record data is displayed: Logdump 5> reclen 280 Logdump – Setting up a View 114 115 Logdump – Viewing the Trail File Header fileheader [ on | detail ] displays the file header: Logdump 14662 >fileheader detail Logdump 14663 >pos 0 Reading forward from RBA 0 Logdump 14664 >n TokenID x46 'F' Record Header Info x00 Length 587 TokenID x30 '0' TrailInfo Info x00 Length 303 TokenID x31 '1' MachineInfo Info x00 Length 103 TokenID x32 '2' DatabaseInfo Info x00 Length 88 TokenID x33 '3' ProducerInfo Info x00 Length 85 TokenID x34 '4' ContinunityInfo Info x00 Length 4 TokenID x5a 'Z' Record Trailer Info x00 Length 587 2008/07/18 13:40:26.034.631 FileHeader Len 587 RBA 0 Name: *FileHeader* 3000 012f 3000 0008 660d 0a71 3100 0006 0001 3200 | 0../0...f..q1.....2. 0008 0000 0016 3300 000c 02f1 7834 eac7 7f3f 3400 | ......3.....x4...?4. 0037 0031 7572 693a 7465 6c6c 7572 6961 6e3a 3a68 | .7.1uri:tellurian::h 6f6d 653a 6d63 6361 7267 6172 3a67 6773 3a67 6773 | ome:mccargar:ggs:ggs 4f72 6163 6c65 3a73 6f75 7263 6536 0000 1700 112e | Oracle:source6...... 2f64 6972 6461 742f 6572 3030 3030 3030 3700 0005 | /dirdat/er0000007... 0138 0000 0800 01e2 4039 0000 0c00 0000 0000 001d | .8......@9.......... GroupID x30 '0' TrailInfo Info x00 Length 303 3000 012f 3000 0008 660d 0a71 3100 0006 0001 3200 | 0../0...f..q1.....2. etc. 115 116 • To go to the first record, and to move from one record to another in sequence: Logdump 6 > pos 0 Logdump 7 > next (or just type n) • To position at an approximate starting point and locate the next good header record: Logdump 8 > pos Logdump 9 > scanforheader (or just type sfh) Logdump – Viewing Trail Records 116 117 Column information Record data, in hex Record data, in ASCII Source table Operation type and time the record was written Image type: could be a before or after image Record header: contains transaction information. Below the header is the data area. Length of record and its RBA position in the trail file I/O type Logdump – Viewing Trail Records 117 118 Logdump> count LogTrail /ggs/dirdat/rt000000 has 4828 records Total Data Bytes 334802 Avg Bytes/Record 69 Delete 900 Insert 3902 FieldComp 26 Before Images 900 After Images 3928 Average of 25 Transactions Bytes/Trans ..... 22661 Records/Trans ... 193 Files/Trans ..... 8 Logdump - Counting the Records in the Trail 118 119 TCUSTMER Total Data Bytes 10562 Avg Bytes/Record 55 Delete 300 Insert 1578 FieldComp 12 Before Images 300 After Images 1590 TCUSTORD Total Data Bytes 229178 Avg Bytes/Record 78 Delete 600 Insert 2324 Field Comp 14 Before Images 600 After Images 2338 Logdump – Counting Records in the Trail (cont’d) 119 120 Logdump – Filtering on a Filename Logdump 7 >filter include filename TCUST* Logdump 8 >filter match all Logdump 9 >n ________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x00) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 56 (x0038) IO Time : 2002/04/30 15:56:40.814 IOType : 5 (x05) OrigNode : 108 (x6c) TransInd : . (x01) FormatType : F (x46) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 105974056 2002/04/30 15:56:40.814 Insert Len 56 Log RBA 1230 File: TCUSTMER Partition 0 After Image: 3220 2020 4A61 6D65 7320 2020 2020 4A6F 686E 736F | 2 James Johnso 6E20 2020 2020 2020 2020 2020 2020 4368 6F75 6472 | n Choudr 616E 7420 2020 2020 2020 2020 2020 4C41 | LA Filtering suppressed 18 records 120 121 Logdump 27 >filter inc hex /68656C20/ Logdump 28 >pos 0 Current position set to RBA Logdump 29 >n __________________________________________________________ Hdr-Ind : E (x45) Partition : . (x00) UndoFlag : . (x00) BeforeAfter: B (x42) RecLength : 56 (x0038) IO Time : 2002/04/30 16:22:14.205 IOType : 3 (x03) OrigNode : 108 (x6c) TransInd : . (x01) FormatType : F (x46) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 109406324 2002/04/30 16:22:14.205 Delete Len 56 Log RBA 64424 File: TCUSTMER Partition 0 Before Image: 3620 2020 4A61 6D65 7320 2020 2020 4A6F 686E 736F | 6 James Johnso 6E20 2020 2020 2020 2020 2020 2020 4574 6865 6C20 | n Ethel 2020 2020 2020 2020 2020 2020 2020 4C41 | LA Filtering suppressed 545 records Logdump – Locating a Hex Data Value 121 122 OGG-01091("unable to open file/dirdat/i1000047")错误处理方法 通常遇到这类问题的时候,系统都会提示如何的方法,一般提示如下: ALTER DPE1,etrollover INFO DPE1,DETAIL 核心提示信息: seqno 10,extrba 0 Alter rep1,extseqno 10,extrba 0 123
还剩122页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享pdf获得金币 ] 3 人已下载

下载pdf

pdf贡献者

hucan1987

贡献于2015-04-21

下载需要 8 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!