RMAN - Oracle Flashback Query
Using Oracle flashback query, you can retrieve the
committed data as it was at a past point in time. As most of other
flashback technologies, this feature retrieves data from the undo
tablespace. It is possible to get undo data based on a specific
time or scn number. Using the select as of statement with both
OF TIMESTAMP and OF SCN clauses,
you can use this feature easily. It can even be performed by any
application developer without any DBA intervention. Developers can
use the dbms_flashback package to perform flashback query directly
from their applications, but the execute privilege should be
granted on the dbms_flashback
package. Now see it in action through the following scenario:
- Scenario 1: At 12.00, Bob got a call from an application
developer who told him that somebody deleted all the rows from
one of the tables by omitting the WHERE clause and committing
the transaction. He wanted to know which rows were deleted from
the table. Bob decided to use flashback query to get the before
image of the deleted rows.
To test a case similar to Bob's situation, perform the
following steps:
- Create a table as it was created by the application
developer and query it as follows:
SQL>
create
table tbl_seg_tbs as
2 select
segment_name, tablespace_name
from
dba_segments
3 where
rownum<7
order by
bytes desc;
Table created.
SQL>
select
count(*)
from
tbl_seg_tbs;
COUNT(*)
----------
6
SQL>
- In order to return back to this state of the table anytime,
get the current scn number and timestamp:
SQL>
select
to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') ddate,
dbms_flashback.get_system_change_number() scn from dual;
DDATE SCN
-------------------- ----------
07-02-2010 15:14:21 460141
This step was not performed by Bob or by the application
developer. The developer knows the time when the table was in a
correct state and told that time to Bob. Here you get the date and
current scn number just for testing purposes.
- Now run the delete command to clear the table and commit the
transaction:
SQL>
delete from
tbl_seg_tbs;
6 rows deleted.
SQL>
commit;
Commit complete.
SQL>
select * from
tbl_seg_tbs;
no rows selected
- As Bob knows the exact time of the correct state of the
table, he uses flashback query and retrieves the before state of
deleted rows from the undo tablespace as follows:
SQL>
select
count(*)
from tbl_seg_tbs
2 as of
timestamp to_timestamp('07-02-2010 15:14:21','dd-mm-yyyy
hh24:mi:ss');
COUNT(*)
----------
6
SQL>
In case he knows the scn number, he uses the OF SCN clause to
view the data of the table at the specified scn as follows:
SQL>
select * from
tbl_seg_tbs
2 as of scn 460135;
COUNT(*)
----------
6
SQL>
To view the data of the table as it was 15 minutes ago, use the
following query:
SQL>
select
count(*)
from tbl_seg_tbs
2 as of timestamp (systimestamp -interval '15' minute);
COUNT(*)
----------
6
SQL>
It is possible to convert scn to timestamp and timestamp to scn
using scn_to_timestamp and timestamp_to_scn functions:
SQL>
select scn_to_timestamp(460141) ddate,
timestamp_to_scn(to_timestamp('07-02-2010 15:14:21','dd-mm-yyyy
hh24:mi:ss')) scn from dual;
DDATE SCN
------------------------------------ ----------
07-FEB-10 03.14.21.000000000 PM 460141
SQL>
Viewing/Retrieving the Dropped PL/SQL Object codes using
Flashback Query
You can retrieve any dropped PL/SQL object using flashback
query. How? It is very easy. The source of these objects is stored
in an internal table: sys.source$. It means that when you create
a trigger, the line that contains its source is added to that
table. The same works for dropping the procedure. Please note
that recreating the objects relies on the dependencies being
valid; thus, any tables/views referenced in the procedure must be
present or recreated before recreating the procedure. So see how
it works:
- Create a simple trigger and get the current scn valueof the database:
SQL>
create or replace
trigger trg_logon
2 after logon on database
3 begin
4 insert into tbl_logons values(sysdate, user);
5 end;
6 /
Trigger created.
SQL>
select
current_scn
from
v$database;
CURRENT_SCN
-----------
528857
SQL>
- Now drop the trigger.
SQL>
drop
trigger trg_logon;
Trigger dropped.
SQL>
- Using the scn value that has been noted before dropping the
trigger, query the dba_source view as follows:
SQL>
select
text
from dba_source
as of
scn 528857
2 where name='trg_logon';
TEXT
---------------------------------------------------
trigger trg_logon
after logon on database
begin
insert into tbl_logons values(sysdate, user);
end;
SQL>
Using the about result text code, you can create or replace the
trigger again.
