6 Managing Database Storage Structures

Control Files

A control file tracks the physical components of the database and other control information. It is essential to the functioning of the database. Because of the importance of the control file, Oracle recommends that the control file be multiplexed. In other words, the control file should have multiple identical copies. For databases created with DBCA, three copies of the control file are automatically created.

If any control file fails, then your database becomes unavailable. As long as you still have a control file copy intact, however, you can shut down your database and re-create the failed control file from one of the other control files, then restart your database. Another option is to delete the failed control file from the CONTROL_FILES initialization parameter and restart your database with the remaining control files. There are other options, depending upon circumstances.

Online Redo Log Files

Every Oracle database has a set of two or more online redo log files. The set of 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 primary function of the redo log is to record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost. To protect against a failure involving the redo log itself, Oracle allows a multiplexed redo log so that two or more identical copies of the online redo log can be maintained on different disks.

Your database's redo log consists of groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is said to be a member of that group. Each group is defined by a number, such as group 1.

The database log writer process writes redo records from the memory buffer to a redo log group until the group fills up or a log switch operation is requested. It then writes to the next group. The log writer performs this action in a circular fashion so that the oldest group is overwritten by the most recent redo records.

Archive Log Files

When you archive your redo log, you write redo log files to another location prior to their being overwritten. This location is called the archive log. You can archive to multiple locations, including a standby database.

These copies of redo log files extend the amount of redo data that can be saved and used for recovery. Archiving can be either enabled or disabled for the database, but Oracle recommends that you enable archiving.

Rollback Segments

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

Tablespaces

A database consists of one or more tablespaces. A tablespace is a logical structure created by and known only to the Oracle database server. A tablespace consists of one or more datafiles or tempfiles.

There are various types of tablespaces, including the following:

• Undo tablespace

  A database running in automatic undo management mode transparently creates and manages undo segments. You can create an undo tablespace and determine the maximum retention time for undo data kept in that tablespace.

• Permanent tablespaces

  These tablespaces are used to store permanent objects and data.

• Temporary tablespaces

  Temporary tablespaces improve the concurrence of multiple sort operations, reduce their overhead, or avoid Oracle space management operations altogether. Temporary tablespaces are the most efficient tablespaces for disk sorts. Space management (extent allocation and deallocation) is locally managed.

Temporary and permanent tablespaces can be assigned at the user level. You can create a default temporary tablespace at database creation time. This tablespace is used as the default temporary tablespace for users who are not otherwise assigned a temporary tablespace.

Datafiles

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

Datafiles can be broken down into the following components:

• Segments and extents

  A segment contains a specific type of database object. For example, tables are stored in data segments, whereas indexes are stored in index segments.

  An extent is a contiguous set of data blocks within a segment. Oracle initially allocates an extent of a specified size for a segment, but if that extent fills, then more extents can be allocated.

• Data block

  Data blocks, also called database blocks, are the smallest unit of I/O to database storage. An extent consists of several contiguous data blocks. The default database block size is specified by a DBA at database creation or defaulted by Oracle.

  After the database has been created, it is not possible to change the default block size without re-creating the database. Nevertheless, it is possible to create tablespace with a block size different than the default block size. For details on how to create tablespaces with a nondefault or nonstandard block size, see Oracle Database Administrator's Guide.

Other Storage Structures

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

Viewing Tablespaces in Your Database

You can view all tablespaces in your database. From the Storage section of the Administration page, click Tablespaces to open the Tablespaces page. This page shows the following information about each tablespace:

• Type of tablespace

• Type of extent management

• Type of segment management

• Status

• Size

• Percent used

Click the tablespace name for more information.

Creating a Tablespace

A user, groups of users, or an application may require its own tablespace for creating schema objects, rather than using the default USER tablespace. To create a tablespace, follow the steps listed in this section. This exercise also familiarizes you with the various attributes that you can set, and possibly later modify, for a tablespace.

1. From Storage section of the Administration page, click Tablespaces.

   The Tablespaces page appears.

2. Click Create to open the Create Tablespace General page.

   Alternatively, to create a tablespace that is like an existing tablespace, select an existing tablespace. From the Actions list, select Create Like and then click Go. The other options that are available from the Actions menu are shortcuts for various tablespace operations.

3. Enter a name for the tablespace. For example, enter TB1.

4. In the Type section, select one of the following types:

   • Permanent

     Permanent tablespaces store permanent database objects created by the system or users.

   • Temporary

     Temporary tablespaces are for storing temporary segments, as would be created when SQL statements perform sorts. The preconfigured database already has a temporary tablespace. You would create another temporary tablespace if you were creating a temporary tablespace group. Under normal circumstances, you should not need to create additional temporary tablespaces.

   • Undo

     Undo tablespaces are used by the system to store undo data. You might want to create another undo tablespace, but only one undo tablespace can be the active one.

     You should not need to create additional undo tablespaces. If you want to switch the undo tablespace used by the database instance, however, then you can create a new undo tablespace and instruct the database to use it instead of the undo tablespace currently in use. The undo tablespace no longer in use can be dropped later.

5. In the Status section, select Read Write from among the following status options:

   • Read Write

     Users can read and write to the tablespace after it is created. This is the default.

   • Read Only

     If the tablespace is created read-only, then the tablespace cannot be written to until its status is changed to Read Write. You would probably not create a tablespace as read-only, but rather change it to that status after you have written data to it that you do not want modified.

   • Offline

     If the tablespace is created offline, then no users can access it. You would probably not create a tablespace as offline, but rather you would change its status to offline to perform some maintenance on its underlying files.

6. In the Datafiles section of the page, click Add to add one or more datafiles to the tablespace.

   Note that if you check Use bigfile tablespace, then the tablespace can have only one datafile. 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.

7. In the Add Datafiles page, enter a file name. For example, enter TB1.dbf. Accept the File Directory and File Size defaults.

8. In the Storage section, select Automatically extend datafile when full (AUTOEXTEND) to allow Oracle database to automatically expand the datafile. Specify an amount in the Increment field by which you want to extend the datafile each time it fills. Leave the Maximum File Size set to Unlimited. Click Continue.

   You are returned to the Create Tablespace page.

9. Click Storage to open the Create Tablespace Storage property page. Leave all of the defaults set on the Storage page. See online Help for more information on these settings.

10. Click OK to add the tablespace. You can see your new tablespace in the Tablespaces page.

Modifying a Tablespace

With Enterprise Manager, you can modify a tablespace. For example, you can extend it, set it to automatically extend, change its space thresholds, or change its status to offline. A tablespace might need extending when you get a critical and warning alert. You might need to take a tablespace offline to recover it.

This section shows you how to set a tablespace to automatically extend when it reaches its limit. To enable autoextend tablespace, do the following:

1. From the Storage section of the Administration page, click Tablespaces.

   The Tablespaces page appears.

2. Select a tablespace to autoextend, such as USERS, and click Edit.

   The Edit Tablespace: USERS General property page appears.

3. Select the datafile associated with the tablespace and click Edit.

   The Edit Datafile page appears.

4. Check Automatically extend datafile when full (AUTOEXTEND). Set a suitable increment, such as 1 MB. You can optionally set a maximum file size or set file size to unlimited depending on your resources.

5. Click Continue to return to the Edit Tablespace page.

6. Click Apply.

   A confirmation message appears.

Similarly, you can change tablespace thresholds by selecting a tablespace on the Tablespaces page and clicking Edit. Click Thresholds to navigate to the Threshold property page. This page lets you set monitored thresholds for space usage. You receive a warning when the threshold is reached. You can accept the default, specify different thresholds, or disable threshold warnings. See Chapter 10, "Monitoring and Tuning the Database" for more information about monitoring the database.

You can take a tablespace offline by selecting a tablespace on the Tablespaces page and clicking Edit. Select Offline on the Edit Tablespace General page and then click Apply.

Dropping a Tablespace

After a tablespace has been dropped, 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.

1. From the Storage section on the Administration page, click Tablespaces.

   The Tablespaces page appears.

2. Select the tablespace that you want to drop and click Delete.

   Enterprise Manager asks for confirmation that you want to delete the tablespace and associated datafiles from the operating system.

3. Click Yes to remove the tablespace.

Reclaiming Wasted Space

Over time, inserts, updates, and deletes (DML operations) to objects within a tablespace can create pockets of empty space that individually are not big enough to be reused. Collectively, these pockets can combine to form large amounts of wasted space. The resulting sparsely populated objects can suffer performance degradation during queries and DML operations.

An Oracle database enables you reclaim wasted space in the following ways:

• In a shrink operation, data is compacted to the front of the table. Free space can either be returned to the tablespace or kept in the table for future inserts. A shrink operation on a table does not affect DML operations to the table during the operation.

• In a reorganization operation, the object is re-created in a different part of the tablespace. This operation has the desirable side effect of compacting the object. For this operation to succeed, however, the tablespace must already have free space equal to the size of the object.

Viewing Segment Advisor Recommendations

The Segment Advisor identifies objects such as tablespaces and tables that have space available for reclamation. It performs its analysis by examining usage and growth statistics and by sampling the data in the object. It is configured to run automatically at regular intervals, and you can also run it on demand (manually).

The regularly scheduled Segment Advisor run is known as the Automatic Segment Advisor. Results are summarized on the Space Summary section of the home page as Segment Advisor Recommendations. See Figure 6-2, "Home Page Space Summary". Click the adjacent link to open the Segment Advisor Recommendations page.

Figure 6-2 Home Page Space Summary

Description of the illustration home_page_space_summary.gif

Objects such as tablespaces, tables, and indexes are made up of one or more segments. The Segment Advisor page summarizes space usage, estimating the amount of reclaimable space for each tablespace. From this page you can drill down to the segment level. See Figure 6-3, "Segment Advisor Recommendations".

Recommendations can be either a shrink or reorganization operation. If you created the tablespace with automatic segment space management, which is the default, then the Segment Advisor recommends shrinking. If the segment is not eligible for shrink, or if the tablespace was created with manual segment space management, then the Segment Advisor recommends other reorganizing methods.

The following section describes how to view the recommendations and then shrink your objects using the shrink wizard.

To view recommendations and shrink an object:

1. From the Space Summary section of the Administration page, click the link adjacent to Segment Advisor Recommendations. Figure 6-2, "Home Page Space Summary" shows the link.

   The Segment Advisor Recommendations page appears, as shown in Figure 6-3, "Segment Advisor Recommendations".

   Figure 6-3 Segment Advisor Recommendations

   
   Description of the illustration segment_reccom.gif
   
   

2. Select a tablespace and then click Recommendation Details.

   The Recommendation Details for Tablespace page appears, as shown in Figure 6-4, "Segment Advisor Recommendation Details". This page lists the tablespace segments that are eligible for shrinking.

   Figure 6-4 Segment Advisor Recommendation Details

   
   Description of the illustration reccomend_details.gif
   
   

3. Select a segment to Shrink by using either of the following techniques:

   • Click the Shrink button next to the segment you want to shrink.

   • Select one or more segments and clicking Implement.

   The Shrink Segment: Options page appears.

   Note that reorganizing an object is also a possible recommendation. You would click the Reorg button at this step and continue with the reorganization wizard.

4. Accept Compact Segments and Release Space. This action returns freed space to the tablespace. If you do not want to release the freed space to the tablespace, then choose Compact Segments. Click Implement.

   The Shrink Segment: Schedule page appears.

5. Note your job name. Under Start, select to run the job Immediately. Note that shrink operation can be resource intensive so Oracle recommends running this operation during off-peak hours. You can alternatively select Later and schedule a better time to run the operation. Click Submit.

   The scheduler Jobs page appears and shows the status of the job.

6. Click Refresh to update the page until the status of the job is Completed. You can also click View Job Status.

About Undo Data

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

• To undo any uncommitted changes made to the database in the event that a rollback operation is necessary. A rollback operation can be the result of a user specifically issuing a rollback statement 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 uncommitted changes may be occurring against the data. For example, if a user issues a query at 10:00 a.m. and the query lasts for 15 minutes, then the query results should reflect the entire state of the data at 10:00 a.m. regardless of updates or inserts by other users during the query.

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

Undo Tablespace Size and Retention Time

Undo data is stored in a logical database structure called an undo tablespace. The undo tablespace is of finite size. Space usage within the tablespace is such that records can be overwritten as transactions occur.

Oracle saves undo data at least until the transaction has been committed. Until this time, the undo data is in the active state. Therefore, the amount of space available in the undo tablespace should be at least large enough to hold the active undo data generated by currently active transactions. Otherwise, some of these transactions might fail. When active undo data is stored in the undo tablespace, Oracle automatically ensures that it is never overwritten until the corresponding transaction has been committed.

Even after the transaction has been committed, the undo data still cannot be overwritten immediately. It is important to the success of Flashback functionality, and for read consistency for long running transactions, that records not be overwritten too soon. For example, if your longest query takes 15 minutes, then the undo tablespace should be large enough to hold 15 minutes worth of undo data.

To control the retention of undo records, Oracle maintains an undo retention period. This period indicates the amount of time that must transpire before Oracle overwrites undo data. The undo retention period affects the size of the undo tablespace; the longer the retention period, the more space is needed.

The undo retention period should be at least as long as your longest-running query. By default, Oracle automatically extends the undo tablespace to accommodate the longest-running query based on your ongoing system activity. Nevertheless, you might need to manually increase the size of your undo tablespace in the following circumstances:

• When your undo tablespace is set to a fixed size (auto-extend disabled) and long running queries are failing with snapshot too old errors.

• When you plan to use Flashback features to recover from user errors such as unintentional changes. In this case, the undo retention should be set equal to the period between the present and the earliest point of time to which you want to return. For more details on Flashback features, see Oracle Database Administrator's Guide.

Automatic Undo Management

The Oracle database automatically determines how long undo data should be preserved based on the time your queries take to run. Undo data preserved within this window of time is said to be in the unexpired state. After this time, the state of the undo data changes to expired. Undo data is a good candidate for overwriting only when it is in the expired state.

The length of time that Oracle keeps undo data in the unexpired state depends on how your tablespace is configured. When you create your database with DBCA, the undo tablespace is set by default to automatically extend itself to maintain unexpired undo for the longest-running query.

With a fixed-sized undo tablespace, Oracle automatically keeps the undo data in the unexpired state for the longest possible time for the tablespace of the specified size. If the undo tablespace does not have adequate free or expired space to store active undo data generated by current transactions, however, then Oracle might be forced to overwrite the unexpired undo data. This situation might cause long-running queries to fail with an error and an alert.

To avoid situations in which long-running queries can fail, it is recommended that you let Oracle automatically extend the size of the undo tablespace. By default, the undo tablespace is set to auto-extend when you use DBCA to configure your database.

You may choose to disable auto-extension and adjust the size of the tablespace manually. In this case, ensure that the tablespace is large enough to meet the read-consistency requirements for your longest-running query. Also, if you use Flashback features, then the tablespace must be large enough to accommodate Flashback operations. In these situations, Oracle Enterprise Manager includes an Undo Advisor to help you determine the optimal size. See "Using the Undo Advisor".

Managing Undo with Enterprise Manager

With Enterprise Manager, you can manage undo as follows:

1. From the Database Control home page, click Administration.

   The Administration property page appears.

2. In the Database Configuration section, click Undo Management.

   The Undo Management page appears, as shown in Figure 6-5.

Figure 6-5 Undo Management page

Description of the illustration undo_manage.gif

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

• Name and size of undo tablespace

• Auto-extend tablespace setting

• Auto-tuned undo retention period

• Minimum retention period

In Undo Tablespace for this Instance, the Auto-Extensible field shows Yes if auto-extending the tablespace is enabled, which is the default. When the undo tablespace is auto-extensible, Oracle automatically increases the size of the tablespace when more space is needed. By combining automatic extension of the undo tablespace with automatically tuned undo retention, you can ensure that long-running queries succeed by guaranteeing the undo for such queries.

If you have a fixed-size tablespace, then you can use this page as a starting point for determining space requirements and extending the tablespace. See "Using the Undo Advisor".

The Undo Retention Settings section describes your minimum undo retention period. While Oracle automatically tunes the undo retention period, minimum undo retention enables you to define the floor value under which Oracle should never bring down the undo retention. When you create a database, the minimum undo retention is set to a default value. You might need to alter this value to build a recovery strategy using Flashback Query. See "Setting Minimum Undo Retention Time"

The Recommendations section of the Undo Management page gives you recommendations on sizing your undo tablespace based on your system activity. Recommendations usually arise (along with alerts) when you are using a fixed-size tablespace and queries have been failing because of insufficient undo space. On rarer occasions, when using an auto-extend tablespace, the same recommendations can arise if the system has reached it maximum disk limit.

If Oracle recommends that you extend the undo tablespace, then you can use the Undo Advisor to determine a better size.

Using the Undo Advisor

The amount of undo data that can be retained depends on the size of your undo tablespace. If your tablespace is set to auto-extend, then Oracle automatically acquires space as needed. If you choose to disable auto-extend, however, then you are responsible for ensuring that the undo tablespace has enough space. In this situation, Oracle configures the undo retention to the maximum possible value for that tablespace size.

The Undo Advisor helps you analyze various scenarios to determine an appropriate undo tablespace size for different values of maximum undo retention. This analysis might be required in the following situations:

• You have a fixed-sized tablespace (auto-extend disabled) and the auto-tuned value of undo retention is not large enough to prevent queries from failing. The Undo Advisor can help you determine a better tablespace size to ensure successful completion of your queries. See "Gaining Advice from Undo Advisor".

• You use Flashback features such as Flashback Query or Flashback Table. For Flashback operations to go back in time, the database must ensure that undo data is not overwritten.

  To build a flashback recovery strategy, you can set the minimum undo retention, which determines the lowest value for automatic undo tuning. For example, if the low trheshold is set to 15 minutes, then Oracle never lowers the undo retention time to less than 15 minutes. Consequently, if your flashback recovery strategy requires you to go back 8 hours to recover from human errors, then set the minimum undo retention to 8 hours. To learn how to set a new retention time, see "Setting Minimum Undo Retention Time"

Gaining Advice from Undo Advisor

The Undo Advisor can help you determine a better size for your undo tablespace to ensure successful completion of queries. Follow these general steps to determine the required new size of your tablespace:

1. Determine the duration of your longest running query according to your application characteristics. If this duration is longer than the tuned retention, then these long-running queries will encounter an error. In this case, your undo tablespace is too small. You need to either set your tablespace to auto-extend or manually extend it.

2. In the Database Configuration section of the Administration page, click Undo Management.

   The Undo Management page appears, as shown in Figure 6-5.

3. Click Undo Advisor.

   The Undo Advisor page appears. The top of the page shows the current auto-tuned undo retention time and undo tablespace size, as shown in Figure 6-6.

4. In the New Undo Retention field of the Advisor section, plug in the value of your longest-running query.

5. In the Analysis Time Period list, select a time period that best reflects your business cycle.

   The Analysis section displays the Required Tablespace size for New Undo Retention.

For example, suppose we have a fixed size tablespace of 25 MB. We determine the longest running query is 60 minutes. We can enter this number in the New Undo Retention field as shown in Figure 6-6. The advisor tells us that we need 10 MB of space to accommodate this query. Because our tablespace is 25 MB, our undo tablespace is adequately configured.

Figure 6-6 Undo Advisor with Fixed-Sized Tablespace

Description of the illustration undo_advisor.gif

Additionally, the Required Tablespace Size by Undo Retention Length graph shows the relationship between retention period and undo tablespace size, highlighting key data points such as the Auto-tuned Undo Retention and Best Possible Undo Retention.