Catbundle causes sessions using text index to throw ORA-4068 on RAC
- From: Yong Huang <yong321@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 26 Mar 2010 13:51:44 -0700 (PDT)
FYI,
On two Oracle 10.2.0.4.0 RAC databases, after we applied Oct-2009 CPU patch,
both times some users got errors similar to the following:
ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in textindexmethods.ODCIIndexUpdate
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "CTXSYS.DRIXMD", line 438
ORA-04061: existing state of package body "CTXSYS.DRIUTL" has been invalidated
ORA-04065: not executed, altered or dropped package body "CTXSYS.DRIUTL"
ORA-06508: PL/SQL: could not find program unit being called: "CTXSYS.DRIUTL"
ORA-06
SQL Statement:
...
This is because at the end of the patch, $ORACLE_HOME/rdbms/admin/catbundle.sql
is run. On RAC, we apply the patch on local node, one node at a time. Before
the patch, VIP is relocated to another node and the services on this node are
stopped. So apps are running on other nodes. On the last node only, we run
catbundle.sql. This script creates catbundle_CPU_<DBNAME>_APPLY.sql, where
there is
@?/ctx/admin/driutl.plb
which replaces ctxsys.driutl package body. If a session on *any* node of the
RAC updates a table using a text index after driutl package body is replaced
with new code, the session will get the above error. (If it's a delete, the
routine on the first line will be ODCIINDEXDELETE). This means that the rolling
nature of the patch has a risk on production.
Eddie Awad summarizes three ways to avoid ORA-4068:
http://awads.net/wp/2007/04/04/here-is-how-to-unpersist-your-persistent-plsql-package-data/
But all need application code change. Since CPU patch is done only once per
three months, to us, it's not worth it.
The package driutl is not changed much. I'm considering commenting out the line
in catbundle_CPU_<DBNAME>_APPLY.sql and manually run it. (Also need to comment
out the last line "@&sf" in catbundle.sql.)
If you need to reproduce the error:
create table testctx as select rownum id, object_name name from user_objects;
create index indctx_testctx on testctx (name) indextype is ctxsys.context;
In another session (invalidate instead of replacing the package, because we
don't have slightly modified code which is needed in 10g and up):
exec dbms_utility.invalidate(<object_id of ctxsys.driurl package>)
Back to the first session, run any of the following:
select * from testctx where contains(name, '<some name in testctx>') > 0;
delete from testctx where id = 1;
update testctx set name = 'xx' where id = 1;
--
//www.freelists.org/webpage/oracle-l
Other related posts:
