12 Database and Instance Startup and Shutdown

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.

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.

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 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.

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.

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

• Transaction 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 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:

• Oracle Database Performance Tuning Guide for a discussion of instance recovery mechanics and instructions for tuning instance and crash recovery

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

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.

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.

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.

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.

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.

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.