CHAPTER 26

Flashback

Exam Objectives

• 63.1.14.1    Describe the Flashback Technologies

• 63.1.14.2    Use Flashback to Query Data

• 63.1.14.3    Perform Flashback Table Operations

• 63.1.14.4    Perform Table Recovery from Backups

• 63.1.14.5    Describe and Use Flashback Data Archive

• 63.1.14.6    Perform Flashback Database

To protect against loss of data because of physical damage to the database, you use backups and archive logging. If they are appropriately managed, you can guarantee that your database will never lose one row of committed data. But what about damage caused by users? What if someone has accidentally (or deliberately) committed a bad transaction? Dropped a table? Any such actions are not errors as far as the database is concerned, and the D (for durability) of the ACID test means that Oracle is not allowed to reverse these actions. However, the various flashback technologies let the database administrator (DBA) do just that: take the entire database, or perhaps just one table or even one transaction, back in time to reverse the effect of the error. A related capability is the ability to query the database as it was at some time in the past.

Describe the Flashback Technologies

Four distinct flashback technologies are available, each implemented with a different underlying architecture. Each technology has different capabilities and limitations, but there is overlapping functionality between them. The typical reason for using any type of flashback technology is to correct mistakes—it is vital to understand what type of flashback technology is appropriate for correcting different types of errors.

Flashback Database

Flashback Database is, by analogy, like pressing a rewind button on the database (if you cannot remember the concept of a rewind button, research the use of tape recorders). The current database is taken as the starting point, and it is taken back in time, change by change, reversing all work done sequentially. The end result is as if you had done an incomplete recovery: all work subsequent to the flashback point is lost, and the database must be opened with RESETLOGS. Clearly, this is a drastic thing to do. It allows you to back out changes that resulted in logical corruptions (in a business sense) such as inappropriate transactions (for example, running your year-end archive-and-purge routines before running your end-of-year reports).

Flashback Query, Transaction, and Table

Three flashback techniques are based on the use of undo segments. The first flashback capability was initially introduced with release 9i of the database and has been substantially enhanced subsequently.

Flashback Query (the release 9i feature) lets you query the database as it was at some time in the past, either for one select statement or by taking your session temporarily back in time so that all its queries will be against a previous version of the database. This can be used to see the state of the data before a set of transactions was committed. What did the tables look like half an hour ago? This can be invaluable in tracking down the cause of business data corruptions and can also be used to correct some mistakes. By comparing the current and previous versions of a table, you can identify what was done that was wrong. It is even possible to select all versions of a row over a period of time to show a history of what has happened to the row, when it happened, who did it, and the identifiers of the transactions that made each change.

Flashback Transaction automates the repair process. Once you have used Flashback Query to identify which transaction it was that caused the problem, Oracle can construct SQL statements that will reverse the changes. This is not the same as rolling back a committed transaction! It is impossible to roll back a committed change because the rules of a relational database do not permit this. But it is possible to construct another transaction that will reverse the effect of the first, erroneous transaction. Unlike Flashback Database, Flashback Transaction does not imply data loss; all other work done remains in effect, and the database stays current.

The third flashback technique based on undo data is Flashback Table. Having determined that inappropriate work has been committed against one table, you can instruct Oracle to reverse all changes made to that table since a particular point in time, while leaving all other tables current.

Throughout any Flashback Query, Flashback Transaction, or Flashback Table operation, the database remains open, and all objects (including those involved in the flashback) are available for use. Transactional integrity and constraints are always enforced, which means that the flashback operation might fail. For example, if a flashback of a transaction requires an insert into a primary key column, that value must not be in use. Flashing back one table may not be possible if it has foreign key constraints—you will have to flash back all the related tables in one operation.

Flashback Drop

It is now possible to “undrop” a table. This is implemented by mapping the DROP command onto a RENAME command. Rather than dropping the table, the table is renamed to a system-generated name and only dropped later, when its storage space is needed for a live object. If necessary and if its storage space has not been reused, the object can be renamed back to its original name and thus restored. Without this capability, the only way to get a table back after a drop would be to do an incomplete recovery to the point in time just before the table was dropped. This was usually time consuming, and it meant the loss of all work done subsequently. The new Flashback Database capability achieves the same result as incomplete recovery and should be much faster, but work done on other tables following the drop is lost, and the database will be unavailable until the operation is completed.

Flashback Drop lets you reinstate the table as it was at the time that it was dropped, with no loss of data whatsoever; the database remains current. This does not require any use of backups, and neither is there any downtime for users. Note that Flashback Drop is specifically for the DROP command; you cannot flash back a TRUNCATE command. Along with the table itself, any associated indexes, constraints, triggers, and permissions will also be restored.

Flashback Data Archive

The Flashback Data Archive provides the ability to view tables as they were at any time in the past. The forms of flashback described so far all have time limits: Flashback Database is restricted by the size of the flashback logs, Flashback Query by the undo retention, and Flashback Drop by the available space in tablespaces. A Flashback Data Archive can be configured to store before images of rows indefinitely. It must, however, be configured. Unlike Flashback Query and Flashback Drop, it is not enabled by default.

Enabling a table for Flashback Data Archive creates another table (and a few other objects) that will store all previous versions of rows, storing them forever if desired. When Data Manipulation Language (DML) is committed against the table, a background process called the Flashback Data Archive (FBDA) process will capture the necessary data and save it to the archive. From there, it can be queried with the same syntax used for a regular Flashback Query—but the flashback can go back years.

When a Flashback Data Archive is created, you specify a time limit (which may be years), and the FBDA will make sure that all data is saved until that time has passed and will then remove it. The FBDA is responsible for creating the objects in the archive, populating them with rows as necessary, and purging data that has passed the expiry date.

When to Use Flashback Technology

Human error has always been the most difficult type of error from which to recover. This is because as far as the database is concerned, human error is not an error at all. Depending on the nature of the error, the different flashback technologies may help you to recover while minimizing downtime and loss of data.

The most drastic flashback technique is Flashback Database. Consider using this only when you would also consider using incomplete recovery—the effect is the same, though the downtime will typically be much less. An example is when dropping a schema on the production system when you thought you were connected to the test system. A critical table truncation (though not a table drop) would also be a time to use Flashback Database.

Flashback Drop will restore a table (together with various dependent objects) to the state it was in at the time of the drop. Note that this will not restore a truncated table—only one that has been completely dropped. There is no downtime involved, other than the obvious fact that until the table is undropped, no one can get to it, and no work will be lost. Unlike Flashback Database, Flashback Drop does not require any configuration—it is always available, unless you specifically disable it.

For finer granularity of recovery, consider Flashback Table and Flashback Transaction. These should not affect the users at all, other than that the work reversed is gone—which is presumably the desired outcome. Like Flashback Drop, the Flashback Query, Transaction, and Table facilities are always available without any configuration other than granting appropriate privileges. They may, however, require some tuning of undo management.

The Flashback Data Archive is for long-term storage. Typically, this will be for legal reasons; in many jurisdictions there are requirements for keeping data for years and then for destroying it (sometimes known as digital shredding). A Flashback Data Archive can enable this transparently. The DBA can thus guarantee legal compliance without the need for any programming effort.

In some cases, you will have a choice of flashback technologies. Consider an example where a batch job is run twice. Perhaps you import a few hundred thousand invoices into your accounting system from your billing system every day, and through some mistake and lack of validation, the same billing run is imported twice. If the import is done as one huge transaction, then Flashback Transaction will reverse it. But if it is done as many small transactions, rather than reversing them all, it may be easier to do a table-level flashback of all the tables affected. It may be that some of the billing system interface tables are dropped after the run—Flashback Drop will recover them. But if the run involves a truncation, the only option is Flashback Database. Also, it may be that the error was not discovered for some time and a significant amount of work has been done based on the erroneously imported data; then Flashback Database may be the only way to ensure that you end up with a database that is consistent in business terms.

When choosing a flashback technique, always remember that Oracle will guarantee transactional integrity but that the results in business terms may not be what you want. Flashback Database, or indeed incomplete recovery, is the only way to guarantee absolutely the integrity of the database and conformity with your business rules—but the price in lost time and data may be very high.

Use Flashback to Query Data

The basic form of Flashback Query lets you query the database as it was at some time in the past. The principle is that your query specifies a time, which is mapped onto a system change number (SCN), and whenever the query hits a block that has been changed since that SCN, it will go to the undo segments to extract the undo data needed to roll back the change. This rollback is strictly temporary and is visible only to the session running the Flashback Query. Clearly, for a Flashback Query to succeed, the undo data must be available. More sophisticated techniques can be used to retrieve all versions of a row, to reverse individual transactions, or to reverse all the changes made to a table since a certain time. It is also possible to guarantee that a flashback will succeed—but there is a price to be paid for enabling this: It may cause transactions to fail.

Basic Flashback Query

Any SELECT statement can be directed against a previous version of a table. Consider this example:

First, note the time. Then delete some rows from a table and commit the change. A query confirms that there are only two rows in the table and no rows where the REGION_NAME begins with A. The next query is directed against the table as it was at the earlier time (back when there were four rows, including those for Asia and Americas). Make no mistake about this—the two rows beginning with A are gone; they were deleted, and the delete was committed. This cannot be rolled back. The deleted rows you are seeing have been constructed from undo data. The final query combines real-time data with historical data to see what rows have been removed. The output of this query could be used for repair purposes to insert the rows back into the table.

Although being able to direct one query against data as of an earlier point in time may be useful, there will be times when you want to make a series of selects. It is possible to take your whole session back in time by using the DBMS_FLASHBACK package.

From this point on, all queries will see the database as it was at the time specified. All other sessions will see real-time data, but this one session will see a frozen version of the database until the flashback is cancelled.

While in flashback mode, it is impossible to execute DML commands. They will throw an error. Only SELECT statements are possible.

How far back you can take a Flashback Query (either one query or by using DBMS_FLASHBACK) depends on the contents of the undo segments. If the undo data needed to construct the out-of-date result set is not available, then the query will fail with an ORA-08180, “No snapshot found based on specified time,” error.

The syntax for enabling Flashback Query will accept either a timestamp or an SCN. If you use an SCN, then the point to which the flashback goes is precise. If you specify a time, it will be mapped onto an SCN with a precision of three seconds.

Flashback Table Query

Conceptually, a table flashback is simple. Oracle will query the undo segments to extract details of all rows that have been changed and then construct and execute statements that will reverse the changes. The flashback operation is a separate transaction, which will counteract the effect of all the previous transactions—if possible. The database remains online and normal work is not affected, unless row locking is an issue. This is not a rollback of committed work; it is a new transaction designed to reverse the effects of committed work. All indexes are maintained, and constraints are enforced; a table flashback is just another transaction, and the usual rules apply. The only exception to normal processing is that, by default, triggers on the table are disabled for the flashback operation.

A table flashback will often involve a table that is in a foreign key relationship. In that case, it is almost inevitable that the flashback operation will fail with a constraint violation. To avoid this problem, the syntax permits flashback of multiple tables with one command, which will be executed as a single transaction with the constraint checked at the end.

The first step to enabling table flashback is to enable row movement on the tables. This is a flag set in the data dictionary that informs Oracle that row IDs may change. A row ID can never actually change, but a flashback operation may make it appear as though it has. For instance, in the case of a row that is deleted, the flashback operation will insert it back into the table. It will have the same primary key value but a different row ID.

In the example that follows, there are two tables: EMP and DEPT. There is a foreign key relationship between them, stating that every employee in EMP must be a member of a department in DEPT.

First, insert a new department and an employee in that department and note the time.

Next, delete the department and the employee, taking care to delete the employee first to avoid a constraint violation.

Now attempt to flash back the tables to the time when the department and employee existed.

This fails because, by default, row movement, which is a prerequisite for table flashback, is not enabled for any table, so enable it for both tables.

Now try the flashback again.

This time the flashback fails for a more subtle reason. The flashback is attempting to reverse the deletion of employee 8000 by inserting him, but employee 8000 was in department 50, which has been deleted and so does not exist. So, there is a foreign key violation. You could avoid this problem by flashing back the DEPT table first, which would insert department 50. But if your flashback involves many tables and many DML statements, it may be logically difficult to find a sequence that will work. The answer is to flash back both tables together.

This succeeds because both the tables are flashed back in one transaction, and the constraints are checked only at the end of that transaction by which time the data is logically consistent.

The flashback could still fail for other reasons:

•  Primary key violations will occur if a key value has been reused between a delete and the flashback.

•  An ORA-08180, “No snapshot found based on specified time,” error will be raised if there is not enough undo information to go back to the time requested.

•  If any rows affected by the flashback are locked by other users, the flashback will fail with ORA-00054: “Resource busy and acquire with NOWAIT specified.”

•  The table definitions must not change during the period concerned; flashback cannot go across Data Definition Language (DDL) statements. Attempting to do this will generate ORA-01466: “Unable to read data—table definition has changed.”

•  Flashback does not work for tables in the SYS schema. Try to imagine the effect of flashing back part of the data dictionary.

If a table flashback fails for any reason, the flashback operation will be cancelled. Any parts of it that did succeed will be rolled back, and the tables will be as they were before the flashback command was issued.

Variations in the syntax allow flashback to a system change number and the firing of DML triggers during the operation.

Flashback Versions Query

A row may have changed several times during its life. Flashback Versions Query lets you see all the committed versions of a row (but not any uncommitted versions), including the timestamps for when each version was created and when it ended. You can also see the transaction identifier of the transaction that created any given version of a row, which can then be used with Flashback Transaction Query. This information is exposed by a number of pseudocolumns that are available with every table. Pseudocolumns are columns appended to the row by Oracle internally; they are not part of the International Organization for Standardization (ISO) standards for a relational database, but they can be useful. One pseudocolumn is the row ID. This is the unique identifier for every row in the database that is used in indexes as the pointer back to the table. The pseudocolumns relevant to flashback are as follows:

•  VERSIONS_STARTSCN    The SCN at which this version of the row was created, either by INSERT or by UPDATE

•  VERSIONS_STARTTIME    The timestamp at which this version of the row was created

•  VERSIONS_ENDSCN    The SCN at which this version of the row expired, because of either DELETE or UPDATE

•  VERSIONS_ENDTIME    The timestamp at which this version of the row expired

•  VERSIONS_XID    The unique identifier for the transaction that created this version of the row

•  VERSIONS_OPERATIONS    The operation done by the transaction to create this version of the row, either INSERT or UPDATE or DELETE

To see these pseudocolumns, you must include the VERSIONS BETWEEN keywords in your query. For example, Figure 26-1 shows all versions of the row for employee 8000.

Figure 26-1    Flashback Versions Query

The versions are sorted in descending order of existence; they must be read from the bottom up. The bottom row shows that employee 8000 was inserted (the I in the last column) at SCN 95828152 by transaction number 01000E0002180000. The employee was given the ENAME of RAMKLASS and the SAL of 3000. This version of the row existed until SCN 95828273, which takes you to the third row. At this SCN, the row was updated (the U in the last column) with a new salary. This version of the row persisted until SCN 95828279, when it was deleted, as shown in the second row. The VERSIONS_ENDSCN column is always null for a deletion. The top row of the result set shows a new insertion, which reuses the employee number. For this row, the VERSIONS_ENDSCN is also null because the row still exists in that version at the end of the time range specified in the query.

In the example in Figure 26-1, the VERSIONS BETWEEN clause uses two constants for the SCN. MINVALUE instructs Oracle to retrieve the earliest information in the undo segments; MAXVALUE will be the current SCN. In other words, the query as written will show all versions that can possibly be retrieved, given the information available. The syntax will also accept a range specified with two timestamps.

The preceding example will select all versions of employee number 8000 that existed during the last hour.

Flashback Transaction Query

Flashback Table Query and Flashback Versions Query use undo data for an object. Flashback Transaction Query analyzes the undo by a different dimension. It will retrieve all the undo data for a transaction, no matter how many objects it affects. The critical view is FLASHBACK_TRANSACTION_QUERY, described here:

Because the data in this view may be sensitive, it is protected by a privilege. You must be granted SELECT ANY TRANSACTION before you can query it. By default, this privilege is granted to SYS and to the DBA role. There will be one or more rows in this view for every transaction whose undo data still exists in the undo segments, and every row will refer to one row affected by the transaction. Table 26-1 describes the columns.

Table 26-1    The Columns of the FLASHBACK_TRANSACTION_QUERY View

A one-line SQL statement might generate many rows in FLASHBACK_TRANSACTION_QUERY. This is because SQL is a set-oriented language. One statement can affect many rows. But each row affected will have its own row in the view. The view will show committed transactions and also transactions in progress. For an active transaction, the COMMIT_SCN and COMMIT_TIMESTAMP columns are NULL. Rolled-back transactions are not displayed.

Take an example where a salary was multiplied by 11 rather than being incremented by 10 percent.

Later, it is suspected that a mistake was made. So, you query the versions of the row.

This does indicate what happened and gives enough information to reverse the change. But what if the transaction affected other rows in other tables? To be certain, query FLASHBACK_TRANSACTION_QUERY, which will have one row for every row affected by the transaction. A minor complication is that the XID column is type RAW, whereas the VERSIONS_XID pseudocolumn is hexadecimal, so you must use a type casting function to make the join.

This query returns only one row, which confirms that there was indeed only one row affected by the transaction and provides a statement that will reverse the impact of the change. Note the use of a ROWID in the UNDO_SQL statement. Provided that there has been no reorganization of the table, this will guarantee that the correct row is changed.

The view FLASHBACK_TRANSACTION_QUERY will construct undo statements to reverse a transaction, but executing them individually would be an awful task for a large transaction. This is where the DBMS_FLASHBACK package is again useful. It includes procedures to back out transactions. To execute the transaction backout procedures, you must have been granted the FLASHBACK ANY TABLE privilege.

Consider this example:

This procedure call will reverse all the work done by the two nominated transactions. Here are the arguments in order:

•  NUMTXNS is the number of transactions that should be reversed; in this example, two.

•  XIDS is a list of transaction identifiers, passed as an XID_ARRAY variable. This list would have been identified with a Flashback Query.

•  OPTIONS can take various values in the form of package constants. The CASCADE option will attempt to order the changes to avoid conflicts.

It is impossible to roll back a committed transaction. The rules of a relational database forbid this. So when the backout procedure reverses one or more transactions, it must construct and attempt to execute more DML in another transaction, which will reverse the effect of the original transactions. This process is fraught with difficulty because of the possibility of dependencies between the transactions and conflicts with work done subsequently. This will typically show up as constraint violations. The OPTIONS argument controls what to do if there is a problem. These are the possible values:

•  NOCASCADE (the default) will apply undo changes with no attempt to identify dependencies. This may well fail if, for instance, the transactions listed affect tables in foreign key relationships.

•  CASCADE attempts to undo the transactions logically such that constraint violations will not occur.

•  NONCONFLICT_ONLY backs out only changes to rows that do not cause problems. The database will remain consistent, but some transactions may be incomplete.

•  NOCASCADE_FORCE will undo SQL statements in reverse order of commit times.

Whatever changes the DBMS_FLASHBACK.BACKOUT_TRANSACTION manages to accomplish are left uncommitted. This gives you an opportunity to investigate what it managed to achieve before committing (or rolling back).

Figure 26-2 shows an example of combining Flashback Query with Flashback Transaction. The first query shows a row, which is then updated and committed. A Flashback Version Query retrieves the identifier of the transaction that made the change, and passing this to DBMS_FLASHBACK.BACKOUT_TRANSACTION reverses the change. Finally, the reversal must be committed.

Figure 26-2    Using the Flashback Transaction facility

Flashback and Undo Data

Flashback Query in its various forms relies entirely on undo data. You are asking Oracle to present you a version of the data as it was some time ago. If the data has been changed since that time, Oracle must roll back the changes. To do this, Oracle needs the undo data that protected the change. Whether the query will succeed will depend on whether that undo data is still available. Consider Figure 26-3.

Figure 26-3    Flashback Query and undo data

The first query asks for a view of the table as it was 40 minutes ago, and it succeeds. This is because there is at least 40 minutes of undo data available in the undo segments. The second query attempts to go back 40 days, and it fails. In virtually all databases, it would be completely unrealistic to expect Flashback Query to work over such a long period. You would need an undo tablespace the size of Jupiter to store that much undo data.

To guarantee that a Flashback Query will always succeed for a given period, set the RETENTION GUARANTEE attribute for the undo tablespace in conjunction with the UNDO_RETENTION instance parameter. This will ensure that you can always flash back the number of seconds specified—but the price you will pay is that if your undo tablespace is not sized adequately for the transaction workload, then the database may hang while performing DML.

Perform Flashback Table Drop Operations

Accidentally dropping a table is easy to do. It is not just that you can drop the wrong table because of a typing error—it could be the right table, but you are connected to the wrong schema or logged onto the wrong instance. You can reduce the likelihood of this by setting your SQL*Plus prompt. Here’s an example:

Flashback Drop lets you reinstate a previously dropped table (but not a truncated table) exactly as it was before the drop. All the indexes will also be recovered and also any triggers and grants. Unique, primary key, and not-null constraints will also be recovered—but not foreign key constraints.

The Implementation of Flashback Drop

Up to and including release 9i of Oracle Database, when a table was dropped, all references to it were removed from the data dictionary. If it were possible to see the source code for the old DROP TABLE command, you would see that it was actually a series of DELETE commands against the various tables in the SYS schema that define a table and its space usage, followed by a COMMIT. There was no actual clearing of data from disk, but the space used by a dropped table was flagged as being unused and thus available for reuse. Even though the blocks of the table were still there, there was no possible way of getting to them because the data dictionary would have no record of which blocks were part of the dropped table. The only way to recover a dropped table was to do a point-in-time recovery, restoring a version of the database from before the drop when the data dictionary still knew about the table.

From release 10g onward of the Oracle database, the implementation of the DROP TABLE command has changed. Tables are no longer dropped at all; they are renamed.

In Figure 26-4, you can see that a table, OLD_NAME, occupies one extent of 64KB, which starts 38,281 blocks into file 4. After the rename to NEW_NAME, the storage is the same; therefore, the table is the same. Querying the view DBA_OBJECTS would show that the table’s object number had not changed either.

Figure 26-4    Renaming tables with SQL*Plus

The DROP TABLE command has been mapped internally onto a RENAME command, which affects the table and all its associated indexes, triggers, and constraints, with the exception of foreign key constraints, which are dropped. Foreign key constraints have to be physically dropped. If they were maintained, even with a different name, then DML on the nondropped parent table would be constrained by the contents of a dropped table, which would be absurd.

Grants on tables do not have names, so they can’t be renamed. When you grant an object privilege, you specify the object by name, and the underlying storage of the grant references the object by its object number. Because the object numbers don’t get changed by a RENAME operation, the grants remain valid.

As far as normal SELECT and DML statements are concerned, a dropped table is definitely dropped. There is no change to any other commands, and all your software will assume that a dropped table really is gone. But now that DROP is in fact RENAME, it becomes possible to undrop—by renaming the table back to its original name. However, this is not guaranteed to succeed. It may be that the space occupied by the dropped table has been reused. There are also complications if in the interim period another table has been created, reusing the same name as the dropped table.

The dropped objects can be queried by looking at the “recycle bin” to obtain their new names. This is a listing of all objects that have been dropped, mapping the original table and index names onto the system-generated names of the dropped objects. There is a recycle bin for each user, visible in the USER_RECYCLEBIN data dictionary view, or for a global picture you can query DBA_RECYCLEBIN. The space occupied by the recycle bin objects will be reused automatically when a tablespace comes under space pressure (after which time the objects cannot be recovered), or you can manually force Oracle to really drop the objects with the PURGE command.

Using Flashback Drop

Consider the example in Figure 26-5. This is the most basic use of Flashback Drop. The DROP command renames the table to a system-generated name, and Flashback Drop brings it back.

Figure 26-5    Using Flashback Drop

Variations in syntax are as follows:

The first command really will drop the table. The PURGE keyword instructs Oracle to revert to the original meaning of DROP: all references to the table are deleted, and it can never be brought back. The second command will flash back the table but give it a new name. This would be essential if between the drop and the flashback another table had been created with the same name as the dropped table. Note that although a table can be renamed during a flashback, it cannot change schemas; all flashback operations occur within the schema to which the object belongs. The indexes, triggers, and constraints that are flashed back along with the table keep their recycle bin names. If you want to return them to their original names, you must rename them manually after the flashback.

There are two points to emphasize here. First, Flashback Drop can recover only from a DROP. It cannot recover from a TRUNCATE. Second, if you drop a user with, CASCADE as shown here, you will not be able to recover any of SCOTT’s tables with a flashback:

The drop of the schema means that Oracle cannot maintain the objects at all, even in the recycle bin, because there is no schema to contain them.

The SQL*Plus command SHOW RECYCLEBIN will display the dropped objects, with their original names and their recycle bin names. The view DBA_RECYCLEBIN provides the same information, and more.

If a table is dropped and then another table is created with the same name and then also dropped, there will be two tables in the recycle bin. They will have different recycle bin names but the same original name. By default, a Flashback Drop command will always recover the most recent version of the table, but if this is not the version you want, you can specify the recycle bin name of the version you want recovered, rather than the original name. Here’s an example:

Exercise 26-1: Use Flashback Drop with SQL*Plus    Create a new schema and a table within it. Drop the table and then recover it with Flashback Drop.

1.  Connect to your database as user SYSTEM with SQL*Plus.

2.  Create a user for this exercise.

3.  Create a table, with an index and a constraint, and insert a row.

4.  Confirm the contents of your schema.

5.  Drop the table.

6.  Rerun the queries from step 4. Note that the objects have been removed from USER_OBJECTS, but the constraint does still exist with a system-generated name.

7.  Query your recycle bin to see the mapping of the original name to the recycle bin names.

Note that this view does not show the constraint.

8.  Demonstrate that it is possible to query the recycle bin but that you cannot do DML against it, as the next illustration shows. Note that the table name must be enclosed in double quotes to allow SQL*Plus to parse the nonstandard characters correctly.

9.  Recover the table with Flashback Drop.

10.  Rerun the queries from steps 4 and 7. Note that the index and the constraint have retained their recycle bin names.

11.  Rename the index and constraint to the original names. In the examples that follow, substitute your own recycle bin names:

12.  Confirm the success of the operation by rerunning the queries from steps 4 and 7.

13.  Connect as user SYSTEM and drop the DROPPER schema.

14.  Query the DBA_RECYCLEBIN view to demonstrate that all the objects owned by user DROPPER really are gone.

Managing the Recycle Bin

The recycle bin is a term given to the storage space used by dropped objects. You can ignore the recycle bin completely; its management is automatic, both in terms of transferring objects into it when they are dropped and removing them permanently when the space is needed in the tablespace for live objects. But there may be circumstances when you will need to be aware of the contents of the recycle bin and how much space they are taking up.

The recycle bin can be disabled with the instance parameter RECYCLEBIN. This defaults to ON, meaning that all schemas will have a recycle bin. The parameter is dynamic and can be set to OFF for a session or for the entire system.

Querying the Recycle Bin

Each user has their own recycle bin and can always view dropped tables in their own schema. The simplest way is the SHOW RECYCLEBIN command.

This shows that the current user has three dropped tables: their original names, their recycle bin names, and the time they were dropped. For more detailed information, query the data dictionary view USER_RECYCLEBIN or query DBA_RECYCLEBIN for a global view.

The critical column is CAN_UNDROP. Oracle is under no obligation to keep dropped tables or indexes. The Flashback Drop facility is purely a convenience that Oracle provides; it is not part of the relational database standard. If Oracle needs the space being occupied by a dropped object to allocate more space to a live object, it will take it. From that point, the dropped object can no longer be recovered with Flashback Drop, and it will be removed from the view. The SPACE column (in units of datafile blocks) shows how much space is taken up by the dropped object.

Having identified the dropped table’s name in the recycle bin, it can be queried like any other table, though you will have to enclose its name in double quotes because of the nonstandard characters used in recycle bin names. But always remember that you have a limited (and unpredictable) time during which you can do this. If you think it is likely that a dropped table will be needed, you should undrop it immediately.

Reclaiming Space from the Recycle Bin

Space taken up by dropped objects is in an ambiguous state. It is assigned to the object, but Oracle can overwrite it at will. The normal diagnostics regarding space usage will ignore space occupied by the recycle bin. This means that your “tablespace percent full” alerts will not fire until the warning and critical space usage levels are reached by live objects. Furthermore, if your datafiles have the AUTOEXTEND attribute enabled, Oracle will not in fact autoextend the datafiles until all space occupied by dropped objects has been reassigned. It will overwrite the recycle bin in preference to increasing the datafile size.

Perform Table Recovery from Backups

Recovery Manager (RMAN) table recovery is not a flashback technology, but the result is similar: a table is returned to the state it was in at some time in the past. It is therefore dealt with at this point. Using an RMAN backup to recover a table bypasses the issues of table flashback, in that the time frame is not constrained by the availability of undo data or whether the table’s space has been reused but rather by the availability of backups of datafile and archive logfiles. This may mean that you can recover a table to the state it was in weeks or months ago.

This is the process:

1.  Create an auxiliary instance. This is an instance started off a default parameter file with a system-generated name.

2.  Restore the SYSTEM, SYSAUX, and UNDO tablespaces to the auxiliary instance from a sufficiently old backup and recover to the desired time.

3.  Restore the datafiles of the tablespace containing the table and recover to the desired time.

4.  Use Data Pump to export the table from the auxiliary instance and import it into the target instance.

5.  Drop the auxiliary instance and database.

This technique has always been possible, but in the current release RMAN can automate the entire process.

Exercise 26-2: Recover a Table from a Backup    In this exercise, you will create a tablespace and a table, backup the tablespace, drop the table, and recover it from the backup. Note that if working on Windows, you may need to run RMAN with elevated privileges in order to create the auxiliary instance. It is necessary for the database to be in archivelog mode, and a full backup must already exist.

1.  Create a tablespace and a table.

2.  Back up the tablespace with RMAN.

3.  Note the current system change number and drop the table.

4.  Recover the table with RMAN.

The RECOVER TABLE command lets you specify a location for the auxiliary database. Choose any suitable directory; in this case (a Windows example), it is c: mp. Substitute the SCN for that in step 3.

5.  Observe the recovery.

Study the output of the recovery command. Do not skip this; it is extremely instructive. When you can understand each line, you are well on the way to passing the final Oracle Certified Professional (OCP) examination.

6.  Confirm the success of the operation and tidy up.

Describe and Use Flashback Data Archive

The flashback technologies discussed so far can be useful, but they all have limited flashback capability. A Flashback Data Archive can be configured to guarantee the ability to flash back a table to any time—perhaps to a time years ago. It can also guarantee that data is removed when it has expired.

Architecturally, Flashback Data Archive requires one or more tablespaces, various segments for each protected table, and a new background process: the FBDA process. The DBA must create the tablespaces and the archives within them, specifying a retention period for each archive, and then nominate tables to be protected by an archive. The necessary segments will be created automatically, and the FBDA will start when required. Users and application software will not be aware of any change so far as DML is concerned. Some DDL commands will be affected. For example, TRUNCATE will be slow, and a DROP is not possible. Flashback Query commands (such as a SELECT with the AS OF clause) will execute successfully against versions of the table within the time frame specified for the archive protecting the table.

To create a Flashback Data Archive, first create a tablespace. It is technically possible to create the archives in a preexisting tablespace, but it makes sense to separate them from regular data. Then create the archive, specifying the tablespace, a retention time, and (optionally) a quota. Here’s an example:

This command includes the DEFAULT keyword, which means that it will be used as the archive for all tables unless specified otherwise. The default archive can be also be set later.

The QUOTA clause limits the space the archive can occupy in the tablespace. If an archive fills, more space can be added either in the original tablespace or in another tablespace. For example, this command will extend the archive into another tablespace:

It is also possible to adjust the retention.

Data is removed from an archive automatically by the FBDA background process once it is older than the nominated retention period, but it can be removed manually before the period has expired. Here’s an example:

Because there will often be legal implications to the ability to manage an archive, it is protected by privileges. The FLASHBACK ARCHIVE ADMINISTER system privilege grants the ability to create, alter, or drop an archive, as well as control the retention and purging. You must grant FLASHBACK ARCHIVE on the archive to a user who will nominate tables to be archived.

Finally, to enable archive protection for a table, use this command:

There are three data dictionary views that document the Flashback Data Archive configuration:

•  DBA_FLASHBACK_ARCHIVE    Describes the configured archives

•  DBA_FLASHBACK_ARCHIVE_TS    Shows the quotas assigned per archive per tablespace

•  DBA_FLASHBACK_ARCHIVE_TABLES    Lists the tables for which archiving is enabled

Exercise 26-3: Create a Flashback Data Archive    In this exercise, you will investigate the structures of a Flashback Data Archive.

1.  Create a tablespace to be used for the Flashback Data archive.

2.  Create a Flashback Data Archive in the tablespace, with a retention of seven years.

3.  Create a schema to use for this exercise and grant it the DBA role.

4.  Grant the user the necessary privilege on the archive.

5.  Connect as the FBDAUSER. Create a table and enable the Flashback Data Archive for this table.

6.  Run these queries to determine the objects created by the archive. You may have to wait several minutes because the objects are not created immediately.

7.  Perform some DML against the protected table.

8.  Perform a Flashback Query against the protected table using standard Flashback Query syntax. Here’s an example:

9.  Attempt some DDLs that would affect the protected table:

Note that these commands all generate errors related to the existence of the archive and the protected table.

10.  Remove the archive protection from the table.

11.  Drop the Flashback Data Archive.

12.  Rerun all the commands from step 9.

Perform Flashback Database

Flashing back an entire database is functionally equivalent to an incomplete recovery, but the method and the enabling technology are completely different.

Flashback Database Architecture

Once Flashback Database is enabled, images of altered blocks are copied from time to time from the database buffer cache to a memory area within the System Global Area (SGA), the flashback buffer. This flashback buffer is flushed to disk, to the flashback logs, by a new background process: the Recovery Writer (RVWR). There is no change to the usual routine of writing changes to the log buffer, which the LGWR then flushes to disk; flashback logging is additional to this. Unlike the redo log, flashback logging is not a log of changes—it is a log of complete block images.

Critical to performance is that not every change is copied to the flashback buffer—only a subset of changes. If all changes to all blocks were copied to the buffer, then the overhead in terms of memory usage and the amount of extra disk input/output (I/O) required to flush the buffer to disk would be crippling for performance. Internal algorithms limit which versions of which blocks are placed in the flashback buffer in order to restrict its size and the frequency with which it will fill and be written to disk. These algorithms are intended to ensure that there will be no negative performance hit when enabling Flashback Database; they guarantee that even very busy blocks are logged only infrequently.

When conducting a database flashback, Oracle will read the flashback logs to extract the versions of each changed database block and copy these versions back into the datafiles. As these changes are applied to the current database in reverse chronological order, this has the effect of taking the database back in time by reversing the writes that the DBWn process has done. Since not every version of every changed block is copied into the flashback buffer and hence to the flashback logs, it is not possible to flash back to an exact point in time. It may be that a block was changed many times but that the flashback log has only a subset of these changes. Consider the case where block A was changed at 10:00 and again at 10:05 but that only the 10:00 version is in the flashback log. Block B was changed at 10:05 and at 10:20, and both versions are in the flashback log. All the changes have been committed. It is now 11:00, and you want to flash back to 10:15. The flashback operation will restore the 10:00 version of block A and the 10:05 version of block B; it will take each changed block back as close as it can to, but no later than, the desired time. Thus, Flashback Database constructs a version of the datafiles that is just before the time you want. This version of the datafiles may well be totally inconsistent. As in this example, different blocks will be at different system change numbers, depending on what happened to be available in the flashback log. To complete the flashback process, Oracle then uses the redo log. It will recover all the blocks to the exact time requested (in the example, only block A needs recovery), thus synchronizing all the datafiles to the same SCN. The final stage is to roll back any transactions that were uncommitted at the point, exactly as occurs at the last stage of an incomplete recovery.

So, Flashback Database is, in fact, a combination of several processes and data structures. First, you must allocate some memory in the SGA (which will be automatic—you cannot control how large the buffer is) and some space on disk to store the flashback data and start the RVWR process to enable flashback logging. When doing a flashback, Oracle will use the flashback logs to take the database back in time to before the time you want and then apply redo logs (using whatever archive redo log files and online redo log files are necessary) in the usual fashion for incomplete recovery to bring the datafiles forward to the exact time you want. Then the database can be opened with a new incarnation, in the same manner as following a normal incomplete recovery.

Flashback Database requires archive log mode because without the availability of the archive log stream, it would not be possible to convert the inconsistent version of the database produced by the application of flashback logs to a consistent version that can be opened. So, what is the benefit of Flashback Database over incomplete recovery, which also requires archive log mode? It is in the speed and convenience with which you can take the database back in time.

An incomplete recovery is always time consuming because part of the process is a full restore. The time for an incomplete recovery is, to a large extent, proportional to the size of the database. By contrast, the time needed for a database flashback is largely proportional to the number of changes that need to be backed out. In any normal environment, the volume of changed data will be tiny when compared to the total volume of data, so a flashback should be many times faster. Furthermore, Flashback Database is easy to use. Once configured, flashback logging will proceed completely unattended, and a database can be flashed back easily with one command. There are none of the possibilities for error inherent in a traditional restore and recover operation.

Configuring Flashback Database

Configuring a database to enable Flashback Database will require downtime if the database is not already in archive log mode. Otherwise, it is an online operation. To configure Flashback Database, follow these steps:

1.  Ensure that the database is in archive log mode.

Archive log mode is a prerequisite for enabling Flashback Database. Confirm this by querying the V$DATABASE view.

2.  Set up a Fast Recovery Area.

The Fast Recovery Area is the location for the flashback logs. You have no control over them other than setting the Fast Recovery Area directory and limiting its size. It is controlled with two instance parameters: DB_RECOVERY_FILE_DEST specifies the destination directory, and DB_RECOVERY_FILE_DEST_SIZE restricts the maximum amount of space in bytes that it can take up. Remember that the Fast Recovery Area is used for purposes other than just flashback logs, and it will need to be sized appropriately. Here’s an example:

3.  Set the flashback retention target.

This is controlled by the DB_FLASHBACK_RETENTION_TARGET instance parameter, which is in minutes, and the default is one day. The flashback log space is reused in a circular fashion, with older data being overwritten by newer data. This parameter instructs Oracle to keep flashback data for a certain number of minutes before overwriting it:

It is only a target (four hours in the preceding example), and if the Fast Recovery Area is undersized, Oracle may not be able to keep to it. But in principle, you should be able to flash back to any time within this target.

4.  Enable flashback logging.

This will start the RVWR process and allocate a flashback buffer in the SGA. The process startup will be automatic from now on.

5.  Open the database.

Logging of data block images from the database buffer cache to the flashback buffer will be enabled from now on.

Monitoring Flashback Database

The flashback retention target is only a target—there is no guarantee that you could actually flash back to a time within it. Conversely, you might be able to flash back to beyond the target. The possible flashback period is a function of how much flashback logging information is being generated per second and how much space is available to store this information before overwriting it with more recent data.

The most basic level of flashback monitoring is to confirm that it is actually enabled.

On Unix you can see the RVWR process as an operating system process; on Windows it will be another thread within ORACLE.EXE.

To monitor the current flashback capability and estimate the space needed for flashback logs to meet your target, query the V$FLASHBACK_DATABASE_LOG view. V$FLASHBACK_DATABASE_STAT gives a historical view of the rate of disk I/O for the datafiles, the online redo log files, and the flashback log files.

In Figure 26-6, the first query shows the setting for the retention target in minutes, as specified by the DB_FLASHBACK_RETENTION_TARGET instance parameter; this is on the default of one day. Then there is the actual space being taken up by the flashback log files, as well as the exact time to which the flashback logs could take the database back. If the Fast Recovery Area is sized appropriately and the retention target is realistic, then there will be a sensible relationship between the time shown in this query and the current time less the retention target.

Figure 26-6    Monitoring Flashback Database

The second query shows the price you are paying for enabling Flashback Database in terms of the bytes of I/O that it necessitates per hour. The top row will always be an incomplete hour, up to the current time. In the example, the database was generating about 10MB of flashback data per hour through the night, with higher rates in the late evening and morning. The impact of this on performance will need to be discussed with your system administrators, bearing in mind whether the system is I/O bound or not. For comparison, the view also shows the I/O related to normal database activity. The view will have one row per hour.

The size of the flashback buffer is outside the DBA’s control, but to see the current size, you can query the V$SGASTAT view.

Using Flashback Database

Flashback Database can be used from SQL*Plus or RMAN. Whichever tool you choose to use, the method is the same.

1.  Shut down the database.

2.  Mount the database.

3.  Flash back to a time, an SCN, or a log sequence number.

4.  Open the database with RESETLOGS.

Provided that all archive logs required are available, a flashback operation will proceed completely automatically.

Flashback with SQL*Plus

The SQL*Plus flashback syntax will accept either a timestamp or a system change number argument. If you are not sure exactly what time you need to go back to (you will be fortunate if you know the exact timestamp or SCN), you can have several attempts by combining flashback with recovery.

Consider this scenario: It is December 20, 2014. At about 10:00 A.M. a junior DBA drops an important schema on the production database. This is terrifyingly easy to do; perhaps it happened during an upgrade of software on a development system when connected to the production database by mistake. The error is noticed within 10 minutes, but it is a big, busy database in a call center, used for taking orders, and every second lost will cost money. The first step is to shut down the database.

There is no point in using any other type of shutdown; all work in progress is going to be lost anyway, and you need to minimize the downtime. Then take the database back to 10:00 as follows:

Note that unlike RECOVER DATABASE UNTIL TIME, this command is sensitive to NLS settings for the timestamp format. While in READ ONLY mode, you can run a query against the dropped schema. If you discover that the schema is still there, perhaps you can recover a bit more user data.

You run your test query again, and you discover that after recovering 2 more minutes of data, the schema is gone. It must have been dropped between 10:00 and 10:02. So, you split the difference.

If the schema is not there now, flash back a few seconds earlier. If it is there, do a few seconds of recovery. You can repeatedly issue flashback and recover commands until you find the time that you want, testing by running queries while in READ ONLY mode. When you get to a point you are satisfied with, do one final shutdown and open with RESETLOGS to create a new incarnation of the database that can be opened for normal use.

This method will minimize the loss of data, and it may take only a few minutes. An incomplete recovery might take hours, particularly if you need to have several tries before you get to the right time.

Flashback with RMAN

Within the Recovery Manager environment you have three options: you can flash back to a time, to an SCN, or to a log sequence number, as in these examples:

Apart from the minor changes in syntax, RMAN flashback is the same as SQL*Plus flashback. In particular, you can use the same technique of repeatedly applying flashback and recovery until you find the optimal point to open the database.

Exercise 26-4: Use Flashback Database    Enable database flashback. Simulate a user error and flash back the database to before the transaction.

1.  Connect to the database as user SYSTEM and enable database flashback.

2.  Note the time and simulate an error.

3.  Attempt recovery using various techniques. Substitute the time from step 2.

4.  Why are these techniques not applicable?

5.  Recover the table using database flashback.

Two-Minute Drill

Describe the Flashback Technologies

•  Flashback is for correcting user (or DBA) error.

•  Physical damage requires use of restore and recover.

Use Flashback to Query Data

•  Flashback Query relies on the use of undo data.

•  Basic Flashback Query uses a modified SELECT statement or DBMS_FLASHBACK.

•  It is also possible to flash back a transaction or all changes to a table or set of tables.

Perform Flashback Table Operations

•  Flashback Drop relies on the fact that, by default, objects are renamed when dropped.

•  Control the availability to Flashback Drop with the RECYCLEBIN instance parameter and override the default with the PURGE keyword.

•  All dependent objects are recovered with the exception of foreign key constraints.

Perform Table Recovery from Backups

•  RMAN can extract a table from a backupset.

•  Data Pump is used to transfer the table from an auxiliary database into the target database.

Describe and Use Flashback Data Archive

•  Flashback Data Archive is not available by default. It must be explicitly configured and enabled for nominated tables.

•  An archive retains data for a defined period, permanently removing it once that period has passed.

•  Query the archive with the usual Flashback Query syntax.

Perform Flashback Database

•  Flashback Database is not enabled by default. Once enabled, it relies on a memory structure (the flashback buffer), a disk structure (the flashback logs), and a background process (the RVWR).

•  Flashback Database is functionally equivalent to an incomplete recovery.

Self Test

1.  Which of the following flashback technologies use data from the current undo tablespace? (Choose all that apply.)

A.  Flashback Table

B.  Flashback Transaction Query

C.  Flashback Query

D.  Flashback Version Query

E.  Flashback Drop

F.  Flashback Database

G.  Flashback Data Archive

2.  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

3.  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? (Choose the best answer.)

A.  A timestamp

B.  An SCN

C.  A WHERE clause on any column in the table

D.  A guaranteed restore point

4.  Which of the following statements is true about the recycle bin? (Choose the best answer.)

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.

5.  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 by any 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.

6.  Which of the following columns is not in the data dictionary view FLASHBACK_TRANSACTION_QUERY? (Choose the best answer.)

A.  UNDO_SQL

B.  XID

C.  OPERATION

D.  ORA_ROWSCN

7.  What happens to the rows in FLASHBACK_TRANSACTION_QUERY when part of the transaction is no longer available in the undo tablespace? (Choose the best answer.)

A.  The user ID number replaces the username 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.

8.  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

9.  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 one week. What is the result of running this command? (Choose the best answer.)

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 restored 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.

10.  Which of the following conditions will prevent recovery of a table or table partition using table recovery from backups? (Choose all that apply.)

A.  The database is in read-only mode.

B.  COMPATIBLE is set to 12.1.

C.  You are recovering a table owned by SYS in the USERS tablespace.

D.  The table is partitioned, and the indexes are in a different partition.

E.  The database is in NOARCHIVELOG mode.

11.  Identify the true statement about Flashback Data Archive. (Choose the best answer.)

A.  You cannot specify more than one 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.

12.  Which of the following data dictionary views contains a list of the tables using a Flashback Data Archive? (Choose the best answer.)

A.  DBA_FLASHBACK_ARCHIVE_TABLES

B.  DBA_FLASHBACK_ARCHIVE

C.  DBA_FLASHBACK_ARCHIVE_TS

D.  DBA_FLASHBACK_DATA_ARCHIVE_TABLES

13.  Which of the following initialization parameters is not required to configure Flashback Database operations? (Choose the best answer.)

A.  DB_RECOVERY_FILE_DEST_SIZE

B.  UNDO_RETENTION

C.  DB_FLASHBACK_RETENTION_TARGET

D.  DB_RECOVERY_FILE_DEST

14.  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 Fast 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 controlfile.

E.  You must have flashback logging enabled to use guaranteed restore points.

Self Test Answers

1.      A, B, C, and D. All of these technologies rely on the undo data in the undo tablespace.
    E, F, and G are incorrect. Flashback Drop relies on the recycle bin, Flashback Database relies on flashback logs in the Fast Recovery Area, and Flashback Data Archive relies on history tables based on a permanent table in tablespaces designated for flashback data archives.

2.      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 incorrect. The parameters DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE are used to configure Flashback Database, not Flashback Query.

3.      D. Guaranteed restore points are used only in recovery scenarios such as Flashback Database.
    A, B, and C are incorrect. All three can be used. 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.

4.      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.

5.      A, B, and C. Table objects in the recycle bin can be undropped, and they can be undropped by the original owner or a user with DBA privileges.
    D and E are incorrect. D is incorrect because a table in the recycle bin may or may not have dependent objects in the recycle bin. E is incorrect because the undrop syntax does permit recovering a table even if an object exists with the same original name as an object in the recycle bin.

6.      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 incorrect. 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.

7.      B. The OPERATION column in FLASHBACK_TRANSACTION_QUERY contains UNKNOWN for data no longer in the UNDO tablespace.
    A, C, D, and E are incorrect. A is incorrect because the user ID replaces the username in the LOGON_USER column when the user no longer exists. C is incorrect because the object number replaces the table name in the TABLE_NAME column when the table no longer exists. D is incorrect because the OPERATION column contains UNKNOWN, not UNAVAILABLE, when the information is no longer available in the UNDO tablespace. E is incorrect because part of a transaction might still be available in the UNDO tablespace.

8.      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 Fast Recovery Area at least as far back as the earliest guaranteed restore point.
    B is incorrect. You cannot use a WHERE clause to specify the time in the past for the FLASHBACK TABLE operation.

9.      A. The table is recovered to its original state right after creation and without the index.
    B, C, and D are incorrect. B is incorrect because FLASHBACK TABLE does not leverage the recycle bin. C is incorrect because the table is recovered as of the SCN but not rolled forward. D is incorrect 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.

10.      A, C, and E. To successfully perform table recovery from backups (TRFB), the database must be in read-write mode, you cannot recover objects owned by SYS, regardless of which tablespace they reside in, and the database must be in ARCHIVELOG mode. In addition, the COMPATIBLE parameter must be set to 12.0 or higher, and you cannot recover objects to the SYSTEM or SYSAUX tablespace.
    B and D are incorrect. The COMPATIBLE parameter must be at least 12.0, and the table to be recovered need not be contained in a single tablespace or group of tablespaces as it would be required with traditional tablespace point in time recovery (TSPITR).

11.      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, C, and D are incorrect. A is incorrect because you can have several Flashback Data Archives. C is incorrect 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 incorrect because you can create Flashback Data Archives only in permanent, non-UNDO tablespaces.

12.      A. DBA_FLASHBACK_ARCHIVE_TABLES contains a list of tables currently using a Flashback Data Archive.
    B, C, and D are incorrect. B is incorrect because DBA_FLASHBACK_ARCHIVE contains a list of the archives but not the tables within. C is incorrect because DBA_FLASHBACK_ARCHIVE_TS contains the archive to tablespace mapping. D is incorrect because DBA_FLASHBACK_DATA_ARCHIVE_TABLES is not a valid data dictionary view.

13.      B. The initialization parameter UNDO_RETENTION is required for other flashback features but not for Flashback Database.
    A, C, and D are incorrect. The parameters DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST are required to define the location and size of the Fast Recovery Area, and DB_FLASHBACK_RETENTION_TARGET is needed to define a desired upper limit for the Flashback Database recovery window.

14.      A and D. A regular restore point does not require a Fast Recovery Area, and it can be aged out of the controlfile; a guaranteed restore point will never be aged out of the controlfile unless it is explicitly dropped.
    B, C, and E are incorrect. B is incorrect because a guaranteed restore point can be referenced for other flashback features, not just Flashback Database. C is incorrect because you can explicitly drop any type of restore point. E is incorrect because you can define guaranteed restore points without flashback logging enabled; however, you must still have a Fast Recovery Area enabled.