在测试环境中发现了这个错误,根据错误信息初步判断,和高级复制环境有关。
在alert日志中,错误信息为:
Errors in file /data/admin/testdata/udump/testdata_ora_29441.trc:
ORA-26500: error on caching "NDMAIN"."CAT_AUTH_BAD_DRUG"
检查trace文件中的详细信息:
*** SESSION ID:(46.31281) 2008-04-04 13:31:03.942
kniacfcb: ORA-26500 (line 3015) column "PROCLAMATION_NUMBER" doesn not exist
*** 2008-04-04 13:31:03.979
kniacfcb-1: ORA-26500 for NDMAIN.CAT_AUTH_BAD_DRUG
kntklc: encountered error 26500 leading to ora-23474
ORA-26500: error on caching "NDMAIN"."CAT_AUTH_BAD_DRUG"
从这里的错误信息看,似乎是由于缺少一个列造成的,但是这个列在表中是存在的:
SQL> SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS
2 WHERE WNER = 'NDMAIN'
3 AND TABLE_NAME = 'CAT_AUTH_BAD_DRUG'
4 AND COLUMN_NAME = 'PROCLAMATION_NUMBER';
COLUMN_NAME
------------------------------
PROCLAMATION_NUMBER
查询了一下metalink,基本将问题锁定在一个bug上:Bug No. 3447035。根据文档的描述,高级复制环境中,主库的表添加一个字段后,生成复制支持,随后删除一个函数索引并重建一个新的函数索引,就可能在随后的DML中得到这个错误信息。
由于当前环境部署了高级复制环境,下面仿照这个步骤,看看能否重现问题:
SQL> CREATE TABLE NDMAIN.T (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));
Table created.
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT(GNAME => 'REP_GROUP', TYPE => 'TABLE',
3 NAME => 'T', SNAME => 'NDMAIN', USE_EXISTING_OBJECT => TRUE, COPY_ROWS => FALSE);
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> CREATE INDEX NDMAIN.IND_F_T_NAME ON NDMAIN.T(SUBSTR(NAME, 1, 5));
Index created.
SQL> INSERT INTO NDMAIN.T VALUES (1, 'A');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> BEGIN
2 DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY(GNAME => 'REP_GROUP');
3 DBMS_REPCAT.ALTER_MASTER_REPOBJECT (SNAME => 'NDMAIN', NAME => 'T', TYPE => 'TABLE',
4 DDL_TEXT => 'ALTER TABLE NDMAIN.T ADD (AGE NUMBER(3))');
5 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(SNAME => 'NDMAIN', NAME => 'T', TYPE => 'TABLE',
6 MIN_COMMUNICATION => TRUE);
7 DBMS_REPCAT.RESUME_MASTER_ACTIVITY(GNAME => 'REP_GROUP');
8 COMMIT;
9 END;
10 /
PL/SQL procedure successfully completed.
SQL> CREATE OR REPLACE FUNCTION NDMAIN.F_RETURN (P_NAME VARCHAR2) RETURN VARCHAR2
2 DETERMINISTIC AS
3 BEGIN
4 RETURN P_NAME || ':';
5 END;
6 /
Function created.
SQL> DROP INDEX NDMAIN.IND_F_T_NAME;
Index dropped.
SQL> CREATE INDEX NDMAIN.IND_F_T_NAME1 ON NDMAIN.T(F_RETURN(NAME));
Index created.
SQL> INSERT INTO NDMAIN.T VALUES (2, 'B', 1);
INSERT INTO NDMAIN.T VALUES (2, 'B', 1)
*
ERROR at line 1:
ORA-23474: definition of "NDMAIN"."T" has changed since generation of
replication support
ORA-26500: error on caching "NDMAIN"."T"
现在问题重现了,检查一下alert文件:
Errors in file /data/oracle/admin/predata/udump/predata_ora_3114.trc:
ORA-26500: error on caching "NDMAIN"."T"
详细信息:
*** SESSION ID:(19.47839) 2008-04-18 13:42:02.684
kniacfcb: ORA-26500 (line 3015) column "AGE" doesn not exist
*** 2008-04-18 13:42:02.686
kniacfcb-1: ORA-26500 for NDMAIN.T
kntklc: encountered error 26500 leading to ora-23474
ORA-26500: error on caching "NDMAIN"."T"
和上面环境中碰到的现象完全一样,现在已经可以确认就是这个bug导致的问题。
由于函数索引会导致Oracle在SYS.COL$中添加一个列,很可能是Oracle在处理这个添加列的时候出现了错误。
SQL> SELECT COL#, NAME, DEFAULT$ FROM SYS.COL$
2 WHERE OBJ# =
3 (SELECT OBJECT_ID FROM DBA_OBJECTS
4 WHERE OBJECT_NAME = 'T'
5 AND WNER = 'NDMAIN');
COL# NAME DEFAULT$
---------- ------------------------------ ------------------------------
1 ID
2 NAME
0 SYS_NC00004$ "NDMAIN"."F_RETURN"("NAME")
3 AGE
Oracle给出的解决方法是再次生成复制支持:
SQL> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(SNAME => 'NDMAIN', NAME => 'T', TYPE => 'TABLE',
3 MIN_COMMUNICATION => TRUE);
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> INSERT INTO NDMAIN.T VALUES (2, 'B', 2);
1 row created.
Oracle将这个bug的状态设置为NOT A BUG,不过个人认为即使这个问题真的不是bug,Oracle也应该在文档中明确的指出,建立、删除函数索引等操作执行后应该重新对表生成复制支持。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-243875/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-243875/