Re: Is this a CAREER-ENDING mistake ? Accidently ran catalog.sql

On Aug 7, 11:15 am, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> On Aug 7, 9:45 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Aug 7, 3:58 am, "Vladimir M. Zakharychev"
>
> > <vladimir.zakharyc..._at_gmail.com> wrote:
> > > On Aug 7, 4:54 am, Richard <RSL..._at_gmail.com> wrote:
>
> > > > Platform 9i on solaris.
>
> > > > EXP(ort) was not functioning correctly so I ran catexp.sql as sys.
> > > > Still had some minor problem, at this point I ran CATALOG.sql because
> > > > some notes seem to suggest CATALOG.sql calls catexp.sql so why not run
> > > > the whole thing. Only to find it is a NO-NO against an existing db.
> > > > When I ran it it errors out with tons of display so I ctrl-C out of it
> > > > and check what catalog.sql does and got the shattering news that it
> > > > rebuilds the data dictionary, ok if you are building new db not
> > > > existing one.
>
> > > > I can still connect to the db but can't do much else. Says invalid pl/
> > > > sql packages when doing simple select * from dba_users, I was afraid
> > > > to even look at the output. Please help !
>
> > > Not much of a worry actually, as catalog+catproc combo is invoked
> > > routinely as part of patchset installation. It's pretty safe to redo
> > > in case of trouble (though proper backup is always a must beforehand.)
> > > What you need to do now is this (in SQL*Plus on server console,
> > > comments embedded, can be removed):
>
> > > REM you must be connected AS SYSDBA
> > > conn / as sysdba
>
> > > REM stop the instance
> > > shutdown immediate
>
> > > REM and start it in migration mode, this is essential
> > > REM when running most of the database catalog scripts
> > > REM (including catexp, if I'm not mistaken)
> > > startup MIGRATE
>
> > > REM If your system appears to be missing the catpatch.sql
> > > REM script then it's time to apply the 9.2.0.8 patchset. :)
> > > REM Download it from MetaLink, study the accompanying readme
> > > REM file and do precisely what it says. This should
> > > REM automagically fix your problem if performed correctly.
> > > spool patch.log
> > > @?/rdbms/admin/catpatch
> > > spool off
>
> > > REM Review patch.log here for any signs of trouble. Some
> > > REM errors during upgrade are ignorable, some are not.
> > > REM Check the patchset readme for details on which errors
> > > REM are expected and can be ignored.
>
> > > REM This should revalidate all PL/SQL objects that were
> > > REM invalidated during the upgrade. Check the output, it
> > > REM should ideally show 0 invalid objects left (unless there
> > > REM are some user objects that are known to be invalid,
> > > REM like those still in development.)
> > > @?/rdbms/admin/utlrp
>
> > > REM Now just restart the instance in normal mode
> > > shutdown immediate
> > > startup
>
> > > Review patch.log for errors (some can be ignored, some should be dealt
> > > with.) If everything checks out ok in the log then your db should be
> > > ok either. SELECT comp_id, comp_name, version, status FROM
> > > dba_registry; will show you which components you have installed and in
> > > which state they currently are. If any of them are not VALID, you may
> > > need to research the reason and fix. The most important components
> > > are, obviously, CATALOG and CATPROC, these must be valid at all times
> > > except during upgrade.
>
> > > In the future, remember to properly backup your database before you
> > > are going to do anything that might affect the dictionary, like
> > > patchset install or some component install/remove. Export is NOT a
> > > backup, not even a substitution to a backup. Don't rely on EXP/IMP as
> > > recovery tools, for they are not.
>
> > > Hth,
> > >    Vladimir M. Zakharychev
> > >    N-Networks, makers of Dynamic PSP(tm)
> > >    http://www.dynamicpsp.com
>
> > I would follow Vladimir's advice except I do not know any valid reason
> > why you would need to be in migrate mode.  I have had to rerun catalog/
> > catproc after someone else messed up the database and I just used
> > restricted session to keep everyone out.  When done running catalog
> > and catproc check the status of all existing sys owned objects.  You
> > can use utlrp to try to revalidate if anything has a status of other
> > than VALID.  If you were not a sysdba (user SYS) when you re-ran
> > catalog I would drop any objects that got created under the wrong
> > username before running catalog and catproc.
>
> > HTH -- Mark D Powell --- Hide quoted text -
>
> > - Show quoted text -
>
> His instructions state to run the catpatch.sql script, not merely
> catalog.sql and catproc.sql, as it is performing some actions which
> require the database be open in MIGRATE mode.  It will fail otherwise.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

Missed or overlooked that point. Thanks David.

• Mark D Powell --