Flashback Recovery Techniques
Quite often, you may be called upon to help
recover from a logical corruption of the database, rather than from a hardware
failure. You can use the following flashback techniques in Oracle Database 11g
to recover from logical errors:
•
Flashback Database: Takes the entire database back to a specific
point in time
•
Flashback Table: Returns individual tables to a past state
•
Flashback Drop: Undoes a DROP TABLE command and recovers the
dropped table
•
Flashback Query, Flashback Version Query, and Flashback Transaction
Query: Retrieve data from a time (or an interval) in the past
•
Flashback Transaction Backout: Lets you undo a transaction,
together with all its dependent transactions as well, with just a single click
in the Database Control
•
Flashback Data Archive: Stores history of the changes made to a
table, which you can use to make queries using older versions of data and for
auditing purposes as well
FLASHBACK TABLE DATA through SCN and TIMESTAMP
19:06:31 SQL> ---FLASHBACK TABLE SCENERIO (SCN)
19:06:43 SQL>
19:06:43 SQL>
19:06:43 SQL>
19:06:44 SQL> SELECT * FROM ASHWANI.KEY;
no rows selected
19:06:53 SQL> INSERT INTO ASHWANI.KEY VALUES ('HELLO1');
1 row created.
19:07:25 SQL> /
1 row created.
19:07:27 SQL> /
1 row created.
19:07:27 SQL> /
1 row created.
19:07:28 SQL> /
1 row created.
19:07:28 SQL> COMMIT;
Commit complete.
19:07:31 SQL> INSERT INTO ASHWANI.KEY VALUES ('HELLO2');
1 row created.
19:07:39 SQL> /
1 row created.
19:07:40 SQL> /
1 row created.
19:07:40 SQL> /
1 row created.
19:07:41 SQL> /
1 row created.
19:07:41 SQL> COMMIT;
Commit complete.
19:07:44 SQL> INSERT INTO ASHWANI.KEY VALUES ('HELLO3');
1 row created.
19:07:49 SQL> /
1 row created.
19:07:51 SQL> /
1 row created.
19:07:52 SQL> /
1 row created.
19:07:52 SQL> /
1 row created.
19:07:53 SQL> COMMIT;
Commit complete.
19:07:55 SQL> SELECT * FROM ASHWANI.KEY;
NAME
--------------------------------------------------
HELLO2
HELLO2
HELLO2
HELLO2
HELLO3
HELLO3
HELLO3
HELLO3
HELLO3
HELLO1
HELLO1
NAME
--------------------------------------------------
HELLO1
HELLO1
HELLO1
HELLO2
15 rows selected.
19:08:07 SQL> SELECT * FROM ASHWANI.KEY ORDER BY NAME;
NAME
--------------------------------------------------
HELLO1
HELLO1
HELLO1
HELLO1
HELLO1
HELLO2
HELLO2
HELLO2
HELLO2
HELLO2
HELLO3
NAME
--------------------------------------------------
HELLO3
HELLO3
HELLO3
HELLO3
15 rows selected.
19:08:18 SQL> select timestamp_to_scn(to_timestamp('19-FEB-2016
19:07:41','DD-MON-YY HH24.MI.SS')) from
dual;
TIMESTAMP_TO_SCN(TO_TIMESTAMP('19-FEB-201619:07:41','DD-MON-YYHH24.MI.SS'))
---------------------------------------------------------------------------
4940959
19:08:51 SQL> FLASHBACK TABLE ASHWANI.KEY TO SCN 5383106;
FLASHBACK TABLE ASHWANI.KEY TO SCN 5381450
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not
enabled
SQL> ALTER TABLE ASHWANI.KEY ENABLE ROW MOVEMENT;
Table altered.
19:08:51 SQL> FLASHBACK TABLE ASHWANI.KEY TO SCN 5381450;
Flashback complete.
19:09:03 SQL> SELECT * FROM ASHWANI.KEY ORDER BY NAME;
NAME
--------------------------------------------------
HELLO1
HELLO1
HELLO1
HELLO1
HELLO1
19:13:52 SQL> FLASHBACK TABLE ASHWANI.KEY TO TIMESTAMP
TO_TIMESTAMP('19-FEB-2016 19:06:44', 'DD-MM-YYYY HH24:MI:SS');
Flashback complete.
19:14:06 SQL> SELECT * FROM ASHWANI.KEY;
no rows selected
19:14:18 SQL>
No comments:
Post a Comment