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: 1) be vewwwy careful with alter schema command, wabbit 2) 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)" <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
