13 Oracle Database Instance

Database Instance Structure

When an instance is started, Oracle Database allocates a memory area called the system global area (SGA) and starts one or more background processes. The SGA serves various purposes, including the following:

• Maintaining internal data structures that are accessed by many processes and threads concurrently

• Caching data blocks read from disk

• Buffering redo data before writing it to the online redo log files

• Storing SQL execution plans

The SGA is shared by the Oracle processes, which include server processes and background processes, running on a single computer. The way in which Oracle processes are associated with the SGA varies according to operating system.

A database instance includes background processes. Server processes, and the process memory allocated in these processes, also exist in the instance. The instance continues to function when server processes terminate.

Figure 13-1 shows the main components of an Oracle database instance.

Figure 13-1 Database Instance

Description of "Figure 13-1 Database Instance"

Database Instance Configurations

You can run Oracle Database in either of the following mutually exclusive configurations:

• Single-instance configuration

  A one-to-one relationship exists between the database and an instance.

• Oracle Real Application Clusters (Oracle RAC) configuration

  A one-to-many relationship exists between the database and instances.

Figure 13-2 shows possible database instance configurations.

Figure 13-2 Database Instance Configurations

Description of "Figure 13-2 Database Instance Configurations"

Whether in a single-instance or Oracle RAC configuration, a database instance is associated with only one database at a time. You can start a database instance and mount (associate the instance with) one database, but not mount two databases simultaneously with the same instance.

Multiple instances can run concurrently on the same computer, each accessing its own database. For example, a computer can host two distinct databases: prod1 and prod2. One database instance manages prod1, while a separate instance manages prod2.

SQL> STARTUP
ORACLE instance started.
 
Total System Global Area  468729856 bytes
Fixed Size                  1333556 bytes
Variable Size             440403660 bytes
Database Buffers           16777216 bytes
Redo Buffers               10215424 bytes
Database mounted.
Database opened.

SQL> SELECT 
TO_CHAR(STARTUP_TIME,'MON-DD-RR HH24:MI:SS')
AS "Inst Start Time" FROM V$INSTANCE;
 
Inst Start Time
------------------
JUN-18-09 13:14:48

SQL> ALTER DATABASE CLOSE;
 Database altered.

SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-16196: database has been
previously opened and closed

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP
Oracle instance started.
. . .

SQL> SELECT 
TO_CHAR(STARTUP_TIME,'MON-DD-RR HH24:MI:SS')
AS "Inst Start Time" FROM V$INSTANCE;
 
Inst Start Time
------------------
JUN-18-09 13:16:40

Overview of Instance and Database Startup

In a typical use case, you manually start an instance and then mount and open the database, making it available for users. You can use the SQL*Plus STARTUP command, Oracle Enterprise Manager (Enterprise Manager), or the SRVCTL utility to perform these steps. Figure 13-3 shows how a database progresses from a shutdown state to an open state.

Figure 13-3 Instance and Database Startup Sequence

Description of "Figure 13-3 Instance and Database Startup Sequence"

A database goes through the following phases when it proceeds from a shutdown state to an open database state:

1. Instance started without mounting database

   The instance is started, but is not yet associated with a database.

   "How an Instance Is Started" explains this stage.

2. Database mounted

   The instance is started and is associated with a database by reading its control file (see "Overview of Control Files"). The database is closed to users.

   "How a Database Is Mounted" explains this stage.

3. Database open

   The instance is started and is associated with an open database. The data contained in the data files is accessible to authorized users.

   "How a Database Is Opened" explains this stage.

Overview of Database and Instance Shutdown

In a typical use case, you manually shut down the database, making it unavailable for users while you perform maintenance or other administrative tasks. You can use the SQL*Plus SHUTDOWN command or Enterprise Manager to perform these steps. Figure 13-4 shows the progression from an open state to a consistent shutdown.

Figure 13-4 Instance and Database Shutdown Sequence

Description of "Figure 13-4 Instance and Database Shutdown Sequence"

Oracle Database automatically performs the following steps whenever an open database is shut down consistently:

1. Database closed

   The database is mounted, but online data files and redo log files are closed.

   "How a Database Is Closed" explains this stage.

2. Database unmounted

   The instance is started, but is no longer associated with the control file of the database.

   "How a Database Is Unmounted" explains this stage.

3. Database instance shut down

   The database instance is no longer started.

   "How an Instance Is Shut Down" explains this stage.

Oracle Database does not go through all of the preceding steps in an instance failure or SHUTDOWN ABORT, which immediately terminates the instance.

Purpose of Checkpoints

Oracle Database uses checkpoints to achieve the following goals:

• Reduce the time required for recovery in case of an instance or media failure

• Ensure that dirty buffers in the buffer cache are written to disk regularly

• Ensure that all committed data is written to disk during a consistent shutdown

When Oracle Database Initiates Checkpoints

The checkpoint process (CKPT) is responsible for writing checkpoints to the data file headers and control file. Checkpoints occur in a variety of situations. For example, Oracle Database uses the following types of checkpoints:

• Thread checkpoints

  The database writes to disk all buffers modified by redo in a specific thread before a certain target. The set of thread checkpoints on all instances in a database is a database checkpoint. Thread checkpoints occur in the following situations:

  • Consistent database shutdown

  • ALTER SYSTEM CHECKPOINT statement

  • Online redo log switch

  • ALTER DATABASE BEGIN BACKUP statement

• Tablespace and data file checkpoints

  The database writes to disk all buffers modified by redo before a specific target. A tablespace checkpoint is a set of data file checkpoints, one for each data file in the tablespace. These checkpoints occur in a variety of situations, including making a tablespace read-only or taking it offline normal, shrinking a data file, or executing ALTER TABLESPACE BEGIN BACKUP.

• Incremental checkpoints

  An incremental checkpoint is a type of thread checkpoint partly intended to avoid writing large numbers of blocks at online redo log switches. DBWn checks at least every three seconds to determine whether it has work to do. When DBWn writes dirty buffers, it advances the checkpoint position, causing CKPT to write the checkpoint position to the control file, but not to the data file headers.

Other types of checkpoints include instance and media recovery checkpoints and checkpoints when schema objects are dropped or truncated.

Purpose of Instance Recovery

Instance recovery ensures that the database is in a consistent state after an instance failure. The files of a database can be left in an inconsistent state because of how Oracle Database manages database changes.

A redo thread is a record of all of the changes generated by an instance. A single-instance database has one thread of redo, whereas an Oracle RAC database has multiple redo threads, one for each database instance.

When a transaction is committed, log writer (LGWR) writes both the remaining redo entries in memory and the transaction SCN to the online redo log. However, the database writer (DBWn) process writes modified data blocks to the data files whenever it is most efficient. For this reason, uncommitted changes may temporarily exist in the data files while committed changes do not yet exist in the data files.

If an instance of an open database fails, either because of a SHUTDOWN ABORT statement or abnormal termination, then the following situations can result:

• Data blocks committed by a transaction are not written to the data files and appear only in the online redo log. These changes must be reapplied to the database.

• The data files contains changes that had not been committed when the instance failed. These changes must be rolled back to ensure transactional consistency.

Instance recovery uses only online redo log files and current online data files to synchronize the data files and ensure that they are consistent.

When Oracle Database Performs Instance Recovery

Whether instance recovery is required depends on the state of the redo threads. A redo thread is marked open in the control file when a database instance opens in read/write mode, and is marked closed when the instance is shut down consistently. If redo threads are marked open in the control file, but no live instances hold the thread enqueues corresponding to these threads, then the database requires instance recovery.

Oracle Database performs instance recovery automatically in the following situations:

• The database opens for the first time after the failure of a single-instance database or all instances of an Oracle RAC database. This form of instance recovery is also called crash recovery. Oracle Database recovers the online redo threads of the terminated instances together.

• Some but not all instances of an Oracle RAC database fail. Instance recovery is performed automatically by a surviving instance in the configuration.

The SMON background process performs instance recovery, applying online redo automatically. No user intervention is required.

Importance of Checkpoints for Instance Recovery

Instance recovery uses checkpoints to determine which changes must be applied to the data files. The checkpoint position guarantees that every committed change with an SCN lower than the checkpoint SCN is saved to the data files.

Figure 13-5 depicts the redo thread in the online redo log.

Figure 13-5 Checkpoint Position in Online Redo Log

Description of "Figure 13-5 Checkpoint Position in Online Redo Log"

During instance recovery, the database must apply the changes that occur between the checkpoint position and the end of the redo thread. As shown in Figure 13-5, some changes may already have been written to the data files. However, only changes with SCNs lower than the checkpoint position are guaranteed to be on disk.

Instance Recovery Phases

The first phase of instance recovery is called cache recovery or rolling forward, and involves reapplying all of the changes recorded in the online redo log to the data files. Because rollback data is recorded in the online redo log, rolling forward also regenerates the corresponding undo segments.

Rolling forward proceeds through as many online redo log files as necessary to bring the database forward in time. After rolling forward, the data blocks contain all committed changes recorded in the online redo log files. These files could also contain uncommitted changes that were either saved to the data files before the failure, or were recorded in the online redo log and introduced during cache recovery.

After the roll forward, any changes that were not committed must be undone. Oracle Database uses the checkpoint position, which guarantees that every committed change with an SCN lower than the checkpoint SCN is saved on disk. Oracle Database applies undo blocks to roll back uncommitted changes in data blocks that were written before the failure or introduced during cache recovery. This phase is called rolling back or transaction recovery.

Figure 13-6 illustrates rolling forward and rolling back, the two steps necessary to recover from database instance failure.

Figure 13-6 Basic Instance Recovery Steps: Rolling Forward and Rolling Back

Description of "Figure 13-6 Basic Instance 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 the SMON process to roll back terminated transactions, new transactions can roll back individual blocks themselves to obtain the required data.

Initialization Parameters

Initialization parameters are configuration parameters that affect the basic operation of an instance. The instance reads initialization parameters from a file at startup.

Oracle Database provides many initialization parameters to optimize its operation in diverse environments. Only a few of these parameters must be explicitly set because the default values are adequate in most cases.

Server Parameter Files

A server parameter file is a repository for initialization parameters that is managed by Oracle Database. A server parameter file has the following key characteristics:

• Only one server parameter file exists for a database. This file must reside on the database host.

• The server parameter file is written to and read by only by Oracle Database, not by client applications.

• The server parameter file is binary and cannot be modified by a text editor.

• Initialization parameters stored in the server parameter file are persistent. Any changes made to the parameters while a database instance is running can persist across instance shutdown and startup.

A server parameter file eliminates the need to maintain multiple text initialization parameter files for client applications. A server parameter file is initially built from a text initialization parameter file using the CREATE SPFILE statement. It can also be created directly by the Database Configuration Assistant.

Text Initialization Parameter Files

A text initialization parameter file is a text file that contains a list of initialization parameters. This type of parameter file, which is a legacy implementation of the parameter file, has the following key characteristics:

• When starting up or shutting down a database, the text initialization parameter file must reside on the same host as the client application that connects to the database.

• A text initialization parameter file is text-based, not binary.

• Oracle Database can read but not write to the text initialization parameter file. To change the parameter values you must manually alter the file with a text editor.

• Changes to initialization parameter values by ALTER SYSTEM are only in effect for the current instance. You must manually update the text initialization parameter file and restart the instance for the changes to be known.

The text initialization parameter file contains a series of key=value pairs, one per line. For example, a portion of an initialization parameter file could look as follows:

db_name=sample
control_files=/disk1/oradata/sample_cf.dbf
db_block_size=8192
open_cursors=52
undo_management=auto
shared_pool_size=280M
pga_aggregate_target=29M
.
.
.

To illustrate the manageability problems that text parameter files can create, assume that you use computers clienta and clientb and must be able to start the database with SQL*Plus on either computer. In this case, two separate text initialization parameter files must exist, one on each computer, as shown in Figure 13-7. A server parameter file solves the problem of the proliferation of parameter files.

Figure 13-7 Multiple Initialization Parameter Files

Description of "Figure 13-7 Multiple Initialization Parameter Files"

Modification of Initialization Parameter Values

You can adjust initialization parameters to modify the behavior of a database. The classification of parameters as static or dynamic determines how they can be modified. Table 13-3 summarizes the differences.

Static parameters include DB_BLOCK_SIZE, DB_NAME, and COMPATIBLE. Dynamic parameters are grouped into session-level parameters, which affect only the current user session, and system-level parameters, which affect the database and all sessions. For example, MEMORY_TARGET is a system-level parameter, while NLS_DATE_FORMAT is a session-level parameter (see "Locale-Specific Settings").

The scope of a parameter change depends on when the change takes effect. When an instance has been started with a server parameter file, you can use the ALTER SYSTEM SET statement to change values for system-level parameters as follows:

• SCOPE=MEMORY

  Changes apply to the database instance only. The change will not persist if the database is shut down and restarted.

• SCOPE=SPFILE

  Changes are written to the server parameter file but do not affect the current instance. Thus, the changes do not take effect until the instance is restarted.

  Note:

  You must specify SPFILE when changing the value of a parameter described as not modifiable in Oracle Database Reference.

• SCOPE=BOTH

  Changes are written both to memory and to the server parameter file. This is the default scope when the database is using a server parameter file.

The database prints the new value and the old value of an initialization parameter to the alert log. As a preventative measure, the database validates changes of basic parameter to prevent illegal values from being written to the server parameter file.

Automatic Diagnostic Repository

Automatic Diagnostic Repository (ADR) is a file-based repository that stores database diagnostic data such as trace files, the alert log, and Health Monitor reports. Key characteristics of ADR include:

• Unified directory structure

• Consistent diagnostic data formats

• Unified tool set

The preceding characteristics enable customers and Oracle Support to correlate and analyze diagnostic data across multiple Oracle instances, components, and products.

ADR is located outside the database, which enables Oracle Database to access and manage ADR when the physical database is unavailable. An instance can create ADR before a database has been created.

ADR Structure

The ADR base is the ADR root directory. The ADR base can contain multiple ADR homes, where each ADR home is the root directory for all diagnostic data—traces, dumps, the alert log, and so on—for an instance of an Oracle product or component. For example, in an Oracle RAC environment with shared storage and ASM, each database instance and each ASM instance has its own ADR home.

Figure 13-8 illustrates the ADR directory hierarchy for a database instance. Other ADR homes for other Oracle products or components, such as ASM or Oracle Net Services, can exist within this hierarchy, under the same ADR base.

Figure 13-8 ADR Directory Structure for an Oracle Database Instance

Description of "Figure 13-8 ADR Directory Structure for an Oracle Database Instance"

As the following Linux example shows, when you start an instance with a unique SID and database name before creating a database, Oracle Database creates ADR by default as a directory structure in the host file system. The SID and database name form part of the path name for files in the ADR Home.

Example 13-1 Creation of ADR

% setenv ORACLE_SID osi
% echo "DB_NAME=dbn" > init.ora
% sqlplus / as sysdba
.
.
. 
Connected to an idle instance.
 
SQL> STARTUP NOMOUNT PFILE="./init.ora"
ORACLE instance started.
 
Total System Global Area  146472960 bytes
Fixed Size                  1317424 bytes
Variable Size              92276176 bytes
Database Buffers           50331648 bytes
Redo Buffers                2547712 bytes
 
SQL> SELECT NAME, VALUE FROM V$DIAG_INFO;
 
NAME                  VALUE
--------------------- --------------------------------------------------
Diag Enabled          TRUE 
ADR Base              /u01/oracle/log
ADR Home              /u01/oracle/log/diag/rdbms/dbn/osi
Diag Trace            /u01/oracle/log/diag/rdbms/dbn/osi/trace
Diag Alert            /u01/oracle/log/diag/rdbms/dbn/osi/alert
Diag Incident         /u01/oracle/log/diag/rdbms/dbn/osi/incident
Diag Cdump            /u01/oracle/log/diag/rdbms/dbn/osi/cdump
Health Monitor        /u01/oracle/log/diag/rdbms/dbn/osi/hm
Default Trace File    /u01/oracle/log/diag/rdbms/dbn/osi/trace/osi_ora_10533.trc 
Active Problem Count  0
Active Incident Count 0

The following sections describe the contents of ADR.

Alert Log

Each database has an alert log, which is an XML file containing a chronological log of database messages and errors. The alert log contents include the following:

• All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60)

• Administrative operations such as DDL statements and the SQL*Plus commands STARTUP, SHUTDOWN, ARCHIVE LOG, and RECOVER

• Several messages and errors relating to the functions of shared server and dispatcher processes

• Errors during the automatic refresh of a materialized view

Oracle Database uses the alert log as an alternative to displaying information in the Enterprise Manager GUI. If an administrative operation is successful, then Oracle Database writes a message to the alert log as "completed" along with a time stamp.

Oracle Database creates an alert log in the alert subdirectory shown in Figure 13-8 when you first start a database instance, even if no database has been created yet. The following example shows a portion of a text-only alert log:

Fri Jun 19 17:05:34 2009
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 2
Autotune of undo retention is turned on.
IMODE=BR
ILAT =12
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.2.0.0.0.
Using parameter settings in client-side pfile 
.
.
.
System parameters with nondefault values:
  db_name                  = "my_test"
Fri Jun 19 17:05:37 2009
PMON started with pid=2, OS id=10329
Fri Jun 19 17:05:37 2009
VKTM started with pid=3, OS id=10331 at elevated priority
VKTM running at (20)ms precision
Fri Jun 19 17:05:37 2009
DIAG started with pid=4, OS id=10335

As shown in Example 13-1, query V$DIAG_INFO to locate the alert log.

Trace Files

A trace file is an administrative file that contain diagnostic data used to investigate problems. Also, trace files can provide guidance for tuning applications or an instance, as explained in "Performance Diagnostics and Tuning".