Tag Archives: shutdown abort

shutdown timeout

I do not like shutdown abort (see this post). I always use shutdown immediate and it always work… well almost always.

Today I discovered a 9iR2 new feature : shutdown timeout !
Shutdown Timeout
If all events blocking the shutdown do not occur within one hour, the shutdown operation aborts with the following message: ORA-01013: user requested cancel of current operation.

Ok, if and only if I am getting this ORA-1013, I shutdown abort, startup, shutdown immediate.

It is very unusual that a shutdown immediate does not terminate in 1 hour, and hard to reproduce. For this test case, I am doing a shutdown normal

1) make sure you have at least one other session open
2) shutdown normal
3) wait about 60 minutes (defined in _shutdown_completion_timeout_mins, not a supported parameter to change)


SQL> shutdown normal
ORA-01013: user requested cancel of current operation
SQL> 

Now we received a ORA-1013 (but I did not use CTRL-C). The instance is now half-stopped, most sessions and background processes like MMON, CJQ, SMCO are already dead and it is probably a good idea to restart it properly. Maybe with startup force and shutdown immediate.

SQL> startup force
ORACLE instance started.

Total System Global Area 1069252608 bytes
Fixed Size                  2166160 bytes
Variable Size             658510448 bytes
Database Buffers          402653184 bytes
Redo Buffers                5922816 bytes
Database mounted.
Database opened.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

This is all documented :
After ORA-01013 occurs, you must consider the instance to be in an unpredictable state…If subsequent SHUTDOWN commands continue to fail, you must submit a SHUTDOWN ABORT

I am extremly happy to have discovered this, because it will still allow a shutdown abort after one hour of shutdown immediate, which is an extremly rare case, but it is an enhancement for my backup scripts.

The reasons why I always avoid to shutdown abort

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.