Tuesday, March 15, 2016

Database Startup Options

Database Startup Options

DBA's responsibility is to start and shutdown of oracle instance/database. It is very important to be aware of all the options used and use the appropriate option.
To start up or shutdown oracle database, appropriate privileges are needed.
Two special connection accounts / authorizations are available for startup and shutdown:
SYSDBA: Any database task can be performed with SYSDBA authorization.
SYSOPER: The SYSOPER authorization is a less powerful authorization that allows startup and shutdown abilities but restricts other administrative tasks, such as access to non-administrative schema objects.

These authorizations are managed either through a passwords file or via operating-system control. Only the SYS schema can connect to the database with the SYSDBA authorization on database installation. SYS authorization and the SYSOPER authorization can be provided to give others the ability to perform these tasks other than SYS user.
On Oracle database startup, the memory structures and background processes are initialized and started for users to communicate with oracle database.
On oracle database startup, it goes through Series of steps to ensure database consistency and a database passes through three modes as:

NOMOUNT
MOUNT
OPEN

Now let me discuss with these modes and the series of events happens on each mode before database is available for normal use.

STARTUP NOMOUNT:

STARTUP NOMOUNT starts the instance without mounting the database. On starting the database on this mode, Parameter file is read file is read and background processes and memory structures are initiated. But the processes are not attached or communicating with the disk structures of the database. Database is not available for use and we can perform only certain tasks.
One of the most common tasks is running scripts to create the underlying database.
The next mode is the mount and some of the times because of some reasons, it hinders database to go to next mode i.e. mount mode. This happens when oracle has a problem in reading the control file structures, which has the important information to continue with startup process. If these structures are damaged or not available, need to solve the problem before database startup process continue.

STARTUP MOUNT:

The STARTUP MOUNT option attaches and interacts with the database structures. On this state Oracle retrieves the information from the control file s that it uses to locate and attach to main database structures.
Many administrative tasks can be performed while database is in mount mode like recovery. You can also physically change file locations or place the database in archive log mode.

STARTUP OPEN:

The STARTUP OPEN option is the default startup mode if no mode is specified on the STARTUP command line. STARTUP OPEN performs all the steps of the STARTUP NOMOUNT and STARTUP MOUNT options. This option makes the database available to all users.

STARTUP FORCE:

In case of difficulty in normal database startup, we use startup force option. Some of the scenarios where this option is used like if a database server lost power and the database stopped abruptly, it can leave the database in a state in which a STARTUP FORCE startup is necessary. What is also different about STARTUP FORCE is that it can be issued no matter what mode the database is in. STARTUP FORCE does a shutdown abort and then restarts the database.

STARTUP RESTRICT:

The STARTUP RESTRICT option starts up the database and places it in OPEN mode, but gives access only to users who have the RESTRICTED SESSION privilege. When you want to perform maintenance on the database while it is open but ensure that users cannot connect and perform work on the database and we use RESTRICTED option.
You can disable the restricted session, SQL>ALTER SYSTEM DISABLE RESTRICTED SESSION; , so everyone can connect to the database.

STARTUP UPGRADE:

When database started with upgrade option, it starts with OPEN UPGRADE mode and sets the initialization parameters to specific values. These values are required to run the database upgrade scripts. This option is only used while we start the database for first time with new version of oracle database server.
Once the upgrade completes, the database should be shut down and restarted normally.

No comments:

Post a Comment