RE: Can't login to oracle other than SYSDBA
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <davidsharples@xxxxxxxxx>, <mmrashid@xxxxxxx>
- Date: Fri, 9 Jun 2006 16:56:27 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of David Sharples
Sent: Friday, June 09, 2006 10:38 AM
To: mmrashid@xxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxx
<mailto:mmrashid@xxxxxxx> > wrote:
My questions:
1. what could cause this?
2. once this happens, what things should look for (as SYSDBA) to provide
some clues?
Other related posts: