Oracle Database Startup and Shutdown

Database Startup:

Oracle database:

It is a set of physical system files. These files actually hold the user data & the meta data. Every running oracle database is associate with at least one oracle instance.

Oracle Instance:

The instance refers to the set of oracle background process and an SGA.

 

• Start the instance
• Mount the database
• Open the database

NOMOUNT Stage:

• Oracle will read the parameter file from the default location or specified in the startup command.
• It will then allocate the memory area (SGA) and start the background process. Together we call is as the instance.
• No database is associate with the instance at this point. Only the instance is started.

Why nomount stage need:

The instance is now started and database is in nomount stage. Starting the instance in nomount stage is usually required db creation, creating or recovering the control file.

startup nomount

startup pfile='<pfile location>' nomount

start spfile='<spfile location>' nomount

alter database nomount;

MOUNT Stage:

• Mounting the instance means associating. The started instance with specified database.
• For this the instance checks the control file specified under control file parameters and opens it.
• It then reads the control file to find the names of the data-files and online redo log files that will attempt to access the opening the database.
• The database is still closed and only the dba can access it.

 Why Mount stage need:

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

startup mount

startup pfile='<pfile location>' mount

start spfile='<spfile location>' mount

alter database mount;

OPEN Stage:

• This is final stage and oracle opens the data files and redo log files.
• If any of the required files are not present media recovery is required.
• It also ensure the data files 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 then oracle database recovery in the next startup.

alter database open;

Database Shutdown:

When we shutdown the database, it closes the database and terminates the instance.

The three different phases of database shutdown:

1.Close the database:

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

2.Dismount the database:

• After the database is closed, oracle database unmount the database to disassociate it from the instance.
• After the database is unmounted, oracle database closes the control file.
• At this point, the instance remains in memory.

3.Shutdown the instance:

• The last step is to remove the shared memory (SGA) and terminate the background process.
• Some time 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.

Shutdown Modes:

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

Shutdown immediate:

• Oracle database terminates any executing SQL statement and disconnects users.
• Active transaction is terminated and uncommitted changes are rolled back.
• Oracle the performs a checkpoint and closes 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 the 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 data files.

Shutdown Abort:

• Oracle closes the datafiles without any checkpoint.
• This is the faster shutdown mode.
• Instance recovery is required in the next startup and hence it will take time.

	Abort	Immediate	Transactional	normal
Allows new connection	false	false	false	false
Waits until current session end	false	false	false	true
Waits until current transactions end	false	false	true	true
Forces a checkpoint and close the data files	false	true	true	true