1. Recover the lost data or undoing incorrect commit.
2. Comparing the current data with corresponding data in the past.
Let us talk about below three topics on this article. The below code in this thread is successfully tested in oracle10gR2.
1. Flashback query(Oracle9i feature)
2. Flashback version query(Oracle10g feature)
3. Flashback transaction query(Oracle10g feature)
Prerequisite to use Flash back query freature..
1. Automatic UNDO managment should be enabled. The following init parameter should be set.
UNDO_MANAGEMENT = AUTO
UNDO_TABLESPACE = undotablespace_name
UNDO_RETENTION = n
2. We need to grant below privilege to schema where we are using this feature. Let us assume, we are using scott schema.
grant execute on dbms_flashback to scott;
Flashback Query(Oracle9i feature)
Flashback query can be enabled or disabled by using dbms_flashback package. We can flashback by using SCN or specific time. Flashback query feature will allow users to see the data on specific time or SCN in the past.
Scenario 1
Let us use the timestamp and display the past data...
scott@orcl> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
scott@orcl> create table flashback as select * from user_objects
2 where rownum <6;
Table created.
scott@orcl> select object_name from flashback;
OBJECT_NAME
--------------------------------------------------------------------------------
AUDITLOG
AUDIT_SEQ
CUSTOMERS_SEQ
DEPT
DEPT1
10:58:59 scott@orcl> update flashback set object_name=lower(object_name);
5 rows updated.
10:59:14 scott@orcl> commit;
Commit complete.
10:59:17 scott@orcl> select object_name from flashback;
OBJECT_NAME
--------------------------------------------------------------------------------
auditlog
audit_seq
customers_seq
dept
dept1
scott@orcl> SELECT OBJECT_NAME from flashback AS OF
2 TIMESTAMP TO_TIMESTAMP('06-MAY-2009 10:58:26');
OBJECT_NAME
--------------------------------------------------------------------------------
AUDITLOG
AUDIT_SEQ
CUSTOMERS_SEQ
DEPT
DEPT1
scott@orcl>
Scenario 2
Recovering data based on specific SCN in the past...
scott@orcl> select current_scn from v$database;
CURRENT_SCN
-----------
19215382
scott@orcl> create table flashback as select * from user_objects
2 where rownum <6;
Table created.
scott@orcl> select current_scn from v$database;
CURRENT_SCN
-----------
19215408
scott@orcl> select * from flashback;
OBJECT_NAME
--------------------------------------------------------------------------------
AUDITLOG
AUDIT_SEQ
BIN$IDl5ashFQMqE8ctrWU8K8Q==$0
BIN$dld0GhwqRlO4sMnrYyAKgQ==$0
CUSTOMERS_SEQ
scott@orcl> update flashback set object_name=lower(object_name);
5 rows updated.
scott@orcl> commit;
Commit complete.
scott@orcl> select * from flashback as of scn 19215382;
select * from flashback as of scn 19215382
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
scott@orcl> select * from flashback as of scn 19215408;
OBJECT_NAME
--------------------------------------------------------------------------------
AUDITLOG
AUDIT_SEQ
BIN$IDl5ashFQMqE8ctrWU8K8Q==$0
BIN$dld0GhwqRlO4sMnrYyAKgQ==$0
CUSTOMERS_SEQ
scott@orcl> select * from flashback;
OBJECT_NAME
--------------------------------------------------------------------------------
auditlog
audit_seq
bin$idl5ashfqmqe8ctrwu8k8q==$0
bin$dld0ghwqrlo4smnryyakgq==$0
customers_seq
scott@orcl>
Note : when we enable flashback query, we provide either a timestamp or SCN. Timestamp is mapped to an SCN number every five minutes, the SCN offers a much finer level of precision for flashback.
Scenario 3
We can use the explicit cursor to read the past data by using flashback query feature.
scott@orcl> create table flashback as select * from user_objects
2 where rownum <11;
Table created.
scott@orcl> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN
-----------
TO_CHAR(SYSTIMESTAMP,'YYYY-MM-
---------------------------------------------------------------------------
19218732
2009-05-06 12:15:58
scott@orcl>
11:36:52 scott@orcl> delete flashback;
10 rows deleted.
11:37:10 scott@orcl> commit;
Commit complete.
11:37:11 scott@orcl>
I deleted the flashback table around 11.37AM. Now i wanted to revert the data through PLSQL cursor by using SCN or TIMESTAMP.
The below example reading through Timestamp.
scott@orcl> declare
cursor c1 is select * from flashback;
emprec c1%rowtype;
begin
dbms_flashback.enable_at_time('06-MAY-2009 11:36:52');
OPEN C1;
DBMS_FLASHBACK.DISABLE;
LOOP
FETCH c1 into emprec;
exit when c1%notfound;
insert into flashback(OBJECT_NAME)
values(emprec.object_name);
end loop;
close c1;
commit;
end;
/
PL/SQL procedure successfully completed.
scott@orcl> SELECT * FROM FLASHBACK;
OBJECT_NAME
--------------------------------------------------------------------------------
AUDITLOG
AUDIT_SEQ
BIN$EkjOJm2AS66HXzkrAueJiA==$0
BIN$IDl5ashFQMqE8ctrWU8K8Q==$0
BIN$YUw9dPZ5SueBF5RDCMLJIQ==$0
BIN$dld0GhwqRlO4sMnrYyAKgQ==$0
CUSTOMERS_SEQ
DEPT
DEPT1
DO_SOMETHING
10 rows selected.
scott@orcl>
The below example reading flashback data through SCN.
scott@orcl> declare
cursor c1 is select * from flashback;
emprec c1%rowtype;
begin
dbms_flashback.Enable_At_System_Change_Number(19218732);
OPEN C1;
DBMS_FLASHBACK.DISABLE;
LOOP
FETCH c1 into emprec;
exit when c1%notfound;
insert into flashback(OBJECT_NAME)
values(emprec.object_name);
end loop;
close c1;
commit;
end;
/
PL/SQL procedure successfully completed.
scott@orcl> select * from flashback;
OBJECT_NAME
--------------------------------------------------------------------------------
AUDITLOG
AUDIT_SEQ
BIN$EkjOJm2AS66HXzkrAueJiA==$0
BIN$IDl5ashFQMqE8ctrWU8K8Q==$0
BIN$YUw9dPZ5SueBF5RDCMLJIQ==$0
BIN$dld0GhwqRlO4sMnrYyAKgQ==$0
CUSTOMERS_SEQ
DEPT
DEPT1
DO_SOMETHING
10 rows selected.
scott@orcl>
Note: We can not do any operation in the database when database is in Flashback mode.
scott@orcl> declare
cursor c1 is select * from flashback;
emprec c1%rowtype;
begin
dbms_flashback.Enable_At_System_Change_Number(19218732);
OPEN C1;
--DBMS_FLASHBACK.DISABLE;
LOOP
FETCH c1 into emprec;
exit when c1%notfound;
insert into flashback(OBJECT_NAME)
values(emprec.object_name);
end loop;
close c1;
commit;
end;
/
declare
*
ERROR at line 1:
ORA-08182: operation not supported while in Flashback mode
ORA-06512: at line 11
So far, we have seen oracle9i features. Oracle9i has ability to read the past data by using SCN or timestamp. But oracle10g has gone one step further and has ability to read the past data for specific time window. This feature is called as flashback version query. Also oracle10g can get extra information about the transactions listed by flashback version queries.
As a summary, oracle10g introduced two new features on top of flashback query (which is introduced in oracle9i).
1. Flashback version query
2. Flashback transaction query
Flashback Version query (Oracle10g feature)
Flashback version query display the past data on specific time window. This feature helps to find the detail level data changes during that window. Here i created table and apply three transaction. At the end, we are able to display all the changes made by the transaction during that time period.
scott@orcl> create table flashversion(name varchar2(20));
Table created.
scott@orcl> SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
TO_CHAR(SYSTIMESTAMP,'YYYY-MM-
---------------------------------------------------------------------------
2009-05-08 09:07:59
scott@orcl> insert into flashversion values('SCOTT');
1 row created.
scott@orcl> commit;
Commit complete.
scott@orcl> update flashversion set name='MANI';
1 row updated.
scott@orcl> commit;
Commit complete.
scott@orcl> update flashversion set name='JANI';
1 row updated.
scott@orcl> commit;
Commit complete.
scott@orcl> SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
TO_CHAR(SYSTIMESTAMP,'YYYY-MM-
---------------------------------------------------------------------------
2009-05-08 09:09:22
scott@orcl> SELECT versions_starttime,
2 name
3 FROM flashversion
4 VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2009-05-08 09:07:59', 'YYYY-MM-DD HH24:MI:SS')
5 AND TO_TIMESTAMP('2009-05-08 09:09:22', 'YYYY-MM-DD HH24:MI:SS');
VERSIONS_STARTTIME
---------------------------------------------------------------------------
NAME
--------------------
08-MAY-09 09.09.15 AM
JANI
08-MAY-09 09.08.41 AM
MANI
08-MAY-09 09.08.07 AM
SCOTT
We can use the below flashback pseudo columns with above query.
VERSIONS_STARTSCN => Starting SCN when row took on this value
VERSIONS_STARTTIME => Starting TIMESTAMP when row took on this value
Note : The value of NULL is returned if the row was created before the lower bound SCN or TIMESTAMP.
VERSIONS_ENDSCN => Ending SCN when row last contained this value
VERSIONS_ENDTIME => Ending TIMESTAMP when row last contained this value
Note : The value of NULL is returned if the value of the row is still current at the upper bound SCN or TIMESTAMP.
VERSIONS_XID => ID of the transaction that created the row in it's current state.
VERSIONS_OPERATION => Operation performed by the transaction ((I)nsert, (U)pdate or (D)elete)
Here is the query with all the pseudo column
SELECT
versions_startscn,
versions_starttime,
versions_endscn,
versions_endtime,
versions_xid,
versions_operation,
name
FROM flashversion
VERSIONS BETWEEN
TIMESTAMP TO_TIMESTAMP('2009-05-08 09:07:59', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2009-05-08 09:09:22', 'YYYY-MM-DD HH24:MI:SS');
/
Flashback Transaction Query(Oracle10g feature)
Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries. The VERSIONS_XID column values from a flashback version query can be used to query the FLASHBACK_TRANSACTION_QUERY view like:
scott@orcl> SELECT versions_xid,name
2 name
3 FROM flashversion
4 VERSIONS BETWEEN
5 TIMESTAMP TO_TIMESTAMP('2009-05-08 09:07:59', 'YYYY-MM-DD HH24:MI:SS')
6 AND TO_TIMESTAMP('2009-05-08 09:09:22', 'YYYY-MM-DD HH24:MI:SS');
VERSIONS_XID NAME
---------------- --------------------
010010007B0D0000 JANI
030028003A110000 MANI
01002E007B0D0000 SCOTT
scott@orcl> connect sys/password@orcl as sysdba
Connected.
sys@orcl> SELECT undo_sql
2 FROM flashback_transaction_query
3 WHERE xid = HEXTORAW('010010007B0D0000');
UNDO_SQL
--------------------------------------------------------------------------------
update "SCOTT"."FLASHVERSION" set "NAME" = 'MANI' where ROWID = 'AAANPLAAEAAAGOlAAA';
sys@orcl> SELECT undo_sql
2 FROM flashback_transaction_query
3 WHERE xid = HEXTORAW('030028003A110000');
UNDO_SQL
--------------------------------------------------------------------------------
update "SCOTT"."FLASHVERSION" set "NAME" = 'SCOTT' where ROWID = 'AAANPLAAEAAAGOlAAA';
Flashback_transaction_query is a view which is belonging to sys schema. If you need to access from different schema, then you need to grant necessary privileges to other schema.
Here is the query content for flashback_transaction_query view.
CREATE OR REPLACE FORCE VIEW SYS.FLASHBACK_TRANSACTION_QUERY
(XID,
START_SCN,
START_TIMESTAMP,
COMMIT_SCN,
COMMIT_TIMESTAMP,
LOGON_USER,
UNDO_CHANGE#,
OPERATION,
TABLE_NAME,
TABLE_OWNER,
ROW_ID,
UNDO_SQL
)
AS
SELECT xid,
start_scn,
start_timestamp,
DECODE (commit_scn,
0, commit_scn,
281474976710655, NULL,
commit_scn)
commit_scn,
commit_timestamp,
logon_user,
undo_change#,
operation,
table_name,
table_owner,
row_id,
undo_sql
FROM sys.x$ktuqqry;
No comments:
Post a Comment