Recently I came across something I wasn’t sure about. Have anyone ever tried to cancel the shutdown database session? Unfortunately this was the first time I tried.
I have changed the Database name to ORCL.

I will show you guys the normal scenario when you shutdown the database. Getting the alertlog details.

Shutting down instance (immediate)
Shutting down instance: further logons disabled
Tue Feb 26 12:43:30 2013
Stopping background process CJQ0
Stopping background process QMNC
Stopping background process MMNL
Stopping background process MMON
License high water mark = 8
All dispatchers and shared servers shutdown
ALTER DATABASE CLOSE NORMAL
Tue Feb 26 12:43:34 2013
SMON: disabling tx recovery
SMON: disabling cache recovery
Tue Feb 26 12:43:34 2013
Shutting down archive processes
Tue Feb 26 12:43:34 2013
ARCH shutting down
ARC4: Archival stopped
Tue Feb 26 12:43:35 2013

blah blah ....

Completed: ALTER DATABASE CLOSE NORMAL

Now here we can see the internal command that is fired.
ALTER DATABASE CLOSE NORMAL and Completed: ALTER DATABASE CLOSE NORMAL
Then comes the dismount stage.

ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
|
|
Tue Feb 26 12:43:40 2013
Stopping background process VKTM:
Tue Feb 26 12:43:42 2013
Instance shutdown complete

I can understand this.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Now what I cant understand is the below scenario that I faced.

oracle - ORCL> . oraenv
ORACLE_SID = [ORCL] ?
The Oracle base remains unchanged with value /u00/app/oracle
[host:/home/oracle]
oracle - ORCL> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Feb 9 14:47:17 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     55543
Current log sequence           55545
SQL> sho parameter db_name

NAME
------------------------------------
TYPE                                         VALUE
-------------------------------------------- ------------------------------
db_name
string                                       ORCL
SQL> shu immediate

ORA-01013: user requested cancel of current operation

SQL>

The shutdown was taking time as there was an active session. Instead of going shut abort from another session, I tried to cancel the current shutdown session.

Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process MMNL
Stopping background process MMON
License high water mark = 12
Sat Feb 09 14:53:16 2013
Active call for process 26870086 user 'oracle' program 'oracle@host (TNS V1-V3)'
SHUTDOWN: waiting for active calls to complete.
Sat Feb 09 14:54:29 2013
Instance shutdown cancelled
Sat Feb 09 14:55:11 2013

Now here is my question I can see the shutdown process has not reached to “ALTER DATABASE CLOSE NORMAL” before the cancel. So there is no chance that the process can reach to next stage i.e. “ALTER DATABASE DISMOUNT”. Then how come I see the Database is in mounted stage. Either I am missing something or its something wierd.

oracle - ORCL> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Feb 9 16:18:49 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME
---------
OPEN_MODE
--------------------------------------------------------------------------------
ORCL
MOUNTED

ok lets try it again. Open the database.

SQL> alter database open;

alter database open
Sat Feb 09 14:55:12 2013
FAST_START_MTTR_TARGET 1 is set too low, using minimum achievable MTTR 19 instead.
Thread 1 opened at log sequence 55545
Current log# 3 seq# 55545 mem# 0: /fprd/u03/oradata/ORCL/redo03a.log
Current log# 3 seq# 55545 mem# 1: /fprd/u01/oradata/ORCL/redo03b.log
Successful open of redo thread 1
|
|
QMNC started with pid=16, OS id=11207154
Completed: alter database open
Sat Feb 09 14:55:22 2013
FAST_START_MTTR_TARGET 1 is set too low, using minimum achievable MTTR 18 instead.

Now shutting it down and cancelling the session.

SQL> shu immediate
ORA-01013: user requested cancel of current operation

SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[host:/fconv/u06/BACKUP/ORCL/fs3]

Checking the status of the database.

oracle - ORCL> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Feb 9 16:18:49 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME
---------
OPEN_MODE
--------------------------------------------------------------------------------
ORCL
READ WRITE

SQL>

Now tell me why is it so. The database is in open stage and not in mount stage. Below is the alertlog.

Sat Feb 09 16:16:52 2013
Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
Stopping background process QMNC
Sat Feb 09 16:16:54 2013
Stopping background process CJQ0
Stopping background process MMON
License high water mark = 13
Sat Feb 09 16:18:40 2013
Instance shutdown cancelled

There is no “ALTER DATABASE CLOSE NORMAL” command initiated so it is in open stage which seems to be perfect.

Its wierd or I am seriously missing something. So guys fill it in if you understand.