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

From: Mark D Powell <>
Date: Thu, 7 Aug 2008 07:45:21 -0700 (PDT)
Message-ID: <>

On Aug 7, 3:58 am, "Vladimir M. Zakharychev" <> wrote:
> On Aug 7, 4:54 am, Richard <> 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 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)

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 -- Received on Thu Aug 07 2008 - 09:45:21 CDT

Original text of this message