Wednesday, October 10, 2012

Startup And Shutdown Modes Of An Oracle Database

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.

Startup no mount: 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 no mount state to

1) Create an oracle database
2) Re-create a controlfile for an existing database

Mount State: From no mount 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;

(or)

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'.


Startup and Shtdown demo:

SQL> CONN /AS SYSDBA
Connected.

SQL> SELECT COUNT(*) FROM TAB;

COUNT(*)
----------
3492

1 row selected.

SQL> SHOW USER
USER is "SYS"

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> EXIT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

[chow@oraserver ~]$
[chow@oraserver ~]$ echo $ORACLE_SID
chow

[chow@oraserver ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 22 08:39:06 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> exit
Disconnected

[chow@oraserver ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 22 08:39:14 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 79691776 bytes
Fixed Size 1217812 bytes
Variable Size 62917356 bytes
Database Buffers 12582912 bytes
Redo Buffers 2973696 bytes

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS
------------
STARTED

SQL> ALTER DATABASE MOUNT;

Database altered.

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS
------------
MOUNTED

SQL> SHUTDOWN
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 79691776 bytes
Fixed Size 1217812 bytes
Variable Size 62917356 bytes
Database Buffers 12582912 bytes
Redo Buffers 2973696 bytes
Database mounted.

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS
------------
MOUNTED

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS
------------
OPEN

SQL> ALTER DATABASE CLOSE;

Database altered.

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS
------------
MOUNTED

SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed


SQL> ALTER DATABASE DISMOUNT;

Database altered.

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS
------------
STARTED

SQL> ALTER DATABASE MOUNT;
ALTER DATABASE MOUNT
*
ERROR at line 1:
ORA-00750: database has been previously mounted and dismounted


SQL> SHUTDOWN
ORA-01507: database not mounted


ORACLE instance shut down.

SQL> STARTUP
ORACLE instance started.

Total System Global Area 79691776 bytes
Fixed Size 1217812 bytes
Variable Size 62917356 bytes
Database Buffers 12582912 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP RESTRICT
ORACLE instance started.

Total System Global Area 79691776 bytes
Fixed Size 1217812 bytes
Variable Size 62917356 bytes
Database Buffers 12582912 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

SQL> CREATE USER SCOTT IDENTIFIED BY TIGER;

User created.

SQL> GRANT CONNECT,RESOURCE TO SCOTT;

Grant succeeded.

SQL> CONN SCOTT/TIGER
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege


Warning: You are no longer connected to ORACLE.
SQL> CONN /AS SYSDBA
Connected.
SQL> GRANT RESTRICTED SESSION TO SCOTT;

Grant succeeded.

SQL> CONN SCOTT/TIGER
Connected.

SQL> SHOW USER
USER is "SCOTT"

SQL> CONN /AS SYSDBA
Connected.

SQL> REVOKE RESTRICTED SESSION FROM SCOTT;

Revoke succeeded.

SQL> CONN SCOTT/TIGER
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege


Warning: You are no longer connected to ORACLE.

SQL> CONN /AS SYSDBA
Connected.

SQL> ALTER DATABASE RESTRICTED SESSION DISABLE;
ALTER DATABASE RESTRICTED SESSION DISABLE
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE


SQL> ALTER DATABASE DISBALE RESTRICTED SESSION;
ALTER DATABASE DISBALE RESTRICTED SESSION
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE


SQL> ED
Wrote file afiedt.buf

1* ALTER DATABASE DISABLE RESTRICTED SESSION

SQL> /
ALTER DATABASE DISABLE RESTRICTED SESSION
*
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> ED
Wrote file afiedt.buf

1* ALTER SYSTEM DISABLE RESTRICTED SESSION
SQL> /

System altered.

SQL> CONN SCOTT/TIGER
Connected.

SQL> CONN /AS SYSDBA
Connected.

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

System altered.

SQL> CONN SCOTT/TIGER
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege


Warning: You are no longer connected to ORACLE.

SQL> CONN /AS SYSDBA
Connected.

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

System altered.

SQL> ALTER DATABASE SUSPEND;
ALTER DATABASE SUSPEND
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE


SQL> ALTER SYSTEM SUSPEND;

System altered.

SQL> ALTER SYSTEM RESUME;

System altered.

SQL>


3 comments:

  1. hi i am raju dba tecnomedia ent

    ReplyDelete
  2. can you explain what will happen internally when we issue sqlplus '/as sysdba'?

    ReplyDelete
  3. When you enter SYS or a slash ( / ) as the user name and provide the AS SYSDBA clause, your access is authenticated using operating system authentication. Operating system authentication uses your Windows, UNIX, or Linux host user account to authenticate you to Oracle Database.

    ReplyDelete

Some Most Popular Articles