| Oracle® Database 2 Day DBA 10g Release 2 (10.2) Part Number B14196-01 |
|
|
View PDF |
| Oracle® Database 2 Day DBA 10g Release 2 (10.2) Part Number B14196-01 |
|
|
View PDF |
This chapter discusses the Oracle instance and database.
The following topics are covered:
Your Oracle database is comprised of a set of operating system files containing data entered by users or applications and structural information about the database itself called database metadata. Information is stored persistently in these files.
In order for you to view or update the data contained in the database, Oracle needs to start a set of processes, called background processes, and needs to allocate some memory to be used during database operation. The background processes and memory allocated by Oracle together make up an instance. Therefore before the database can be used, the database instance must be started. 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.
The Oracle instance and the Oracle database are separate entities, although the term instance is often used to mean one or the other or both. Technically, they are distinguished as follows:
An Oracle instance consists of the shared memory structures and background processes that run the Oracle database. You can have an instance without a database (for example, when you have not yet created a database), and if a database exists, it can be open or not.
A Oracle database refers to the physical and logical components of a specific database, and its operation.
This section presents some of the concepts of an instance and its functioning. It is intended that these concepts can provide a basis for understanding instance 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
This, the preferred form of initialization parameter file, is contained in 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 is persistent 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.
This section briefly describes the memory structures of an instance. The size of these structures affects the performance of the Oracle database server and is controlled by initialization parameters. These initialization parameters can be categorized as memory parameters.
When a database is created with DBCA, the memory parameters are automatically set to optimal values based on your specification of the database workload, such as Data Warehouse, General Purpose, or Transaction Processing. However, as your database usage expands, you might find it necessary to 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 (controlled by Oracle) and information stored in the SGA can avoid repeated access from physical disk, a time consuming operation.
For optimal performance, the SGA should be large enough to avoid frequent disk reads and writes.
The SGA has several subcomponents as listed in the following table:
| 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. The buffer cache is the component of the SGA that acts as the buffer to store any data being queried or modified. 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. Some examples:
|
| Redo Log Buffer | This buffer improves performance by caching redo information (used for instance recovery) until it can be written at once and at a more opportune time to the physical redo log files that are stored on disk. Redo information and redo log files are discussed in "Online Redo Log Files". |
| Large Pool | This is an optional area that is used for buffering 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 private PGA area that is a nonshared area of memory created by Oracle when a server 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 that interact with each other and with the operating system to manage memory structure, asynchronously perform I/O to write data to disk, and do general housekeeping. The background processes consolidate functions that would otherwise be handled by multiple Oracle programs running for each user process. They asynchronously perform I/O and monitor other Oracle processes to provide increased parallelism for better performance and reliability.
There are many background processes and not all may be present depending upon the features that are being used in the database. The most common background processes, and ones that most directly affect you, are the following:
| 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 is responsible for signalling DBWn at checkpoints and updating all of the datafiles and control files of the database to indicate the most recent checkpoint. |
| System Monitor (SMON) | The system monitor performs crash recovery when a failed instance starts up again. |
| Process Monitor (PMON) | The process monitor performs process recovery when a user process fails. It is responsible for cleaning up the cache and freeing resources that the failed process was using. |
| Archiver (ARCn) | When the database is running in archive log mode, one or more archiver processes copy the redo log files to archival storage when the log files are full or a log switch occurs. For more information, see Chapter 9, "Performing Backup and Recovery". |
The database instance can be started (made available) only by an authorized database administrator who has a special type of connection privilege to the Oracle instance. After the database instance has been started, it is usually open for access by normal users for whom database user accounts have been created. Later, you will learn how to start up and shut down an Oracle instance and database in "Shutting Down and Restarting the Instance and Database".
This section describes the process of starting the Oracle instance and database and the creation of server processes that enable user access.
The administrator who starts up the Oracle instance and database must connect to the instance with a special kind of 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 to use these privileges occurs either through the operating system or through 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 granted to them, but only user SYS can initially connect with the SYSDBA privilege.
The process of starting the instance and database is as follows:
You start the instance in one of the following ways:
Use Oracle Enterprise Manager. See "Shutting Down and Restarting the Instance and Database".
Use the STARTUP statement using SQL*Plus. 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 also specify that the database is to be mounted, 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 specify that the database is to be opened, 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.
The process is reversed when you shut down the database.
You issue the statement to shut down the database by similar means as explained in "The Startup Process". Any modified data blocks that are cached in the SGA that have not been written to disk are now written to disk. The redo log buffer is similarly 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 requests of user or client processes that connect to the instance. 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
In some situations when the client and Oracle operate on the same machine, it is possible to 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.
Shared server mode eliminates the need for a dedicated server process for each connection. A dispatcher directs multiple incoming network session requests to a pool of shared server processes. An idle shared server process from a shared pool of server processes 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. Also, 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.
If you need to shut down and later restart your instance or database, you can do this by clicking the Shutdown or Startup button, under the General heading on the Database Home page as shown in Figure 5-1, "Shutdown/Startup Button on Home Page".
The first page to appear is Startup/Shutdown: Specify Host and Target Database Credentials. This page requires you to log in to the machine that Oracle is running on, and into the database itself with SYSDBA or SYSOPER privileges. The next screen enables you to shut down the database, or start up the database, specifying options.
You can start and shut down your Oracle database with the Windows Services program. To start Oracle, you must start the following services:
OracleServiceSID, which is your Oracle Database instance
OracleORACLE_HOMETNSListener, which is your listener; required to allow clients to connect to your database
OracleDBConsoleSID, which enables clients to connect to Enterprise Manager
In the preceding service names, SID refers to the system identifier for the instance and ORACLE_HOME refers to the Oracle home.
To start these services:
Click the Start button and then select Settings, Control Panel, Administrative Tools, and Services.
A list of all available services on your system appears.
Locate the Oracle services listed at the beginning of this section. For example, if your SID is orcl, then locate the following services:
OracleServiceORCL
OracleOraDb10g_home1TNSListener
OracleDBConsoleorcl
Start all three services. You can use the following techniques:
Right-clicking the service and select Start.
In the Action menu, select Start.
Double-click the service to open a dialog box and then click Start.
To shut down the database, select or click Stop instead of Start.
When you install one of the preconfigured databases provided by Oracle, the initialization parameters are optimized for normal use in the environment that you specified. It is not necessary for you to alter any initialization parameters at this time. The steps described in this section will familiarize you with the initial parameter setting for your database and indicate how to modify parameters.
To view or modify the initialization parameters for your database:
From the Database Home page, click the Administration property page. The Administration home page appears as shown in Figure 5-2.
Click All Initialization Parameters under the Database Configuration heading.
Enterprise Manager displays the Initialization Parameters page. This page lists the current value of each initialization parameter as seen by the database instance. You can also use this page to alter initialization parameter values, using the Show SQL, Revert, and Apply buttons.
There are two property pages shown on the Initialization Parameters page:
Current—The table on this property page displays all of the initialization parameter values currently seen by the database instance (in memory).
You can use this page to make dynamic changes to parameters in the current running instance. Only parameters marked dynamic can be changed. To do so, enter a new value and click Apply.
SPFile—This property page is present when you are using a server parameter file, whose location is displayed a the top of the table. This property page shows parameter settings in the server parameter file.
Changes to parameter settings in this file are persistent across instance startup and shutdown. To make persistent changes to an initialization parameter, enter the new value and click Apply. You can optionally apply changes to the current running instance by checking Apply changes in SPFile to the current running instance. If you do not check this box, your changes will not take effect until the database is shut down and restarted.
Parameters marked as Basic represent a small subset of all initialization parameters, and are considered necessary to keeping the database running smoothly.
As the number of database users grows and workload increases, you might need to alter some initialization parameters. You can make these changes directly using the Initialization Parameter page as described, or indirectly using one of the advisors provided by Oracle. One such advisor, the memory advisor, is described in "Managing Memory Parameters".
Some initialization parameters, referred to here as memory parameters, determine the total size of the system global area (SGA) and the program global area (PGA), and of the subcomponents of the SGA. The settings of memory parameters can affect the performance of your database. When you install your database, these parameters are tuned to meet the requirements of the environment that you specify.
If you enabled Automatic Shared Memory Configuration when you configured your database, Oracle automatically sizes the subcomponents of the SGA, which include the shared pool and buffer cache. Oracle recommends that you enable memory auto tuning.
If Automatic Shared Memory Management is disabled, you can enable it on the Memory Parameters page. You can navigate to this page from the Administration page by clicking Memory Parameters under the Instance heading. From this page you can enable or disable Automatic Shared Memory Management and view your SGA and PGA memory settings from their property pages.
To modify the size of an SGA subcomponent without shutting down your instance, you can disable Automatic Shared Memory Management on the Memory Parameters page. With the Advice button on the Memory Parameters page, the Memory Advisor (discussed in "Using the Memory Advisor" in Chapter 10, "Monitoring and Tuning the Database") gives you advice on optimal memory settings. If you choose to modify your memory settings manually, you can enter new sizes and apply changes dynamically while the instance is up. You can also do so from the Current property page on the Initialization Parameters page. However, there are some restrictions on dynamic modification of memory parameters. For more information, see Oracle Database Administrator's Guide.
To make changes to memory parameters persistent across instance startup and shutdown, you must alter your parameter file. You can do so by navigating to SPFile property page from the Memory Parameters page and making your changes there.
|
See Also: Oracle Database Performance Tuning Guide for more information about memory parameters and their tuning |
Oracle by Example (OBE) has a series on the Oracle Database 2 Day DBA book. This OBE steps you through the tasks in this chapter, and includes annotated screen shots.
To view the Instances OBE, point your browser to the following location:
http://www.oracle.com/technology/obe/10gr2_2day_dba/instance/instance.htm
