Flash Back in Oracle

The project uses:

First of all: The Flash back method has a premise, is the need to identify problems early, decisive action. If the record of the misoperation has been cleared in the Undo table space, this method is not available and needs to be found elsewhere.

For example:

SELECT * from Tb_moko_info_basic as of TIMESTAMP to_timestamp (' 2014-12-16 9:50:24 ', ' yyyy-mm-dd HH24:MI:SS ') WHERE receipt _num = ' P14120100496 ';

Use the flashback feature to quickly recover a user's mis-operation

The flashback feature provided by Oracle is very helpful for fast recovery of data that has been mistakenly manipulated. In earlier versions of Oracle without this feature, if you need to recover data loss due to user errors, you need a lot of time and effort to do incomplete recovery. However, this space-time-use strategy also has limitations and it is not possible to recover indefinitely from any point-in-time error. It is very effective to use this method for the misoperation of "timely" discovery.

A simple demonstration of this method, for reference.

1. Create the experimental table T [email protected]> CREATE table t (x int);

Table created.

2. Initialize data [email protected]> INSERT INTO T values (1);

1 row created.

[email protected]> commit;

Commit complete.

[email protected]> INSERT INTO T values (2);

1 row created.

[email protected]> commit;

Commit complete.

3. View the data in the table, the reason why the rowID field is used is convenient for later experiment reference. [email protected]> select rowID, x from T;

ROWID X----------------------------aaasvaaafaaaaayaaa 1 Aaasvaaafaaaaayaab 2

4. Analog error Delete [email protected]> delete from T;

2 rows deleted.

[email protected]> commit;

Commit complete.

5. Take a look at what actions are recorded on the T-table at this time? [email protected]> select Versions_starttime,versions_endtime, Versions_xid, versions_operation,t.x from T versions between timestamp minvalue and MaxValue;

versions_starttime    versions_endtime      VERSIONS_XID      v          X------------------------------------------ ---------------------------05-dec-09 09.52.51 pm                        09002c004b250000 d           2 05-dec-09 09.52.51 pm                        09002c004b250000 d           1 05-dec-09 09.50.57 pm 05-dec-09 09.52.51 pm 090018004b250000 i           2 05-dec-09 09.49.54 pm 05-dec-09 09.52.51 pm 090008004b250000 i           1

Since the operation time is not very long, the operation of the T-table is clearly displayed.

6. Recovery method One: Use Undo SQL. Focus on the "VERSIONS_XID" content, each of the different values correspond to a complete operation action. Let's look at the recovery statement for each of the above actions. 1) If you want to restore an operation that was mistakenly deleted, you can use the following undo SQL. [email protected]> select undo_sql from flashback_transaction_query WHERE xid= ' 09002c004b250000 ';

Undo_sql----------------------------------------------------------------------INSERT INTO "SEC". T "(" X ") VALUES (' 2 '); INSERT INTO "SEC". " T "(" X ") VALUES (' 1 ');

2) One step back: Roll back the inserted "2", that is, delete the row corresponding to the record "2". [email protected]> select undo_sql from flashback_transaction_query WHERE xid= ' 090018004b250000 ';

Undo_sql----------------------------------------------------------------------Delete from "SEC". " T "Where ROWID = ' Aaasvaaafaaaaayaab ';

3) Roll forward again: Roll back the inserted "1", that is, delete the row corresponding to the record "1". [email protected]> select undo_sql from flashback_transaction_query WHERE xid= ' 090008004b250000 ';

Undo_sql----------------------------------------------------------------------Delete from "SEC". " T "Where ROWID = ' aaasvaaafaaaaayaaa ';

7. Recovery Method II: Using the SCN number for easy recovery The above although the specific rollback of the SQL statement is listed, as long as the simple execution, but if the number of records involved is very much, then the use of specific undo SQL is not very convenient. Here is a relatively simple method, we can get the SCN number before operation by the value of XID in the Flashback_transaction_query view, and then flash back recovery according to the SCN number. A simple demonstration. 1) Get the SCN number before deleting the [email protected] > Col undo_sql for A42 [email protected] > Col operation for A8 [email  Protected] > select Undo_sql, operation, START_SCN from Flashback_transaction_query WHERE xid= ' 09002c004b250000 ';

Undo_sql operatio start_scn--------------------------------------------------------- ---insert INTO "SEC". "   T "(" X ") VALUES (' 2 '); DELETE 31205010 INSERT INTO "SEC". "   T "(" X ") VALUES (' 1 '); DELETE 31205010 BEGIN 31205010 2) Flash back using the SCN number "31205010" above[email protected]> Flashback table T to SCN 31205010; Flashback table T to SCN 31205010 * ERROR @ line 1:ora-08189:cannot flashback the table because row mov Ement is not enabled

3) The above error message is clear, you need to enable "row movement". [email protected]> ALTER TABLE T enable row movement;

Table altered.

4) try to flash back again, success. [email protected]> Flashback table T to SCN 31205010;

Flashback complete.

5) finally re-check the operation record of the T-table [email protected]> select Versions_starttime,versions_endtime, Versions_xid, Versions_ Operation,t.x from T versions between timestamp minvalue and MaxValue;

versions_starttime    versions_endtime      VERSIONS_XID      v          X------------------------------------------ ---------------------------05-dec-09 10.02.16 pm                        09000f004c250000 i           1 05-dec-09 10.02.16 pm                        09000f004c250000 i           2 05-dec-09 09.52.51 pm                        09002c004b250000 d           2 05-dec-09 09.52.51 PM      &NBsp;                09002C004B250000 D           1 05-dec-09 09.50.57 pm 05-dec-09 09.52.51 pm 090018004b250000 I           2 05-dec-09 09.49.54 pm 05-dec-09 09.52.51 pm 090008004b250000 I           1

6 rows selected.

The visible flashback action is also documented, because the actual SQL is used to complete.

6) Using this SCN-based recovery method can restore the table to any "recoverable" point in time and is more flexible. For example, we can use the following SQL to recover the T table to only insert the status of record "1". [email protected]> Flashback table T to SCN 31204988;

Flashback complete.

[email protected]> select * from T;

X----------1

Flash Back in Oracle