- 最后登录
- 2018-11-1
- 在线时间
- 377 小时
- 威望
- 29
- 金钱
- 6866
- 注册时间
- 2011-10-12
- 阅读权限
- 200
- 帖子
- 891
- 精华
- 4
- 积分
- 29
- UID
- 1
|
2#
发表于 2017-4-28 15:06:32
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. |
|
发表于 2017-4-17 15:48:33
|
查看: 3291|
回复: 2
|手机版|Archiver|Oracle数据库数据恢复、性能优化
IP卡
狗仔卡
分享
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
显身卡
发表于 2017-5-5 09:16:54