Oracle Database Startup / Shutdown process
Starting/shutting down a oracle database seems very simple for
new DBA’s, just connect and put “startup” or “shutdown immediate” command, but
a thorough understanding of its stages is needed to be able to troubleshoot
startup and shutdown related issues. It is mandatory for any Oracle DBA to have
proper understanding of startup and shutdown process.
Starting up Oracle Database:
To start a oracle database you need to login as “sysdba”, on “startup”
command found in sql prompt it search for parameter file (spfile) to get
configuration of oracle instance. The path of parameter file located by referring
environment variable on the system (ORACLE_HOME, ORACLE_SID..). Oracle Database
startup process consists of three stages.
Stage 1: NOMOUNT =>
startup nomount
Stage 2: MOUNT =>
startup mount
Stage 3: OPEN =>
startup
When we fire “startup” command once connected as sysdba, in the background
above three stages are followed to open the database.
$sqlplus / as sysdba
SQL>startup
ORACLE instance started.
Total System Global Area
732352512 bytes
Fixed Size
1339036 bytes
Variable Size
440402276 bytes
Database Buffers
285212672 bytes
Redo Buffers
5398528 bytes
Database mounted.
Database opened.
As you can see it the startup command automatically takes the
database in Open mode. However some operations, like restores and recovers
require decomposing the startup process. In this case switching up from one
mode to the next one is done using an "alter database" command.
Three stages and process of bringing oracle database up is
described as follows.
1.
“Nomount” Stage:
This is the first stage in the startup process. You can
start Oracle database in nomount mode using the command
SQL>startup nomount
ORACLE instance
started.
Total System Global
Area 732352512 bytes
Fixed Size 1339036 bytes
Variable Size 440402276 bytes
Database Buffers 285212672 bytes
Redo Buffers 5398528 bytes
When you execute the above command, an Oracle instance is
started. When instance starts it will read the initialisation file (commonly
known as parameter file) called init.ora file. From this parameter file the
instance will know about the size of SGA, PGA, database buffer size and other
configurable parameters. The instance will also start the Oracle background
process such as (PMON, SMON, LGWR etc). This stage also opens the alert log and
the trace files.
Check the running process
oracle@vmoratest1:/home/oracle/
[DBC] ps -ef |grep DBC
oracle 3999
1 0 09:12 ? 00:00:00 ora_pmon_DBC
oracle 4001
1 0 09:12 ? 00:00:00 ora_vktm_DBC
oracle 4005
1 0 09:12 ? 00:00:00 ora_gen0_DBC
oracle 4007
1 0 09:12 ? 00:00:00 ora_diag_DBC
oracle 4009
1 0 09:12 ? 00:00:00 ora_dbrm_DBC
oracle 4011
1 0 09:12 ? 00:00:00 ora_psp0_DBC
oracle 4013
1 0 09:12 ? 00:00:00 ora_dia0_DBC
oracle 4015
1 1 09:12 ? 00:00:00 ora_mman_DBC
oracle 4017
1 0 09:12 ? 00:00:00 ora_dbw0_DBC
oracle 4019
1 0 09:12 ? 00:00:00 ora_lgwr_DBC
oracle 4021
1 0 09:12 ? 00:00:00 ora_ckpt_DBC
oracle 4023
1 0 09:12 ? 00:00:00 ora_smon_DBC
oracle 4025
1 0 09:12 ? 00:00:00 ora_reco_DBC
oracle 4027
1 0 09:12 ? 00:00:00 ora_mmon_DBC
oracle 4029
1 0 09:12 ? 00:00:00 ora_mmnl_DBC
oracle 4031
1 0 09:12 ? 00:00:00 ora_d000_DBC
oracle 4033
1 0 09:12 ? 00:00:00 ora_s000_DBC
oracle 4106
3356 0 09:12 pts/1 00:00:00 grep DBC
Instance in NOMOUNT mode, it is only possible to access and interact
to the spfile information
SQL> show parameter
name
NAME TYPE
VALUE
--------------------------------
----------
------------------------------
db_file_name_convert string
db_name string ULTHAN
db_unique_name string ULTHAN_SITE1
global_names boolean
FALSE
instance_name string DBC
lock_name_space string
log_file_name_convert string
service_names string DBC.it.dhu-domain.com
SQL> show parameter
spfile;
NAME TYPE VALUE
-------------------------
----------- ------------------------------
spfile string
/u00/app/oracle/admin/DBC/pfile/spfileDBC.ora
To access some instance views
SQL> select
INSTANCE_NAME,HOST_NAME,STATUS from v$instance;
INSTANCE_NAME HOST_NAME STATUS
----------------- ---------------------------------
------------
DBC vmoratest1.it.dhu-domain.com STARTED
2.
“Mount” Stage:
The next stage after NOMOUNT is called MOUNT. You can manually
start an Oracle database in MOUNT stage using the command
SQL>startup mount
Or when database is already in nomount stage then you can change
the stage by running the command
SQL>alter database mount;
When database goes into mount stage, it will read the control
files specified in the parameter file. The control files contain the
information about the physical structure of the database. So the control file
will have the names and locations of all the datafiles and online redo log
files. At this stage these datafiles and log files are not opened.
SQL> show parameter
control_files
NAME TYPE VALUE
------------------- ------------- ----------------------------------------------------
control_files string /u01/oradata/DBC/control01DBC.dbf, /u02/oradata
/DBC, /control02DBC.dbf,/u03/oradata/DBC
/control03DBC.dbf
NAME TYPE VALUE
------------------- ------------- ----------------------------------------------------
control_files string /u01/oradata/DBC/control01DBC.dbf, /u02/oradata
/DBC, /control02DBC.dbf,/u03/oradata/DBC
/control03DBC.dbf
Some DBA activities and maintenance can only be performed when
the Oracle database is MOUNT stage. For example Oracle full database recovery
can be done only when the database is in mount stage. If you want to rename a
datafile you may need to take the database to mount stage unless the tablespace
of the datafile is already offline.
SQL> select
open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> select name
from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/DBC/system01DBC.dbf
/u01/oradata/DBC/sysaux01DBC.dbf
/u01/oradata/DBC/undotbs01DBC.dbf
/u01/oradata/DBC/users01DBC.dbf
SQL>select member
from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/oradata/DBC/redog1m1DBC.dbf
/u02/oradata/DBC/redog1m2DBC.dbf
/u01/oradata/DBC/redog2m1DBC.dbf
/u02/oradata/DBC/redog2m2DBC.dbf
/u01/oradata/DBC/redog3m1DBC.dbf
/u02/oradata/DBC/redog3m2DBC.dbf
6 rows selected.
3. “Open” Stage:
The final stage in the Oracle startup process. When the database
is open then only normal database operations can takes place. Which means users
and applications can login and start reading/writing data.
Running the command below will start the Oracle database and put
into OPEN stage.
SQL>startup
And if the database is already in MOUNT stage then you can open
the database using the command
SQL> alter database open;
Database altered.
SQL> select status,open_mode from v$instance,v$database;
STATUS OPEN_MODE
-------------- --------------------
OPEN READ WRITE
You can see above that database is opened in READ WRITE Mode
, Database can also be opened in some special mode for specific purpose by DBA,
namely Restricted, readonly.
SQL> startup restrict
ORACLE instance started.
Total System Global
Area 732352512 bytes
Fixed Size 1339036 bytes
Variable Size 440402276 bytes
Database
Buffers 285212672 bytes
Redo Buffers 5398528 bytes
Database mounted.
Database opened.
SQL> alter
database open read only;
Database altered.
SQL> select
open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
Shutting down Oracle Database:
There are four methods available to shutdown the oracle
database:
- Normal Shutdown
- Shutdown transactional
- Shutdown Immediate
- Shutdown Abort
1. Normal Shutdown
During normal shutdown no new user connection will be
allowed, before the oracle database is shut down, oracle will wait for all
active users to finish its work, use this option to shutdown the database under
normal conditions. This mode of shutdown may take minutes or hours.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
2. Shutdown Transactional
During Shutdown Transactional no new user connections are
permitted and existing sessions that are not involved in active transactions will
be terminated. However sessions currently involved in a transaction are allowed
to complete the transaction and will then be terminated. Once all sessions are
terminated, the database will shutdown.
3. Shutdown Immediate
During immediate shutdown, before the oracle database is
shut down, oracle will rollback active transaction and disconnect all active
users. Use this option when there is a problem with your database and you don’t
have enough time to request users to log-off.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
4. Shutdown Abort
shutdown abort is simply oracle instance crash, before the
oracle database is shutdown, all user sessions will be terminated immediately.
Uncomitted transactions will not be rolled back. Use this option only during
emergency situations when the “shutdown” and “shutdown immediate” doesn’t work.
After a shutdown abort, the SMON process will have to
perform an instance recovery at next startup.
$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jan 18 11:11:33
2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> shutdown abort
ORACLE instance shut down.
No comments:
Post a Comment