Oracle Database 12c DBA Handbook (2015)

In Chapter 6, we touched briefly on how the space in an undo tablespace is managed, along with views such as V$UNDOSTAT that can help the DBA monitor and size the undo tablespace. In this chapter, we’ll delve much more deeply into the configuration and management of the undo tablespace, and how we may resolve the sometimes conflicting requirements of providing enough undo for read consistency while also ensuring that DML statements will not fail.

To start off this chapter, we’ll do a quick review of transactions from a database user’s point of view so that you will better understand how to support the user’s transactions with the appropriately sized undo tablespace. Next, we’ll cover the basics of how to create an undo tablespace, either during database creation or later using the familiar CREATE TABLESPACE command. Undo segments fulfill a number of requirements for database users, and I will enumerate and explain each of those requirements in some detail.

Oracle provides a number of ways to monitor and, as a result, more precisely size undo tablespaces. The package DBMS_ADVISOR can be used to analyze the undo tablespace usage, as we did in Chapter 6; we will investigate this package in more detail and look at how Oracle Enterprise Manager Cloud Control can make it easy to perform the analysis. Oracle Database 12c further refines the resource requirements for undo by allowing the undo against temporary tables to be stored in a temporary tablespace.

I’ll also review the different types of Oracle Flashback features that rely on an adequately sized undo tablespace to recover from a number of different user error scenarios. All the major Flashback features at the query, table, or transaction level are covered in this chapter; Flashback Database is covered in Chapter 16.

Rollback segments from previous Oracle releases were hard to manage and were usually sized too large or too small by most DBAs; Oracle strongly recommends that all new databases use Automatic Undo Management and that databases upgraded from a previous version of Oracle be converted to using Automatic Undo Management. We won’t cover any aspects of manual undo management here except for how to migrate from rollback segments to automatic undo.

Transaction Basics

A transaction is a collection of SQL DML statements that is treated as a logical unit; the failure of any of the statements in the transaction implies that none of the other changes made to the database in the transaction should be permanently saved to the database. Once the DML statements in the transaction have successfully completed, the application or SQL*Plus user will issue a COMMIT to make the changes permanent. In the classic banking example, a transaction that transfers a dollar amount from one account to another is successful only if both the debit of one account (an UPDATE of the savings account balance) and the credit of another account (an UPDATE of the checking account balance) are successful. Failure of either or both statements invalidates the entire transaction. When the application or SQL*Plus user issues a COMMIT, if only one or the other of the UPDATE statements is successful, the bank will have some very unhappy customers!

A transaction is initiated implicitly. After a COMMIT of a previous transaction is completed, and at least one row of a table is inserted, updated, or deleted, a new transaction is implicitly created (an UPDATE with a predicate returning no rows does not create a transaction slot). Also, any DDL commands such as CREATE TABLE and ALTER INDEX will commit an active transaction and begin a new transaction. You can name a transaction by using the SET TRANSACTION … NAME ‘transaction_name’ command. Although this provides no direct benefit to the application, the name assigned to the transaction is available in the dynamic performance view V$TRANSACTION and allows a DBA to monitor long-running transactions; in addition, the transaction name helps the DBA resolve in-doubt transactions in distributed database environments. The SET TRANSACTION command, if used, must be the first statement within the transaction.

Within a given transaction, you can define a savepoint. A savepoint allows the sequence of DML commands within a transaction to be partitioned so that it is possible to roll back one or more of the DML commands after the savepoint, and subsequently submit additional DML commands or commit the DML commands performed before the savepoint. Savepoints are created with the SAVEPOINT savepoint_name command. To undo the DML commands since the last savepoint, you use the command ROLLBACK TO SAVEPOINT savepoint_name.

A transaction is implicitly committed if a user disconnects from Oracle normally; if the user process terminates abnormally, the most recent transaction is rolled back.

Undo Basics

Undo tablespaces facilitate the rollback of logical transactions. In addition, undo tablespaces support a number of other features, including read consistency, various database-recovery operations, and Flashback functions.

Rollback

As described in the previous section, any DML command within a transaction—whether the transaction is one or one hundred DML commands—may need to be rolled back. When a DML command makes a change to a table, the old data values changed by the DML command are recorded in the undo tablespace within a system-managed undo segment or a rollback segment.

When an entire transaction is rolled back (that is, a transaction without any savepoints), Oracle undoes all the changes made by DML commands since the beginning of the transaction using the corresponding undo records, releases the locks on the affected rows, if any, and the transaction ends.

If part of a transaction is rolled back to a savepoint, Oracle undoes all changes made by DML commands after the savepoint. All subsequent savepoints are lost, all locks obtained after the savepoint are released, and the transaction remains active.

Read Consistency

Undo provides read consistency for users who are reading rows that are involved in a DML transaction by another user. In other words, all users who are reading the affected rows will see no changes in the rows until they issue a new query after the DML user commits the transaction. Undo segments are used to reconstruct the data blocks back to a read-consistent version and, as a result, provide the previous values of the rows to any user issuing a SELECT statement before the transaction commits.

For example, user CLOLSEN begins a transaction at 10:00 that is expected to commit at 10:15, with various updates and insertions to the EMPLOYEES table. As each INSERT, UPDATE, and DELETE occurs on the EMPLOYEES table, the old values of the table are saved in the undo tablespace. When the user SUSANP issues a SELECT statement against the EMPLOYEES table at 10:08, none of the changes made by CLOLSEN are visible to anyone except CLOLSEN; the undo tablespace provides the previous values of CLOLSEN’s changes for SUSANP and all other users. Even if the query from SUSANP does not finish until 10:20, the table still appears to be unchanged until a new query is issued after the changes are committed. Until CLOLSEN performs a COMMIT at 10:15, the data in the table appears unchanged as of 10:00.

If there is not enough undo space available to hold the previous values of changed rows, the user issuing the SELECT statement may receive an “ORA-01555: Snapshot Too Old” error. Later in this chapter, we will discuss ways in which we can address this issue.

Database Recovery

Undo tablespaces are also a key component of instance recovery. The online redo logs bring both committed and uncommitted transactions forward to the point in time of the instance crash; the undo data is used to roll back any transactions that were not committed at the time of the crash or instance failure.

Flashback Operations

The data in the undo tablespace is used to support the various types of Flashback options: Flashback Table, Flashback Query, and the package DBMS_FLASHBACK. Flashback Table will restore a table as of a point of time in the past, Flashback Query lets you view a table as of an SCN or time in the past, and DBMS_FLASHBACK provides a programmatic interface for Flashback operations. Flashback Data Archive, new as of Oracle Database 11g, stores and tracks all transactions on a specified table for a specified time period; in a nutshell, Flashback Data Archive stores undo data for a specific table in a specific tablespace outside of the global undo tablespace. Also new as of Oracle Database 11g is Flashback Transaction Backout that can roll back an already committed transaction and its dependent transactions while the database is online. All these Flashback options are covered in more detail at the end of this chapter.

Managing Undo Tablespaces

Creating and maintaining undo tablespaces is a “set it and forget it” operation once the undo requirements of the database are understood. Within the undo tablespace, Oracle automatically creates, sizes, and manages the undo segments, unlike previous versions of Oracle in which the DBA would have to manually size and constantly monitor rollback segments.

In the next couple sections, we’ll review the processes used to create and manage undo tablespaces, including the relevant initialization parameters. In addition, we’ll review some scenarios where we may create more than one undo tablespace and how to switch between undo tablespaces.

Creating Undo Tablespaces

Undo tablespaces can be created in two ways: at database creation or with the CREATE TABLESPACE command after the database is created. As with any other tablespace in Oracle 12c, the undo tablespace can be a bigfile tablespace, further easing the maintenance of undo tablespaces.

Creating an Undo Tablespace with CREATE DATABASE

A database may have more than one undo tablespace, although only one can be active at a time. Here’s what creating an undo tablespace at database creation looks like:

If the undo tablespace cannot be successfully created in the CREATE DATABASE command, the entire operation fails. The error must be corrected, any files remaining from the operation must be deleted, and the command must be reissued.

Although the UNDO TABLESPACE clause in the CREATE DATABASE command is optional, if it is omitted and Automatic Undo Management is enabled (the default), an undo tablespace is still created with an autoextensible datafile with an initial size of 10MB and the default name SYS_UNDOTBS.

Creating an Undo Tablespace with CREATE TABLESPACE

Any time after the database is created, a new undo tablespace can be created. An undo tablespace is created just as any other tablespace with the addition of the UNDO keyword:

Most DML won’t need more than 500MB of UNDO space, so we start out this tablespace at only 500MB and allow it to grow for the occasional larger or one-time DML statements that might need more.

Extents in an undo tablespace must be system managed; in other words, you can only specify EXTENT MANAGEMENT as LOCAL AUTOALLOCATE.

Creating an Undo Tablespace Using EM Cloud Control

Creating an undo tablespace is straightforward using Enterprise Manager Cloud Control. From the instance’s home page, navigate to Administration | Storage | Tablespaces. You will be presented with a list of existing tablespaces; click the Create button. In Figure 7-1, we’re creating a new undo tablespace named UNDO_BATCH. This undo tablespace will be used for all transactions running during the overnight batch window even if SELECT statements are running. Specify Undo Retention Guarantee as well. I’ll explain how that works later in this chapter.

FIGURE 7-1. Using EM Database Cloud Control to create an undo tablespace

At the bottom of the screen, click Add and specify the name of the datafile to use for the undo tablespace, as indicated in Figure 7-2. In this example, you use the ASM disk group DATA for the datafile with a size of 500MB and 100MB more each time it extends. Click Continue to return to the page shown in Figure 7-1.

Clicking the Storage tab allows us to specify extent allocation, although for an undo tablespace it must be automatic. If we are supporting multiple block sizes, we can specify the block size for the undo tablespace. Figure 7-3shows that we are specifying automatic extent allocation and a block size of 8192, the default and only block size defined for the database.

FIGURE 7-2. Specifying a datafile for a new undo tablespace

FIGURE 7-3. Specifying storage characteristics for an undo tablespace

As with most every EM Cloud Control maintenance screen, we can view the actual SQL commands that will be executed when we are ready to create the tablespace. In Figure 7-4, we clicked the Show SQL button to preview the SQL commands used to create the tablespace.

FIGURE 7-4. Previewing SQL commands to create an undo tablespace

After we click OK on the screen shown in Figure 7-3, the new undo tablespace is created successfully, as shown in Figure 7-5.

FIGURE 7-5. Create undo tablespace confirmation

Note that EM Cloud Control, although a big timesaver for the DBA, does not cover every possible scenario, nor does it prevent the DBA from trying to create an undo tablespace with the wrong parameters. On the Storage tab in Figure 7-3, we could have specified Uniform under Extent Allocation, but when we then tried to create the undo tablespace, it would have failed with an error message. As mentioned earlier in this chapter, undo tablespaces must have automatically allocated extents.

Dropping Undo Tablespaces

Dropping an undo tablespace is similar to dropping any other tablespace; the only restriction is that the undo tablespace being dropped must not be the active undo tablespace or still have undo data for an uncommitted transaction. You may, however, drop an undo tablespace that has unexpired undo information, which may cause a long-running query to fail. To drop the tablespace we created in the previous section, we use the DROP TABLESPACE command:

The clause INCLUDING CONTENTS is implied when dropping an undo tablespace. However, to remove the operating system data files when the tablespace is dropped, you must specify INCLUDING CONTENTS AND DATAFILES. Trying to drop the active undo tablespace is not allowed:

The active undo tablespace must be switched with another undo tablespace before it can be dropped. More information on switching undo tablespaces is covered later in this chapter.

Modifying Undo Tablespaces

The following operations are allowed on undo tablespaces:

Adding a datafile to an undo tablespace

Renaming a datafile in an undo tablespace

Changing an undo tablespace’s datafile to online or offline

Beginning or ending an open tablespace backup (ALTER TABLESPACE UNDOTBS BEGIN BACKUP)

Enabling or disabling the undo retention guarantee

Everything else is automatically managed by Oracle.

Using OMF for Undo Tablespaces

In addition to using a bigfile tablespace for undo tablespaces, you can also use Oracle Managed Files (OMF) to automatically name (and locate, if you’re not using ASM) an undo tablespace; the initialization parameter DB_CREATE_FILE_DEST contains the location where an undo tablespace will be created if the DATAFILE clause is not specified in the CREATE UNDO TABLESPACE command. In the following example, we create an undo tablespace using OMF in an ASM disk group:

Because we did not specify a datafile size either, the tablespace defaults to a size of 100MB; in addition, the datafile is autoextensible with an unlimited maximum size, limited only by the file system.

Undo Tablespace Dynamic Performance Views

A number of dynamic performance views and data dictionary views contain information about undo tablespaces, user transactions, and undo segments. Table 7-1 contains the view names and their descriptions.

TABLE 7-1. Undo Tablespace Views

The views in Table 7-1 are described in more detail later in this chapter.

Undo Tablespace Initialization Parameters

The following sections describe the initialization parameters needed to specify the undo tablespace for the database as well as control how long Oracle will retain undo information in the database.

UNDO_MANAGEMENT

The parameter UNDO_MANAGEMENT defaults to MANUAL in Oracle Database 10g, and AUTO in Oracle Database 11g and 12c. Setting the parameter UNDO_MANAGEMENT to AUTO places the database in Automatic Undo Management mode. At least one undo tablespace must exist in the database for this parameter to be valid, whether UNDO_TABLESPACE is specified or not. UNDO_MANAGEMENT is not a dynamic parameter; therefore, the instance must be restarted whenever UNDO_MANAGEMENT is changed from AUTO to MANUAL, or vice versa.

UNDO_TABLESPACE

The UNDO_TABLESPACE parameter specifies which undo tablespace will be used for Automatic Undo Management. If UNDO_MANAGEMENT is not specified or is set to MANUAL, and UNDO_TABLESPACE is specified, the instance will not start.

NOTE

UNDO_TABLESPACE is used in a Real Application Clusters (RAC) environment to assign a particular undo tablespace to an instance, where the total number of undo tablespaces in the database is the same or more than the number of instances in the cluster.

Conversely, if UNDO_MANAGEMENT is set to AUTO and there is no undo tablespace in the database, the instance will start, but then the SYSTEM rollback segment will be used for all undo operations, and a message is written to the alert log. Any user DML that attempts to make changes in non-SYSTEM tablespaces will, in addition, receive the error message “ORA-01552: cannot use system rollback segment for non-system tablespace ‘USERS,’” and the statement fails.

UNDO_RETENTION

UNDO_RETENTION specifies a minimum amount of time that undo information is retained for queries. In automatic undo mode, UNDO_RETENTION defaults to 900 seconds. This value is valid only if there is enough space in the undo tablespace to support read-consistent queries; if active transactions require additional undo space, an unexpired undo may be used to satisfy the active transactions and may cause “ORA-01555: Snapshot Too Old” errors.

The column TUNED_UNDORETENTION of the dynamic performance view V$UNDOSTAT gives the tuned undo retention time for each time period; the status of the undo tablespace usage is updated in V$UNDOSTAT every ten minutes:

Because the transaction load is very light during the most recent time period, and the instance has just recently started up, the value in the TUNED_UNDORETENTION column is the same as the minimum specified in the UNDO_RETENTION initialization parameter: 900 seconds (15 minutes). You may even set UNDO_RETENTION to 24 hours or more to facilitate AS OF queries for users without needing DBA intervention.

TIP

You don’t need to specify UNDO_RETENTION unless you have Flashback or LOB retention requirements; the UNDO_RETENTION parameter is not used for managing transaction rollback.

Multiple Undo Tablespaces

As mentioned earlier in this chapter, a database can have multiple undo tablespaces, but only one of them can be active for a given instance at any one time. In this section, I’ll show an example of switching to a different undo tablespace while the database is open.

NOTE

In a RAC environment, one undo tablespace is required for each instance in the cluster.

In our dw database, we have three undo tablespaces:

But only one of the undo tablespaces is active:

For overnight processing, we change the undo tablespace from UNDOTBS1 to the tablespace UNDO_BATCH, which is much larger to support higher DML activity. The disk containing the daytime undo tablespace is much faster but has a limited amount of space; the disk containing the overnight undo tablespace is much larger, but slower. As a result, we use the smaller undo tablespace to support OLTP during the day, and the larger undo tablespace for our data mart and data warehouse loads, as well as other aggregation activities, at night when response time is not as big of an issue.

NOTE

Other than special circumstances described in this section, it is unlikely that you will be switching undo tablespaces for a given instance. Oracle’s best practices suggest that you create a single undo tablespace per instance that is large enough to handle all transaction loads; in other words, “set it and forget it.”

About the time the undo tablespace is going to be switched, the user HR is performing some maintenance operations on the HR.EMPLOYEES table, and she has an active transaction in the current undo tablespace:

Checking V$TRANSACTION, you see HR’s uncommitted transaction:

You change the undo tablespace as follows:

HR’s transaction is still active, and therefore the old undo tablespace still contains the undo information for HR’s transaction, leaving the undo segment still available with the following status until the transaction is committed or rolled back:

Even though the current undo tablespace is UNDO_BATCH, the daytime tablespace UNDOTBS1 cannot be taken offline or dropped until HR’s transaction is committed or rolled back:

The error message ORA-30042 applies if you try to offline an undo tablespace that is in use—either it is the current undo tablespace or it still has pending transactions. Note that if we switch back to the daytime tablespace before HR commits or rolls back the original transaction, the status of HR’s rollback segment reverts to ONLINE:

Sizing and Monitoring the Undo Tablespace

There are three types of undo data in the undo tablespace: active or unexpired, expired, and unused. Active or unexpired is undo data that is still needed for read consistency, even after a transaction has been committed. Once all queries needing the active undo data have completed and the undo retention period is reached, the active undo data becomes expired. Expired undo data may still be used to support other Oracle features, such as the Flashback features, but it is no longer needed to support read consistency for long-running transactions. Unused undo data is space in the undo tablespace that has never been used.

As a result, the minimum size for an undo tablespace is enough space to hold the before-image versions of all data from all active transactions that have not yet been committed or rolled back. If the space allocated to the undo tablespace cannot even support the changes to uncommitted transactions to support a rollback operation, the user will get the error message “ORA-30036: unable to extend segment by space_qty in undo tablespace tablespace_name.” In this situation, the DBA must increase the size of the undo tablespace, or as a stopgap measure the user can split up a larger transaction into smaller ones while still maintaining any required business rules.

Manual Methods

The DBA can use a number of manual methods to correctly size the undo tablespace. As demonstrated in Chapter 6, we can review the contents of the dynamic performance view V$UNDOSTAT to see the undo segment usage at ten-minute intervals. In addition, the column SSOLDERRCNT indicates how many queries failed with a “Snapshot too old” error:

Between 19:27 and 19:37 we have a spike in undo usage, resulting in some failed queries. As a rule of thumb, you can use the following calculations:

In this formula, UR equals undo retention in seconds (from the initialization parameter UNDO_RETENTION), UPS equals undo blocks used per second (maximum), and overhead equals undo metadata, usually a very small number relative to the overall size. For example, if a database has an 8KB block size, UNDO_RETENTION equals 43200 (12 hours), and we generate 500 undo blocks every second, all of which must be retained for at least 12 hours, our total undo space must be

Add about 10 to 20 percent to this calculation to allow for unexpected situations. Alternatively, you can enable autoextend for the datafiles in the undo tablespace. Although this calculation is useful as a starting point, Oracle 10g’s and Oracle 11g’s built-in advisors, using trending analysis, can give a better overall picture of undo space usage and recommendations.

Undo Advisor

Oracle 12c’s Undo Advisor automates a lot of the tasks necessary to fine-tune the amount of space required for an undo tablespace. In Chapter 6, we reviewed two examples of using Undo Advisor: via the EM Cloud Control interface and using the PL/SQL DBMS_ADVISOR packages within the Automatic Workload Repository (AWR) to programmatically choose a time period to analyze and perform the analysis.

The Automatic Undo Management GUI screen is shown in Figure 7-6.

FIGURE 7-6. Tablespace characteristics

UNDO_RETENTION is currently set to 15 minutes and the size of the active undo tablespace (UNDOTBS1) is 60MB. In this example, if we want a read-consistent view of table data for 720 minutes, clicking the Run Analysis button tells us that we only need an undo tablespace size of only 36MB (and ideally three times this amount) to support workload fluctuations. Therefore, our undo tablespace would be sized adequately for future growth at 108MB.

Controlling Undo Usage

Since Oracle9i, Oracle’s Database Resource Manager can help to control undo space usage by user or by group of users within a resource consumer group via the UNDO_POOL directive. Each consumer group can have its own undo pool; when the total undo generated by a group exceeds the assigned limit, the current transaction generating the undo is terminated and generates the error message “ORA-30027: Undo quota violation—failed to get number(bytes).” The session will have to wait until the DBA increases the size of the undo pool or until other transactions from users in the same consumer group complete.

In the following example, we change the default value of UNDO_POOL from NULL (unlimited) to 50000KB (50MB) for users in the resource consumer group LOW_GROUP:

Oracle Database Resource Manager and other resource directives are covered in more detail in Chapter 5.

Storing Undo in Temporary Tablespaces

Oracle Database 12c introduces a new option for using undo: temporary undo. Although temporary tables were available in previous versions of Oracle Database and enhanced performance because no redo operations were generated by DML against temporary tables, there was still undo generated that was recorded in the redo log files. Figure 7-7 shows the locations for undo data for both persistent and temporary tables.

FIGURE 7-7. Temporary undo architecture

Undo is still required for a temporary table to ensure consistent reads and transaction rollback to a savepoint for the temporary table used in a single session, but the undo does not have to reside in the database’s default undo tablespace. Instead, the undo for temporary tables can reside in the temporary tablespace itself and therefore not generate additional vectors in the online redo log files.

To enable temporary undo at the database level, change the initialization parameter TEMP_UNDO_ENABLED:

Temporary undo can also be enabled at the session level. To use temporary undo, the COMPATIBLE initialization parameter must be set to at least 12.1.0.0.0.

Read Consistency vs. Successful DML

For OLTP databases, generally we want DML commands to succeed at the expense of read-consistent queries. For a DSS environment, however, we may want long-running queries to complete without getting a “Snapshot too old” error. Although increasing the UNDO_RETENTION parameter or increasing the size of the undo tablespace helps to ensure that undo blocks are available for read-consistent queries, undo tablespaces have another characteristic to help ensure that queries will run to completion: the RETENTION GUARANTEE setting.

Undo retention guarantee is set at the tablespace level, and it can be altered at any time. Setting a retention guarantee for an undo tablespace ensures that any unexpired undo within the tablespace should be retained even if it means that DML transactions might not have enough undo space to complete successfully. By default, a tablespace is created with NOGUARANTEE, unless you specify the GUARANTEE keyword, either when the tablespace is created or later with ALTER TABLESPACE:

For non-undo tablespaces, the value of RETENTION is always NOT APPLY.

Flashback Features

In this section we’ll discuss the Flashback features supported by undo tablespaces or Flashback Data Archive: Flashback Query, Flashback Table, Flashback Version Query, and Flashback Transaction Query. In addition, we’ll cover the highlights of using the DBMS_FLASHBACK package. As of Oracle Database 11g, these features are collectively known as the Oracle Total Recall Option.

Flashback Database and Flashback Drop are covered in Chapter 16. Flashback Database uses Flashback logs in the Flash Recovery Area instead of undo in an undo tablespace to provide the Flashback functionality. Flashback Drop places dropped tables into a virtual recycle bin within the tablespace and they remain there until the user retrieves them with FLASHBACK TABLE … TO BEFORE DROP command or empties the recycle bin, or else until the space is needed by new permanent objects in the tablespace.

To further extend the self-service capabilities of Oracle Database 12c, the DBA can grant system and object privileges to users to allow them to fix their own problems, usually without any DBA intervention. In the following example, we’re enabling the user SCOTT to perform Flashback operations on specific tables and to access transaction metadata across the database:

Flashback Query

The AS OF clause is available in a SELECT query to retrieve the state of a table as of a given timestamp or SCN. You might use this to find out which rows in a table were deleted since midnight, or you might want to just do a comparison of the rows in a table today versus what was in the table yesterday.

In the following example, HR is cleaning up the EMPLOYEES table and deletes two employees who no longer work for the company:

Normally, HR will copy these rows to the EMPLOYEES_ARCHIVE table first, but she forgot to do that this time; HR doesn’t need to put those rows back into the EMPLOYEES table, but she needs to get the two deleted rows and put them into the archive table. Because HR knows she deleted the rows less than an hour ago, we can use a relative timestamp value with Flashback Query to retrieve the rows:

Because we know that EMPLOYEE_ID is the primary key of the table, we can use it to retrieve the employee records that existed an hour ago but do not exist now. Note also that we didn’t have to know which records were deleted; we essentially compared the table as it exists now versus an hour ago and inserted the records that no longer exist into the archive table.

TIP

It is preferable to use the SCN for Flashback over a timestamp; SCNs are exact, whereas the timestamp values are only stored every three seconds to support Flashback operations. As a result, enabling Flashback using timestamps may be off by as much as 1.5 seconds.

Although we could use Flashback Table to get the entire table back, and then archive and delete the affected rows, in this case it is much simpler to merely retrieve the deleted rows and insert them directly into the archive table.

Another variation of Flashback Table is to use Create Table As Select (CTAS) with the subquery being a Flashback Query:

This is known as an out-of-place restore (in other words, restoring the table or a subset of the table to a different location than the original). This has the advantage of being able to further manipulate the missing rows, if necessary, before placing them back in the table; for example, after reviewing the out-of-place restore, an existing referential integrity constraint may require that you insert a row into a parent table before the restored row can be placed back in the child table.

One of the disadvantages of an out-of-place restore using CTAS is that neither constraints nor indexes are rebuilt automatically.

DBMS_FLASHBACK

An alternative to Flashback Query is the package DBMS_FLASHBACK. One of the key differences between the DBMS_FLASHBACK package and Flashback Query is that DBMS_FLASHBACK operates at the session level, whereas Flashback Query operates at the object level.

Within a PL/SQL procedure or a user session, DBMS_FLASHBACK can be enabled and all subsequent operations, including existing applications, can be carried out without the AS OF clause being added to SELECT statements. After DBMS_FLASHBACK is enabled as of a particular timestamp or SCN, the database appears as if the clock was turned back to the timestamp or SCN until DBMS_FLASHBACK is disabled. Although DML is not allowed when DBMS_FLASHBACK is enabled, a cursor can be opened in a PL/SQL procedure before DBMS_FLASHBACK is enabled to allow data from a previous point in time to be inserted or updated in the database as of the current point in time.

Table 7-2 lists the procedures available within DBMS_FLASHBACK.

TABLE 7-2. DBMS_FLASHBACK Procedures

The procedures that enable and disable Flashback mode are relatively simple to use. The complexity usually lies within a PL/SQL procedure, for example, that creates cursors to support DML commands.

In the following example, we’ll revisit HR’s deletion of the EMPLOYEES rows and how HR can restore those to the table using the DBMS_FLASHBACK package. In this scenario, HR will put the deleted employee rows back into the table and instead add a termination date column to the table to reflect the date at which the employees left the company:

About ten minutes later, HR decides to get those rows back using DBMS_FLASHBACK, and enables Flashback for her session:

Next, HR verifies that the two deleted rows existed as of 45 minutes ago:

To put the rows back into the HR.EMPLOYEES table, HR writes an anonymous PL/SQL procedure to create a cursor to hold the deleted rows, disable Flashback Query, then reinsert the rows:

Note that HR could have enabled Flashback within the procedure; in this case, HR enabled it outside of the procedure to run some ad hoc queries, and then used the procedure to create the cursor, turn off Flashback, and reinsert the rows.

Flashback Transaction Backout

A given transaction in a complex application may be consistent and atomic, but the validity of the transaction may not be validated until many other transactions have taken place; in other words, the ill effects of an earlier transaction may cause other transactions to further modify the same data as the original transaction. Trying to manually track the interdependent successive transactions is tedious and error-prone. Flashback Transaction makes it easy to identify and roll back the offending transaction and optionally all dependent transactions.

To enable Flashback Transaction Backout, enable archiving (if it is not already in ARCHIVELOG mode) while the database is mounted (but not open):

Next, run these commands to create at least one archived redo log file and to add additional transaction information to the log files.

Adding the supplemental log data will have a noticeable impact on performance in a heavy DML environment. Be sure to monitor system resources before and after you enable the additional logging to assess the cost of the logging operation. Finally, open the database:

You leverage Flashback Transaction Backout features via the DBMS_FLASHBACK procedure TRANSACTION_BACKOUT. After you run DBMS_FLASHBACK.TRANSACTION_BACKOUT, the DML against the related tables is performed but not committed; you must then review the tables DBA_FLASHBACK_TRANSACTION_STATE and DBA_FLASHBACK_TRANSACTION_REPORT to see if the correct transactions were rolled back. You must then manually perform either a COMMIT or a ROLLBACK.

Flashback Table

The Flashback Table feature not only restores the state of rows in a table as of a point of time in the past, but also restores the table’s indexes, triggers, and constraints while the database is online, increasing the overall availability of the database. The table can be restored as of a timestamp or an SCN. Flashback Table is preferable to other Flashback methods if the scope of user errors is small and limited to one or very few tables. It’s also the most straightforward method if you know that you want to restore the table to a point in the past unconditionally. For recovering the state of a larger number of tables, Flashback Database may be a better choice. Flashback Table cannot be used on a standby database and cannot reconstruct all DDL operations, such as adding and dropping columns. See Chapter 14 for a way to recover a single table from an RMAN backup.

To use Flashback Table on a table or tables, you must enable row movement on the table before performing the Flashback operation, although row movement need not be in effect when the user error occurs. Row movement is also required to support Oracle’s segment shrink functionality; because row movement will change the ROWID of a table row, do not enable row movement if your applications depend on the ROWID being the same for a given row until the row is deleted. Because none of our applications reference our tables by ROWID, we can safely enable row movement for the HR tables:

The next day, the HR user accidentally deletes all the rows in the EMPLOYEES table due to a cut-and-paste error from an existing script:

Because the undo tablespace is large enough and the HR user notices the problem within the retention period, the HR user can bring back the entire table quickly without calling the DBA:

If two or more tables have a parent/child relationship with foreign key constraints, and rows were inadvertently deleted from both tables, they can be flashed back in the same FLASHBACK command:

The HR user can also use EM Cloud Control to flash back one or more tables by choosing Availability | Perform Recovery to open the Perform Recovery page, shown in Figure 7-8.

FIGURE 7-8. EM Cloud Control Perform Recovery page

In simple scenarios, using the command line would take less time and is probably more straightforward; however, if you have unknown dependencies or if the command-line syntax is unfamiliar to you, then EM Cloud Control is a better option.

Flashback Version Query

Flashback Version Query, another Flashback feature that relies on undo data, provides a finer level of detail than an AS OF query: Whereas the Flashback methods presented up to now bring back rows of a table or an entire table for a particular point in time, Flashback Version Query returns the entire history of a given row between two SCNs or timestamps.

For the examples in this and the next section, the user HR makes a number of changes to the HR.EMPLOYEES and HR.DEPARTMENTS tables:

The next day, the HR user is out of the office, and the other HR department employees (who use the HR user account) want to know what rows and tables were changed. Using Flashback Version Query, the user HR can see not only the values of a column at a particular time, but the entire history of changes between specified timestamps or SCNs.

A Flashback Version Query uses the VERSIONS BETWEEN clause to specify a range of SCNs or timestamps for analysis of a given table (in this case, the EMPLOYEES table). When VERSIONS BETWEEN is used in a Flashback Version Query, a number of pseudocolumns are available to help identify the SCN and timestamp of the modifications, as well as the transaction ID and the type of operation performed on the row. Table 7-3 shows the pseudocolumns available with Flashback Version Queries.

TABLE 7-3. Flashback Version Query Pseudocolumns

The HR user runs a Flashback Version Query to see the changes to any key columns in HR.EMPLOYEES for the two employees with IDs 195 and 196:

The rows are presented with the most recent changes first. Alternatively, HR could have filtered the query by TIMESTAMP or displayed the TIMESTAMP values, but either can be used in a Flashback Query or Flashback Table operation, if required later. From this output, we see that one employee was deleted and that another employee received two pay adjustments instead of one. It’s also worth noting that some of the transactions contain only one DML command, and others have two. In the next section, we’ll attempt to correct one or more of these problems.

Flashback Transaction Query

Once we have identified any erroneous or incorrect changes to a table, we can use Flashback Transaction Query to identify any other changes that were made by the transaction containing the inappropriate changes. Once identified, all changes within the transaction can be reversed as a group, typically to maintain referential integrity or the business rules used to process the transaction in the first place.

A Flashback Transaction Query, unlike a Flashback Version Query, does not reference the table involved in DML transactions; instead, you query the data dictionary view FLASHBACK_TRANSACTION_QUERY, the columns of which are summarized in Table 7-4.

TABLE 7-4. FLASHBACK_TRANSACTION_QUERY Columns

To further investigate the changes that were made to the EMPLOYEES table, we will query the view FLASHBACK_TRANSACTION_QUERY with the oldest transaction from the query in the previous section:

We confirm what we already expected—that another user in the HR department made the deletion and salary update (thus pointing out the usefulness of assigning separate user accounts for each member of the HR department). The UNDO_SQL column contains the actual SQL code that can be used to reverse the effect of the transaction. Note, however, that in this example, this is the first transaction to occur between the SCNs of interest. If other transactions made further updates to the same columns, we may want to review the other updates before running the SQL code in the UNDO_SQL column.

Flashback Data Archive

Regulations such as Sarbanes-Oxley and HIPAA require strict control and tracking requirements for customer and patient data; keeping a historical record of all changes to rows in critical tables is error prone and requires custom applications or database triggers to maintain repositories for the historical changes. Every time you create a new application or update a table in an application that requires historical tracking, you must make changes to your tracking application as well. As of Oracle Database 11g, you can use Flashback Data Archive to automatically save historical changes to all key tables for as long as regulatory agencies or your stakeholders require.

Flashback Data Archive is implemented natively in Oracle Database 11g; in a nutshell, you create one or more repository areas (one of which can be the default), assign a default retention period for objects in the repository, and then mark the appropriate tables for tracking.

A Flashback Data Archive acts much like an undo tablespace; however, a Flashback Data Archive only records UPDATE and DELETE statements, but not INSERT statements. In addition, undo data is typically retained for a period of hours or days for all objects; rows in Flashback Data Archives can span years or even decades. Flashback Data Archive has a much narrower focus as well, recording only historical changes to table rows; Oracle uses data in an undo tablespace for read-consistency in long-running transactions and to roll back uncommitted transactions.

You can access data in a Flashback Data Archive just as you do with Flashback Query: using the AS OF clause in a SELECT statement. In the next few sections, I’ll show you how to create a Flashback Data Archive, assign permissions to users and objects, and query historical data in a Flashback Data Archive.

Creating an Archive

You can create one or several Flashback Data Archives in existing tablespaces using the CREATE FLASHBACK ARCHIVE command; however, Oracle best practice recommends that you use dedicated tablespaces. All archives must have a default retention period using the RETENTION clause and can optionally be identified as the default archive using the DEFAULT keyword. The disk quota in an archive is limited by the disk space within the tablespace unless you assign a maximum amount of disk space in the archive using the QUOTA keyword.

In this example, you first create a dedicated tablespace for your Flashback Data Archive:

Next, you create three Flashback Data Archives: one for the ES department with no quota limit and a ten-year retention period, a second one for the finance department with a 500MB limit and a seven-year retention period, and a third for all other users in the USERS4 tablespace as the default with a 250MB limit and a two-year retention period:

You cannot specify more than one tablespace in the CREATE FLASHBACK ARCHIVE command; you must use the ALTER FLASHBACK ARCHIVE command to add a tablespace, as you’ll see a bit later in this chapter, in the section “Managing Flashback Data Archives.”

Using Flashback Data Archive Data Dictionary Views

Two new data dictionary views support Flashback Data Archives: DBA_FLASHBACK_ARCHIVE and DBA_FLASHBACK_ARCHIVE_TS. DBA_FLASHBACK_ARCHIVE lists the archives, and DBA_FLASHBACK_ARCHIVE_TS displays the tablespace-to-archive mapping:

The view DBA_FLASHBACK_ARCHIVE_TABLES tracks the tables enabled for flashback archiving. I’ll show you the contents of this view later in this chapter after enabling a table for flashback archiving.

Assigning Flashback Data Archive Permissions

A user must have the FLASHBACK ARCHIVE ADMINISTER system privilege to create or modify Flashback Data Archives, and the FLASHBACK ARCHIVE object privilege to enable tracking on a table. Once tracking is enabled, a user doesn’t need any specific permissions to use the AS OF clause in a SELECT statement other than the SELECT permission on the table itself.

The FLASHBACK_ARCHIVE_ADMINSTER privilege also includes adding and removing tablespaces from an archive, dropping an archive, and performing an ad hoc purge of history data.

Managing Flashback Data Archives

You can easily add another tablespace to an existing archive; use the ALTER FLASHBACK ARCHIVE command like this to add the USERS3 tablespace to the FB_DFLT archive with a quota of 400MB:

You can purge archive data with the PURGE clause; in this example, you want to purge all rows in the FB_DFLT archive before January 1, 2010:

Assigning a Table to a Flashback Data Archive

You assign a table to an archive either at table creation using the standard CREATE TABLE syntax with the addition of the FLASHBACK ARCHIVE clause, or later with the ALTER TABLE command, as in this example:

Note that the previous command specified a specific archive for the HR.EMPLOYEES table; if you did not specify an archive, Oracle would assign FB_DFLT. You can review the tables that use Flashback Data Archive by querying the data dictionary view DBA_FLASHBACK_ARCHIVE_TABLES:

Querying Flashback Data Archives

Querying the historical data for a table in a Flashback Data Archive is as easy as using the AS OF clause in a table when you are using DML activity stored in an undo tablespace. In fact, users will not know whether they are retrieving historical data from the undo tablespace or from a Flashback Data Archive.

In this scenario, much like in the scenarios earlier in this chapter, one of the employees in the HR department deletes an employee row in the EMPLOYEES table and forgets to archive it to the EMPLOYEE_HISTORY table first; with Flashback Data Archive enabled for the EMPLOYEES table, the HR employee can rely on the FB_ES archive to satisfy any queries on employees no longer in the EMPLOYEE table. This is the DELETE statement from three weeks ago:

The HR employee needs to find the hire date for employee 169, so she retrieves the historical information from the EMPLOYEES table with the AS OF clause specifying a time four weeks ago:

Whether Oracle is using an undo tablespace or a Flashback Data Archive for a query containing AS OF is completely transparent to the user.

Flashback and LOBs

Undo data for LOB columns in a table can take up gigabytes of disk space even for a single row; therefore, to enable flashback operations for LOB columns, you must explicitly specify the RETENTION keyword in the storage clause for the LOB. This keyword is mutually exclusive with the PCTVERSION keyword, which specified a percentage of the table space for old versions of the LOBs. If you use the RETENTION keyword, old versions of a LOB are retained for the amount of time specified by the UNDO_RETENTION parameter, just as any other table rows in the undo tablespace.

Migrating to Automatic Undo Management

To migrate your environment from manually managed rollback segments to Automatic Undo Management, you need to know one thing: how large to size the undo tablespace based on the usage of the rollback segments in manual undo mode. With all manual rollback segments online, execute the procedure DBMS_UNDO_ADV.RBU_MIGRATION to return the size, in megabytes, of the current rollback segment utilization:

In this example, an undo tablespace created to replace the rollback segments should be at least 2840MB, or 2.84GB, to support the undo requirements currently supported by rollback segments.

Summary

Rarely does an Oracle database environment support only OLTP with constant DML or only BI queries. Even though your e-commerce database primarily takes customer orders, you need to run real-time analytic queries during the day along with the hourly extracts to the data warehouse. As a result you have at least two conflicting priorities for space in your undo tablespace. You need to have enough space in the undo tablespace to roll back failed transactions in addition to providing read consistency for long-running queries initiated while hundreds or even thousands of transactions are running against the same database.

The last three versions of Oracle Database (including 12c) have new features that leverage undo data beyond transaction consistency and read consistency, in particular the Flashback Query features covered in this chapter. As a result, your undo tablespace seems to get bigger with every release and requires a longer retention period for the undo data. This is not a problem as long as you, the DBA, understand your workload and set parameters such as UNDO_RETENTION appropriately along with specifying the RETENTION GUARANTEE parameter for a given undo tablespace.

The key to successful undo management involves using not only the tools available for reactive management such as Oracle Enterprise Manager Cloud Control 12c but also the extensive procedures in the DBMS_ADVISOR PL/SQL package to analyze your database on a regular basis and size your undo tablespace for the constantly changing workload—both the type of workload and the inevitable size of your workload as the demand for database consolidation increases.