使用Oracle logminer 找回delete的数据

  1. 云栖社区>
  2. 博客>
  3. 正文

使用Oracle logminer 找回delete的数据

andrew征程 2017-08-17 13:25:12 浏览1979
展开阅读全文
1、logminer介绍
2、logminer的用法
3、帮助用户找回delete的数据
1、logminer介绍
LogMiner 是Oracle公司从产品8i以后提供的一个实际非常有用的分析工具,使用该工具可以轻松获得Oracle 重做日志文件(归档日志文件)中的具体内容,LogMiner分析工具实际上是由一组PL/SQL包和一些动态视图组成,它作为Oracle数据库的一部分来发布,是oracle公司提供的一个完全免费的工具。

2、logminer的用法
在用logminer获取数据字典文件时,需要设置UTL_FILE_DIR参数,然而设置这个参数必须要重新启动数据库,生产库一般不能随便重启,从9i以上的版本可以直接使用在线数据文件字典,选项为dbms_logmnr.dict_from_online_catalog 。
直接使用三个dbms包:
exec dbms_logmnr.add_logfile();
exec dbms_logmnr.start_logmnr();
execute dbms_logmnr.end_logmnr;

3、帮助用户找回delete的数据
客户反馈晚上12点左右删除了数据,通过flashback已经无法闪回,也没有导出备份,rman全备倒是有,但是没有临时环境,且操作过程较长,此时需要logminer出马找回被delete的数据。
3.1 准备工作
相关视图
------------
v$logmnr_contents
SQL> desc v$logmnr_contents
Name Null? Type
----------------------------------------- -------- ----------------------------
SCN NUMBER
START_SCN NUMBER
COMMIT_SCN NUMBER
TIMESTAMP DATE
START_TIMESTAMP DATE
COMMIT_TIMESTAMP DATE
XIDUSN NUMBER
XIDSLT NUMBER
XIDSQN NUMBER
XID RAW(8)
PXIDUSN NUMBER
PXIDSLT NUMBER
PXIDSQN NUMBER
PXID RAW(8)
TX_NAME VARCHAR2(256)
OPERATION VARCHAR2(32)
OPERATION_CODE NUMBER
ROLLBACK NUMBER
SEG_OWNER VARCHAR2(32)
SEG_NAME VARCHAR2(256)
TABLE_NAME VARCHAR2(32)
SEG_TYPE NUMBER
SEG_TYPE_NAME VARCHAR2(32)
TABLE_SPACE VARCHAR2(32)
ROW_ID VARCHAR2(18)
USERNAME VARCHAR2(30)
OS_USERNAME VARCHAR2(4000)
MACHINE_NAME VARCHAR2(4000)
AUDIT_SESSIONID NUMBER
SESSION# NUMBER
SERIAL# NUMBER
SESSION_INFO VARCHAR2(4000)
THREAD# NUMBER
SEQUENCE# NUMBER
RBASQN NUMBER
RBABLK NUMBER
RBABYTE NUMBER
UBAFIL NUMBER
UBABLK NUMBER
UBAREC NUMBER
UBASQN NUMBER
ABS_FILE# NUMBER
REL_FILE# NUMBER
DATA_BLK# NUMBER
DATA_OBJ# NUMBER
DATA_OBJV# NUMBER
DATA_OBJD# NUMBER
SQL_REDO VARCHAR2(4000)
SQL_UNDO VARCHAR2(4000)
RS_ID VARCHAR2(32)
SSN NUMBER
CSF NUMBER
INFO VARCHAR2(32)
STATUS NUMBER
REDO_VALUE NUMBER
UNDO_VALUE NUMBER
SAFE_RESUME_SCN NUMBER
CSCN NUMBER
OBJECT_ID RAW(16)
EDITION_NAME VARCHAR2(30)
CLIENT_ID VARCHAR2(64)
----------------
3.2 确定日志
确定删除操作的时段,23:30~03:00之间做的delete,还好晚上没有什么业务,只产生了一个归档日志
3.3 添加日志
SQL> exec dbms_logmnr.add_logfile(LogFileName => '/backup/archivelog/1_251_946834694.dbf', Options => dbms_logmnr.new);
PL/SQL procedure successfully completed

3.4 解析日志
解析方法
DBMS_LOGMNR.START_LOGMNR ( startScn IN NUMBER default 0, endScn IN NUMBER default 0, startTime IN DATE default '01-jan-1988', endTime IN DATE default '31-dec-2110', DictFileName IN VARCHAR2 default '', Options IN BINARY_INTEGER default 0 );

执行解析
SQL> exec dbms_logmnr.start_logmnr(Options =>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed

解析这里可以根据时间段或者scn范围解析,scn和时间转换可以用
select timestamp_to_scn(to_date('2017-08-12 00:00:01','YYYY-MM-DD HH24:MI:SS')) from dual;
select timestamp_to_scn(to_date('2017-08-12 00:10:01','YYYY-MM-DD HH24:MI:SS')) from dual;
我没有使用时间段,把整个log进行解析,然后通过sql_redo对应的delete语句查找sql_undo的语句

确认所需的sql,客户删除语句类似DELETE from T_WXSCKD ……这样的操作
select operation,sql_redo,sql_undo from v$logmnr_contents where OPERATION='DELETE' and SQL_REDO like 'delete from "ERP_CC"."T_WXSCKD"%';
输出结果清楚的看到redo和undo的sql信息,现在要做的是把对应的undo中insert语句导出来,生成sql脚本。
1065dc18c415bc09fdf6d1fa52cfa9a9d82cd576

可以加参数美化输出结果,但是格式化后的sql不适合作为脚本执行:DBMS_LOGMNR.PRINT_PRETTY_SQL
exec dbms_logmnr.start_logmnr(Options =>dbms_logmnr.dict_from_online_catalog + DBMS_LOGMNR.PRINT_PRETTY_SQL);
5fd4d6a55257c348bcdfaa0e06e9394efffbf304

3.5 生成脚本并导入到临时表
注意中文的输出,需要先设置NLS_LANG环境变量
export NLS_LANG="AMERICAN_AMERICA.zhs16gbk"
进行sqlplus格式化,这样输出的sql脚本中每个insert都是一行。
SQLPLUS>
set line 1000 --设置行的长度
set pagesize 0 --输出不换页
set feedback off --默认的当一条sql发出的时候,oracle会给一个反馈,比如说创建表的时候,如果成功命令行会返回类似:Table created的反馈,off后不显示反馈
set heading off --不显示表头信息
set trimspool on --如果trimspool设置为on,将移除spool文件中的尾部空
set trims on --去掉空字符
set echo off;     --显示start启动的脚本中的每个sql命令,缺省为on
set termout off --不在屏幕上显示结果
spool 12345.sql --记录数据到db1.txt
select count(1) from v$logmnr_contents where OPERATION='DELETE' and SQL_REDO like 'delete from "ERP_CC"."T_WXSCKD"%'; --导出数据语句
spool off --收集完毕
cce0ac3d62537c97414011135f58a96599276c9b


创建临时表,
create table ERP_CC.T_WXSCKD_tmp as select * from ERP_CC.T_WXSCKD where 1=0;
SQL>@12345.sql
commit;
客户根据条件找回所需的数据。

网友评论

登录后评论
0/500
评论
andrew征程
+ 关注