1.3. The Oracle Architecture
Each interface described in the previous section allows a user to interact with the database. Using these tools requires that user accounts be created in the database and connectivity to the database be in place across the network. Users must also have adequate storage capacity for the data that they insert, and they need recovery mechanisms for restoring the transactions that they are performing in the event of a hardware failure. As the DBA, you take care of each of these tasks, as well as others, which include the following:
Selecting the server hardware on which the database software will run
Installing and configuring the Oracle 10g software on the server hardware
Creating the database itself
Creating and managing the tables and other objects used to manage the application data
Creating and managing database users
Establishing reliable backup and recovery processes for the database
Monitoring and tuning database performance
The remainder of this book is dedicated to helping you understand how to perform these and other important Oracle database administration tasks. But, first, to succeed as an Oracle DBA, you need to completely understand Oracle's underlying architecture and its mechanisms. Understanding the relationship between Oracle's memory structures, background processes, and I/O activities is critical before learning how to manage these areas.
The Oracle Server architecture can be described in three categories:
User-related processes
Logical memory structures that are collectively called an Oracle instance
Physical file structures that are collectively called a database
Figure 1.11 shows all the parts of an Oracle instance and database.
The architecture in Figure 1.11 may at first seem complex. However, each of these architecture components is described in more detail in the following sections, beginning with the userrelated processes.
NOTE
Taken together, the instance and the database are called an Oracle Server.
1.3.1. User Processes
At the user level, two processes allow a user to interact with the instance and, ultimately, with the database: the User Process and the Server Process.
Whenever a user runs an application, such as a human resources or order-taking application, Oracle starts a User Process to support the user's connection to the instance. Depending on the technical architecture of the application, the User Process exists either on the user's own PC or on the middle-tier application server. The User Process then initiates a connection to the instance. Oracle calls the process of initiating and maintaining communication between the User Process and the instance a connection. Once the connection is made, the user establishes a session in the instance.
After establishing a session, each user then starts a Server Process on the host server itself. It is this Server Process that is responsible for performing the tasks that actually allow the user to interact with the database.
Figure 1.11. The Oracle 10g architecture
Examples of these interactions include sending SQL statements to the database, retrieving needed data from the database's physical files, and returning that data to the user.
NOTE
Server Processes generally have a one-to-one relationship with User Processes— each User Process connects to one and only one Server Process. However, in some Oracle configurations, multiple User Processes can share Server Processes. See Chapter 5, "Oracle Shared Server," for details.
In addition to the User and Server processes that are associated with each user connection, an additional memory structure called the Program Global Area (PGA) is also created for each user. The PGA stores user-specific session information such as bind variables and session variables. Every Server Process on the server has a PGA memory area. Figure 1.12 shows the relationship between a User Process, Server Processes, PGA, and session.
Figure 1.12. The relationship between User and Server processes
The Server Process communicates with the Oracle instance on behalf of the user. The Oracle instance is examined in the next section.
1.3.2. The Oracle Instance
An Oracle Server instance is made up of Oracle's main memory structure, called the System Global Area (SGA), and several Oracle background processes. It is with the SGA that the Server Process communicates when the user accesses the data in the database. The components of the instance are described in the following sections.
1.3.2.1. The System Global Area
The SGA is made up of three required components and three optional components. Table 1.9 describes the required components, and Table 1.10 describes the optional components.
| SGA Component | Description |
|---|---|
| Shared Pool | Caches the most recently used SQL statements that have been issued by database users |
| Database Buffer Cache | Caches the data that has been most recently accessed by database users |
| Redo Log Buffer | Stores transaction information for recovery purposes |
Oracle uses a least recently used (LRU) algorithm to manage the contents of the Shared Pool and Database Buffer Cache. When a user's Server Process needs to put a SQL statement into the Shared Pool or copy a database block into the Buffer Cache, Oracle uses the space in memory that is occupied by the least recently accessed SQL statement or buffer to hold the requested SQL or block copy. Using this technique, Oracle keeps frequently accessed SQL statements and database buffers in memory longer, improving the overall performance of the server by minimizing parsing and physical disk I/O.
The sizes of these SGA components can be managed in two ways: manually and automatically. If you choose to manage these components manually, you must specify the size of each SGA component and then increase or decrease the size of each component according to the needs of the application. If these components are managed automatically, the instance itself will monitor the utilization of each SGA component and adjust their sizes accordingly, relative to a predefined maximum allowable aggregate SGA size.
Whether size is managed manually or automatically, Oracle accomplishes this dynamic allocation of space within the SGA by dividing the allocated SGA memory into chunks called granules. These granules of memory are dynamically allocated or deallocated from the Buffer Cache, Shared Pool, Large Pool, and Java Pool as needed according to the demands placed on these areas by the application users.
NOTE
Depending on your server operating system and the size of the SGA, granules can be 4MB, 8MB, or 16MB in size.
Whether the instance operates in manual or automatic mode is determined by settings in a configuration file called the parameter initialization file. There are two types of parameter initialization files: Parameter Files (PFILES), and Server Parameter Files (SPFILES). You can use either type of file to configure instance and database options, including the size of the SGA and its components if manual SGA management is being used, or the overall memory allocated to the SGA if automatic SGA management is being used. However, there are some important differences between the two types of configuration files, as shown in Table 1.11.
NOTE
The use of automatic SGA management features requires the use of the SPFILE for maximum benefit.
NOTE
See the section "OFA Directory Paths" later in this chapter for details on the default locations of PFILES and SPFILES.
You can specify more than 250 documented configuration parameters in the PFILE or SPFILE. Oracle 10g divides these parameters into two categories: basic and advanced. Oracle recommends that you set only about 30 basic initialization parameters manually. Oracle also recommends that you do not modify the remaining 220 or so parameters unless directed to do so by Oracle Support or to meet the specific needs of your application. The basic initialization parameters are described in Table 1.12.
| Parameter Name | Description |
|---|---|
| CLUSTER_DATABASE | Tells the instance whether it is part of a clustered environment. |
| COMPATIBLE | Specifies the release level and feature set that you want to be active in the instance. |
| CONTROL_FILES | Designates the physical location of the database control files. |
| DB_BLOCK_SIZE | Specifies the default database block size. |
| DB_CREATE_FILE_DEST | Specifies the directory location where database datafiles will be created if the Oracle Managed Files feature is used. |
| DB_CREATE_ONLINE_LOG_DEST_n | Specifies the location(s) where the database redo log files will be created if the Oracle Managed Files feature is used. |
| DB_DOMAIN | Specifies the logical location of the database on the network. |
| DB_NAME | Specifies the name of the database that is mounted by the instance. |
| DB_RECOVERY_FILE_DEST | Specifies the location where recovery files will be written if the Flash Recovery feature is used. |
| DB_RECOVERY_FILE_DEST_SIZE | Specifies the amount of disk space available for storing Flash Recovery files. |
| DB_UNIQUE_NAME | Specifies a globally unique name for the database within the enterprise. |
| INSTANCE_NUMBER | Identifies the instance in a Real Application Clusters (RAC) environment. |
| JOB_QUEUE_PROCESSES | Specifies the number of background processes to start for handling jobs submitted via Enterprise Manager or DBMS_JOBS. |
| LOG_ARCHIVE_DEST_n | Specifies as many as nine locations where archived redo log files are to be written. |
| LOG_ARCHIVE_DEST_STATE_n | Indicates how the specified locations should be used for log archiving. |
| NLS_LANGUAGE | Specifies the default language of the database. |
| NLS_TERRITORY | Specifies the default region or territory of the database. |
| OPEN_CURSORS | Sets the maximum number of cursors that an individual session can have open at one time. |
| PGA_AGGREGATE_TARGET | Establishes the overall amount of memory that all PGA processes are allowed to consume. |
| PROCESSES | Specifies the maximum number of operating system processes that can connect to the instance. |
| REMOTE_LISTENER | Specifies a network name that points to the address or list of addresses of remote Oracle Net listeners. |
| REMOTE_LOGIN_PASSWORDFILE | Determines whether the instance uses a password file and what type. |
| ROLLBACK_SEGMENTS | Specifies only if Automatic Undo Management is not being used. |
| SESSIONS | Determines the maximum number of sessions that can connect to the database. |
| SGA_TARGET | Establishes the maximum size of the SGA, within which space is automatically allocated to each SGA component when automatic memory management is used. |
| SHARED_SERVERS | Specifies the number of Shared Server processes to start when the instance is started. See Chapter 5 for details. |
| STAR_TRANSFORMATION_ENABLED | Determines whether the optimizer will consider star transformations when queries are executed. See Chapter 9, "Proactive Database Maintenance and Performance Monitoring," for details on the optimizer. |
| UNDO_MANAGEMENT | Establishes whether system undo is automatically or manually managed. See Chapter 8 for details on undo segments. |
| UNDO_TABLESPACE | Specifies which tablespace stores undo segments if the Automatic Undo Management option is used. See Chapter 8 for details on undo management. |
As shown in Table 1.12, many initialization parameters are used to specify the size of the SGA and its components. Any parameters not specified in the PFILE or SPFILE take on their default values. The following is an example of the contents of a typical Unix Oracle 10g PFILE that contains both basic and advanced parameters:
db_block_size=8192 db_file_multiblock_read_count=16 open_cursors=300 db_name=PROD background_dump_dest=/u01/app/oracle/admin/PROD/bdump
core_dump_dest=/u01/app/oracle/admin/PROD/cdump
user_dump_dest=/u01/app/oracle/admin/PROD/udump
control_files=(/u02/oradata/PROD/control01.ctl,
/u03/oradata/PROD/control02.ctl,
/u05/oradata/PROD/control03.ctl)
db_recovery_file_dest=/u01/app/oracle/flash_recovery_area/
db_recovery_file_dest_size=2147483648
job_queue_processes=10
compatible=10.1.0.2.0
sga_target=500M
max_sga_size=800M
processes=250
remote_login_passwordfile=EXCLUSIVE
pga_aggregate_target=25165824
sort_area_size=65536
undo_management=AUTO
undo_tablespace=UNDOTBS1In this sample PFILE, the sizes of the Shared Pool, Database Buffer Cache, Large Pool, and Java Pool are not individually specified. Instead, Oracle 10g's automatic memory management features allow you to simply set one configuration parameter—SGA_TARGET—to establish the total amount of memory allocated to the SGA. Oracle then automatically allocates portions of this overall memory allocation to each of the SGA components at instance startup and also dynamically reallocates the space as needed to maximize performance while the database is in use. In addition to examining the PFILE/SPFILE, you can also use the V$SGA and V$SGA_ DYNAMIC_COMPONENTS dynamic performance view to display the size of the SGA and some of its components, as shown here:
SQL> select * 2 from V$SGA; NAME VALUE -------------------- ---------- Fixed Size 787988 Variable Size 145750508 Database Buffers 25165824 Redo Buffers 262144
The output from this query shows that the total size of the SGA is 171,966,464 bytes. This total size is composed of the variable space that is composed of the Shared Pool, the Large Pool, and the Java Pool (145,750,508 bytes), the Database Buffer Cache (25,165,824 bytes), the Redo Log Buffer (262,144 bytes), and some additional space (787,988 bytes) that stores information used by the instance's background processes. The V$SGA_DYNAMIC_COMPONENTS view displays additional detail about the allocation of space within the SGA, as shown in the following query:
SQL> select component,current_size 2 from v$sga_dynamic_components; COMPONENT CURRENT_SIZE ---------------------------------------- ------------ shared pool 83886080 large pool 8388608 java pool 50331648 streams pool 0 DEFAULT buffer cache 25165824 KEEP buffer cache 0 RECYCLE buffer cache 0 DEFAULT 2K buffer cache 0 DEFAULT 4K buffer cache 0 DEFAULT 8K buffer cache 0 DEFAULT 16K buffer cache 0 DEFAULT 32K buffer cache 0 OSM Buffer Cache 0 13 rows selected.
The results of this query show that the Shared Pool is 83,886,080 bytes, the Large Pool is 8,388,608 bytes, the Java Pool is 50,331,648 bytes, and the Database Buffer Cache is 25,165,824 bytes.
You can also use EM Database Control to view the sizes of each of the SGA components, as shown in Figure 1.13.
Figure 1.13. EM Database Control showing SGA components
1.3.2.2. Oracle Background Processes
There are many types of Oracle background processes. Each performs a specific job in helping to manage the instance. Five Oracle background processes are required, and several background processes are optional. The required background processes are found in all Oracle instances. Optional background processes may or may not be used depending on which optional Oracle features are being used in the database. Table 1.13 describes the required background processes, and Table 1.14 describes some of the optional background processes.
| Process Name | Operating System Process | Description |
|---|---|---|
| System Monitor | SMON | Performs instance recovery following an instance crash, coalesces free space in the database, and manages space used for sorting |
| Process Monitor | PMON | Cleans up failed user database connections |
| Database Writer | DBWn[] | Writes modified database blocks from the SGA's Database Buffer Cache to the datafiles on disk |
| Log Writer | LGWR | Writes transaction recovery information from the SGA's Redo Log Buffer to the online Redo Log files on disk |
| Checkpoint | CKPT | Updates the database files following a Checkpoint Event |
| [] | ||
[] The n in any operating system process name signifies that more than one of these processes may be running. In these cases, the n is replaced with a numeric value. For example, if four database writer processes are running, their process names at the operating system level are DBW0, DBW1, DBW2, and DBW3.
| Process Name | Operating System Process | Description |
|---|---|---|
| Archiver | ARCn | Copies the transaction recovery information written to disk by LGWR (log writer) to the online Redo Log files and to a secondary location in case it is needed for recovery. Nearly all production databases use this optional process. See Chapter 2, "Creating and Controlling a Database," for details on how to enable this process. |
| Recoverer | RECO | Recovers failed transactions that are distributed across multiple databases when using Oracle's distributed database feature. |
| Job Queue Monitor | CJQn | Assigns jobs to the Job Queue processes when using Oracle's job scheduling feature. |
| Job Queue | Jnnn | Executes database jobs that have been scheduled using Oracle's job scheduling feature. |
| Queue Monitor | QMNn | Monitors the messages in the message queue when Oracle's Advanced Queuing feature is used. |
| Parallel Query Slave | Qnnn | Used to carry out portions of a larger overall query when Oracle's Parallel Query feature is used. |
| Dispatcher | Dnnn | Assigns user's database requests to a queue where they are then serviced by Shared Server processes when Oracle's Shared Server feature is used. See Chapter 5 for details on using Shared Servers. |
| Shared Server | Snnn | Server Processes that are shared among several users when Oracle's Shared Server feature is used. See Chapter 5 for details on using Shared Servers. |
| Memory Manager | MMAN | Manages the size of each individual SGA component when Oracle's Automatic Shared Memory Management feature is used. See Chapter 9 for more information on using this feature. |
| Memory Monitor | MMON | Gathers and analyzes statistics used by the Automatic Workload Repository feature. See Chapter 9 for more information on using this feature. |
| Memory Monitor Light | MMNL | Gathers and analyzes statistics used by the Automatic Workload Repository feature. See Chapter 9 for more information on using this feature. |
| Recovery Writer | RVWR | Writes recovery information to disk when Oracle's Flashback Database Recovery feature is used. See Chapter 10, "Implementing Database Backups," and Chapter 11, "Implementing Database Recovery," for details on how to use the Flashback Database Recovery feature. |
| Change Tracking Writer | CTWR | Keeps track of which database blocks have changed when Oracle's incremental Recovery Manager feature is used. See Chapters 10 and 11 for details on using Recovery Manager to perform backups. |
On Unix systems, you can view these background processes from the operating system using the ps command, as shown here:
$ ps -ef |grep PROD oracle 3969 1 0 10:02 ? 00:00:05 ora_pmon_PROD oracle 3971 1 0 10:02 ? 00:00:00 ora_mman_PROD
oracle 3973 1 0 10:02 ? 00:00:07 ora_dbw0_PROD oracle 3975 1 0 10:02 ? 00:00:07 ora_lgwr_PROD oracle 3977 1 0 10:02 ? 00:00:10 ora_ckpt_PROD oracle 3979 1 0 10:02 ? 00:00:20 ora_smon_PROD oracle 3981 1 0 10:02 ? 00:00:00 ora_reco_PROD oracle 3983 1 0 10:02 ? 00:00:09 ora_cjq0_PROD oracle 3985 1 0 10:02 ? 00:00:00 ora_d000_PROD oracle 3987 1 0 10:02 ? 00:00:00 ora_s000_PROD oracle 4052 1 0 10:02 ? 00:00:00 ora_qmnc_PROD oracle 4054 1 0 10:02 ? 00:00:29 ora_mmon_PROD oracle 4057 1 0 10:02 ? 00:00:08 ora_mmnl_PROD oracle 4059 1 0 10:02 ? 00:01:04 ora_j000_PROD oracle 27544 1 0 20:29 ? 00:00:00 ora_q000_PROD
This output shows that 15 background processes are running on the Unix server for the PROD database.
NOTE
User Server processes are not considered part of the instance.
NOTE
In Windows environments, a Windows service called OracleService Instance-Name is also associated with each instance. This service must be started in order to start up the instance in Windows environments.
1.3.3. The Oracle Database
An instance is a temporary memory structure, but the Oracle database is made up of a set of physical files that reside on the host server's disk drives. These files are called control files, datafiles, and redo logs. Additional physical files that are associated with the Oracle database, but are not technically part of the database, are the password file, the PFILE and SPFILE described previously, and any archived redo log files. Table 1.15 summarizes the role that each of these files plays in the database architecture.
The three files that make up a database—the control file, datafile, and redo logs—are described in the following sections.
| File Type | Information Contained in File(s) |
|---|---|
| Control | Locations of other physical files, database name, database block size, database character set, and recovery information. These files are required to open the database. |
| Datafile | All application data and internal metadata. |
| Redo log | Record of all changes made to the database; used for recovery. |
| Parameter (PFILE or SPFILE) | Configuration parameters for the SGA, optional Oracle features, and background processes. |
| Archived log | Copy of the contents of previous online redo logs, used for recovery. |
| Password | Optional file used to store names of users who have been granted the SYSDBA and SYSOPER privileges. See Chapter 6 for details on SYSDBA and SYSOPER privileges. |
| Oracle Net | Entries that configure the database listener and client-todatabase connectivity. See Chapter 4 for details. |
1.3.3.1. Control Files
Control files are critical components of the database because they store important information that is not available anywhere else. This information includes the following:
The name of the database
The names, locations, and sizes of the datafiles and redo log files
Information used to recover the database in the case of a disk failure or user error
The control files are created when the database is created in the locations specified in the control_files parameter in the parameter file. Because loss of the control files negatively impacts the ability to recover the database, most production databases multiplex their control files to multiple locations. Oracle uses the CKPT background process to automatically update each of these files as needed, keeping the contents of all copies of the control synchronized. You can use the dynamic performance view V$CONTROLFILE to display the names and locations of all the database's control files. A sample query of V$CONTROLFILE on a Unix system is shown here:
SQL> select name from v$controlfile; NAME ------------------------------------ /u02/oradata/PROD/control01.ctl
/u03/oradata/PROD/control02.ctl /u05/oradata/PROD/control03.ctl
This query shows that the database has three control files, called control01.ctl, control02.ctl, and control03.ctl, which are stored in the directories /u02/oradata/ PROD/, /u03/oradata/PROD/, and /u05/oradata/PROD/ respectively. You can also monitor control files using EM Database Control, as shown in Figure 1.14.
NOTE
Control files are usually the smallest files in the database, generally between 1MB and 5MB in size. However, they can be larger depending on the PFILE/ SPFILE setting for CONTROLFILE_RECORD_KEEP_TIME when the Recovery Manager feature is used.
One thing that the control files keep track of in the database are the names, locations, and sizes of the database datafiles. Datafiles, and their relationship to another database structure called a tablespace, are examined in the next section.
1.3.3.2. Datafiles
Datafiles are the physical files that actually store the data that has been inserted into each table in the database. The size of the datafiles is directly related to the amount of table data that they store. Datafiles are the physical structure behind another database storage area called a tablespace. A tablespace is a logical storage area within the database. Tablespaces group logically related segments. For example, all the tables for the Accounts Receivable application might be stored together in a tablespace called AR_TAB, and the indexes on these tables might be stored in a tablespace called AR_IDX.
Figure 1.14. EM Database Control showing control files
By default, every Oracle 10g database must have at least three tablespaces. These tablespaces are described in Table 1.16.
| Tablespace Name | Description |
|---|---|
| SYSTEM | Stores the data dictionary tables and PL/SQL code. |
| SYSAUX | Stores segments used for database options such as the Automatic Workload Repository, Online Analytical Processing (OLAP), and Spatial. |
| TEMP | Used for performing large sort operations. TEMP is required when the SYSTEM tablespace is created as a locally managed tablespace; otherwise, it is optional. See Chapter 3 for details. |
In addition to these three required tablespaces, most databases have tablespaces for storing other database segments similar to those shown in Table 1.17.
| Tablespace Name | Description |
|---|---|
| TOOLS | Used to store segments for nonapplication management tools. |
| USERS | Used as the default tablespace for database users. |
| UNDOTBS1 | Used to store transaction information for read consistency and recovery purposes. See Chapter 8 for details. |
Beyond the six common tablespaces listed in Tables 1.16 and 1.17, production databases often have many more tablespaces for storing application segments. Either you or the application vendor determines the total number and names of these tablespaces. You can use the DBA_TABLESPACES data dictionary view to display a list of all the tablespaces in the database. This is a sample query on DBA_TABLESPACES:
SQL> select tablespace_name 2 from dba_tablespaces 3 order by tablespace_name; TABLESPACE_NAME ------------------------------ APPL_IDX
APPL_TAB EXAMPLE SYSAUX SYSTEM TEMP UNDOTBS1 7 rows selected.
This output shows that this database consists of seven tablespaces: SYSTEM, UNDOTBS1, SYSAUX, TEMP, EXAMPLE, APPL_TAB, and APPL_IDX. You can also monitor tablespaces using EM Database Control, as shown in Figure 1.15.
NOTE
The current release of Oracle's Application 11i uses more than 375 tablespaces to store application data and indexes.
For each of the tablespaces shown in Figure 1.15, there must be at least one datafile. Some tablespaces may be composed of several datafiles for management or performance reasons. The data dictionary view DBA_DATA_FILES shows the datafiles associated with each tablespace in the database. The following SQL statement shows a sample query on the DBA_DATA_FILES data dictionary view.
SQL> select tablespace_name, file_name 2 from dba_data_files 3 order by tablespace_name; TABLESPACE_NAME FILE_NAME ------------------------------ ----------------------------------- APPL_IDX /u01/oradata/PROD/appl_idx01.dbf APPL_IDX /u03/oradata/PROD/appl_idx02.dbf APPL_TAB /u01/oradata/PROD/appl_tab01.dbf APPL_TAB /u03/oradata/PROD/appl_tab02.dbf EXAMPLE /u02/oradata/PROD/example01.dbf SYSAUX /u04/oradata/PROD/sysaux01.dbf SYSTEM /u05/oradata/PROD/system01.dbf UNDOTBS1 /u02/oradata/PROD/undotbs101.dbf
This output shows that the datafiles for the six tablespaces in this database are stored in the /u01/oradata/PROD through /u05/oradata/PROD directories. The APPL_DATA and APPL_ INDX tablespaces are each made up of two datafiles; the rest of the tablespaces are each made up of only one datafile. You can also monitor datafiles using EM Database Control, as shown in Figure 1.16.
Figure 1.15. EM Database Control showing tablespaces
Figure 1.16. EM Database Control showing datafiles
NOTE
Datafiles are usually the largest files in the database, ranging from megabytes to gigabytes or terabytes in size.
When a user performs a SQL operation on a table, the user's Server Process copies the affected data from the datafiles into the Database Buffer Cache in the SGA. If the user has performed a committed transaction that modifies that data, the Database Writer process (DBWn) ultimately writes the modified data back to the datafiles.
When Does Database Writer Write?The DBWn background process writes to the datafiles whenever one of the following events occurs:
|
1.3.3.3. Redo Log Files
Whenever a user performs a transaction in the database, the information needed to reproduce this transaction in the event of a database failure is automatically recorded in the Redo Log Buffer. The contents of the Redo Log Buffer are ultimately written to the redo logs by the LGWR background process.
Because of the important role that redo logs play in Oracle's recovery mechanism, they are usually multiplexed, or copied. This means that each redo log contains one or more copies of itself in case one of the copies becomes corrupt or is lost due to a hardware failure. Collectively, these sets of redo logs are referred to as redo log groups. Each multiplexed file within the group is called a redo log group member. Oracle automatically writes to all members of the redo log group to keep the files in sync. Each redo log group must be composed of one or more members. Each database must have a minimum of two redo log groups because redo logs are used in a circular fashion, as shown in Figure 1.17.
Figure 1.17 shows a database that has three redo log groups: group 1, group 2, and group 3. Each group is composed of two members. The first member of each group is stored in the directory /u02/oradata/PROD. The second, multiplexed member is stored in the directory /u04/oradata/PROD. You can use the V$LOGFILE dynamic performance view to view the names of the redo log groups and the names and locations of their members, as shown next. The following SQL statement shows a sample query on a Unix system of the DBA_DATA_FILES data dictionary view.
SQL> select group#, member
2 from v$logfile
3 order by group#;
GROUP# MEMBER
---------- ------------------------------
1 /u02/oradata/PROD/redo01a.rdo1 /u04/oradata/PROD/redo01b.rdo
2 /u02/oradata/PROD/redo02a.rdo
2 /u04/oradata/PROD/redo02b.rdo
3 /u02/oradata/PROD/redo03a.rdo
3 /u04/oradata/PROD/redo03b.rdo
6 rows selected.
When Does Log Writer Write?The LGWR background process writes to the current redo log group whenever one of the following database events occurs:
|
Figure 1.17. How redo logs are used in the database
This output shows that the database has a total of three redo log groups and that each group has two members. Each of the members is located in a separate directory and disk volume on the server's disk drives so that the loss of a single disk drive will not cause the loss of the recovery information stored in the redo logs. You can also monitor redo logs using EM Database Control, as shown in Figure 1.18.
When a user performs a DML activity on the database, the recovery information for this transaction is written to the redo log buffer by the user's Server Process. LGWR eventually writes this recovery information to the active redo log group until that log group is filled. Once the current log fills with transaction information, LGWR switches to the next redo log until that log group fills with transaction information, and so on, until all available redo logs are used. When the last redo log is used, LGWR wraps around and starts using the first redo log again. As shown in the following query, you can use the V$LOG dynamic performance view to display which redo log group is currently active and being written to by LGWR:
SQL> select group#, members, status
2 from v$log
3 order by group#;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 CURRENT
2 2 INACTIVE
3 2 INACTIVEThis output shows that redo log group number 1 is currently active and being written to by LGWR. Once redo log group 1 is full, LGWR switches to redo log group 2.
Figure 1.18. EM Database Control showing redo logs
When LGWR wraps around from the last redo log group back to the first redo log group, any recovery information previously stored in the first redo log group is overwritten and therefore no longer available for recovery purposes. However, if the database is operating in archive log mode, the contents of these previously used logs are copied to a secondary location before the log is reused by LGWR. If this archiving feature is enabled, it is the job of the ARCn background process described in the previous section to copy the contents of the redo log to the archive location. These copies of old redo log entries are called archive logs. This process is shown graphically in Figure 1.19.
In Figure 1.19, the first redo log group has been filled, and LGWR has moved to on to redo log group 2. As soon as LGWR switches from redo log group 1 to redo log group 2, the ARCn process starts copying the contents of redo log group 1 to the archive log file location. Once the first redo log group is safely archived, LGWR is free to wrap around and reuse the first redo log group once redo log group 3 is filled.
NOTE
Nearly all production databases run in archive log mode because they need to be able to redo all transactions since the last backup in the event of a hardware failure or user error that damages the database.
NOTE
If LGWR needs to write to the redo log group that ARCn is trying to copy but cannot because the destination is full, the database hangs until space is cleared on the drive.
The next section talks about how to install and configure the Oracle software on your server so that you can then create a database. Creating a database is described in detail in Chapter 2.
Figure 1.19. How ARCn copies redo log entries to disk
