Archive for the ‘Oracle DBA’ Category

Oracle Shutdown Concepts

Shut down the database to make operating system offline backups of all physical structures and to have modified static initialization parameters take effect when restarted.

SHUTDOWN [NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT ]

SHUTDOWN NORMAL

Normal is the default shutdown mode.Normal database shutdown proceeds with the
following conditions:

• No new connections can be made.
• The Oracle server waits for all users to disconnect before completing the shutdown.
• Database and redo buffers are written to disk.
• Background processes are terminated, and the SGA is removed from memory.
• Oracle closes and dismounts the database before shutting down the instance.
• The next startup does not require an instance recovery.

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>



SHUTDOWN TRANSACTIONAL

A transactional shutdown prevents clients from losing work. A transactional database

shutdown proceeds with the following conditions:

• No client can start a new transaction on this particular instance.
• A client is disconnected when the client ends the transaction that is in progress.
• When all transactions have finished, a shutdown occurs immediately.
• The next startup does not require an instance recovery.

SQL> shutdown transactional

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SHUTDOWN IMMEDIATE

Immediate database shutdown proceeds with the following conditions:

• Current SQL statements being processed by Oracle are not completed.
• The Oracle server does not wait for the users, who are currently connected to the database, to disconnect.
• Oracle rolls back active transactions and disconnects all connected users.
• Oracle closes and dismounts the database before shutting down the instance.
• The next startup does not require an instance recovery.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SHUTDOWN ABORT

If no shutdown option works then database instance can be shutdown using abort option. Aborting an instance proceeds with the following conditions:

• Current SQL statements being processed by the Oracle server are immediately terminated.
• Oracle does not wait for users currently connected to the database to disconnect.
• Database and redo buffers are not written to disk.
• Uncommitted transactions are not rolled back.
• The instance is terminated without closing the files.
• The database is not closed or dismounted.
• The next startup requires instance recovery, which occurs automatically.

SQL> shutdown abort

ORACLE instance shut down.

SQL>

All shutdown options except ABORT are clean shutdown where no instance recovery is required. Shutdown abort is a dirty shutdown as it requires instance recovery before getting back up.

Starting and Altering Database

Starting Up a Database

There are different states in which database can start up. The following scenarios describe different stages of starting up an instance starting with shutdown.

 

SHUTDOWN

NOMOUNT

MOUNT

OPEN

 

Starting the Instance (NOMOUNT)

An instance is started in the NOMOUNT stage only during database creation or the re-creation
of control files.
Starting an instance includes the following tasks:

• Reading the initialization file from $ORACLE_HOME/dbs in the following order:
        – First spfileSID.ora
        – If not found then, spfile.ora
        – If not found then, initSID.ora
• Allocating the SGA
• Starting the background processes
• Opening the alertSID.log file and the trace files

STARTUP NOMOUNT

Specifying the PFILE parameter with STARTUP overrides the default behavior

STARTUP NOMOUNT PFILE = /u01/oracle/dbs/init.ora
 

Mounting the Database (MOUNT)

To perform specific maintenance operations, you start an instance and mount a database but
do not open the database.

For example, the database must be mounted but not open during the following tasks:
• Renaming data files (data files for an offline tablespace can be renamed when the
database is open)
• Enabling and disabling online redo log file archiving options
• Performing full database recovery
Mounting a database includes the following tasks:
• Associating a database with a previously started instance
• Locating and opening the control files specified in the parameter file
• Reading the control files to obtain the names and status of the data files and online
redo log files. However, no checks are performed to verify the existence of the data
files and online redo log files at this time.

STARTUP MOUNT

 

Opening the Database (OPEN)

Normal database operation means that an instance is started and the database is mounted and
open. Opening the database includes the following tasks:

• Opening the online data files
• Opening the online redo log files

If any of the data files or online redo log files are not present when you attempt to open the
database, the Oracle server returns an error. Oracle server verifies that all the data files and online redo log
files can be opened and checks the consistency of the database. If necessary, the SMON
background process initiates instance recovery.

STARTUP

 

Restricting Access to an Instance at Startup

You can start an instance, and optionally mount and open a database, in restricted mode so that the instance is available only to administrative personnel (not general database users). Use this mode of instance startup when you need to accomplish one of the following tasks:

  • Perform an export or import of data

  • Perform a data load (with SQL*Loader)

  • Temporarily prevent typical users from using data

  • Perform certain migration or upgrade operations

STARTUP RESTRICT

 

Forcing Instance to Start / Instance with Media Recovery

         You cannot shut down the current instance with the SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN TRANSACTIONAL commands. Use the STARTUP command with the FORCE clause:

STARTUP FORCE

If media recovery is required, start by using the STARTUP command with the RECOVER clause:

STARTUP OPEN RECOVER
 

Altering Database Availability

You can alter the availability of a database. You may want to do this in order to restrict access for maintenance reasons or to make the database read only. The following sections explain how to alter the availability of a database:

 

Database can be altered to mount state from nomount state but cannot be altered directly to open state.

ALTER DATABASE MOUNT;

To open a mounted database, use the ALTER DATABASE statement with the OPEN clause:

ALTER DATABASE OPEN;

 

Opening a database in read-only mode enables you to query an open database while eliminating any potential for online data content changes.

ALTER DATABASE OPEN READ ONLY;

You can also open a database in read/write mode as follows which is the default mode:

ALTER DATABASE OPEN READ WRITE;