9
Configuring and Using Flashback
CERTIFICATION OBJECTIVES
9.01 Restore dropped tables from the recycle bin
9.03 Use Flashback Transaction
9.04 Perform Flashback Table operations
9.05 Set up and use a Flashback Data Archive
9.06 Configure, monitor Flashback Database and perform Flashback Database operations
Disaster strikes when you least expect it, and Oracle’s flashback features, which are a part of Oracle’s Total Recall option, make it easy to recover from logical corruptions such as erroneously dropped tables or incorrect transactions. Most, if not all, of these recovery options are available to database users, freeing up the DBA for other tasks and potentially decreasing the recovery time. Flashback technology makes it easy for you to view the changes from the erroneous operations before you reverse the changes. You can view all data at a point in time, all changes to a row for a particular time period, or all changes within a specific transaction.
Oracle’s flashback features are supported by one of three structures in the database: undo data, the flash recovery area, and the recycle bin. The undo data in the undo tablespace, in addition to supporting transaction rollback, supports most of the Flashback Table operations. Flashback Data Archives support queries of previous versions of table rows. Flashback Data Archives provide an area in one or more tablespaces outside of the undo tablespace to support a much longer retention period than an undo tablespace can. Flashback logs, which reside in the flash recovery area, makes it easy to roll back your entire database to a previous time without performing a traditional restore and recovery operation. The recycle bin within each tablespace contains one or more versions of dropped tables and indexes, which can easily be restored by the user as long as there is no space pressure within the tablespace.
This chapter covers Flashback Drop after reviewing the role of the undo tablespace for other database features. Flashback Drop relies on a construct introduced in Oracle Database 10g, the recycle bin. The recycle bin is a data dictionary table that tracks one or more versions of dropped tables and allows you to recover any version of the table to its state immediately before it was dropped.
Next, you’ll learn about flashback technologies at the table level. You can query a table to see what a row looked like at a particular time, and depending on how far back in time you need to go, Oracle will use either undo data or a Flashback Data Archive if it is configured. You can also query the contents of a particular row as it changed during a specified time interval, or query changes to all rows for a particular database transaction.
Finally, you’ll learn how to use flashback technology at the database level with Flashback Database. Although you don’t want to use a sledgehammer on a mosquito, you may have to roll back an entire database to an earlier point in time if the logical corruptions are widespread or the object dependencies make it difficult or impossible to use flashback technologies at the table level.
CERTIFICATION OBJECTIVE 9.01
Restore Dropped Tables from the Recycle Bin
Flashback Drop relies on a construct introduced in Oracle Database 10g, the recycle bin, which behaves much like the recycle bin on a Windows-based computer. If enough room is available in the tablespace, dropped objects can be restored to their original schema with all indexes, triggers, and constraints intact. The following sections explore every aspect of the recycle bin: how to configure it, how to restore dropped tables, how space is managed in the recycle bin, how to bypass the recycle bin, and how to access tables directly in the recycle bin.
Understanding the Recycle Bin
In a nutshell, the recycle bin is a data dictionary table that keeps track of dropped objects. The objects themselves still exist in the same location within the tablespace but they are renamed. They are still listed in data dictionary views such as USER_TABLES, albeit with new names. The recycle bin supports objects with the same original name. In other words, if you create an EMPLOYEES table and subsequently drop it on three different occasions, all three versions of the EMPLOYEES table will be available in the recycle bin as long as enough space is available in the tablespace.
Even though you can refer to all objects in the recycle bin collectively, each tablespace has its own local recycle bin. Thus, some newer dropped objects may be aged out of the USERS tablespace due to space pressure. However, older dropped objects can remain for a much longer time in the TRAINING tablespace if new objects are not created frequently in that tablespace.
The recycle bin is enabled by default. To turn the recycle bin on and off, you can use the RECYCLEBIN initialization parameter:
You can also enable or disable the recycle bin at the session level using ALTER SESSION:
RECYCLEBIN is a dynamic initialization parameter, so you can change it using ALTER SYSTEM. The change will take effect immediately if you specify SCOPE=MEMORY or SCOPE=BOTH:
Regardless of which method you use to disable the recycle bin, the objects that are already in the bin stay there unless you purge them or they are purged due to space pressure from new objects. Until you re-enable the recycle bin, newly dropped objects will not be recoverable using the recycle bin.
When you drop an object with the recycle bin enabled, the space allocated to the dropped object and all associated objects (such as indexes) is immediately reflected in the data dictionary view DBA_FREE_SPACE. However, the space used by the objects still counts toward a user’s quota until the object is explicitly removed from the recycle bin or is forced out by new objects in the tablespace. In addition, the table and its dependent objects are renamed to a system-assigned name using this format:
The unique_id portion of the name is a 26-character globally unique name for the object. A table with the same name dropped in a different database will have a different unique_id. The version portion of the name is the version number of the dropped object, which is assigned by Oracle. The next time a table with the same name is dropped, both the unique_id and version are the same, but each will have different values for DROPTIME. You will learn how to select which version to recover later in this chapter.
Querying the Recycle Bin
To query the recycle bin, you can use the data dictionary view USER_RECYCLEBIN; RECYCLEBIN is a global synonym for USER_RECYCLEBIN. You can view purged objects for all users using the view DBA_RECYCLEBIN:
Note the column CAN_UNDROP. At first glance, you might think that if the object is in the recycle bin, it can be recovered. Upon closer inspection, you can see that CAN_UNDROP is set to NO for objects such as indexes because you cannot undrop an index. You must undrop the table first, and any associated indexes will be automatically undropped.
The data dictionary view USER_RECYCLEBIN has the same columns as DBA_RECYCLEBIN, except that USER_RECYCLEBIN does not have the OWNER column. This is consistent with all other Oracle data dictionary views that have the USER_, DBA_, and ALL_ prefixes, although in this case no ALL_RECYCLEBIN data dictionary view exists.
EXERCISE 9-1
Move Objects to the Recycle Bin
In this exercise, you’ll create and drop the same table twice, and then query the recycle bin to identify the name of the table and its dependent objects in the recycle bin.
1. Create the table VAC_SCHED and insert a row into it:
3. Create the table again, and insert a row with different values from those in the row you inserted into the first version of the table:
5. Query the recycle bin and confirm that the table has two different system-assigned names in the recycle bin:
Looking closely, you can see that the new names differ by one character in the eighth position of the unique_id portion of the name.
As you might expect, you can purge the entire contents of the recycle bin with the PURGE RECYCLEBIN command. If you have the appropriate privileges, you can purge the contents of the recycle bin for all users using the PURGE DBA_RECYCLEBIN command.
Restoring Tables from the Recycle Bin
To restore a table from the recycle bin, you use the FLASHBACK TABLE . . . TO BEFORE DROP command. If you specify the original table name in the command, the most recently dropped version of the table and its dependent objects are restored. If you want to restore a previous version of the same table, you must specify the name of the previous version in the recycle bin, as in this example:
Note that you will always have to put the recycle bin object name in double quotes due to lowercase or special characters in the base-64 string representation of the dropped table.
If you attempt to restore a table that has been re-created since it was dropped, you will receive an error unless you use the RENAME TO clause to give the restored table a new name. Here is an example:
When you flashback a table using the RENAME option, the table acquires its original name but the table’s dependent objects do not. If you want to keep the original names for the indexes, triggers, and constraints, query the recycle bin before you flashback and rename the other objects after the table is restored.
EXERCISE 9-2
Restore a Table from the Recycle Bin, Keeping the Original Dependent Object Names
This exercise picks up where you left off in Exercise 9-1. Query the recycle bin for the VAC_SCHED table and its dependent objects, restore the most recent version of the table, and rename the dependent objects to their original names.
1. Query the recycle bin and identify the most recently dropped version of VAC_SCHED along with its dependent objects:
2. Restore the most recent version of the table:
3. Rename the primary key constraint’s index to its original name (the index dropped at the same time as the table):
4. Query the data dictionary view USER_CONSTRAINTS to identify the name of the primary key constraint:
5. Rename the constraint to its original name, or at least to a more understandable name if the original name was system-generated:
Recycle Bin Space Reclamation
In the following sections, you’ll learn more about how Oracle manages the space in the recycle bin, how you can manually manage the space, and how you can query the contents of the recycle bin. Both automated and manual recycle bin space management functions can be used.
Automatic Recycle Bin Space Reclamation
The space in the recycle bin, and by extension the space in the tablespace containing the recycle bin, is managed automatically by Oracle. In other words, all dropped objects remain available for recovery in the recycle bin as long as new objects don’t need the space occupied by dropped objects.
Older objects in the recycle bin are removed before new objects when free space is low; in the next section, you’ll learn you how to remove objects selectively from the recycle bin. If the tablespace is autoextensible (the tablespace has the AUTOEXTEND ON attribute), space from dropped objects is used first. If insufficient space is available for a new object, the tablespace autoextends.
Manual Recycle Bin Space Reclamation
You can manually remove objects from the recycle bin by using the PURGE command. When you purge a table from the recycle bin, the table and all its dependent objects are removed as well. This makes sense, because you would not have much use for a table’s index once the table itself is gone!
When you purge a table from the recycle bin, you can use either the recycle bin name of the object or the original name of the object. If you specify the original table name, the oldest version of the table is purged first. Therefore, if you want to purge a more recent version of the table, use the recycle bin object name, as in this example:
You can also purge indexes in the recycle bin. This is useful if you want to keep tables in the recycle bin that otherwise might be aged out by new objects. If you need to recover a table from the recycle bin that no longer has its associated index, you can easily re-create it after you have recovered the table itself.
If you need even finer-grained control of which objects you can purge from the recycle bin, you can purge recycle bin objects from a specific tablespace for the current user, as in this example:
Furthermore, if you want to purge only objects owned by a particular user, and you have the DROP ANY TABLE system privilege, you can drop all recycle bin objects for a specific user, as in this example:
You can drop all objects from the recycle bin from all tablespaces if you have the SYSDBA privilege and you use the command PURGE DBA_RECYCLEBIN.
Bypassing the Recycle Bin
You can explicitly bypass the recycle bin when you drop a table by appending PURGE to the DROP TABLE command. This can be useful if you know that the table is temporary or has been erroneously created, and you will never need to resurrect it. Remember also that a dropped table that remains in the recycle bin still counts toward a user’s quota on the tablespace. Oracle’s definition of space pressure, which drives removal of objects from the recycle bin, includes a user exhausting her disk quota in a tablespace. Therefore, using DROP TABLE . . . PURGE will prevent removal of a user’s other objects in the recycle bin even if enough free space exists in the tablespace itself.
Another operation that bypasses the recycle bin is the DROP TABLESPACE command. This makes a lot of sense when you consider that the objects in the recycle bin are still in the tablespace—just renamed. Note that you must include the INCLUDING CONTENTS clause if any non–recycle bin objects exist in the tablespace when you use the DROP TABLESPACE command.
Finally, if you issue the DROP USER . . . CASCADE command, all the user’s objects are dropped from all tablespaces and not placed into the recycle bin. Any of the user’s objects will be automatically purged if they are already in the recycle bin when you issue the DROP USER command.
Accessing Tables in the Recycle Bin
When an object resides in the recycle bin, you can still use a SELECT statement to access the dropped table. Also, the dropped table still appears in the data dictionary views DBA_TABLES, DBA_OBJECTS, and DBA_SEGMENTS. Other than the very cryptic name of the table in the recycle bin, if the value of the column DROPPED in these views is set to YES, then you know the table is in the recycle bin.
Here is an example of accessing a table in the recycle bin:
Note that you can also use the AS OF clause in a SELECT query on a table in the recycle bin for flashback queries, which are covered later in this chapter. No other Data Manipulation Language (DML) or Data Definition Language (DDL) operations are allowed on tables in the recycle bin unless you recover them with the FLASHBACK TABLE . . . TO BEFORE DROP command first.
CERTIFICATION OBJECTIVE 9.02
Perform Flashback Query
Flashback query makes it easy to see a row in a table at a particular time. In the following sections, you’ll learn how to use Flashback Query to view one or more rows in a table at a time in the past. In addition, you’ll see how to use flashback version query to view a single row’s contents over a specified time range. Before you dive into any undo-dependent flashback features, however, you need to get familiar with the basic tablespace and initialization parameters that support flashback operations.
Configuring Flashback Parameters
To utilize flashback functionality fully, you need to configure your undo tablespace correctly. Your initialization parameters for the undo tablespace should look something like the following:
The UNDO_RETENTION parameter in this example specifies that the undo tablespace should retain undo data for at least 1800 seconds (30 minutes) as long as enough space exists in the undo tablespace. Setting UNDO_MANAGEMENT to AUTO directs Oracle to subsequently adjust the undo retention based on the size of the undo tablespace. By default, unexpired undos will be overwritten to ensure that DML operations will not fail due to a lack of available space in the undo tablespace.
|
The To ensure the success of flashback operations or long-running queries at the expense of DML activity, you must specify RETENTION GUARANTEE for the undo tablespace either when you create the tablespace or later with the ALTER TABLESPACE command, as in this example:
You can check the retention status of an undo tablespace by querying the data dictionary view DBA_TABLESPACES, as in this example:
When retention guarantee is enabled, the specified minimum undo retention period is guaranteed. As a result, DML activity can fail if long-running queries or flashback operations are using unexpired undo information.
If you need a much longer retention period for a subset of tables in the database, you should use Flashback Data Archive, which is covered later in this chapter.
Using Flashback Query
Flashback Query allows you to query data in one or more tables in a SELECT query as of a time in the past. Any changes to data in a table generates undo (or optionally data in a Flashback Data Archive), which can give you a snapshot of the entire database down to the granularity of a transaction.
Flashback Query uses the AS OF clause to specify the previous point in time as a timestamp or System Change Number (SCN). In the following example, the user HR is cleaning up the EMPLOYEES table and deletes two employees who no longer work for the company:
Normally, the user HR will copy these rows to the EMPLOYEES_ARCHIVE table first, but she forgot to do that this time. The user HR doesn’t need to put those rows back into the EMPLOYEES table, but she needs to put the two deleted rows into the archive table. Because the user HR knows she deleted the rows less than an hour ago, she can use a relative timestamp value with Flashback Query to retrieve the rows:
We can use this to retrieve the employee records that existed an hour ago but do not exist now because we know that EMPLOYEE_ID is the primary key of the table. Note also that we didn’t have to know which records were deleted. We essentially compare the table as it exists now to the table that existed an hour ago, and then insert the records that no longer exist in the original table into the archive table.
It is preferable to use the SCN for flashback over a timestamp. SCNs are exact, whereas the timestamp values are stored only every 3 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 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, which means restoring the table or a subset of the table to a location that differs from the original. This has the advantage of letting you manipulate the missing rows, if necessary, before placing them back in the table. For example, after you review 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 into the child table.
One of the disadvantages of an out-of-place restore using CTAS is that neither constraints nor indexes are rebuilt automatically.
Using Flashback Version Query
Flashback Version Query is another flashback feature that relies on undo data and provides a finer level of detail than an AS OF query (a Flashback 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 will return the entire history of a row between two SCNs or timestamps.
For the examples in this and the next sections, the user HR makes a number of changes to the HR.EMPLOYEES and HR.DEPARTMENTS tables:
The next day, the primary HR account user is out of the office and the other HR department employees want to know which rows and tables were changed. Using Flashback Version Query, the user HR (or any account with the required privileges), can see not only the values of a column at a particular time, but also the entire history of any 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. Also, the transaction ID and the type of operation performed on the row are available. Table 9-1 shows the pseudocolumns available with Flashback Version Query.
TABLE 9-1 Flashback Version Query Pseudocolumns
Pseudocolumn |
Description |
|
The starting SCN or timestamp when the change was made to the row. |
|
The ending SCN or timestamp when the change was no longer valid for the row. If this is |
|
The transaction ID of the transaction that created the row version. |
|
The operation performed on the row (I=Insert, D=Delete, U=Update). |
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, the user HR could have filtered the query by TIMESTAMP or displayed the TIMESTAMP values. If required later, either can be used in a Flashback Query or Flashback Table operation. From this output, you can 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 using Flashback Transaction.
Some restrictions and caveats do apply when you use Flashback Version Query. First, you can’t query these objects with the VERSIONS clause:
External tables
Fixed tables (internal Oracle X$ tables)
Temporary tables
Views
You would probably not consider querying most of these with the VERSIONS clause, except possibly views. As a result, you can use the VERSIONS clause as part of a view’s definition.
Finally, you cannot use the VERSIONS clause across DDL statements that change the structure of the tables in the query. In other words, you can go back in time only up to the first transaction after the last DDL statement on the table, which of course includes creating the table itself!
CERTIFICATION OBJECTIVE 9.03
Use Flashback Transaction
Once you have identified any erroneous or incorrect changes to a table, you 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.
The following sections provide the details for identifying the Structured Query Language (SQL) needed to reverse an entire transaction or part of the transaction. The data dictionary view FLASHBACK_TRANSACTION_QUERY has all the information you need to identify this SQL. You’ll learn how to identify the SQL required using this view and look at how to use the Enterprise Manager (EM) interface to accomplish the same task.
Understanding Flashback Transaction Query
Unlike a Flashback Version Query, a Flashback Transaction Query does not reference the table involved in DML transactions. Instead, you query the data dictionary view FLASHBACK_TRANSACTION_QUERY. The columns of FLASHBACK_TRANSACTION_QUERY are summarized in Table 9-2.
TABLE 9-2 FLASHBACK_TRANSACTION_QUERY Columns
Column Name |
Description |
|
Transaction ID number |
|
SCN for the first DML in the transaction |
|
Timestamp of the first DML in the transaction |
|
SCN when the transaction was committed |
|
Timestamp when the transaction was committed |
|
User who owned the transaction |
|
Undo SCN |
|
DML operation performed: |
|
Table changed by DML |
|
Owner of the table changed by DML |
|
ROWID of the row modified by DML |
|
SQL statement to undo the DML operation |
The table FLASHBACK_TRANSACTION_QUERY contains all changes to the database, including DDL operations. This makes sense, because Oracle uses tables and indexes to manage the data dictionary and space allocation. Thus, a DDL operation shows up in FLASHBACK_TRANSACTION_QUERY as a series of space management and metadata maintenance operations.
Dropped tables and users show up in FLASHBACK_TRANSACTION_QUERY as well. However, they no longer exist, so object numbers show up instead of table names and user ID numbers replace the usernames.
Undo space is not unlimited; thus you may have only partial transactions in FLASHBACK_TRANSACTION_QUERY. In this situation, the value of the OPERATION column contains UNKNOWN for any DML that is no longer in the undo tablespace for the selected transaction.
Flashback Transaction Query Prerequisites
Before you can use Flashback Transaction Query, you must enable additional logging to the redo log stream. The redo log stream is the same data that Log Miner uses, except with a different interface. Obviously, the redo log stream data is in addition to the information recorded in the undo tablespace. Both enhanced redo and undo information are required for Flashback Transaction Query.
First, enable logging of columns and primary key (PK) values referenced in DML changes, using these ALTER DATABASE commands:
Next, grant the appropriate permissions on the DBMS_FLASHBACK package, as well as give the SELECT ANY TRANSACTION privilege to the users who will be using Flashback Transaction Query:
Using Flashback Transaction Query
To investigate the changes that were made to the EMPLOYEES table, you can query the view FLASHBACK_TRANSACTION_QUERY with the oldest transaction from the Flashback Version Query shown earlier in this chapter:
This confirms what we already expected—that another user in the HR department made the deletion and salary update—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, to the contrary, 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, you might want to review the other updates before running the SQL code in the UNDO_SQL column.
Using EM with Flashback Transaction Query
Enterprise Manager (EM) provides an easy way to browse the contents of the FLASHBACK_TRANSACTION_QUERY view and optionally reverse some or all of the changes you see in that view. EM provides an easy-to-use GUI as the front end for the procedure DBMS_FLASHBACK.TRANSACTION_BACKOUT. From the EM home page, select the Schema tab and click the Tables link. In the Search boxes, select the HR schema and the table EMPLOYEES. After clicking the Go button, you will see the results shown in Figure 9-1.
FIGURE 9-1 EM table search results
Next, select Flashback Transaction from the Actions drop-down menu and click the Go button. In Figure 9-2, you see step 1 of the Flashback Transaction wizard, where you enter the beginning and ending SCNs from the Flashback Version Query earlier in the chapter. You also have the option to show all transactions for the table or a timestamp range. Once you identify the range, you can further filter the results by using a WHERE clause at the bottom of the page.
FIGURE 9-2 Specifying SCN range for Flashback Transaction Query
Click the Next button. You see the page in Figure 9-3 identifying the only transactions in the range you specified. Select the radio button next to the most recent transaction.
FIGURE 9-3 Identifying transaction to view in EM
Click the Next button to proceed to the next step. After a short wait, you will see the page shown in Figure 9-4—the Review screen, where you can back out the transaction.
FIGURE 9-4 Flashback Transaction Review screen
Click the Show Undo SQL Script button in Figure 9-4 and you’ll see the page in Figure 9-5 that shows the SQL that EM will perform to roll back the selected transaction.
FIGURE 9-5 Flashback Transaction SQL
Choosing Transaction Back-out Options
Note in Figure 9-5 that the transaction rollback will use the NOCASCADE option as the default. In other words, the transaction will back out the transaction expecting no dependent transactions. If any dependent transactions exist, EM will offer the three other options when it runs the procedure DBMS_FLASHBACK.TRANSACTION_BACKOUT. Here is a summary of all four backout options:
CASCADE Back out all transactions and their dependent transactions.
NOCASCADE Back out only the specified transaction, if possible.
NOCASCADE_FORCE Back out the specified transactions, ignoring dependent transactions.
NONCONFLICT_ONLY Back out changes to nonconflicting rows of the specified transaction only.
Note that using NONCONFLICT_ONLY leaves the database in a consistent state but the specified transactions are no longer atomic.
CERTIFICATION OBJECTIVE 9.04
Perform Flashback Table Operations
Oracle’s Flashback Table feature restores the state of rows in a table as of a point of time in the past. It also restores the table’s indexes, triggers, and constraints, while the database is online. This increases the overall availability of the database. In the following sections, you’ll learn more about when to use Flashback Table and how to configure your environment for Flashback Table. You’ll also see some real-world scenarios for which you might use Flashback Table.
Understanding Flashback Table
A 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 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. Since Flashback Table uses the undo tablespace, your window of recovery is relatively limited compared to other recovery methods, such as RMAN-based recovery or Flashback Database.
Flashback Table is performed in-place, while the database is online, rolling back changes to the table and all its dependent objects, such as indexes. If the table has other tables as dependent objects, you can specify more than one table in the FLASHBACK TABLE command. Whether you specify one or many tables in a Flashback Table operation, the operation is considered to be a single transaction. All changes are successful or they are rolled back as in a traditional transaction.
Nonsystem users can perform the flashback as long as they have the appropriate privileges. You’ll learn how to configure all aspects of using Flashback Table in the next section.
Configuring Flashback Table
To perform Flashback Table, a user must have the FLASHBACK ANY TABLE privilege or the FLASHBACK object privilege on a specific table:
As is true with all schema objects, a user does not need additional privileges to flashback her own tables. However, to use Flashback Table on a table or tables, you must enable row movement on the table before performing the flashback operation. Row movement need not be in effect when the user error occurs. Row movement is also required to support Oracle’s segment shrink functionality. This is 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.
Using Flashback Table
Before using Flashback Table, you must consider a few restrictions. First, even with the appropriate privileges, you cannot perform Flashback Table on system tables, fixed (X$) tables, or remote tables.
In addition, Flashback Table operations cannot span DDL operations, such as adding or dropping a column. This is true with all flashback features except for Flashback Database. However, you can flashback a table to a point in time before an index on the table was dropped, although the index will not be re-created during the Flashback Table operation.
Finally, any statistics gathered for the tables in the FLASHBACK TABLE command are not flashed back. As a result, it is a good practice to gather new statistics on the tables immediately after the Flashback Table operation is complete.
EXERCISE 9-3
Use Flashback Table on a Table
In this exercise, you will use Flashback Table to recover from accidental deletion of all rows from the EMPLOYEES table.
1. Enable row movement for several tables. You can safely enable row movement because none of your applications reference your tables by ROWID:
2. "Inadvertently” delete all the rows in the EMPLOYEES table:
3. The HR user can bring back the entire table quickly without calling the DBA. This is because the undo tablespace is large enough and the HR user notices the problem within the retention period:
If the accidental deletions were not noticed right away, and changes were made to dependent tables in the meantime, you can include the dependent tables in the Flashback Table operation as well:
CERTIFICATION OBJECTIVE 9.05
Set Up and Use a Flashback Data Archive
Regulations such as Sarbanes-Oxley (2002) and the Health Insurance Portability and Accountability Act of 1996 (HIPAA) require strict control and tracking requirements for customer and patient data. Retaining 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. You can use Flashback Data Archive to save historical changes automatically to all key tables for as long as regulatory agencies or your stakeholders require.
Understanding Flashback Data Archive
Flashback Data Archive is implemented natively in Oracle (as opposed to an application layer using triggers or a set of PL/SQL packages). 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 records only 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 Archives have 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.
|
online and archived redo logs, flashback logs, Flashback Data Archives, and the recycle bin support a particular flashback feature. |
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. Flashback Version Query and Flashback Transaction Query can also use the data in a Flashback Data Archive. In the following sections, you’ll learn 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 practices recommend 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 later in this chapter, in the section “Managing Flashback Data Archives.”
Depending on your business requirements, you can enable and disable Flashback Data Archive on a table at will. For example, you might want to drop a column to a table being tracked by Flashback Data Archive. However, no DDL statements are allowed on tables being tracked using Flashback Data Archive except for adding columns. Once you disable Flashback Data Archive for a table, the historical data for the table is lost even if you immediately re-enable it for the table.
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. We’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 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 ADMINISTER privilege also includes privileges for 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 the following 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, 2005:
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 in the previous command that specified a particular archive for the HR.EMPLOYEES table, if you did not specify an archive, Oracle assigns 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 Archives 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 EMPLOYEES table. This is a DELETE statement from three weeks ago:
The HR employee needs to find the hire date for employee 169. She retrieves the historical information from the EMPLOYEES table with the AS OF clause specifying a time four weeks ago:
It is completely transparent to the user whether Oracle is using an undo tablespace or a Flashback Data Archive for a query containing AS OF.
CERTIFICATION OBJECTIVE 9.06
Configure, Monitor Flashback Database and Perform Flashback Database Operations
As you might expect, Oracle’s Flashback Database feature uses the FLASHBACK DATABASE command to return the database to a past time or SCN, providing a fast alternative to performing incomplete database recovery. In the following sections, you’ll learn how to configure Flashback Database, you’ll step through a simple example, and you’ll learn how to monitor a Flashback Database. In addition, you’ll review some of the finer points, such as how to exclude one or more tablespaces from a Flashback Database operation and how to use guaranteed restore points.
Understanding Flashback Database
You can use Flashback Database to bring your entire database quickly to a previous point in time. When you enable Flashback Database, the before images of modified blocks are saved in the flash recovery area as Flashback Database logs. When a logical corruption occurs that requires a recovery to a time in the past, the Flashback Database logs restore the data blocks’ before images, and then the archived and online redo logs roll forward to the desired flashback time. This process is typically much faster than performing a traditional restore and recovery operation, because the database’s datafiles do not need to be restored.
When Flashback Database is enabled, the before image data is saved in a buffer that is appropriately named the flashback buffer. Then it uses the Recovery Writer (RVWR) background process to save the before image information from the flashback buffer to the Flashback Database logs in the flash recovery area. The logs in the flash recovery area are reused in a circular fashion. How far back you can rewind the database depends on the amount of space in your flash recovery area and the guaranteed restore points you have configured. You’ll learn more about guaranteed restore points later in this section.
Configuring Flashback Database
To use Flashback Database, you must configure the flash recovery area (see Chapter 2 for more information on configuring the flash recovery area using the parameters DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE). Configuring the size of the flash recovery area correctly ensures that enough space exists for Flashback Database logs in addition to all the other information in the flash recovery area, such as archived redo log files and RMAN backups. You set the initialization parameter DB_FLASHBACK_RETENTION_TARGET to an upper limit (in minutes) for your usable recovery window. This parameter is a target and not a guarantee. You will use guaranteed restore points to ensure the retention of Flashback Database logs in the flash recovery area.
Here is the typical sequence of commands you will use to enable Flashback Database:
In this example, the target retention time for Flashback logs is 2880 minutes (2 days).
Using Flashback Database
The most straightforward scenario for using Flashback Database is to restore the entire database to a specific timestamp. However, many scenarios are not this clear. In the following sections, you’ll learn how to use Flashback Database with an SCN or a guaranteed restore point. You’ll also learn how to exclude one or more tablespaces from the Flashback Database operation, and you’ll see some dynamic performance views that can help you monitor the ability to meet your retention target.
Performing Flashback Database
You can use the FLASHBACK DATABASE command from RMAN or from the SQL< prompt. Some subtle syntactical differences exist between the two versions. The RMAN version offers you some additional granularity options, such as flashing back to a particular log sequence number and thread (instance). Here’s an example:
We’ll use the SQL version of the FLASHBACK DATABASE command in the rest of this section. The basic syntax for the SQL FLASHBACK DATABASE command is as follows:
You can use either the TO SCN or TO TIMESTAMP clause to set the point to which the entire database should be flashed back, which is in addition to a guaranteed restore point. You can flash back TO BEFORE a critical point, such as a transaction that produced an unintended consequence for multiple tables. Use the ORA_ROWSCN pseudocolumn for a particular table row to see the SCNs of the most recent changes to the row:
With the database open for more than an hour, verify that the flashback data is available and then flash it back—you will lose all transactions that occurred during that time:
When you execute the FLASHBACK DATABASE command, Oracle checks to make sure all required archived and online redo log files are available. If the logs are available, the online datafiles are reverted to the time, SCN, or guaranteed restore point specified.
If there is not enough data online in the archive logs and the flashback area, you will need to use traditional database recovery methods to recover the data. For example, you might need to use a file system recovery method followed by rolling the data forward.
Once the flashback has completed, you must open the database using the RESETLOGS option to have write access to the database:
To turn off the flashback database option, execute the ALTER DATABASE FLASHBACK OFF command when the database is mounted but not open:
Excluding Tablespaces from Flashback Database
By default, all tablespaces will participate in a Flashback Database operation unless you change the FLASHBACK attribute to OFF at the time the tablespace is created or later using the ALTER TABLESPACE command. Here’s an example:
To re-enable Flashback Database on this tablespace, you use ALTER TABLESPACE EXAMPLE FLASHBACK ON, as you’d expect, but the database must be shut down and reopened in MOUNT mode.
When you need to use Flashback Database, offline all tablespaces with the FLASHBACK attribute set to OFF. When the database is back up, you can use other point-in-time recovery methods to recover the offline datafiles and eventually bring them back online.
Using Guaranteed Restore Points
A guaranteed restore point is similar to a regular restore point in that it can be used as an alias for an SCN during a recovery operation. A guaranteed restore point is different in that it is not aged out of the control file and must be explicitly dropped. Not surprisingly, guaranteed restore points are useful for Flashback Database operations. Creating a guaranteed restore point when you have flashback logging enabled ensures that flashback logs are retained in the flash recovery area so that the database can be rolled back to any point after the creation of the guaranteed restore point.
Here is an example of a guaranteed restore point created before a major application upgrade:
Here is how you would use this guaranteed restore point:
To use guaranteed restore points, you must also enable these prerequisites:
The COMPATIBLE initialization parameter must be 10.2 or higher.
The database must be running in ARCHIVELOG mode.
You must have archived redo log files available starting from the time of the first guaranteed restore point.
You must have a flash recovery area configured.
Keep in mind that guaranteed restore points will likely cause space pressure in the flash recovery area over time because Oracle will retain any flashback logs in the flash recovery area after the first guaranteed restore point.
Monitoring Flashback Database
You can use several dynamic performance views to monitor the space usage of the flash recovery area to ensure that you can meet the retention target for possible Flashback Database operations.
You can determine how far back you can flashback the database by querying the V$FLASHBACK_DATABASE_LOG view. The amount of flashback data retained in the database is controlled by the initialization parameter and the size of the flash recovery area. The following listing shows the available columns in V$FLASHBACK_ DATABASE_LOG and sample contents:
You can verify the database’s flashback status by querying V$DATABASE. The FLASHBACK_ON column will have a value of YES if the flashback has been enabled for the database:
Finally, you can use the view V$FLASHBACK_DATABASE_STAT to monitor the rate at which flashback data is generated on an hour-by-hour basis:
FLASHBACK_DATA is the number of bytes of flashback data written during the interval. REDO_DATA is the number of bytes of redo written during the same period. DB_DATA is the number of bytes in all data blocks written. The column ESTIMATED_FLASHBACK_ SIZE (abbreviated to EST_FB_SZ) contains the same value as ESTIMATED_FLASHBACK_SIZE in V$FLASHBACK_DATABASE_LOG.
CERTIFICATION SUMMARY
The beginning of the chapter reviewed the Flashback Drop feature that uses a recycle bin construct supported in every tablespace. It behaves much like the recycle bin on a Windows-based computer. If enough room is available in the tablespace, dropped objects can be restored to their original schema with all indexes, triggers, and constraints intact. The recycle bin uses a data dictionary table that keeps track of dropped objects. You can restore one or more versions of a dropped table from the recycle bin as long as the recycle bin is enabled and space pressure in the tablespace has not purged the dropped table from the tablespace.
Next, you learned the basics of Flashback Query and how it can view one or more rows of a table at a time in the past. To use Flashback Query, you must first properly configure automatic undo management, and then size the undo tablespace to accommodate undo retention of undo data as far back as needed. Undo retention must accommodate DML transactions, query read consistency, and support Flashback Query to the desired point of time in the past.
Flashback Transaction is similar to Flashback Query, except Flashback Transaction uses the data dictionary view FLASHBACK_TRANSACTION_QUERY to contain information about past transactions on one or more tables. To support Flashback Transaction, you must enable supplemental logging for all table columns and the primary key column, somewhat increasing the amount of data that the Log Writer (LGWR) process writes to the redo log files. Flashback Transaction leverages Log Miner technology to retrieve the details on previous transactions. Once you’ve identified the changes made within a transaction, you can use Enterprise Manager or the UNDO_SQL column in FLASHBACK_TRANSACTION_QUERY to reverse part or all of the transaction. This depends on your tolerance for atomicity and other subsequent transactions in the database.
Flashback Table is another technology that lets you rewind the state of one table or a group of tables to a time in the past. Like many flashback features, Flashback Table relies on data in the undo tablespace and is subject to the configured retention policy.
Flashback Data Archive provides a way to preserve the history of selected tables over a much longer period of time than is supported by the undo tablespace. To configure and use Flashback Data Archive, 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. Once changes start recording in the Flashback Data Archive, you can use the familiar AS OF clause in the SELECT statement to view previous versions of rows in a table.
Finally, you learned how to configure and use Flashback Database to roll back the entire database to a time in the past. After you configure the flash recovery area to retain the before images of changed data blocks (flashback logs), you can recover the database to a specific timestamp or SCN as long as the required flashback logs are still in the flash recovery area. You can use guaranteed restore points to ensure that the database can be rolled back to the guaranteed restore point or any SCN or timestamp since you created the guaranteed restore point.
TWO-MINUTE DRILL
Restore Dropped Tables from the Recycle Bin
Flashback Drop uses the recycle bin to recover dropped tables.
The recycle bin is a data dictionary table that keeps track of dropped objects.
You can restore the current or previous versions of dropped tables from the recycle bin.
When you drop an object with the recycle bin enabled, the space allocated to the dropped object and all associated objects (such as indexes) is immediately reflected in the data dictionary view DBA_FREE_SPACE.
When a table is dropped, the table and its dependent objects are renamed to a system-assigned name using the format BIN$unique_id$version.
To query the recycle bin, you can use the data dictionary view USER_RECYCLEBIN. RECYCLEBIN is a global synonym for USER_RECYCLEBIN.
The data dictionary view USER_RECYCLEBIN has the same columns as DBA_RECYCLEBIN, except that USER_RECYCLEBIN does not have the OWNER column.
To restore a table from the recycle bin, you use the FLASHBACK TABLE . . . TO BEFORE DROP command.
If you attempt to restore a table that has been re-created since it was dropped, you will receive an error unless you use the RENAME TO clause to give the restored table a new name.
The space in the recycle bin, and by extension the space in the tablespace containing the recycle bin, is managed automatically by Oracle.
All dropped objects remain available for recovery in the recycle bin as long as new objects don’t need the space occupied by dropped objects.
You can use the PURGE command to remove tables manually from the recycle bin.
When an object resides in the recycle bin, you can still use a SELECT statement to access the dropped table. The dropped table still appears in the data dictionary views DBA_TABLES, DBA_OBJECTS, and DBA_SEGMENTS.
Perform Flashback Query
Flashback query enables you to view one or more rows in a table at a time in the past.
To ensure the success of flashback operations or long-running queries at the expense of DML activity, you must specify RETENTION GUARANTEE for the undo tablespace.
You can check the retention status of an undo tablespace by querying the data dictionary view DBA_TABLESPACES.
Flashback Query uses the AS OF clause to specify the previous point in time as a timestamp or SCN.
Flashback Version Query, another flashback feature that relies on undo data, provides a finer level of detail than an AS OF query (a Flashback Query).
A Flashback Version Query uses the VERSIONS BETWEEN clause to specify a range of SCNs or timestamps for analysis of a given table.
Use Flashback Transaction
The data dictionary view FLASHBACK_TRANSACTION_QUERY has all the information you need to identify the SQL required to reverse a transaction.
Before you can use Flashback Transaction Query, you must enable additional logging to the redo log stream. This is the same data that Log Miner uses although using a different interface.
You must grant permissions on the DBMS_FLASHBACK package, as well as the SELECT ANY TRANSACTION privilege to the users who will be using Flashback Transaction Query.
The UNDO_SQL column of FLASHBACK_TRANSACTION_QUERY contains the actual SQL code that can be used to reverse the effect of the transaction.
Enterprise Manager (EM) provides an easy-to-use GUI as the front end for the procedure DBMS_FLASHBACK. TRANSACTION_BACKOUT.
The four transaction backout options are CASCADE, NOCASCADE, NOCASCADE_FORCE, and NONCONFLICT_ONLY.
Perform Flashback Table Operations
Oracle’s 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.
Flashback Table is preferable to other flashback methods if the scope of user errors is small and limited to one or very few tables.
Flashback Table is performed in place while the database is online, rolling back changes to the table and all its dependent objects, such as indexes.
To perform Flashback Table, a user must have the FLASHBACK ANY TABLE privilege or the FLASHBACK object privilege on a specific table.
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.
Flashback Table operations cannot span DDL operations, such as adding or dropping a column.
Set Up and Use a Flashback Data Archive
A Flashback Data Archive retains historical data for one or more tables for a specified retention period.
To enable a Flashback Data Archive, 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 records only UPDATE and DELETE statements but not INSERT statements.
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.
You create one or several Flashback Data Archives in existing tablespaces using the CREATE FLASHBACK ARCHIVE command.
The data dictionary views supporting Flashback Data Archives are DBA_FLASHBACK_ARCHIVE and DBA_FLASHBACK_ARCHIVE_TS.
The view DBA_FLASHBACK_ARCHIVE_TABLES tracks the tables enabled for flashback archiving.
A user must have the FLASHBACK ARCHIVE ADMINISTER system privilege to create or modify Flashback Data Archives.
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.
Configure, Monitor Flashback Database and Perform Flashback Database Operations
Flashback Database uses the FLASHBACK DATABASE command to return the database to a past time or SCN, providing a fast alternative to performing incomplete database recovery.
When you enable Flashback Database, the before images of modified blocks are saved in the flash recovery area as Flashback Database logs.
The logs in the flash recovery area are reused in a circular fashion.
Configuring the size of the flash recovery area correctly ensures that enough space is available for Flashback Database logs in addition to all the other information in the flash recovery area.
You set the initialization parameter DB_FLASHBACK_RETENTION_TARGET to an upper limit (in minutes) for your usable recovery window; this is a target, not a guarantee.
You can use the FLASHBACK DATABASE command from RMAN or from the SQL< prompt.
You can use either the TO SCN or TO TIMESTAMP clause to set the point to which the entire database should be flashed back, in addition to a guaranteed restore point.
You can use the ORA_ROWSCN pseudocolumn for a given table row to see the SCNs of the most recent changes to a table’s row.
If not enough data exists in the archive logs and the flashback area, you will need to use traditional database recovery methods to recover the data.
To turn off the flashback database option, execute the ALTER DATABASE FLASHBACK OFF command when the database is mounted but not open.
By default, all tablespaces will participate in a Flashback Database operation unless you change the FLASHBACK attribute to OFF at the time the tablespace is created, or later using the ALTER TABLESPACE command.
A guaranteed restore point is similar to a regular restore point in that it can be used as an alias for an SCN during a recovery operation.
A guaranteed restore point is different in that it is not aged out of the control file and must be explicitly dropped.
Creating a guaranteed restore point when you have flashback logging enabled ensures that flashback logs are retained in the flash recovery area so that the database can be rolled back to any point after the creation of the guaranteed restore point.
You can determine how far back you can flashback the database by querying the V$FLASHBACK_DATABASE_LOG view.
You can use the view V$FLASHBACK_DATABASE_STAT to monitor the rate at which flashback data is generated on an hour-by-hour basis.
SELF TEST
The following questions will help you measure your understanding of the material presented in this chapter. Read all the choices carefully, because there might be more than one correct answer. Choose all correct answers for each question.
Restore Dropped Tables from the Recycle Bin
1. Which of the following statements is true about the recycle bin?
A. When you drop an object, the space allocated by the object is not immediately reflected in DBA_FREE_SPACE and counts against the user’s quota.
B. When you drop an object, the space allocated by the object is immediately reflected in DBA_FREE_SPACE and does not count against the user’s quota.
C. When you drop an object, the space allocated by the object is immediately reflected in DBA_FREE_SPACE but still counts against the user’s quota.
D. When you drop an object, the space allocated by the object is not immediately reflected in DBA_FREE_SPACE and does not count against the user’s quota.
2. The column CAN_UNDROP is set to YES for an object in the view DBA_RECYCLEBIN. Which of the following is true for this object? (Choose all that apply.)
A. The object is a table.
B. The object can be undropped by the user who owns the object.
C. The object can be undropped only by a user with DBA privileges.
D. The object does not have any dependent objects in the recycle bin.
E. No existing object with the same name exists outside of the recycle bin.
Perform Flashback Query
3. Which of the following parameters directly affect the behavior and proper functioning of Flashback Table? (Choose all that apply.)
A. DB_RECOVERY_FILE_DEST
B. UNDO_MANAGEMENT
C. DB_RECOVERY_FILE_DEST_SIZE
D. UNDO_TABLESPACE
E. UNDO_RETENTION
4. When using the VERSIONS BETWEEN clause for Flashback Version Query, what can’t you use to restrict the number of rows returned by the query?
A. A timestamp
B. An SCN
C. A WHERE clause on any column in the table
D. A guaranteed restore point
Use Flashback Transaction
5. Which of the following columns is not in the data dictionary view FLASHBACK_TRANSACTION_QUERY?
A. UNDO_SQL
B. XID
C. OPERATION
D. ORA_ROWSCN
6. What happens to the rows in FLASHBACK_TRANSACTION_QUERY when part of the transaction is no longer available in the undo tablespace?
A. The user ID number replaces the user name in the LOGON_USER column.
B. The OPERATION column contains the value UNKNOWN.
C. The object number replaces the table name in the TABLE_NAME column.
D. The OPERATION column contains the value UNAVAILABLE.
E. All rows for the transaction are no longer available in FLASHBACK_TRANSACTION_QUERY.
Perform Flashback Table Operations
7. What methods can you use in the AS OF clause of a Flashback Table operation to specify the time in the past to which you want to recover the table? (Choose all that apply.)
A. A timestamp
B. A filter condition in the WHERE clause
C. An SCN
D. A restore point
E. A guaranteed restore point
8. You create the table VAC_SCHED on Monday with a primary key index; the SCN right after table creation was 5680123. On Wednesday, you drop the index. On Thursday, you accidentally delete most of the rows in the database. On Friday, you execute this command:
You have set guaranteed undo retention to 1 week. What is the result of running this command?
A. The table is recovered to SCN 5680123 without the index.
B. The table is recovered using the data in the undo tablespace and the index is re-created using the dropped index in the recycle bin.
C. The table is recovered and all rows deleted on Thursday are restoring using archived and online redo log files.
D. The command fails because FLASHBACK TABLE cannot recover a table before a change to a dependent object.
Set Up and Use a Flashback Data Archive
9. Identify the true statement about Flashback Data Archives.
A. You can specify more than one default Flashback Data Archive.
B. If you do not specify a RETENTION clause for a Flashback Data Archive, you must specify it when assigning a table to the Flashback Data Archive.
C. The QUOTA parameter is required when creating a Flashback Data Archive to limit the amount of space used in the tablespace.
D. A Flashback Data Archive can exist in multiple tablespaces including undo tablespaces and temporary tablespaces.
10. Which of the following data dictionary views contains a list of the tables using a Flashback Data Archive?
A. DBA_FLASHBACK_ARCHIVE_TABLES
B. DBA_FLASHBACK_ARCHIVE
C. DBA_FLASHBACK_ARCHIVE_TS
D. DBA_FLASHBACK_DATA_ARCHIVE_TABLES
Configure, Monitor Flashback Database and Perform Flashback Database Operations
11. Which of the following initialization parameters is not required to configure Flashback Database operations?
A. DB_RECOVERY_FILE_DEST_SIZE
B. UNDO_RETENTION
C. DB_FLASHBACK_RETENTION_TARGET
D. DB_RECOVERY_FILE_DEST
12. What is the difference between a regular restore point and a guaranteed restore point? (Choose all that apply.)
A. A regular restore point does not require that a flash recovery area be configured.
B. A guaranteed restore point can be used only with Flashback Database.
C. A guaranteed restore point cannot be dropped.
D. A guaranteed restore point will never be aged out of the control file.
E. You must have flashback logging enabled to use guaranteed restore points.
SELF TEST ANSWERS
Restore Dropped Tables from the Recycle Bin
1. 
C. A dropped object’s space is immediately reflected in DBA_FREE_SPACE but still counts against the user’s quota until it is purged from the recycle bin.
A, B, and D are incorrect. All three reflect incorrect statements about free space management and quota management for objects in the recycle bin.
2. A and B. Table objects in the recycle bin can be undropped, and they can be undropped by the original owner or a user with DBA privileges.
C is wrong because an object in the recycle bin can be undropped by the owner or a user with DBA privileges; the view DBA_RECYCLEBIN has an OWNER column to indicate which user dropped the object. D is wrong because a table in the recycle bin may or may not have dependent objects in the recycle bin. E is wrong because there may or may not be an object with the same original name as an object in the recycle bin.
Perform Flashback Query
3. B, D, and E. For Flashback Query, Flashback Table, Flashback Transaction Query, and Flashback Version Query, you must have automatic undo management configured, an undo tablespace defined, and an undo retention value to specify how long undo data is retained in the undo tablespace.
A and C are wrong. The parameters DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE are used to configure Flashback Data Archive retention, but not Flashback Query.
4. D. Guaranteed restore points are used only in recovery scenarios such as Flashback Database.
A, B, and C can be used and are therefore wrong. You can restrict the results of a Flashback Version Query by SCN or timestamp. You can further filter the rows by using a WHERE clause on the table columns.
Use Flashback Transaction
5. D. ORA_ROWSCN is a pseudocolumn that is available for all tables and contains the last SCN that modified or created the row.
A, B, and C are wrong. UNDO_SQL is the SQL you can use to reverse the change to the row, XID is the transaction ID, and OPERATION is the DML operation performed.
6. B. The OPERATION column in FLASHBACK_TRANSACTION_QUERY contains UNKNOWN for data no longer in the undo tablespace.
A is wrong because the user ID replaces the user name in the LOGON_USER column when the user no longer exists. C is wrong because the object number replaces the table name in the TABLE_NAME column when the table no longer exists. D is wrong because the OPERATION column contains UNKNOWN, not UNAVAILABLE, when the information is no longer available in the undo tablespace. E is wrong because part of a transaction might still be available in the undo tablespace.
Perform Flashback Table Operations
7. A, C, D, and E. You can use the AS OF clause with the TIMESTAMP or SCN qualifier to specify a time to which you want to recover the table. In addition, you can specify a restore point or a guaranteed restore point for Flashback Table. Guaranteed restore points are also useful in Flashback Database operations to ensure that flashback logs are maintained in the flash recovery area at least a far back as the earliest guaranteed restore point.
B is wrong because you cannot use a WHERE clause to specify the time in the past for the FLASHBACK TABLE operation.
8. A. The table is recovered to its original state right after creation with no rows and without the index.
B is wrong because FLASHBACK TABLE does not leverage the recycle bin. C is wrong because the table is recovered as of the SCN, but not rolled forward. D is wrong because a dropped index does not affect the recoverability of a table; however, a change to the structure of the table itself prevents a flashback operation to before the DDL change to the table.
Set Up and Use a Flashback Data Archive
9. B. You must either specify a default retention period for the Flashback Data Archive itself or specify a retention period when adding the table to the archive.
A is wrong because you can have several Flashback Data Archives. C is wrong because the QUOTA parameter is needed only if you want to limit the amount of space used by the Flashback Data Archive in the tablespace; otherwise it can grow to use all available space in the tablespace. D is wrong because you can create Flashback Data Archives only in permanent, non-undo tablespaces.
10. A. DBA_FLASHBACK_ARCHIVE_TABLES contains a list of tables currently using a Flashback Data Archive.
B is wrong because DBA_FLASHBACK_ARCHIVE contains a list of the archives, but not the tables within. C is wrong because DBA_FLASHBACK_ARCHIVE_TS contains the archive to tablespace mapping. D is wrong because DBA_FLASHBACK_DATA_ARCHIVE_TABLES is not a valid data dictionary view.
Configure, Monitor Flashback Database and Perform Flashback Database Operations
11. B. The initialization parameter UNDO_RETENTION is required for other Flashback features, but not for Flashback Database.
A, C, and D are wrong. The parameters DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST are required to define the location and size of the Flash Recovery area, and DB_FLASHBACK_RETENTION_TARGET is needed to define a desired upper limit for the Flashback Database recovery window.
12. A and D. A regular restore point does not require a flash recovery area, and it can be aged out of the control file; a guaranteed restore point will never be aged out of the control file unless it is explicitly dropped.
B is wrong because a guaranteed restore point can be referenced for other flashback features, not just Flashback Database. C is wrong because you can explicitly drop any type of restore point. E is wrong because you can define guaranteed restore points without flashback logging enabled; however, you must still have a flash recovery area enabled.