Pages

Sunday, January 27, 2008

Issue with invalid objects while upgrading the database to 10.2.0.3

Some times we face a problem with invalid objects while upgrading a 9i database to 10.2.0.3 …When the objects in CTXSYS are invalid the upgrade will not proceed. In this case we need manually uninstall & install Oracle text. This is what we have done with one of our databases on a Solaris 9 unix server.

Please refer the following notes for uninstalling & installing the Oracle text

Note 275689.1 "Manual installation, deinstallation of Oracle Text 9.2.0.x"
Note.280713.1 Manual installation, deinstallation of Oracle Text 10gR1 and 10gR2

An additional point to remember ……….. We faced this problem while doing it in one database :

While installing oracle text, especially when we run the script “start ?/ctx/admin/defaults/drdefus.sql” (refer the aforementioned notes), it may throw the following error..

SQL> create index dropme_idx on dropme(name) indextype is ctxsys.context;
create index dropme_idx on dropme(name) indextype is ctxsys.context
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-04068: existing state of packages has been discarded
ORA-04063: stored procedure "CTXSYS.TEXTINDEXMETHODS" has errors

IN this case please do the following…….

Sql>connect ctxsys/****
Sql>@$ORACLE_HOME/ctx/admin/dr0def.sql
Sql>connect sys as sysdba
Sql>declare
ver varchar2(80);
begin
select ver_dict into ver from ctxsys.ctx_version;
dbms_registry.loaded('CONTEXT', ver,
'Oracle Text Release 'ver' - Production');

-- to do: a validation procedure
dbms_registry.valid('CONTEXT');
end;
/
Sql>exit