|
Step 6 - Managing Undo for Your Database
|
Learn Oracle - Managing Undo for Your Database
This section discusses undo management which involves storing the changes of
database transactions long enough to accommodate rollback, read consistency, and
flashback features.
When Oracle DBA install the database, Oracle automatically enables auto-tuning of the undo
retention period. Auto-tuning of undo retention enables Oracle DBA to get started on
building your database without immediately managing undo. Later as your database
activity and transaction rate increase, understanding how to manage undo will
become more useful to Oracle DBA.
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 Oracle DBA 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 Oracle DBA 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 Oracle DBA want to go back. .
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 Oracle DBA allow the size of the undo
tablespace to be automatically increased by enabling auto-extend.
If Oracle DBA cannot enable auto extension for any reason and choose to size the undo
tablespace manually, Oracle Database includes an undo advisor to help Oracle DBA determine
the optimal size.
With Enterprise Manager, Oracle DBA can access the Undo Management page from the
database Administration page by selecting Undo Management under the Instance
heading.
Oracle DBA 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.
Oracle DBA 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 Oracle DBA define the floor value under which Oracle should never bring
down the undo retention. When Oracle DBA 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.
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,
Oracle DBA 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 Oracle DBA extend the undo tablespace, you can do so.
To obtain more detailed information about the recommendations, click Undo Advisor.
Extending the Undo Tablespace
When auto-extend tablespace is enabled, the system will automatically extend the
undo tablespace if it is under space pressure.
However, when the auto-extend tablespace feature is disabled, you might need to
manually extend the undo tablespace. You might discover this through pro-active
planning with the undo advisor. See "Using the Undo Advisor" on page 6-14.
Oracle DBA might also need to extend it when you get an undo tablespace alert (warning or
critical), or when you get long query alert or snapshot too old error.
To resize the tablespace, click Edit Undo Tablespace. The Edit Tablespace page
appears. To extend the tablespace, select a datafile and click Edit. Enter the new size
for File Size. You can also opt to have the system automatically extend the datafile by
enabling Automatically extend datafile when full under Storage and specifying an
increment size.
Click Continue. Click Apply. A confirmation message should appear.
Using the Undo Advisor
Oracle DBA 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. Oracle DBA 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.
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 Oracle DBA 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.
|
|
|
