Oracle Database Startup Stages Or Modes

Hey Folks,

Here we will see some basics stuff about Oracle Database Startup and shutdown stages like Open database is in restrict, read-only, open and what happens if we open database in these modes and why these are requires and what basics command we use to do that. Let's start.

 

ALTER DATABASE command : 

• Change the state of the database from nomount to Mount

          ALTER DATABASE ORCL MOUNT;

• Open the database as a read-only/open  database

         ALTER DATABASE ORCL OPEN READ ONLY;

To move the Database from the NOMOUNT to  MOUNT stage or from the MOUNT to  OPEN stage, Use the ALTER Database command.

    ALTER DATABSE  {MOUNT | OPEN };

To Prevent data from being modified by user transaction, the database can be opened in read-only mode.

To Start up an Instance, Use the Fallowing Command :

    ALTER DATABSE OPEN { READ WRITE | READ ONLY };

Where :

• READ WRITE : Opens the database in read/write mode, so that users can generate online redo log file.
• READ ONLY : Restricts Users to read-only  transactions, preventing them from generating online redo log file information.

 Note : Read/Write is the default mode of operation.

Opening a Database in Restricted Mode :

• Use the STARTUP command to restrict access to a database

          STARTUP RESTRICT

• Use the ALTER SYSTEM command to place an instance in restricted mode.

           ALTER SYSTEM ENABLE RESTRICTED SESSION;

 

A restricted Session is useful, For example, When you perform structure maintenance or a database export and import. The database can be started in restricted mode so that  it is available only to users with the RESTRICTION SESSION Privilege.

The Database can also be put in restricted mode ALTER SYSTEM SQL command :

    ALTER SYSTEM [ { ENABLE/DISABLE } RESTRICTED SESSION ] 

Where : 

ENABLE RESTRICTED SESSION : Enables future logins only for users who have the RESTRICTED SESSION privilege

DISABLE RESTRICTED SESSION : Disables RESTRICTED SESSION so that the Users who do not have the privilege can log on also.

Terminate Session :

After placing an instance in restricted mode, you may want to kill all current user sessions before performing administrative tasks. this can be done by the fallowing :

    ALTER SYSTEM KILL SESSION  'Integer1', Integer2';

Where :

Integer1 : Value of the SID column in the V$SESSION view

Integer2 : Value of the SERIAL# column in the V$SESSION view

Note : The session ID and Serial number are used to uniquely identify a session. This Guarantees that the ALTER SYSTEM KILL SESSION command is applied to be the correct session even if the user logs off and a new session uses the same session ID.

 

Effect of  terminating Session :

The ALTER SYSTEM KILL SESSION command causes the background process PMON to perform the fallowing steps upon executions :

• Roll back the user's current transactions
• Release all currently help table or row locks
• Free all resources currently reserved by the users session.

Opening a Database in Read-Only Mode : 

Opening a database in read-only mode enables you to query like an open database But it Protects any potential online data content changes. While opening a database in read-only mode guarantees that datafile and redo log files are not written to, it does not restrict database recovery or operations that change the state of the database without generating redo. For example, you can take datafiles offline or bring them online since these operations do not affect data content.

• Opening a database in Read-Only mode :

          STARTUP MOUNT

          ALTER DATABASE OPEN READ ONLY;

• Can be used to
• Execute queries
• Execute disk sorts using locally managed tablespaces
• Take data files offline and online, but not tablespaces
• Perform recovery of offline data files and tablespaces 

 

A database can be opened as read-only, as long as it is not already open in read/write mode. The feature is especially useful for a standby database to offload query processing from the production database. 

If a query needs to use a temporary tablespace, for example , to do a sort operation, the current user must have a locally managed  tablespace assigned as the default temporary tablespace, otherwise the query fails.

Read-Only mode does not restrict database recovery or operations that change the database state without generating redo data. For Example, in read-only mode :

• Data files can be taken offline and online
• Recovery of offline data files and tablespaces can be performed

Disk writes to other files, such as control files, operating system audit trail, trace files, and alert log files, can continue in Read-Only mode.

Thank You !!