When startup command is issued, Oracle
looks for the parameter file in following order.
1. Spfile<SID>.ora
2. init<SID>.ora
Oracle look for the parameter file in
$ORACLE_HOME/dbs
We can startup the database instance with
following options.
1. startup
2. startup nomount
3. Startup mount
startup pfile=’/location/filename’
4. startup upgrade
5. startup mount restrict
1. startup:-
1. It will read the parameter file.
2. It will start the background processes
and allocate memory (SGA). – Instance started.
3. It reads the controlfile. –Instance
mounted.
4. Physically checks the datafiles and
redolog files.
5. Checks the SCN number of the datafiles
and controlfiles.
It is used in normal situations.
If the SCNs in
the control files don’t match some of the SCNs in the data files headers—the
background process (smon) will automatically perform an instance recovery
before opening the database.
2. startup mount:--
It will do 1,2 and 3.
It will not do 4 and 5.
Can be used when:-- Recovery is needed for
the entire database, Flashback is to be performed, any feature like archive log
is to be enabled or disabled.
3. startup nomount:--
It will do 1 and 2.
It will not do 3,4 and 5.
Can be used when:-- Creating database and
creating controlfile.
4. startup upgrade:--
It will do 1 to 5 and
6. Give the option of upgrading database.
7. Users cannot connect to the database.
Can be used to upgrade the database
dictionary
Database must be shutdown and restarted for
users to connect.
5. startup mount restrict:--
It will read 1, 2 and 3.
It will not read 4 and 5.
Used when the database is dropped.
Changing the status:--
Alter database mount;
This command can be used when database is
in nomount state, this will read the controlfiles and mount the database.
Alter database open;
This command can be used when database is
mounted.
This will read the datafiles and redolog
file and will check if the datafiles are consistent. Allows the users to
connect to the database,
Alter database close;
This command can be used when database
open. This will release the datafiles and redolog files.
User connections will not be allowed.
Status will change to mount.
Alter database dismount;
This command can be used when database is at
mount state. This will release the control file and change the status to
nomount.
The
database can be shutdown with the following options:--
Shutdown:--
No new user
connections can be made to the database once the command is issued. Oracle
waits for all users to exit their sessions before shutting down the database.
No instance recovery is needed when you restart database, it will be consistent
when it’s shut down in this way. Oracle closes the data files and terminates
the background processes. Oracle’s SGA is deallocated.
Shutdown
transactional:--
No new user
connections are permitted once the command is issued. Existing users can’t
start a new transaction and will be disconnected. If a user has a transaction
in progress, oracle will wait until the transaction is completed before
disconnecting the user. After all existing transactions are completed. Oracle
shuts down the instance and deallocates memory. Oracle writes all redo log
buffers and data block buffers to disk. No instance recovery is needed because
the database is consistent.
Shutdown
immediate:--
No new user
connections are allowed once the command is issued. Oracle immediately
disconnects all users. Oracle terminates all currently executing transactions.
For all transactions terminated midway, oracle will perform a rollback so that
database ends up consistent. Sometimes oracle may be busy rolling back the
transactions it just terminated. Oracle terminates the background processes and
deallocates memory. No instance recovery is needed upon starting up the
database because it is consistent when shut down.
Shutdown
abort:--
No new
connections are permitted once the command is issued. Existing sessions are
terminated, regardless of whether they have an active transaction or not.
Oracle doesn’t roll back the terminated transactions. Oracle doesn’t write the
redo log buffers and data buffers to disk. Oracle terminates the background
processes, deallocates memory immediately, and shutdown. Upon restarting,
oracle will perform an automatic instance recovery, because the database isn’t
guaranteed to be consistent when shut down.