Here are the steps to perform Flashback Re-instantiation:

1. On your new primary database (Instance B): SQL> select standby_became_primary_scn from v$database;

STANDBY_BECAME_P
----------------
2960985

2. Mount the old primary database (Instance A).
   SQL> STARTUP MOUNT
3. Flashback the old primary database (Instance A) to the SCN.
   SQL> flashback database to scn 2960985;
4. Disable Flashback on the old primary database (Instance A).
   SQL> ALTER DATABASE FLASHBACK OFF;
5. On the old primary database (Instance A), create a standby control file.
   SQL> alter database create standby controlfile
   as'/dba/standby/stbycf.f' reuse;
6. Shutdown the old primary database (Instance A), and replace the control files with the new standby control files.
   SQL> shutdown immediate;
   $ cp /dba/standby/stbyct.f /u02/oradata/sid/control01.ctl
   $ cp /dba/standby/stbyct.f /u02/oradata/sid/control02.ctl
7. Bring up the old primary database as a new physical standby database (Instance A).
   SQL> startup mount;
8. Turn flashback back on the new primary database (Instance A).
   SQL> alter database flashback on;
9. Enable transport from the new primary database (Instance B)
   SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;
10. On the new standby database (Instance A), start real time apply.
    SQL> RECOVER MANAGED STANDBY DATABASE using current logfile DISCONNECT;
11. The Managed Recovery process (MRP) will hit the End-Of-Redo and then need to be restarted.
    SQL> RECOVER MANAGED STANDBY DATABASE using current logfile DISCONNECT;

Flashback Drop

Prior to Oracle 10g, a DROP command permanently removed objects from the database. In Oracle 10g, a DROP command places the object in the recycle bin. The extents allocated to the segment are not reallocated until you purge the object. You can restore the object from the recycle bin at any time.

This feature eliminates the need to perform a point-in-time recovery operation. Therefore, it has minimum impact to other database users.

Recycle Bin

A recycle bin contains all the dropped database objects until,

- You permanently drop them with the PURGE command.
- Recover the dropped objects with the UNDROP command.
- There is no room in the tablespace for new rows or updates to existing rows.
- The tablespace needs to be extended.

You can view the dropped objects in the recycle bin from two dictionary views:

- user_recyclebin - lists all dropped user objects
- dba_recyclebin - lists all dropped system-wide objects

Example 2: Restoring a Dropped Object

This example will restore a dropped table test.

SQL> flashback table RB$$42514$TABLE$0 to before drop;

Example 3: Dropping a Table Permanently

This statement puts the table in the recycle bin:

Example 4: Dropping a Tablespace

You can only issue this command when the tablespace users is empty. Objects in the recycle bin of tablespace users will be purged:

This statement purges the user recycle bin:

Flashback Table allows you to recover a table or tables to a specific point in time without restoring a backup. When you use the Flashback Table feature to restore a table to a specific point in time, all associated objects, such as indexes, constraints, and triggers will be restored.

Flashback Table operations are not valid for the following object types:

- Tables that are part of a cluster
- Materialized views
- Advanced Queuing tables
- Static data dictionary tables
- System tables
- Partitions of a table
- Remote tables (via database link)

Flashback Table is extremely useful when a user accidentally inserts, deletes, or updates the wrong rows in a table. It provides a way for users to easily and quickly recover a table to a previous point in time.

However, if the following DDL commands are issued, the flashback table command does not work:

- ALTER TABLE ... DROP COLUMN
- ALTER TABLE ... DROP PARTITION
- CREATE CLUSTER
- TRUNCATE TABLE
- ALTER TABLE ... MOVE

undo_retention Parameter

Data used to recover a table is stored in the undo tablespace. You can use the parameter undo_retention to set the amount of time you want undo information retained in the database.

To create an undo tablespace with the RETENTION GUARANTEE option, issue the following command:

CREATE UNDO TABLEAPCE undo_tbs

DATAFIEL '/u02/oradata/grid/undo_tbs01.dbf' SIZE 1 G

RETENTION GUARANTEE;

Guaranteed Retention

When an active transaction uses all the available undo tablespace, the system will start reusing undo space that would have been retained, unless you have specified RETENTION GUARANTEE for the tablespace.

Flashback Table Privileges

You must have the FLASHBACK TABLE or FLASHBACK ANY TABLE system privilege to use the Flashback Table feature.

Example 1: Flashback Table using SCN

This statement brings a table 'billing' back to a certain SCN number; table row movement must be enabled as a prerequisite:

This statement brings a table 'billing' back to a certain timestamp:

Flashback Query was first introduced in Oracle9i to provide a way to view historical data. In Oracle 10g, this feature has been extended. You can now retrieve all versions of the rows that exist or ever existed between the time the query was issued and a point back in time. This type of query is called Flashback Row History.

You can use the VERSIONS BETWEEN clauses to retrieve all historical data related to a row.

Let's take a look at the example below:

The VERSION BETWEEN clause does not change the query plan. You can use the clause in a SELECT statement against a view. However, you cannot use the VERSION BETWEEN clause in a view definition.

The row history data is stored in the undo tablespace. The undo_retention initialization parameter specifies how long the database will keep the committed undo information. If a new transaction needs to use undo space and there is not enough free space left, any undo information older than the specified undo retention period will be overwritten. Therefore, you may not be able to see all the row histories. However, you can set the undo tablespace option to RETENTION GUARANTEE to retain all row histories.

To verify the retention value for the tablespace, you can issue the following statement:

The Flashback Transaction History feature provides a way to view changes made to the database at the transaction level. It allows you to diagnose problems in your database and perform analysis and audit transactions. You can use this feature in conjunction with the Flash Row History feature to roll back the changes made by a transaction. You can also use this feature to audit user and application transactions. The Flashback Transaction History provides a faster way to undo a transaction than LogMiner.

You can retrieve the transaction history from dba_transaction_query view:

SQL> desc dba_transaction_query

 Name                                 Null?    Type

 ------------------------------------ -------- ----------------

 XID                                           RAW(8)

 START_SCN                                     NUMBER

 START_TIMESTAMP                               DATE

 COMMIT_SCN                                    NUMBER

 COMMIT_TIMESTAMP                              DATE

 LOGON_USER                                    VARCHAR2(30)

 UNDO_CHANGE#                                  NUMBER

 OPERATION                                     VARCHAR2(32)

 TABLE_NAME                                    VARCHAR2(256)

 TABLE_OWNER                                   VARCHAR2(32)

 ROW_ID                                        VARCHAR2(19)

 UNDO_SQL                                      VARCHAR2(4000)

The above is an excerpt from the bestselling Oracle10g book Oracle Database 10g New Features by Mike Ault, Madhu Tumma and Daniel Liu, published by Rampant TechPress.