Re: Oracle 9.2.0.3

vanderhoeven_at_ping.be (Cedric Vonck) wrote in message news:<71aee92e.0307150427.124b93b1_at_posting.google.com>...
> Thank you all for replying.
>
> I use Windows XP Professional with Service Pack 1.
> With all the latest updates available from Microsoft.
>
> My database was corrupt:
> => After i ran the catexp.sql for recreation of the Export /
> Import views, I did a select * from all_objects where status =
> 'INVALID', this query returned about 500 invalid objects, ranging from
> java classes, packages to views, triggers..... and so on.
> => So, I re - ran catalog.sql to recreate my data dictionary.
> I did a select * from all_objects where status =
> 'INVALID'.
> Result 100% of my objects where invalid.
> * No compile time for my objects anymore.
> => So then, via the database configuration Assistant (dbca), I
> dropped the database, and created an other one. The creation process
> gave then various ora errors. (I did this 2 months ago)
>
> My server was warped.....
> => With that I mean, there is no way to recover from failed oracle
> installation process, you have to first backup everything that you
> can, format the whole lot and re-install all of it.
> => Oracle & uninstallation AND applying a patchset on top of that.
> This combination does not work very well.
>
> Yes I read the documentation carefully.
> Yes I have experience in applying patchsets from Oracle (only for
> developer).
> Yes I have experience with Oracle as developer and little less with
> DBA
>
> No I do not recall the explicit error message(s) anymore.
>
> How I discovered this?
>
> exp username/password_at_database file=export.dmp
>
> => Oracle ORA 00942 Table or view does not exist.
> => Export / Import views don't exist.
> ...
> From metalink.oracle.com => run the catexp.sql to recreate the
> export/import views
>
> Oracle did even ask me to open a tar (technical assistance request)
> for this issue because somebody else also experienced problems with
> this patchset for NT.
> I could not do that because this was done in my spare time, not
> company time.
> We have very strict rules here for demanding a tar to oracle.
>
>
> Thanx again for replying.

first off, you most likely needed to run the recompile utility after rebuilding the dictionary. This is covered in the readme. if something caused a package such as dbms_standard to become invalid, most everything would also be invalid.

you can set an event such that dependencies are not tracked until after the upgrade. This was the standard method as far back as 8.1.7.3.0 - maybe earlier.
again, this is covered in the readme.txt.

ALTER SESSION SET EVENTS '10520 TRACE NAME CONTEXT FOREVER, LEVEL 10'; this is undone by the following statement:

ALTER SESSION SET EVENTS '10520 TRACE NAME CONTEXT OFF'; here is the utility for recompiling invalid objects:

@%ORACLE_HOME\rdbms\admin\utlrp.sql;

second, there is a known issue (as mentioned in the readme.txt) that is fixed in 9.2.0.3 patch2 regarding EXP. the error that is thrown is not ORA-942, but that more than one row is returned by an exact fetch. I downloaded 9.2.0.3 patch 2 for win32 yesterday, but have not yet had a chance to start testing it. This may might be *your* bug, but there could certainly be others.

hth.

Pd Received on Tue Jul 15 2003 - 12:51:54 CDT