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:-
ü
It will read the parameter file.
ü
It will start the background processes and
allocate memory (SGA). – Instance started.
ü
It reads the controlfile. –Instance mounted.
ü
Physically checks the datafiles and redolog
files.
ü
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 read the parameter file.
ü
It will start the background processes and
allocate memory (SGA). – Instance started.
ü
It reads the controlfile. –Instance mounted.
ü
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 read the parameter file.
ü
It will start the background processes and
allocate memory (SGA). – Instance started
ü
Can be used when:-- Creating database and
creating controlfile.
4. startup upgrade:--
ü
It will do 1 to 5 and
ü
Give the
option of upgrading database.
ü
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.
BEGIN
DBMS_LOCK.sleep(seconds => 60.01);
END;
/
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, de allocates
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.
