Skip Headers
Oracle® Database Concepts
11g Release 1 (11.1)

Part Number B28318-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF

12 Database and Instance Startup and Shutdown

This chapter explains the procedures involved in starting and stopping an Oracle database instance and database.

This chapter contains the following topics:

Introduction to an Oracle Instance

Every running Oracle Database is associated with an Oracle database instance. When a database is started on a database server (regardless of the type of computer), Oracle Database allocates a memory area called the System Global Area (SGA) and starts one or more Oracle Database processes. This combination of the SGA and the Oracle Database processes is called an Oracle instance. The memory and processes of an instance manage the associated database's data efficiently and serve the one or multiple users of the database.

Figure 12-1 shows an Oracle database instance.

Figure 12-1 An Oracle Instance

Description of Figure 12-1 follows
Description of "Figure 12-1 An Oracle Instance"

This section includes the following topics:

The Instance and the Database

After starting an instance, Oracle Database associates the instance with the specified database. This is a mounted database. The database is then ready to be opened, which makes it accessible to authorized users.

Multiple instances can run concurrently on the same computer, each accessing its own physical database. In large-scale cluster systems, Oracle Real Application Clusters enables multiple instances to mount a single database.

Only the database administrator can start up an instance and open the database. If a database is open, then the database administrator can shut down the database so that it is closed. When a database is closed, users cannot access the data that it contains.

Security for database startup and shutdown is controlled through connections to Oracle Database with administrator privileges. Normal users do not have control over the current status of an Oracle database.

Connection with Administrator Privileges

Database startup and shutdown are powerful administrative options and are restricted to users who connect to Oracle Database with administrator privileges. Depending on the operating system, one of the following conditions establishes administrator privileges for a user:

  • The user's operating system privileges allow him or her to connect using administrator privileges.

  • The user is granted the SYSDBA or SYSOPER privileges and the database uses password files to authenticate database administrators.

When you connect with SYSDBA privileges, you are in the schema owned by SYS. When you connect as SYSOPER, you are in the public schema. SYSOPER privileges are a subset of SYSDBA privileges.

See Also:

  • Your operating system-specific Oracle Database documentation for more information about how administrator privileges work on your operating system

  • Chapter 20, "Database Security" for more information about password files and authentication schemes for database administrators

Initialization Parameter Files and Server Parameter Files

To start an instance, Oracle Database must read either an initialization parameter file or a server parameter file. These files contain a list of configuration parameters for that instance and database. Oracle Database traditionally stored initialization parameters in a text initialization parameter file. You can also choose to maintain initialization parameters in a server-side binary server parameter file (SPFILE).

Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup.

Initialization parameters are divided into two groups: basic and advanced. In the majority of cases, it is necessary to set and tune only the basic parameters to get reasonable performance. In rare situations, modification to the advanced parameters may be needed for optimal performance.

Most initialization parameters belong to one of the following groups:

  • Parameters that name things, such as files

  • Parameters that set limits, such as maximums

  • Parameters that affect capacity, such as the size of the SGA, which are called variable parameters

Among other things, the initialization parameters tell Oracle Database:

  • The name of the database for which to start up an instance

  • How much memory to use for memory structures in the SGA

  • What to do with filled redo log files

  • The names and locations of the database control files

  • The names of undo tablespaces in the database

This section includes the following topics:

Server Parameter Files and Hardware Assisted Resilient Data

The Oracle Hardware Assisted Resilient Data (HARD) initiative is a comprehensive program designed to prevent data corruptions before they happen. By implementing the Oracle data validation algorithms inside storage devices, Oracle Database can prevent corrupted data from being written to permanent storage. Starting in Oracle Database 11g, you can create a server parameter file in a new format that is usable on a HARD-compliant storage system. The database can read and write server parameter files in both the old and new format.

See Also:

Oracle Database Administrator's Guide to learn how to create and manage a server parameter file

How Parameter Values Are Changed

The database administrator can adjust variable parameters to improve the performance of a database system. Exactly which parameters most affect a system depends on numerous database characteristics and variables.

Some parameters can be changed dynamically with the ALTER SESSION or ALTER SYSTEM statement while the instance is running. Unless you are using a server parameter file (SPFILE), changes made using the ALTER SYSTEM statement are only in effect for the current instance. You must manually update the text initialization parameter file for the changes to be known the next time you start up an instance.

When you use a server parameter file, you can use the ALTER SYSTEM SET statement to change parameter values in memory, disk, or both. The database prints the new value and the old value (if it exists) to the alert log. As a preventative measure, the database performs validation steps when you change a basic parameter to prevent illegal values from being written to the server parameter file.

Oracle Database provides values in the starter initialization parameter file provided with your database software, or as created for you by the Database Configuration Assistant. You can edit these Oracle-supplied initialization parameters and add others, depending upon your configuration and options and how you plan to tune the database. For any relevant initialization parameters not specifically included in the initialization parameter file, Oracle supplies defaults. If you are creating a database for the first time, it is suggested that you minimize the number of parameter values that you alter.

See Also:

Overview of Instance and Database Startup

The three steps to starting an Oracle database and making it available for systemwide use are:

  1. Start an instance.

  2. Mount the database.

  3. Open the database.

A database administrator can perform these steps using the SQL*Plus STARTUP statement or Enterprise Manager.

This section includes the following topics:

How an Instance Is Started

When Oracle Database starts an instance, it reads the server parameter file (SPFILE) or initialization parameter file to determine the values of initialization parameters. Then, it allocates an SGA, which is a shared area of memory used for database information, and creates background processes. At this point, no database is associated with these memory structures and processes.

When the instance starts, the database writes all explicit parameter settings to the alert log in valid parameter syntax. If necessary, you can copy and paste this text into a new parameter file and restart the instance. Note that changed

See Also:

This section includes the following topics:

Restricted Mode of Instance Startup

You can start an instance in restricted mode (or later alter an existing instance to be in restricted mode). This restricts connections to only those users who have been granted the RESTRICTED SESSION system privilege.

Forced Startup in Abnormal Situations

In unusual circumstances, a previous instance might not have been shut down cleanly. For example, one of the instance's processes might not have terminated properly. In such situations, the database can return an error during normal instance startup. To resolve this problem, you must terminate all remnant Oracle Database processes of the previous instance before starting the new instance.

How a Database Is Mounted

The instance mounts a database to associate the database with that instance. To mount the database, the instance finds the database control files and opens them. Control files are specified in the CONTROL_FILES initialization parameter in the parameter file used to start the instance. Oracle Database then reads the control files to get the names of the database's datafiles and redo log files.

At this point, the database is still closed and is accessible only to the database administrator. The database administrator can keep the database closed while completing specific maintenance operations. However, the database is not yet available for normal operations.

This section includes the following topics:

How a Database Is Mounted with Oracle Real Application Clusters

If Oracle Database allows multiple instances to mount the same database concurrently, the database administrator can use the CLUSTER_DATABASE initialization parameter to make the database available to multiple instances. The default value of the CLUSTER_DATABASE parameter is false. Versions of Oracle Database that do not support Oracle RAC only allow CLUSTER_DATABASE to be false.

If CLUSTER_DATABASE is false for the first instance that mounts a database, then only that instance can mount the database. If CLUSTER_DATABASE is set to true on the first instance, then other instances can mount the database if their CLUSTER_DATABASE parameters are set to true. The number of instances that can mount the database is subject to a predetermined maximum, which you can specify when creating the database.

See Also:

for more information about the use of multiple instances with a single database

How a Clone Database Is Mounted

A clone database is a specialized copy of a database that can be used for tablespace point-in-time recovery. When you perform tablespace point-in-time recovery, you mount the clone database and recover the tablespaces to the desired time, then export metadata from the clone to the primary database and copy the datafiles from the recovered tablespaces.

See Also:

Oracle Database Backup and Recovery User's Guide for information about clone databases and tablespace point-in-time recovery

What Happens When You Open a Database

Opening a mounted database makes it available for normal database operations. Any valid user can connect to an open database and access its information. Usually, a database administrator opens the database to make it available for general use.

When you open the database, Oracle Database opens the online datafiles and redo log files. If a tablespace was offline when the database was previously shut down, the tablespace and its corresponding datafiles will still be offline when you reopen the database.

If any of the datafiles or redo log files are not present when you attempt to open the database, then Oracle Database returns an error. You must perform recovery on a backup of any damaged or missing files before you can open the database.

See Also:

"Online and Offline Tablespaces" for information about opening an offline tablespace

This section includes the following topics:

Crash and Instance Recovery

Database buffers in the buffer cache in the SGA are written to disk only when necessary, using a least-recently-used (LRU) algorithm. Because of the way that the database writer process uses this algorithm to write database buffers to datafiles, datafiles could contain some data blocks modified by uncommitted transactions and some data blocks missing changes from committed transactions.

Two potential problems can result if an instance failure occurs:

  • Data blocks modified by a transaction might not be written to the datafiles at commit time and might only appear in the redo log. Therefore, the redo log contains changes that must be reapplied to the database during recovery.

  • After the roll forward phase, the datafiles could contain changes that had not been committed at the time of the failure. These uncommitted changes must be rolled back to ensure transactional consistency. These changes were either saved to the datafiles before the failure, or introduced during the roll forward phase.

If the database was last closed abnormally, either because the database administrator terminated its instance or because of a power failure, Oracle Database automatically performs instance or crash recovery when the database is reopened.

Crash recovery is used to recover from a failure either when a single-instance database fails or all instances of an Oracle Real Application Clusters database fail. Instance recovery refers to the case where a surviving instance recovers a failed instance in an Oracle Real Application Clusters database.

The goal of crash and instance recovery is to restore the data block changes located in the cache of the terminated instance and to close the redo thread that was left open. Instance and crash recovery use only online redo log files and current online datafiles. Oracle Database recovers the redo threads of the terminated instances together.

When recovering a database with encrypted tablespaces (for example after a SHUTDOWN ABORT or a catastrophic error that brings down the database instance), you must open the Oracle Wallet after database mount and before database open, so the recovery process can decrypt data blocks and redo.

Crash and instance recovery involve two distinct operations: rolling forward the current, online datafiles by applying both committed and uncommitted transactions contained in online redo records, and then rolling back changes made in uncommitted transactions to their original state.

Crash and instance recovery have the following shared characteristics:

  • Redo the changes using the current online datafiles (as left on disk after the failure or SHUTDOWN ABORT)

  • Use only the online redo logs and never require the use of the archived logs

  • Have a recovery time governed by the number of terminated instances, amount of redo generated in each terminated redo thread since the last checkpoint, and by user-configurable factors such as the number and size of redo log files, checkpoint frequency, and the parallel recovery setting

Oracle Database performs this recovery automatically on two occasions:

  • At the first database open after the failure of a single-instance database or all instances of an Oracle RAC database (crash recovery).

  • When some but not all instances of an Oracle RAC configuration fail (instance recovery). The recovery is performed automatically by a surviving instance in the configuration.

The important point is that in both crash and instance recovery, Oracle Database applies the redo automatically: no user intervention is required to supply redo logs. Nevertheless, you can set parameters in the database server that can tune the duration of instance and crash recovery performance. Also, you can tune the rolling forward and rolling back phases of instance recovery separately.

To solve this dilemma, two separate steps are generally used by Oracle Database for a successful recovery of a system failure: rolling forward with the redo log (cache recovery) and rolling back with the rollback or undo segments (transaction recovery).

This section includes the following topics:

Cache Recovery

To solve this dilemma, two separate steps are generally used by Oracle Database for a successful recovery of a system failure: rolling forward with the redo log (cache recovery) and rolling back with the rollback or undo segments (transaction recovery).

The online redo log is a set of operating system files that record all changes made to any database block, including data, index, and rollback segments, whether the changes are committed or uncommitted. All changes to Oracle Database blocks are recorded in the online redo log.

The first step of recovery from an instance or media failure is called cache recovery or rolling forward, and involves reapplying all of the changes recorded in the redo log to the datafiles. Because rollback data is also recorded in the redo log, rolling forward also regenerates the corresponding rollback segments.

Rolling forward proceeds through as many redo log files as necessary to bring the database forward in time. Rolling forward usually includes online redo log files (instance recovery or media recovery) and could include archived redo log files (media recovery only).

After rolling forward, the data blocks contain all committed changes. They could also contain uncommitted changes that were either saved to the datafiles before the failure, or were recorded in the redo log and introduced during cache recovery.

Transaction Recovery

After the roll forward, any changes that were not committed must be undone. Oracle Database applies undo blocks to roll back uncommitted changes in data blocks that were either written before the failure or introduced by redo application during cache recovery. This process is called rolling back or transaction recovery.

Figure 12-2 illustrates rolling forward and rolling back, the two steps necessary to recover from any type of system failure.

Figure 12-2 Basic Recovery Steps: Rolling Forward and Rolling Back

Description of Figure 12-2 follows
Description of "Figure 12-2 Basic Recovery Steps: Rolling Forward and Rolling Back"

Oracle Database can roll back multiple transactions simultaneously as needed. All transactions that were active at the time of failure are marked as terminated. Instead of waiting for SMON to roll back terminated transactions, new transactions can recover blocking transactions themselves to get the row locks they need.

See Also:

Undo Space Acquisition and Management

When you open the database, the instance attempts to acquire an undo tablespace. If more than one undo tablespace exists, the UNDO_TABLESPACE initialization parameter designates the undo tablespace to use. If this parameter is blank, the first available undo tablespace in the database is chosen.

See Also:

"Introduction to Undo Segments and Automatic Undo Management" for more information about undo tablespaces.

Resolution of In-Doubt Distributed Transaction

Occasionally a database closes abnormally with one or more distributed transactions in doubt (neither committed nor rolled back). When you reopen the database and recovery is complete, the RECO background process automatically, immediately, and consistently resolves any in-doubt distributed transactions.

See Also:

Oracle Database Administrator's Guide for information about recovery from distributed transaction failures

Open a Database in Read-Only Mode

You can open any database in read-only mode to prevent its data from being modified by user transactions. Read-only mode restricts database access to read-only transactions, which cannot write to the datafiles or to the redo log files.

Disk writes to other files, such as control files, operating system audit trails, trace files, and alert logs, can continue in read-only mode. Temporary tablespaces for sort operations are not affected by the database being open in read-only mode. However, you cannot take permanent tablespaces offline while a database is open in read-only mode. Also, job queues are not available in read-only mode.

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

  • Datafiles can be taken offline and online

  • Offline datafiles and tablespaces can be recovered

  • The control file remains available for updates about the state of the database

One useful application of read-only mode is that standby databases can function as temporary reporting databases.

See Also:

Oracle Database Administrator's Guide for information about how to open a database in read-only mode

Limitations of a Read-only Database

  • An application must not write database objects while executing against a read-only database. For example, an application writes database objects when it inserts, deletes, updates, or merges rows in a database table, including a global temporary table. An application writes database objects when it manipulates a database sequence. An application writes database objects when it locks rows, when it runs EXPLAIN PLAN, or when it executes DDL. Many of the functions and procedures in Oracle-supplied PL/SQL packages, such as DBMS_SCHEDULER, write database objects. If your application calls any of these functions and procedures, or if it performs any of the operations mentioned above, your application writes database objects and hence is not read-only.

  • When executing on a read-only database, you must commit or roll back any in-progress transaction that involves one database link before you use another database link. This is true even if you execute a generic SELECT statement on the first database link and the transaction is currently read-only.

  • You cannot compile or recompile PL/SQL stored procedures on a read-only database. To minimize PL/SQL invalidation because of remote procedure calls, use REMOTE_DEPENDENCIES_MODE=SIGNATURE in any session that does remote procedure calls on a read-only database.

  • You cannot invoke a remote procedure (even a read-only remote procedure) from a read-only database if the remote procedure has never been called on the database. This limitation applies to remote procedure calls in anonymous PL/SQL blocks and in SQL statements. You can either put the remote procedure call in a stored procedure, or you can invoke the remote procedure in the database prior to it becoming read only.

Overview of Database and Instance Shutdown

The three steps to shutting down a database and its associated instance are:

  1. Close the database.

  2. Unmount the database.

  3. Shut down the instance.

A database administrator can perform these steps using Enterprise Manager. Oracle Database automatically performs all three steps whenever an instance is shut down.

This section includes the following topics:

Close a Database

When you close a database, Oracle Database writes all database data and recovery data in the SGA to the datafiles and redo log files, respectively. Next, Oracle Database closes all online datafiles and redo log files. (Any offline datafiles of any offline tablespaces have been closed already. If you subsequently reopen the database, any tablespace that was offline and its datafiles remain offline and closed, respectively.) At this point, the database is closed and inaccessible for normal operations. The control files remain open after a database is closed but still mounted.

Close the Database by Terminating the Instance

In rare emergency situations, you can terminate the instance of an open database to close and completely shut down the database instantaneously. This process is fast, because the operation of writing all data in the buffers of the SGA to the datafiles and redo log files is skipped. The subsequent reopening of the database requires recovery, which Oracle Database performs automatically.


If a system or power failure occurs while the database is open, then the instance is, in effect, terminated, and recovery is performed when the database is reopened.

Unmount a Database

After the database is closed, Oracle Database unmounts the database to disassociate it from the instance. At this point, the instance remains in the memory of your computer.

After a database is unmounted, Oracle Database closes the control files of the database.

Shut Down an Instance

The final step in database shutdown is shutting down the instance. When you shut down an instance, the SGA is removed from memory and the background processes are terminated.

Abnormal Instance Shutdown

In unusual circumstances, shutdown of an instance might not occur cleanly; all memory structures might not be removed from memory or one of the background processes might not be terminated. When remnants of a previous instance exist, a subsequent instance startup most likely will fail. In such situations, the database administrator can force the new instance to start up by first removing the remnants of the previous instance and then starting a new instance, or by issuing a SHUTDOWN ABORT statement in SQL*Plus or using Enterprise Manager.

See Also:

Oracle Database Administrator's Guide for more detailed information about instance and database startup and shutdown