OGG ora-01403錯誤怎么處理

這篇文章給大家分享的是有關OGG  ora-01403錯誤怎么處理的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

OGG運維中有一個經典錯誤-1403。現象是目標端復制update或者delete操作導致復制進程abended，原因是update或delete時找不到目標數據。至于該數據為什么不在目標端有很多可能，比如人為刪除、trigger沒有禁用導致刪除、級聯外鍵刪除沒有禁用導致刪除等等。通常我們的排查手段是確認目標端的trigger、級聯外鍵刪除、job是否啟動了？如果啟動了禁用它。然后再排查源端表是否有主鍵，主鍵在trandata中是否生效。上述排查都沒有問題的話就開始做表級初始化吧，數據泵導出導入，同步變化...

但是有時候我們也可以不這么折騰，可以采取“補缺”的方式讓復制進程迅速恢復。思路如下：

1. 通過目標端ggserr日志和replcat.dsc文件來定位丟失的數據

2. 在源端使用database link執行insert into 目標端 select * from 源表 where=（步驟一確認的條件）的方式來手工補缺。

3. 啟動復制進程，復制進程會重新操作abended之前失敗的操作。

下面通過一個實驗來演示上述過程

1. source插入第一條測試數據

Insert into FM_TAX_RATE_TEST (TEST_ID,COUNTRY, STATE, TAX_TYPE, TAX_RATE)

Values (1,'CN', '68', 'WT3', 0.0015);

commit;

2. target確認同步

select * from fm_tax_rate_test;

COUNTR STAT TAX_TY TAX_RATE  TEST_ID

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

CN     68   WT3    .0015      1

3. target刪除復制記錄，人為制造1403錯誤

delete from fm_tax_rate_test where test_id=1;

commit;

4. source對第一條測記錄執行update操作會導致target復制進程中斷。中斷原因是update語句中的where字句定位的數據在target端不存在，因為我剛剛手工刪除了這條記錄。

update FM_TAX_RATE_TEST set country='US' where test_id=1;

commit;

此時target端已經中斷，在source增加數據變化，期待target重啟后會應用這些故障后產生的變化。

Insert into FM_TAX_RATE_TEST (TEST_ID,COUNTRY, STATE, TAX_TYPE, TAX_RATE)

Values (2,'TW', '68', 'WT3', 0.0015);

Insert into FM_TAX_RATE_TEST (TEST_ID,COUNTRY, STATE, TAX_TYPE, TAX_RATE)

Values (3,'JP', '68', 'WT3', 0.0015); 2

commit;

target復制進程中斷

GGSCI (cdbsym3) 6> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REPSYM 00:00:00 00:00:02

REPLICAT ABENDED REPSYM_T 00:10:20 00:00:01

target端ggserr.log中錯誤信息片段

2015-03-31 13:50:26 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, repsym_t.prm: Aborted grouped transaction on 'OGG_TEST.FM_TAX_RATE_TEST', Database error 1403 (OCI Error ORA-01403: no data found, SQL <UPDATE "OGG_TEST"."FM_TAX_RATE_TEST" SET "COUNTRY" = :a1,"STATE" = :a2,"TAX_TYPE" = :a3,"TAX_RATE" = :a4,"TEST_ID" = :a5 WHERE "TEST_ID" = :b0>).

2015-03-31 13:50:26 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, repsym_t.prm: Repositioning to rba 170249512 in seqno 12.

2015-03-31 13:50:26 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, repsym_t.prm: SQL error 1403 mapping OGG_TEST.FM_TAX_RATE_TEST to OGG_TEST.FM_TAX_RATE_TEST OCI Error ORA-01403: no data found, SQL <UPDATE "OGG_TEST"."FM_TAX_RATE_TEST" SET "COUNTRY" = :a1,"STATE" = :a2,"TAX_TYPE" = :a3,"TAX_RATE" = :a4,"TEST_ID" = :a5 WHERE "TEST_ID" = :b0>.

2015-03-31 13:50:26 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, repsym_t.prm: Repositioning to rba 170249512 in seqno 12.

2015-03-31 13:50:26 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, repsym_t.prm: Error mapping from OGG_TEST.FM_TAX_RATE_TEST to OGG_TEST.FM_TAX_RATE_TEST.

2015-03-31 13:50:26 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, repsym_t.prm: PROCESS ABENDING.

target端discard文件中記錄了test_id=1的數據執行udpate失敗

more repsym_t.dsc

Oracle GoldenGate Delivery for Oracle process started, group REPSYM_T discard file opened: 2015-03-31 13:50:25

Current time: 2015-03-31 13:50:26

Discarded record from action ABEND on error 1403

OCI Error ORA-01403: no data found, SQL <UPDATE "OGG_TEST"."FM_TAX_RATE_TEST" SET "COUNTRY" = :a1,"STATE" = :a2,"TAX_TYPE" = :a3,"TAX_

RATE" = :a4,"TEST_ID" = :a5 WHERE "TEST_ID" = :b0>

Aborting transaction on ./dirdat/yt beginning at seqno 12 rba 170249512

error at seqno 12 rba 170249512

Problem replicating OGG_TEST.FM_TAX_RATE_TEST to OGG_TEST.FM_TAX_RATE_TEST

Record not found

Mapping problem with compressed key update record (target format)...

*

TEST_ID = 1

COUNTRY = US

STATE = 68

TAX_TYPE = WT3

TAX_RATE = .00150000

TEST_ID = 1

這時候很多運維人員最常用的就是按照csn一致性導出source表，重新初始化target端數據不一致的表。在使用下面的方式來修改復制進程參數文件，重啟復制進程追進度。

map schema.table, target schema.table, filter (@GETENV ("TRANSACTION", "CSN") > 9527);

如果同步的表比較大，這個過程會很漫長。

如果只是缺少那么幾條數據，別人被認為誤刪除了造成的，也需要這么大動干戈處理么？其實可以用個簡單的方法來處理，在源庫創建一個database link，將target端缺少的數據手工insert過去補全這個漏洞，然后啟動復制進程。復制進程會再次嘗試失敗的update語句，where字句鎖定剛才手工插入的數據，修改成功。 復制進程繼續應用source端數據變化。

5. 源端創建database link。其中SERVICE_NAME = data為target數據庫的SID

5-1 在tnsnames.ora中添加target端數據庫的字符串 

to19 =

 (DESCRIPTION =

  (ADDRESS_LIST =

  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.78.2.19)(PORT = 1553))

 )

 (CONNECT_DATA =

 (SERVICE_NAME = data)

 )

)

5-2 創建database link指向target數據庫; 其中ogg_test為target數據庫的schema。

create public databbase link to19 connect to ogg_test identified by ogg_test;

5-3 通過database link手工同步丟失語句。其中select語句是源表的數據，insert into是目標數據庫。

insert into ogg_test.fm_tax_rate_test@to19 select * from ogg_test.fm_tax_rate_test where test_id=1;

6. target啟動復制進程

GGSCI (cdbsym3) 4> start repsym

Sending START request to MANAGER ...

REPLICAT REPSYM starting

GGSCI (cdbsym3) 5> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REPSYM 00:00:00 00:00:00

REPLICAT RUNNING REPSYM_T 00:00:00 00:00:01

數據變化已經被應用到復制端了

GGSCI (cdbsym3) 8> stats repsym total table dbp.rb_restraints

Sending STATS request to REPLICAT REPSYM ...

Start of Statistics at 2015-03-31 11:09:14.

Replicating from SYMBOLS.RB_RESTRAINTS to DBP.RB_RESTRAINTS:

*** Total statistics since 2015-03-31 11:08:13 ***

Total inserts 1.00

Total updates 4.00

Total deletes 0.00

Total discards 0.00

Total operations 5.00

End of Statistics.

7. 在數據庫中查看復制進程啟動后的數據變化

OGG_TEST@data> select * from ogg_test.fm_tax_rate_test;

COUNTR STAT TAX_TY TAX_RATE TEST_ID

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

US 68 WT3 .0015 1

TW 68 WT3 .0015 2

JP 68 WT3 .0015 3

其中第一條數據就是我們通過手工同步的數據，后面兩條數據是故障之后的數據變化。

注意:如果手工同步之前源表的數據也執行delete操作就無法通過isnert into select 的方式獲取并同步到target端了。

感謝各位的閱讀！關于“OGG  ora-01403錯誤怎么處理”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！