ORACLE: Shutting Down Your Database

Databases needs to be shut down for several reasons. There are three methods to shutdown your Oracle database. These are as follows:

• Shutdown (Normal)
• shutdown Immediate
• Shutdown Abort

As to which will be used among these three will depend upon your situation.

1. Shutdown (Normal)

This is used when you shutdown your database on normal condition. This might take long as this shutdown method will wait for all active users to disconnect their sessions before the oracle instance is shut down.

SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>


2. Shutdown Immediate

With this shutdown method, all active users will be disconnected and oracle will rollback all active transaction. This method is used when database is already needed to be shut down immediately and cannot wait for active users to disconnect their session.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>


3. Shutdown Abort

This shutdown method should only be used on emergency cases. This method will immediately disconnect users and end all transactions without rollback. Shutdown abort will then shut down Oracle instance. You may use this option when normal shutdown is not an option and shutdown immediate is not working.

SQL> shutdown abort
ORACLE instance shut down.
SQL>

To summarize, you may use these three methods on the following situations:

Shutdown (normal) - when you have ample time to wait for all the users to disconnect their sessions and for all transactions to be committed.

Shutdown Immediate - when you need to have the instance shut down but cannot wait for all the users to disconnect.

Shutdown Abort - used for emergency cases when the other two options are not working.

ORACLE: Starting up your database

In starting up your database, there are three thing that you would actually want to do to have your database usable.
First is to start your instance. Second is to mount your database. Third is to open your database.

This can all be done using the command STARTUP.


SQL> startup;
ORACLE instance started.

Total System Global Area  812529152 bytes
Fixed Size                  2264280 bytes
Variable Size             960781800 bytes
Database Buffers           54654432 bytes
Redo Buffers                3498640 bytes
Database mounted.
Database opened.
SQL>


With the startup command, the database is now open and can be accessible to your users. There are different stages in starting up your database. Below are some start up methods and their purpose.

1. Startup Nomount
When startup nomount is used, only your Oracle instance will be started. Oracle will read the initialization parameter file to start the instance according to how it is configured. It will then allocate memory for the database instance. Also, since the instance is already started in this phase, Oracle background processes will also be started.

SQL>startup nomount;

Startup Nomount is used when creating database for first time. It is also used for some recovery procedures or when recreating your control files.


2. Startup Mount
When startup mount is used, it will now read the control files. The control file contains entries that specifies the physical structure of the database. This will include the Database name and the locations of the datafiles and redo log files.

SQL>startup mount;

Database should be in mount state when doing some administrative tasks on your database. Some of these are;

• renaming/moving datafile
• changing archive log mode
• database full recovery

When database was already started nomount, you can change to mount state using below command.

SQL>alter database mount;

Note: Databases that are used as standby are also in mount state.

3. Startup (OPEN)
When you did not specify whether the startup type and execute only STARTUP, this will then start the database in open mode, which is the 3rd stage after mounting the database. Only in this phase can we consider that the database is finally operational. Applications and users can now start reading and writing data on the database. During this stage, database and redo log files will be open. If there are corrupted or missing in any of the required files, the startup command will return an error.

SQL> startup;

When the database is already mounted, you can then open the database with below command.

SQL> alter database open;


To summarize the stages of starting up your database:

1. no mount - instance started, reads your parameter file
2. mount - control files are read
3. open - database file and redo log files are open


Feel free to comment below for anything that can make this post a better one.

Sharing simple DB tutorials for newbies like me.

Hello! Since I have already decided to follow the path of being a DBA, I have decided to make a blog of random DB-related things that I will learn along the way.

Hope my blogs will be useful to you :)