Managing the Oracle 10g instance

Learning objective

After completing this topic, you should be able to recognize the characteristics of the Oracle instance and its management interfaces.

1. Identifying basic memory structures

In each Oracle database server, there is an Oracle instance and an Oracle database.

The Oracle instance does not exist until it is started and the parameter file is read. The instructions about the configuration of the instance are set out in the parameter file.

When the instance is started, the database is opened and the user can access the data.

Two memory structures form an Oracle instance.

• System Global Area (SGA)
• Program Global Area (PGA)

System Global Area (SGA)

The System Global Area (SGA) is an area of memory shared by all server and background processes. This is where the Oracle instance holds data in buffers, memory caches and pools to be shared among the various processes.

If multiple users are connected to the same instance concurrently, the data in the instance's SGA is shared among the users.

This provides fast, repeat access to data without having to read the hard disk each time.

The SGA contains several data structures:

Before any data stored in the database can be queried or modified, it must be read from disk and stored in memory. The database buffer cache is used to store this data.

The redo log buffer provides a short-term store of data to be used in instance recovery. It maintains a record of data before it can be written to the physical redo log files stored on disk.

The shared pool stores constructs that can be accessed by multiple users.

The large pool is an optional area that you can create to provide a temporary store for large I/O processing requests.

The Java pool is used to store all Java code and data for a particular session of the Java Virtual Machine (JVM).

The streams pool is used by Oracle Streams to manage data, transactions, and events in a data stream.

The SGA has the ability to dynamically change the size of the shared pool, the Java pool, the database buffer cache, the streams pool, and the large pool - without needing to shut down the instance.

Although the memory parameters of the database are preconfigured, you may want to alter them as the needs of your database increase.

Oracle helps you determine the appropriate values for memory parameters by providing alerts and advisors that spot memory sizing problems when they occur.

The memory allocated for the SGA is displayed in Enterprise Manager or SQL*Plus when you start the instance.

Program Global Area (PGA)

The Program Global Area (PGA) is an area of memory, private to each server and background process, that contains data and control information for that one process. When a server process is started, its private PGA is created.

The PGA can only be accessed by its server process and is read and written by Oracle code acting on its behalf.

The PGA usually contains

The private SQL area stores the bind information and runtime memory structures. There is a private SQL area for every session that issues a SQL statement.

The session memory is memory made available for session variables and other session-related information.

How much PGA memory is used and what it contains depend on whether the instance is available in shared server mode.

Question

Match the memory structures associated with an Oracle instance to the appropriate characteristics.

Options:

1. It contains a private SQL area
2. It contains session information
3. It includes a database buffer cache and a redo log buffer.
4. It's where the Oracle instance holds data in buffers, memory caches, and pools.

Targets:

1. SGA
2. PGA

Answer

The SGA is where the Oracle instance holds data in buffers, memory caches, and pools and it includes a database buffer cache and a redo log buffer. The PGA contains a private SQL area and session information.

2. Recognizing Oracle processes

Every time you use an application or an Oracle tool, such as Enterprise Manager, a server process is initiated by the Oracle server to service the request.

The instance also starts a number of background processes to manage the memory structure, interact with the operating system, write data to disk, and complete general housekeeping tasks.

Which background processes are invoked depends on the features used in the database.

The background processes most often used include

• system monitor (SMON)
• process monitor (PMON)
  
• database writer (DBWn)
• checkpoint (CKPT)
• log writer (LGWR)
• archiver (ARCn)

system monitor (SMON)

When the instance is started after a system failure, the system monitor will perform the crash recovery.

process monitor (PMON)

When a user process fails, the process cleanup is performed by the process monitor.

database writer (DBWn)

The database writer is used to write the changed data from the database buffer cache to the long-term store on the hard disk.

checkpoint (CKPT)

The checkpoint tells the database writer about data changes. It also updates the database's data files and control files to indicate the most recent checkpoint.

log writer (LGWR)

The log writer writes redo log entries to the hard disk.

archiver (ARCn)

When the log files are full or a log switch occurs, the archiver moves the redo log files to archival storage.

Question

Match the background process to the appropriate description.

Options:

1. Checkpoint
2. Database writer
3. Process monitor
4. System monitor

Targets:

1. Performs crash recovery
2. Performs the process cleanup
3. Writes the modified data to the files on hard disk
4. Tells the database writer about data changes

Answer

The system monitor performs crash recovery and the process monitor performs the process cleanup when a user process fails. The database writer writes the changed data from the database buffer cache, and the checkpoint tells the database writer about data changes.

The checkpoint alerts the database writer about database changes. It also updates data files and control files to indicate the most recent checkpoint.

The database writer writes the changed data from the database buffer cache to the disk files.

The process monitor performs the process cleanup when a user process fails.

The system monitor performs crash recovery after a system failure and when the instance has started.

The data dictionary is a read-only reference containing information about the sets of tables and views in a particular database.

The information in a data dictionary includes

• the allocated space for a schema object and the amount currently in use
• the database's physical and logical structure
• the integrity constraints imposed
• a list of valid users

When you create a database, you also create a data dictionary. Similarly, when the structure of the database is modified and updated, the data dictionary is automatically updated.

Enterprise Manager uses the data dictionary to get information about objects in the database.

You can also get information from the data dictionary tables using the DICTIONARY view in Enterprise Manager. This view provides useful descriptions of the data dictionary tables and views.

The names of data dictionary's tables and views are generally appended to one of three prefixes:

• USER
• ALL
• DBA

USER

The USER prefix indicates that the data relates to those objects owned by the current user.

ALL

The ALL prefix indicates that the data relates to those objects that are accessible to the current user.

DBA

The DBA prefix indicates that the data relates to every object in the database.

Question

Which of these are characteristics of the data dictionary?

Options:

1. It is created when a database is created
2. It is updated when the database is modified
3. It is used by Enterprise Manager for getting data on objects
4. It performs process cleanup when a user process fails

Answer

A data dictionary is created when a database is created and is updated when the database is. Enterprise Manager uses the data dictionary to get data on objects.

Option 1 is correct. When you create a database, a data dictionary is also created. Similarly, when the database is updated, the data dictionary is too.

Option 2 is correct. When modifications are made to the database and it's updated, the data dictionary is also updated.

Option 3 is correct. The DICTIONARY view in Enterprise Manager also provides useful descriptions of the data dictionary tables and views.

Option 4 is incorrect. The process monitor performs process cleanup when a user process fails. This is a background process.

3. Understanding database control and grid control

Oracle Enterprise Manager's Database Control is a web-enabled control console that comes packaged with every Oracle Database 10g.

As a database administrator, you typically use Database Control to monitor and manage your database.

However, if your needs are greater, you can use Enterprise Manager's Grid Control to expand and integrate Database Control's capabilities with the rest of your system.

You use Database control to

• manage alerts
• administer database objects and users
• manage storage
• monitor performance
• control advisors and maintenance wizards
• perform database backup and recovery

The Grid Control framework is very flexible and has a high level of functionality.

This allows you to customize Enterprise Manager to suit the management and monitoring needs of your environment.

The typical Enterprise Manager configuration consists of several functional areas:

• database control
• managed targets
• application server control
• management services
• web-based grid control

Question

Match the controls to the appropriate characteristic.

Options:

1. It includes database control and application server control
2. Its framework is very flexible and has high functionality
3. It's used to monitor the performance of a single database
4. It's used to administer a single database's database objects and users

Targets:

1. Database Control
2. Grid Control

Answer

Database Control is used to monitor the performance of a single database and to administer its database objects and users. Grid Control includes database control and application server control and its framework is very flexible and has high functionality.

Summary

Each Oracle Database 10g consists of a database and an instance. The instance is idle until it is started, when it reads the parameter file to get its configuration. The basic memory structures associated with the instance are the System Global Area (SGA) and the Program Global Area (PGA).

Invoking a tool such as Enterprise Manager creates a server process to complete the request. It also initiates several background processes. The most common background processes are System Monitor (SMON), Process Monitor (PMON), Database Writer (DBWn), Checkpoint (CKPT), Log Writer (LGWR), and Archiver(ARCn). Enterprise Manager uses the Data Dictionary to view objects such as tables and views.

Every Oracle Database 10g comes packaged with Enterprise Manager Database Control. This is used by the administrator to manage and monitor the database. The capabilities of Database Control can be extended using Grid Control.