oracle logminer恢复某电信误删除的700万条数据
早知道8i 以后oracle就有logmnr这个东西，一直没机会尝试，这次终于逮着机会用了一下，真是挺管用！背景：solaris2.6 oracle817 管理员误操作删除了电信缴费的700万条数据，有两天前的rman全备及之后的所有归档日志，但是从来没进行过恢复测试，而且系统没有多余空间用于复制数据库。
方案:利用logminer找出删除操作的倒退操作，将删除的数据重新插入表中。
优点：对现有的数据库没有破坏，无后顾之忧。
实施：几乎所有的oracle教材中都有介绍，照做就是。
结果：所有被误删的数据都恢复。
总结：oracle自带的工具我们都应该尝试一下，其实有些很有用而且并不困难，只是我们没去尝试而已。
v$logmnr中sql_redo是用户作的dml,sql_undo是相应sql_redo的倒退操作，如果误操作数据不是太多，可以一次分析全部的日志（当然首先要确定要分析哪些日志），然后把sql_undo选择出来生成脚本，执行就可以了。
我要恢复的有700多万条，所以要分多次分析日志，多次插入。
Using Oracle LogMiner to undo incorrectly committed changes
A data loss due to user error means rows in a table accidentally updated or deleted by user and is also known as logical corruption. This usually happens when users lack training on how to properly use the application and its work flows. With appropriate training these kind of problems can be minimized, however oracle does provide a few ways to recover from such kind of losses. e.g. RMAN incomplete recovery if you have backups of data that is lost, FLASHBACK database (Oracle 10g) if FLASHBACK Logs are setup and FLASHBACK QUERY if UNDO management is being done appropriately etc.
One other way that requires almost no initial setup to be able to recover from such user errors is undoing the bad sql from Oracle redo log files using LogMiner.
Using LogMiner to analyze transactions between two SCN's (STARTSCN and ENDSCN):
If you happen to know the system change numbers where the bad sql was executed then you can target those SCN's in the redo log files and generate UNDO sql statements.
$ sqlplus / as sysdba
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
650778
SQL> conn scott/tiger
Connected.
SQL> select dname from dept;
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS
SQL> update dept set dname = 'X';
4 rows updated.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
650800
This is a disastrous update which changed and committed data incorrectly and it happened somewhere in between SCN 650778 and 650800.
begin
dbms_logmnr.start_logmnr (
startscn => '650778',
endscn => '650800',
options => dbms_logmnr.dict_from_online_catalog +
dbms_logmnr.continuous_mine +
dbms_logmnr.no_sql_delimiter +
dbms_logmnr.print_pretty_sql
);
end;
/
PL/SQL procedure successfully completed.
column sql_undo format a35
column sql_redo format a35
set lines 10000
set pages 200
select scn , sql_redo , sql_undo from v$logmnr_contents
where username = 'SCOTT'
and seg_name = 'DEPT';
SCN SQL_REDO SQL_UNDO
---------- ----------------------------------- -----------------------------------
650794 update "SCOTT"."DEPT" update "SCOTT"."DEPT"
set set
"DNAME" = 'X' "DNAME" = 'ACCOUNTING'
where where
"DNAME" = 'ACCOUNTING' and "DNAME" = 'X' and
ROWID = 'AAAMfKAAEAAAAAQAAA' ROWID = 'AAAMfKAAEAAAAAQAAA'
650794 update "SCOTT"."DEPT" update "SCOTT"."DEPT"
set set
"DNAME" = 'X' "DNAME" = 'RESEARCH'
where where
"DNAME" = 'RESEARCH' and "DNAME" = 'X' and
ROWID = 'AAAMfKAAEAAAAAQAAB' ROWID = 'AAAMfKAAEAAAAAQAAB'
650794 update "SCOTT"."DEPT" update "SCOTT"."DEPT"
set set
"DNAME" = 'X' "DNAME" = 'SALES'
where where
"DNAME" = 'SALES' and "DNAME" = 'X' and
ROWID = 'AAAMfKAAEAAAAAQAAC' ROWID = 'AAAMfKAAEAAAAAQAAC'
650794 update "SCOTT"."DEPT" update "SCOTT"."DEPT"
set set
"DNAME" = 'X' "DNAME" = 'OPERATIONS'
where where
"DNAME" = 'OPERATIONS' and "DNAME" = 'X' and
ROWID = 'AAAMfKAAEAAAAAQAAD' ROWID = 'AAAMfKAAEAAAAAQAAD'
SQL>
SQL> exec dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
dbms_logmnr.start_logmnr with startscn, endscn and option => dbms_logmnr.continuous_mine, finds a list of online redo log files or archived redo log files having the transactions happened in the range of provided SCN's and add those files to the LogMiner session.
dbms_logmnr.dict_from_online_catalog uses the online oracle data dictionary to translate the object ids in the log file to object names.
dbms_logmnr.no_sql_delimiter makes sure there is no sql delimiter like ";" or "/" in the end of the undo or redo sql generated by the LogMiner. If you have plans to run them via PL/SQL "execute immediate", these delimiters will cause problem.
dbms_logmnr.print_pretty_sql is used to format the sql redo or undo so they look pretty.
Using LogMiner to analyze transactions between two timestamps (STARTTIME and ENDTIME):
If you have no idea about the system change number when the data loss occurred, then you can provide a date and time range where you suspect the incorrect change happened.
$ sqlplus / as sysdba
SQL> alter session set nls_date_format="DD-MON-YYYY HH24:MI:SS";
Session altered.
SQL> select sysdate from dual;
SYSDATE
--------------------
10-JUN-2010 13:07:53
SQL> conn scott/tiger
Connected.
SQL> select dname from dept;
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS
SQL> update dept set dname = 'XYZ';
4 rows updated.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
Connected.
SQL> alter session set nls_date_format="DD-MON-YYYY HH24:MI:SS";
Session altered.
/*
Setting nls_date_format omits the need of applying to_date() on the date value that you are
going to provide to dbms_logmnr.start_logmnr().
*/
SQL> select sysdate from dual;
SYSDATE
--------------------
10-JUN-2010 13:09:06
begin
dbms_logmnr.start_logmnr (
starttime => '10-JUN-2010 13:07:53',
endtime => '10-JUN-2010 13:09:06',
options => dbms_logmnr.dict_from_online_catalog +
dbms_logmnr.continuous_mine +
dbms_logmnr.no_sql_delimiter +
dbms_logmnr.print_pretty_sql
);
end;
/
PL/SQL procedure successfully completed.
column sql_undo format a35
column sql_redo format a35
set lines 10000
set pages 200
select timestamp , sql_redo , sql_undo
from v$logmnr_contents
where username = 'SCOTT'
and seg_name = 'DEPT';
TIMESTAMP SQL_REDO SQL_UNDO
-------------------- ----------------------------------- -----------------------------------
10-JUN-2010 13:08:29 update "SCOTT"."DEPT" update "SCOTT"."DEPT"
set set
"DNAME" = 'XYZ' "DNAME" = 'ACCOUNTING'
where where
"DNAME" = 'ACCOUNTING' and "DNAME" = 'XYZ' and
ROWID = 'AAAMfKAAEAAAAAQAAA' ROWID = 'AAAMfKAAEAAAAAQAAA'
10-JUN-2010 13:08:29 update "SCOTT"."DEPT" update "SCOTT"."DEPT"
set set
"DNAME" = 'XYZ' "DNAME" = 'RESEARCH'
where where
"DNAME" = 'RESEARCH' and "DNAME" = 'XYZ' and
ROWID = 'AAAMfKAAEAAAAAQAAB' ROWID = 'AAAMfKAAEAAAAAQAAB'
10-JUN-2010 13:08:29 update "SCOTT"."DEPT" update "SCOTT"."DEPT"
set set
"DNAME" = 'XYZ' "DNAME" = 'SALES'
where where
"DNAME" = 'SALES' and "DNAME" = 'XYZ' and
ROWID = 'AAAMfKAAEAAAAAQAAC' ROWID = 'AAAMfKAAEAAAAAQAAC'
10-JUN-2010 13:08:29 update "SCOTT"."DEPT" update "SCOTT"."DEPT"
set set
"DNAME" = 'XYZ' "DNAME" = 'OPERATIONS'
where where
"DNAME" = 'OPERATIONS' and "DNAME" = 'XYZ' and
ROWID = 'AAAMfKAAEAAAAAQAAD' ROWID = 'AAAMfKAAEAAAAAQAAD'
dbms_logmnr.start_logmnr with starttime, endtime and option => dbms_logmnr.continuous_mine, builds a list of online or archived redo log files which contain transactions happened in the range of provided time range and add them to the LogMiner session.
Apply SQL_UNDO generated by LogMiner to recover from user error data loss:
Once you are able to identify the bad sql in the redo log files, simply write a PL/SQL program to execute the UNDO_SQL generated by the LogMiner as below and you should be able to recover the data back.
SQL> select dname from scott.dept;
DNAME
--------------
XYZ
XYZ
XYZ
XYZ
set serveroutput on
declare
CURSOR c1 IS
select sql_undo from v$logmnr_contents
where username = 'SCOTT'
and seg_name = 'DEPT';
begin
for rec in c1 loop
execute immediate rec.sql_undo;
dbms_output.put_line(sql%rowcount||' row(s) updated.');
end loop;
end;
/
1 row(s) updated.
1 row(s) updated.
1 row(s) updated.
1 row(s) updated.
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select dname from scott.dept;
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS
SQL> exec dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed. 好东西，学习了．
页:
[1]