重庆思庄Oracle、Redhat认证学习论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

搜索
查看: 5125|回复: 7

LOGMNR让你忘掉闪回,业务表的数据你敢闪?

[复制链接]
发表于 2015-6-9 09:48:40 | 显示全部楼层 |阅读模式

刚在群里搜索了一下没有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
SQL> /
150136 rows inserted
SQL> /
300272 rows inserted
SQL> /
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行。
SQL> /
已更新1201104行。
SQL> /
已更新1201104行。
SQL> /
已更新1201104行。
SQL> /
已更新1201104行。
SQL> /
已更新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<5533541 and sql_redo like'delete%';
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工具真的是很黄很暴力。

[此贴子已经被郑全于2015-06-09 10:03:04编辑过]
回复

使用道具 举报

发表于 2015-6-9 10:07:04 | 显示全部楼层
信山哥,得永生
回复 支持 反对

使用道具 举报

发表于 2015-6-9 10:59:26 | 显示全部楼层

说一下,从9i开始,使用logminer,不需要创建数据字典,可以对归档日志,联机日志进行分析,只需要做的步骤为:

 

1.

exec dbms_logmnr.add_logfile(
LogFileName =>'D:oraclearchO1_MF_1_117_79SR4KVR_.ARC',
Options => dbms_logmnr.new);

2. execute dbms_logmnr.start_logmnr();

 

3.分析

  select SCN,OPERATION,SEG_OWNER, TABLE_NAME,SEG_TYPE_NAME,
SQL_REDO,SQL_UNDO from v$logmnr_contents where scn>5533530
and scn<5533541 and sql_redo like'delete%';

,只是在11g r2以后,追加日志默认没有开启,这样,就sql_undo里面就没有数据

   开启追加日志的语法为: 

alter database add supplemental log data;

回复 支持 反对

使用道具 举报

发表于 2015-6-9 11:00:38 | 显示全部楼层

闪回数据库一般不在生产库上用,闪回表可能会用。

 

回复 支持 反对

使用道具 举报

发表于 2015-6-9 11:11:57 | 显示全部楼层
&nbsp;好贴~
回复 支持 反对

使用道具 举报

发表于 2015-6-9 11:13:40 | 显示全部楼层
 写的挺多, 一看就没了
总的来说就是,csn能找到就闪回表,没有的话就用日志?
回复 支持 反对

使用道具 举报

 楼主| 发表于 2015-6-9 11:41:14 | 显示全部楼层
不是的,找到也别闪回。因为业务表在你闪回前肯定有无数数据。你只有通过日志做单点恢复。有一经典的句话,生成上的业务表,谁闪谁死。字典表可以闪回,业务表别去干这事。
回复 支持 反对

使用道具 举报

发表于 2015-12-9 21:38:42 | 显示全部楼层
在使用LOGMNR的时候,alter database add supplemental log data是不是必须的呢?
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛 ( 渝ICP备12004239号-4 )

GMT+8, 2020-10-30 23:45 , Processed in 0.124051 second(s), 20 queries .

重庆思庄学习中心论坛-重庆思庄科技有限公司论坛

© 2001-2020

快速回复 返回顶部 返回列表