GoldenGate 遭遇ORA-00904: "VISIBILITY": invalid identifier
在做ogg 同步的时候遭遇ORA-00904: "VISIBILITY": invalid identifier，具体信息如下：源端配置信息
操作系统版本
$oslevel -s
5300-08-04-0844
数据库版本（RAC）
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio
NLSRTL Version 10.2.0.5.0 - Production
OGG 版本
ogg112101_ggs_AIX_ppc_ora10.2_64bit
目标端配置信息
操作系统Windows 2008 R2 64bit
数据库版本
Oracle 11.2.0.3
OGG 版本
ogg112101_ggs_Windows_x64_ora11g_64bit
在做同步的时候碰到如下问题：
2013-01-16 19:26:57 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, REP1.prm: REPLICAT REP1 started.
2013-01-16 19:26:58 ERROR OGG-00661 Oracle GoldenGate Delivery for Oracle, REP1.prm: Error selecting unique keys for NANJING.STO_SELLER_BALANCE: ORA-00904: "VISIBILITY": invalid identifier, SQL < SELECT key.key_name, key.column_name, key.descend FROM (SELECT c.constraint_name key_name, c.column_name column_name, c.position position, 'ASC' descend FROM all_cons_columns c WHERE c.owner = :owner1 AND c.table_name = :table1 AND c.constraint_name = ( SELECT MIN(con1.name) FROM sys.user$ user1, sys.user$ user2, sys.cdef$ cdef, sys.con$ con1, sys.con$ con2, sys.obj$ obj1, sys.obj$ obj2 WHERE user1.name = :owner2 AND obj1.name = :table2 AND cdef.type# = 3 AND bitand(cdef.defer, 36) = 4 AND con2.owner# = user2.user#(+) AND cdef.robj# = obj2.obj#(+) AND cdef.rcon# = con2.con#(+) AND obj1.owner# = user1.user# AND cdef.con# = con1.con# AND cdef.obj# = obj1.obj#) AND EXISTS ( SELECT 'x' FROM all_tab_columns t WHERE t.owner = c.owner AND t.table_name = c.table_name AND t.column_name = c.column_name) UNION SELECT i.index_name key_name, c.column_name column_name, c.column_position position, c.descend descend FROM all_indexes i, all_ind_columns c WHERE i.table_owner = :owner3 AND i.table_name = :table3 AND i.uniqueness = 'UNIQUE' AND i.owner = c.index_owner AND i.index_name = c.index_name AND i.table_name = c.table_name .
2013-01-16 19:26:58 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, REP1.prm: PROCESS ABENDING.
后来我专门针对NANJING.STO_SELLER_BALANCE 这张表来进行同步
配置信息如下：
GGSCI (p570_svr3) 2> view params ext2
EXTRACT ext2
SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")
USERID ogg@orclerp, PASSWORD ogg
TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORD oracle123
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000
EXTTRAIL /ogg/oracle/ggs/dirdat/ex
DYNAMICRESOLUTION
GETTRUNCATES
DDLERROR RESTARTSKIP 100000 SKIPTRIGGERERROR 100000
FETCHOPTIONS FETCHPKUPDATECOLS
DDL INCLUDE mapped objname "NANJING.STO_SELLER_BALANCE";
TABLE NANJING.STO_SELLER_BALANCE;
GGSCI (p570_svr3) 5> view params pump2
EXTRACT pump2
USERID ogg,PASSWORD ogg
RMTHOST 192.168.8.181, MGRPORT 7809
RMTTRAIL E:\ogg\dirdat\lp
--PASSTHRU
dynamicresolution
gettruncates
DDLERROR RESTARTSKIP 100000 SKIPTRIGGERERROR 100000
FETCHOPTIONS FETCHPKUPDATECOLS
DDL INCLUDE mapped objname "NANJING.STO_SELLER_BALANCE";
TABLE NANJING.STO_SELLER_BALANCE;
GGSCI (IDC-570BK) 2> view params rep2
replicat rep2
SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")
userid ogg@orclerp,password ogg
--reperror default,discard
discardfile E:\ogg\dirdat\rep2_discard.txt, append, megabytes 1024
--NOBINARYCHARS
--NODYNSQL
--SHOWSYNTAX
grouptransops 1000
maxtransops 1000
BATCHSQL OPSPERBATCH 200
--BATCHSQL OPSPERBATCH 2000
BATCHSQL BATCHESPERQUEUE 100, OPSPERBATCH 8000
handlecollisions
sqlexec "Alter session set constraints=deferred"
DBOPTIONS _MAXSEQUENCEDISTANCE 50
ASSUMETARGETDEFS
checksequencevalue
dynamicresolution
gettruncates
HANDLECOLLISIONS
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
DDLOPTIONS REPORT
map NANJING.STO_SELLER_BALANCE, target NANJING.STO_SELLER_BALANCE;
结果还是出现上述问题，请大大们帮忙分析一下，谢谢！
GGSCI (IDC-570BK) 3> view report rep2
***********************************************************************
Oracle GoldenGate Delivery for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
Windows x64 (optimized), Oracle 11g on Apr 23 2012 06:25:54
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Starting at 2013-01-17 08:54:05
***********************************************************************
Operating System Version:
Microsoft Windows Server 2008 R2 , on x64
Version 6.1 (Build 7601: Service Pack 1)
Process id: 7920
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
2013-01-17 08:54:05 INFO OGG-03035 Operating system character set identified as GBK. Locale: zh_Hans_CN, LC_ALL:.
replicat rep2
SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")
Set environment variable (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ogg@orclerp,password ***
--reperror default,discard
discardfile E:\ogg\dirdat\rep2_discard.txt, append, megabytes 1024
--NOBINARYCHARS
--NODYNSQL
--SHOWSYNTAX
grouptransops 1000
maxtransops 1000
BATCHSQL OPSPERBATCH 200
--BATCHSQL OPSPERBATCH 2000
BATCHSQL BATCHESPERQUEUE 100, OPSPERBATCH 8000
handlecollisions
sqlexec "Alter session set constraints=deferred"
Executing SQL statement...
2013-01-17 08:54:05 INFO OGG-00893 SQL statement executed successfully.
DBOPTIONS _MAXSEQUENCEDISTANCE 50
ASSUMETARGETDEFS
checksequencevalue
dynamicresolution
gettruncates
HANDLECOLLISIONS
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
DDLOPTIONS REPORT
map NANJING.STO_SELLER_BALANCE, target NANJING.STO_SELLER_BALANCE;
2013-01-17 08:54:05 INFO OGG-00506 Both GETTRUNCATES and DDL replication are enabled.
2013-01-17 08:54:05 INFO OGG-01815 Virtual Memory Facilities for: COM
anon alloc: MapViewOfFile anon free: UnmapViewOfFile
file alloc: MapViewOfFile file free: UnmapViewOfFile
target directories:
E:\ogg\dirtmp.
CACHEMGR virtual memory values (may have been adjusted)
CACHESIZE: 2G
CACHEPAGEOUTSIZE (normal): 8M
PROCESS VM AVAIL FROM OS (min): 4G
CACHESIZEMAX (strict force to disk): 3.41G
Database Version:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio
NLSRTL Version 10.2.0.5.0 - Production
Database Language and Character Set:
NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK"
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "ZHS16GBK"
***********************************************************************
** Run Time Messages **
***********************************************************************
Opened trail file E:\ogg\dirdat\lp000000 at 2013-01-17 08:54:05
MAP resolved (entry NANJING.STO_SELLER_BALANCE):
map "NANJING"."STO_SELLER_BALANCE", target NANJING.STO_SELLER_BALANCE;
Using following columns in default map by name:
SHIFT_DATE, POS_NO, BIG_TYPE, SALE_TYPE, FACT_SUM, ACCOUNT_SUM,
BALANCE, EARNING_SURPLUS, EXPORT_SURPLUS, PAYMENT_NO, MAGCARD_FLAG,
CHECK_FLAG, UP_FLAG
Source Context :
SourceModule :
SourceID : [../gglib/ggdbora/orautil.c]
SourceFunction :
SourceLine :
ThreadBacktrace : elements
: ]
: [E:\ogg\gglog.dll(?_MSG_ERR_ORACLE_SELECT_UNIQUE_KEY_FAILURE@@YAPEAVCMessage@@PEAVCSourceContext@@AEBV?$CQualDBObjNam
e@$00@ggapp@gglib@ggs@@PEBD2W4MessageDisposition@CMessageFactory@@@Z+0x124) ]
: ]
: ]
: ]
: ]
: ]
: ]
: ]
: ]
: ]
: ]
: ]
: ]
2013-01-17 08:54:06 ERROR OGG-00661 Error selecting unique keys for NANJING.STO_SELLER_BALANCE: ORA-00904: "VISIBILITY": invalid identifier,
SQL < SELECT key.key_name, key.column_name, key.descend FROM (SELECT c.constraint_name key_name, c.column_name
column_name, c.position position, 'ASC' descend FROM all_cons_columns c WHERE c.owner = :ow
ner1 AND c.table_name = :table1 AND c.constraint_name = ( SELECT MIN(con1.name) FRO
M sys.user$ user1, sys.user$ user2, sys.cdef$ cdef, sys.con$ con1,
sys.con$ con2, sys.obj$ obj1, sys.obj$ obj2 WHERE user
1.name = :owner2 AND obj1.name = :table2 AND cdef.type# = 3 AND bitand(cdef.defer,
36) = 4 AND con2.owner# = user2.user#(+) AND cdef.robj# = obj2.obj#(+) AND cdef.r
con# = con2.con#(+) AND obj1.owner# = user1.user# AND cdef.con# = con1.con# AND cd
ef.obj# = obj1.obj#) AND EXISTS ( SELECT 'x' FROM all_tab_columns t WHERE t.
owner = c.owner AND t.table_name = c.table_name AND t.column_name = c.column_name) UNION
SELECT i.index_name key_name, c.column_name column_name, c.column_position position, c.d
escend descend FROM all_indexes i, all_ind_columns c WHERE i.table_owner = :owner3 AND i.tab
le_name = :table3 AND i.uniqueness = 'UNIQUE' AND i.owner = c.index_owner AND i.index_name = c.index_name
AND i.table_name = c.table_name AND i.index_name in ( SELECT index_name FROM all_indexes
WHERE table_owner = :owner4 AND table_name = :table4 AND visibility != 'INVISIBLE'
AND uniqueness = 'UNIQUE') AND i.index_name NOT IN ( SELECT c.constraint_name FROM
all_cons_columns c WHERE c.owner = :owner5 AND c.table_name = :table5 AND c.constra
int_name IN ( SELECT c1.name FROM sys.user$ u1, sys.user$
u2, sys.cdef$ d, sys.con$ c1, sys.con$ c2,
sys.obj$ o1, sys.obj$ o2 WHERE u1.name = :owner6
AND o1.name = :table6 AND d.type# in (2, 3) AND (d.defer is NULL
OR d.defer = 0 OR bitand(d.defer, 36) = 4) AND
c2.owner# = u2.user#(+) AND d.robj# = o2.obj#(+) AND d.rcon# = c2.con#(+)
AND o1.owner# = u1.user# AND d.con# = c1.con# AND d.obj# = o1.obj#)
AND EXISTS ( SELECT 'X' FROM all_tab_columns t
WHERE t.owner = c.owner AND t.table_name = c.table_name
AND t.column_name = c.column_name)) AND (EXISTS ( SELECT 'x' FROM all_tab_co
lumns t WHERE t.owner = c.table_owner AND t.table_name = c.table_name AND t.column_n
ame = c.column_name) OR c.descend = 'DESC') ) KEY ORDER BY key.key_name, key.position >.
。。。。。 两边表的结构是一直的
SQL*Plus: Release 10.2.0.5.0 - Production on Thu Jan 17 10:12:36 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected.
SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui, supplemental_log_data_all from v$database;
SUPPLEME SUP SUP SUP
-------- --- --- ---
YES YES YES YES
SQL> desc NANJING.STO_SELLER_BALANCE
Name Null? Type
----------------------------------------- -------- ----------------------------
SHIFT_DATE NOT NULL VARCHAR2(8)
POS_NO NOT NULL VARCHAR2(4)
BIG_TYPE NOT NULL VARCHAR2(1)
SALE_TYPE NOT NULL VARCHAR2(1)
FACT_SUM NOT NULL NUMBER(12,2)
ACCOUNT_SUM NOT NULL NUMBER(12,2)
BALANCE NOT NULL NUMBER(12,2)
EARNING_SURPLUS NUMBER(12,2)
EXPORT_SURPLUS NUMBER(12,2)
PAYMENT_NO NOT NULL VARCHAR2(4)
MAGCARD_FLAG NOT NULL VARCHAR2(1)
CHECK_FLAG VARCHAR2(1)
UP_FLAG VARCHAR2(1)
目标端
SQL*Plus: Release 11.2.0.3.0 Production on 星期四 1月 17 10:16:17 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
已连接。
SQL> desc NANJING.STO_SELLER_BALANCE
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
SHIFT_DATE NOT NULL VARCHAR2(8)
POS_NO NOT NULL VARCHAR2(4)
BIG_TYPE NOT NULL VARCHAR2(1)
SALE_TYPE NOT NULL VARCHAR2(1)
FACT_SUM NOT NULL NUMBER(12,2)
ACCOUNT_SUM NOT NULL NUMBER(12,2)
BALANCE NOT NULL NUMBER(12,2)
EARNING_SURPLUS NUMBER(12,2)
EXPORT_SURPLUS NUMBER(12,2)
PAYMENT_NO NOT NULL VARCHAR2(4)
MAGCARD_FLAG NOT NULL VARCHAR2(1)
CHECK_FLAG VARCHAR2(1)
UP_FLAG VARCHAR2(1
NANJING.STO_SELLER_BALANCE的唯一索引
SQL> select index_name,column_name from dba_ind_columns where table_owner='NANJING' and table_name='STO_SELLER_BALANCE';
INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------------------------------------
SYS000999999 SHIFT_DATE
SYS000999999 POS_NO
SYS000999999 BIG_TYPE
SYS000999999 SALE_TYPE
SYS000999999 PAYMENT_NO
SYS000999999 MAGCARD_FLAG 本帖最后由 cargoo 于 2013-1-17 11:28 编辑
10g中ALL_INDEXES视图没有这个VISIBILITY 字段。
你给出的配置方向是错误的。实际复制方向是11g向10g复制。 问题解决：
2013-01-17 08:54:05 INFO OGG-01815 Virtual Memory Facilities for: COM
anon alloc: MapViewOfFile anon free: UnmapViewOfFile
file alloc: MapViewOfFile file free: UnmapViewOfFile
target directories:
E:\ogg\dirtmp.
CACHEMGR virtual memory values (may have been adjusted)
CACHESIZE: 2G
CACHEPAGEOUTSIZE (normal): 8M
PROCESS VM AVAIL FROM OS (min): 4G
CACHESIZEMAX (strict force to disk): 3.41G
Database Version:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio
NLSRTL Version 10.2.0.5.0 - Production
Database Language and Character Set:
NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK"
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "ZHS16GBK"
从这一段中得知ogg 连接到了10.2.0.5 上面去了，仔细检查参数配置，发现rep2中的 userid ogg@orclerp,password ogg配置有误，orclerp 这里写错了，写到源库上去了。。。
页:
[1]