Oracle uses Logmnr to retrieve accidentally deleted data

Logmnr has made many improvements after Oracle 9i. If you do not need to build a flat text file, you can analyze the log, which means you can analyze the log without modifying the utl_file_dir parameter, the problem of restarting the database caused by modifying the utl_file_dir parameter is avoided. In addition, it also began to support building dictionary information into online logs to analyze and archive logs in different regions.

Logmnr is useful when flashback cannot help. As long as archive logs exist during misoperations, archive logs can be used to restore deleted data by mistake.

If you use a local online data dictionary to analyze archived logs at or above Oracle 9i, this is simple:

Piner @ 9iR2> exec-
Sys. dbms_logmnr.add_logfile (LogFileName => '/archive_log/archive/pai9.arc ',-
Options => dbms_logmnr.new );
PL/SQL procedure successfully completed.

Piner @ 9iR2> exec sys. dbms_logmnr.start_logmnr (-
Options => sys. dbms_logmnr.dict_from_online_catalog );
PL/SQL procedure successfully completed.
You can see that online analysis only requires these two steps to add and analyze logs. Note that the preceding OPTIONS => DBMS_LOGMNR.NEW indicates adding the first log. To add more logs, use the following method.

Piner @ 9iR2> exec sys. dbms_logmnr.add_logfile (LogFileName => '/archive_log/archive/pai10.arc ');
If the log analysis is complete, save the required information to the temporary table, as shown in figure

Piner @ 9iR2> create table tmp_logmnr
2 select operation, SQL _redo, SQL _undo from v $ logmnr_contents
3 where seg_name = 'test ';
Then, terminate the log analysis process.

Piner @ 9iR2> exec sys. dbms_logmnr.end_logmnr
For SQL _UNDO in a temporary table, you can choose to recover it. For example, you can use the following script to restore it. Here, to reduce blocking, commit each 1000 entries. In addition, note that the semicolon in SQL _UNDO must be removed if you want to use dynamic SQL for execution. [LINUX community www.LinuxIDC.com]

Declare
Mysql varchar2 (4000 );
Num number: = 0;
Begin
For c_tmp in (select SQL _undo from tmp_logmnr where operation = 'delete') loop
-- Remove the semicolon in the statement. It is assumed that only the statement ends with a semicolon and there is no semicolon in the middle of the statement.
-- If a semicolon exists in a statement, you can replace the '(single quotation mark) at the end of the statement with' (single quotation mark ).
Mysql: = replace (c_tmp. SQL _undo ,';','');
Execute immediate mysql;
Num: = num + 1;
If mod (num, 1000) = 0 then
Commit;
End if;
End loop;
Commit;
Exception
When others then
-- Exception Handling
End;
/
The preceding PL/SQL code can also be enhanced. For example, if one item is successfully restored, set the status value to 1. Otherwise, set the status value to-1 to facilitate tracking of records that are successfully restored, which records fail to be restored.

In addition, note that the temporary LOGMNR Table v $ logmnr_contents under 10 Gb in Oracle uses the system tablespace, and the system tablespace is changed to the sysaux tablespace after 10 Gb in Oracle. You can use the following command to change the specific tablespace of logmnr to prevent insufficient space in the system tablespace.

Piner @ 9iR2> exec sys. dbms_logmnr_d.set_tablespace ('users ');