配置GoldenGate意外处理及记录Oracle错误信息(1)

GoldenGate自身不提供异常处理的程序。在默认情况下,若Replicat进程遭遇操作故障都会导致其异常终止(ABEND),同时将会回滚事务到最近的检查点。在生产环境中这往往并不理想。当我们在做无缝的数据迁移时会用到HANDLECOLLISIONS和NOHANDLECOLLISIONS参数,这2个参数控制了Replicat是否会试图解决重复记录和缺失记录的错误,但这错误真的应该被忽略吗?这个问题只有熟悉应用的维护人员才能解答,我们需要做的是在出现这类错误后及时记录错误的相关信息。

这就要求我们在Replicat中配置相关的意外处理程序,该程序用以记录是哪个Replicat进程,是那些数据引起了错误的产生。

接下来我们会尝试建立不同的意外处理程序,用以捕获复制过程中Oracle出现的相关错误,但在这些故障发生后我们允许Replicat继续它的工作:

1. 首先我们要做的是创建用以记录错误相关信息的错误记录表:
SQL> conn maclean/maclean
Connected.
SQL> create table exception_log
2  ( replicat_name varchar2(10),
3    table_name varchar2(100),
4    errno number,
5    dberrmsg varchar2(4000),
6    optype varchar2(20),
7    errtype varchar2(20),
8    logrba number,
9    logposition number,
10    committimestamp timestamp);
Table created.
SQL> alter table exception_log add primary key (logrba,logposition,committimestamp);
Table altered.
以上意外处理记录表应建立于Goldengate管理员账户名下,可用以记录所有Replicat的意外数据。
2.修改各Replicat的参数文件添加相关的意外处理程序:
GGSCI (rh3.oracle.com) 59> view params rep1
replicat rep1
userid maclean,password maclean
ASSUMETARGETDEFS
discardfile /s01/discard/rep1.log,append,megabytes 10
REPERROR (DEFAULT, EXCEPTION)
REPERROR (DEFAULT2,ABEND)
map defs.tbc, target defs.tbc;
map defs.tbc, target maclean.exception_log, 
EXCEPTIONSONLY,
INSERTALLRECORDS,
COLMAP (   replicat_name = "rep1"
, table_name = @GETENV ("GGHEADER", "TABLENAME")
, errno = @GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = @GETENV ("LASTERR", "DBERRMSG") 
, optype = @GETENV ("LASTERR", "OPTYPE")
, errtype = @GETENV ("LASTERR", "ERRTYPE")
, logrba = @GETENV ("GGHEADER", "LOGRBA")
, logposition = @GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP"));
REPERROR参数用以控制Replicat进程如何响应映射过程中发生的错误
DEFAULT参数代表一种全局错误类型,即除去所有已明确指定的错误外的一切错误
DEFAULT2参数代表当DEFAULT错误以Exception方式响应时,所有MAP映射中未定义Exception部分出现的所有错误
3.停止并重启Replicat 进程
GGSCI (rh3.oracle.com) 60> stop rep1
Sending STOP request to REPLICAT REP1 ...
Request processed.
GGSCI (rh3.oracle.com) 61> start rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
4.以上完成了对单表defs.tbc意外处理的配置,接着我们可以启动相关的应用了
5.通过某些手动篡改数据或不同步操作,可以很容易地触发意外处理而将相关错误信息记录到我们的exception_log表中,我们可以来查看
相关记录:
SQL> col dberrmsg for a1;
SQL> col table_name for a10;
SQL> select * from exception_log;
REPLICAT_NAME TABLE_NAME      ERRNO D OPTYPE               ERRTYPE                  LOGRBA LOGPOSITION COMMITTIMESTAMP
------------- ---------- ---------- - -------------------- -------------------- ---------- ----------- ---------
rep1          DEFS.TBC         1403   PK UPDATE            DB                          231    59259920 23-DEC-10 1
rep1          DEFS.TBC         1403   PK UPDATE            DB                          231    59260812 23-DEC-10 1
rep1          DEFS.TBC         1403   PK UPDATE            DB                          231    94620688 23-DEC-10 1
rep1          DEFS.TBC         1403   PK UPDATE            DB                          231    94621580 23-DEC-10 1
rep1          DEFS.TBC         1403   PK UPDATE            DB                          231    94682640 23-DEC-10 1
rep1          DEFS.TBC         1403   PK UPDATE            DB                          231    94683532 23-DEC-10 1
6 rows selected
可以看到以上为基于主键更新时出现了1403 "no data found"的Oracle常规错误。
意外处理程序所能记录的相关信息还不于止此,我们还可以用它来记录如Update操作的前后数据镜像,这些信息可以在冲突解决时派上用场。

Know more about DML

The change vectors as constructed by the transaction layer are used by the cache layer to apply the physical block changes.

Redo log ordering and recovery as well as read consistency are the fundamental purposes of SCNs.

The change vector:
Change vectors are built in the PGA process of the process modifying a block, have the DBA and SCN/SEQ of the block before the change, and only ever perform the change for one block.

The system commit number:
The SCN is a 6-byte structure consisting of the SCN base and SCN wrap.
Is a 6-byte structure consisting of the SCN base and SCN wrap, 4 bytes for the SCN base and 2 bytes for the SCN wrap

The SCN is used within the data block to reflect the committed version of the block.
There is only one global SCN generator for each database.
A large transaction is defined as a transaction that is participating in a parallel DML (PDML) operation or a distributed transaction.
One of the first checks for rollback segment allocation is whether the transaction is participating in a PDML or distributed transaction. These are used to determine the largest rollback segment to be allocated.

steps below to illustrate the order necessary to begin a transaction.

1.bind to the available rollback segment
2.allocate a slot in the transaction table
3.allocate an undo block to the rollback segment

When a free slot cannot be allocated in the transaction table, exclusive access is acquired in the rollback segment header.
Only when a free slot can be allocated in the transaction table do you gain exclusive access to the RBS header.

The system rollback segment will be used only for data for the system tablespace and data that is being created from recursive SQL calls generated by user data.

The search for the allocation of an undo block to a rollback segment will occur in:
Searching for an undo block to be allocated will begin in the current extent if the extent boundary has not been reached, in the next extent provided there are no uncommitted transactions, or in a newly allocated extent if the next extent has uncommitted data.

Rollback segments are shrunk either explicitly through the ’alter rollback segment … shrink to …’ command or implicitly by the SMON process. Implicit shrinkage can be disabled by:
PCTINCREASE has no affect on rollback segments. All extents will be sized by the setting of the next_extent storage attribute.
Setting the event 10512 will disable the implicit shrink operation performed by SMON.
Setting the initialization parameter ‘rbs_disable_shk’. There is no such parameter. Setting the event 10512 will disable the implicit shrinkage operation performed by SMON.

The Oracle server begins shrinking a rollback segment starting from the current extent +2

Rollback segment shrinkage will occur while optimal or minextents is not reached, there are at least two remaining extents, the extent is not busy, and the extent is not extent #0.

An extent is reused if all its blocks are inactive. RBS undo blocks are reused on a per extent basis. An undo block becomes inactive when the active transaction in the undo block commits.

correct order of Interested Transaction List (ITL) allocation.

1. Find an  Unused ITL
2. FInd ITL of oldest commited TX
3. DO a partial block cleanout that stops when an ITL becomes available
4. Extend ITL area

The variable portion of the transaction layer is dictated by the MAXTRANS parameter.
The INITRANS parameter dictates the number of ITL’s and hence the variable portion of the Transaction Layer.

An ITL must be acquired by any transaction wishing to modify a block. If an ITL is unavailable and there is no space left in the block, a transaction will wait.

A transaction that currently has an ITL within a block cannot update any row in the block if the required row is locked by a previous transaction. That transaction will wait on the TX enqueue.When attempting to update a row already held by a previous transaction, a transaction will wait on the TX enqueue in exclusive mode.

The free space credit field of the transaction layer maintains the space as freed for a given transaction, and that is returned on commit of the transaction.

When a transaction is unable to allocate an ITL within a data block for which it needs to modify a block, the transaction will:
That transaction will wait on the TX enqueue in share mode until an ITL becomes available within the block where the modification is required.

The Oracle server maintains the row header flag and lock byte (total 2 bytes) for the deleted row. It is necessary to keep these first two bytes to maintain the row lock. This is referred to as stub space.

The correct statistic Rollback changes – undo records applied is incremented for each link in the undo record chain.

The Data Block Address (DBA) in the Transaction Table slot indicates:
The beginning of the undo chain for the transaction  Correct! The DBA in the Transaction Table slot is the starting point for rolling back a transaction

the correct sequence of events at the COMMIT of a transaction.

1. FIND an scn value
2. update the transaction table slot with the scn
3. create a commit record in the redo log buffer
4. flush the redo log buffer to disk
5. release locks held on the rows and tables

The application of the undo to the data block is performed by the
The data layer ,and the transaction layer do not have the right to perform physical block-level changes.

沪ICP备14014813号

沪公网安备 31010802001379号