Feb 13

Invalid ODCIPARTINFOLIST on Upgrade

I have been working on upgrading Oracle 12.1.0.2 databases to Oracle 19.3 when I ran into a problem that took me quite awhile to figure out. I did a search on My Oracle Support and a quick Google search and didn’t see anyone else have this problem. So hopefully this will save someone time in the future. Side note: I had the same problem upgrading from 12.1.0.2 to 18.3 but did not check 12.1.0.2 to 12.2.0.1.

During the upgrade, the DBUA gives me an error that the type SYS.ODCIPARTINFOLIST is invalid. After a few more minutes, I get a lot of error messages and XDB will not upgrade correctly. As I learned, XDB relies heavily on indextype CTXSYS.CONTEXT, which depends on type CTXSYS.TEXTINDEXMETHODS, which depends on type SYS.ODCIPARTINFOLIST. I boiled the root cause of the problem down to these three objects in the database.

I figured out how to resolve the issue and move forward. I cannot stop this problem from occurring. But if I fix the issue and press the Retry button on the DBUA, it still will not upgrade the database for me so I am left with a manual upgrade.

Fixing the first two invalid objects is easy. Just compile them. Fixing the CONTEXT indextype is a bit more complicated. Here are the steps I used to get everything valid again and get my database upgraded.

$NEW_HOME/bin/dbupgrade -l /tmp/19c_upgrade
alter type sys.odcipartinfolist compile;
grant execute on odcipartinfolist to public;
alter type ctxsys.textindexmethods compile;
drop indextype ctxsys.context;
@?/ctx/admin/catctx.sql password SYSAUX TEMP LOCK
@?/rdbms/admin/utlrp.sql
exec dbms_registry.valid('CONTEXT');

Irrespective of the nature of the appliances, its performance level and period of warranty, there is no side effect attached to male enhancement pills as they are composed of the same active ingredient and helps man getting back sildenafil online no prescription to the normal sexual mode. Some of the most common are: Chronic fatigue and irritability Morning sickness and lethargic feeling Emotional instability due to unstable chemicals in brain Digestive viagra samples problems like diarrhea and stomach ache Inability to lose weight. The profound joy of feeding, clothing, teaching and caring for a child cannot be order viagra viagra see now described or replicated in any way. Taking overdose of the medicine would not minimize the discomfort generic india levitra of migraine headaches attack.

At this point my database was upgraded and ready to roll. So let’s go over the steps above. After starting the database in UPGRADE mode, I used the dbupgrade utility to perform the manual upgrade. I still received the same errors that DBUA reports. Once the upgrade was complete, I compiled the ODCIPARTINFOLIST type. That was easy. For some reason I cannot figure out, the grant to PUBLIC on the type is dropped during the upgrade. I had to issue the grant otherwise the next compile would not be successful. Then compile the TEXTINDEXMETHODS type for Oracle Text.

I wish I could have just compiled the CONTEXT indextype but it would never compile for me. So I had to drop the indextype and then run the catctx.sql script to get it created again. Then run utlrp.sql to recompile everything else.

A note on dropping the indextype. If you have indexes that reference the indextype, you will receive an error. Query DBA_INDEXES where ITYP_OWNER=’CTXSYS’ to find any indexes you have using this indextype. Use DBMS_METADATA.GET_DDL to obtain the statement to recreate those indexes. Drop the indexes and then drop the index type. After you have the database up and running, recreate the indexes.

I spent the better part of a week working through all of this so hopefully this blog post finds you if you have a similar problem and you can be on your way much quicker than I was.

bpeasland