What are the various stages of database startup and shutdown

What are the various stages of database startup and shutdown? 

To start up or shut down an Oracle instance, you need to be connected to the database with the appropriate privileges. Two special connection account authorizations are available for startup and shutdown: SYSDBA and SYSOPER

Starting Up an Oracle Database

STARTUP NOMOUNT This starts the instance without mounting the database. When a database is started in this mode, the parameter file is read, and the background processes and memory structures are initiated, but they are not attached or communicating with the disk structures of the database. When the instance is in this state, the database is not available for use.

If a database is started in NOMOUNT mode, only the background processes and instance are started. The instance is not associated with any database. This state is used to create a database or to create a database control file.

At times, a database may not be able to go to the next mode (called MOUNT mode) and remains in NOMOUNT mode. For example, this can occur if Oracle has a problem accessing the control file structures, which contain important information to continue with the startup process. If these structures are damaged or not available, the database startup process cannot continue until the problem is resolved.

If STARTUP NOMOUNT fails, the most likely cause is that the parameter file cannot be read or is not in the default location. Other causes include OS resource limits that prevent memory or process allocation.

STARTUP MOUNT This performs all the work of the STARTUP NOMOUNT option but also attaches and interacts with the database structures. At this point, Oracle obtains information from the control files that it uses to locate and attach to the main database structures. The control file contains the name of the database, all the data file names, and the redo log files associated with the database.

Certain administrative tasks can be performed while the database is in this mode, including renaming data files, enabling or disabling archive logging, renaming and adding redo log files, and recovering the database.

STARTUP OPEN This is the default startup mode if no mode is specified on the STARTUP command line. STARTUP OPEN performs all the steps of the STARTUP NOMOUNT and STARTUP MOUNT options. This option makes the database available to all users.

When opening the database, you can use a couple of options. STARTUP OPEN READ ONLY opens the database in read-only mode. STARTUP OPEN RECOVER opens the database and performs a database recovery.

Although you typically use the STARTUP NOMOUNT, STARTUP MOUNT, and STARTUP OPEN options, a few other startup options are available that you can use in certain situations: STARTUP FORCE and STARTUP RESTRICT. These are discussed next.

STARTUP FORCE You can use the STARTUP FORCE startup option if you are experiencing difficulty starting the database in a normal fashion. For example, if a database server lost power and the database stopped abruptly, it can leave the database in a state in which a STARTUP FORCE startup is necessary. This type of startup should not normally be required but can be used if a normal startup does not work. What is also different about STARTUP FORCE is that it can be issued no matter what mode the database is in. STARTUP FORCE does a shutdown abort and then restarts the database.

STARTUP RESTRICT The STARTUP RESTRICT option starts up the database and places it in OPEN mode but gives access only to users who have the RESTRICTED SESSION privilege. You might want to open a database using the RESTRICTED option when you want to perform maintenance on the database while it is open but ensure that users cannot connect and perform work on the database. You might also want to open the database using the RESTRICTED option to perform database exports or imports and guarantee that no users are accessing the system during these activities. After you are done with your work, you can disable the restricted session, ALTER SYSTEM DISABLE RESTRICTED SESSION, so everyone can connect to the database.

General Syntax:

STARTUP [NOMOUNT|MOUNT|OPEN] [PFILE/SPFILE=] [RESTRICT]

Shutting Down an Oracle Database

SHUTDOWN NORMAL A normal shutdown is the default type of shutdown that Oracle performs if no shutdown options are provided. You need to be aware of the following when doing a normal shutdown:

1.  No new Oracle connections are allowed from the time the SHUTDOWN NORMAL command is issued.

2.  The database will wait until all users are disconnected to proceed with the shutdown process.

Because Oracle waits until all users are disconnected before shutting down, you can find yourself waiting indefinitely for a client who may be connected but is no longer doing any work or may have left for the day. This can require extra work, identifying which connections are still active and either notifying the users to disconnect or forcing the client disconnections by killing their session. This type of shutdown is also known as a clean shutdown because when you start Oracle again, no recovery is necessary.

SHUTDOWN TRANSACTIONAL A transactional shutdown of the database is a bit more aggressive than a normal shutdown. The characteristics of the transactional shutdown are as follows:

1.  No new Oracle connections a NN re allowed from the time the SHUTDOWN TRANSACTIONAL command is issued.

2.  No new transactions are allowed to start from the time the SHUTDOWN TRANSACTIONAL command is issued.

3.  Once all active transactions on the database have completed, all client connections are disconnected.

A transactional shutdown does allow client processes to complete prior to the disconnection. This can prevent a client from losing work and can be valuable especially if the database has long-running transactions that need to be completed prior to shutdown. This type of shutdown is also a clean shutdown and does not require any recovery on a subsequent startup.

SHUTDOWN IMMEDIATE The immediate shutdown method is the next most aggressive option. An immediate shutdown is characterized as follows:

1.  No new Oracle connections are allowed from the time the SHUTDOWN IMMEDIATE command is issued.

2.  Any uncommitted transactions are rolled back. Thus, a user in the middle of a transaction will lose all the uncommitted work.

3.  Oracle does not wait for clients to disconnect. Any unfinished transactions are rolled back, and their database connections are terminated.

This type of shutdown works well if you want to perform unattended or scripted shutdowns of the database and you need to ensure that the database will shut down without getting hung up during the process by clients who are connected. Even though Oracle is forcing transactions to roll back and disconnecting users, an immediate shutdown is still a clean shutdown. No recovery activity takes place when Oracle is subsequently restarted.

SHUTDOWN ABORT A shutdown abort is the most aggressive type of shutdown and has the following characteristics:

1.  No new Oracle connections are allowed from the time the SHUTDOWN ABORT command is issued.

2.  Any SQL statements currently in progress are terminated, regardless of their state.

3.  Uncommitted work is not rolled back.

4.  Oracle disconnects all client connections immediately upon the issuance of the SHUTDOWN ABORT command.

Do not use SHUTDOWN ABORT regularly. Use it only if the other options for database shutdown fail or if you are experiencing some type of database problem that is preventing Oracle from performing a clean shutdown. This type of shutdown is not a clean shutdown and requires instance recovery when the database is subsequently started. Instance recovery is performed automatically when you do the startup—no manual intervention required. During instance recovery the uncommitted changes are rolled back from the database, and committed changes are written to the data files. Oracle uses the redo log files and undo segments to construct the instance recovery information.

General Syntax:

SHUTDOWN [NORMAL|TRANSACTIONAL|IMMEDIATE|ABORT]