Starting and Stoping Oracle Database

by E-Computer Concepts September 23, 2019 at 5:22 pm

Starting the DATABASE

A database needs to be started before it can be accessed by users, Starting Up a database should be done by a privileged user. User who have been granted the SYSDBA or SYSOPER role can perform a startup or a shutdown of the database. When a database is started three important steps are executed. The first involves creation of the Oracle instance in memory followed by mounting the database where the control files are read and finally opening the database where all the data stored in the database is accessible to the users of the database. To get a complete understanding let us deal with each stage individually.

1. Instance Creation

This is the first step in starting the database. The initialization parameter file is read, (spfile or pfile depending on the options used in the STARTUP command) and the System Global Area (SGA) is configured. The parameter file identifies the name of the database, and various sizes for the memory pools, the optional mandatory and optional background processes are started. The alert log file and other trace file are also started.

2. Mounting the Database

After instance creation, the database is mounted. When a database is mounted a database administrator can perform certain maintenance or administrative tasks.

3. Opening the Database

This is the last stage in starting a database. This phase has to be performed so that users of the database can access the data in the database. Once the control file has been read and the location of the physical files of the database identified after mounting, the files are opened and made available to the users. The files that are opened are the online datafiles and the online redo log files. If any of the files are unavailable, an error will be reported and the database will not be opened. It is at this point that the Oracle server verifies the consistency of the database. In case the database was shutdown improperly the last time, it will be detected at this point and the SMON background process will perform instance recovery.

Shuting Down the DATABASE

1. Normal

This is the default mode for shutting down the database. The Oracle server waits for all currency connected users to disconnect their sessions. No new connections are permitted. A checkpoint is performed on all the databases and the files are closed. When a database is shutdown in this mode, an instance recovery will not need to be done during the subsequent startup.

2. Immediate

When a database is shutdown in this mode, the Oracle server automatically rolls back all currency active transactions. After the transactions have been rolled back the user sessions are terminated. No new connections are allowed. The database is then closed, dismounted and the instance released. No instance recovery will be performed during subsequent startup.

3. Transactional

When the database is shutdown using this option all currently active transactions will be allowed to complete. As soon as a user’s transaction completes the user is automatically disconnected. No new user connections will be allowed. The database is then closed, dismounted and the instance released. No instance recovery will be performed during subsequent startup.

4. Abort

When the database is shutdown using this option, the instance is shutdown. This is a case of an improper shutdown. No check pointing is done. All user connections are abnormally terminated. The database is not closed and dismounted.

Add Comment