A shallow summary of oracle log miner
A shallow summary of oracle log miner
Sometimes, we should recover one or more rows data which is deleted carelessly by users, but it is not necessary to recover the whole db and the flashback does not recovery the data because it's timeout.
For this moment, we should mine the log file to find the redo or undo data to recover the loss data and find who delete the data.
How to use the log miner?
After oracle 9i, oracle provides a tool to mine the log file, there is a package of oracle dbms for the logmnr named dbms_logmnr.
[sql] Make sure the db is archive log mode SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination +FAR/arch1 Oldest online log sequence 833 Next log sequence to archive 834 Current log sequence 834 SQL> Example of log miner 易做图yze procedure: SQL> SQL> create table test(id number); Table created. SQL> insert into test values(1); 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> commit; Commit complete. SQL> begin 2 for i in 1..100 loop 3 insert into test values(i); 4 commit; 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. Switch log file by user system SQL> alter system switch logfile; System altered. SQL> Engine the log miner. select name, thread#, sequence#, first_change#, next_change# from V$ARCHIVED_LOG; NAME THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# -------------------- ---------- ---------- ------------- ------------ 1 831 4062556971 4064186822 2 634 4063922165 4064177226 2 635 4064177226 4064275220 2 636 4064275220 4064429426 1 832 4064186822 4066775319 +FAR/arch1/1_833_817 1 833 4066775319 4066914873 620325.dbf +FAR/arch1/1_834_817 1 834 4066914873 4071821640 620325.dbf NAME THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# -------------------- ---------- ---------- ------------- ------------ +FAR/arch2/2_637_817 2 637 4064429426 4071821645 620325.dbf SQL> exec dbms_logmnr.add_logfile(logfilename=>'+FAR/arch1/1_834_817620325.dbf', options=>dbms_logmnr.new); PL/SQL procedure successfully completed. SQL> options: dbms_logmnr.new stand for the first log file to be 易做图yzed. SQL> exec dbms_logmnr.start_logmnr(options => sys.dbms_logmnr.dict_from_online_catalog); PL/SQL procedure successfully completed. SQL> select scn, timestamp, seg_name, seg_owner, sql_redo, sql_undo from v$logmnr_contents where seg_name='TEST' and set_owner='RAY'; SCN TIMESTAMP SEG_NAME SEG_OWNER SQL_REDO SQL_UNDO ---------- --------- ---------- ---------- ---------------------------------------- ---------------------------------------- 4071818078 11-SEP-13 TEST RAY create table test(id number); 4071818819 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '1 1'); ' and ROWID = 'AAAY8eAAFAAABbNAAA'; 4071818840 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '1 1'); ' and ROWID = 'AAAY8eAAFAAABbNAAB'; 4071818851 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '1 1'); ' and ROWID = 'AAAY8eAAFAAABbNAAC'; 4071819125 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '1 SCN TIMESTAMP SEG_NAME SEG_OWNER SQL_REDO SQL_UNDO ---------- --------- ---------- ---------- ---------------------------------------- ---------------------------------------- 1'); ' and ROWID = 'AAAY8eAAFAAABbNAAD'; 4071819127 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '2 2'); ' and ROWID = 'AAAY8eAAFAAABbNAAE'; 4071819129 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '3 3'); ' and ROWID = 'AAAY8eAAFAAABbNAAF'; 4071819131 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '4 4'); ' and ROWID = 'AAAY8eAAFAAABbNAAG'; ............... SCN TIMESTAMP SEG_NAME SEG_OWNER SQL_REDO SQL_UNDO ---------- --------- ---------- ---------- ---------------------------------------- ---------------------------------------- 4071819228 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '5 53'); 3' and ROWID = 'AAAY8eAAFAAABbNAA3'; 4071819231 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '5 54'); 4' and ROWID = 'AAAY8eAAFAAABbNAA4'; 4071819233 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '5 55'); 5' and ROWID = 'AAAY8eAAFAAABbNAA5'; 4071819235 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '5 SCN TIMESTAMP SEG_NAME SEG_OWNER SQL_REDO SQL_UNDO ---------- --------- ---------- ---------- ---------------------------------------- ---------------------------------------- 56'); 6' and ROWID = 'AAAY8eAAFAAABbNAA6'; 4071819237 11-SEP-13 TEST RAY insert into "RAY"."TE
- 更多Oracle疑问解答:
- 运行exp备份oracle数据库提示oracle-12154错误
- 有没有,生产Oracle Rman 备份脚本的工具啊!
- 初学orcle,希望有大大帮忙解说一下详细步骤,从登录oracle到创建表的过程
- oracle语句问题:一张user表,三个字段,id,name,time,插入记录比如:张三2007,李四2008,张三2011
- 如何写一个ORACLE触发器同步两个表中的数据?
- oracle 如何查看一个服务器上有多少个数据库.
- oracle 创建包的时候错误 求解
- oracle 重复列的问题
- oracle 中如何查处2星期前的数据
- 请教oracle数据库安装中的问题
- 请问谁能提供给我标准的oracle ERP的数据库表结构并详细说明各表主要的作用?
- 安装oracle遇到的问题 invalid entry CRC (expected 0x3e12e795 but got 0x9db0e9fd)
- 我的是ORACLE 10G,在RMAN中如何按指定的时间恢复数据文件啊?
- oracle为什么没有自动增长列
- oracle快捷键都有哪些啊?