Home » RDBMS Server » Server Administration » Flashback query(!?)
Flashback query(!?) [message #297017] Tue, 29 January 2008 23:03 Go to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Hi all!

I am trying to use Flashback Query as Oracle documenation 's introduction, and I tested an example following:

SQL> drop table test purge;

Table dropped.

SQL> create table test as select * from scott.dept@test_meta;

Table created.

SQL> select * from test;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 accounting     new york
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>


According to Oracle document
Quote:

Suppose, for instance, that a DBA discovers at 12:30 PM that data for employee JOHN had been deleted from the employee table, and the DBA knows that at 9:30AM the data for JOHN 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 had been lost.



And I deleted one row from test
SQL> delete from test where deptno=40;

1 row deleted.

SQL> select * from test;

    DEPTNO DNAME          LOC
---------- -------------- ------------
        50 accounting     new york
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO

SQL>


And then, I used a Flashback Query to define what happens at 11:55

SQL> select * from test
  2  as of timestamp to_timestamp('2008-01-30 11:55:00','YYYY-MM-DD HH:MI:SS')
  3  where deptno=40;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

SQL>


Recreate the row which was deleted
SQL> ed
Wrote file afiedt.buf

  1  insert into test
  2  (select * from test as of timestamp to_timestamp('2008-01-30 11:55:00','YYY
Y-MM-DD HH:MI:SS')
  3* where deptno=40)
SQL> /

1 row created.

SQL> select * from test;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 accounting     new york
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>


One question which I wondered that is: How do I know about the time when a DML is corrupted/falied/forgot....? In this example, I tested with my Database, okie, but I knew the time.

Thank you for your reply!
Many thank to Michel Cadot who took an example using Flashback multiple tables 1 year ago!
Re: Flashback query(!?) [message #297083 is a reply to message #297017] Wed, 30 January 2008 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How do I know about the time when a DML is corrupted/falied/forgot....?

Ask your users when it was correct and it was not.
Use flashback transaction query.
Use flashback query (as you did here).
Use LogMiner.

Regards
Michel

[Updated on: Wed, 30 January 2008 01:24]

Report message to a moderator

Re: Flashback query(!?) [message #297092 is a reply to message #297083] Wed, 30 January 2008 02:14 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Quote:

Use LogMiner.


This is the most useful utility I am carring about.

For more information, I would get some example, however, I did not get SCN from a past - time when a table was dropped. I tried to read more, but I could not.

May you have an example to use DBMS_LOGMNR to get SCN and recover point_in_time?

1/ Create a table Test
2/ Drop table Test
3/ Get SCN from archived_log_file before the table is dropped
4/ Incomplete recovery

And, with your Flashback demo to flashback multitables, would you like to explain why did you use DBMS_LOCK.Sleep()?

Thank you very much!

[Updated on: Wed, 30 January 2008 02:16]

Report message to a moderator

Re: Flashback query(!?) [message #297100 is a reply to message #297092] Wed, 30 January 2008 02:45 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
You can use
FROM flash
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
To see all committed versions of your data (within reasonable time parameters)
and you can use the pseudo-columns:

VERSIONS_STARTTIME
VERSIONS_ENDTIME , VERSIONS_STARTSCN
VERSIONS_ENDSCN
VERSIONS_XID VERSIONS_OPERATION
To get more info about each row version.
Re: Flashback query(!?) [message #297107 is a reply to message #297092] Wed, 30 January 2008 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
with your Flashback demo to flashback multitables, would you like to explain why did you use DBMS_LOCK.Sleep()?

Could you give a link to the code I posted a year ago?
I write thousands posts each year.

Quote:
May you have an example to use DBMS_LOGMNR to get SCN and recover point_in_time?

This is the purpose of another topic, yesterday, continue on this one.

Regards
Michel
Re: Flashback query(!?) [message #297110 is a reply to message #297107] Wed, 30 January 2008 03:03 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Yes! This is the link
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:53865178246302

Here's an example: 

SQL> -- Create the tables and display their content --
SQL> 
SQL> create table t1 enable row movement
  2  as select level val from dual connect by level <=3
  3  /
Table created.
SQL> create table t2 enable row movement
  2  as select level val from dual connect by level <=3
  3  /
Table created.
SQL> exec dbms_lock.sleep(10);
PL/SQL procedure successfully completed.
SQL> select * from t1;
          VAL
-------------
            1
            2
            3

3 rows selected.

SQL> select * from t2;
          VAL
-------------
            1
            2
            3

3 rows selected.

SQL> col scn new_value scn
SQL> SELECT dbms_flashback.get_system_change_number scn FROM DUAL;
          SCN
-------------
4339478652078

1 row selected.

SQL> def scn
DEFINE SCN             = 4339478652078 (NUMBER)
SQL> 
SQL> -- Modify data --
SQL> 
SQL> delete t1 where mod(val,2)=0;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> update t2 set val=val*val;
5 rows updated.
SQL> commit;
Commit complete.
SQL> select * from t1;
          VAL
-------------
            1
            3

2 rows selected.

SQL> select * from t2;
          VAL
-------------
            1
            4
            9

3 rows selected.

SQL> 
SQL> -- Flashback both tables at the same time --
SQL> 
SQL> flashback table t1,t2 to scn &scn;

Flashback complete.

SQL> select * from t1;
          VAL
-------------
            1
            2
            3

3 rows selected.

SQL> select * from t2;
          VAL
-------------
            1
            2
            3

3 rows selected.

Regards 
Michel 



Re: Flashback query(!?) [message #297111 is a reply to message #297110] Wed, 30 January 2008 03:07 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I used dbms_lock because there must be a minimum time (a couple of seconds) between the last DDL and the ability to use flashback query.

Regards
Michel
Previous Topic: not able to connect as shared server
Next Topic: deletion of bdump .trc files
Goto Forum:
  


Current Time: Thu Dec 08 16:32:13 CST 2016

Total time taken to generate the page: 0.08968 seconds