On occasion I needed to recover a transaction COMMIT validated, but unwanted or erroneous. Here's an example:
- user deletes a row.
- by accident.
- Do not know what line it is, but 'something' says "deleted record."
-
(... deleted and validated)
.
- No idea what was the row data.
- Neither he, nor anyone.
- Since then, the row X has disappeared without trace.
With variations, this case is quite frequent, and if the user has erased the record is fast enough, there is a possibility of recovering the lost data.
Generally, all conventional transaction information processed through the areas of redo log. In these files is all the information for the new change. Oracle bases its entire business in the proper functioning of the redo log record and, therefore, in this area only basic physical data stores to redo the operation. Therefore, no information is stored on the previous values.
Which it does in the area of \u200b\u200brollback and rollback management also happens to redo!.
Oracle has a package, the LOG MINER that tracks the redo logs (online and archived) along with the rollback information also available in them. Once you run the package that creates the object dictionary file, Oracle is able to interpret and display information such redologs as valid as the generated SQL and SQL opposite to "repair" action.
SQL> connect / as sysdba Connected
.
SQL> show parameters utl NAME TYPE VALUE
------------------------------------ ------------------------------ -----------
utl_file_dir string c: \\ oraclefiles
SQL> exec DBMS_LOGMNR_D.BUILD (DICTIONARY_FILENAME => 'dictionary.ora' DICTIONARY_LOCATION => 'c: \\ oraclefiles'); PL / SQL procedure successfully completed. SQL> select member from v $ logfile; ----------------------------------- MEMBER --------------------------------------------- C: \\ ORACLE \\ oradata \\ ORCL \\ REDO04A.LOG C: \\ ORACLE \\ oradata \\ ORCL \\ REDO04B.LOG C: \\ ORACLE \\ oradata \\ ORCL \\ REDO05A.LOG C: \\ ORACLE \\ oradata \\ ORCL \\ REDO05B.LOG C: \\ ORACLE \\ oradata \\ ORCL \\ REDO06A.LOG
C: \\ ORACLE \\ oradata \\ ORCL \\ REDO06B.LOG
6 rows selected.
SQL> exec
DBMS_LOGMNR.add_logfile ('C: \\ ORACLE \\ oradata \\ ORCL \\ REDO04A.LOG');
PL / SQL procedure successfully completed.
SQL> exec
DBMS_LOGMNR.add_logfile ('C: \\ ORACLE \\ oradata \\ ORCL \\ REDO05A.LOG');
PL / SQL procedure successfully completed.
SQL> exec
DBMS_LOGMNR.add_logfile ('C: \\ ORACLE \\ oradata \\ ORCL \\ REDO06A.LOG');
PL / SQL procedure successfully completed.
SQL> EXECUTE
DBMS_LOGMNR.START_LOGMNR (DICTFILENAME => 'c: \\ oraclefiles \\ dictionary.ora');
PL / SQL procedure successfully completed.
SQL> set pages 100
SQL> set lines 120
SQL> column sql_redo
format a50 SQL> column sql_undo
format a50 SQL> select sql_redo, sql_undo from v $ logmnr_contents
WHERE rownum
SQL_REDO SQL_UNDO -------------------------------------------------- --------------------------------------------------
insert into "DISCOVERER9I." EUL5_MV101651 ("Import delete from" DISCOVERER9I. "EUL5_MV101651" where "
and SUM", "Amount COUNT", "Amount MAX", "Amount MIN" Amount SUM "= '170, 01 ' and "Amount COUNT" = '1 '
, "Amount AVG", "City", "Country", "Age Range", "Sex" and "Amount MAX' = '170, 01 'and' Amount MIN" = '
, "Flight Details") values \u200b\u200b('170, 01 ', '1', '170, 01 ', '1 170.01' and 'Amount AVG = '170, 01' and 'City' 70.01 ', '170, 01', 'Madeira', 'Portugal', 'Over 5 =' Madeira 'and "Country" =' Portugal 'and' Eda Range 0 to ± os', 'Woman', 'VUELO52149' ) d '=' Over 50 a ± os 'and' Sex '=' Male 'and
"Flight Details" =' VUELO52149 'and ROWID =' AAALV
mAAPAAAAn8AAA ';
insert into "DISCOVERER9I." EUL5_MV101651 ("Import delete from" DISCOVERER9I. "EUL5_MV101651" where " and SUM", "Amount COUNT", "Amount MAX", "Amount MIN" Amount SUM " = '112, 95 'and' Amount COUNT "= '1 '," Amount AVG "," City "," Country "," Age Range "," Sex "and" Amount MAX' = '112, 95 'and "Amount MIN" = '
, "Flight Details") values \u200b\u200b('112, 95', '1 ', '112, 95', '1 112.95 'and' Amount AVG = '112, 95 'and " City "
12.95 ', '112, 95', 'Madeira', 'Portugal', 'Over 5 =' Madeira 'and" Country " = 'Portugal' and 'Eda
Range 0 to ± os',' Woman ',' VUELO52206 ') d' = 'Over 50 a ± os' and' Sex '=' Male 'and
"Flight Details" = 'VUELO52206' and ROWID = 'AAALV
mAAPAAAAn8AAB';
insert into "DISCOVERER9I." EUL5_MV101651 ("Import delete from" DISCOVERER9I. "EUL5_MV101651" where "
and SUM", "Amount COUNT", "Amount MAX "," Amount MIN "Amount SUM" = '123, 29 'and' Amount COUNT "= '1 '
," Amount AVG "," City "," Country "," Age Range "," Sex "and" Amount MAX = '123, 29 'and' Amount MIN "= '," Flight Details ") values \u200b\u200b('123, 29', '1 ', '123, 29', '1 123.29 'and' Amount AVG" = ' 123.29 "and" City " 23.29 ', '123, 29', 'Madeira', 'Portugal', 'Over 5 =' Madeira 'and" Country "=' Portugal 'and' Range Eda <10;
0 a ± os', 'Woman', 'VUELO52331') d '=' Over 50 a ± os' and 'Sex' = 'Male' and "Flight Details" = 'VUELO52331' and ROWID = 'AAALV mAAPAAAAn8AAC ';
insert into "DISCOVERER9I." EUL5_MV101651 ("Import delete from "DISCOVERER9I." EUL5_MV101651 "where"
and SUM "," Amount COUNT "," Amount MAX "," Amount MIN "Amount SUM" = '178, 55 'and' Amount COUNT "= '1 '
, "Amount AVG", "City", "Country", "Age Range", "Sex" and "Amount MAX = '178, 55 'and' Amount MIN" = '
, "Flight Details") values \u200b\u200b('178 , 55 ', '1', '178, 55 ', '1 178.55' and 'Amount AVG = '178, 55' and 'City'
78.55 ', '178, 55', 'Madeira' , 'Portugal', 'Over 5 =' Madeira 'and "Country" =' Portugal 'and' Eda
Range 0 to ± os', 'Woman', 'VUELO52419') d '=' Over 50 to ± os' and "Sex" = 'Male' and
"Flight Details" = 'VUELO52419' and ROWID = 'AAALV
mAAPAAAAn8AAD';
insert into "DISCOVERER9I." EUL5_MV101651 ("Import delete from" DISCOVERER9I. "EUL5_MV101651" where "
and SUM", "Amount COUNT", "Amount MAX", "Amount MIN" Amount SUM "= '107, 36 'and' Amount COUNT" = '1 '
, "Amount AVG", "City" "Country", "Age Range", "Sex" and "Amount MAX = '107, 36 'and' Amount MIN" = '
, "Flight Details") values \u200b\u200b('107, 36', '1 ',' 107.36 ', '1 107.36' and 'Amount AVG = '107, 36 'and' City '
07.36', '107, 36 ',' Madeira ',' Portugal ',' Over 5 = 'Madeira' and "Country" = 'Portugal' and " Eda
Range 0 to ± os ',' Woman ',' VUELO52488 ') d' = 'Over 50 a ± os' and 'Sex' = 'Male' and
"Flight Details" = 'VUELO52488' and ROWID = 'AAALV
mAAPAAAAn8AAE';
insert into "DISCOVERER9I." EUL5_MV101651 ("Import delete from" DISCOVERER9I. "EUL5_MV101651" where "
and SUM", "Amount COUNT", "Amount MAX", "Amount MIN" Amount SUM "= '120, 5 ' and "Amount COUNT" = '1 'to
, "Amount AVG", "City", "Country", "Age Range", "Sex" nd "Max Amount" = '120, 5 "and" Amount MIN =
'12, "Flight Details") values \u200b\u200b('120, 5 ', '1', '120, 5 ', '120 0.5' and 'Amount AVG = '120, 5' and 'City' = ' M
, 5 ', '120, 5', 'Madeira', 'Portugal', 'Over 50 to ± adeira "and" Country "=' Portugal 'and' Range Age" =
os', 'Woman' 'VUELO52561'), 'Over 50 a ± os' and "Sex" = "Woman" and "Flight Of
size" =' VUELO52561 'and ROWID =' AAALVmAAP
AAAAn8AAF ';
insert into "DISCOVERER9I." EUL5_MV101651 ("Import delete from" DISCOVERER9I. "EUL5_MV101651" where "
and SUM", "Amount COUNT", "Amount MAX", "Amount MIN" Amount SUM " = '109, 01 'and' Amount COUNT "= '1 '
," Amount AVG "," City "," Country "," Age Range "," Sex "and" Amount MAX' = '109, 01 'and "Amount MIN" = '
, "Flight Details") values \u200b\u200b('109, 01', '1 ', '109, 01', '1 109.01 'and' Amount AVG = '109, 01 'and " City "
09.01 ', '109, 01', 'Madeira', 'Portugal', 'Over 5 =' Madeira 'and" Country " = 'Portugal' and 'Eda
Range 0 to ± os',' Woman ',' VUELO52690 ') d' = 'Over 50 a ± os' and' Sex '=' Male 'and
"Flight Details" = 'VUELO52690' and ROWID = 'AAALV
mAAPAAAAn8AAG';
insert into "DISCOVERER9I." EUL5_MV101651 ("Import delete from" DISCOVERER9I. "EUL5_MV101651" where "
and SUM", "Amount COUNT", "Amount MAX "," Amount MIN "Amount SUM" = '174, 91 'and' Amount COUNT "= '1 '
," Amount AVG "," City "," Country "," Age Range "," Sex "and" Amount MAX = '174, 91 'and' Amount MIN "= '
," Flight Details ") values \u200b\u200b('174, 91', '1 ', '174, 91', '1 174.91 'and' Amount AVG" = ' 174.91 "and" City "
74.91 ', '174, 91', 'Madeira', 'Portugal', 'Over 5 =' Madeira 'and" Country "=' Portugal 'and' Range Eda
0 a ± os', 'Woman', 'VUELO52693') d '=' Over 50 a ± os' and 'Sex' = 'Male' and
"Flight Details" = 'VUELO52693' and ROWID = 'AAALV
mAAPAAAAn8AAH ';
insert into "DISCOVERER9I." EUL5_MV101651 ("Import delete from "DISCOVERER9I." EUL5_MV101651 "where"
and SUM "," Amount COUNT "," Amount MAX "," Amount MIN "Amount SUM" = '131, 5 "and" Amount COUNT "= '1 'to
"Amount AVG", "City", "Country", "Age Range", "Sex" nd "Amount MAX = '131, 5" and "Amount MIN" = '13
, "Flight Details") values \u200b\u200b( '131, 5 ', '1', '131, 5 ', '131 1.5' and 'Amount AVG = '131, 5' and 'City' = 'M
, 5', '131, 5 ' 'Madeira', 'Portugal', 'Over 50 to ± adeira "and" Country "=' Portugal 'and' Range Age" =
os ',' Woman ',' VUELO52951 '),' Over 50 to ± os' and "Sex" = "Woman" and "Flight Of
size" = 'VUELO52951' and ROWID = 'AAALVmAAP
AAAAn8AAI';
9 rows selected.
0 comments:
Post a Comment