Flashback Query:
You can perform a Flashback Query using a SELECT statement with an AS OF clause. You can use a Flashback Query to retrieve data as it existed at some time in the past. The query explicitly references a past time using a timestamp or SCN. It returns committed data that was current at that point in time.
Example:
This example uses a Flashback Query to examine the state of a table at a specified time in the past. Suppose, for instance, that a DBA discovers at 12:30 PM that data for employee JON has been deleted from the employee table, and the DBA knows that at 9:30AM the data for JON was correctly stored in the database. The DBA can use a Flashback Query to examine the contents of the table at 9:30, to find out what data has been lost. If appropriate, the DBA can then re-insert the lost data in the database.
The following query retrieves the state of the employee record for JOHN at 9:30AM, April 4, 2006:
SQL> SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2006-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JON';
This update then restores John's information to the employee table:
SQL> INSERT INTO employee
(SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2006-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JON');
Flashback Query Functions
The TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP functions have been added to SQL and PL/SQL to simplify flashback operations:
DECLARE
l_scn NUMBER;
l_timestamp TIMESTAMP;
BEGIN
l_scn := TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);
l_timestamp := SCN_TO_TIMESTAMP(l_scn);
END;
/
Flashback Version Query
The Flashback Version Query is a powerful tool for the DBA to run analysis and answer the question, "How did this happen?".
Various elements for this are shown below:
VERSIONS_XID - The transaction id that created this version of the row
VERSIONS_OPERATION - The action that created this version of the row (such as delete, insert, and update)
VERSIONS_STARTSCN - The SCN in which this row version first occurred
VERSIONS_ENDSCN - The SCN in which this row version was changed.
For example: we use the Dept table in Scott schema and update dept 10 to 11, then 12, and then 13 (with a commit after every update). Thus we have done three updates:
SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation,
dept from Dept versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V Dept
---------------------- ---------------------- ---------------- - ----------
01-DEC-06 03.57.12 PM 01-DEC-06 03.57.30 PM 0002002800000C61 I 10
01-DEC-06 03.57.30 PM 01-DEC-06 03.57.39 PM 000A000A00000029 U 11
01-DEC-06 03.57.39 PM 01-DEC-06 03.57.55 PM 000A000B00000029 U 12
We can automatically generate the undo statement using:
SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID = '000A000A00000029';
- output: update "SCOTT"."Dept" set "Dept" = '12' where ROWID = 'AAAMicAAEAAAAA/AAA';
Flashback Transaction Query
A Flashback Transaction Query is a query on a view called FLASHBACK_TRANSACTION_QUERY. You use a Flashback Transaction Query to obtain transaction information, including SQL code that you can use to undo each of the changes made by the transaction. It uses the XID from above and shows the complete transaction steps for that XID. For example:
SQL> SELECT xid, start_scn START, commit_scn COMMIT,
operation OP, logon_user USER, undo_sql FROM flashback_transaction_query
WHERE xid = HEXTORAW('000200030000002D');
XID START COMMIT OP USER UNDO_SQL
---------------- ----- ------ -- ---- ---------------------------
000200030000002D 195243 195244 DELETE HR insert into "HR"."EMP"
("EMPNO","EMPNAME","SALARY") values ('111','Mike','655');
000200030000002D 195243 195244 INSERT HR delete from "HR"."DEPT"
where ROWID = 'AAAKD4AABAAAJ3BAAB';
000200030000002D 195243 195244 UPDATE HR update "HR"."EMP"
set "SALARY" = '555' where ROWID = 'AAAKD2AABAAAJ29AAA';
000200030000002D 195243 113565 BEGIN HR
4 rows selected.
Flashback Table
Just as the flashback query helps to retrieve rows of a table, FLASHBACK TABLE helps to restore the state of a table to a certain point in time. The following simple command will take us to the table state at the specified timestamp:
SQL> FLASHBACK TABLE Employee TO
TIMESTAMP TO_TIMESTAMP('13-SEP-06 8:50:58','DD-MON-YY HH24: MI: SS');
Flashback Drop (Recycle Bin)
The accidental dropping of objects has always been a problem for users and DBAs alike. Users soon realize their mistake, but by then it's too late - and until now there has been no easy way to recover those dropped tables, indexes, constraints, triggers, etc.
Flashback Drop provides a safety net when dropping objects in Oracle Database 10g. When a user drops a table, Oracle automatically places it into the Recycle Bin.
The Recycle Bin
The Recycle Bin is a virtual container for all dropped objects. Under its lid, the objects occupy the same space as when they were created. If table EMP was created in the USERS tablespace, the dropped table EMP remains in the
USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependent objects are not moved; they are simply renamed with the prefix BIN$$. You can continue to access the data in a dropped table, or even use Flashback Query on it.
SQL> FLASHBACK TABLE dept_test TO BEFORE DROP;
Restoring a Dropped Table
You can restore any dropped table, as long as it is still listed in the Recycle Bin, by using the
FLASHBACK TABLE table_name TO BEFORE DROP command (at which point Oracle will also remove it
from the Recycle Bin). The following example would restore the previously dropped persons table.
SQL> FLASHBACK TABLE persons TO BEFORE DROP;
Flashback complete.
SQL>
Alternatively, you can use the system-generated table name:
SQL> FLASHBACK TABLE "BIN$xTMPjHZ6SG+1xnDIaR9E+g==$0"
TO BEFORE DROP;
Flashback complete.
SQL>
As part of the Flashback operation, you may want to rename the previously dropped table, as
follows (you can use either the system-generated or original table name):
SQL> FLASHBACK TABLE "BIN$xTMPjHZ6SG+1xnDIaR9E+g==$0"
TO BEFORE DROP
RENAME TO NEW_PERSONS;
Flashback Database
Traditional point in time recovery required restoring databasefiles and required applying redo data forward to a given point in time, to a given SCN. Flashback database requires flashback logs to be applied backwards, so Oracle is backing out the changes, one need not to restore backupped database files anymore. As such the bigger the database is, the lesser the transactional activity is, the more interesting it becomes to use the flashback database feature in order to bring the database back in time.
In order to be able to back out the changes Oracle 10G uses a new kind of log files : the Flashback Database Logs which are created in the flash recovery area ( db_recovery_file_dest ). These new type of log files are written towards the flash recovery area by a new background process RVWR which sequentially writes from the flashback buffer towards the recovery area. The flashback buffer - part of the SGA - depends on the sizing of the log buffer, once more it is recommended for databases with high transactional activity to have an appropriate log buffer sizing.
Recovring Truncated table
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
6052503
SQL> connect testora10/testora10
Connected.
SQL> select count(*) from porder;
COUNT(*)
----------
500000
The user is doing an unwanted operation
SQL> truncate table porder;
Table truncated.
SQL> select count(*) from porder;
COUNT(*)
----------
0
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
6054696
In order to definitively reset the database to that SCN one need to open the database with resetlogs. The DBA can temporary take a look and open the flashbacked db in read only mode, the flashback is not permanent
SQL> connect sys/cosw as sysdba
Connected.
SQL> flashback database to scn 6052503;
flashback database to scn 6052503
*
ERROR at line 1:
ORA-38757: Database must be mounted EXCLUSIVE and not open to FLASHBACK.
Let 's shutdown and startup mount the instance
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 788524 bytes
Variable Size 150304724 bytes
Database Buffers 54525952 bytes
Redo Buffers 4096000 bytes
Database mounted.
SQL> flashback database to scn 6052503;
Flashback complete.
Let' s take a look at the alertfile
Completed: ALTER DATABASE MOUNT
Mon Jul 25 17:59:13 2005
flashback database to scn 6052503
Mon Jul 25 17:59:14 2005
Starting Flashback Database to before SCN 6052504
Flashback Restore Start
Starting Flashback Restore of datafile 1.
Datafile 1: 'C:\ORACLE\ORADATA\ORA10\SYSTEM01.DBF'
Starting Flashback Restore of datafile 2.
Datafile 2: 'C:\ORACLE\ORADATA\ORA10\UNDOTBS01.DBF'
Starting Flashback Restore of datafile 3.
Datafile 3: 'C:\ORACLE\ORADATA\ORA10\SYSAUX01.DBF'
Starting Flashback Restore of datafile 4.
Datafile 4: 'C:\ORACLE\ORADATA\ORA10\USERS01.DBF'
Starting Flashback Restore of datafile 5.
Datafile 5: 'C:\ORACLE\ORADATA\ORA10\EXAMPLE01.DBF'
Starting Flashback Restore of datafile 7.
Datafile 7: 'C:\ORACLE\ORADATA\ORA10\DAMIEN.DBF'
Starting Flashback Restore of datafile 9.
Datafile 9: 'C:\ORACLE\ORADATA\ORA10\TEST.DBF'
Starting Flashback Restore of datafile 10.
Datafile 10: 'C:\ORACLE\ORADATA\ORA10\CW4ORAD1.DBF'
Starting Flashback Restore of datafile 11.
Datafile 11: 'C:\ORACLE\ORADATA\ORA10\CW4ORAI1.DBF'
Starting Flashback Restore of thread 1.
Mon Jul 25 17:59:30 2005
Completed Flashback Restore of thread 1.
Flashback Restore Complete
Flashback Media Recovery Start
Mon Jul 25 17:59:33 2005
Recovery of Online Redo Log: Thread 1 Group 2 Seq 760 Reading mem 0
Mem# 0 errs 0: C:\ORACLE\ORADATA\ORA10\REDO02.LOG
Mon Jul 25 17:59:44 2005
Media Recovery Applied UNTIL CHANGE 6052507
Flashback Media Recovery Complete
Completed: flashback database to scn 6052503
Let ' s open the database in read only mode in order to have a look
SQL> alter database open read only;
Database altered.
SQL> select count(*) from testora10.porder;
COUNT(*)
----------
500000
OK, let ' s go definitively go back to that scn number
SQL> alter database close;
Database altered.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 788524 bytes
Variable Size 150304724 bytes
Database Buffers 54525952 bytes
Redo Buffers 4096000 bytes
Database mounted.
Either we go definitively back with the command below
SQL> alter database open resetlogs;
Database altered.
Either we decide to go to the actual SCN, our database hasn' t been flashbacked definitively, we are recovering the flashback databases with the commands below
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select count(*) from testora10.porder;
COUNT(*)
----------
0
Hence we see the flashback database was recovered up to the current SCN. As long as we do not open the flashbacked database with resetlogs we can undo our flashback operation. This can be interesting, suppose we have truncated a table, we flashback our database, we open it in read only mode, we export the table, we recover the table up to the current SCN and we import the table again.
Flashback database cannot be used in order to flashback through
1. Recreation of controlfile
2. Drop tablespace
3. Shrunk datafile
4. Resetlogs.
