6 Managing Database Storage Structures

The Control File

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 this, Oracle recommends that the control file be multiplexed. In other words, it should have multiple identical copies. For databases created using DBCA, three copies of the control file are automatically created.

If any control file fails, then your database becomes unavailable. But as long as you still have any of the control file copies intact, 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. See Oracle Database Administrator's Guide for detailed information about control files.

Click the Controlfiles link. The Controlfiles General page shows that your database has a multiplexed control file. There are two other property pages, in addition to the General page, for viewing control file information. The Advanced and Record Section pages give you more detailed information about your control file. Explanations of this information are contained in the online help.

Redo Log Files

Every Oracle database has a set of two or more 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 (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 copies of the 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, and 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, then it writes to the next group. This is done in a circular fashion, where the oldest group is overwritten by the most recent redo records.

Click Redo Log Groups. The Redo Log Groups page appears, showing the attributes of the redo log groups for your database. A database typically consists of three groups. Note the status attribute for the redo log groups. The CURRENT status group is the one currently being written to disk by the log writer process.

You can see that each group contains only one member. This is because the database does not have a multiplexed redo log. Select a redo log group and click View to see information about the redo log group members.

Archive Log Files

When you archive your redo log, you write redo log files to another medium 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 information that can be saved and used for recovery. Archiving can be either enabled or disabled for the database, but Oracle recommends you enable archiving.The status of a log group that is actively being archived is ACTIVE.

Click Archive Logs. The Archive Logs page is displayed. This page lets you view archive log files and their attributes.

For placing the database into archive log mode, or adding archive log locations, see "Configuring Your Database for Basic Backup and Recovery".

Rollback Segments

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

Click Rollback Segments. You see the attributes of the system rollback segment. The system rollback segment is self managing.

Tablespaces

A database consists of one or more tablespaces. A tablespace is a logical structure, or container, created by and known only to the Oracle database server in which database objects are stored. A tablespace consists of one or more datafiles or tempfiles.

There are various types of tablespaces, including undo tablespaces, temporary tablespaces, and permanent tablespaces.

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.

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. After temporary tablespaces, tablespaces of type TEMPORARY are the next best tablespaces to use for sort operations. Space management is dictionary managed. Permanent tablespaces (which are not of type TEMPORARY) are least efficient for performance of disk sorts.

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, and indexes are stored in index segments.

  An extent is a contiguous set of data blocks (defined next) 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. However, it is possible to create tablespace with a block size different than the default block size. For details on how to create tablespaces with nondefault (or non-standard) block size, see Oracle Database Administrator's Guide.

Click Datafiles. Your database datafiles are displayed. Select a datafile and click View to see more attributes for the datafile, or select a tablespace link to see the attributes of the owning tablespace.

Other Storage Structures

Other storage structures that can exist in an Oracle database include the following:

Viewing Tablespaces in Your Database

You can see all tablespaces in your database. From the Administration page, click Tablespaces under Storage. This takes you to the Tablespaces page.

The Results page shows all tablespaces, including the following information about each: type of tablespace, type of extent management, type of segment management, status, size, and percent used. Click the tablespace name for more information.

Creating a Tablespace

A particular 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 will also familiarize you with the various attributes that you can set, and possibly later modify, for a tablespace.

1. From the Administration page, click Tablespaces under Storage. This takes you to the Tablespaces page.

2. Click Create. Or, if you want to create a tablespace that is like an existing tablespace, select an existing tablespace. Then from the Actions menu, select Create Like.

   The Create Tablespace General page appears.

   Did you notice the other options that are available from the Actions pull down menu? These are shortcuts for various tablespace operations.

3. Enter a name for the tablespace. For example: TB1

4. Under the Extent Management heading, select Locally Managed.

   A locally managed tablespace's extents are managed locally and efficiently within the tablespace by Oracle. For a dictionary managed tablespace, you must more actively manage extents and data dictionary access is required for tracking them. Dictionary managed tablespaces are being deprecated. Oracle does not recommend their use.

5. Under the Type heading, select appropriate type.

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

   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 tablespaces are used by the system to store undo information. You might want to create another undo tablespace, but only one undo tablespace can be the active one.

   Again, you should not need to create additional undo tablespaces. However, if you want to switch the undo tablespace used by the database instance, 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.

6. Under Status, select Read Write.

   Read-write status means users can read and write to the tablespace after it is created. This is the default.

   If the tablespace was created read-only, then the tablespace could not 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.

   If the tablespace was created offline, then no users could have access to it. You would probably not create a tablespace as offline, but rather you would change its status to offline when, for example, you wanted to perform some maintenance on its underlying files.

7. In the Datafiles section of the page, you can add one or more datafiles to the tablespace. However, if you select Use bigfile tablespace, then the tablespace can have only one datafile. Click Add.

   Bigfile tablespaces are used with ultralarge databases where Oracle's Automatic Storage Management or other logical volume managers support striping or RAID, and dynamically extensible logical volumes.

8. In the Add Datafiles page, enter a file name. For example: TS101.dbf. Use the File Directory and File Size defaults.

   At least one datafile is required for the SYSTEM and SYSAUX tablespaces of a database. Your database should contain several other tablespaces with their associated datafiles or tempfiles. The number of datafiles that you anticipate creating for your database can affect the settings of initialization parameters and the specification of CREATE DATABASE statement clauses. Be aware that your operating system might impose limits on the number of datafiles contained in your Oracle database. Also consider that the number of datafiles, and how and where they are allocated can affect the performance of your database.

9. To allow Oracle to automatically grow the datafile, under Storage, select Automatically extend datafile when full (AUTOEXTEND) and 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 General page.

10. Click the Storage property page.

    The Create Tablespace Storage page appears.

    You can manually resize a datafile with the ALTER DATABASE statement. This lets you add more space to your database without adding more datafiles, if you are concerned about reaching the maximum number of datafiles allowed in your database. For a bigfile tablespace, use the ALTER TABLESPACE statement. You cannot add a datafile to a bigfile tablespace. Manually reducing the sizes of datafiles lets you reclaim unused space in the database. This is useful for correcting errors in estimates of space requirements.

11. Leave all of the defaults set on the Storage page. See online help for more information on these settings.

12. Click the Thresholds property page.

    The Thresholds page appears. 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.

13. After determining thresholds, click OK to add the tablespace. The Tablespaces page appears where you can see your new tablespace in the Results section.

Modifying a Tablespace

With Enterprise Manager, you can modify a tablespace, such as 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 offline a tablespace to perform recovery operations on 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 Administration page, click Tablespaces under Storage. This takes you to the Tablespaces page.

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 M. You can optionally set a maximum file size or set file size to unlimited depending on your resources.

   You can add datafiles and associate them with a tablespace with SQL statements. You can either specify the file specifications for the datafiles being created, or you can use the Oracle Managed Files feature to create files that are created and managed by the database server.

   You can also use SQL statements to manually resize a datafile. This lets you add space to your database without adding datafiles, if you are concerned about reaching the maximum number of datafiles allowed in your database. Manually reducing the sizes of datafiles lets you reclaim unused space in the database. This is useful for correcting errors in estimates of space requirements. For more information on managing datafiles, see Oracle Database Administrator's Guide.

5. Click Continue. This brings you back to the Edit Tablespace page. Click Apply. A confirmation message should appear.

Similarly, you can change tablespace thresholds by selecting a tablespace on the Tablespaces page and navigating to the Threshold property page. Here you can alter your space thresholds that generate the warning and critical alerts. For more information about thresholds and alerts, see Chapter 10, "Monitoring and Tuning the Database".

To offline a tablespace, on the Edit Tablespace page, under Status, select Offline and click Apply.

Dropping a Tablespace

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

1. From the Administration page, click Tablespaces under Storage. This takes you to the Tablespaces page.

2. Select the tablespace that you want to drop, and click Delete. Enterprise Manager will ask for confirmation that you want to delete the tablespace and associated datafiles from the operating system.

Reclaiming Wasted Space

Over time, 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.

Oracle lets you reclaim wasted space through a shrink operation. In a shrink operation, data is compacted to the front of the table. Free space can either be given back to the tablespace or kept in the table for future inserts. A shrink operation on a table does not affect DML to the table during the operation.

About Undo

Undo data stores changes made to the database by transactions. Undo is necessary for the following:

• 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. This 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 am and the query lasts for 15 minutes, the query results should reflect the entire state of the data at 10:00 am regardless of any updates or inserts by other users during the 15 minute query.

• To enable Oracle's flashback features, which enables you to view or recover data to a previous point in time. These features include Flashback Query and Flashback Table.

Undo Tablespace Size and Retention Time

In order for changes to be rolled back or undone, Oracle makes a copy of the original data before modifying it. The original copy of the modified data is called undo data. Undo data is stored in a logical database structure called an undo tablespace.

The undo tablespace is of a finite size. Space usage within the tablespace is such that records can be overwritten. The undo data needs to be saved at least until the transaction has been committed. Until then the undo data is said to be in the active state. The amount of space available in the undo tablespace should, therefore, be at least large enough to hold the active undo data generated by currently active transactions. Otherwise some of these transactions may fail. After the 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, the undo tablespace should be big enough to hold 15 minutes worth of undo information.

To control the retention of undo records, Oracle maintains an undo retention period, which in turn affects the size of the undo tablespace; the longer the retention period, the bigger the tablespace. The undo retention period should be at least as long as your longest running query. In addition, if you plan to use flashback features to recover from human errors such as unintentional changes, the undo retention should be set equal to the amount of time in the past you want to go back. For more details on Flashback features, see Oracle Database Administrator's Guide.

Automatic Undo Management

Oracle Database automatically determines how long undo data should be preserved after the transaction has been committed based on queries running against the database. During this time, the undo data is said to be in the unexpired state. Oracle will keep the undo data in the unexpired state as long as needed by queries currently running against the database or as long as any low undo retention threshold (whichever is longer). After this time, the state of undo data changes to expired. Undo data is a good candidate for overwriting only when it is in the expired state.

If the undo tablespace does not have adequate free or expired space to store active undo data generated by current transactions, Oracle may be forced to overwrite the unexpired undo data. As this may cause your queries to fail, this is not a desirable situation. In order to avoid this, Oracle recommends you allow the size of the undo tablespace to be automatically increased by enabling auto-extend. See "Extending the Undo Tablespace".

If you cannot enable auto extension for any reason and choose to size the undo tablespace manually, Oracle Database includes an undo advisor to help you determine the optimal size. See "Using the Undo Advisor" on page 6-14.

Undo Management with Enterprise Manager

With Enterprise Manager, you can access the Undo Management page from the database Administration page by selecting Undo Management under the Instance heading.

You can use the Undo Management page to view undo configuration, including the auto-tuned undo retention period, its low threshold retention period, and the name and size of the undo tablespace. Auto-tuning of undo retention cannot be disabled.

You can also use this page to set the low threshold retention or to extend your tablespace. While Oracle automatically tunes the undo retention period, low threshold undo retention lets you define the floor value under which Oracle should never bring down the undo retention. When you create a database, the low threshold undo retention is set to a default value. You may need to alter this value only when you need to build a recovery strategy using Flashback Query.

To change the low threshold, click its value. The Undo Advisor page appears, which can help you determine a better setting. See "Using the Undo Advisor" on page 6-14 for more information.

The Configuration section also shows if auto-extending the tablespace is enabled, which is the default. When auto-extend tablespace is enabled, Oracle automatically increases the size of the undo 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 will succeed by guaranteeing the undo required for such queries.

The methods for determining the undo retention period are described in the following sections:

• Using Oracle Recommendations for Managing Undo

• Using the Undo Advisor

Using Oracle Recommendations for Managing Undo

In the Recommendations section of the Undo Management page, you can specify a past time period for Oracle to analyze system activity. The current recommendations for your undo configuration are summarized. To update the analysis, click Update Analysis.

If Oracle recommends that you extend the undo tablespace, you can do so. See "Extending the Undo Tablespace" for more information.

To obtain more detailed information about the recommendations, click Undo Advisor. See also "Using the Undo Advisor" on page 6-14.

Using the Undo Advisor

You can use the undo advisor to advise on the undo tablespace size and the low threshold undo retention setting. To navigate to the Undo Advisor page, do one of the following from the Undo Management page:

• Click Undo Advisor

• Click the value adjacent to Low Threshold Undo Retention.

The top of the page shows the current auto-tuned undo retention time and undo tablespace size.

The Undo Advisor helps you answer the following questions about your undo configuration:

1. What is the maximum undo retention time required to guarantee successful query? This time period corresponds to the duration of your longest running query. You can find Longest Running Query on the Undo Management page under System Activity and Tablespace Usage.

2. Does my database have enough space to accommodate the maximum undo retention given the current tablespace configuration? To figure this out, note the Best Possible Undo Retention under analysis on the Undo Advisor page. If this time period is longer than your longest running query, your undo tablespace is adequately configured.

3. What if the best possible retention time is less than my longest running query? If this is the case, your undo tablespace is too small. You need to either set your tablespace to auto-extend or manually extend it. To figure out the required new size of your tablespace, on the Undo Advisor page New Undo Retention field, plug in the value of your longest running query. Enter an appropriate Analysis Time Period and click Update Analysis and Graph. Under Analysis, note the Required Tablespace size for New Undo Retention. To learn how to alter your tablespace, "Extending the Undo Tablespace".

4. How do I configure undo to accommodate Flashback operations? If you are interested in using flashback features such as Flashback Query or Flashback Table, you can use the Undo Advisor to advise on how to configure your tablespace and undo retention. In order 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 low threshold parameter, which determines the lowest value for automatic undo tuning. For example, if the low threshold is set to 15 minutes, 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, set the low threshold to 8 hours. To learn how to set a new retention time, see "Setting New Threshold Undo Retention Time".

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 and Best Possible Retention.