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.
Nilesh N. said:
Complicated Blog Boss
shrikant rao said:
🙂 ya it is…