动手为王——利用logminer挖掘日志恢复误操作
挖掘日志恢复误操作
1 介绍:
LogMiner是Oracle数据库提供的一个工具,它用于分析重做日志和归档日志所记载的事务操作。
Logminer是oracle从8i开始提供的用于分析重做日志信息的工具,它包括DBMS_LOGMNR和DBMS_LOGMNR_D两个package。它既能分析redo log file,也能分析归档后的archive log file。在分析日志的过程中需要使用数据字典,一般先生成数据字典文件后使用,10g版本还可以使用在线数据字典。
Logminer也可以分析其它数据库的重做日志文件,但是必须使用重做日志所在数据库的数据字典,否则会出现无法识别的乱码。另外被分析数据库的操作系统平台最好和当前Logminer所在数据库的运行平台一样,且block size相同。
LogMiner的功能
1)确定数据库的逻辑损坏时间
通过LogMiner可以准确定位该误操作的执行时间和SCN值,然后通过基于时间恢复或者基于SCN恢复可以完全恢复该表数据。
SQL> select
scn_to_timestamp(2599788) from dual;
scn_to_timestamp(2599788)
——————-
2016-05-01 06:06:01
SQL>select
timestamp_to_scn(to_timestamp(‘2016-5-1 6:06:06′,’yyyy-mm-dd hh34:mi:ss’)) FROM
dual;
TIMESTAMP_TO_SCN(TO_TIMESTAMP(‘2016-5-16:06:06′,’YYYY-MM-DDHH24:MI:SS’))
————————————————————————
2599788
2)确定事务级要执行的精细逻辑恢复操作
通过LogMiner可以取得任何用户的DML操作及相应的UNDO操作,通过执行UNDO操作可以取消用户的错误操作。
3)执行后续审计
通过LogMiner可以跟踪Oracle数据库的所有DML、DDL和DCL操作,从而取得执行这些操作的时间顺序、执行这些操作的用户等信息。
追加日志
重做日志用于实现例程恢复和介质恢复,这些操作所需要的数据被自动记录在重做日志中。但是,重做应用可能还需要记载其他列信息到重做日志中,记录其他列的日志过程被称为追加日志。
默认情况下,Oracle数据库没有开启追加日志,从而导致默认情况下LogMiner无法支持以下特征:
1)索引簇、链行和迁移行;
2)直接路径插入;
3)摘取LogMiner字典到重做日志;
4)跟踪DDL;
5)生成键列的SQL_REDO和SQL_UNDO信息;
6)LONG和LOB数据类型。
—-如何修改追加日志数据模式:
SYS@test> select
supplemental_log_data_min from v$database;
SUPPLEME
——–
NO
SYS@test> alter database add supplemental
log data;
Database altered.
SYS@test> select
supplemental_log_data_min from v$database;
SUPPLEME
——–
YES
—-关闭追加日志
SYS@test> alter database drop
supplemental log data;
Database altered.
SYS@test> select
supplemental_log_data_min from v$database;
SUPPLEME
——–
NO
2环境准备(数据误delete后并commit)
SYS@test> create user shall identified
by shall;
User created.
SYS@test> grant connect,resource to
shall;
Grant succeeded.
SHALL@test> create table test(id
number,name varchar2(20));
Table created.
SHALL@test> begin
2 for i in 1..100000 loop
3 insert into test
values(i,’zhong’);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SHALL@test> select count(*) from test;
COUNT(*)
———-
100000
SHALL@test> select * from test where id
= 999;
ID NAME
———- ——————–
999 zhong
SHALL@test> delete test;
100000 rows deleted.
SHALL@test> commit;
Commit complete.
SHALL@test> select * from test;
no rows selected
3开始数据挖掘找回已提交的删除数据
(1)数据库是关闭追加日志状态:
SYS@test> select
supplemental_log_data_min from v$database;
SUPPLEME
——–
NO
(2)查询库redo位置
SYS@test> select member from v$logfile;
MEMBER
——————————————————————————–
/u01/app/oracle/oradata/orcl/redo01_1.log
/u01/app/oracle/oradata/orcl/redo02_1.log
/u01/app/oracle/oradata/orcl/redo03_1.log
(3)生成日志挖掘队列
SYS@test> exec dbms_logmnr.add_logfile
(‘/u01/app/oracle/oradata/orcl/redo01_1.log’);
PL/SQL procedure successfully completed.
SYS@test> exec dbms_logmnr.add_logfile
(‘/u01/app/oracle/oradata/orcl/redo02_1.log’);
PL/SQL procedure successfully completed.
SYS@test> exec dbms_logmnr.add_logfile
(‘/u01/app/oracle/oradata/orcl/redo03_1.log’);
PL/SQL procedure successfully completed.
—-如果刚做了误操作,日志未切换,可以只添加当前redo
SYS@test> select group#,status from
v$log;
GROUP# STATUS
———- —————-
1 INACTIVE
2 INACTIVE
3 CURRENT
SYS@test> col member for a50
SYS@test> select group#,member,type from
v$logfile;
GROUP# MEMBER
TYPE
———-
————————————————– ——-
1 /u01/app/oracle/oradata/orcl/redo01_1.log ONLINE
2 /u01/app/oracle/oradata/orcl/redo02_1.log ONLINE
3 /u01/app/oracle/oradata/orcl/redo03_1.log ONLINE
(4)开始挖掘
SYS@test> exec
dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
(5)从v$logmnr_contents查前滚SQL和反算回来的回滚SQL
SYS@test> select sql_redo from
v$logmnr_contents where lower(sql_redo) like ‘%delete%’ and seg_name=’TEST’;
redo数据如下:
SQL_REDO
——————————————————————————–
delete from
“SHALL”.”TEST” where “ID” = ‘42016’ and
“NAME” = ‘zhong’ and ROWID =
‘AAAWGEAAEAAAOSfABL’;
delete from “SHALL”.”TEST”
where “ID” = ‘42017’ and “NAME” = ‘zhong’ and ROWID =
‘AAAWGEAAEAAAOSfABM’;
delete from
“SHALL”.”TEST” where “ID” = ‘42018’ and
“NAME” = ‘zhong’ and ROWID =
‘AAAWGEAAEAAAOSfABN’;
delete from
“SHALL”.”TEST” where “ID” = ‘42019’ and
“NAME” = ‘zhong’ and ROWID =
undo数据如下:
SYS@test> select sql_redo from
v$logmnr_contents where lower(sql_undo) like ‘%delete%’ and seg_name=’TEST’;
SQL_REDO
——————————————————————————–
insert into
“SHALL”.”TEST”(“ID”,”NAME”) values (‘5039′,’zhong’);
insert into
“SHALL”.”TEST”(“ID”,”NAME”) values
(‘5040′,’zhong’);
insert into
“SHALL”.”TEST”(“ID”,”NAME”) values
(‘5041′,’zhong’);
insert into
“SHALL”.”TEST”(“ID”,”NAME”) values
(‘5042′,’zhong’);
insert into
“SHALL”.”TEST”(“ID”,”NAME”) values
(‘5043′,’zhong’);
insert into
“SHALL”.”TEST”(“ID”,”NAME”) values
(‘5044′,’zhong’);
insert into
“SHALL”.”TEST”(“ID”,”NAME”) values
(‘5045′,’zhong’);
insert into
“SHALL”.”TEST”(“ID”,”NAME”) values
(‘5046′,’zhong’);
SYS@test> set linesize 200
SYS@test> set pagesize 10000
SYS@test> create table shall as select
sql_redo from v$logmnr_contents where lower(sql_undo) like ‘%delete%’ and
seg_name=’TEST’;
SYS@test> select * from shall;
(6)最后结束挖掘:
SYS@test> EXECUTE
DBMS_LOGMNR.END_LOGMNR;
PL/SQL procedure successfully completed.
发布于:2023-01-07,除非注明,否则均为
原创文章,转载请注明出处。
发表评论