- overview of different flashback technologies
- flashback query(including flashback query, flashback version query , flashback transaction, flashback table)
- flashback drop
- flashback database
- flashback data archive
overview of different flashback technologies
Firstly this chapter is writen based on oracle 11g 11.0.2.3. Ok. Flashback technology in oracle 11g including four different kinds of technologies. They are falshback database，falshback drop, falshback query(which including four variations ——flashback query,flashback version query,flashback transaction, flashback table) and flashback data archive. Flashback data archive is a new feature in 11g.
Firstly we see the flashback query. Flashback query is based on the undo segments. With flashback query we can query the database as if it was at some time in the past. We can select all versions of a row over a period of time, to show a history of what has happened to the row, when it happened, who did it, and the identifiers of the transactions that made each change. All these informations comes from the undo. Once you use the flashback query identifiy which transaction made those changes, you can use the flashback transaction feature to construct another transaction to reverse those changes. We can say the flashback transaction is a variation of flashback query. The other variation of flashback query is flashback table. With flashback table feature, you can specify some table to make them recover to a certain point of time.
Now we know if we did some logical error like wrongly delete,update or insert some values and commited. We can use the flashback query (and it`s different variations) to easily recover. As long as the undo is exist. But what if we accidently drop the table? DDL is no a transaction. It won`t have before image in the undo segment. So you can not use flashback to recover a drop table statement. Here we need the flashback drop feature. flashback drop can revert the drop table statement. the corresponding index, permissions are also restored.
The wrong dml we can use flashback query to restore. The wrongly drop table can be restored by flashback drop. What if we wrongly drop a schema? We can use flashback database. Flashback database is a faster version of incomplete recovery. So whenever you want use incomplete recover, you can use flashback recovery.
Flashback data archive is a new feature in 11g. The flashback query is based on undo, flashback drop is possible only if the tablespace disk space is not reused, flashback database is based on flasback log. But with proper configuration, flashback data archive can restore data years ago.
flashback query
Flashback query has 4 variations.
- Basic flashback query
- flashback version query
- flashback transaction
- flashback table.
Basic flashback query do not need any configuration. here is an example for basic flashback query.
SQL> select to_char(sysdate,'dd-mm-yy hh24:mi:ss') as time from dual; TIME ----------------- 04-09-13 13:28:13 SQL> select * from test where empno=7844; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 SQL> update test set sal=10000 where empno=7844; 1 row updated. SQL> commit; Commit complete. SQL> select * from test where empno=7844; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7844 TURNER SALESMAN 7698 08-SEP-81 10000 0 30
Here we can see the salary for empno=7844 is 1500 at 13:28:13. Then we did an update make the sal=10000 and commit. Now we are going to use basic flashback to check what is the row looks like in the past time which is 13:28:13.
SQL> select * from test as of timestamp to_timestamp('04-09-13 13:28:13','dd-mm-yy hh24:mi:ss') where empno=7844 ; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
We can see the sal is 1500. So we managed to find what the value looks like in the past time.
We can even set the whole session back in to some point of time. Here is another example:
SQL> exec dbms_flashback.enable_at_time(to_timestamp('04-09-13 13:28:13','dd-mm-yy hh24:mi:ss')); PL/SQL procedure successfully completed. SQL> select * from test where empno=7844; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
Flashback query can not work in sys schema, also you can not do any dml if you use the dbms_flashback to set the session is some old point of time. If you use scn the point to which the flashback goes is precise, if you use a time, then it will be mapped to a scn with 3 seconds precision.
Flashback version query is an enhanced feature. A row may be updated many times,Flashback Versions Query lets you see all the committed versions of a row (but not any uncommitted versions), including the timestamps for when each version was created and when it ended.You can also see the transaction identifier of the transaction that created any given version of a row, which can then be used with Flashback Transaction Query. This information is exposed by a number of pseudocolumns that are available with every table. The pseudocolumns relevant to flashback are
• VERSIONS_STARTSCN The SCN at which this version of the row was created, either by INSERT or by UPDATE
• VERSIONS_STARTTIME The timestamp at which this version of the row was created
• VERSIONS_ENDSCN The SCN at which this version of the row expired, because of either DELETE or UPDATE
• VERSIONS_ENDTIME The timestamp at which this version of the row expired
• VERSIONS_XID The unique identifier for the transaction that created this version of the row
• VERSIONS_OPERATIONS The operation done by the transaction to create this version of the row, either INSERT or UPDATE or DELETE
To see these pseudocolumns, you must include the VERSIONS BETWEEN keywords in your query. For example:
We did a few more dml on the table test where empno=7844
SQL> update test set sal=2000 where empno=7844; 1 row updated. SQL> commit; Commit complete. SQL> delete from test where empno=7844; 1 row deleted. SQL> commit; Commit complete.
These operation will be traced with the flashback version query.
SQL> select empno,sal,versions_xid,versions_starttime,versions_endtime,versions_operation from test versions between scn minvalue and maxvalue 2 where empno=7844; EMPNO SAL VERSIONS_XID VERSIONS_STARTTIME VERSIONS_ENDTIME V ---------- ---------- ---------------- ------------------------------------------------ ------------------------------------------------ - 7844 2000 04001F001C1C0000 04-SEP-13 02.27.21 PM D 7844 2000 07000200E91C0000 04-SEP-13 02.26.39 PM 04-SEP-13 02.27.21 PM U 7844 10000 04-SEP-13 02.26.39 PM
Here we use two constants for the SCN. MINVALUE instructs Oracle to retrieve the earliest information in the undo segments; MAXVALUE will be the current SCN. We can also use timestamp as below.
SQL> create table test as select * from emp; Table created. SQL> select * from test where empno=7844; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as time from dual; TIME ------------------- 2013-09-04 14:53:34 SQL> update test set sal=10000 where empno=7844; 1 row updated. SQL> commit; Commit complete. SQL> update test set sal=20000 where empno=7844; 1 row updated. SQL> commit; Commit complete. SQL> delete from test where empno=7844; 1 row deleted. SQL> commit; Commit complete. SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as time from dual; TIME ------------------- 2013-09-04 14:54:34 SQL> select empno,sal,versions_xid,versions_starttime,versions_endtime,versions_operation 2 from test versions between scn minvalue and maxvalue 3 where empno=7844; EMPNO SAL VERSIONS_XID VERSIONS_STARTTIME VERSIONS_ENDTIME V ---------- ---------- ---------------- ------------------------------------------------ ------------------------------------------------ - 7844 20000 04001500161C0000 04-SEP-13 02.54.25 PM D 7844 20000 03002000E71C0000 04-SEP-13 02.54.07 PM 04-SEP-13 02.54.25 PM U 7844 10000 05000B00B11C0000 04-SEP-13 02.53.55 PM 04-SEP-13 02.54.07 PM U 7844 1500 04-SEP-13 02.53.55 PM SQL> select empno,sal,versions_xid,versions_starttime,versions_endtime,versions_operation 2 from test versions between timestamp to_timestamp('04-09-13 14:53:34','dd-mm-yy hh24:mi:ss') and to_timestamp('04-09-13 14:54:34','dd-mm-yy hh24:mi:ss') 3 where empno=7844; EMPNO SAL VERSIONS_XID VERSIONS_STARTTIME VERSIONS_ENDTIME V ---------- ---------- ---------------- ------------------------------------------------ ------------------------------------------------ - 7844 20000 04001500161C0000 04-SEP-13 02.54.25 PM D 7844 20000 03002000E71C0000 04-SEP-13 02.54.07 PM 04-SEP-13 02.54.25 PM U 7844 10000 05000B00B11C0000 04-SEP-13 02.53.55 PM 04-SEP-13 02.54.07 PM U 7844 1500 04-SEP-13 02.53.55 PM
Flashback Version Query cannot work against external tables, temporary tables, or V$ views. Why not? Because none of these objects generates undo.
Flashback transaction include two very useful feature. Check the past transactions (you need to enable supplemental logging for this feature)and revert the commited transactions(you need to enable the supplemental logging and archive log). To check the past transactions information you need to check the view flashback_transaction_query.
SQL> desc flashback_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)
Because this view contains important information, you need to be granted select any transaction privilege to check it. There will be one or more rows in this view for every transaction whose undo data still exists in the undo segments, and every row will refer to one row affected by the transaction. The table that follows describes the columns.
- XID The transaction identifier. This is the join column to the pseudocolumn
- VERSIONS_XID displayed in a Flashback Versions Query
- START_SCN The system change number at the time the transaction started
- START_TIMESTAMP The timestamp at the time the transaction started
- COMMIT_SCN The system change number at the time the transaction was committed
- COMMIT_TIMESTAMP The timestamp at the time the transaction was committed
- LOGON_USER The Oracle username of the session that performed the transaction
- UNDO_CHANGE# The undo system change number. This is not likely to be relevant to most work
- OPERATION The DML operation applied to the row: INSERT, UPDATE, or DELETE
- TABLE_NAME The table to which the row belongs
- TABLE_OWNER The schema to which the table belongs
- ROW_ID The unique identifier of the row affected
- UNDO_SQL A constructed statement that will reverse the operation. For example, if the OPERATION were a DELETE, then this will be an INSERT
The view will show committed transactions and also transactions in progress. For an active transaction, the COMMIT_SCN and COMMIT_TIMESTAMP columns are NULL.
Let`s see an example.
SQL> update test set ename='KRAMER' where empno=7566; 1 row updated. SQL> COMMIT; Commit complete. SQL> update test set sal=99999 where empno=7566; 1 row updated. SQL> commit; Commit complete.
We have run two transactions. Then we use flashback version query to see the xid.
SQL> select empno,ename,sal,versions_xid from test versions between scn minvalue and maxvalue where empno=7566; EMPNO ENAME SAL VERSIONS_XID ---------- ---------- ---------- ---------------- 7566 KRAMER 99999 02002100D71C0000 7566 KRAMER 2975 03001200EF1C0000 7566 JONES 2975
Then we use the xid to check the view flashback_transaction_query. But here we have one thing to notice. The xid in flashback_transaction_query is raw type but the versions_xid is hexadecimal. So we have to use type casting function.
SQL> select xid,undo_sql from flashback_transaction_query where xid=hextoraw('02002100D71C0000'); XID UNDO_SQL ---------------- ------------------------------------------------------------------------------------ 02002100D71C0000 02002100D71C0000
oh.. nothing found. I dont know where i did wrong so I went to the internet did a few search. It is said we have to enable supplemental logging..
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Database altered.
SQL> update test set ename='kramer' where empno=7566; 1 row updated. SQL> commit; Commit complete. SQL> update test set sal=10000 where empno=7566; 1 row updated. SQL> commit; Commit complete.
Then check the transaction id again
SQL> select empno,ename,sal,versions_xid from test versions between scn minvalue and maxvalue where empno=7566; EMPNO ENAME SAL VERSIONS_XID ---------- ---------- ---------- ---------------- 7566 kramer 10000 09000F00C31C0000 7566 kramer 99999 08002100C01C0000 7566 KRAMER 99999 04000100281C0000 7566 KRAMER 2975 06001000C31C0000 7566 JONES 2975
check the view flashback_transaction_query
SQL> set linesize 180 SQL> select xid,undo_sql from flashback_transaction_query where xid=hextoraw('08002100C01C0000'); XID UNDO_SQL ---------------- ------------------------------------------------------------------------------------ 08002100C01C0000 update "SCOTT"."TEST" set "ENAME" = 'KRAMER' where ROWID = 'AAAVXPAAEAAAAIbAAD'; 08002100C01C0000 SQL> select xid,undo_sql from flashback_transaction_query where xid=hextoraw('06001000C31C0000'); XID UNDO_SQL ---------------- ------------------------------------------------------------------------------------ 06001000C31C0000 06001000C31C0000
This time we succeed. Also note that the transaction before enable supplemental logging still can not check undo_sql.
Besides check the transaction information, we can also revert the transaction with dbms_flashback package as below.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Database altered. SQL> conn scott Enter password: Connected. SQL> drop table test; Table dropped. SQL> create table test as select * from emp; Table created. SQL> set linesize 180 SQL> update test set ename='kramer' where empno=7844; 1 row updated. SQL> commit; Commit complete. SQL> update test set sal=99999 where ename='kramer'; 1 row updated. SQL> commit; Commit complete. SQL> select empno,ename,sal,versions_xid from test versions between scn minvalue and maxvalue where versions_xid is not null; EMPNO ENAME SAL VERSIONS_XID ---------- ---------- ---------- ---------------- 7844 kramer 99999 08001900C21C0000 7844 kramer 1500 02000100D41C0000 SQL> quit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options [oracle@racnode1 nfsdg1]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 4 16:37:47 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> execute dbms_flashback.transaction_backout(numtxns=>2,xids=>sys.xid_array('08001900C21C0000','02000100D41C0000'),options=>dbms_flashback.cascade); BEGIN dbms_flashback.transaction_backout(numtxns=>2,xids=>sys.xid_array('08001900C21C0000','02000100D41C0000'),options=>dbms_flashback.cascade); END; * ERROR at line 1: ORA-55510: Mining could not start ORA-06512: at "SYS.DBMS_FLASHBACK", line 37 ORA-06512: at "SYS.DBMS_FLASHBACK", line 70 ORA-06512: at line 1
Check through the internet found below infor
// *Cause: Mining could not start for the following reasons.
// 1. A logminer session was processing
// 2. The database was not mounted or not opened for read and write
// 3. Minimum supplemental logging was not enabled
// 4. Archiving was not enabled
// *Action: Fix the mentioned problems and try again. Note that if
// you enable supplemental logging now, you will not be able to
// remove a transaction that has committed without supplemental
// logging.
It seems i need to enable archive log. Ok I will do another test
[oracle@racnode1 nfsdg1]$ srvctl stop database -d orcl [oracle@racnode1 nfsdg1]$ srvctl start database -d orcl -o mount [oracle@racnode1 nfsdg1]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 4 16:51:25 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> drop table test; Table dropped. SQL> create table test as select * from emp; Table created. SQL> update test set ename='kramer' where empno=7844; 1 row updated. SQL> commit; Commit complete. SQL> update test set sal=99999 where ename='kramer'; 1 row updated. SQL> commit; Commit complete. SQL> select empno,ename,sal,versions_xid from test versions between scn minvalue and maxvalue where versions_xid is not null; EMPNO ENAME SAL VERSIONS_XID ---------- ---------- ---------- ---------------- 7844 kramer 99999 05000200B31C0000 7844 kramer 1500 07001B00F61C0000 SQL> quit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options [oracle@racnode1 nfsdg1]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 4 16:55:10 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> execute dbms_flashback.transaction_backout(numtxns=>2,xids=>sys.xid_array('05000200B31C0000','07001B00F61C0000'),options=>dbms_flashback.cascade); PL/SQL procedure successfully completed.
This time we succeed. So we need supplemental logging to select the flashback_transaction_query. We need the archive log mode and supplemental logging to run dbms_flashback.transaction_backout. You also need to notice that after you run the procedure, the reversed transaction is in an uncommited state. You need to commit it if you want the reverse take effect.
After talking about flashback query, flashback version query, flashback transaction, let`s see the flashback table. This feature seems make you be able to make a table go back to some old state. But actually it is also using some transaction. The flashback table is actually using some transaction to modify the table. So if the tables defination is changed you can not use flashback table feature. Because DDL is not transaction. Before using flashback table, you need to enable row movement for the table.
Also there is an more complicated situation in flashback table. Think in this case. We have two tables parent and child.
SQL> create table parent(id number); Table created. SQL> alter table parent add constraint parent_pk primary key(id); Table altered. SQL> insert into parent values(1); 1 row created. SQL> insert into parent values(2); 1 row created. SQL> commit; Commit complete. SQL> create table child(id number ,constraint child_fk foreign key(id) references parent(id)); Table created. SQL> insert into child values(1); 1 row created. SQL> insert into child values(2); 1 row created. SQL> commit; Commit complete.
Now we have two normal transactions. First modify the child, then modify the parent.
SQL> delete from child ; 2 rows deleted. SQL> commit; Commit complete. SQL> delete from parent; 2 rows deleted. SQL> commit; Commit complete.
Now if you use flashback table to flashback child first.
SQL> flashback table child to timestamp to_timestamp('05-09-13 14:10:00','dd-mm-yy hh24:mi:ss' ) ; flashback table child to timestamp to_timestamp('05-09-13 14:10:00','dd-mm-yy hh24:mi:ss' ) * ERROR at line 1: ORA-02091: transaction rolled back ORA-02291: integrity constraint (CITOSADMIN.CHILD_FK) violated - parent key not found
Then you hit constraint issue because the parent do not have data yet.
But you can flashback them togather, oracle will resolve the dependency.
SQL> flashback table child,parent to timestamp to_timestamp('05-09-13 14:10:00','dd-mm-yy hh24:mi:ss' ) ; Flashback complete.
Note that in flashback table, the transaction oracle use to flashback the table is like normal transactions, the only different is that the trigger will not be triggered.
But you can enable the trigger with this syntax.
SQL> flashback table emp,dept to scn 6539425 enable triggers;
flashback drop
You may accidently drop a table. Flashback drop is the mechanism can help us to rescue this situation. But before this happen, will introduce another skill can help you to avoid this kind of error. When you accidently drop some thing, it`s usually because you do not know where you are. You might thought you are in some test enviroment. There is one way to aovid this.
SQL> set sqlprompt "_user'@'_connect_idnetifier>" user@_connect_idnetifier>
The above command change the SQL prompt which can remmind you where you are. You can also put this command in to the gloign.sql which in ORACLE_HOME/sqlplus/admin directory. Then whenever you login, the command will run automatically.
Now we focuse on the flashback drop. In oracle the dorp command actually do the following things. Rename the table to some system generated names.
Then nothing else. The space taken by the table still exist but can be used by other objects. If these space not being alllocated to other objects, then you can flashback the table anytime. Everything on the table is flashbacked too except the foreign key constraints. But you need to konw that the disk space taken by oracle will not trigger space usage alert. To oracle they are free space.
Here is an example for flashback drop.
SQL> drop table child; Table dropped. SQL> drop table parent; Table dropped. SQL> flashback table child to before drop rename to new; Flashback complete. SQL> select * from new; ID ---------- 1 2
As you can see, we esalily flashback the child table. The foreign key constraint is not a problem.
Note that tables under system tablespace can not be flashbacked. If you dorp them then they will be purdge immediately.
flashback database
Flashback database architecture. Once Flashback Database is enabled, images of altered blocks are copied from time to time from the database buffer cache to a new memory area within the SGA, the flashback buffer. This flashback buffer is flushed to disk, to the flashback logs, by a new background process: the Recovery Writer, or RVWR. There is no change to the usual routine of writing changes to the log buffer, which the LGWR then flushes to disk; flashback logging is additional to this. Unlike the redo log, flashback logging is not a log of changes—it is a log of complete block images.
Critical to performance is that not every change is copied to the flashback buffer— only a subset of changes. If all changes to all blocks were copied to the buffer, then the overhead in terms of memory usage and the amount of extra disk I/O required to flush the buffer to disk would be crippling for performance. Internal algorithms limit which versions of which blocks are placed in the flashback buffer, in order to restrict its size and the frequency with which it will fill and be written to disk. These algorithms are intended to ensure that there will be no negative performance hit when enabling Flashback Database: they guarantee that even very busy blocks are logged only infrequently.
When conducting a database flashback, Oracle will read the flashback logs to extract the versions of each changed database block, and copy these versions back into the datafiles. As these changes are applied to the current database in reverse chronological order, this has the effect of taking the database back in time, by reversing the writes that the DBWn process has done.
But since not every datablock change version is saved in the flashback log. For example, the datablock is changed 8 times but only 4 version is saved. So you will not be able to flashback the table to a precise point of time. So you will flashback the table to a previsoue point of time, then use incomplete finnish the rest.
So Flashback Database is in fact a combination of several processes and data structures. First, you must allocate some memory in the SGA (which will be automatic— you cannot control how large the buffer is) and some space on disk to store the flashback data, and start the RVWR process to enable flashback logging. When doing a flashback, Oracle will use the flashback logs to take the database back in time to before the time you want, and then apply redo logs (using whatever archive redo log files and online redo log files are necessary) in the usual fashion for incomplete recovery to bring the datafiles forward to the exact time you want. Then the database can be opened with a new incarnation, in the same manner as following a normal incomplete recovery.
Below are the steps of configure flashback database
1. Ensure that the database is in archivelog mode. Archive log mode is a prerequisite for enabling Flashback Database. Confirm this by querying the V$DATABASE view: SQL> select log_mode from v$database; 2. Set up a flash recovery area. The flash recovery area is the location for the flashback logs. You have no control over them other than setting the flash recovery area directory and limiting its size. It is controlled with two instance parameters: DB_ RECOVERY_FILE_DEST specifies the destination directory and DB_RECOVERY_ FILE_DEST_SIZE restricts the maximum amount of space in bytes that it can take up. Remember that the flash recovery area is used for purposes other than flashback logs, and it will need to be sized appropriately. For example, SQL> alter system set db_recovery_file_dest='/flash_recovery_area'; SQL> alter system set db_recovery_file_dest_size=8G; 3. Set the flashback retention target. This is controlled by the DB_FLASHBACK_RETENTION_TARGET instance parameter, which is in minutes, and the default is one day. The flashback log space is reused in a circular fashion, older data being overwritten by newer data. This parameter instructs Oracle to keep flashback data for a certain number of minutes before overwriting it: SQL> alter system set db_flashback_retention_target=240; It is only a target (four hours in the preceding example) and if the flash recovery area is undersized, Oracle may not be able to keep to it. But in principle, you should be able to flash back to any time within this target. 4. Cleanly shut down and mount the database. SQL> shutdown immediate; SQL> startup mount; 5. Enable flashback logging. While in mount mode, SQL> alter database flashback on; will start the RVWR process and allocate a flashback buffer in the SGA. The process startup will be automatic from now on. 6. Open the database. SQL> alter database open; Logging of data block images from the database buffer cache to the flashback buffer will be enabled from now on.
The use is very simple
SQL> shutdown abort; SQL> startup mount; SQL> flashback database to timestamp to_timestamp('20-12-08 10:01:00','dd-mm-yy hh24:mi:ss'); SQL> alter database open read only;