>From what I have read there are three problems that Bill has described.
As this seems urgent and it is still daytime the Pacific timezone, I can
offer some limited advice. In order of importance they would be ...
1. Recovery of the database.
2. Slow shutdown.
3. SQL statements hanging.
1. I strongly recommend the TAR (or iTAR) approach - if you flag
it as a production issue you should get a priority response. No Oracle
error messages were provided so it makes it hard to give advice.
Oracle Support should be able to talk you through the recovery process
with minimal risk of making the situation worse.
2. There can be many reasons for a slow shutdown. The main things
to look for would be long-running transactions that need to be rolled-back
during a shutdown. This can be quantified by looking at the active
transactions in v$rollstat. Once again there is not much info. to
go on here.
3. Again, there can be many causes for SQL statements hanging and
with not much info. to go on I would look at locking and the execution
plan. First check to see if there are any locks in the database using
dba_locks or v$locked_object. An SQL statement will sit and wait
for the lock to be released by default. Dba_locks and v$locked_object
will help identify offending sessions and the locks that they hold which
are blocking other SQL statements. The other thing you could look
at is the execution plan for a long-running (hung) statement. The
statement may be executing the hard-way ie. not using an index etc ...
The easiest way to verify the execution plan is to use the autotrace facility
in SQL*Plus (the plan_table needs to exist). The actual SQL statement
can be obtained either from the user or from v$sqlarea/v$sqltext for a
specific session.
I know this info. is brief and a bit vague but the best I can offer
with the lack of specific info.
Glen
Jonathan Gennick wrote:
Fellow list members, I received the following email
from a
reader a few minutes ago. If you skip down to where he talks
about backup, you'll see that he's in trouble with a
database that won't recover. I've already suggested that he
open a TAR, and that he supply more specifics as to error
messages and the like, but maybe someone on this list can
draw some conclusions from what he's told me so far. If
you're good at recovery, have a look at what he says. I'll
post his email address later if he says its ok, and I'll
pass on any advice/suggestions I receive in the meantime.
--
Best regards,
Jonathan Gennick
mailto:jonathan_at_gennick.com
* 906.387.1698
http://Gennick.com * http://MichiganWaterfalls.com
* http://MetalDrums.org
Tuesday, May 01, 2001, 10:27:51 PM, X wrote:
BC> Hi Jonathan,
BC> I always read and like your articles in the Oracle
BC> Magazine.
BC> Could you please give me a moment of your time ..
BC> I have some couple of pressing problems and questions
BC> if you don't mind as i am under fire at work ..
BC> I have trouble with some of the Oracle databases that
BC> i manage on Sun Solaris platform, sometimes i have my
BC> shutdowns hanging and this takes a very long time to
BC> complete. Also, hanging problems with some of the sql
BC> statements. Could you lend a hand about any possible
BC> clues as to what i should do or where i should look
BC> for answers.
BC> My last question is about hotback.
BC> I ran a hot backup yesterday and tried recovering
BC> today. I was faced with an Oracle error saying system
BC> tablespace needs more recovery and that open resetlogs
BC> will get an error after automatically applying the
BC> redo logs.
BC> My backup strategy went this way,
BC> i created a backup shell script and
BC> i put all tablespaces in hotbackup mode at the same
BC> time with this syntax..
BC> select 'alter tablespace' || tablespace_name ||'begin
BC> backup;'
BC> from dba_tablespaces
BC> where status <> 'INVALID';
BC> I copied my datafiles to backup using the syntax below
BC> ...
BC> !cp /u02/oradata/prod/system01.dbf /backup/prod
BC> !cp ....
BC> !cp ....
BC> !cp ....
BC> !cp ....
BC> then ended backup the same way as i began backup
BC> with the ||'end backup;'
BC> I backed up my controlfile
BC> and everything went well. Today, I restored my
BC> datafiles from backup and i was greeted with the
BC> problem i just expalined to you.
BC> I am the only dba DBA at this location
BC> and i need some advise on what to do as this is a
BC> serious issue with my supervisor.
BC> Thanks,
BC> Bill
BC> __________________________________________________
BC> Do You Yahoo!?
BC> Yahoo! Auctions - buy the things you want at great prices
BC> http://auctions.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan Gennick
INET: jonathan_at_gennick.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).
--
Glen Mitchell NZ Phone: +64 9 3730400
Energy Research Lab URL: http://www.peace.com
Peace Software Email: glen.mitchell_at_peace.com
Received on Wed May 02 2001 - 00:17:42 CDT
