It’s already quite a while that I posted my last article on the Oracle basics, so I decided to take some times in the train to continue this small serie. This time, I’m going to speak about one of the root of Oracle administration, starting and shutting down a database server.

Startup and shutdown processes within oracle database servers are split in different steps (for startup) and modes (for shutdown) and even if these principles are quite trivial, some confusions between these steps and/or modes can easily be done. So I’m going to give a picture of both of these processes and their variations.

Before shutting any database server down, it needs first to be started, so let’s begin with the startup process…

Note that to avoid confusion, I use the term database server to designate the entity composed from both the Database and the Instance.

Starting a database server: a 3 steps process

Before understanding the process itself, the first question is “How to start a database server?” The answer is that you need to connect locally to it and to be a SYSDBA!
So then the next question is “How to connect to something which is NOT running yet?” To do so you need 3 environment variables:

ORACLE_HOME –> Gives the Oracle Binaries Path
ORACLE_SID  –> Gives the Instance SID to connect to
PATH –> Must contain $ORACLE_HOME/bin or %ORACLE_HOME%bin (Windows)

oracle@vmoratest1:/home/oracle/ [TYRION] echo $ORACLE_HOME
/u00/app/oracle/product/11.2.0/db_1
oracle@vmoratest1:/home/oracle/ [TYRION] echo $ORACLE_SID
TYRION

Then you have to be on the server and be part of the DBA Operating System group. In UNIX world this corresponds to the group “dba”, while in Windows it is “oradba”.

Unix:

oracle@vmoratest1:/home/oracle/ [TYRION] id
uid=500(oracle) gid=501(dba) groups=501(dba),502(oinstall)

 
Windows:

In Windows environment, you need also that the Instance Service is started.

Finally, start SQLPLUS and simply connect as internal: sqlplus / as sysdba    where the / means to connect using OS authentication (DBA group) on the Instance defined by the variable ORACLE_SID.

oracle@vmoratest1:/home/oracle/ [TYRION] sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed May 18 09:02:07 2011
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.

Once connected, simply run the command: startup

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.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE

This is it! you database server is started 🙄
So now let’s have a looking what is going done backstage.

Basically starting an Oracle database is divided in 3 steps:

  • NOMOUNT
  • MOUNT
  • OPEN

For each of them there is a corresponding startup command:

  • NOMOUNT => startup nomount
  • MOUNT   => startup mount
  • OPEN    => startup

As you can see it the startup command automatically takes the database in Open mode. However some operations, like restores and recovers require to decompose the startup process. In this case swithing up from one mode to the next one is done using an “alter database” 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
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE

Note that you can NOT switch a database one mode/step down. This requires a shutdown!

Here we need to open a small bracket for the databases running on Windows servers.
As said above, on Windows the service OracleService must be running to connect to the database. However starting this service will fire up a full startup of the database, which means that a healthy database will be fully open once the service is started. On databases requiring restore/recover operations, you will basically also need to start the service and let him failing to open the database before you can go ahead.

In order to really understand the logic behind the startup process, you need to keep in mind the difference between the Oracle Instance and the Oracle Database. You can have a look to my first article on Oracle Basis to get a refresh on it.

Step 1 – NOMOUNT

In the NOMOUNT level, only the Oracle INSTANCE is started. To do so Oracle looks for the Instance spfile or pfile and starts all Processes and Memory Structures.

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

Let’s have a look on the running processes:

oracle@vmoratest1:/home/oracle/ [TYRION] ps -ef |grep TYRION
oracle    3999     1  0 09:12 ?        00:00:00 ora_pmon_TYRION
oracle    4001     1  0 09:12 ?        00:00:00 ora_vktm_TYRION
oracle    4005     1  0 09:12 ?        00:00:00 ora_gen0_TYRION
oracle    4007     1  0 09:12 ?        00:00:00 ora_diag_TYRION
oracle    4009     1  0 09:12 ?        00:00:00 ora_dbrm_TYRION
oracle    4011     1  0 09:12 ?        00:00:00 ora_psp0_TYRION
oracle    4013     1  0 09:12 ?        00:00:00 ora_dia0_TYRION
oracle    4015     1  1 09:12 ?        00:00:00 ora_mman_TYRION
oracle    4017     1  0 09:12 ?        00:00:00 ora_dbw0_TYRION
oracle    4019     1  0 09:12 ?        00:00:00 ora_lgwr_TYRION
oracle    4021     1  0 09:12 ?        00:00:00 ora_ckpt_TYRION
oracle    4023     1  0 09:12 ?        00:00:00 ora_smon_TYRION
oracle    4025     1  0 09:12 ?        00:00:00 ora_reco_TYRION
oracle    4027     1  0 09:12 ?        00:00:00 ora_mmon_TYRION
oracle    4029     1  0 09:12 ?        00:00:00 ora_mmnl_TYRION
oracle    4031     1  0 09:12 ?        00:00:00 ora_d000_TYRION
oracle    4033     1  0 09:12 ?        00:00:00 ora_s000_TYRION
oracle    4106  3356  0 09:12 pts/1    00:00:00 grep TYRION

Here we can recognize processes like SMON, LGWR, DBW or PMON

The question now is, how Oracle find the spfile?
In fact Oracle goes automatically in the dbs (UNIX) or database (Windows) folder in ORACLE_HOME and look for a spfile spfile.ora. If there is no spfile Oracle will the search for a pfile, init.ora, and finally take a default spfile if none are present. A good practice, OFA (Oracle Flexible Architecture), is to store the spfile in the admin directory of the database and to use a pfile to point to it.

Here is a small example:

oracle@vmoratest1:/home/oracle/ [TYRION] cd $ORACLE_HOME/dbs
oracle@vmoratest1:/u00/app/oracle/product/11.2.0/db_1/dbs/ [TYRION] ls
hc_DBUA0.dat  hc_TYRION.dat  init.ora  initTYRION.ora  lkULTHAN_SITE1  orapwTYRION  peshm_DBUA0_0  peshm_ULTHAN_SITE1_0
oracle@vmoratest1:/u00/app/oracle/product/11.2.0/db_1/dbs/ [TYRION] cat initTYRION.ora 
SPFILE='/u00/app/oracle/admin/TYRION/pfile/spfileTYRION.ora'
oracle@vmoratest1:/u00/app/oracle/product/11.2.0/db_1/dbs/ [TYRION] cd /u00/app/oracle/admin/TYRION/pfile/
oracle@vmoratest1:/u00/app/oracle/admin/TYRION/pfile/ [TYRION] ls
spfileTYRION.ora

On a 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     TYRION
lock_name_space                  string
log_file_name_convert            string
service_names                    string     TYRION.it.dhu-domain.com
SQL> show parameter spfile
NAME                       TYPE       VALUE
------------------------- ----------- ------------------------------
spfile                     string     /u00/app/oracle/admin/TYRION/pfile/spfileTYRION.ora

and to access some instance views

SQL> select INSTANCE_NAME,HOST_NAME,STATUS from v$instance;
INSTANCE_NAME      HOST_NAME                         STATUS
----------------- --------------------------------- ------------
TYRION             vmoratest1.it.dhu-domain.com      STARTED

The startup_mode column permits to identify the current mode of the database server. Here STARTED means in NOMOUNT.

Step 2 – MOUNT

Once taking the database server from NOMOUNT to MOUNT, Oracle access the control files, as declared in the spfile, and mount the database.

SQL> show parameter control_files
NAME                TYPE          VALUE
------------------- ------------- ----------------------------------------------------
control_files       string        /u01/oradata/TYRION/control01TYRION.dbf, /u02/oradata
                                  /TYRION, /control02TYRION.dbf,/u03/oradata/TYRION    
                                  /control03TYRION.dbf
SQL> alter database mount;
Database altered.

Check the new status

SQL> select INSTANCE_NAME,HOST_NAME,STATUS from v$instance;

INSTANCE_NAME      HOST_NAME                         STATUS
----------------- --------------------------------- ------------
TYRION             vmoratest1.it.dhu-domain.com      MOUNTED

Let’s check the control files usage:

[root@vmoratest1 TYRION]# fuser -a ./*
./control01TYRION.dbf:  4017  4019  4021

At this point Oracle knows information like the files composing the databases and their location, the backup history, the SCN status aso… SYSDBA users are still the own one, who can access the database, but now they have access to several information stored in v$views (thanks to the control file).

SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/TYRION/system01TYRION.dbf
/u01/oradata/TYRION/sysaux01TYRION.dbf
/u01/oradata/TYRION/undotbs01TYRION.dbf
/u01/oradata/TYRION/users01TYRION.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/oradata/TYRION/redog1m1TYRION.dbf
/u02/oradata/TYRION/redog1m2TYRION.dbf
/u01/oradata/TYRION/redog2m1TYRION.dbf
/u02/oradata/TYRION/redog2m2TYRION.dbf
/u01/oradata/TYRION/redog3m1TYRION.dbf
/u02/oradata/TYRION/redog3m2TYRION.dbf
6 rows selected.

Step 3 – OPEN

The last step is to open the database, which means accessing the data files and redo logs. Once the database is OPEN, all users can log on again and go ahead with their normal activities.

SQL> alter database open;
Database altered.
SQL> select status,open_mode from v$instance,v$database;
STATUS         OPEN_MODE
-------------- --------------------
OPEN           READ WRITE

Some variations

For some maintenance or administration activities, it may be necessary to open the database but to avoid any user to access it. The best solution is then to open the database in RESTRICT mode. This means that only SYSDBA users will be allowed to log in.

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.

A other possibility is to open the database in READ ONLY instead of READ WRITE.

SQL> alter database open read only;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY

 

Startup process summary

This drawing summarize the Oracle database startup process.

Shutting down a database

Sometimes it is necessary to shutdown a database server, like for example activating the archivelog or flashback mode, changing a static parameter or simply patching it. In this case, the DBA has 4 different solutions:

  1. shutdown normal (default mode with shutdown command)   => shutdown
  2. shutdown transactional    => shutdown transactional
  3. shutdown immediate    => shutdown immediate
  4. shutdown abort   => shutdown abort

These modes are going from the safer to the most risky one, but also from the slower to the faster one.

Shutdown Normal

This is the default. No new user connections will be allowed, but all current connections continue normaly. Once all users have logged off, the database will finally be allowed to shutdown.

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.

Shutdown Immediate

No new sessions are permitted, all currently connected sessions are
terminated an any active transactions are rolled back. Then the database will go down.

Shutdown Abort

As far as Oracle is concerned, this is the equivalent of a power failure. The instance terminates immediately (instance “crash”). Nothing is written to disk, no file handles are closed and there is no transactions are terminated, even not in a orderly way. A shutdown abort will not damage the database, but some operations like backups are not advisable after an abort.

Shutdown process description

Basically in a “clean” shutdown process (Normal, Transactionnal or Immediate), the process will be the reverse of startup. During an orderly shutdown, the database is first be closed, then dismounted, and finally the instance is stopped.

During the close phase:

  • all sessions are terminated
  • PMON roll back any incomplete transactions.
  • A checkpoint is issued, which forces the DBWn process to write all updated data from the db buffer cache to the datafiles
  • LGWR flushes any change vectors still in memory to the logfiles
  • The file headers are updated, and the file handles closed.

At this point the database is in a consistent state: all datafiles and logfiles are synchronized.
During the dismount phase the control files are closed
Then the instance is stopped by deallocating the SGA memory and terminating the background processes.
In case of the Abort mode, it leaves the database in an inconsistent state:

  • Committed transactions have been lost, because they were only in memory and DBWn had not yet written them to the datafiles
  • Uncommitted transactions in the datafiles may not yet have been rolled back.

After a shutdown abort, the SMON process will have to perform an instance recovery at next startup.
To finish, I will open a last bracket about Windows environment 😉

I said that in Windows there is a service OracleService to start/stop the database. Basically you can stop your database just by stopping the corresponding service. In this case Oracle runs a SHUTDOWN IMMEDIATE by default. The type of shutdown the service runs can be customized, since Oracle 11g, using the parameter -SHUTMODE normal|immediate|abort of oradim.

However there is a small issue with this principle 😕
Unfortunately in case of a server shutdown/reboot, the service will be stopped before the database shutdown is  performed!

Alert.log output:

***********************************************************************
Fatal NI connect error 12638, connecting to:
 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
VERSION INFORMATION:
 TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
 Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
 Time: 25-MAY-2011 17:40:47
 Tracing not turned on.
 Tns error struct:
 ns main err code: 12638
TNS-12638: Credential retrieval failed
 ns secondary err code: 0
 nt main err code: 0
 nt secondary err code: 0
 nt OS err code: 0
This means that the database is not shutdown but crashed! Therefore a instance recovery will be performed at next startup:

Completed: alter database mount exclusive
alter database open
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
...
...

A solution is to use the registry entry ORA_SID_SHUTDOWN_TIMEOUT (HKEY_LOCAL_MACHINE –> Software –> Oracle –> Key_) to theoricaly define a timeout while the service waits for the database shutdown confirmation before stopping. If the timeout is reached then it performs a shutdown abort.

And here comes now the Bug 1638610! 😮

The parameter ORA_SID_SHUTDOWN_TIMEOUT isn’t taken in account if the setting SQLNET.AUTHENTICATION_SERVICES is set in the sqlnet.ora. This bug applies from Oracle 8i to 10g and is still true in Oracle 11gR2. The bug state is set by Oracle as “not feasible to fix”…

I hope that this small article, gave you a good understanding about Oracle startup and shutdown processes 🙂

Have fun 😀

Post Views: 2,484
Thumbnail [60x60]
by
David Hueber