6 Managing Database Storage Structures

About Control Files

A control file tracks the physical components of the database. It is the root file that the database uses to find all the other files used by the database. Because of the importance of the control file, Oracle recommends that the control file be multiplexed, or have multiple identical copies. For databases created with Oracle Database Configuration Assistant (DBCA), three copies of the control file are automatically created and kept synchronized with each other.

If any control file fails, then your database becomes unavailable. If you have a control file copy, however, you can shut down your database and re-create the failed control file from the copy, then restart your database. Another option is to delete the failed control file from the CONTROL_FILES initialization parameter and restart your database using the remaining control files.

About Online Redo Log Files

Every Oracle database has a set of two or more online redo log files. The set of online redo log files is collectively known as the redo log for the database. A redo log is made up of redo entries, which are also called redo records.

The online redo log stores a copy of the changes made to data. If a failure requires a data file to be restored from backup, then the recent data changes that are missing from the restored data file can be obtained from the online redo log files, so work is never lost. The online redo log files are used to recover a database after hardware, software, or media failure. To protect against a failure involving the online redo log file itself, Oracle Database can multiplex the online redo log file so that two or more identical copies of the online redo log file can be maintained on different disks.

The online redo log for a database consists of groups of online redo log files. A group consists of an online redo log file and its multiplexed copies. Each identical copy is considered to be a member of that group. Each group is defined by a number, such as Group 1.

Figure 6-2 shows the configuration of a database that has three online redo log groups and two members in each group. For each group, the members are stored on separate disks for maximum availability. For example, the members of Group 1 are the redo log files A_LOG1 and B_LOG1.

Figure 6-2 Online Redo Log Groups and Their Members

Description of "Figure 6-2 Online Redo Log Groups and Their Members"

The database log writer process (LGWR) writes redo records from the memory buffer to a redo log group until the log files in that group reach their storage size limit, or until you request a log switch operation. The LGWR process then writes to the next log group. The LGWR process performs this action in a circular fashion so that the oldest group is overwritten by the most recent redo records.

About Archived Redo Log Files

When you enable archiving of the online redo logs, Oracle Database copies the online redo log files to another location before they are overwritten. These copied files are referred to as archived redo log files. You can archive to multiple locations.

These archived redo log files extend the amount of redo data that can be saved and are used for recovery. Archived redo log files are required to recover a backup of the database from the time of the backup to the current time. Archiving can be either enabled or disabled for the database, but Oracle strongly recommends that you enable archiving. Oracle also recommends that you configure the database to write archived redo log files to the fast recovery area.

About Rollback Segments

Rollback segments were database structures used to track undo information for the database in earlier releases of Oracle Database. Now, the preferred way of managing undo information is with the undo tablespace. For more information, see "Managing Undo Data".

About Data Files

Data files are the operating system files that store the data within the database. The data is written to these files in an Oracle proprietary format that cannot be read by other programs. Tempfiles are a special class of data files that are associated only with temporary tablespaces.

Data files can be broken down into the following components:

• Segment

  A segment contains a specific type of database object. For example, a table is stored in a table segment, and an index is stored in an index segment. A data file can contain many segments.

• Extent

  An extent is a contiguous set of data blocks within a segment. Oracle Database allocates space for segments in units of one extent. When the existing extents of a segment are full, the database allocates another extent for that segment.

• Data block

  A data block, also called a database block, is the smallest unit of I/O to database storage. An extent consists of several contiguous data blocks. The database uses a default block size at database creation.

  After the database has been created, it is not possible to change the default block size without re-creating the database. It is possible, however, to create a tablespace with a block size different than the default block size.

Segments, extents, and data blocks are all logical structures. Only Oracle Database can determine how many data blocks are in a file. The operating system recognizes only files and operating system blocks, not the number of data blocks in an Oracle Database file. Each data block maps to one or more operating system blocks.

About Tablespaces

A database is divided into logical storage units called tablespaces, which group related logical structures (such as tables, views, and other database objects). For example, all application objects can be grouped into a single tablespace to simplify maintenance operations.

A tablespace consists of one or more physical data files. Database objects assigned to a tablespace are stored in the physical data files of that tablespace.

When you create an Oracle database, some tablespaces already exist, such as SYSTEM and SYSAUX.

Tablespaces provide a means to physically locate data on storage. When you define the data files that comprise a tablespace, you specify a storage location for these files. For example, you might specify a data file location for a certain tablespace as a designated host directory (implying a certain disk volume) or designated Oracle Automatic Storage Management disk group. Any schema objects assigned to that tablespace then get located in the specified storage location. Tablespaces also provide a unit of backup and recovery. The backup and recovery features of Oracle Database enable you to back up or recover at the tablespace level.

Table 6-1 describes some tablespaces included in the database.

You can create new tablespaces to support your user and application data requirements. During tablespace creation, you set the following parameters:

• Locally Managed Tablespaces

• Tablespace Types

• Tablespace Status

• Autoextend Tablespace

• Encrypted Tablespaces

About Other Storage Structures

Other storage structures that can exist in an Oracle database include the initialization parameter file, the password file, and backup files.

Initialization Parameter File

Initialization parameters are used by the Oracle instance at startup to determine the run-time properties and resources for the database. Some parameters can be set or modified while the database is running. Other initialization parameters require the database to be restarted for the changes to take effect. See "Viewing and Modifying Initialization Parameters".

Password File

A database can use a password file to authenticate administrative users with SYSDBA or SYSOPER connection privileges. A password file is required for remote connections to the database as SYSDBA or SYSOPER. These privileges enable a DBA to start and shut down the database and perform other high-level administrative tasks. This password file is outside of the database itself, thereby enabling the authentication of a DBA when the database is not yet started. (A DBA must authenticate before starting the database.)

When you invoke DBCA as part of the Oracle Database installation process, DBCA creates a password file with one entry: the SYS user. Granting SYSDBA to a user adds that user to the password file automatically.

Backup Files

Backup files are not technically database files, but are copies of the database in some form that can be used to recover the database if a failure causes loss of data.

Viewing Control File Information

You can use Database Control to view location and status information about control files.

To view control file information:

1. Go to the Database Home page.

   See "Accessing the Database Home Page".

2. At the top of the page, click Server to view the Server subpage.

3. In the Storage section, click Control Files.

   The Control Files page appears, showing the General subpage.

   This page shows whether your database has a multiplexed control file. The Advanced and Record Section subpages give you more detailed information about your control files.

Viewing Online Redo Log File Information

You use Database Control to view status and multiplexing information about online redo log files.

To view online redo log file information:

1. Go to the Database Home page.

   See "Accessing the Database Home Page".

2. At the top of the page, click Server to view the Server subpage.

3. In the Storage section, click Redo Log Groups.

   The Redo Log Groups page appears. This page shows the attributes of the online redo log groups for your database.

   When an online redo log group contains only one member, it is not multiplexed. Note the Status attribute for the online redo log groups. The online redo log group with status Current is the one currently being written to disk by the log writer.

4. (Optional) In the Group column, click a group number to view information about online redo log group members.

Viewing Archived Redo Log File Information

You use Database Control to view status information about archived redo log files.

To view archived redo log file information:

1. Go to the Database Home page.

   See "Accessing the Database Home Page".

2. At the top of the page, click Server to view the Server subpage.

3. In the Storage section, click Archive Logs.

   The Archive Logs page appears.

Viewing Data File Information

You use Database Control to view status and location information about data files.

To view data file information:

1. Go to the Database Home page.

   See "Accessing the Database Home Page".

2. At the top of the page, click Server to view the Server subpage.

3. In the Storage section, click Datafiles.

   The Datafiles page appears.

4. (Optional) Select a data file, and then click View to display more attributes for the data file.

5. (Optional) Click the name of a tablespace in the Tablespace column to display the attributes of the tablespace associated with the data file.

Viewing Tablespace Information

You use Database Control to view configuration, size, and status information about tablespaces.

To view tablespace information:

1. Go to the Database Home page.

   See "Accessing the Database Home Page".

2. At the top of the page, click Server to view the Server subpage.

3. In the Storage section, click Tablespaces.

   The Tablespaces page appears.

Creating a Tablespace

You can create additional tablespaces to store user data, so that not all data is stored in the USERS tablespace. The following are some reasons to create additional tablespaces:

• For certain users, groups of users, or applications, it may be convenient to keep all application data in a separate tablespace or set of tablespaces for backup and recovery or maintenance reasons. For example, suppose you must recover all application data from backup due to a hardware or software failure, and you want to perform an offline recovery. If the application data is kept in a separate tablespace, then you can take just that tablespace offline and recover it, without affecting the operation of other database applications.

• Some applications, such as those with large partitioned tables, may benefit from distributing data across multiple tablespaces. This approach allows the optimal use of the available storage because frequently accessed data can be placed on high performance disks, and infrequently retrieved data can be placed on less expensive storage.

To create a tablespace:

1. Go to the Database Home page.

   See "Accessing the Database Home Page".

2. At the top of the page, click Server to view the Server subpage.

3. In the Storage section, click Tablespaces.

   The Tablespaces page appears.

   
   Description of the illustration tablespaces.gif
   
   

4. Do one of the following:

   1. To create a new tablespace, click Create.

   2. To create a tablespace that is similar to an existing tablespace, in the Select column, select the tablespace whose attributes you want to reproduce. In the Actions list, select Create Like and then click Go.

   The Create Tablespace page appears, showing the General subpage.

   
   Description of the illustration create_tablespace.gif
   
   

5. In the Name field, enter a name for the tablespace.

6. In the Extent Management section, select Locally Managed.

   See "Locally Managed Tablespaces".

7. In the Type section, select Permanent.

   See "Tablespace Types".

8. In the Status section, select Read Write.

   See "Tablespace Status".

9. In the Datafiles section, click Add to add one or more data files to the tablespace.

   The Add Datafile page appears.

   Note:

   If you select Use bigfile tablespace, then the tablespace can have only one data file. Bigfile tablespaces are used with very large databases that use Automatic Storage Management or other logical volume managers that support striping, RAID, and dynamically extensible logical volumes.

10. If the Storage Type is File System, then in the File Name field, enter a file name. In the File Directory and File Size fields, enter appropriate values for your data file location and initial size.

11. In the Storage section, complete the following steps:

    1. Select Automatically extend data file when full (AUTOEXTEND).

    2. Set a suitable increment, such as 10 MB.

       This is the amount of disk space that is added to the data file when it needs more storage space.

    3. For Maximum File Size, do one of the following, depending on available storage:

       • Select Unlimited to permit the file to increase without limits.

       • Select Value, and then enter a value in KB, MB, GB, or TB.

12. Click Continue.

    The Create Tablespace page returns.

13. (Optional) Toward the top of the page, click Storage to view the Storage subpage. Examine all of the default settings on this subpage.

14. Click OK to add the tablespace.

    The Tablespaces page returns, showing the newly created tablespace.

Modifying a Tablespace

You can use Database Control to modify a tablespace. For example, you can extend it by increasing data file sizes or adding another data file, set it to automatically extend, change its space usage alert thresholds, or change its status to Offline. When you get a critical or warning alert, you might have to extend a tablespace if the alert is related to a space limitation or you might have to take the tablespace offline for recovery if the alert is related to corrupted data or other serious errors.

This section contains the following topics:

• Setting a Tablespace to Automatically Extend

• Changing Space Usage Alert Thresholds for a Tablespace

• Taking a Tablespace Offline

Dropping a Tablespace

After a tablespace has been dropped (deleted), the objects and data in it are no longer available. To recover them can be a time-consuming process. Oracle recommends performing a backup before and after dropping a tablespace.

To drop a tablespace:

1. Go to the Database Home page.

   See "Accessing the Database Home Page".

2. At the top of the page, click Server to view the Server subpage.

3. In the Storage section, click Tablespaces.

   The Tablespaces page appears.

4. Select the tablespace to drop, and then click Delete.

   Database Control asks for confirmation to delete the tablespace and gives you the option to also delete the associated data files from the disk.

5. Click Yes to remove the tablespace.

   A confirmation is displayed and the deleted tablespace no longer appears on the Tablespaces page.

Reclaiming Unused Space

This section provides background information about reclaimable unused space in the database, and provides instructions about how to reclaim this space. It contains the following topics:

• About Reclaimable Unused Space

• About the Segment Advisor

• Viewing Segment Advisor Recommendations and Reclaiming Space

• Running the Segment Advisor Manually

Multiplexing the Online Redo Log

Oracle recommends that you multiplex the online redo log. Multiplexing provides better protection for data if an instance or media failure occurs.

To multiplex your online redo log, you must add members to each online redo log group. It is not required that online redo log groups be symmetrical, but Oracle recommends that your groups all have the same number of members. A database must have a minimum of two online redo log groups.

To multiplex the online redo log:

1. Go to the Database Home page.

   See "Accessing the Database Home Page".

2. At the top of the page, click Server to view the Server subpage.

3. In the Storage section, click Redo Log Groups.

   The Redo Log Groups page appears.

4. Select a group and click Edit.

   The Edit Redo Log Group page appears.

5. Click Add.

   The Add Redo Log Member page appears.

6. If the Storage Type is set to File System, then in the File Name field, enter a file name for the new redo log member.

   For example, if your existing member file name is REDO01.log, then you might name this member REDO01a.log.

7. In the File Directory field, enter the directory where you want the data file to be stored on disk.

   You can create this file in the same directory as the other member of the redo log file group, but it is recommended that you store members on separate disk drives. That way, if there is a drive failure, then you still have access to one member.

8. Click Continue, and then click Apply to accept your changes.

   A confirmation message appears.

9. At the top of the page, click the Redo Log Groups link to return to the Redo Log Groups page.

10. Repeat Step 4 through Step 9 for every existing log group.

Switching a Log File

When a log switch occurs, the log writer (LGWR) process stops writing to the current online redo log group and starts writing to the next available redo log group. After a log switch, the current online redo log group becomes inactive, and the next available online redo log group becomes the current online redo log group.

You can force a log switch to make the current redo group inactive and available for redo log maintenance operations. Forcing a log switch is useful in configurations with large redo log files that take a long time to fill. For example, you might want to:

• Drop the current redo group, but are not able to do so until the group is inactive

• Archive the current online redo log group members immediately, even though they are not yet completely filled

To switch a log file:

1. Go to the Database Home page.

   See "Accessing the Database Home Page".

2. At the top of the page, click Server to view the Server subpage.

3. In the Storage section, click Redo Log Groups.

   The Redo Log Groups page appears.

4. In the Actions list, select Switch logfile, and then click Go.

   A confirmation message appears. The status of the group that had been Current changes to Active, and the status of the next group in the list changes from Inactive to Current.

About Undo Data

When a transaction modifies data, Oracle Database copies the original data before modifying it. The original copy of the modified data is called undo data. Saving this information is necessary for the following reasons:

• To undo any uncommitted changes made to the database if a rollback is necessary. A rollback can be needed because a user wants to undo the changes of a misguided or unintentional transaction, or it can be part of a recovery operation.

• To provide read consistency, which means that each user can get a consistent view of data, even while other changes may be occurring against the data. With read consistency, a user session does not see uncommitted changes made in other user sessions (sometimes referred to as dirty reads). For example, if a user issues a query at 10:00 a.m. and the query lasts for 15 minutes, then the query results reflect the entire state of the data at 10:00 a.m., regardless of update or insert operations performed by other users after the query started.

• To enable certain Oracle Flashback features, such as Oracle Flashback Query and Oracle Flashback Table, which enable you to view or recover data to a previous point in time.

Undo Tablespace

With automatic undo management, undo data is stored in an undo tablespace. Undo tablespaces have additional properties beyond those of permanent tablespaces. There can be multiple undo tablespaces, but only one can be active for an Oracle instance.

When you create the database using Database Configuration Assistant (DBCA), it creates an autoextending undo tablespace named UNDOTBS1, with a maximum extension size of 32,767 MB.

Undo Retention

Oracle Database automatically ensures that undo data that is in use by an active transaction is never overwritten until that transaction has been committed. After the transaction has been committed, the space occupied by that undo data can be reused, or overwritten. In this case, that undo data could be overwritten if space in the undo tablespace becomes scarce.

Even after a transaction has been committed, it is useful to retain (not overwrite) its undo data, to ensure the success of Oracle Flashback features and for read consistency for long-running queries. To this end, the database maintains and automatically tunes an undo retention period. Committed undo data whose age is less than the undo retention period is retained for use by queries or Oracle Flashback operations.

About Managing Undo Data

Although by default Oracle Database manages undo data and the undo tablespace automatically, if your installation uses Oracle Flashback features, then you may have to perform some undo management tasks to ensure the success of these operations.

Oracle Flashback operations resulting in snapshot too old errors indicate that you must intervene to ensure that sufficient undo data is retained to support these operations.

The following methods better support Oracle Flashback operations:

• Set the minimum undo retention period for the autoextending tablespace to be as long as the longest expected Oracle Flashback operation.

  You achieve this goal by setting the UNDO_RETENTION initialization parameter. See Oracle Database Administrator's Guide for details.

• Change the undo tablespace to a fixed size.

  For an autoextending undo tablespace, Oracle Database always automatically tunes the undo retention period to be slightly longer than the longest-running active query. However, this autotuned retention period may be insufficient to accommodate Oracle Flashback operations. If the undo tablespace has autoextending disabled, or has a fixed size, then Oracle Database uses a different method for tuning the undo retention period to better accommodate Oracle Flashback operations.

  To change the undo tablespace to a fixed size, you must choose a tablespace size that is sufficiently large. If you choose an undo tablespace size that is too small, then the following errors could occur:

  • DML could fail because there is not enough space to accommodate undo data for new transactions.

  • Long-running queries could fail with a snapshot too old error, which means that there was insufficient undo data for read consistency.

  Oracle Enterprise Manager Database Control (Database Control) includes an Undo Advisor to help you determine the minimum size for the fixed size of the undo tablespace. See "Computing the Minimum Undo Tablespace Size Using the Undo Advisor".

Viewing Undo Information

You can use the Automatic Undo Management page to view the following information about your undo configuration:

• Name and current size of the undo tablespace

• Autoextend tablespace setting (Yes or No)

• Current autotuned undo retention period

To view undo information:

1. Go to the Database Home page.

   See "Accessing the Database Home Page".

2. At the top of the page, click Server to view the Server subpage.

3. In the Database Configuration section, click Automatic Undo Management.

   The Automatic Undo Management page appears.

Computing the Minimum Undo Tablespace Size Using the Undo Advisor

If you must change the undo tablespace to a fixed size, then use the Undo Advisor to help determine the minimum required size. You can also use the Undo Advisor to set the minimum undo retention period.

To configure the undo tablespace to have a fixed size, Oracle suggests that you first allow enough time after database creation to run a full workload, thus allowing the undo tablespace to grow to its minimum required size to handle the workload. Then, you can use the Undo Advisor to determine the best size to configure the undo tablespace to allow for future long-running queries and Oracle Flashback operations.

To compute the minimum undo tablespace size using the Undo Advisor:

1. Go to the Automatic Undo Management page.

   See "Viewing Undo Information".

2. Decide whether you want to compute the minimum size of the undo tablespace based on statistics gathered over a designated time period (such as the last 7 days), or based on an undo retention period of a duration that you choose.

   The automatically gathered statistics include the duration of the longest-running query and the undo generation rate. Computing the minimum undo tablespace size based on these statistics is advisable if you do not use Oracle Flashback features or if you do not expect future long-running queries to exceed the duration of previous long-running queries.

   If you prefer to choose and enter the duration of a desired undo retention period, then the duration must be based on your expectations of the duration of future long-running queries or Oracle Flashback operations.

3. To compute the minimum undo tablespace size based on statistics gathered over a period, complete the following steps:

   1. In the Analysis Period section, in the Analysis Time Period list, select the desired analysis time period.

      If you select Customize Time Period, then a page appears that enables you to enter the starting and ending date and time for the period.

   2. Select Automatically chosen based on longest query in analysis period.

   3. Click Run Analysis.

      The minimum required undo tablespace size is displayed in the Analysis Results section.

   4. (Optional) Click Show Graph to view a graph of the analysis results.

4. To compute the minimum undo tablespace size based on a duration that you enter, complete the following steps:

   1. In the Analysis Period section, select Specified manually to allow for longer duration queries or flashback.

   2. In the Duration field, enter the desired duration of the undo retention period in seconds, minutes, hours, or days.

      You may compute this duration as follows:

      • Determine the duration of the expected longest-running query for your workload.

        You can base your determination on the longest-running query recorded during a previous workload period. This information is available from the System Activity subpage of the Automatic Undo Management page. You choose the analysis period for this subpage by selecting from the Analysis Time Period list on the General subpage. You can choose a custom analysis period that corresponds to your typical workload period.

      • Determine the longest duration that is expected for an Oracle Flashback operation.

      • Take the maximum of these two durations.

   3. In the Analysis Period section of the General subpage, click Run Analysis.

      The minimum required undo tablespace size is displayed in the Analysis Results section.

   4. (Optional) Click Show Graph to view a graph of the analysis results.

      
      Description of the illustration undo_management_graph.gif
      
      

5. (Optional) Click the tick-mark, or point, on the curve that corresponds to the desired undo retention period.

   The Duration field changes to match the selected undo retention period, and the Minimum Required Undo Tablespace Size field above the graph changes to reflect the matching required size.

6. (Optional) Click Apply.

   The minimum undo retention period is set to the value of the Duration field.

Changing the Undo Tablespace to a Fixed Size

You change the undo tablespace to a fixed size to prevent the tablespace from growing too large or to better support Oracle Flashback operations.

To change the undo tablespace to a fixed size:

1. On the Automatic Undo Management page, after determining the minimum required undo tablespace size, click Edit Undo Tablespace.

   The Edit Tablespace page appears, displaying the properties of the undo tablespace.

2. In the Datafiles section, click Edit.

   The Edit Datafile page appears.

3. In the File Size field, enter the computed minimum size for the undo tablespace.

   See "Computing the Minimum Undo Tablespace Size Using the Undo Advisor".

4. In the Storage section, deselect Automatically extend data file when full (AUTOEXTEND).

5. Click Continue.

   The Edit Tablespace page returns.

6. Click Apply.

   A confirmation message appears.