1.挖掘日志恢复方式
1.1 介绍：
DDL可以直接挖，如果挖掘dml语句需要将数据库置为追加日志数据模式。
如果数据做了dml操作（insert、update、delete）然后commit了，这时如果找回数据，可以直接挖。
----如何修改追加日志数据模式：
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
1.2环境准备
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
1.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.
2.利用flashback query恢复dml数据
2.1使用闪回查询前必须确定下面两个参数
UNDO_MANAGEMENT = AUTO
undo_retention = 1800; #这个时间可以随便设，他表示在系统中保留提交了的UNDO信息的时间，1800就是保留30分钟。
select dbms_flashback.get_system_change_number from dual; --获取当前scn号
2.2环境准备(9i)
（1）查看数据库模式
SQL> archive log list
数据库日志模式 非存档模式
自动存档 禁用
存档终点 C:\oracle\ora92\RDBMS
最早的概要日志序列 1
当前日志序列 2
SQL> select log_mode,open_mode from v$database;
LOG_MODE OPEN_MODE
------------ ----------
NOARCHIVELOG READ WRITE
（2）创建闪回用户
SQL> create user shallzhong identified by shall;
用户已创建
SQL> grant connect,resource to shallzhong;
授权成功。
SQL> grant execute on dbms_flashback to shallzhong;
授权成功。
SQL> conn shallzhong/shall
已连接。
（3）创建测试表，并插入数据提交
SQL> create table zhong(id int,name varchar2(10));
表已创建。
SQL> begin
2 for i in 1..100001 loop
3 insert into zhong values(i,'zhong');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select count(*) from zhong;
COUNT(*)
----------
100001
（4）记录删除之前时间或SCN
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2016-05-14 11:30:56
select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
275829
记录时间后，最好等一段时间在做下面的操作，oracle每5分钟才记录一次scn，所以可能会导致闪回查询不到数据
（5）删除数据并提交
SQL> delete zhong;
已删除100001行。
SQL> commit;
提交完成。
2.3闪回查询
SQL> execute dbms_flashback.enable_at_time(to_date('2016-05-14 11:30:56','yyyy-mm-dd hh24:mi:ss'));
PL/SQL 过程已成功完成。
SQL> select count(*) from zhong;
COUNT(*)
----------
0
SQL> execute dbms_flashback.disable;
PL/SQL 过程已成功完成。
SQL> execute dbms_flashback.enable_at_system_change_number(275829);
PL/SQL 过程已成功完成。
SQL> select count(*) from zhong;
COUNT(*)
----------
100001
SQL> execute dbms_flashback.disable;
PL/SQL 过程已成功完成。
SQL> select count(*) from zhong;
COUNT(*)
----------
0
（1）为什么基于时间闪回查询不到数据？而基于scn可以？
闪回查询是基于SCN的：（oracle9i中smon_scn_time表每5分钟被更新一次，oracle10g是每6秒更新一次）
Oracle并不会精确的这个时间点，进行恢复。而Oracle 9i是每五分钟记的而是ROUND DOWN到最近的一次SCN，然后从这个SCN开始记录一次SCN，并将SCN和对应时间的映射做个纪录。这正是上面进行dbms_flashback.enable_at_time查询不到删除数据的原因。所以，删除之前等5分钟试试最保险。
select * from smon_scn_time;
SMON_SCN_TIME记录5天的数据，也就是1440 ((5d x 24h x 12m = 1440) 条记录。所以在Oracle9iR2中，表属性修改时间和flashback时间差至少应为5分钟。否则就会出现ORA-01466错误
SQL> select thread,time_mp,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss'),scn_wrp,scn_bas from smon_scn_time ;
THREAD TIME_MP TO_CHAR(TIME_DP,'YY SCN_WRP SCN_BAS
---------- ---------- ------------------- ---------- ----------
1 1463195362 2016-05-14 11:09:24 0 252950
1 1463195670 2016-05-14 11:14:31 0 254047
1 1463196445 2016-05-14 11:27:25 0 274757
1 1463196752 2016-05-14 11:32:33 0 276521
1 1463197061 2016-05-14 11:37:41 0 277760
1 1463197368 2016-05-14 11:42:49 0 278504
1 1463197672 2016-05-14 11:47:56 0 279318
1 1463197983 2016-05-14 11:53:03 0 280126
已选择107行。
（2）truncate后重新测试
SQL> truncate table zhong;
表已截掉。
SQL> execute dbms_flashback.enable_at_system_change_number(275829);
PL/SQL 过程已成功完成。
SQL> select count(*) from zhong;
select count(*) from zhong
*
ERROR 位于第 1 行:
ORA-01466: 无法读数据 - 表定义已更改
SQL> execute dbms_flashback.disable;
PL/SQL 过程已成功完成。
-------------------------------------------------------
（3）环境准备
SQL> begin
2 for i in 1..99999 loop
3 insert into zhong values(i,'shall');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select count(*) from zhong;
COUNT(*)
----------
99999
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2016-05-14 12:03:44
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
282784
SQL>
SQL> select thread,time_mp,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss'),scn_wrp,scn_bas from smon_scn_time ;
1 1463197983 2016-05-14 11:53:03 0 280126
1 1463198288 2016-05-14 11:58:10 0 281285
1 1463198596 2016-05-14 12:03:18 0 282698
（4）等待scn记录......
SQL> select thread,time_mp,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss'),scn_wrp,scn_bas from smon_scn_time ;
1 1463197983 2016-05-14 11:53:03 0 280126
1 1463198288 2016-05-14 11:58:10 0 281285
1 1463198596 2016-05-14 12:03:18 0 282698
1 1463198903 2016-05-14 12:08:25 0 283509
（5）删除数据
SQL> delete zhong;
已删除99999行。
SQL> commit;
提交完成。
（6）闪回查询数据
SQL> execute dbms_flashback.enable_at_time(to_date('2016-05-14 12:03:44','yyyy-mm-dd hh24:mi:ss'));
PL/SQL 过程已成功完成。
SQL> select count(*) from zhong;
COUNT(*)
----------
99999
SQL> execute dbms_flashback.disable;
PL/SQL 过程已成功完成。
2.4利用闪回恢复数据
（1）利用闪回时间恢复数据
declare
cursor zhong_recover is select * from zhong;
t_recode zhong%rowtype;
begin
dbms_flashback.enable_at_time(to_date('2016-05-14 12:03:44','yyyy-mm-dd hh24:mi:ss'));
open zhong_recover;
dbms_flashback.disable;
loop fetch zhong_recover into t_recode;exit when zhong_recover%notfound;
insert into zhong values(t_recode.id,t_recode.name);
end loop;
close zhong_recover;
commit;
end;
/
PL/SQL 过程已成功完成。
SQL> select count(*) from zhong;
COUNT(*)
----------
99999
（2）利用闪回scn恢复数据
先删除之前恢复的部分数据
SQL> delete zhong where id>400;
已删除99599行。
SQL> select count(*) from zhong;
COUNT(*)
----------
400
SQL> commit;
提交完成。
创建表recover用于存放恢复数据
SQL> create table recover as select * from zhong where 1=0;
表已创建。
SQL> select * from recover;
未选定行
开始闪回恢复
declare
cursor zhong_recover is select * from zhong;
t_recode zhong%rowtype;
begin
dbms_flashback.enable_at_system_change_number(282784);
open zhong_recover;
dbms_flashback.disable;
loop fetch zhong_recover into t_recode;exit when zhong_recover%
notfound;
insert into zhong values(t_recode.id,t_recode.name);
insert into recover values(t_recode.id,t_recode.name);
end loop;
close zhong_recover;
commit;
end;
/
PL/SQL 过程已成功完成。
SQL> select count(*) from recover;
COUNT(*)
----------
99999
SQL> select count(*) from zhong;
COUNT(*)
----------
100399
（3）当恢复的数据再次写入到原表中时，会出现重复的数据，下面进行去重
--------------去重
delete from zhong
where id in (select id from zhong
group by id
having count(id)>1)
and rowid not in(select max(rowid) from zhong
group by id
having count(id)>1);
已删除400行。
SQL> select count(*) from zhong;
COUNT(*)
----------
99999
SQL> commit;
提交完成。
去重也这样写：
delete zhong z1
where z1.rowid <> (select max(rowid) from zhong z2
where z1.id=z2.id);
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2099799/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/30130773/viewspace-2099799/
1108
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
