Bruce,
Thanks for the info, it is a useful thing to remember.
Now for the post-game analysis: the way we found this
was that RMAN stopped functioning with a
"dbms_backup_restore.somethingorother must be
declared" because the one in the wrong schema was
invalid. RMAN logs in as sys.
When the oncall ran catproc again under the advice of
tech support, it recreated all the stored procedures
in the schema set by the "alter schema". This rendered
people logging in from sqlplus unable to find
dbms_application_info, although the public synonym was
pointing to the sys package.
This was the red herring that kept throwing us off -
the public synonyms were pointing to the sys objects,
but the processes kept trying to go to the schema set
by the login trigger. We were working under the
implicit assumption (oops! Unproven Assumptions Bite
DBAS, Details at 10) that the object refs in the rman
and sqlplus login code were hard-coded (after all, why
would you NOT hardcode the sys owner in your dd refs?
not like anyone else will ever own those objects) and
that somehow sys and the new schema were crossed up in
the data dictionary.
so, What We Learned:
- be vewwwy careful with alter schema command, wabbit
- apparently neither the sqlplus nor the rman code
use schema prefixes in their object references.
Lot of work for that little tidbit of info, I would
say!
;)
jack
- "Reardon, Bruce (CALBBAY)"
<Bruce.Reardon_at_comalco.riotinto.com.au> wrote:
> Jack,
> Something you may find useful if you're not already
> aware is the schemaname field in v$session.
> Compare this to username and this may help determine
> if set current_schema is being used.
>
> We use the set current_schema in a login trigger,
> though the trigger has smarts in it to only do it
> for application users and not for schema owners,
> sys, system etc.
>
> HTH,
> Bruce Reardon
>
> -----Original Message-----
> Sent: Thursday, 13 June 2002 4:20
> To: Multiple recipients of list ORACLE-L
>
>
> It was the "alter system set current_schema=x"
> statement after all.
>
> I am sure that this raises some interesting
> questions,
> if only I had time to dwell on it. Just now cleaning
> up all the broken indexes from the loads that
> abended
> when the db went down.
>
> I love this job. Where else do you get to play at
> work?
>
> jack
>
>
>
>
> > --- Jack Silvey <jack_silvey_at_yahoo.com> wrote: >
> > All,
> > >
> > > thanks for the input. Looks like someone
> > implemented
> > > a
> > > login trigger. haven't seen the code yet, but I
> > > would
> > > venture a guess he used the unsupported "alter
> > > system
> > > set current_schema=x".
> > >
> > > sometimes you live and learn, sometimes you just
> > > live!
> > >
> > > thx,
> > >
> > > jack silvey
> > >
> > >
> > > --- Hately Mike <Mike.Hately_at_churchill.com>
> wrote:
> > > > I don't hold out much hope here Jack.
> > > > It sounds like data dictionary corruption;
> maybe
> > > > somewhere round user$(?).
> > > > That's not to say the situation's
> irretrievable;
> > > > I've seen OTS fix some bad
> > > > situations in my time but I'm not sure that
> I'd
> > > want
> > > > to keep the database
> > > > even if Oracle Support can fix the problem.
> > > >
> > > > Regards,
> > > > Mike
> > > >
> > > > -----Original Message-----
> > > > Sent: 12 June 2002 14:23
> > > > To: Multiple recipients of list ORACLE-L
> > > >
> > > >
> > > > Listers,
> > > >
> > > > Our warehouse now has a split personality and
> we
> > > > have
> > > > a sev1 open on it. Suspect recovery is in the
> > > cards,
> > > > but want to avoid if possible.
> > > >
> > > > Yesterday, users unable to get to their own
> > > > functions.
> > > > Soon after, RMAN cannot find package
> > > > dbms_backup_restore, even though it exists
> under
> > > > sys.
> > > > Oncall ran the sql script to recreate - and
> the
> > > > pacakge was recreated under a schema called
> > > > dma_rbate2. RMAN now finds the package under
> > > > dma_rbate2, although it is invalid. Drop the
> > > package
> > > > under dma_rbate2, and now RMAN cannot find the
> > > > package
> > > > any longer, although it still exists under
> sys.
> > > >
> > > > Logged in as sys. Tried to desc
> > > dbms_backup_restore
> > > > -
> > > > no luck. Tried to desc sys.dbms_backup_restore
> -
> > > > success.
> > > >
> > > > Analyst reccomends running catalog.sql. Oncall
> > > does
> > > > so, and it creates as many packages as it is
> > able
> > > > under dma_rbate2.
> > > >
> > > > I get up this AM and can't login, because the
> > > > sessions
> > > > can't find the package
> > > > dma_rbate2.dbms_application_info.
> > > >
> > > > Anyone? Buhler? Buhler?
> > > >
> > > > thx,
> > > >
> > > > jack
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Reardon, Bruce (CALBBAY)
> INET: Bruce.Reardon_at_comalco.riotinto.com.au
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: jack_silvey_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Fri Jun 14 2002 - 11:18:22 CDT
