Globusz^® Publishing 

Using the Flashback Technology

You can use the flashback technology at the database, table, and transaction levels:

• Flashback database enables you to take the entire database to a past point in time (using flashback logs).
• Flashback drop lets you retrieve accidentally dropped tables and indexes (using the recycle bin).
• Flashback table lets you recover a table to a time in the past (using undo data).
• Flashback query lets you query and restore data rows to a point in time (using undo data).

General Flashback Technology

Guaranteed Undo Retention

The initialization parameter UNDO_RETENTION enables you to specify the length of time Oracle must retain undo information in the undo segments.

By default, Oracle doesn’t guarantee undo retention.

Methods to specify Undo Guarantee:

• By using the RETENTION GUARANTEE clause when you create an undo tablespace:

CREATE UNDO TABLESPACE test1

DATAFILE 'c:\oracle\oradata\undotbs_01.dbf'

SIZE 100M AUTOEXTEND ON

RETENTION GUARANTEE

• You can also use the ALTER TABLESPACE command:

ALTER TABLESPACE test1 RETENTION GUARANTEE

• You can specify undo guarantee for the undo tablespace when you create the database.

Note: You can use the RETENTION NOGUARANTEE clause to turn off the guaranteed retention of undo information.

Note: The amount of time for which undo is retained for the Oracle Database for the current undo tablespace can be obtained by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT dynamic performance view.

Note: Use Oracle’s Undo Advisor to get approximate undo parameter values as well as suggestions regarding the sizing of your undo tablespace to successfully support flashback for a specified time.

Time Mapping Granularity

• Oracle gives you a choice between using either clock time or the system change number (SCN) to specify exactly what time in the past you want to go back to.
• The SCN_TO_TIMESTAMP SQL function lets you convert an SCN to a calendar time (TIMESTAMP) value. There is a mapping granularity of three seconds.

SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database

• The TIMESTAMP_TO_SCN function converts a timestamp to its corresponding SCN.

Flashback Database

How Flashback Database Works

• Once you enable the flashback database feature, at regular intervals, a new process RVWR (RecoveryWriter) copies images of each altered block in the datafiles from memory (flashback buffer) to the new flashback logs.
• Oracle stores these flashback logs in the flashback recovery area.
• If you want to flashback to 8:00 A.M., it may turn out that the flashback logs nearest to the target time were written at 7:56 A.M. To cover this gap, you must apply the changes from archived or online redo log files pertaining to that period.
• Always remember that Oracle doesn’t guarantee that you can flashback your database to the flashback retention target. If Oracle is running low on free space in the flash recovery area for newly arriving archived redo log files, it will remove some flashback logs to make room.

Flashback Database Considerations

• If a datafile was resized during the time span covered by the Flashback Database operation, you can’t flashback that datafile. Instead, you must offline that particular datafile before you start the flashback database operation.
• If a control file has been restored or re-created during the time span you want to flashback over, you can’t use the Flashback Database feature.
• You can’t flashback a database to before a RESETLOGS operation.
• You can’t flashback a datafile that was dropped or shrunk during the time span covered by the flashback table operation.

Configuring Flashback Database

1. Ensure that your database is in the archivelog mode.

V$DATABASE (cols: logmode)

ARCHIVE LOG LIST (in SQL*Plus)

2. Your database must be using the flash recovery area.

SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_recovery_file_dest'

3. You must set the initialization parameter DB_FLASHBACK_RETENTION_TARGET to set your flashback retention target (in minutes).

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440

4. Shut down the database and restart in the MOUNT EXCLUSIVE mode.

5. Turn the flashback database feature on with the following command:

ALTER DATABASE FLASHBACK ON;

6. Use the ALTER DATABASE OPEN command to open the database.

SELECT FLASHBACK_ON FROM V$DATABASE;

Note: You can turn the feature off by using the ALTER DATABASE FLASHBACK OFF command while the database in the MOUNT EXCLUSIVE mode. When you do so, Oracle deletes all flashback database logs in the flash recovery area.

Note: If you don’t want certain tablespaces to be part of your flashback operations, issue the following command after setting the tablespace offline:

ALTER TABLESPACE USERS FLASHBACK OFF

Flashbacking a Database

1. Restart the database in the MOUNT (exclusive) mode then issue one of the commands:

FLASHBACK DATABASE TO SCN 5964663

FLASHBACK DATABASE TO BEFORE SCN 5964663

FLASHBACK DATABASE TO TIMESTAMP (SYSDATE -1/24)

FLASHBACK DATABASE TO SEQUENCE 12345

2. Open the database with READ ONLY option to check that the database flashed back to the correct time.
3. If you decide to go back further in time, you can flashback the database again.
4. If you determine that you flashed back too far into the past, you can use redo logs to roll forward.
5. Open the database with RESETLOGS option:

ALTER DATABASE OPEN RESETLOGS

6. If you want to completely undo the effects of the flashback database operation, just use the command RECOVER DATABASE to perform a complete recovery of the database.

Displaying Flashback Storage Information

In order to estimate the space you need to add to your flash recovery area for accommodating the flashback database logs:

SELECT ESTIMATED_FLASHBACK_SIZE, RETENTION_TARGET, FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG

To really know how far back you can flashback your database at any given time, you must query the V$FLASHBACK_DATABASE_LOG in the following manner:

SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG

The view V$FLASHBACK_DATABASE_STATS helps you monitor the I/O overhead of logging flashback data.

BEGIN_TIME and END_TIME stand for the beginning and ending hourly time intervals for which the view’s statistics were collected. Oracle collects flashback data on an hourly basis for a maximum of 24 hours. If you issue a query on the table, however, it may return 25 rows, the 25^th row being for the most recent fraction of time after the last (24th) row was logged in the view.

FLASHBACK_DATA stands for the number of bytes of flashback data written during the interval.

DB_DATA stands for the number of bytes of database data read and written during the interval.

REDO_DATA stands for the number of bytes of redo data written during the interval.

ESTIMATED_FLASHBACK_SIZE is identical to the value of the ESTIMATED_FLASHBACK_SIZE column in the V$FLASHBACK_DATABASE_LOG view.

Flashback Drop

How the Flashback Drop Feature Works

• When you issue the DROP TABLE command, Oracle merely renames the table and moves it to a recycle bin.
• The recycle bin is merely a data dictionary table that maintains information about dropped tables.
• You can use the SELECT command to query the objects in the recycle bin. You can’t use INSERT, UPDATE, and DELETE commands on these objects.

Querying the Recycle Bin

You can view the contents of the recycle bin by using either the DBA_RECYCLEBIN or USER_RECYCLEBIN.

Alternatively, you can use the SHOW RECYCLEBIN command which shows only those objects that you can undrop.

Restoring Dropped Tables

In order to restore a dropped table:

FLASHBACK TABLE persons TO BEFORE DROP

FLASHBACK TABLE "BIN$ksisyyg0TxKnt18rqukpQA==$0"

TO BEFORE DROP RENAME TO NEW_PERSONS

Note: W?hen you flashback a table, Oracle will recover the dependent objects (except bitmap indexes) as well, but they will continue to have their cryptic system-generated names.

If you drop and re-create a table with the same name, the recycle bin will have several versions of the dropped table, each with a unique system-generated table name. If you then issue a FLASHBACK TABLE... TO BEFORE DROP command, Oracle will simply recover the latest version of the table. If you don’t want Oracle to do this, you have the following options:

• In the FLASHBACK TABLE command, provide the specific system-generated name of the table you want to recover.
• Keep issuing the FLASHBACK TABLE command until you recover the particular table you want.

Permanently Removing Tables

DROP TABLE PERSONS PURGE

PURGE TABLE "BIN$Q1QZGCCMRSSCBBRN9IVWFA==$0"

PURGE TABLESPACE USERS USER SCOTT

PURGE RECYCLEBIN or PURGE USER_RECYCLEBIN will remove all objects belonging to the user issuing the command.

PURGE DBA_RECYCLEBIN command will remove all objects in the recycle bin. You must have the SYSDBA privilege to purge the entire recycle bin.

If you drop a tablespace, any objects belonging to the tablespace that are part of the recycle bin are purged immediately.

If you use the command DROP USER ... CASCADE, any objects in the recycle bin that belong to that user are automatically purged.

Restrictions on Flashback Drop

• Table should belong to any non-SYSTEM, locally managed tablespace.
• Dependent objects can be in either a locally or dictionary managed tablespace, to be stored in the recycle bin.
• The following types of dependent objects aren’t saved in the recycle bin:
• Materialized view logs
• Referential integrity constraints
• Bitmap join indexes
• You can’t save a table that has fine-grained auditing (FGA) or Virtual Private Database policies defined on it.

Flashback Table

How Flashback Table Works

Flashback table technology uses undo information to restore data rows in changed blocks of tables.

Pre-requisites

• You must have either the FLASHBACK ANY TABLE or the more specific FLASHBACK object privilege on the table you want to recover. In addition, you must have the SELECT, INSERT, DELETE, and ALTER privileges on the table.
• Make sure you enable row movement in the table:

ALTER TABLE persons ENABLE ROW MOVEMENT

How to Flashback a Table

First, it is useful to note the current SCN then issue the command:

FLASHBACK TABLE persons TO SCN 6039341

FLASHBACK TABLE persons TO TIMESTAMP TO_TIMESTAMP ('2004-07-04 08:05:00', 'YYYY-MM-DD HH24:MI:SS')

Oracle disables all relevant triggers by default and reenables them upon completing the table recovery. You may simply append the ENABLE TRIGGERS clause to your FLASHBACK TABLE command if you want to override this default behavior.

The persons table continues to be online and accessible to users for all queries. However, Oracle acquires exclusive DML locks on the table during the Flashback Table operation.

Undoing a Flashback Table Operation

It is important to note your current SCN before using a Flashback Table operation.

Use the FLASHBACK TABLE statement again to go back to just before you were when you issued the first statement.

Restrictions on Flashback Table

• You can’t flashback a system or remote table.
• You can’t flashback a table back to a time preceding any DDL operation that changes the structure of a table (for example, adding or dropping a column).
• Oracle doesn’t flashback statistics of the recovered objects.

Row Level Flashback Features

The value of the UNDO_RETENTION parameter determines the length of time your users can flashback their queries.

Flashback Query (SELECT...AS OF)

SELECT * FROM persons AS OF TIMESTAMP

TO_TIMESTAMP('2004-07-04 08:05:00', 'YYYY-MM-DD HH:MI:SS') WHERE NAME = 'ALAPATI'

Flashback Versions Query

When you issue a SELECT statement using the VERSIONS clause, Oracle will return the different committed versions of the same row between two SCNs or two timestamps.

VERSIONS BETWEEN

{SCN | TIMESTAMP} start|MINVALUE AND

end|MAXVALUE

[AS OF {SCN|TIMESTAMP expr}]

Here is a brief explanation of pseudocolumns that will be part of the flashback versions query output:

VERSIONS_STARTSCN and VERSIONS_STARTTIME This pseudocolumn tells you the SCN and timestamp when this particular row was first created.

VERSIONS_ENDSCN and VERSIONS_ENDTIME These pseudocolumns tell you when this particular row expired.

VERSIONS_OPERATION This pseudocolumn provides you with information as to the type of DML activity that was performed on the particualr row. The DML activities are indicated by letters: I stands for insert, D for delete, and U for update.

VERSIONS_XID This pseudocolumn stands for the unique transaction identifier of the transaction that resulted in this row version.

Note: If the VERSIONS_STARTSCN and the VERSIONS_STARTTIME are NULL, then the row was created before the lower bound specified by your BETWEEN clause.

Note: If the VERSIONS_ENDSCN and the VERSIONS_ENDTIME are NULL, this means that this row version is current when you tried to use the Flashback Versions Query operation, or the row was part of a delete operation.

Note: An index-organized table (IOT) will show an update operation as a separate insert and a delete operation.

Example:

SELECT VERSIONS_XID XID, VERSIONS_STARTSCN START_SCN, VERSIONS_ENDSCN END_SCN, VERSIONS_OPERATION OPERATION, empname, salary

FROM hr.emp

VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE

AS OF SCN 113900

WHERE empno = 111

Flashback Transaction Query

FLASHBACK_TRANSACTION_QUERY lets you identify which transaction or transactions were responsible for certain changes during a certain interval.

Its columns are:

XID, START_SCN, START_TIMESTAMP, COMMIT_SCN, COMMIT_TIMESTAMP, LOGON_USER, UNDO_CHANGE#, OPERATION, TABLE_NAME, TABLE_OWNER, ROW_ID, UNDO_SQL

Note: You must have the SELECT ANY TRANSACTION system privilege to query the FLASHBACK_TRANSACTION_QUERY view.

Using Flashback Transaction Query and Flashback Versions Query

SELECT XID, START_SCN START, COMMIT_SCN COMMIT,

OPERATION OP, LOGON_USER USER,

UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY

WHERE XID = HEXTORAW('000200030000002D')

Value passed to HEXTORAW function obtained from Flashback versions query of an old row version to undo or audit.

Flashback Transaction Query Considerations

• Flashback Transaction Query on a transaction underlying a DDL displays the changes made to the data dictionary.
• When you use Flashback Transaction Query on a dropped table, object number ( not the table name) will be displayed.
• When you use Flashback Transaction Query on a dropped table, userid (not the username) will be displayed.
• If you query a transaction involving an IOT, an update operation is always shown as a two-step delete/insert operation.
• Sometimes you’ll notice a value of UNKNOWN under the OPERATION column, if the transaction didn’t have enough undo information to correctly identify its operation type.
• You may want to turn on minimal supplemental logging in order to support operations involving chained rows and special storage structures such as clustered tables.

ALTER DATABASE ADD SUPPLEMENT LOG DATA