Detailed data recovery after Oracle mistakenly deletes table data _oracle

Recovery after Oracle mistakenly deletes table data

Test environment:
SYSTEM:IBM AIX 5L Oracle version:10gr2

1. Query and modification of undo_retention parameters

Use the show parameter Undo command to view the current database parameter undo_retention settings.
The display is as follows:
Sql> Show Parameter Undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Undo_management string AUTO
Undo_retention Integer 900
Undo_tablespace string UNDOTBS2
Undo_retention (holding force), 900 units is seconds, that is 15 minutes.
to modify the default undo_retention parameter settings:
sql> ALTER SYSTEM SET undo_retention=10800 scope=both;

System altered.

Sql> Show Parameter Undo

name                                  type         VALUE
----------------------------------------------------------------------- ------
undo_management                       string      AUTO
undo_retention                         integer     10800
undo_tablespace                       string       UNDOTBS2
undo_retention 10800, unit seconds, that is 3 hours.

2. Oracle mistakenly delete table data after the rapid recovery function method

2.1 Method One
The flash-back functionality provided via Oracle

EXEC dbms_flashback.enable_at_time (to_date (' 2011-04-15 08:21:00 ', ' yyyy-mm-dd hh24:mi:ss '));
Set serveroutput on
DECLARE r_temp hr.job_history%rowtype;
CURSOR C_temp is SELECT * from Hr.job_history;
BEGIN
OPEN c_temp;
dbms_flashback.disable;
LOOP
FETCH c_temp into r_temp;
EXIT when C_temp%notfound;
Insert into Hr.job_history (employee_id,job_id,start_date,end_date) VALUES (r_temp. Employee_id,r_temp. Job_id,r_temp. Start_date,r_temp. end_date);
commit;
End LOOP;
Close c_temp;
End;

This approach restores the deleted data to the corresponding table, first ensuring that the user has permission to execute the Dbms_flashback package.

2.2 Method Two
INSERT INTO Hr.job_history
SELECT * from hr.job_history as of timestamp to_timestamp (' 2011-04-15 08:20:00 ', ' yyyy-mm-dd hh24:mi:ss ');
This method is simple, easy to master, the function and the above, the time here for you before the mistake of the time, preferably from the wrong operation is relatively close, because the Oracle save in the rollback retention section of the data time has a certain time limit, this limit is determined by undo_retention this parameter value.

View First_change#,next_change#,first_time
Sql> Set pagesize 9999
Sql> Col FSCN for 999999999
Sql> Col NSCN for 999999999
Sql> Select name,first_change# fscn,next_change# nscn,first_time from V$archived_log;

The current SCN is:
Sql> Select Dbms_flashback.get_system_change_number FSCN from dual;
Fscn
----------
3435958

Using the application user to try to flash back
Sql> Connect Username/password
Connected.

Existing data:
Sql> Select COUNT (*) from Hs_passport;
COUNT (*)
----------
851998
To create a recovery table:
Sql> CREATE TABLE Hs_passport_recov as SELECT * from Hs_passport where 1=0;

Table created.

Select the SCN forward recovery:
Sql> Select COUNT (*) from Hs_passport as of SCN 12929970422;
COUNT (*)
----------
861686

Try multiple SCN to get the best value (and get accurate data flashback if you know the time)

Sql> Select COUNT (*) from Hs_passport as of SCN &scn; Enter value for scn:12929941968 old 1:select count (*) from Hs_passport as of the SCN &SCN new 1:select count (*) from  Hs_passport as of SCN 12929941968 COUNT (*)----------861684 sql>/Enter value for scn:12927633776 old 1:select Count (*) from Hs_passport as of the SCN &SCN new 1:select count (*) from Hs_passport as of SCN 12927633776 select count ( * from Hs_passport as of the SCN 12927633776 * ERROR at line 1:ora-01466:unable to read data-table definition Has changed sql>/Enter value for scn:12929928784 old 1:select count (*) from Hs_passport as of SCN &SCN new 1 : SELECT COUNT (*) from Hs_passport as of the SCN 12929928784 count (*)----------825110 sql>/Enter value for Scn:12 928000000 Old 1:select Count (*) from Hs_passport as of SCN &SCN new 1:select count (*) from Hs_passport as of SCN 1 2928000000 Select COUNT (*) from Hs_passport as of SCN 12928000000 * ERROR At line 1:ora-01466:unable to read data-table definition has changed

 

Finally choose to revert to a point in time when the SCN is 12929941968
sql> INSERT INTO Hs_passport_recov select * from Hs_passport as of SCN 12929941968;

861684 rows created.

Sql> commit;

Commit complete.

Simple example of data recovery
In the past, if the user mistakenly deleted/updated the data, as the user does not have any direct way to restore, they have to help the DBA to restore the database, to the oracle9i, this embarrassing situation has improved. Oracle 9i provides a new technique--a flash-back query that allows users to get data before a bad operation in a timely manner, and to recover for errors without DBA intervention.

3. Below we use an example to specify the use of the flash-back query

Example
3.1 You must determine the following two parameters before using a flash-back query:
Undo_management = AUTO
Undo_retention = 10800;
This time can be arbitrarily set, it means that in the system to retain the submitted undo information time, 10800 is to keep 3 hours, that is 180 minutes.
3.2 Use Flash back query

Sql> conn/as sysdba Connected.

sql> drop user LSF cascade;

User dropped.

Sql> create user LSF identified by LSF;

User created.

Sql> Grant Connect,resource to LSF;

Grant succeeded.

Sql> Grant execute on Dbms_flashback to LSF;

Grant succeeded.
Sql> Conn LSF/LSF Connected.

Sql> CREATE TABLE T (id int, name VARCHAR2 (20));

Table created.

sql> INSERT INTO T values (1, ' LSF ');

1 row created.

sql> INSERT INTO T values (2, ' LSF ');

1 row created.

sql> INSERT into T values (3, ' LSF ');

1 row created.

Sql> commit;

Commit complete.

    Sql> select * from T; ID NAME----------------------------------------------------------------------1 LSF 2 LSF 3 LSF sql>

Set time on 10:12:50 sql> deletes from T where id=1;

1 row deleted.

10:13:02 sql> commit;

Commit complete.

    10:13:10 sql> SELECT * from T; ID NAME----------------------------------------------------------------------2 LSF 3 LSF 10:13:18 sql> ex ECute dbms_flashback.

Enable_at_time (To_date (' 2011-04-15 10:12:50 ', ' yyyy-mm-dd HH24:MI:SS '));

Pl/sql procedure successfully completed.

    10:13:50 sql> SELECT * from T;  ID NAME----------------------------------------------------------------------1 LSF 2 LSF 3 LSF 10:13:57 Sql> Execute dbms_flashback.

DISABLE;

Pl/sql procedure successfully completed.

    10:15:48 sql> SELECT * from T;

 ID NAME----------------------------------------------------------------------2 LSF 3 LSF

3.3 Recover data using a flash-back query

10:16:59 sql> truncate TABLE T;

Table truncated.

10:18:15 sql> SELECT * from T;

No rows selected 10:18:22 sql> insert INTO T values (1, ' LSF ');

1 row created.

10:19:42 sql> INSERT INTO T values (2, ' LSF ');

1 row created.

10:19:48 sql> INSERT INTO T values (3, ' LSF ');

1 row created.

10:19:55 sql> INSERT INTO T values (4, ' LSF ');

1 row created.

10:20:07 sql> INSERT INTO T values (5, ' LSF ');

1 row created.

10:20:15 sql> INSERT INTO T values (6, ' LSF ');

1 row created.

10:20:21 sql> commit;

Commit complete.

    10:20:26 sql> SELECT * from T; ID NAME----------------------------------------------------------------------1 LSF 2 LSF 3 LSF 4 ls

F 5 LSF 6 LSF 6 rows selected.

10:20:56 sql> Delete T;

6 rows deleted.

10:21:27 sql> commit;

Commit complete.
10:21:40 sql> DECLARE 10:22:29 2 cursor flash_recover is 10:22:43 3 select * from T;
10:22:50 4 T_recode T%rowtype; 10:23:11 5 begin 10:23:14 6 DBMS_FLASHBACK.enable_at_time (To_date (' 2011-04-15 10:20:56 ', ' yyyy-mm-dd HH24:MI:SS '));
10:24:22 7 Open Flash_recover; 10:24:39 8 Dbms_flashback.
DISABLE;
10:24:59 9 Loop 10:25:05 FETCH flash_recover into T_recode;
10:25:24 one EXIT when Flash_recover%notfound;
10:25:45 INSERT into T values (t_recode.id,t_recode.name);
10:26:35 end Loop;
10:26:39 Close flash_recover;
10:26:50 commit;
10:26:56 end;

10:26:58 17/pl/sql procedure successfully completed.

    10:27:00 sql> SELECT * from T; ID NAME----------------------------------------------------------------------1 LSF 2 LSF 3 LSF 4 ls

 F 5 LSF 6 LSF 6 rows selected.

We may have recovered all 6 records, but due to the limitations of the flashback query, it is possible that all 6 records could not be recovered, for the following reasons.

4. Limitations
The 4.1 Flashback query is based on the SCN, although we perform the following:
Dbms_flashback. Enable_at_time (To_date (' 2011-04-15 10:20:56 ', ' yyyy-mm-dd HH24:MI:SS '));
But Oracle will not be precise at this point in time, but round down to the most recent SCN, and then start the recovery from this SCN. The Oracle 9i records the SCN every five minutes and records the SCN and the mapping of the corresponding time.
So if you use Dbms_flashback. Enable_at_time to recover, we can wait for 5 minutes before we recover, in order to avoid a recovery failure.
Use Dbms_flashback. The downside of enable_at_time recovery is that the mapping of SCN and corresponding time in Oracle 9i will only remain for 5 days, so we cannot pass dbms_flashback. Enable_at_time to restore the data 5 days ago. If you want to use a flashback query to recover data from 5 days ago, you have to identify the SCN you need to restore and then use Dbms_flashback. Enable_at_system_change_number (Scn_number); To locate your recovery point in time, here's how to use it:

10:27:27 sql> VARIABLE scn_save number;
10:32:47 sql> Execute:scn_save: = Dbms_flashback. Get_system_change_number;

Pl/sql procedure successfully completed.

10:33:24 sql> print scn_save;

 Scn_save
----------
  3438420

10:33:41 sql> execute dbms_flashback. Enable_at_system_change_number (: scn_save);

Pl/sql procedure successfully completed.

10:34:31 sql> SELECT * from T;

    ID NAME
----------------------------------------------------------------------
     1 LSF
     2 LSF
     3 LSF
     4 LSF
     5 LSF
     6 LSF

6 rows selected.

In addition, the use of dbms_flashback. Before Enable_at_time, you have to set the exact level of your Nls_date_format, Oracle default is accurate to the day, if you do not set, like the above example you will not get the expected results.
4.2 If you use Sysdate and Dbms_flashback. Get_system_change_number to get the point-in-time or SCN values, you must be aware that they are all current point-in-time and SCN values.
4.3 You can only enter the Flash-back query mode at the beginning of the transaction, and you must commit if you have a DML operation before.
4.4 Flashback queries cannot be recovered until the table structure changes, because the current data dictionary used by the flashback query.