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/