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

From: Vladimir M. Zakharychev <>
Date: Thu, 7 Aug 2008 00:58:10 -0700 (PDT)
Message-ID: <>

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
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.)

REM Now just restart the instance in normal mode shutdown immediate

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.


   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm) Received on Thu Aug 07 2008 - 02:58:10 CDT

Original text of this message