LOGMNR让你忘掉闪回，业务表的数据你敢闪？

刚在群里搜索了一下没有LOGMNR相关技术方面的帖子，以前珍藏的分享出来，很呆很暴力。信山哥，得永生，只要你有归档，你的数据就丢不了。

场景描述：

ORACLE事故——误删除，公司开发人员告诉我，前天下午五点-六点不小心误删了几条数据，问是否可以恢复，

环境是ORACLE 11g，没有备份，但有开归档和闪回，这个是可以找回数据的。我想妈呀，几天前的数据我敢闪回业务表。敢哥也不敢停机啊，终于oracle的日志技术解救了我。各位看官请看：

SQL> create table t1(id number,name varchar2(20));

Table created

SQL> insert into t1 values(1,'zhangsan');

1 row inserted

SQL> insert into t1 values(2,'zhangsi');

1 row inserted

SQL> insert into t1 values(3,'zhangwu');

1 row inserted

SQL> commit;

Commit complete

删除部分数据，并记录SCN。

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

4354137

SQL> delete from t1 where id=3;

1 row deleted

SQL> commit;

Commit complete

创建一张大表，用于测试。

SQL> create table t2 as select * from dba_objects;

Table created

SQL> INSERT INTO T2 SELECT * FROM T2;

75068 rows inserted

150136 rows inserted

300272 rows inserted

600544 rows inserted

对T2表做大量的update操作，模拟回滚段被回收。

UPDATE T2 SET OWNER=OWNER,OBJECT_NAME=OBJECT_NAME,SUBOBJECT_NAME=SUBOBJECT_NAME,

OBJECT_ID=OBJECT_ID,DATA_OBJECT_ID=DATA_OBJECT_ID,OBJECT_TYPE=OBJECT_TYPE,

CREATED=CREATED,LAST_DDL_TIME=LAST_DDL_TIME,TIMESTAMP=TIMESTAMP,STATUS=STATUS,

TEMPORARY=TEMPORARY,GENERATED=GENERATED,SECONDARY=SECONDARY,

NAMESPACE=NAMESPACE,EDITION_NAME=EDITION_NAME;

已更新1201104行。

已更新1201104行。

已更新1201104行。

已更新1201104行。

已更新1201104行。

已更新1201104行。

如果回滚段足够大，此时可以查询到SCN4354137之前的信息。

SQL> select * from t1 as of scn 4354137;

ID NAME

---------- --------------------

1 zhangsan

2 zhangsi

3 zhangwu

此时可以使用闪回表技术找回数据。

SQL> flashback table t1 to scn 4354137;

闪回完成。

SQL> select * from t1;

ID NAME

---------- --------------------

1 zhangsan

2 zhangsi

3 zhangwu

如果回滚段不够大，回滚段SCN4354137之前的空间将被回收，此时将无法查询SCN4354137之前的信息。

SQL> select * from t1 as of scn 4354137;

select * from t1 as of scn 4354137

ORA-01555: 快照过旧: 回退段号 8 (名称为 "_SYSSMU8_2456689326$") 过小

此时如果数据库开闪回，并且误删除的时间在db_flashback_retention_target参数范围内，可以利用闪回数据库技术，将整个数据库回退到之前的状态。

SQL> shutdown immediate

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup mount

ORACLE 例程已经启动。

Total System Global Area 313860096 bytes

Fixed Size 1374304 bytes

Variable Size 201328544 bytes

Database Buffers 104857600 bytes

Redo Buffers 6299648 bytes

数据库装载完毕。

SQL> flashback database to scn 4354137;

闪回完成。

SQL> alter database open resetlogs;

数据库已更改。

SQL> select * from t1;

ID NAME

---------- --------------------

1 zhangsan

2 zhangsi

3 zhangwu

如果误删除的时间超出了db_flashback_retention_target参数的范围，可能数据库无法闪回到scn 4354137状态，即使可以闪回到误删除之前的状态，无论是闪回表还是闪回数据库，必然对scn 4354137之后的操作有影响，闪回表到scn 4354137，scn 4354137之后对这个表所做的所有操作都将回退，如果是闪回数据库，整个数据库scn 4354137之后的操作都将被回退。误删除的数据重要，误删除之后的数据也重要，这时候如果选择闪回技术就要权衡哪个更重要的问题啦，还好ORACLE自8i开始，推出了LOGMNR日志分析工具，借用 LOGMNR工具，可以在不影响其他数据的同时找回误删除的数据。

初次使用，需要安装，很简单，只需要执行以下2个脚本即可。

SQL> conn / as sysdba

已连接。

SQL> @?/rdbms/admin/dbmslm

程序包已创建。

授权成功。

同义词已创建。

SQL> @?/rdbms/admin/dbmslmd

程序包已创建。

同义词已创建。

查看utl_file_dir设置

SQL> show parameter utl_file

NAME TYPE VALUE

------------------------------------ ----------- ---------------

utl_file_dir string D:oraclearch

可以通过命令行修改此参数，也可以通过修改pfile文件设置此参数。

SQL> alter system set utl_file_dir='D:oraclearch' scope=spfile;

系统已更改。

该参数为静态参数，需重启数据库后生效。

创建LOGMNR数据字典。

SQL> exec dbms_logmnr_d.build(

dictionary_filename => 'dict.ora',

dictionary_location => 'd:oraclearch');

PL/SQL 过程已成功完成。

添加需要分析的归档日志。

SQL> exec dbms_logmnr.add_logfile(

LogFileName =>'D:oraclearchO1_MF_1_117_79SR4KVR_.ARC',

Options => dbms_logmnr.new);

PL/SQL 过程已成功完成。

开始日志挖掘，分析日志。

SQL> execute dbms_logmnr.start_logmnr (

DictFileName => ’D:＼oracle＼arch＼dict.ora’);

PL/SQL procedure successfully completed

查看日志信息

SQL> select SCN,OPERATION,SEG_OWNER, TABLE_NAME,SEG_TYPE_NAME,

SQL_REDO,SQL_UNDO from v$logmnr_contents where scn>5533530

and scn

SCN：4354137

OPERATION：DELETE

SEG_OWNER：STREAM

TABLE_NAME：T1

SEG_TYPE_NAME：TABLE

SQL_REDO：delete from "STREAM"."TEST01" where "ID" = '3' and "NAME" = 'zhangwu'

SQL_UNDO：insert into "STREAM"."TEST01"("ID","NAME") values ('3','zhangwu');

SQL_REDO即之前做的模拟误删除的操作，SQL_UNDO就是还原应该做的操作，ORACLE LOGMNR工具真的是很黄很暴力。