Whenever we have
to shutdown a database, we would normally go by the golden rule – “The database
shutdown has to consistent”. So we shutdown the database with either “shutdown”
or “shutdown immediate” commands. Because we know if the database is shutdown
normal or immediate, it won’t ask for recovery and would be in consistent
state. So I just wondered what happens if I say “shutdown abort” at the SQL
prompt. The answer would be – you will have database shut down in an
inconsistent state.
So I just gave a try to identify what goes on in the background with the
processes when we shutdown the database with different options.
Shutdown:
Whenever we say “shutdown” at the SQL prompt, the oracle process will wait for
all the client connections to be closed before proceeding with the shutdown of
the database. Once all the connections are closed, ckpt process will fire a
complete checkpoint in which all the headers of the data files are freezed and
updated with latest SCN. After the checkpoint is done, pmon process will come
into action by cleaning the process variables and releasing the associated
memory. Later smon will close the pmon process thereby bringing down the
database. It also performs the cleaning up of the pmon process releasing memory
associated with it.
So the database is shutdown normal without any inconsistencies.
Shutdown Immediate:
With
shutdown immediate, in contrary to normal shutdown, the oracle process won’t
wait for all the client connection to be closed; instead it will kill all the
client connections. Once the client connections are closed, rest all processes
continue to action as in with normal shutdown. Hence even when the database is
shutdown using immediate option, the database is consistent.
Attached is the snapshot of alert log file:
Shutting down
instance: further logons disabled
Shutting down instance (immediate)
– This is where
all the connections are closed and PMON is invoked.
License high
water mark = 10
Wed Aug 10 22:26:44 2005
ALTER DATABASE CLOSE NORMAL
Wed Aug 10 22:26:44 2005
– This is where
PMON is closed and SMON is invoked.
SMON: disabling
TX recovery
SMON: disabling cache recovery
Wed Aug 10 22:26:44 2005
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 1913
Successful close of redo thread 1.
Wed Aug 10 22:26:44 2005
Completed: ALTER DATABASE CLOSE NORMAL
Wed Aug 10 22:26:44 2005
Shutdown Abort:
So my real concern is with this option. What happens when the database is
shutdown abort?
Whenever the database is shutdown using abort option, the ckpt process is by
passed, thereby closing the oracle bequeath process. This in turn invokes pmon
process to clean up the process and releases any memory associated with it,
thus bringing the database down. Eventually as the ckpt process is not fired
against the database before shutdown, the database will be in inconsistent
state. Oracle does not recommend using this option.
Lets take a case study. Consider we have a database by name MAHESH.
The processes
pertaining to the database would be:
$ ps –ef|grep MAHESH
oracle10 26996 1 0 14:25:55 ? 0:00 ora_p001_MAHESH
oracle10 26998 1 0 14:25:56 ? 0:00 ora_p002_MAHESH
oracle10 26992 26960 0 14:25:55 ? 0:02 oracleMAHESH (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle10 26974 1 0 14:25:50 ? 0:00 ora_psp0_MAHESH
oracle10 26978 1 0 14:25:50 ? 0:00 ora_dbw0_MAHESH
oracle10 26988 1 0 14:25:51 ? 0:01 ora_mmon_MAHESH
oracle10 26990 1 0 14:25:51 ? 0:00 ora_mmnl_MAHESH
oracle10 26994 1 0 14:25:55 ? 0:00 ora_p000_MAHESH
oracle10 26980 1 0 14:25:51 ? 0:00 ora_lgwr_MAHESH
oracle10 27018 27017 0 14:26:41 pts/9 0:00 grep MAHESH
oracle10 26972 1 0 14:25:50 ? 0:00 ora_pmon_MAHESH
oracle10 26986 1 0 14:25:51 ? 0:00 ora_reco_MAHESH
oracle10 27000 1 0 14:25:57 ? 0:00 ora_qmnc_MAHESH
oracle10 26976 1 0 14:25:50 ? 0:00 ora_mman_MAHESH
oracle10 27014 1 0 14:26:12 ? 0:00 ora_q001_MAHESH
oracle10 27007 1 0 14:26:07 ? 0:00 ora_q000_MAHESH
oracle10 26982 1 0 14:25:51 ? 0:00 ora_ckpt_MAHESH
oracle10 26984 1 0 14:25:51 ? 0:00 ora_smon_MAHESH
When the
database is shutdown using abort option, the oracle bequeath process with pid
26992 is closed, thereby by passing the ckpt process and leaving the data file
headers un-updated.
This could be
clearly seen in the alert log of the MAHESH database:
Shutting down
instance (abort)
License high water mark = 4
Instance terminated by USER, pid = 26992
Startup Force:
The startup force option internally shuts down the database and restarts it. So
if you have a look at alert log of this, you will see that the database is
shutdown using “shutdown abort” option and started normal. The database in this
case would be in consistent state as it started up normal.
Tue Jul 31
14:54:50 2007
Shutting down instance (abort)
License high water mark = 4
Instance terminated by USER, pid = 27249
Tue Jul 31 14:54:52 2007
Starting ORACLE instance (normal)
Tue Jul 31 14:54:52 2007
Where pid 27249
is the oracle bequeath process id.
$ ps –ef|grep MAHESH
oracle10 27249 26936 0 14:25:55 ? 0:02 oracleMAHESH (DESCRIPTION=(LOCAL=YES)(ADDRESS
For my remaining posts on Oracle DBA please click here
