Overview of Flashback Technology

Oracle Flashback Query:select as of
Oracle Flashback Version Query:
Dbms_flashback Package
Oracle Flashback Table:
Oracle Flashback Drop:
Oracle Flashback Database:
http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmflash.htm#i1018669

Oracle Flashback Query:select as of
Oracle Flashback Version Query:
SELECT VERSIONS_STARTSCN, Versions_starttime,
VERSIONS_ENDSCN, Versions_endtime,
Versions_xid, Versions_operation,
Name, Salary
From Employees
VERSIONS between TIMESTAMP
To_timestamp (' 2003-07-18 14:00:00 ', ' yyyy-mm-dd HH24:MI:SS ')
and To_timestamp (' 2003-07-18 17:00:00 ', ' yyyy-mm-dd HH24:MI:SS ')
WHERE name = ' JOE ';

Flashback Transaction Query (Federated queries with Flashback Version query)
SELECT XID, Logon_User
From Flashback_transaction_query
WHERE XID in (
SELECT Versions_xid from employees versions between TIMESTAMP
To_timestamp (' 2003-07-18 14:00:00 ', ' yyyy-mm-dd HH24:MI:SS ') and
To_timestamp (' 2003-07-18 17:00:00 ', ' yyyy-mm-dd HH24:MI:SS ')
);

Dbms_flashback Package

Flashback Transaction:flashback Transaction is part of the Dbms_flashback package

Flashback Data Archive (Oracle total Recall):

Create table employee and store the historical data in the default Flashback data Archive:

CREATE TABLE Employee (EMPNO number (4) Not NULL, ename VARCHAR2 (10),
JOB VARCHAR2 (9), MGR number (4)) FLASHBACK ARCHIVE;

Create table employee and store the historical data in the Flashback data Archive fla1:

CREATE TABLE Employee (EMPNO number (4) Not NULL, ename VARCHAR2 (10),
JOB VARCHAR2 (9), MGR number (4)) FLASHBACK ARCHIVE fla1;

Enable flashback archiving for the table employee and store the historical data in the default flashback data Archive:

ALTER TABLE employee FLASHBACK ARCHIVE;

Enable flashback archiving for the table employee and store the historical data in the flashback data Archive FLA1:

ALTER TABLE employee FLASHBACK ARCHIVE fla1;

Disable flashback archiving for the table employee:

ALTER TABLE employee NO FLASHBACK ARCHIVE;


Oracle Flashback Table:


Connect Sql*plus to the target database and identify the current SCN.

You cannot roll back a FLASHBACK table statement, but can issue another FLASHBACK table statement and specify a time J UST prior to the current time. Therefore, it's advisable to record the current SCN. You can obtain it by querying V$database as follows:

SELECT CURRENT_SCN
From V$database;

Identify the time, SCN, or restore point to which your want to return the table.

If you have created restore points and then you can list available restore points by executing the following query:

SELECT NAME, SCN, time
From V$restore_point;

Ensure that enough, undo data exists to rewind, the table to the specified target.

If the undo_retention intialization parameter is set, and the UNDO RETENTION guarantee are on, then you can use the Followi ng query to determine how long undo data is being retained:

SELECT NAME, VALUE/60 minutes_retained
From V$parameter
WHERE NAME = ' undo_retention ';

Ensure that row movement was enabled for all objects, that's rewinding with Flashback Table.

You can enable row movement for a table with the following SQL statement, where table is the name of the the table so you AR E rewinding:

ALTER Table Table ENABLE ROW movement;

Determine whether the table, the intend to flash back have dependencies on other tables. If dependencies exist, then decide whether-flash back these tables as well.

You can issue the following SQL query to determine the dependencies, where schema_name are the schema for the table to be f Lashed back and TABLE_NAME is the name of the table:

SELECT Other.owner, Other.table_name
From Sys.all_constraints this, sys.all_constraints other
WHERE This.owner = schema_name
and this.table_name = table_name
and This.r_owner = Other.owner
and this.r_constraint_name = Other.constraint_name
and this.constraint_type= ' R ';

Execute a FLASHBACK TABLE statement for the objects so want to flash back.

The following SQL statement returns the Hr.temp_employees table to the restore point named Temp_employees_update:

FLASHBACK TABLE Hr.temp_employees
to RESTORE point temp_employees_update;

The following SQL statement rewinds the Hr.temp_employees table to their state when the database is at the time specified b Y The SCN:

FLASHBACK TABLE Hr.temp_employees
to SCN 123456;

As shown in the following example, you can also specify the target point in time with To_timestamp:

FLASHBACK TABLE Hr.temp_employees
To TIMESTAMP To_timestamp (' 2007-10-17 09:30:00 ', ' yyyy-mm-dd HH:MI:SS ');


Oracle Flashback Drop:
Use the FLASHBACK TABLE ... To before DROP statement to recover objects from the Recycle Bin. You can specify either the name of the table in the Recycle Bin or the original table name.

This section assumes a scenario in which you drop the wrong table. Many times you had been asked to drop tables in the test databases, but in this case you accidentally connect to the Prod Uction database instead and drop Hr.employee_demo. You decide to use FLASHBACK TABLE to retrieve the dropped object.

To retrieve a dropped table:

Connect Sql*plus to the target database and obtain the name of the dropped table in the Recycle Bin.

You can use the Sql*plus command SHOW RecycleBin as follows:

SHOW RecycleBin;

ORIGINAL name RecycleBin name TYPE DROP time
---------------- --------------------------------- ------------ -------------
Employee_demo bin$gk3lsj/3akk5hg3j2lkl5j3d==$0 TABLE 2005-04-11:17:08:54

The ORIGINAL Name column shows the ORIGINAL name of the object, while the RecycleBin Name column shows the name of the obj ECT as it exists in the bin.

Alternatively, you can query User_recyclebin or dba_recyclebin to obtain the table name. The following example queries the views to determine the original names of dropped objects:

SELECT object_name as Recycle_name, original_name, type
From RecycleBin;

Recycle_name original_name TYPE
--------------------------------  ---------------------  ----------
Bin$gk3lsj/3akk5hg3j2lkl5j3d==$0 Employee_demo TABLE
Bin$jks983293m1dsab4gsz/i249==$0 I_emp_demo INDEX

If you plan to manually the restore original names for dependent objects and then ensure so you make note of each dependent obj ECT ' s system-generated Recycle Bin name before you restore the table.

Note:
Object views such as Dba_tables does not display the Recycle Bin objects.

Optionally, query the table in the Recycle Bin.

You must use the Recycle Bin name of the object in your query rather than the object ' s original name. The following example queries the table with the Recycle Bin name of bin$ksd8db9l345kla==$0:

SELECT *
From "bin$gk3lsj/3akk5hg3j2lkl5j3d==$0";

Quotes is required because of the special characters in the Recycle Bin name.

Note:
If you have the necessary privileges and then you can also use Oracle Flashback Query on tables in the Recycle Bin, and only By using the Recycle Bin name rather than the original table name. You cannot use DML or DDL statements on objects in the Recycle Bin.

Retrieve the dropped table.

Use the FLASHBACK TABLE ... To before DROP statement. The following example restores the Bin$gk3lsj/3akk5hg3j2lkl5j3d==$0 table, the changes its name is back to Hr.employee_demo, and Purges its entry from the Recycle Bin:

FLASHBACK TABLE "Bin$gk3lsj/3akk5hg3j2lkl5j3d==$0" to before DROP;

Note that the table name was enclosed in quotes because of the possibility of special characters appearing in the Recycle B In object names.

Alternatively, you can use the original name of the table:

FLASHBACK TABLE HR. Employee_demo to before DROP;

You can also assign a new name to the restored table by specifying the RENAME to clause. For example:

FLASHBACK TABLE "Bin$ksd8db9l345kla==$0" to before DROP
RENAME to Hr.emp_demo;

Optionally, verify that all dependent objects retained their system-generated Recycle Bin names.

The following query determines the names of the indexes of the retrieved Hr.employee_demo table:

SELECT index_name
From User_indexes
WHERE table_name = ' employee_demo ';

Index_name
------------------------------
Bin$jks983293m1dsab4gsz/i249==$0

Optionally, rename the retrieved indexes to their original names.

The following statement renames the index to its original name of I_emp_demo:

ALTER INDEX "bin$jks983293m1dsab4gsz/i249==$0" RENAME to I_emp_demo;

If The retrieved table had referential constraints before it is placed in the Recycle Bin and then re-create them.

This step must is performed manually because the Recycle Bin does not preserve referential constraints on a table.

Retrieving Objects when multiple Objects Share the same Original Name

You can create, and then drop, several objects with the same original name. All the dropped objects is stored in the Recycle Bin. For example, consider the SQL statements in the following example.

Example 16-1 dropping multiple Objects with the same Name

CREATE TABLE temp_employees (... columns); # Temp_employees Version 1
DROP TABLE temp_employees;

CREATE TABLE temp_employees (... columns); # Temp_employees Version 2
DROP TABLE temp_employees;

CREATE TABLE temp_employees (... columns); # Temp_employees Version 3
DROP TABLE temp_employees;

In Example 16-1, each table Temp_employees was assigned a unique name in the Recycle Bin when it was dropped. can use a FLASHBACK TABLE ... To before DROP statement with the original name of the table, as shown in this example:

FLASHBACK TABLE temp_employees to before DROP;

The most recently dropped table and this original name are retrieved from the Recycle Bin, with its original name. Example 16-2 shows the retrieval from the Recycle Bin of any three dropped temp_employees tables from the previous Example With each assigned a new name.

Example 16-2 renaming Dropped Tables

FLASHBACK TABLE temp_employees to before DROP
RENAME to Temp_employees_version_3;
FLASHBACK TABLE temp_employees to before DROP
RENAME to Temp_employees_version_2;
FLASHBACK TABLE temp_employees to before DROP
RENAME to Temp_employees_version_1;

Because the original name in FLASHBACK table refers to the most recently dropped table with this name, the last TABLE drop PED is the first retrieved.

You can also retrieve no table from the Recycle Bin, regardless of any collisions among original names, by using the Uniq UE Recycle Bin name of the table. For example, assume this you query the Recycle Bin as follows (sample output included):

SELECT object_name, Original_name, Createtime
From RecycleBin;

object_name original_name Createtime
------------------------------ --------------- -------------------
Bin$yrmklzalmhfgnagaimenra==$0 temp_employees 2007-02-05:21:05:52
Bin$yrmklzavmhfgnagaimenra==$0 temp_employees 2007-02-05:21:25:13
Bin$yrmklzaqmhfgnagaimenra==$0 temp_employees 2007-02-05:22:05:53

You can use the following command to retrieve the middle table:

FLASHBACK TABLE bin$yrmklzavmhfgnagaimenra==$0 to before DROP;


Oracle Flashback Database:

Overview of Flashback Technology