Have a Wetpaint account? Sign in
Query Flashback
1 Query Flashback What it’s for?
· Recovering deleted or incorrectly modified data.
· Compare Current Data with Old Data.
· See historic data.
· Avoid the use of temporal data.
Get the SCN number
EXEC DBMS_FLASHBACK.ENABLE_AT_TIME ( TIMESTAMP '2002-01-01 00:00:00');
Enable the session in flashback mode at approximately that time
EXEC DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(3254658);
Enable the session in flashback mode exactly at that SCN
EXEC DBMS_FLASHBACK.DISABLE;
Disable the flashback mode
SELECT FROM AS OF TIMESTAMP '2002-01-01 00:00:00'
Select the table in flashback mode at approximately that time
SELECT FROM A AS OF SCN 3254658;
Select the table in flashback mode exactly at that SCN
Privilege to execute DBMS_FLASBACK package, if you use that package.
We start creating the table, inserting the values and getting the proper SCN, after every operation.
14:34:07 SQL> CREATE TABLE A ( B VARCHAR2(1))
14:34:23 2 ;
Table created.
14:34:25 SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
3254181
14:57:58 SQL> INSERT INTO A VALUES( '1');
GET_SYSTEM_CHANGE_NUMBER
------------------------
3254652
14:58:22 SQL> INSERT INTO A VALUES( '2');
GET_SYSTEM_CHANGE_NUMBER
------------------------
3254653
14:58:26 SQL> INSERT INTO A VALUES( '3');
GET_SYSTEM_CHANGE_NUMBER
------------------------
3254654
14:58:30 SQL> commit;
Commit complete.
15:08:32 SQL> DELETE FROM A;
3 rows deleted.
15:08:36 SQL> COMMIT;
Commit complete.
If you would like to use the time stamp you could use
SELECT * FROM A AS OF TIMESTAMP TO_TIMESTAMP('2003-01-01 01:00','YYYY-MM-DD HH:MI')
15:08:39 SQL> SELECT * FROM A AS OF SCN 3254658;
B
-
1
2
3
15:08:41 SQL> SELECT * FROM A;
no rows selected
If you would like to use the time stamp you could use
EXEC DBMS_FLASHBACK.ENABLE_AT_TIME(TO_TIMESTAMP('2003-01-01 01:00','YYYY-MM-DD HH:MI'));
But we use the SCN
15:14:27 SQL> EXEC DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(3254658);
PL/SQL procedure successfully completed.
15:15:46 SQL> SELECT * FROM A;
B
-
1
2
3
15:15:52 SQL> EXEC DBMS_FLASHBACK.DISABLE;
PL/SQL procedure successfully completed.
15:15:55 SQL> SELECT * FROM A;
no rows selected
scott@ORA920> column scn new_val scn
scott@ORA920> select dbms_flashback.get_system_change_number scn from dual;
SCN
----------
25585211
scott@ORA920>
scott@ORA920> update emp set ename = lower(ename);
14 rows updated.
scott@ORA920>
scott@ORA920> select a.ename, b.ename
2 from emp a, emp as of scn &scn b
3 where a.empno = b.empno
4 /
old 2: from emp a, emp as of scn &scn b
new 2: from emp a, emp as of scn 25585211 b
ENAME ENAME
---------- ----------
smith SMITH
allen ALLEN
ward WARD
jones JONES
martin MARTIN
blake BLAKE
clark CLARK
scott SCOTT
king KING
turner TURNER
adams ADAMS
james JAMES
ford FORD
miller MILLER
14 rows selected.
The system change number (SCN), a version number for the database that is incremented on every commit.
You can use the function In Oracle 9i
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER
In 8i you can use
USERENV('COMMITSCN')
create or replace procedure save_scn
as
pragma autonomous_transaction/* commit independently*/;
l_scn number;
begin
insert into scn
values (
SYSDATE,
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER
);
commit;
end;
/
SELECT * FROM example AS OF
TIMESTAMP (SYSTIMESTAMP - INTERVAL '120' MINUTE);
When using this technique, remember that daylight savings time and leap years can cause anomalous results. For example, SYSDATE - 1might refer to 23 or 25 hours ago, shortly after a change in daylight savings time.
Using the parameter flashback_scn, flashback_time.
For example
exp system/manager full=y file=flashbacktest.dmp flashback_scn=10002
You can query the table SMON_SCN_TIME
select * from SYS.SMON_SCN_TIME
1 row(s) selected
THREAD TIME_MP TIME_DP SCN_WRP SCN_BAS
---------- ---------- -------------------- ---------- ----------
1 1041971155 7-Ene-2003 16:25:57 0 3385087
1 1041971462 7-Ene-2003 16:31:04 0 3385188
1 1041971770 7-Ene-2003 16:36:12 0 3385289
Not including storage attributes of a table as INITTRANS, MAXTRANS.
This situation creates the ORA-01466 unable to read data - table definition has changed
Only the data if affected by a flashback query. Other information is got from data dictionary, including current character set.
But can use the AS OF clause and perform flashback queries, this not includes V$VIEWS data
SELECT AS OF SCN, ….
CREATE TABLE AS SELECT AS OF SCN….
SELECT AS OF TIMESTAMP…..
CREATE TABLE AS SELECT AS OF TIMESTAMP…..
· Recovering deleted or incorrectly modified data.
· Compare Current Data with Old Data.
· See historic data.
· Avoid the use of temporal data.
1.1 How it works
To flashback in time Oracle only read the needed undo segments and display the information, this isn’t a new technology is the same technology to get a consistent query, to the point the query started.1.2 Syntax
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER RETURN NUMBER;Get the SCN number
EXEC DBMS_FLASHBACK.ENABLE_AT_TIME ( TIMESTAMP '2002-01-01 00:00:00');
Enable the session in flashback mode at approximately that time
EXEC DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(3254658);
Enable the session in flashback mode exactly at that SCN
EXEC DBMS_FLASHBACK.DISABLE;
Disable the flashback mode
SELECT FROM AS OF TIMESTAMP '2002-01-01 00:00:00'
Select the table in flashback mode at approximately that time
SELECT FROM A AS OF SCN 3254658;
Select the table in flashback mode exactly at that SCN
1.3 Database Configuration
Is advisable to set automatic undo management1.4 Performance
1.4.1 Most performant with queries that normally do a small amount of logical IO
Select small sets of data using indexes, rather than queries that require full table scans. If you must do a full table scan, consider adding a parallel hint to the query.1.4.2 Keep statistics current on tables involved in flashback queries
Because it uses the cost-based optimizer.1.5 Privileges
FLASHBACK ANY TABLE to issue a flashback on any table, view or materialized view in any schema. Or FLASHBACK object privilege to specific objects. ( Not needed for DBMS_FLASBACK package).Privilege to execute DBMS_FLASBACK package, if you use that package.
1.6 Examples and techniques
1.6.1 Inserting Data
We start creating the table, inserting the values and getting the proper SCN, after every operation.
14:34:07 SQL> CREATE TABLE A ( B VARCHAR2(1))
14:34:23 2 ;
Table created.
14:34:25 SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
3254181
14:57:58 SQL> INSERT INTO A VALUES( '1');
GET_SYSTEM_CHANGE_NUMBER
------------------------
3254652
14:58:22 SQL> INSERT INTO A VALUES( '2');
GET_SYSTEM_CHANGE_NUMBER
------------------------
3254653
14:58:26 SQL> INSERT INTO A VALUES( '3');
GET_SYSTEM_CHANGE_NUMBER
------------------------
3254654
14:58:30 SQL> commit;
Commit complete.
15:08:32 SQL> DELETE FROM A;
3 rows deleted.
15:08:36 SQL> COMMIT;
Commit complete.
1.6.2 Using SELECT AS OF
After we delete the data, we still can see the dataIf you would like to use the time stamp you could use
SELECT * FROM A AS OF TIMESTAMP TO_TIMESTAMP('2003-01-01 01:00','YYYY-MM-DD HH:MI')
15:08:39 SQL> SELECT * FROM A AS OF SCN 3254658;
B
-
1
2
3
15:08:41 SQL> SELECT * FROM A;
no rows selected
1.6.3 Using DBMS_FLASBACK package
If you would like to use the time stamp you could use
EXEC DBMS_FLASHBACK.ENABLE_AT_TIME(TO_TIMESTAMP('2003-01-01 01:00','YYYY-MM-DD HH:MI'));
But we use the SCN
15:14:27 SQL> EXEC DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(3254658);
PL/SQL procedure successfully completed.
15:15:46 SQL> SELECT * FROM A;
B
-
1
2
3
15:15:52 SQL> EXEC DBMS_FLASHBACK.DISABLE;
PL/SQL procedure successfully completed.
15:15:55 SQL> SELECT * FROM A;
no rows selected
1.6.4 Use SCN to be more precise
You don’t need to wait 5 minutes.scott@ORA920> column scn new_val scn
scott@ORA920> select dbms_flashback.get_system_change_number scn from dual;
SCN
----------
25585211
scott@ORA920>
scott@ORA920> update emp set ename = lower(ename);
14 rows updated.
scott@ORA920>
scott@ORA920> select a.ename, b.ename
2 from emp a, emp as of scn &scn b
3 where a.empno = b.empno
4 /
old 2: from emp a, emp as of scn &scn b
new 2: from emp a, emp as of scn 25585211 b
ENAME ENAME
---------- ----------
smith SMITH
allen ALLEN
ward WARD
jones JONES
martin MARTIN
blake BLAKE
clark CLARK
scott SCOTT
king KING
turner TURNER
adams ADAMS
james JAMES
ford FORD
miller MILLER
14 rows selected.
1.6.5 Function to save SCN data
To get precision in your flashback queries is better to save the SCN number periodically.The system change number (SCN), a version number for the database that is incremented on every commit.
You can use the function In Oracle 9i
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER
In 8i you can use
USERENV('COMMITSCN')
create or replace procedure save_scn
as
pragma autonomous_transaction/* commit independently*/;
l_scn number;
begin
insert into scn
values (
SYSDATE,
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER
);
commit;
end;
/
1.6.6 Create a view or a function to get old data
CREATE VIEW vew_example ASSELECT * FROM example AS OF
TIMESTAMP (SYSTIMESTAMP - INTERVAL '120' MINUTE);
When using this technique, remember that daylight savings time and leap years can cause anomalous results. For example, SYSDATE - 1might refer to 23 or 25 hours ago, shortly after a change in daylight savings time.
1.6.7 Exporting using flashback option
You can export using flashback query:Using the parameter flashback_scn, flashback_time.
For example
exp system/manager full=y file=flashbacktest.dmp flashback_scn=10002
1.6.8 How to get the date time for a specific SCN or vice versa
It only tracks the last five days and only in five minute increments.You can query the table SMON_SCN_TIME
select * from SYS.SMON_SCN_TIME
1 row(s) selected
THREAD TIME_MP TIME_DP SCN_WRP SCN_BAS
---------- ---------- -------------------- ---------- ----------
1 1041971155 7-Ene-2003 16:25:57 0 3385087
1 1041971462 7-Ene-2003 16:31:04 0 3385188
1 1041971770 7-Ene-2003 16:36:12 0 3385289
1.7 Restrictions and Errors
1.7.1 CTAS your data as fast as you can
The period of UNDO retention is not guaranteed (unless you do so in 10gR2 and upward) This means if some operation requires the space, it will overwrite your previous data, the advice is to do a CREATE TABLE AS SELECT AS OF, as sonn as you can, to guarantee not to lose that information.1.7.2 Flashback is session-specific
You enable it only for your session.1.7.3 Automatic Undo Management
The flashback query mechanism is most effective when you use automatic undo management.1.7.4 Use SCN for precision when gathering data
Use SCN instead of time to be more precise.1.7.5 Undo Data Invalidation
DDL statements like drop, modify, move, truncate invalidates undo data.Not including storage attributes of a table as INITTRANS, MAXTRANS.
This situation creates the ORA-01466 unable to read data - table definition has changed
Only the data if affected by a flashback query. Other information is got from data dictionary, including current character set.
1.7.6 Materialized Views
Flashback queries against materialized views do not take advantage of query rewrite.1.7.7 SYS User
Cannot make calls to the DBMS_FLASHBACK package.But can use the AS OF clause and perform flashback queries, this not includes V$VIEWS data
1.7.8 Database links
Cannot perform flashback queries on remote tables through database links1.7.9 Cannot nest flashback calls
You must disable it before enabling it to a different time.1.8 Oracle dictionary
1.9 Other Tools
1.10 Features by Release
1.10.1 9.0.1
Introduced.1.10.2 9.2.0
You can flashback without using the DBMS_FLASHBACK package.SELECT AS OF SCN, ….
CREATE TABLE AS SELECT AS OF SCN….
SELECT AS OF TIMESTAMP…..
CREATE TABLE AS SELECT AS OF TIMESTAMP…..
1.10.3 10.1
· Perform remote queries of past data.1.10.4 10.2
Automatic Undo Retention Enhancement1.11 Bugs by Release
juancarlosreyesp |
Latest page update: made by juancarlosreyesp
, Sep 9 2008, 8:11 PM EDT
(about this update
About This Update
Edited by juancarlosreyesp
1132 words added view changes - complete history) |
|
Keyword tags:
Database Recover
More Info: links to this page
|
There are no threads for this page.
Be the first to start a new thread.

