some oracle schema objects invalid after the upgrade

[maswien]

I upgraded one of our databases, there are some objects under XDB and ORDPLUGINS invalid, any idea?

Thanks

 

[SantaMufasa]

Yes, maswein...Following the upgrade, did you re-run these Oracle's data dictionary scripts?:

%ORACLE_HOME%\rdbms\admin\catalog
%ORACLE_HOME%\rdbms\admin\catproc
%ORACLE_HOME%\rdbms\admin\catblock
%ORACLE_HOME%\rdbms\admin\catexp
%ORACLE_HOME%\rdbms\admin\utlxplan
%ORACLE_HOME%\rdbms\admin\utltkprf

("\" become "/" for Unix.)

Let us know.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[karluk]

Did you run utlrp.sql in the $ORACLE_HOME/rdbms/admin directory? That's one of the last steps in the upgrade and should recompile any objects that in reality are valid, but were left in an invalid status by the upgrade. If you've done so and the objects are still invalid, it will be necessary to investigate why.

I try to make it a habit to query prior to the upgrade for invalid objects and then compare what's invalid after the upgrade. That tells me whether the upgrade introduced new problems or whether the objects were already invalid beforehand.

Incidentally, I hate to disagree with my esteemed colleague, SantaMufasa, but in general you shouldn't be running the catalog scripts he recommends. Instead, it's better to stick closely to the installation instructions for the appropriate catalog script(s) to run. For example, the 10.2.0.3.0 patch set instructions tell you to run catupgrd.sql on each database affected by the upgrade, while the April, 2007 critical patch update says to run catcpu.sql. Most likely its harmless to recreate the entire catalog the way SantaMufasa says, but it's unnecessary and Oracle support won't like it if you have a problem and they find out you haven't followed their installation instructions.

 

[SantaMufasa]

Karl, In no wise do I suggest that you ignore any scripts/instructions that the install/upgrade notes suggest. Just the opposite.

...In general you shouldn't be running the catalog scripts he recommends.

I am not aware that any of the data dictionary scripts cause any damage by re-running them. I would be interested in seeing any Oracle guidance that discourages their use or suggests that running those scripts can cause damage.

I listed the scripts, above, as examples of the types of scripts that any new Oracle installation/upgrade requires. If there are additional scripts required by an installation/upgrade, then, by golly, run them.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[karluk]

The underlying problem with your advice is that a patch set or quarterly patch does NOT require any of these catalog scripts to be run. Routinely run them as part of an upgrade is in itself a deviation from the installation instructions. For the 10.2.0.3.0 patch set, the only script that needs to be run is catupgrd.sql, and for the April CPU, the only one need is catcpu.sql.

Of course the real issue is what patching maswien is doing, and what the instructions for that patch say. Maswien, can you clarify what exactly you are patching?

 

[karluk]

Judging from thread1177-1366362, maswien is probably upgrading from 9i enterprise edition to 10g standard edition via export/import. I see that the export script provided by SantaMufasa excluded only SYS, SYSTEM and DBSNMP from the export. That makes it likely the import into 10g standard edition created a bunch of 9i compatible objects in the XDB and ORDPLUGINS schemas. There's no reason to believe that the 9i objects are compatible with 10g, so it's not too surprising that there are invalid objects in both schemas. If you can afford the time to start over and redo the export/import into a clean 10g database, that's what I would do. Then you can modify the export to exclude XDB and ORDPLUGINS, along with any other 9i schemas that aren't part of your application.

 

[maswien]

Hi Karl,

If that is the case, can i just drop XDB and ORDPLUGINS schema, and recreate them in the 10g database? I found the article in metalink there are manual steps to do that.

Thanks

 

[karluk]

That is certainly a reasonable approach. The only concern I have is that there may be other schemas in addition to XDB and ORDPLUGINS that maybe should have been left out of the import. If you tighten up your export to include only your application schemas, then you won't have to worry about what other 10g schemas might contain imported 9i objects.

That being said, I don't want to discourage you from dropping and reinstall XDB and ORDPLUGINS. Since you have found documentation on how to do this, you can be confident of getting a clean install.