| Oracle® Database 2 Day DBA 10g Release 2 (10.2) Part Number B14196-03 |
|
|
PDF · Mobi · ePub |
| Oracle® Database 2 Day DBA 10g Release 2 (10.2) Part Number B14196-03 |
|
|
PDF · Mobi · ePub |
This chapter discusses the Oracle instance and database.
The following topics are covered:
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.
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:
| 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.
See Also:
"Managing Memory Parameters"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.
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 Database Home Page".
Figure 5-1 Shutdown/Startup Button on Database 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 for 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 Start 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-click the service and 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, 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. The steps described in this section will familiarize you with viewing the initial parameter settings for your database and modifying 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.
Under Database Configuration, click All Initialization Parameters.
Enterprise Manager displays the Initialization Parameters page, where you can view the value of each initialization parameter for the database instance. You can 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—This page displays all of the initialization parameter values that are currently in memory for the database instance.
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 Basic represent a small subset of all initialization parameters, and are needed to keep 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 using the Initialization Parameter page as described, or using one of the advisors provided by Oracle. One such advisor, the memory advisor, is described in "Managing Memory Parameters".
Memory parameters are initialization parameters that 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 automatic memory tuning as such.
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 modify memory parameters 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 tuningOracle 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/webfolder/technetwork/tutorials/obe/db/10g/r2/2day_dba/instance/instance.htm
|
 Copyright © 2004, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
