Is this a CAREER-ENDING mistake ? Accidently ran catalog.sql
Richard
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 !
Palooka
Some utility doesn't work as expected, so you run scripts at random?
Is this a production database?
Palooka
P.S. Catalog is going to invalidate most of your packages. That's why
catproc is usually run soon after. The exact sequence depends on a few
things though, so it would probably be a good plan to get a competent
dba in at this point.
Chuck Whealton
Richard
There's no backup except there's an exp ...owner=sys, can that be
used to restore ? As I understand all system packages are owned by
sys, so the export of sys will restore it, I hope ?
Vladimir M. Zakharychev
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
Mark D Powell
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 --
ora...@msn.com
>
> - 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
Vladimir M. Zakharychev
Migrate mode disables internal triggers and sets some internal events
among other things. Those internal triggers in particular may (and
often do) get in the way and prevent proper catalog upgrade. Older
patchsets mandated setting _system_trig_enabled=false and some obscure
events in the init.ora before running the catalog upgrade, and, if
memory serves me right, in 9.2 it all was addressed in migrate mode
making patchset installations a bit less painful.
Regards,
Ana C. Dent
4e0139...@2g2000hsn.googlegroups.com:
> There's no backup except there's an exp ...owner=sys, can that be
> used to restore ? As I understand all system packages are owned by
> sys, so the export of sys will restore it, I hope ?
>
If you dodged this bullet I hope you'll learn a few things from it.
1) Objects owned by SYS can not be exported!
2) The database needs to be online & operational for import to work.
Since SYS owns all internal DB objects & the DB needs to be open & online
for import to function; thinking that you can import objects owned by SYS
is 100% WRONG/FLAWED/FATAL/NONSENSICAL!
As a learning exercise after this dust settles.
Take your dump file & invoke the import utility into a fresh,
empty & disposable instance.
Please use CUT & PASTE & post the results back here for posterity!
HTH & YMMV!
Ana C. Dent
news:Xns9AF3B986C264Ea...@69.16.185.247:
exp / file=sys.dmp log=sys.log owner=sys statistics=none
Export: Release 10.2.0.3.0 - Production on Thu Aug 7 18:26:57 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
EXP-00056: ORACLE error 1017 encountered
ORA-01017: invalid username/password; logon denied
Username: / as sysdba
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
- 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SYS
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SYS
About to export SYS's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
Where's the beef?
Lots of sizzle, but NO data!
IIRC, I tried this with either V7 or V8 & exp refused to do anything.
I'm not sure this behavior is better or worse than earlier versions.
It obvisouly is somewhat misleading at best & dangerous at worst.
Managing Oracle should only be done by trained professionals.
Well meaning ameteurs can be dangerous; especially with incomplete or
incorrect "knowledge".
You can lead some folks to knowledge but you can't always make them
think!
HAND!
Richard
Thank for your excellent advices. In a couple of days I have to take
some corrective action. Please keep your advices coming since the more
I know the better I be and I want to make good this time around.
Thank You In Advance. -- Richard.
Mark D Powell
>
> - Show quoted text -
Missed or overlooked that point. Thanks David.