It is a common practice to always shutdown abort the database before restarting and shutting in down immediate. This is because sometimes SHUTDOWN IMMEDIATE takes ages. For instance due to a huge transaction to be rollback.
I do not like it. At all.
First, chance exists that you won’t be able to start the database anymore. I have not heard or meet anyone who had this issue since Oracle7, but I still believe it.
Second, shutdown abort is very useful if something goes seriously wrong. But if something goes wrong, you may want to find out what it is.
Third, you may hit more bugs than if you do close normal. And you may get less help from support if this is due to an abusive shutdown abort. YMMV
Ok, small demo to preach to the converted
disclaimer: this demo is not innocent, do not try this on your database
SQL> create flashback archive fa tablespace ts retention 1 day;
Flashback archive created.
SQL> create table t(x number primary key);
Table created.
SQL> alter table t flashback archive fa;
Table altered.
SQL> insert into t values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from DBA_SEGMENTS where tablespace_name='TS';
no rows selected
I have created a flashback archive table, and the committed transaction is not written down to the flashback tablespace yet.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2227072 bytes
Variable Size 234882176 bytes
Database Buffers 171966464 bytes
Redo Buffers 8470528 bytes
Database mounted.
Database opened.
SQL> sho parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDO1
SQL> create undo tablespace undo2 datafile '/u02/oradata/@/undo2_01.dbf' size 10m reuse;
Tablespace created.
SQL> alter system set undo_tablespace=undo2;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2227072 bytes
Variable Size 234882176 bytes
Database Buffers 171966464 bytes
Redo Buffers 8470528 bytes
Database mounted.
Database opened.
I have switched undo tablespace. So far so good. But remember the flashback archive did not write to the flashback tablespace before shutdown abort.
SQL> drop tablespace undo1 including contents and datafiles;
drop tablespace undo1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_3544069484$' found, terminate dropping tablespace
You see… I cannot drop my old undo tablespace.
Q.E.D.
You say since Oracle 734, but I remember working on a 806 or maybe a 817 database and had a db that wouldn’t come up after an ABORT. And I only ran that AFTER immediate ‘failed’
I’ve always thought of that as a ‘last resort’ option. Maybe I am suffering from old wives’ tales?
The problem with abort has always been that it exposes you unnecessarily to any redo stream problem. Now, for years many people claim (one way or another) that the redo stream is inviolate, if you don’t trust Oracle recovery you should go work on Access, etc. The general claim is that redo and commit are atomic, either something is done or not, and recovery will always sort it out.
Well, I’m more paranoid than that, partly because I’ve seen standby go bonkers in several versions, and what is standby than continuous recovery (granted, once things get out into a network there are additional corruption vectors), and partly because a quick look at bugs in recent versions still finds recovery bugs (Bug 9406607 in my 2-minute search looks tasty, though not completely deadly). A less common but still seen issue is hardware errors (sometimes silly configuration that allows hardware to lie to Oracle, sometimes real problems like earlier SSD’s aging and randomly flipping bits).
I’ve certainly seen plain old crashing make problems in 8 and 8i. One of my favorites was a Sun box in a secure military center with the db set to autostart. Electrician comes in and starts working on the UPS, booting the Sun repeatedly as it tries to come up. All you need is the right paperwork…
Add a few new features, and look what you demo in a dozen lines. Respect shutdown au-thor-i-tah.
The main reason is that it does not work with “_disable_logging=true” 🙂
Pingback: Flashback Data Archive « Oracle Database Blog: Experiments & Learnings
yes, but what’s the alternative when your database is frozen?
when running the shutdown abort and no feedback from database ….
The only way is to kill the PMON, at OS level…
@gabone
if it is really really frozen, and shutdown abort, check this :
sqlplus -prelim
Thanks Laurent.
It wasn’t frozen due to ORA-00020 maximum number of processes exceeded.
The problem was on a RAC database and the running jobs were causing some deadlocks at OS level (due to intercluster communication), they were accessing same objects.
Shutting down with srvctl didn’t help…
Cheers.
By frozen I mean: any select couldn’t be run …
In alert log, errors related to locking resources.
kill -9 `ps aux|grep pmon|cut -f4 -d ‘ ‘`
I would proceed in this order
1) sqlplus / as sysdba : shutdown immediate
2) sqlplus / as sysdba : shutdown abort
3) sqlplus -prelim / as sysdba : shutdown abort
4) open a SR
5) kill SMON
6) get a coffee
6) kill -9 PMON
why do you prefer killing SMON first instead of killing PMON first?
sqlplus -prelim / as sysdba – this is used for dumping system state and hunganalyze info.
Did anyone shutdown a database using -prelim option?
sqlplus /nolog
SQL*Plus: Release 10.2.0.3.0 – Production on Fri Aug 19 21:04:30 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> set _prelim on
SQL> conn / as sysdba
Prelim connection established
SQL> select name from v$database;
select name from v$database
*
ERROR at line 1:
ORA-01012: not logged on
SQL>
I prefer kill without -9. shutdown abort with prelim release the shared memory segment, where kill -9 does not.
Jonathan Lewis has given a good reason for not using “shutdown abort” on
http://tonyhasler.wordpress.com/2011/08/22/why-is-acid-important/#comments
(See “Comment by Jonathan Lewis — August 23, 2011 @ 10:14 am”):
when Oracle says
“…Completed crash recovery …”,
this can be a lie
Pingback: Links for 2011-07-25 « Den's Random Ramblings of Rude Reality
Pingback: shutdown timeout | Laurent Schneider