Oracle 10g database architecture

Learning objective

After completing this topic, you should be able to identify the files and logical storage units that make up the Oracle database.

1. Understanding the Oracle database

A successfully run database is made up of both core and ancillary files. Without core files, such as the control file, a database cannot function.

A database can function without ancillary files, such as the parameter file, but these files are very important if the database is to run correctly.

The core files in an Oracle database are

• control files
• data files
• online redo log files

control files

Control files contain data about the status of the physical data files stored in the database. This data or "metadata" is read by the instance on startup and during normal operation.

data files

Data files are used to contain the data belonging to the database.

online redo log files

Redo log files are used to help recover database instances if the original data is lost due to a system failure such as a power outage or a computer fault. They contain a record of all the data and are usually stored in a number of locations for safety.

The ancillary files that are needed for a successfully run database are

• parameter file
  
• password file
• archive log files

parameter file

The parameter file contains the location of the control files and is used to define how the instance is configured. The control files are then read when the instance is started.

password file

The password file is used if you want to apply security to your database. The file contains the username and password details of designated users to allow them to access the database.

archive log files

The archive log files contain a history of the redo logs that have been generated to prevent data loss. The log files are archived regularly so that they can be used with a backup database to replace the data.

Question

Which of these files are required for a database to function?

Options:

1. Control file
2. Data file
3. Parameter file
4. Redo log files

Answer

The control files, data files, and redo log files are required for a database to function.

Option 1 is correct. The control file provides the information, or metadata, on how the physical files are stored in the database.

Option 2 is correct. The data file contains the data itself. This is the information that is stored in the database.

Option 3 is incorrect. The parameter file, which contains the location of the control files, is not a core file of the database. However, although a database can function without it, it is very important if it is to run successfully.

Option 4 is correct. The redo log files contains the backup data should an emergency occur such as a power outage or a computer fault. These files are archived to prevent data loss.

Question

Match each file type to its function in the database.

Options:

1. Archived log files
2. Control files
3. Password files
4. Redo log files

Targets:

1. Specify how the physical data files are stored in the database
2. Contain a history of the redo logs that are generated by the instance
3. Used to help recover database instances if the original data is lost due to a system failure
4. Used if you want to apply security to your database

Answer

Control files specify how the physical data files are stored in the database. Redo log files are used to help recover database instances. Archived log files contain a history of the redo logs, and password files are used if you want to apply security to your database.

Option 1 is correct. The archive log files maintain an updated history of the redo logs. These files provide a detailed record of data so that they can be used with a backup database to recover lost information.

Option 2 is correct. The control file is read when the instance is started. It contains information on where and how the physical files are stored.

Option 3 is correct. The password file is not a core file of the database. However, if you want to apply security to your database, you use it to store a list of usernames and passwords.

Option 4 is correct. Redo log files are generated by the instance on a regular basis so that they can be used with a backup database to recover data.

The logical data structures of the database exist as physical files in the database.

You can get information about these structures through the Administration page in Enterprise Manager, where they are listed in the Storage section.

To read information about a structure, you select the relevant hyperlink.

You click Datafiles.

In the Datafiles page you can use a search engine to find the required data file.

Alternatively, you can scroll through the Results list, select the appropriate data file, SYSAUX in this case, and click View.

The View Datafile page provides you with detailed information about the data file - for example, its location, status, size, and whether it can be autoextended.

Question

You want to view the properties of a particular data file.

This task requires you to check the maximum file size set for the system01 data file. Which of these options allows you to complete this task?

Options:

1. You select Administration in Enterprise Manager. Then in the Storage section you select Datafiles. Next you select /system01.dbf in the Results list and click View.
2. You select Maintenance in Enterprise Manager. Then in the Storage section you select Datafiles. Next you select /system01.dbf in the Results list and click View.

Answer

You select Administration in Enterprise Manager. Then in the Storage section you select Datafiles. Next you select /system01.dbf in the Results list and click View.

2. Identifying control and redo log files

A database's control file is read when the instance is started, and the database mounted, to find out what physical data files are in the database.

It is automatically updated when you add or remove any files from the database and its location is defined in the database's parameter file.

As the database can't function without its control file, you should multiplex - or copy - it to at least three separate physical devices. This will prevent it being lost.

If you set the parameter file to allow multiple files, the Oracle database server will then be able to maintain more than one copy of the control file.

When a database changes, for example when a transaction occurs, the change is automatically recorded in the database's redo log file.

This record is there to aid the recovery of data in case of database failure due to a power outage or a computer problem.

When such a problem occurs, the record in the redo log is used, along with a backup database, to replace the lost data.

To prevent the loss of these important resources, redo logs should be multiplexed to several separate locations.

The redo log file is made up of a number of groups, with each group containing a redo log file and multiple identical copies of it.

The copies of the redo log file are considered to be members of the group and each group has its own number.

The redo log buffer, containing data about the most recent changes to the database, sends the information to the log writer (LGWR) which in turn writes it to a redo log in a redo log group.

The LGWR continues to write to that group until the file is filled or a log switch operation is executed.

When that occurs, the LGWR continues in the redo log in the next group. The LGWR moves through the redo log groups in a circular fashion.

Question

Match each database file type to the appropriate characteristics.

Options:

1. Store information about the physical data files in the database
2. Used to aid the recovery of data in the event of database failure
3. Their locations are defined in the database's parameter file
4. Are used with a backup database

Targets:

1. Control files
2. Redo log files

Answer

Control files are used to store information about the physical data files in the database. Their locations are defined in the database's parameter file. Redo log files are used with a backup database to recover data in the event of database failure.

You can view information about the control files in a database by selecting Controlfiles in the Storage section of the Enterprise Manager's Administration page.

The Controlfiles page opens on the General tabbed page. This page provides the name and location of the control files and their multiplexed versions, otherwise known as 'mirrored images'.

In a production database, the control files should be placed on separate media.

The Controlfiles page also has an Advanced tab and a Record Section tab.

You click Advanced at the bottom of the Controlfiles General tabbed page to view advanced information about the control file.

The Advanced tabbed page provides you with information about the creation and modification of the control file, along with the database ID.

You click Record Section to access information about the control file record.

The Record Section tabbed page contains record information about the control file, such as the record type, the record size, the total number of records, and the number of records used.

To access any of the redo log files in a database, you click Redo Log Groups in the Storage section of the Enterprise Manager's Administration page.

In the Redo Log Groups page you can access detailed information about a redo log file.

To view information about a redo log group, you select the option in the Select column beside the group number you want, 2 in this case, and click View.

Note

You can also do a search if a lot of groups are listed in the Redo Log Groups page.

The View Redo Log Group page displays the redo log group number, file size, and status, and the names and locations of the redo log members.

Question

While conducting a review of backup resources in your database, you want to check that the members of the redo log groups are in the correct location.

This task requires you to view the directory information for redo log group 3. Which of these options allows you to complete this task?

Options:

1. You select Redo Log Groups in the Storage section of the Administration page in Enterprise Manager. You then select 3 in the Group column.
   
   
2. You select Redo Log Groups in the Storage section of the Administration page in Enterprise Manager. You then select the option for Group 3 in the Select column and click View.

Answer

You select Redo Log Groups in the Storage section of the Administration page in Enterprise Manager. You then select the option for Group 3 in the Select column and click View.

3. Identifying logical storage units

A database stores data in logical storage units known as tablespaces.

Tablespaces are used to group related logical structures together. They can contain one or more data files and these store the data of all the logical structures physically in the database.

You can divide each database logically into one or more tablespaces.

By default, Oracle databases use smallfile tablespaces, which contain many small data files.

However, you can also use what are referred to as bigfile tablespaces. These tablespaces use one very large data file - a bigfile can be as large as 8 exabytes.

Database objects, such as tables and indexes, are stored in a tablespace as segments.

A segment, in turn, contains one or more extents and each extent is made up of contiguous data blocks. As a result, each extent can only exist in a single data file.

A data block is the smallest unit of data used by a database.

Question

Which of these make up the logical storage units of a database?

Options:

1. Data blocks
2. Data files
3. Extents
4. Segments

Answer

Data blocks, extents, and segments are logical storage units in a database.

Option 1 is correct. Data blocks are the smallest units of stored data in the database and are contained contiguously in extents.

Option 2 is incorrect. A data file is a core file in the database. It is created for a tablespace to store the data physically but it is not a logical storage unit in its own right.

Option 3 is correct. A segment can contain one or more extents. These in turn consist of contiguous data blocks. Because of this, each extent can only exist in one data file.

Option 4 is correct. Database objects are stored in tablespaces as segments. These consist of extents, which, in turn, are made up of contiguous data blocks.

When a set of data blocks is requested by the database from the Operating System (OS), the OS aligns the requested data blocks with real OS blocks on the storage device.

This frees the DBA from the need to have a physical address for the data in the database.

As the data blocks in the database are mapped to OS blocks in the storage device, you can stripe or mirror the data file on several disks.

Striping a data file means copying certain parts of the file to a separate ancillary file.

Mirroring a file means copying it identically in a new location. This is done to preserve the data in case the original is lost.

The default size of a data block is 8 KB, which is adequate for most purposes.

However, you can change the size of the data blocks when creating the database.

For example, you may want to create a larger block if your database uses a data warehouse application that has large tables and indexes.

If your database supports a transactional application with random reads and writes a smaller block size might be more appropriate.

The maximum size you can make a block is determined by your OS. The minimum block size is 2 KB, although this smallest size should be avoided.

Tablespaces can have different block sizes, but you should only allow this when supporting transportable tablespaces.

Question

Rank the storage units according to their place in the logical hierarchy of a database.

Options

Option	Description
A	Data blocks
B	Extents
C	Segments

Answer

Correct ranking

Option	Description
C	Segments Correct. Database objects are placed in a tablespace in segments. A segment contains one or more extents, which in turn contain data blocks.
B	Extents Correct. A segment can contain one or more extents. An extent consists of contiguous data blocks. As a result, each extent can only exist in one data file.
A	Data blocks Correct. Data blocks are contained contiguously in extents. When a database requests data blocks from the OS, the OS maps the data blocks to OS blocks in the storage device.

Summary

A database consists of three core files - control files, data files, and redo log files.
While these are needed for the database to function, there are also other ancillary files which enable the database to run successfully. These are the parameter file, the password file, and the archived log file.

A database's control file is read when the instance is started. It is used to tell the database about the status of the physical data files, and should be multiplexed to prevent it being lost. Redo log files are used to record all the changes to a database. Used with a backup database, they can help replace lost data should a serious fault occur.

The database stores data in logical storage units known as tablespaces. Each tablespace can contain one or more data files. Database objects are stored in tablespaces as segments. Each segment consists of one or more extents, and these in turn are made up of contiguous data blocks.