OGG-00446 Could not find archived log for sequence

0    60    1

👉 本文共约12768个字,系统预计阅读时间或需48分钟。

前言部分

导读和注意事项

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:

① OGG-00446 Could not find archived log for sequence 的解决方法(重点)

② OGG基本的维护、排错操作

本文简介

同事说有一套OGG的环境挂掉了,不能同步了,让处理一下,查看了进程状态和错误日志后分析得出是source端的归档日志不见了,也没有备份,最后只能重新初始化数据来解决了。

相关知识点扫盲

OGG的告警日志路径

ogg也有类似oracle的告警文件,该文件在 $OGG_HOME/ggserr.log

oracle@ZT1XPADRDB1:/gg/ogg$ l ggserr.log

-rw-rw-r-- 1 oracle dba 81355509 Jul 25 2025 ggserr.log

oracle@ZT1XPADRDB1:/gg/ogg$

故障分析及解决过程

故障环境介绍

项目source dbtarget db
db 类型rac环境rac环境
db version10.2.0.5.011.2.0.3.0
db 存储RAWASM
ORACLE_SIDoraXPADoraXPAD
db_nameoraXPADoraXPAD
主机IP地址:22.188.131.27 22.188.131.4722.188.132.82 22.188.132.85
OS版本及kernel版本AIX 5.3AIX 6.1
OS hostnameZTGXPADDB1ZT1XPADRDB1

故障发生现象及报错信息

source 端:

source端告警日志:

oracle@ZT1XPADRDB1:/gg/ogg$ tail -f ggserr.log

2020-08-03 07:47:27 INFO OGG-01226 Oracle GoldenGate Capture for Oracle, ggspump.prm: Socket buffer size set to 27985 (flush size 27985).

2020-08-03 07:47:27 INFO OGG-01055 Oracle GoldenGate Capture for Oracle, ggspump.prm: Recovery initialization completed for target file ./dirdat/tt002073, at RBA 1153.

2020-08-03 07:47:27 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, ggspump.prm: Output file ./dirdat/tt is using format RELEASE 10.4/11.1.

2020-08-03 07:47:27 INFO OGG-01026 Oracle GoldenGate Capture for Oracle, ggspump.prm: Rolling over remote file ./dirdat/tt002074.

2020-08-03 07:47:27 INFO OGG-01053 Oracle GoldenGate Capture for Oracle, ggspump.prm: Recovery completed for target file ./dirdat/tt002074, at RBA 1119.

2020-08-03 07:47:27 INFO OGG-01057 Oracle GoldenGate Capture for Oracle, ggspump.prm: Recovery completed for all targets.

2020-08-03 07:47:44 ERROR OGG-00446 Oracle GoldenGate Capture for Oracle, ggsext.prm: Could not find archived log for sequence 2598 thread 1 under alternative destinations. SQL <SELECT MAX(sequence#) FROM v$log WHERE thread# = :ora_thread>. Last alternative log tried /gg/sarch2/1_2598_704996932.arc, error retrieving redo file name for sequence 2598, archived = 1, use_alternate = 0Not able to establish initial position for sequence 2598, rba 89822224.

2020-08-03 07:47:44 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, ggsext.prm: PROCESS ABENDING.

2020-12-29 15:37:48 ERROR OGG-01117 Oracle GoldenGate Command Interpreter for Oracle: Received signal: Program interrupt (2).

2020-12-29 15:37:48 ERROR OGG-01668 Oracle GoldenGate Command Interpreter for Oracle: PROCESS ABENDING.

target端告警日志:

oracle@ZT1XPADRDB1:/gg/ogg$ tail -f ggserr.log

2020-12-30 09:27:54 WARNING OGG-00956 Oracle GoldenGate Manager for Oracle, mgr.prm: hours_since_modified calculated as -39947, modtime is 212619862981000000 for /gg/ogg/dirdat/tt001891.

2020-12-30 09:27:54 WARNING OGG-00956 Oracle GoldenGate Manager for Oracle, mgr.prm: hours_since_modified calculated as -39951, modtime is 212619877906000000 for /gg/ogg/dirdat/tt001892.

2020-12-30 09:27:54 WARNING OGG-00956 Oracle GoldenGate Manager for Oracle, mgr.prm: hours_since_modified calculated as -40038, modtime is 212620189201000000 for /gg/ogg/dirdat/tt001893.

2020-12-30 09:27:54 WARNING OGG-00950 Oracle GoldenGate Manager for Oracle, mgr.prm: Purge of old extract file /gg/ogg/dirdat/tt001894 failed because the prev seqno exists. Purge rule was applying UseCheckPoints purge rule: Oldest Chkpt Seqno 2032 > 1894.

2020-12-30 09:34:14 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start GGSREP.

2020-12-30 09:34:14 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 22.188.132.82 (START REPLICAT GGSREP ).

2020-12-30 09:34:14 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: REPLICAT GGSREP starting.

2020-12-30 09:34:14 INFO OGG-00995 Oracle GoldenGate Delivery for Oracle, ggsrep.prm: REPLICAT GGSREP starting.

2020-12-30 09:34:14 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, ggsrep.prm: REPLICAT GGSREP started.

2020-12-30 09:34:14 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, ggsrep.prm: No unique key is defined for table RICH_CUSTPAYAMOUNTHIS. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

2020-12-30 09:34:14 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, ggsrep.prm: Aborted grouped transaction on 'XPADRPT.RICH_CUSTPAYAMOUNTHIS', Database error 1403 ().

2020-12-30 09:34:14 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, ggsrep.prm: Repositioning to rba 47143866 in seqno 2032.

2020-12-30 09:34:14 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, ggsrep.prm: SQL error 1403 mapping XPADB.RICH_CUSTPAYAMOUNTHIS to XPADRPT.RICH_CUSTPAYAMOUNTHIS.

2020-12-30 09:34:14 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, ggsrep.prm: Repositioning to rba 47143866 in seqno 2032.

2020-12-30 09:34:14 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, ggsrep.prm: Error mapping from XPADB.RICH_CUSTPAYAMOUNTHIS to XPADRPT.RICH_CUSTPAYAMOUNTHIS.

2020-12-30 09:34:14 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, ggsrep.prm: PROCESS ABENDING.

2020-12-30 09:37:54 WARNING OGG-00956 Oracle GoldenGate Manager for Oracle, mgr.prm: hours_since_modified calculated as -39947, modtime is 212619862981000000 for /gg/ogg/dirdat/tt001891.

2020-12-30 09:37:54 WARNING OGG-00956 Oracle GoldenGate Manager for Oracle, mgr.prm: hours_since_modified calculated as -39951, modtime is 212619877906000000 for /gg/ogg/dirdat/tt001892.

2020-12-30 09:37:54 WARNING OGG-00956 Oracle GoldenGate Manager for Oracle, mgr.prm: hours_since_modified calculated as -40038, modtime is 212620189201000000 for /gg/ogg/dirdat/tt001893.

2020-12-30 09:37:54 WARNING OGG-00950 Oracle GoldenGate Manager for Oracle, mgr.prm: Purge of old extract file /gg/ogg/dirdat/tt001894 failed because the prev seqno exists. Purge rule was applying UseCheckPoints purge rule: Oldest Chkpt Seqno 2032 > 1894.

故障分析及解决过程

确认归档文件是否存在

根据报错信息,可以知道source端的thread 1 2598归档日志不存在了,如果备份存在的话可以先把备份的归档还原回来就可以了,我们看看归档是否存在?

oracle@ZTHXPADDB2:/oracle$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 30 08:50:48 2020

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options

SQL> set line 9999

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME

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

1 1 2907 1072693248 1 NO CURRENT 1.2242E+13 30-DEC-20

2 1 2905 1072693248 1 YES INACTIVE 1.2242E+13 28-DEC-20

3 1 2906 1072693248 1 YES INACTIVE 1.2242E+13 29-DEC-20

4 2 2718 1072693248 1 YES INACTIVE 1.2242E+13 29-DEC-20

5 2 2719 1072693248 1 NO CURRENT 1.2242E+13 29-DEC-20

6 2 2717 1072693248 1 YES INACTIVE 1.2242E+13 28-DEC-20

6 rows selected.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /gg/sarch2

Oldest online log sequence 2717

Next log sequence to archive 2719

Current log sequence 2719

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options

oracle@ZTHXPADDB2:/oracle$ cd /gg/sarch2

oracle@ZTHXPADDB2:/gg/sarch2$ l

total 193332080

-rw-rw---- 1 oracle dba 1065581568 Mar 05 2020 2_2625_704996932.arc

-rw-rw---- 1 oracle dba 1065574400 Mar 06 2020 2_2626_704996932.arc

-rw-rw---- 1 oracle dba 1065574400 Mar 06 2020 2_2627_704996932.arc

-rw-rw---- 1 oracle dba 1065577472 Mar 07 2020 2_2628_704996932.arc

-rw-rw---- 1 oracle dba 1065568768 Mar 20 2020 2_2629_704996932.arc

-rw-rw---- 1 oracle dba 1065582592 Mar 21 2020 2_2630_704996932.arc

《《《《。。。。。。。。篇幅原因,有省略。。。。。。。。》》》》

-rw-rw---- 1 oracle dba 1065574400 Dec 23 13:06 2_2707_704996932.arc

-rw-rw---- 1 oracle dba 1065574400 Dec 24 00:26 2_2708_704996932.arc

-rw-rw---- 1 oracle dba 1065574912 Dec 24 13:15 2_2709_704996932.arc

-rw-rw---- 1 oracle dba 1065744384 Dec 25 02:23 2_2710_704996932.arc

-rw-rw---- 1 oracle dba 1065566208 Dec 25 19:23 2_2711_704996932.arc

-rw-rw---- 1 oracle dba 1065576960 Dec 26 09:08 2_2712_704996932.arc

-rw-rw---- 1 oracle dba 1065627648 Dec 26 23:21 2_2713_704996932.arc

-rw-rw---- 1 oracle dba 1065574400 Dec 27 12:25 2_2714_704996932.arc

-rw-rw---- 1 oracle dba 1065691648 Dec 28 04:32 2_2715_704996932.arc

-rw-rw---- 1 oracle dba 1065658368 Dec 28 21:02 2_2716_704996932.arc

-rw-rw---- 1 oracle dba 1065575936 Dec 29 10:37 2_2717_704996932.arc

-rw-rw---- 1 oracle dba 1067223552 Dec 29 23:22 2_2718_704996932.arc

-rw-r--r-- 1 oracle dba 79 Jun 27 2013 afiedt.buf

oracle@ZTHXPADDB2:/gg/sarch2$ l *1_2598*

ls: 0653-341 The file *1_2598* does not exist.

oracle@ZTHXPADDB2:/gg/sarch2$ cd /gg/sarch1

oracle@ZTHXPADDB2:/gg/sarch1$ l

total 166951440

-rw-rw---- 1 oracle dba 1065574400 Mar 05 2020 1_2825_704996932.arc

-rw-rw---- 1 oracle dba 1065580032 Mar 06 2020 1_2826_704996932.arc

-rw-rw---- 1 oracle dba 1065574400 Mar 06 2020 1_2827_704996932.arc

-rw-rw---- 1 oracle dba 1065574400 Mar 07 2020 1_2828_704996932.arc

《《《《。。。。。。。。篇幅原因,有省略。。。。。。。。》》》》

-rw-rw---- 1 oracle dba 1065574400 Dec 27 10:52 1_2902_704996932.arc

-rw-rw---- 1 oracle dba 1065574400 Dec 28 00:59 1_2903_704996932.arc

-rw-rw---- 1 oracle dba 1065687040 Dec 28 15:16 1_2904_704996932.arc

-rw-rw---- 1 oracle dba 1065607680 Dec 29 08:40 1_2905_704996932.arc

-rw-rw---- 1 oracle dba 1065577984 Dec 30 01:32 1_2906_704996932.arc

-rw-rw---- 1 oracle dba 278522368 Aug 06 07:15 2_2689_704996932.arc

-rw-r--r-- 1 oracle dba 90 Dec 22 2013 afiedt.buf

oracle@ZTHXPADDB2:/gg/sarch1$

RMAN> list archivelog all;

List of Archived Log Copies

Key Thrd Seq S Low Time Name

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

4651 1 2825 A 05-MAR-20 /gg/sarch1/1_2825_704996932.arc

4653 1 2826 A 05-MAR-20 /gg/sarch1/1_2826_704996932.arc

4655 1 2827 A 06-MAR-20 /gg/sarch1/1_2827_704996932.arc

4656 1 2828 A 06-MAR-20 /gg/sarch1/1_2828_704996932.arc

4658 1 2829 A 07-MAR-20 /gg/sarch1/1_2829_704996932.arc

4660 1 2830 A 20-MAR-20 /gg/sarch1/1_2830_704996932.arc

4662 1 2831 A 20-MAR-20 /gg/sarch1/1_2831_704996932.arc

4665 1 2832 A 21-MAR-20 /gg/sarch1/1_2832_704996932.arc

4667 1 2833 A 30-MAR-20 /gg/sarch1/1_2833_704996932.arc

4670 1 2834 A 30-MAR-20 /gg/sarch1/1_2834_704996932.arc

《《《《。。。。。。。。篇幅原因,有省略。。。。。。。。》》》》

4793 2 2702 A 02-OCT-20 /gg/sarch2/2_2702_704996932.arc

4795 2 2703 A 20-DEC-20 /gg/sarch2/2_2703_704996932.arc

4796 2 2704 A 21-DEC-20 /gg/sarch2/2_2704_704996932.arc

4798 2 2705 A 21-DEC-20 /gg/sarch2/2_2705_704996932.arc

4801 2 2706 A 22-DEC-20 /gg/sarch2/2_2706_704996932.arc

4803 2 2707 A 22-DEC-20 /gg/sarch2/2_2707_704996932.arc

4804 2 2708 A 23-DEC-20 /gg/sarch2/2_2708_704996932.arc

4806 2 2709 A 24-DEC-20 /gg/sarch2/2_2709_704996932.arc

4808 2 2710 A 24-DEC-20 /gg/sarch2/2_2710_704996932.arc

4811 2 2711 A 25-DEC-20 /gg/sarch2/2_2711_704996932.arc

4813 2 2712 A 25-DEC-20 /gg/sarch2/2_2712_704996932.arc

4815 2 2713 A 26-DEC-20 /gg/sarch2/2_2713_704996932.arc

4817 2 2714 A 26-DEC-20 /gg/sarch2/2_2714_704996932.arc

4819 2 2715 A 27-DEC-20 /gg/sarch2/2_2715_704996932.arc

4821 2 2716 A 28-DEC-20 /gg/sarch2/2_2716_704996932.arc

4823 2 2717 A 28-DEC-20 /gg/sarch2/2_2717_704996932.arc

4824 2 2718 A 29-DEC-20 /gg/sarch2/2_2718_704996932.arc

可以看到 2598号的归档日志已经不存在了。

第一种办法:改变抽取进程的时间

改变抽取进程的时间, 但是会导致数据不一致,若是测试库,可以执行:alter extract extl,begin now

GGSCI (ZTHXPADDB2) 11> alter extract GGSEXT,begin now

EXTRACT altered.

GGSCI (ZTHXPADDB2) 12> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED GGSEXT 00:00:00 00:00:05

EXTRACT RUNNING GGSPUMP 00:00:00 00:00:00

GGSCI (ZTHXPADDB2) 13> start GGSEXT

Sending START request to MANAGER ...

EXTRACT GGSEXT starting

GGSCI (ZTHXPADDB2) 14> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING GGSEXT 00:00:00 00:00:28

EXTRACT RUNNING GGSPUMP 00:00:00 unknown

GGSCI (ZTHXPADDB2) 17> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING GGSEXT 00:00:00 unknown

EXTRACT RUNNING GGSPUMP 00:00:00 00:00:00

GGSCI (ZTHXPADDB2) 18> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING GGSEXT 00:00:00 unknown

EXTRACT RUNNING GGSPUMP 00:00:00 unknown

GGSCI (ZTHXPADDB2) 19> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING GGSEXT 00:00:00 unknown

EXTRACT RUNNING GGSPUMP 00:00:00 00:00:01

可以看到虽然处于RUNNING的状态,但是Time Since Chkpt列是不对的,而这是target端也不能正常启动。

oracle@ZT1XPADRDB1:/gg/ogg$ tail -f ggserr.log

2020-12-30 09:27:54 WARNING OGG-00956 Oracle GoldenGate Manager for Oracle, mgr.prm: hours_since_modified calculated as -39947, modtime is 212619862981000000 for /gg/ogg/dirdat/tt001891.

2020-12-30 09:27:54 WARNING OGG-00956 Oracle GoldenGate Manager for Oracle, mgr.prm: hours_since_modified calculated as -39951, modtime is 212619877906000000 for /gg/ogg/dirdat/tt001892.

2020-12-30 09:27:54 WARNING OGG-00956 Oracle GoldenGate Manager for Oracle, mgr.prm: hours_since_modified calculated as -40038, modtime is 212620189201000000 for /gg/ogg/dirdat/tt001893.

2020-12-30 09:27:54 WARNING OGG-00950 Oracle GoldenGate Manager for Oracle, mgr.prm: Purge of old extract file /gg/ogg/dirdat/tt001894 failed because the prev seqno exists. Purge rule was applying UseCheckPoints purge rule: Oldest Chkpt Seqno 2032 > 1894.

2020-12-30 09:34:14 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start GGSREP.

2020-12-30 09:34:14 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 22.188.132.82 (START REPLICAT GGSREP ).

2020-12-30 09:34:14 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: REPLICAT GGSREP starting.

2020-12-30 09:34:14 INFO OGG-00995 Oracle GoldenGate Delivery for Oracle, ggsrep.prm: REPLICAT GGSREP starting.

2020-12-30 09:34:14 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, ggsrep.prm: REPLICAT GGSREP started.

2020-12-30 09:34:14 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, ggsrep.prm: No unique key is defined for table RICH_CUSTPAYAMOUNTHIS. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

2020-12-30 09:34:14 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, ggsrep.prm: Aborted grouped transaction on 'XPADRPT.RICH_CUSTPAYAMOUNTHIS', Database error 1403 ().

2020-12-30 09:34:14 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, ggsrep.prm: Repositioning to rba 47143866 in seqno 2032.

2020-12-30 09:34:14 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, ggsrep.prm: SQL error 1403 mapping XPADB.RICH_CUSTPAYAMOUNTHIS to XPADRPT.RICH_CUSTPAYAMOUNTHIS.

2020-12-30 09:34:14 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, ggsrep.prm: Repositioning to rba 47143866 in seqno 2032.

2020-12-30 09:34:14 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, ggsrep.prm: Error mapping from XPADB.RICH_CUSTPAYAMOUNTHIS to XPADRPT.RICH_CUSTPAYAMOUNTHIS.

2020-12-30 09:34:14 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, ggsrep.prm: PROCESS ABENDING.

2020-12-30 09:37:54 WARNING OGG-00956 Oracle GoldenGate Manager for Oracle, mgr.prm: hours_since_modified calculated as -39947, modtime is 212619862981000000 for /gg/ogg/dirdat/tt001891.

2020-12-30 09:37:54 WARNING OGG-00956 Oracle GoldenGate Manager for Oracle, mgr.prm: hours_since_modified calculated as -39951, modtime is 212619877906000000 for /gg/ogg/dirdat/tt001892.

2020-12-30 09:37:54 WARNING OGG-00956 Oracle GoldenGate Manager for Oracle, mgr.prm: hours_since_modified calculated as -40038, modtime is 212620189201000000 for /gg/ogg/dirdat/tt001893.

2020-12-30 09:37:54 WARNING OGG-00950 Oracle GoldenGate Manager for Oracle, mgr.prm: Purge of old extract file /gg/ogg/dirdat/tt001894 failed because the prev seqno exists. Purge rule was applying UseCheckPoints purge rule: Oldest Chkpt Seqno 2032 > 1894.

GGSCI (ZT1XPADRDB1) 6> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

REPLICAT ABENDED GGSREP 33665:56:32 00:03:08

第二种办法:数据初始化

source端准备

oracle@ZTGXPADDB1:/softtmp$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Sat Jan 2 16:38:32 2021

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options

SQL> col current_scn format 999999999999999

SQL> Select current_scn from v$database;

CURRENT_SCN

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

12242466771468

SQL>

由于是基于表级别的复制,并且全用户和表的大小差异很大,所以建议导出的时候基于表来导出,导入也基于表来导入。

OGG-00446 Could not find archived log for sequenceOGG-00446 Could not find archived log for sequence

source库导出操作,flashback_scn为 12242466771468:

oracle@ZTGXPADDB1:/softtmp$ expdp XPADB/XPADB directory=DMP dumpfile=xpadb_20160125_01.dmp LOGFILE=xpadb_20160125.log TABLES=BASE_ACTIONPOWER,BASE_BANK,BASE_BANKMERGE,BASE_BANKTREE FLASHBACK_SCN=12242466771468

Export: Release 10.2.0.5.0 - 64bit Production on Saturday, 02 January, 2021 16:41:50

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options

Starting "XPADB"."SYS_EXPORT_TABLE_01": XPADB/******** directory=DMP dumpfile=xpadb_20160125_01.dmp LOGFILE=xpadb_20160125.log TABLES=BASE_ACTIONPOWER,BASE_BANK,BASE_BANKMERGE,BASE_BANKTREE,BASE_BRCHBANKCTRL,BASE_CERTIFICATE,BASE_CESS,BASE_COREBANK,BASE_CURRENCY,BASE_DEPARTMENT,BASE_HOLIDAYCALENDAR,BASE_HOLIDAYDATE,BASE_INTEREST,BASE_INTERESTHIS,BASE_MAINAREA,BASE_MENU,BASE_PRODTYPECODE,BASE_RATE,BASE_ROLE,BASE_SYSLOG,BASE_SYSPARAM,BASE_TERMINALTELLER,BASE_USER,FINANCIAL_BANKPROFITLOG,FINANCIAL_DIVIDENDPLAN,FINANCIAL_FUNDPRICE,FINANCIAL_INTERESTRESET,FINANCIAL_ISSUE,FINANCIAL_ISSUEAUDIT,FINANCIAL_ISSUEAUDITHIS,FINANCIAL_ISSUEBRAND,FINANCIAL_ISSUECFL,FINANCIAL_ISSUECONTROL,FINANCIAL_ISSUEDIFFRATE,FINANCIAL_ISSUEDISCOUNT,FINANCIAL_ISSUEEXPD,FINANCIAL_ISSUEEXT,FINANCIAL_ISSUEFEE,FINANCIAL_ISSUEFORCUSTRISKLVL,FINANCIAL_ISSUEFUNDSTRANSFER,FINANCIAL_ISSUEOBSERV,FINANCIAL_ISSUEPAY,FINANCIAL_ISSUEPROFIT,FINANCIAL_ISSUEQTYSPLIT,FINANCIAL_ISSUESELLCTRL,FINANCIAL_ISSUESERIAL,FINANCIAL_ISSUEVARIABLE,FINANCIAL_ISSUEWORKTIME,FINANCIAL_LIQUIDATEACT,FINANCIAL_MESSAGEINFO,FINANCIAL_PERIOD,FINANCIAL_PRICE,FINANCIAL_PRICEHIS,FINANCIAL_REFERINDEX,FINANCIAL_POSITION,RICH_AUTOTRADE,RICH_CUSTACCOUNT,RICH_CUSTAUTOTRADE,RICH_CUSTAUTOTRADEHIS,RICH_CUSTCAPITAL,RICH_CUSTFAVORABLE,RICH_CUSTFREEZEUNIT,RICH_CUSTFUNDPROFIT,RICH_CUSTIMPAWN,RICH_CUSTKEEPBAL,RICH_CUSTOMERINFO,RICH_CUSTOMERINFOHIS,RICH_CUSTORDERTRADE,RICH_CUSTPAYAMOUNTLOG,RICH_CUSTPRODUCTBAL,RICH_CUSTPRODUCTBALQTY,RICH_CUSTPROFITDCCY,RICH_CUSTPROFITLOG,RICH_CUSTPROFITNORMAL,RICH_CUSTPROFITPAYMODE,RICH_CUSTPROFITPAYMODEHIS,RICH_CUSTRISKLVL,RICH_FUNDTRADELOG,RICH_NOPAYAMOUNT,RICH_NORMALPAY,RICH_ORDERTRADE,RICH_SMSCMDSIGN,RICH_SMSCMDTRADE,RICH_STARTBAL,RICH_STARTBALHIS,financial_positionhis,financial_positiontra,financial_positioncst,financial_positionacc,corp_stattemptab,corp_stattemptabacc,corp_stattemptabcst,FINANCIAL_ICCARDCODE,BASE_FINANCIALICCARD,FINANCIAL_ISSUESPECIFICATION,PA_RPT_PARAM,FINANCIAL_SALEBANK_HIS,RICH_TRA

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 6.913 GB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/COMMENT

Processing object type TABLE_EXPORT/TABLE/TRIGGER

Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "XPADB"."RICH_CUSTPRODUCTBAL" 63.64 KB 582 rows

. . exported "XPADB"."RICH_CUSTOMERINFO" 616.9 KB 2596 rows

. . exported "XPADB"."RICH_CUSTACCOUNT" 11.33 MB 177757 rows

. . exported "XPADB"."FINANCIAL_MESSAGEINFO" 329.7 MB 3067896 rows

. . exported "XPADB"."RICH_AUTOTRADE" 36.53 KB 161 rows

. . exported "XPADB"."BASE_SYSLOG" 24.70 KB 131 rows

《《《《。。。。。。。。篇幅原因,有省略。。。。。。。。》》》》

. . exported "XPADB"."RICH_TRADELOG_HIS":"P_201411" 0 KB 0 rows

. . exported "XPADB"."RICH_TRADELOG_HIS":"P_201412" 0 KB 0 rows

. . exported "XPADB"."RICH_TRADELOG_HIS":"P_999999" 0 KB 0 rows

ORA-39166: Object RIC was not found.

Master table "XPADB"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for XPADB.SYS_EXPORT_TABLE_01 is:

/softtmp/dmp/xpadb_20160125_01.dmp

Job "XPADB"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 16:42:37

target 准备

先做导出备份操作:

root@ZT1XPADRDB1:/# su - oracle

oracle@ZT1XPADRDB1:/oracle$ expdp xpadrpt/xpadrpt directory=OGGD dumpfile=xpadb_20160125_02.dmp LOGFILE=xpadb_20160125_2.log TABLES=BASE_ACTIONPOWER,BASE_BANK,BASE_BANKMERGE,BASE_BANKTREE,BASE_BRCHBANKCTRL,BASE_CERTIFICATE,BASE_CESS,BASE_COREBANK,BASE_CURRENCY,BASE_DEPARTMENT,BASE_HOLIDAYCALENDAR,BASE_HOLIDAYDATE,BASE_INTEREST,BASE_INTERESTHIS,BASE_MAINAREA,BASE_MENU,BASE_PRODTYPECODE,BASE_RATE,BASE_ROLE,BASE_SYSLOG,BASE_SYSPARAM

Export: Release 11.2.0.3.0 - Production on Sat Jan 2 17:06:22 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Starting "XPADRPT"."SYS_EXPORT_TABLE_01": xpadrpt/******** directory=OGGD dumpfile=xpadb_20160125_02.dmp LOGFILE=xpadb_20160125_2.log TABLES=BASE_ACTIONPOWER,BASE_BANK,BASE_BANKMERGE,BASE_BANKTREE,BASE_BRCHBANKCTRL,BASE_CERTIFICATE,BASE_CESS,BASE_COREBANK,BASE_CURRENCY,BASE_DEPARTMENT,BASE_HOLIDAYCALENDAR,BASE_HOLIDAYDATE,BASE_INTEREST,BASE_INTERESTHIS,BASE_MAINAREA,BASE_MENU,BASE_PRODTYPECODE,BASE_RATE,BASE_ROLE,BASE_SYSLOG,BASE_SYSPARAM,BASE_TERMINALTELLER,BASE_USER,FINANCIAL_BANKPROFITLOG,FINANCIAL_DIVIDENDPLAN,FINANCIAL_FUNDPRICE,FINANCIAL_INTERESTRESET,FINANCIAL_ISSUE,FINANCIAL_ISSUEAUDIT,FINANCIAL_ISSUEAUDITHIS,FINANCIAL_ISSUEBRAND,FINANCIAL_ISSUECFL,FINANCIAL_ISSUECONTROL,FINANCIAL_ISSUEDIFFRATE,FINANCIAL_ISSUEDISCOUNT,FINANCIAL_ISSUEEXPD,FINANCIAL_ISSUEEXT,FINANCIAL_ISSUEFEE,FINANCIAL_ISSUEFORCUSTRISKLVL,FINANCIAL_ISSUEFUNDSTRANSFER,FINANCIAL_ISSUEOBSERV,FINANCIAL_ISSUEPAY,FINANCIAL_ISSUEPROFIT,FINANCIAL_ISSUEQTYSPLIT,FINANCIAL_ISSUESELLCTRL,FINANCIAL_ISSUESERIAL,FINANCIAL_ISSUEVARIABLE,FINANCIAL_ISSUEWORKTIME,FINANCIAL_LIQUIDATEACT,FINANCIAL_MESSAGEINFO,FINANCIAL_PERIOD,FINANCIAL_PRICE,FINANCIAL_PRICEHIS,FINANCIAL_REFERINDEX,FINANCIAL_POSITION,RICH_AUTOTRADE,RICH_CUSTACCOUNT,RICH_CUSTAUTOTRADE,RICH_CUSTAUTOTRADEHIS,RICH_CUSTCAPITAL,RICH_CUSTFAVORABLE,RICH_CUSTFREEZEUNIT,RICH_CUSTFUNDPROFIT,RICH_CUSTIMPAWN,RICH_CUSTKEEPBAL,RICH_CUSTOMERINFO,RICH_CUSTOMERINFOHIS,RICH_CUSTORDERTRADE,RICH_CUSTPAYAMOUNTLOG,RICH_CUSTPRODUCTBAL,RICH_CUSTPRODUCTBALQTY,RICH_CUSTPROFITDCCY,RICH_CUSTPROFITLOG,RICH_CUSTPROFITNORMAL,RICH_CUSTPROFITPAYMODE,RICH_CUSTPROFITPAYMODEHIS,RICH_CUSTRISKLVL,RICH_FUNDTRADELOG,RICH_NOPAYAMOUNT,RICH_NORMALPAY,RICH_ORDERTRADE,RICH_SMSCMDSIGN,RICH_SMSCMDTRADE,RICH_STARTBAL,RICH_STARTBALHIS,financial_positionhis,financial_positiontra,financial_positioncst,financial_positionacc,corp_stattemptab,corp_stattemptabacc,corp_stattemptabcst,FINANCIAL_ICCARDCODE,BASE_FINANCIALICCARD,FINANCIAL_ISSUESPECIFICATION,PA_RPT_PARAM,FINANCIAL_SALEBANK_HIS,R

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 7.606 GB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/COMMENT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/TRIGGER

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "XPADRPT"."RICH_CUSTPRODUCTBAL" 45.04 KB 367 rows

《《《《。。。。。。。。篇幅原因,有省略。。。。。。。。》》》》

. . exported "XPADRPT"."RICH_TRADELOG_HIS":"P_201408" 0 KB 0 rows

. . exported "XPADRPT"."RICH_TRADELOG_HIS":"P_201409" 0 KB 0 rows

. . exported "XPADRPT"."RICH_TRADELOG_HIS":"P_201410" 0 KB 0 rows

. . exported "XPADRPT"."RICH_TRADELOG_HIS":"P_201411" 0 KB 0 rows

. . exported "XPADRPT"."RICH_TRADELOG_HIS":"P_201412" 0 KB 0 rows

. . exported "XPADRPT"."RICH_TRADELOG_HIS":"P_999999" 0 KB 0 rows

ORA-39166: Object XPADRPT.RICHCUSTPRODUCTBALHIS\ was not found.

Master table "XPADRPT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for XPADRPT.SYS_EXPORT_TABLE_01 is:

/gg/bkup/xpadb_20160125_02.dmp

Job "XPADRPT"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 17:11:30

数据备份完成后可以进行导入操作,注意schema、tablespace的remap操作,以及表的操作,我这里根据业务需求应该是删除后重建,所以选择replace:

oracle@ZT1XPADRDB1:/oracle$ impdp XPADRPT/xpadrpt DIRECTORY=OGGD DUMPFILE=xpadb_20160125_01.dmp LOGFILE=impdp.xpadb_20160125_01.log REMAP_SCHEMA=xpadb:xpadrpt REMAP_TABLESPACE=xpaddat:xpaddata table_exists_action=replace

Import: Release 11.2.0.3.0 - Production on Sat Jan 2 17:18:39 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Master table "XPADRPT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "XPADRPT"."SYS_IMPORT_FULL_01": XPADRPT/******** DIRECTORY=OGGD DUMPFILE=xpadb_20160125_01.dmp LOGFILE=impdp.xpadb_20160125_01.log REMAP_SCHEMA=xpadb:xpadrpt REMAP_TABLESPACE=xpaddat:xpaddata table_exists_action=replace

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "XPADRPT"."RICH_CUSTPRODUCTBAL" 63.64 KB 582 rows

. . imported "XPADRPT"."RICH_CUSTOMERINFO" 616.9 KB 2596 rows

. . imported "XPADRPT"."RICH_CUSTACCOUNT" 11.33 MB 177757 rows

. . imported "XPADRPT"."FINANCIAL_MESSAGEINFO" 329.7 MB 3067896 rows

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

《《《《。。。。。。。。篇幅原因,有省略。。。。。。。。》》》》

. . imported "XPADRPT"."RICH_TRADELOG_HIS":"P_201408" 0 KB 0 rows

. . imported "XPADRPT"."RICH_TRADELOG_HIS":"P_201409" 0 KB 0 rows

. . imported "XPADRPT"."RICH_TRADELOG_HIS":"P_201410" 0 KB 0 rows

. . imported "XPADRPT"."RICH_TRADELOG_HIS":"P_201411" 0 KB 0 rows

. . imported "XPADRPT"."RICH_TRADELOG_HIS":"P_201412" 0 KB 0 rows

. . imported "XPADRPT"."RICH_TRADELOG_HIS":"P_999999" 0 KB 0 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/COMMENT

Processing object type TABLE_EXPORT/TABLE/TRIGGER

Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "XPADRPT"."SYS_IMPORT_FULL_01" successfully completed at 17:29:25

导入成功,接下来启动target端的REPLICAT进程:

GGSCI (ZT1XPADRDB1) 16> stop ggsrep

REPLICAT GGSREP is already stopped.

GGSCI (ZT1XPADRDB1) 17> info all

Program Status Group Lag Time Since Chkpt

MANAGER STOPPED

REPLICAT ABENDED GGSREP 33743:45:19 01:56:55

GGSCI (ZT1XPADRDB1) 18> info all

Program Status Group Lag Time Since Chkpt

MANAGER STOPPED

REPLICAT ABENDED GGSREP 33743:45:19 02:07:52

GGSCI (ZT1XPADRDB1) 19> start mgr

Manager started.

GGSCI (ZT1XPADRDB1) 20> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

REPLICAT ABENDED GGSREP 33743:45:19 02:08:01

GGSCI (ZT1XPADRDB1) 21> start replicat ggsrep , aftercsn 12242466771468

Sending START request to MANAGER ...

REPLICAT GGSREP starting

GGSCI (ZT1XPADRDB1) 22> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING GGSREP 33716:13:56 00:00:02

GGSCI (ZT1XPADRDB1) 23> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING GGSREP 33216:43:48 00:00:04

GGSCI (ZT1XPADRDB1) 24> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING GGSREP 30818:03:37 00:00:05

GGSCI (ZT1XPADRDB1) 25> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING GGSREP 24194:29:09 00:00:02

GGSCI (ZT1XPADRDB1) 26> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING GGSREP 00:00:00 00:00:06

GGSCI (ZT1XPADRDB1) 27> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING GGSREP 00:00:00 00:00:08

GGSCI (ZT1XPADRDB1) 28> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING GGSREP 00:00:00 00:00:09

GGSCI (ZT1XPADRDB1) 29> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING GGSREP 00:00:00 00:00:06

可以看到target端的进程已经正常了。

验证是否可以正常同步数据

当时忘记记录日志,经测试已经可以正常同步数据了,省略验证过程。。。。。

故障处理总结

OGG-00446 Could not find archived log for sequence 53586 thread 1 under alternative destinations.

错误信息:

OGG-00446 Could not find archived log for sequence 53586 thread 1 under alternative destinations. SQL \<SELECT MAX(sequence#) FROM v$log WHERE thread# = :ora_thread>. Last alternative log tried /arch_cx/1_53586_776148274.arc., error retri

eving redo file name for sequence 53586, archived = 1, use_alternate = 0Not able to establish initial position for sequence 53586, rba

44286992.

处理办法:

将缺失的归档日志从备份中恢复出来。如果依旧找不到所需归档日志,那么只能重新实施数据初始化。

第一种办法,会导致数据不一致,改变抽取进程的时间,如下执行:

GGSCI (HP-HP) 8> alter extract extl,begin now

第二种办法:重新初始化

重新初始化过程如下:

---- source库

SQL> col current_scn format 999999999999999

SQL> Select current_scn from v$database;

CURRENT_SCN

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

12242466771468

expdp XPADB/XPADB directory=DMP dumpfile=xpadb_20160125_01.dmp LOGFILE=xpadb_20160125.log TABLES=BASE_ACTIONPOWER,BASE_BANK FLASHBACK_SCN=12242466771468

--- target库

impdp XPADRPT/xpadrpt DIRECTORY=OGGD DUMPFILE=xpadb_20160125_01.dmp LOGFILE=impdp.xpadb_20160125_01.log REMAP_SCHEMA=xpadb:xpadrpt REMAP_TABLESPACE=xpaddat:xpaddata table_exists_action=replace

start replicat ggsrep , aftercsn 12242466771468

到此所有的处理算是基本完毕,过程很简单,但是不同的场景处理方式有很多种,我们应该学会灵活变通。

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

13 + 8 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部