Master Note: Overview of Database Startup and Shutdown (Doc ID 1505155.1)

In this Document

 Purpose Details Oracle Database and Instance Database Startup Nomount Stage Mount Stage  Open Stage Instance recovery: Database Shutdown Different Modes in Database Shutdown Shutdown Immediate Shutdown Transactional Shutdown Normal Shutdown Abort Different Phases in Database Shutdown Close the Database Dismount the Database Shutdown the Instance Steps for a Clean Startup after an Instance Crash Issues Encountered During Startup and Shutdown References

---

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.

PURPOSE

This document provides an overview of the database startup and shutdown, including the various phases and steps. This is not intended to be a troubleshooting document. But provides a clear understanding on how the the single instance database startup and shutdown works.

DETAILS

Oracle Database and Instance

The Database is a set of physical operating system files. These files actually holds the user data and the metadata (or the data dictionary). Every running Oracle database is associated with (atleast) an Oracle instance.

The Instance refers to the set of Oracle background processes or threads and a shared memory area (SGA). An instance can mount and open at most one database in its life. 

A database may be mounted and opened by one or more instances (using RAC) and the number of instances mounting a single database can fluctuate over time.

Database Startup

For any normal user to access the database, the instance has to be started up and it should mount and open the corresponding database. We term the entire steps as database startup. In short, database startup includes the following steps:

1. Start an instance. 
2. Mount the database. 
3. Open the database.

We will see each of these in details.

Database startup requires administrative (SYSDBA) privilege. 

Database can be started using the steps:

$ export ORACLE_SID=<SID of the instance>

$ export ORACLE_HOME= <location of ORACLE_HOME>

$ sqlplus / as sysdba

SQL> startup

The above syntax assumes you have a pfile or spfile in the default location. If you are using a non default parameter file, the startup command is:

$ sqlplus / as sysdba

SQL> startup pfile=<file name and location>

Nomount Stage

When you issue a 'Startup' command, this is the first stage. ie, starting up the instance. Here, Oracle will read the parameter file (spfile or pfile) in the default location or in the location specified in the startup command. It will then allocate the memory area (SGA) and starts the background processes. Together we call it as the instance. Please note that no database is associated with the instance at this point.

We can start the instance alone using the command:

$ sqlplus / as sysdba

SQL> startup nomount

Starting the instance in nomount stage is usually required for database creation or for creating or recovering the controlfiles.

In the following example I am starting a 11.2.0.3 instance with SID sb23Sa on Linux platform.

Before starting up the database:

[celcaix3]/grdbms/home> ps -ef|grep SMONgrdbms 30015760 22134982 0 08:12:49 pts/12 0:00 grep SMON[celcaix3]/grdbms/home> env|grep ORACLE_SIDORACLE_SID=NOT_SET

Now, if you try starting up the database:

[celcaix3]/grdbms/home> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 12 08:24:32 2012

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

ERROR: ORA-12162: TNS:net service name is incorrectly specified

When you attempt a connection, Oracle will look for a connect string or an environment variable ORACLE_SID. Both these are not set here and hence the ORA-12162 is reported here.  We will now set the required ORACLE_SID which acts as a key to gain access to the instance.

[celcaix3]/grdbms/home> export ORACLE_SID=sb23Sa[celcaix3]/grdbms/home> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 12 08:27:44 2012

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

Connected to an idle instance.

No shared memory created as of now:

[celcaix3]/grdbms/home> ipcs -a[celcaix3]/grdbms/home>

We will check for the processes associated with sb23Sa instance:

[celcaix3]/grdbms/home> ps -ef|grep sb23Sagrdbms 1556696 8880272 0 08:29:20 - 0:00 oraclesb23Sa (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))grdbms 856460 22134982 0 08:29:54 pts/12 0:00 grep sb23Sa

This is the shadow server process used to initiate the instance creation.

SQL> startup nomountORACLE instance started.

Total System Global Area 304807936 bytesFixed Size 2221000 bytesVariable Size 113249336 bytesDatabase Buffers 184549376 bytesRedo Buffers 4788224 bytes

Checking the processes and the memory segment shows:

[celcaix3]/grdbms/home> ipcs -a |grep grdbmsm 283124525 0xea950904 --rw-r----- grdbms grdbms grdbms grdbms 18 8192 1556696 23953626 8:40:20 8:40:20 8:40:15m 44056335 00000000 --rw-r----- grdbms grdbms grdbms grdbms 18 8388608 1556696 23953626 8:40:20 8:40:20 8:40:15m 73422245 00000000 --rw-r----- grdbms grdbms grdbms grdbms 18 297795584 1556696 23953626 8:40:20 8:40:20 8:40:15[celcaix3]/grdbms/home>

[celcaix3]/grdbms/home> ps -ef|grep sb23Sagrdbms 950420 1 0 08:40:18 - 0:00 ora_smon_sb23Sagrdbms 1060994 1 0 08:40:18 - 0:00 ora_ckpt_sb23Sagrdbms 1171494 1 0 08:40:18 - 0:00 ora_dbw0_sb23Sagrdbms 1302528 1 0 08:40:17 - 0:00 ora_diag_sb23Sagrdbms 1855508 1 0 08:40:19 - 0:00 ora_d000_sb23Sagrdbms 6815820 1 0 08:40:18 - 0:00 ora_reco_sb23Sagrdbms 11944014 1 0 08:40:18 - 0:00 ora_mmon_sb23Sagrdbms 22179998 1 0 08:40:17 - 0:00 ora_dbrm_sb23Sagrdbms 23245036 1 0 08:40:17 - 0:00 ora_vktm_sb23Sagrdbms 23724154 1 0 08:40:19 - 0:00 ora_s000_sb23Sagrdbms 26566668 22134982 0 08:40:57 pts/12 0:00 grep sb23Sagrdbms 30306348 1 0 08:40:16 - 0:00 ora_pmon_sb23Sagrdbms 30400650 1 0 08:40:18 - 0:00 ora_mman_sb23Sagrdbms 295222 8880272 0 08:40:19 - 0:00 oraclesb23Sa (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))grdbms 1945870 1 0 08:40:19 - 0:00 ora_mmnl_sb23Sagrdbms 7033176 1 0 08:40:18 - 0:00 ora_lgwr_sb23Sagrdbms 13021688 1 0 08:40:16 - 0:00 ora_psp0_sb23Sagrdbms 23032234 1 0 08:40:18 - 0:00 ora_dia0_sb23Sagrdbms 29548960 1 0 08:40:17 - 0:00 ora_gen0_sb23Sa

The instance is now started and the database is in nomount stage.

Mount Stage 

In this phase the instance will mount the database. Mounting the instance means associating the started instance with a specified database. For this, the instance checks the controlfiles specified under CONTROL_FILES parameter and opens it. It then reads the control files to find the names of the data files and the online redo log files that it will attempt to access when opening the database.

The database is still closed and only the DBA can access it.

This stage is normally used for maintenance operations like  renaming datafiles, enabling and disabling archiving options. Adding, dropping, or renaming redo log files is also done in mount stage. For performing full database recovery database is opened in mount stage.

To mount a database:

$ sqlplus / as sysdba

SQL> startup mount

Open Stage

This is the final stage and here Oracle opens the online datafiles and redolog files. If any of the required files are not present, media recovery is required . It also ensures the datafiles are consistent. Incase of a normal shutdown, the in-memory changes will be written to disk as part of the shutdown checkpoint. But if the instance crashed (or shutdown abort), then Oracle Database performs the instance recovery in the next startup.

Instance recovery:

Whether instance recovery is required depends on the state of the redo threads. A redo thread is marked open in the control file when a database instance opens in read/write mode, and is marked closed when the instance is shut down consistently. If redo threads are marked open in the control file, but no live instances hold the thread enqueues corresponding to these threads, then the database requires instance recovery.

Instance recovery is performed in two steps. ie, rollforward and rollback.

Cache Recovery or Rollforward:

Here, the changes recorded in the redolog files are applied to the affected blocks. This includes both committed and uncommited data. Since Undo data is protected by redo, rollforward generated the undo images also. The time required for this will be proportional to the changes made in the database after the last successful checkpoint. After cache recovery, the database will be 'consistent' to the point when the crash occurred. Now the database will be open and users can start connecting to it. The parameter RECOVERY_PARALLELISM specifies the number of processes to participate in instance or crash recovery and we can thus speed up rollforward.

Transaction Recovery or Rollback

The uncommitted data in the database will now be rolled back. This is coordinated by SMON and rolls back set of transactions parallely (by default) using multiple server processes. SMON automatically decides when to begin parallel rollback and disperses the work among several parallel processes: process one rolls back one transaction, process two rolls back a second transaction, and so on. And if a transaction is huge Oracle  begins intra-transaction recovery by dispersing the huge transaction among the slave processes: process one takes one part, process two takes another part, and so on. Parallel mode is the default one and is decided by the parameter FAST_START_PARALLEL_ROLLBACK . We can either turn it off (for serial recovery) or increase the degree of parallelism. If you change the value of the parameter FAST_START_PARALLEL_ROLLBACK, then transaction recovery will be stopped and restarted with the new implied degree of parallelism.  

As mentioned earlier, user sessions are allowed to connect even before the transaction recovery is completed. If a user attempts to access a row that is locked by a terminated transaction, Oracle rolls back only those changes necessary to complete the transaction; in other words, it rolls them back on demand. Consequently, new transactions do not have to wait until all parts of a long transaction are rolled back.

This transaction recovery is required and has to be completed. We can disable transaction recovery temporarily but at some point this has to be completed. We can monitor the progress of fast-start parallel rollback by examining the V$FAST_START_SERVERS and V$FAST_START_TRANSACTIONS views.

The Fast-Start Fault Recovery feature reduces the time required for cache recovery, and makes the recovery bounded and predictable by limiting the number of dirty buffers and the number of redo records generated between the most recent redo record and the last checkpoint. With the Fast-Start Fault Recovery feature, the FAST_START_MTTR_TARGET initialization parameter simplifies the configuration of recovery time from instance or system failure. FAST_START_MTTR_TARGET specifies a target for the expected mean time to recover (MTTR), that is, the time (in seconds) that it should take to start up the instance and perform cache recovery. After FAST_START_MTTR_TARGET is set, the database manages incremental checkpoint writes in an attempt to meet that target.

If the SMON is busy doing the transaction recovery you should never attempt a shutdown abort and restarting the database. The entire work done till that point needs to be done again.

There are different modes in which you can open the database eg: migrate, read only, restricted modes.

Database Shutdown

During a database shutdown we close the database and terminates the instance.

Different Modes in Database Shutdown

There are different modes to bring down the database:

1. Shutdown immediate
2. Shutdown transactional
3. Shutdown normal
4. Shutdown abort

No user session will be permitted once you issue any of these Shutdown commands.

Shutdown Immediate

Oracle Database terminates any executing SQL statements and disconnects users. Active transactions are terminated and uncommitted changes are rolled back. Oracle then performs a checkpoint and then close the online datafiles.

Shutdown Transactional

This mode prevents users from starting new transactions, but waits for all current transactions to complete before shutting down. Oracle then performs a checkpoint and then close the online datafiles.

Shutdown Normal

The database waits for all connected users to disconnect before shutting down. It waits till all the current transactions end. Oracle then performs a checkpoint and then close the online datafiles.

Shutdown Abort

Oracle Closes the datafiles without any checkpoint. This is the fastest shutdown mode. Instance recovery is required in the next startup and hence it will take time.
The syntax to shutdown is:

$ sqlplus / as sysdba

SQL> shutdown immediate

-- OR --

SQL> shutdown abort

-- OR --

SQL> shutdown transactional

 

Different Phases in Database Shutdown

Close the Database

Oracle writes the data in the SGA to the disk, updates the file headers and closes the online datafiles and the redo log files. But the database will still be mounted.

Dismount the Database

After the database is closed, Oracle Database unmounts the database to disassociate it from the instance. After a database is unmounted, Oracle Database closes the control files of the database. At this point, the instance remains in memory.

Shutdown the Instance

The last step is to remove the shared memory (SGA) and terminate the background processes.
Sometimes shutdown does not cleanup the SGA or background process completely. This can cause error during the next startup. In such situation we can force a instance startup

$ sqlplus / as sysdba

SQL> startup force

Best practice to bring down the database during a planned outage is (a) Shutdown the application (b) Stop DBConsole, if any (c) Shutdown the database. You can check for any long running queries before shutting down the database.
Now, if you close the database explicitly, you cannot open the database again using the same instance.

SQL> select open_mode from v$database;OPEN_MODE--------------------READ WRITESQL> alter database close;Database altered.SQL> select open_mode from v$database;OPEN_MODE--------------------MOUNTEDSQL> alter database open;alter database open*ERROR at line 1:ORA-16196: database has been previously opened and closedSQL> 

Now the only option will be to terminate the instance and startup the database again You can open only one database and only once using an instance. 

Steps for a Clean Startup after an Instance Crash

Sometimes the instance will not start if the previous shutdown was not complete (leaving some shared memory segments or processes). In such cases you can retry a fresh startup after cleaning up the old shared memory segments / semaphores: 

a. check if the background processes are running
    $ ps -ef | grep $ORACLE_SID 

b. If background processes are running remove the background processes:
    $ kill -9  #### 
    
c. Check if the shared memory and semaphore are running for your SID:
    $ ipcs –a    
    For more information, please see 
    Note 68281.1 - DETERMINING WHICH INSTANCE OWNS WHICH SHARED MEMORY & SEMAPHORE SEGMENTS 
    
d. If shared memory and semaphore are in use then remove the shared memory and semaphore:
    $ ipcrm -m  #### -- for shared memory    
    $ ipcrm -s  #### -- for semaphore 
    
e. Change directory to the "oracle_home/dbs":
    $ cd $ORACLE_HOME/dbs 
    
f. Check if the file "lk" and "sgadef.dbf". 

g. If files are there, then remove the "lk<SID>" and "sgadef<sid>.dbf" files:
    $ rm lk<SID> sgadef<sid>.dbf 
    A server reboot will do this cleanup except for the lk<SID> files.

Issues Encountered During Startup and Shutdown

In case of any issues during startup and shutdown, you can check the known issues documented in
Note 1270450.1 - Master Note for Database Startup/Shutdown

If this is not helping or if you need further assistance in troubleshooting please refer 
Note 851057.1 - Troubleshooting Database Startup/Shutdown Problems

If required, you can raise a Service Request with Oracle Support after collecting the diagnostic informations mentioned in Note 851057.1.