Skip Headers
Oracle® Database 2 Day DBA
10g Release 2 (10.2)

Part Number B14196-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

Overview of an Instance and Instance Management

The Oracle database is made of a set of operating system files containing data entered by users or applications and structural information about the database itself called metadata. Information is stored persistently in these files.

To enable users and applications to view or update data in the database, Oracle must start a set of processes, called background processes, and must allocate some memory to be used during database operation. The background processes and memory allocated by Oracle together make up an instance. An instance must be started to read and write information to the database. However, having a database is not necessary to run an instance.

When the database instance is not available, your data is safe in the database but it cannot be accessed by any user or application.

The properties of a database instance are specified using instance initialization parameters. When the instance is started, an initialization parameter file is read and the instance is configured accordingly.

This section presents some of the concepts of an instance and its management. The following topics are discussed:

About Initialization Parameters

Instance management involves configuring parameters that affect the basic operation of the database instance. These parameters are called initialization parameters. The Oracle database server reads these parameters at database startup and monitors them while the database is running. They are stored in memory, where many of them can be changed dynamically. There are two types of parameter files, and whether these dynamic changes are persistent across database shutdown and startup depends upon the type of parameter file you are using.

Server Parameter File

The server parameter file is the preferred form of initialization parameter file, and is a binary file that can be written to and read by the database server. It must not be edited manually. It resides on the machine that Oracle is running on, and changes to it persist across shutdown and startup.

Text Initialization Parameter File

This type of initialization parameter file can be read by the database server, but it is not written to by the server. In this file, you can set initialization parameters with a text editor for them to be persistent across shutdown and startup.

Instance Memory Structure

The size of the instance memory structures affects the performance of the Oracle database server and is controlled by initialization parameters.

When a database is created with DBCA, the memory parameters are automatically set to optimal values based on the database workload, such as data warehouse, general purpose, or transaction processing. However, as your database usage expands, you can alter the settings of the memory parameters.

Oracle provides alerts and advisors to identify memory sizing problems and to help you determine appropriate values for memory parameters.

The System Global Area (SGA)

The SGA is a shared memory area that contains data and control information for the instance. Multiple users can share data within this memory area and avoid repeated, time-consuming access from physical disk. For optimal performance, the SGA should be large enough to avoid frequent disk reads and writes.

The SGA has several subcomponents as listed in:

Table 5-1 SGA Components

Component Description

Buffer Cache

Before any data stored in the database can be queried or modified, it must be read from disk and stored in memory in the buffer cache. All user processes connected to the database share access to the buffer cache.

Shared Pool

The shared pool caches information that can be shared among users:

  • SQL statements that can be reused

  • Information from the data dictionary such as user account data, table and index descriptions, and privileges

  • Stored procedures, which is executable code that is stored in the database

Redo Log Buffer

This buffer improves performance by caching redo information which is used for instance recovery until it can be written once at a more opportune time to the physical redo log files stored on disk. Redo information and redo log files are discussed in "Online Redo Log Files".

Large Pool

This optional area is used to buffer large I/O requests for various server processes.

Java Pool

The Java pool memory is used for all session-specific Java code and data within the Java Virtual Machine (JVM)

Streams Pool

The Streams pool is used by the Oracle Streams product.


Program Global Area (PGA)

A program global area (PGA) is a memory area used by a single Oracle server process. A server process is a process that services a client's requests. Each server process has its own nonshared PGA when the process is started.

The PGA is used to process SQL statements and to hold logon and other session information.

The amount of PGA memory used and its content depends on the instance configuration, that is, whether the instance is running in dedicated server or shared server mode.

Oracle Background Processes

Oracle creates a set of background processes for an instance to manage memory structure, asynchronously perform I/O to write data to disk, and do general maintenance tasks. The background processes consolidate functions that would otherwise be handled by multiple Oracle programs running for each user process. They monitor other Oracle processes to provide increased parallelism for better performance and reliability.

The background processes that are present depend on the features that are being used in the database. The most common background processes, and ones that most directly affect you, are described in Table 5-2, "Oracle Background Processes".

Table 5-2 Oracle Background Processes

Background Process Description

Database Writer (DBWn)

The database writer writes modified blocks from the database buffer cache to the files on disk. Oracle allows a maximum of 20 database writer processes.

Log Writer (LGWR)

The log writer process writes redo log entries to disk. Redo log entries are generated in the redo log buffer of the SGA and the log writer process writes the redo log entries sequentially into an online redo log file.

Checkpoint

At specific times, all modified database buffers in the SGA are written to the datafiles by a database writer process (DBWn). This event is called a checkpoint. The checkpoint process signals DBWn, updates all of the datafiles and control files of the database, and records the time of this update.

System Monitor (SMON)

The system monitor performs crash recovery when a failed instance starts up again.

Process Monitor (PMON)

The process monitor performs recovery when a user process fails. It cleans up the cache and frees resources that the failed process was using.

Archiver (ARCn)

Archiver processes copy the redo log files to archival storage when the log files are full or a log switch occurs. The database must be in archive log mode to run archive process. For more information, see Chapter 9, "Performing Backup and Recovery".


Accessing the Database

The database instance can be started only by an authorized database administrator with a special connection privilege to the Oracle instance. After the database instance has been started, it is usually open for access by users with database accounts.

About Database Administration Privileges

The administrator who starts the Oracle instance and database must connect to the instance with a special connection privilege. There are two of these privileges: SYSDBA for fully empowered database administrators and SYSOPER for users who operate the database, but have no privileges to access user objects. Authorization for these privileges is granted either by the operating system or by a special password file.

When you create an Oracle database, there are two primary administrative user accounts that are automatically created: SYS and SYSTEM. Both of these users have full database administration privileges, but only user SYS can initially connect with the SYSDBA privilege.

Starting the Oracle Database

The process of starting the instance and database is as follows:

  1. Start the instance using one of the following methods:

    Oracle reads the initialization parameter file, allocates SGA memory, and starts the background processes for the instance.

  2. If you choose to mount the database, then the instance opens the database control file. The database is now said to be in the mount state. This state enables administrators to perform certain administrative functions that cannot be performed when other users are accessing the database.

  3. If you choose to open the database, then the instance opens the redo log files and datafiles for the database. The database is now open and available for all user access.

    The default startup behavior is to complete the three stages as described earlier. Unless you explicitly specified otherwise, the instance will be started, and the database mounted and opened.

Shutting Down the Oracle Database

To shut down the database, reverse the start up procedure:

  1. Shut down the database using one of the methods in "Starting the Oracle Database". Any modified data blocks cached in the SGA that have not been written to disk are now written to disk. The redo log buffer is flushed. Datafiles are checkpointed and their headers are marked current as of the time the database was closed. Datafiles and log files are closed. Users can no longer access the database.

  2. The Oracle instance dismounts the database and updates relevant entries in the control file to record a clean shutdown. The control file is closed. The database is now closed and only the instance remains.

  3. The Oracle instance stops the background processes of the instance and deallocates the shared memory used by the SGA.

Server and Client Processes

In addition to background processes, Oracle creates server processes that handle the connection requests of user or client processes. A user connection is composed of two distinct pieces:

  • A client program acting on behalf of the user, such as Oracle Enterprise Manager, SQL*Plus, or an application

  • A server process that handles the connection to the database on behalf of the client program

When the client and Oracle operate on the same machine, you can combine the user process and corresponding server process into a single process to reduce system overhead. However, when the client and Oracle operate on different machines, a user process always communicates with Oracle through a separate server process.

Server processes can be either dedicated or shared. In dedicated server mode, each client process has its own server process. While a dedicated server process is good for long running queries and administrative tasks, an idle process or too many dedicated processes can result in an inefficient use of resources.

Using shared server mode eliminates the need for a dedicated server process for each connection. A dispatcher directs incoming network session requests to a pool of shared server processes. An idle shared server process picks up a request from a common queue, which means a small number of shared servers can perform the same amount of processing as many dedicated servers. Because the amount of memory required for each user is relatively small, less memory and process management are required, and more users can be supported.

Shared server mode is more efficient at supporting multiple users and clients making frequent short-running queries.

Network Connections

Oracle Net is a layer of software that allows different physical machines to communicate to access an Oracle database. Oracle Net runs on the client machine and on the database server. The client machine and the database server are often the same machine.

Oracle Net enables the client and server to communicate over a network using many popular network protocols, and it provides location transparency such that the client machine does not need to know the server's location. When the database is moved to another location, you only need to reconfigure Oracle Net. No changes are necessary to client applications.

Oracle Net must be separately configured and started for it to handle client connections to the database. You can configure Oracle Net using Oracle Enterprise Manager or with a separately launched GUI tool called the Oracle Net Manager. To learn more, see Chapter 4, "Configuring the Network Environment".

When an instance starts, a listener process establishes a communication pathway to Oracle. When a user process makes a connection request, the listener determines whether it should use a shared server dispatcher process or a dedicated server process and establishes an appropriate connection.