RE: Can't login to oracle other than SYSDBA

1. Sharples is right on target.
2. Since ?.?.?.? when Oracle allowed opening of the database before everything is totally resolved on a warm start, certain resources may be tied up in recovery or rollback that prevent less than DBA logins. This matches your symptoms, but of course that does not prove it is happening to you. I’m guessing that some stack of a long broken transaction combined with some variety of auditing might hang up non DBA logins, or it could be something else. The duration probably has some functional relationship to the excess entropy you’re causing in the universe (42 times something), bringing us closer to that last restaurant… (So stop doing cold backups, just make sure you know how to make hot backups bulletproof and/or move to RMAN or both – DBAs should wear a belt and braces because the world is out to get you.)
3. Clues would be looking for rollback activity and/or tablespaces not yet on line. Did you try every single user, or just some that happen to have huge transactions with auditing? Making a user that doesn’t do a doggone thing ever and has no logon triggers and uses system for temporary might help you narrow down whether any user at all can log in. If the never does anything, has no triggers user can log in, then it something pending from the shutdown is the problem with the regular users.
4. If something pending is the reason the regular users can’t log in yet, I hope that really drives home why your cold backup is no better than a hot backup, and why any backup should not be scheduled against long running massive transactions (they can be long running and small and it is not *that* big a problem, and if they are massive but short you can avoid them easily.) If you really, really, really want a “COLD” backup that is the tiniest bit better than a “HOT” backup, then you have to complete the startup restrict and shut down normal after the requisite checkpoint/switch logfile/and archive so the “COLD” backup does not have online redo and rollback to process. But of course you can do the same dance and get a recoverable tape set from a “HOT” backup without the extra overhead of killing jobs and making the system unavailable. So do that instead.
5. Did I mention I can’t think of a valid reason to do a “COLD” backup?

Regards,

mwf

PS: You can blame me (at least in part) for insisting (as part of VLDB) that Oracle let us start working on the database as DBA before everything is neat and nice. I guarantee that is better than the alternative.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of David Sharples
Sent: Friday, June 09, 2006 10:38 AM
To: mmrashid_at_att.com
Cc: oracle-l_at_freelists.org
Subject: Re: Can't login to oracle other than SYSDBA

stop doing a cold backup if that is the cause.... Simple answer but could be the most effective
On 09/06/06, Rashid, Mohammad M, NTWOP < mmrashid_at_att.com <mailto:mmrashid_at_att.com> > wrote:
My questions:
1. what could cause this?
2. once this happens, what things should look for (as SYSDBA) to provide some clues?

--
http://www.freelists.org/webpage/oracle-l