日志挖掘实验:
备份scott.emp表:
create emp_tmp as select * from emp;
删除emp数据:
delete from emp;
commit;
假如不能闪回查询:
select * from scott.emp as of timestamp to_timestamp('2013-12-15 12:00:00','YYYY-MM-DD HH24:MI:SS');
采用日志挖掘:
首先设置utl_file_dir参数:
alter system set utl_file_dir='/home/oracle/logmgr' scope=sfile;
重启数据库
建立新用户,将恢复的数据导入到这个表中:
create user wyq identified by wyq default tablespace TEST;
grant dba to wyq;
把原表数据备份到新用户下,用于做回滚:
create table wyq.emp as SELECT * from scott.emp;
生成数据字典:
exec dbms_logmnr_d.build('dictionary.ora','/home/oracle/logmgr');
(做这一步之前注意需要修改LD_LIBRARY_PATH和LIBPATH,使得lib的变量在lib32前面。
不然会有报错ORA-00600: internal error code, arguments: [unable to load XDB library], [], [], [], [], [], [], [])
注意:此报错没有遇见。
检查需要回滚的日志,客户要求回滚到12.15 17:00之前:
archive log list
cd /u02/arch_testdb2
-rw-r----- 1 oracle oinstall 45368320 Dec 15 15:49 1_330_821972228.dbf
-rw-r----- 1 oracle oinstall 45364224 Dec 15 16:00 1_331_821972228.dbf
-rw-r----- 1 oracle oinstall 45361664 Dec 15 18:39 1_331_821972228.dbf
-rw-r----- 1 oracle oinstall 45364224 Dec 15 19:01 1_333_821972228.dbf
-rw-r----- 1 oracle oinstall 4862976 Dec 15 20:46 1_334_821972228.dbf
添加归档日志:
exec dbms_logmnr.add_logfile(LogFileName=>'/u02/arch_testdb2/1_330_821972228.dbf',Options=>dbms_logmnr.new);
exec dbms_logmnr.add_logfile(LogFileName=>'/u02/arch_testdb2/1_331_821972228.dbf',Options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(LogFileName=>'/u02/arch_testdb2/1_332_821972228.dbf',Options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(LogFileName=>'/u02/arch_testdb2/1_333_821972228.dbf',Options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(LogFileName=>'/u02/arch_testdb2/1_334_821972228.dbf',Options=>dbms_logmnr.addfile);
开始挖日志:
exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmgr/dictionary.ora');
将logmnr的数据暂时保存在一个表里面,免得再次查询时候不用再次添加归档日志:
create table wyq_logmnr nologging as select * from v$logmnr_contents where 1=2;
insert /*+ append */ into wyq_logmnr select * from v$logmnr_contents;
导出脚本,用脚本做回滚,注意SQL_UNDO中的delete语句末尾有rowid,不能直接用,需要用正则表达式替换掉:
spool scott_undosql.txt
select regexp_replace(replace(SQL_UNDO,'"SCOTT"','"WYQ"'),'and ROWID.+;',';')
from wyq_logmnr
WHERE
SEG_NAME = 'EMP' AND
SEG_OWNER = 'SCOTT'
order by to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') desc;
spool off
去掉脚本的头部的语句和末尾返回多少多少行的文字,在wyq下执行这个脚本,实现数据回滚
备份scott.emp表:
create emp_tmp as select * from emp;
删除emp数据:
delete from emp;
commit;
假如不能闪回查询:
select * from scott.emp as of timestamp to_timestamp('2013-12-15 12:00:00','YYYY-MM-DD HH24:MI:SS');
采用日志挖掘:
首先设置utl_file_dir参数:
alter system set utl_file_dir='/home/oracle/logmgr' scope=sfile;
重启数据库
建立新用户,将恢复的数据导入到这个表中:
create user wyq identified by wyq default tablespace TEST;
grant dba to wyq;
把原表数据备份到新用户下,用于做回滚:
create table wyq.emp as SELECT * from scott.emp;
生成数据字典:
exec dbms_logmnr_d.build('dictionary.ora','/home/oracle/logmgr');
(做这一步之前注意需要修改LD_LIBRARY_PATH和LIBPATH,使得lib的变量在lib32前面。
不然会有报错ORA-00600: internal error code, arguments: [unable to load XDB library], [], [], [], [], [], [], [])
注意:此报错没有遇见。
检查需要回滚的日志,客户要求回滚到12.15 17:00之前:
archive log list
cd /u02/arch_testdb2
-rw-r----- 1 oracle oinstall 45368320 Dec 15 15:49 1_330_821972228.dbf
-rw-r----- 1 oracle oinstall 45364224 Dec 15 16:00 1_331_821972228.dbf
-rw-r----- 1 oracle oinstall 45361664 Dec 15 18:39 1_331_821972228.dbf
-rw-r----- 1 oracle oinstall 45364224 Dec 15 19:01 1_333_821972228.dbf
-rw-r----- 1 oracle oinstall 4862976 Dec 15 20:46 1_334_821972228.dbf
添加归档日志:
exec dbms_logmnr.add_logfile(LogFileName=>'/u02/arch_testdb2/1_330_821972228.dbf',Options=>dbms_logmnr.new);
exec dbms_logmnr.add_logfile(LogFileName=>'/u02/arch_testdb2/1_331_821972228.dbf',Options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(LogFileName=>'/u02/arch_testdb2/1_332_821972228.dbf',Options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(LogFileName=>'/u02/arch_testdb2/1_333_821972228.dbf',Options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(LogFileName=>'/u02/arch_testdb2/1_334_821972228.dbf',Options=>dbms_logmnr.addfile);
开始挖日志:
exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmgr/dictionary.ora');
将logmnr的数据暂时保存在一个表里面,免得再次查询时候不用再次添加归档日志:
create table wyq_logmnr nologging as select * from v$logmnr_contents where 1=2;
insert /*+ append */ into wyq_logmnr select * from v$logmnr_contents;
导出脚本,用脚本做回滚,注意SQL_UNDO中的delete语句末尾有rowid,不能直接用,需要用正则表达式替换掉:
spool scott_undosql.txt
select regexp_replace(replace(SQL_UNDO,'"SCOTT"','"WYQ"'),'and ROWID.+;',';')
from wyq_logmnr
WHERE
SEG_NAME = 'EMP' AND
SEG_OWNER = 'SCOTT'
order by to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') desc;
spool off
去掉脚本的头部的语句和末尾返回多少多少行的文字,在wyq下执行这个脚本,实现数据回滚
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29677883/viewspace-1170225/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29677883/viewspace-1170225/