流复制象传说的那样不是很强壮!

最新推荐文章于 2021-12-18 14:48:30 发布

最新推荐文章于 2021-12-18 14:48:30 发布 · 78 阅读

文章标签：

#数据库

本文记录了一次使用Oracle数据库进行流复制时遇到的问题及解决过程。主要原因是由于表中存在两个完全相同的记录导致复制过程中出现ORA-01422错误。文章强调了为主键的重要性。

配置好的流复制再试了几个语句后。想试一下下面这个语句：

SQL> select * from kpi_bak;

ID IS_HARD
---------- ----------
NAME
--------------------------------------------------------------------------------
VALUEOFKPI
--------------------------------------------------------------------------------
OBJ_VERSION LEVELOFSERVICE_ID EXTRA
----------- ----------------- ----------
2 0
standart_KPI
12345
0 1

SQL> delete from kpi_bak where rownum=1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from kpi_bak;

结果发现没复制过去。

一看apply已经被迫关闭了。

源库日志报如下错误：

LOGMINER: Begin mining logfile for session 41 thread 1 sequence 4394, /opt/app/oracle/oracle/product/10.2.0/db_1/oradata/LAB10G/redo01.log

Thu Jul 2 04:05:36 2009
knllgobjinfo: MISSING Streams multi-version data dictionary!!!
knlldmm: gdbnm=LAB10G.CENTRAL
knlldmm: objn=531168
knlldmm: objv=1
knlldmm: scn=194446182
knllgobjinfo: MISSING Streams multi-version data dictionary!!!
knlldmm: gdbnm=LAB10G.CENTRAL
knlldmm: objn=531170
knlldmm: objv=1
knlldmm: scn=194446325
knllgobjinfo: MISSING Streams multi-version data dictionary!!!
knlldmm: gdbnm=LAB10G.CENTRAL
knlldmm: objn=531172。。。。。。。。。。。。。。。

分析原因：

SQL> select * from dba_apply;

STREAMS_APPLY STREAMS_APPLY_Q
STRMADMIN YES RULESET$_60
STRMADMIN STRMADMIN

ABORTED
02-JUL-09 26714
ORA-26714: User error encountered while applying

SQL> select * from dba_apply_error
2 /

STREAMS_APPLY STREAMS_APPLY_Q
STRMADMIN 2.2.56416
LAB10G.CENTRAL
1.2.118518 194445622 1 1422
ORA-01422: exact fetch returns more than requested number of rows
77 STRMADMIN 1 02-JUL-09

SQL> set serverout on size 23456
SQL> exec print_errors
*************************************************
----- ERROR #1
----- Local Transaction ID: 2.2.56416
----- Source Database: LAB10G.CENTRAL
----Error in Message: 1
----Error Number: 1422
----Message Text: ORA-01422: exact fetch returns more than requested number of
rows

--message: 1
type name: SYS.LCR$_ROW_RECORD
source database: LAB10G.CENTRAL
owner: SVC_CONTRACT
object: KPI_BAK
is tag null: Y
command_type: DELETE
old(1): ID
2
old(2): IS_HARD
0
old(3): NAME
standart_KPI
old(4): VALUEOFKPI
12345
old(5): OBJ_VERSION
0
old(6): LEVELOFSERVICE_ID
1
old(7): EXTRA

PL/SQL procedure successfully completed.

SQL> exec print_transaction('2.2.56416')
----- Local Transaction ID: 2.2.56416
----- Source Database: LAB10G.CENTRAL
----Error in Message: 1
----Error Number: 1422
----Message Text: ORA-01422: exact fetch returns more than requested number of
rows