You can use the FLASHBACK DATABASE command to return your database contents to points in time within the flashback window. You can also use FLASHBACK DATABASE to return to any guaranteed restore point you previously defined

Note: If you do not have a guaranteed restore point defined, verify that the target SCN is within the flashback window, the range of SCNs for which you can use FLASHBACK DATABASE.

If the flashback window does not extend far enough back into the past to reach the desired target time and you do not have a guaranteed restore point at the desired time, you can achieve similar results by using database point-in-time recovery

1. Performing Flashback Database:

Determine the desired SCN, restore point or point in time for the FLASHBACK DATABASE command.

1
2
3

rman TARGET /
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;

#Specify the target time using one of the forms

1
2
3

RMAN> FLASHBACK DATABASE TO SCN 46963;
RMAN> FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;
RMAN> FLASHBACK DATABASE TO TIME "TO_DATE('09/20/00','MM/DD/YY')";

Verify that you have returned the database to the desired state, by opening the database read-only and performing some queries to inspect the database contents.

1

RMAN> SQL 'ALTER DATABASE OPEN READ ONLY';

1.1. Options After a Successful Flashback Database Operation

If you are satisfied with the state of the database after the Flashback Database operation, you have two choices:

• Make the database available for updates by performing an OPEN RESETLOGS operation:
  RMAN> ALTER DATABASE OPEN RESETLOGS;
• Use Oracle export utilities to export the objects whose state was corrupted. Then, recover the database to the present time:
  - This step undoes the effect of the Flashback Database, by re-applying all changes in the redo logs to the database, returning it to the most recent SCN.
  RMAN> RECOVER DATABASE; After re-opening the database read-write, you can import the exported objects using the import utility

1.2. Options After Flashback Database to the Wrong Time

If, after investigating the state of your database, you find that you used the wrong restore point, time or SCN for Flashback Database, then you have several options:

If your chosen target time was not far enough in the past, then you can use another FLASHBACK DATABASE command to rewind the database further in time.

1

RMAN> FLASHBACK DATABASE TO SCN 42963;  #earlier than current SCN

If you chose a target SCN that is too far in the past, then you can mount the database and use RECOVER DATABASE UNTIL to wind the database forward in time to the desired SCN:

1

RMAN> RECOVER DATABASE UNTIL SCN 56963; #later than current SCN

If you want to completely undo the effect of the FLASBACK DATABASE command, you can perform complete recovery of the database by using the RECOVER DATABASE command without an UNTIL clause or SET UNTIL command:

1

RMAN>  RECOVER DATABASE;

This re-applies all changes to the database, returning it to the most recent SCN.

1.3. Performing Flashback Database to a Guaranteed Restore Point

You can list the available guaranteed restore points using the V$RESTORE_POINT view, as follows:

1
2
3
4
5
6
7
8

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE='YES';
 
NAME                   SCN TIME                  DATABASE_INCARNATION# GUA
--------------- ---------- --------------------- --------------------- ---
BEFORE_CHANGES     5753126 04-MAR-05 12.39.45 AM                     2 YES

Having identified the restore point to use, mount the database and run the FLASHBACK DATABASE command, using the restore point. For example:

1
2
3

RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_CHANGES';

When the command completes, you may open the database read-only and inspect the effects of the operation, and if satisfied, open the database with the RESETLOGS option

2. Flashback Query: Recovering at the Row Level

You discover that at 12:30 PM, an employee 'JOHN' had been deleted from your EMP table, and you know that at 9:30AM that employee's data was correctly stored in the database, you could query the contents of the table as of a time before the deletion to find out what data had been lost, and, if appropriate, re-insert the lost data in the database.

For example, the following query retrieves the state of the employee record for 'JOHN' at 9:30AM, April 4, 2005:

1
2
3

SELECT * FROM EMP AS OF TIMESTAMP
TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN';

Restoring John's information to the table EMP requires the following update:

1
2
3
4

INSERT INTO EMP
(SELECT * FROM EMP AS OF TIMESTAMP
TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN');

3. Flashback Versions Query

4. Flashback Transaction Query

5. Flashback Table: Returning Individual Tables to Past States

• Flashback Table restores tables while automatically maintaining associated attributes such as current indexes, triggers and constraints.
• You do not have to restore any data from backups,

5.1. 17.A- Prerequisites for Using Flashback Table

• Row movement must be enabled on the table. Use following SQL statement:
  ALTER TABLE table ENABLE ROW MOVEMENT;
• You must have FLASHBACK ANY TABLE or FLASHBACK object privilege on the table.
• You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.

5.2. 17.B- Performing Flashback Table

The EMP table is restored to its state when the database was at the time specified by the SCN.
FLASHBACK TABLE EMP TO SCN 123456;

You can also specify the target point in time for the FLASHBACK TABLE operation using TO_TIMESTAMP. For example:
FLASHBACK TABLE EMP TO TIMESTAMP
TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')

5.3. 17.C- mapping of timestamps to SCNs

The mapping of timestamps to SCNs is not always exact. When using timestamps with the FLASHBACK TABLE statement, the actual point in time to which the table is flashed back can vary by up to approximately three seconds of the time specified for TO_TIMESTAMP. If an exact point in time is required, use an SCN rather than a time expression.

5.4. 17.E- Triggers State during operation

By default, the database disables triggers during FLASHBACK TABLE operation, and after operation enabled or disabled again. If you wish to enable triggers during FLASHBACK TABLE, use following statement:

1
2
3
4
5

FLASHBACK TABLE table_name
TO TIMESTAMP timestamp ENABLE TRIGGERS;
 
FLASHBACK TABLE EMP
TO TIMESTAMP TO_TIMESTAMP('2005-03-03 14:00:00') ENABLE TRIGGERS;

6. Flashback Drop: Undo a DROP TABLE Operation

For example, this statement places the EMPLOYEE_DEMO table, along with any indexes, constraints, or other dependent objects listed previously, in the recycle bin:

1

SQL> DROP TABLE EMPLOYEE_DEMO;

The table and its dependent objects will remain in the recycle bin until they are purged from the recycle bin. You can explicitly purge a table or other object from the recycle bin with the SQL*Plus PURGE statement

If you are sure that you will not want to recover a table later, you can drop it immediately and permanently, instead of placing it in the recycle bin, by using the PURGE option of the DROP TABLE statement, as shown in this example:

1

DROP TABLE employee_demo PURGE;

Recycle bin objects are not counted as used space. If you query the space views to obtain the amount of free space in the database, objects in the recycle bin are counted as free space.

Dropped objects still appear in the views USER_TABLES, ALL_TABLES, DBA_TABLES, USER_INDEX, ALL_INDEX and DBA_INDEX. A new column, DROPPED, is set to YES for these objects. You can use the DROPPED column in queries against these views to view only objects that are not dropped.

To view only objects in the recycle bin, use the USER_RECYCLEBIN and DBA_RECYCLEBIN views

6.1. Enabling and Disabling the Recycle Bin

The recycle bin is enabled by default. The initialization parameter RECYCLEBIN can be used to explicitly enable or disable the recycle bin.

You can also use an ALTER SYSTEM statement to change the value of the RECYCLEBIN parameter for the entire database. For example:

1

ALTER SYSTEM SET RECYCLEBIN=OFF|ON;

To specify recycle bin behavior for your own database session

1

ALTER SESSION SET RECYCLEBIN=OFF;

Objects you drop during this session are no longer placed in the recycle bin. However, other users continue to be protected by the recycle bin.

Note:
Objects already in the recycle bin are not affected by enabling or disabling the recycle bin using ALTER SYSTEM or ALTER SESSION.

6.2. 18.B- Viewing and Querying Objects in the Recycle Bin

This example uses the views to determine the original names of dropped objects:

1
2

SQL> SELECT object_name AS recycle_name, original_name, type
FROM recyclebin;

6.3. Purging Objects from the Recycle Bin

The PURGE command is used to permanently purge objects from the recycle bin. Once purged, objects can no longer be retrieved from the bin using Flashback Drop.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

#purges individual table and all of its dependent objects from the recycle 
#bin
PURGE TABLE EMP;
 
#Purge specific table
PURGE TABLE "BIN$KSD8DB9L345KLA==$0";
 
#If you have created and dropped multiple tables with the same orignal name,
CREATE TABLE EMP;   # version 1 of the table
DROP TABLE EMP;     # version 1 dropped
CREATE TABLE EMP;   # version 2 of the table
DROP TABLE EMP;     # version 2 dropped
CREATE TABLE EMP;   # version 3 of the table
DROP TABLE EMP;     # version 3 dropped
 
#If you execute PURGE TABLE EMP several times, the effect is as described 
#here:
PURGE TABLE EMP;    # version 1 of the table is purged
PURGE TABLE EMP;    # version 2 of the table is purged
PURGE TABLE EMP;    # version 3 of the table is purged
 
#To purge just an index , while keeping the base table:
PURGE INDEX "BIN$GTE72KJ22H9==$0";
 
# To purge all dropped tables and other dependent objects from a specific 
# tablespace
PURGE TABLESPACE hr;
 
#To purge only objects from a tablespace belonging to a specific user
PURGE TABLESPACE hr USER scott;
 
# purges the contents of the recycle bin for the currently logged-in user.
PURGE RECYCLEBIN;
 
#To purge all objects from the recycle bin DBA role is requied
PURGE DBA_RECYCLEBIN;

6.4. 18.D- Privileges for Flashback Drop

DROP:
Any user with drop privileges over the object can drop the object, placing it in the recycle bin.

FLASHBACK TABLE... TO BEFORE DROP
Privileges are tied to the privileges for DROP. That is, any user who can drop an object can perform Flashback Drop to retrieve the dropped object from the recycle bin.

PURGE
Privileges are tied to the DROP privileges. Any user having DROP TABLE or DROP ANY TABLE privileges can purge the objects from the recycle bin.

SELECT for objects in the Recycle Bin

Users must have SELECT and FLASHBACK privileges over an object in the recycle bin to be able to query the object in the recycle bin.

6.5. 18.E- Performing Flashback Drop on Tables in the Recycle Bin

The following example restores the BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0 table, changes its name back to hr.int_admin_emp, and purges its entry from the recycle bin:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

#restore form recycle bin with it original name
FLASHBACK TABLE "BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0" TO BEFORE DROP;
OR
FLASHBACK TABLE HR.INT_ADMIN_EMP TO BEFORE DROP;
 
# Restore with new name
FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE DROP
RENAME TO hr.int2_admin_emp;
 
#Multiple Objects With the Same Original Name
CREATE TABLE EMP ( ...COLUMNS ); # EMP version 1
DROP TABLE EMP;
CREATE TABLE EMP ( ...COLUMNS ); # EMP version 2
DROP TABLE EMP;
CREATE TABLE EMP ( ...COLUMNS ); # EMP version 3
DROP TABLE EMP;
 
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VERSION_3;
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VERSION_2;
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VERSION_1;
 
 
 

Related Articles

1. FlashBack Database Technology [Part-1] [Introduction]
2. FlashBack Database Technology [Part-2] [Set up and Maintain flashback database]
3. FlashBack Database Technology [Part-3] [Set up and Maintain Restore points]
4. FlashBack Database Technology [Part-4] [Flashback Database]
5. FlashBack Database Technology [Part-5] [Flashback and Database Point-in-Time Recovery (Coming Soon)]