An oracle database can be started in various modes. Each mode is used by the DBA’s to perform some specific operation in the database.
Nomount State:
We can bring database to nomount state from shutdown state. When we start an oracle database in nomount state then oracle will create the oracle instance. The instance is created by reading all the values from a file known as init.ora parameter file. Based on the values from this file oracle will allocate the sga in the RAM and start the background processes.
Command to start database in nomount state:
Sql>startup nomount
The dba uses the nomount state to
1) Create an oracle database
2) Re-create a controlfile for an existing database
Mount State:
From nomount the dba can take the database to mount state. In the mount state oracle will open the controlfile. After opening the control file oracle will read the path of the datafiles and log files from the control file.
To mount a database directly from shutdown state we issue
Sql> startup mount
To mount a database from a started state (nomount state)
Sql> alter database mount;
1) The mount state is used to recover a database that has crashed due to media failure.
2) The mount state is also used by the dba to enable archiver process.
3) it is also used by the dba to create a standby controlfile for configuring a standby database using dataguard.
Open state:
After mounting the database the dba can take the database to open state, when we open a database then a check will performed by oracle i.e, called sanity check. Sanity check is nothing but reading the path and filenames of the dbf’s and log’s from the controlfile and checking wether those files are physically available in the same locations at the o/s level. Incase any of the physical files is missing then sanity check will fail. If the sanity check is successful then oracle will begin to open each dbf one by one, when oracle opens the dbf it will check the scn (system change number) that is available in the dbf header and cross verify that scn with the scn in the controlfile.
IF the scn is the same then that file can be opened successfully, this way oracle will compare all the scns for all the files with the scn in the controlfile, only when all scns are matched with controlfile scn then only can the database be opened.
Incase the scns are not matched that means that the database is in an inconsistent state due to improper shutdown previously.
In this case oracle will try to recover the database with automatic crash recovery, if that also fails then oracle will ask the dba to manually recover the datbase using media recovery methods.
We can open an already mounted database by below command.
Sql>alter database open;
We can directly go from a shut database to an open database by typing below command.
Sql>startup;
When we open the database oracle will,
1) Open each datafile one at a time.
2) On opening a datafile oracle will check the scn in the file header with the scn in the control file.
If all the scn’s in the dbf’s are matched with the scn in the controlfile then the db can be opened for use.
But if any of the file scn is not matching with control file scn then oracle will ask to recover that file and database cannot be opened.
Startup Restrict:
If we start an oracle database in restricted mode then only those users who have restricted session privilege will be able to connect to the database.
Sql> startup restrict;
Sql> alter system disable restricted session;
Sql> alter system enable restricted session;
Suspending a database:
If we want to suspend all i/o operations.
Sql> alter system suspend;
Sql> select database_status from v$instance;
Database_status
—————–
Suspended
Sql> alter system resume;
Sql> select database_status from v$instance;
Database_status
—————–
Active
Quiesce database:
Quiesce a database gives us the opportunity to put our database in a single user mode without shutting down the database.
Startup mount restrict:
This mode has been introduced from oracle 10g onwards. If we want to drop the database then we mount the db it in this mode.
*the nomount state is used by the dba to create a new oracle database.
*the mount state is used by the dba to perform recovery
*the open state is used by the dba and programmers to work with the database in a normal way.
Shutdown modes in oracle:
Shutdown normal or shutdown or shut
When this command is issued then oracle will not allow any new connections/sessions to the database and will wait untill all the exising sessions log off.
There after it will save all the commited transactions and then shut the database.
A common scn number will be updated to controlfiles and datafiles before the database shutsdown.
Shutdown immediate:
When this command is issued then oracle will not allow any new connections/sessions to the database and will forcefully disconnect the existing connected users from the database and rollback all uncommited transactions and shut the database.
A common scn number will be updated to controlfiles and datafiles before the database shutsdown.
Shutdown transactional:
When this command is issued then oracle will not allow any new connections/sessions to the database and will wait for the users to commit/rollback their open transactions after which oracle will shut the database.
A common scn number will be updated to controlfiles and datafiles before the database shuts down.
Shutdown abort:
When this command is issued then oracle will not allow any new connections/sessions to the database and will kill the oracle instance in a abrupt manner.
When this command is issued all the uncommited transactions will be lost and oracle will not be able to update a common scn to all the files of the database. When we restart the db after a shut abort then oracle smon will have to perform crash recovery.
Shut abort is used when we have a loss of files at the o/s level.
We cannot use a normal shutdown as oracle will always write unsaved data to the file before shutting the database, but in this case our files are lost so oracle cannot find the files to write the data in the sga, before shutdown, therefore it will not allow the database to be shut so we have to shutdown abort.
Startup force:
This command will perform a shut abort and then executes a startup. This command must be avoided as it calls for crash recovery.
Important note:
When ever we are shutting a database in a normal way then before shutting the oracle database, oracle will write a common scn to the file headers of the datafiles and to the controlfile.
But incase of a shut abort oracle does not get the chance to write the common scn thus when we restart the database then oracle will find that the scn does not match for the data files and the control file. Thus oracle will call smon to perform ‘crash recovery’ or ‘instance recovery’.