OGG-01082 The call to the DBOCI_check_for_virtual_column: OCI error (1031-ORA-01031: insufficient privileges) determining if column is virtual
OGG-01082 The call to the DBOCI_check_for_virtual_column: OCI error (1031-ORA-01031: insufficient privileges) determining if column is virtual. Table: ODS.POS_INFO_F, Column ITEM_NO function from get_odbc_table_def failed with return code 0.
解决：
1 | grant all on sys.user$ to oggo; |
1.生产中升级了11.2.0.3到12.2.0.1数据库之后，启动ogg目标端复制进程，发现报错。
++++++++++++++++++++++++++++++++++++++++++++++++++++
2018-03-21 15:41:39 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, reputest.prm: REPLICAT REPUTEST started.
2018-03-21 15:41:40 ERROR OGG-01082 Oracle GoldenGate Delivery for Oracle, reputest.prm: The call to the DBOCI_check_for_virtual_column: OCI error (1031-ORA-01031: insufficient privileges) determining if column is virtual. Table: SCOTT.EMP1, Column EMPNO function from get_odbc_table_def failed with return code 0.
2018-03-21 15:41:40 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, reputest.prm: PROCESS ABENDING.
2018-03-21 15:47:55 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info all.
++++++++++++++++++++++++++++++++++++++++++++++++++++
2.这个错误从表面上看，就是权限不足(1031-ORA-01031: insufficient privileges)。怎样定位问题。
在复制进程中加入如下参数。对执行的语句做10046的trace跟踪。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | GGSCI 1> view params reputest REPLICAT REPUTEST setenv (NLS_LANG="AMERICAN_AMERICA.UTF8") USERID goldengate@clone, PASSWORD goldengate ASSUMETARGETDEFS DISCARDFILE /u01/goldengate/dirrpt/reputest.dsc,PURGE --NODYNSQL --NOBINARYCHARS --SHOWSYNTAX --GROUPTRANSOPS 1 --MAXTRANSOPS 1 --HANDLECOLLISIONS MAP SCOTT.EMP, TARGET SCOTT.EMP; MAP SCOTT.DEPT, TARGET SCOTT.DEPT; MAP SCOTT.BONUS, TARGET SCOTT.BONUS; MAP SCOTT.EMP1, TARGET SCOTT.EMP1; SQLEXEC "ALTER SESSION SET TRACEFILE_IDENTIFIER='OGG_DEBUG'" SQLEXEC "ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'" |
3.使用tkporf，格式化跟踪文件
1 2 3 | [oracle@lxtrac03 trace]$ tkprof srw_ora_32062_OGG_DEBUG.trc aa.prf TKPROF: Release 12.2.0.1.0 - Development on Wed Mar 21 15:42:35 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. |
可以明显看到引发报错ORA-01031语句。将语句放到sqlplus中执行。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | [oracle@lxtrac03 trace]$ more aa.prf TKPROF: Release 12.2.0.1.0 - Development on Wed Mar 21 15:42:35 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Trace file: srw_ora_32062_OGG_DEBUG.trc Sort options: default ******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ******************************************************************************** The following statement encountered a error during parse: SELECT col#, segcol# FROM sys.col$ c, sys.obj$ o, sys.user$ u WHERE c.name = 'EMPNO' AND c.obj# = o.obj# AND o.name = 'EMP1' AND o.owner# = u.user# A ND u.name = 'SCOTT' Error encountered: ORA-01031 ******************************************************************************** SQL ID: 2yzsfc3szyrva Plan Hash: 415205717 select value$ from sys.props$ where name = 'NLS_LANGUAGE' …………………… |
从执行结果看，是对user$没有权限。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | [oracle@lxtrac03 ~]$ sqlplus goldengate/goldengate SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 21 15:44:39 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Wed Mar 21 2018 15:41:38 +08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> SELECT col#, segcol# 2 FROM sys.col$ c, sys.obj$ o, sys.user$ u 3 WHERE c.name = 'EMPNO' 4 AND c.obj# = o.obj# 5 AND o.name = 'EMP1' 6 AND o.owner# = u.user# AND u.name = 'SCOTT'; FROM sys.col$ c, sys.obj$ o, sys.user$ u * ERROR at line 2: ORA-01031: insufficient privileges SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@lxtrac03 ~]$ |
在mos上面查找(1031-ORA-01031: insufficient privileges) user$ 。就发现有下列的文档
OGG-00665 OCI Error describe for query (status = 1031-ORA-01031: insufficient privileges) (文档 ID 2137888.1)
上面明确指出，rdbms从11g升到12c，但是goldengate还是在11.2。就会引发问题
+++++
Upgraded Oracle RDBMS from 11g to 12c, but Goldengate is still 11.2
++++
解决方案；
1 2 | conn / as sysdba grant all on sys.user$ to <username>; |
