[oracle@dg ~(16:50:39)]$ export ORACLE_SID=wailon
[oracle@dg ~(17:24:57)]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 28 17:25:01 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-- 系统是否处于归档日志模式
17:25:20 SCOTT@wailon> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
-- 需要使用LOGMINER必须启用SUPPLEMENTAL LOG(追加日志模式)
17:28:46 SCOTT@wailon> alter database add supplemental log data;
Database altered.
17:29:50 SCOTT@wailon> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
-- 建立LOGMNR_ADMIN角色,赋予相关授权
17:29:52 SCOTT@wailon> create role logmnr_admin;
Role created.
17:33:43 SCOTT@wailon> grant create session to logmnr_admin;
Grant succeeded.
17:35:18 SYS@wailon> grant select on v_$logmnr_contents to logmnr_admin;
Grant succeeded.
17:35:30 SYS@wailon> grant select on v_$logmnr_dictionary to logmnr_admin;
Grant succeeded.
17:35:46 SYS@wailon> grant select on v_$logmnr_parameters to logmnr_admin;
Grant succeeded.
17:36:00 SYS@wailon> grant select on v_$logmnr_logs to logmnr_admin;
Grant succeeded.
17:36:20 SYS@wailon> grant select on v_$archived_log to logmnr_admin;
Grant succeeded.
17:36:24 SYS@wailon> grant execute_catalog_role,
17:36:59 2 select any dictionary,
17:37:07 3 select any transaction,
17:37:18 4 select any table,
17:37:22 5 create tablespace,
17:37:37 6 drop tablespace to logmnr_admin;
Grant succeeded.
-- 创建用户并授权
17:37:47 SYS@wailon> create user logmnr identified by logmnr;
User created.
17:37:53 SYS@wailon> alter user logmnr quota 10m on users;
User altered.
17:38:08 SYS@wailon> grant logmnr_admin to logmnr;
Grant succeeded.
17:38:31 SYS@wailon> grant execute on dbms_flashback to logmnr;
Grant succeeded.
17:38:48 SYS@wailon> grant create any table to logmnr;
Grant succeeded.
17:39:21 SYS@wailon> grant flashback any table to logmnr;
Grant succeeded.
17:39:29 SYS@wailon> alter system switch logfile;
System altered.
-- 连接用户并创建测试表
17:39:50 SYS@wailon> conn logmnr/logmnr
Connected.
17:39:58 LOGMNR@wailon> create table tab_logmnr (id int,name varchar2(30),salary number(8,2));
Table created.
17:42:48 LOGMNR@wailon> insert into tab_logmnr values (1,'wailon',1000);
1 row created.
17:42:50 LOGMNR@wailon> insert into tab_logmnr values (2,'yojan',1200);
1 row created.
17:43:00 LOGMNR@wailon> insert into tab_logmnr values (3,'peter',1100);
1 row created.
17:43:11 LOGMNR@wailon> commit;
Commit complete.
17:43:15 LOGMNR@wailon> select * from tab_logmnr;
ID NAME SALARY
---------- ---------------------------------------- ----------
1 wailon 1000
2 yojan 1200
3 peter 1100
17:43:21 LOGMNR@wailon> -- transaction 1
17:44:11 LOGMNR@wailon> update tab_logmnr set salary=1500 where id=1;
1 row updated.
17:44:30 LOGMNR@wailon> update tab_logmnr set salary=1500 where id=2;
1 row updated.
17:44:37 LOGMNR@wailon> commit;
Commit complete.
17:44:39 LOGMNR@wailon> -- transaction 2
17:44:46 LOGMNR@wailon> delete from tab_logmnr where id=1;
1 row deleted.
17:44:59 LOGMNR@wailon> commit;
Commit complete.
17:45:00 LOGMNR@wailon> select * from tab_logmnr;
ID NAME SALARY
---------- ---------------------------------------- ----------
2 yojan 1500
3 peter 1100
17:45:14 LOGMNR@wailon> -- 需要恢复id=1的记录,将id=2的SALARY值恢复为原始值
-- 查看当前日志
17:46:10 LOGMNR@wailon> select a.group#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;
GROUP# STATUS MEMBER
---------- ---------------- --------------------------------------------------
1 CURRENT /u01/app/oracle/oradata/wailon/redo01.log
3 INACTIVE /u01/app/oracle/oradata/wailon/redo03.log
2 INACTIVE /u01/app/oracle/oradata/wailon/redo02.log
-- 开始LOGMNR操作,添加日志文件
17:47:18 LOGMNR@wailon> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/wailon/redo01.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
-- 开始LOGMNR
17:48:05 LOGMNR@wailon> exec dbms_logmnr.start_logmnr;
PL/SQL procedure successfully completed.
-- 查看LOGMNR结果
17:49:37 LOGMNR@wailon> select sql_redo from v$logmnr_contents where seg_owner='LOGMNR' and seg_name='TAB_LOGMNR';
SQL_REDO
------------------------------------------------------------------------------------------------------------------------
create table tab_logmnr (id int,name varchar2(30),salary number(8,2));
17:49:46 LOGMNR@wailon> select sql_undo from v$logmnr_contents where seg_owner='LOGMNR' and seg_name='TAB_LOGMNR';
SQL_UNDO
------------------------------------------------------------------------------------------------------------------------
17:50:09 LOGMNR@wailon> -- 未找到任何结果,换一下options
17:50:31 LOGMNR@wailon> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
-- 重做SQL,恢复被删除的记录
17:51:22 LOGMNR@wailon> select sql_redo from v$logmnr_contents where seg_owner='LOGMNR' and seg_name='TAB_LOGMNR';
SQL_REDO
------------------------------------------------------------------------------------------------------------------------
create table tab_logmnr (id int,name varchar2(30),salary number(8,2));
insert into "LOGMNR"."TAB_LOGMNR"("ID","NAME","SALARY") values ('1','wailon','1000');
insert into "LOGMNR"."TAB_LOGMNR"("ID","NAME","SALARY") values ('2','yojan','1200');
insert into "LOGMNR"."TAB_LOGMNR"("ID","NAME","SALARY") values ('3','peter','1100');
update "LOGMNR"."TAB_LOGMNR" set "SALARY" = '1500' where "ID" = '1' and "NAME" = 'wailon' and "SALARY" = '1000' and ROWID = 'AAASwaAAEAAAADFAAA';
update "LOGMNR"."TAB_LOGMNR" set "SALARY" = '1500' where "ID" = '2' and "NAME" = 'yojan' and "SALARY" = '1200' and ROWID = 'AAASwaAAEAAAADFAAB';
delete from "LOGMNR"."TAB_LOGMNR" where "ID" = '1' and "NAME" = 'wailon' and "SALARY" = '1500' and ROWID = 'AAASwaAAEAAAADFAAA';
7 rows selected.
17:52:08 LOGMNR@wailon> insert into "LOGMNR"."TAB_LOGMNR"("ID","NAME","SALARY") values ('1','wailon','1000');
1 row created.
17:52:27 LOGMNR@wailon> update "LOGMNR"."TAB_LOGMNR" set "SALARY" = '1500' where "ID" = '1' and "NAME" = 'wailon' and "SALARY" = '1000' and ROWID = 'AAASwaAAEAAAADFAAA';
0 rows updated.
17:52:27 LOGMNR@wailon> update "LOGMNR"."TAB_LOGMNR" set "SALARY" = '1500' where "ID" = '1' and "NAME" = 'wailon' and "SALARY" = '1000';
1 rows updated.
17:52:39 LOGMNR@wailon> select * from tab_logmnr;
ID NAME SALARY
---------- ---------------------------------------- ----------
2 yojan 1500
3 peter 1100
1 wailon 1000
-- 撤销SQL,恢复被修改的记录
17:51:28 LOGMNR@wailon> select sql_undo from v$logmnr_contents where seg_owner='LOGMNR' and seg_name='TAB_LOGMNR' and sql_undo like lower(%update%);
SQL_UNDO
------------------------------------------------------------------------------------------------------------------------
update "LOGMNR"."TAB_LOGMNR" set "SALARY" = '1000' where "ID" = '1' and "NAME" = 'wailon' and "SALARY" = '1500' and ROWI
D = 'AAASwaAAEAAAADFAAA';
update "LOGMNR"."TAB_LOGMNR" set "SALARY" = '1200' where "ID" = '2' and "NAME" = 'yojan' and "SALARY" = '1500' and ROWID
= 'AAASwaAAEAAAADFAAB';
2 rows selected.
17:53:48 LOGMNR@wailon> update "LOGMNR"."TAB_LOGMNR" set "SALARY" = '1200' where "ID" = '2' and "NAME" = 'yojan' and "SALARY" = '1500';
1 row updated.
17:54:34 LOGMNR@wailon> select * from tab_logmnr;
ID NAME SALARY
---------- ---------------------------------------- ----------
2 yojan 1200
3 peter 1100
1 wailon 1500
17:54:45 LOGMNR@wailon> commit;
Commit complete.
以上只是演示LogMiner的简单使用,实际环境中如果有这种恢复需要,10g以上可以使用flashback技术更加方便。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/429786/viewspace-776423/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/429786/viewspace-776423/