| Oracle® Database 2 Day DBA 10g Release 2 (10.2) Part Number B14196-02 |
|
|
View PDF |
| Oracle® Database 2 Day DBA 10g Release 2 (10.2) Part Number B14196-02 |
|
|
View PDF |
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:
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.
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 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:
|
|
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. |
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 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". |
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.
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.
The process of starting the instance and database is as follows:
Start the instance using one of the following methods:
Use Oracle Enterprise Manager. See "Shutting Down and Restarting the Instance and Database with Enterprise Manager".
Use the SQL*Plus STARTUP statement. See Oracle Database Administrator's Guide.
On Windows, you can start the Oracle services. See "Starting and Shutting Down the Database Instance on Windows".
Oracle reads the initialization parameter file, allocates SGA memory, and starts the background processes for the instance.
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.
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.
To shut down the database, reverse the start up procedure:
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.
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.
The Oracle instance stops the background processes of the instance and deallocates the shared memory used by the SGA.
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.
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.
