A shallow summary of oracle log miner
来源：互联网 发布：手机怎么多注册淘宝号 编辑：程序博客网 时间：2024/04/28 23:31
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 oracle log miner, we should pay attention to another main points.
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.
Make sure the db is archive log modeSQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination +FAR/arch1Oldest online log sequence 833Next log sequence to archive 834Current log sequence 834SQL>Example of log miner analyze 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 systemSQL> 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 4066914873620325.dbf+FAR/arch1/1_834_817 1 834 4066914873 4071821640620325.dbfNAME THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#-------------------- ---------- ---------- ------------- ------------+FAR/arch2/2_637_817 2 637 4064429426 4071821645620325.dbfSQL> 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 analyzed.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"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '5 57'); 7' and ROWID = 'AAAY8eAAFAAABbNAA7';4071819239 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '5 58'); 8' and ROWID = 'AAAY8eAAFAAABbNAA8';4071819241 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '5 59'); 9' and ROWID = 'AAAY8eAAFAAABbNAA9'; SCN TIMESTAMP SEG_NAME SEG_OWNER SQL_REDO SQL_UNDO---------- --------- ---------- ---------- ---------------------------------------- ----------------------------------------4071819243 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '6 60'); 0' and ROWID = 'AAAY8eAAFAAABbNAA+';4071819244 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '6 61'); 1' and ROWID = 'AAAY8eAAFAAABbNAA/';4071819247 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '6 62'); 2' and ROWID = 'AAAY8eAAFAAABbNABA';4071819249 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '6 63'); 3' and ROWID = 'AAAY8eAAFAAABbNABB';........... SCN TIMESTAMP SEG_NAME SEG_OWNER SQL_REDO SQL_UNDO---------- --------- ---------- ---------- ---------------------------------------- ---------------------------------------- 78'); 8' and ROWID = 'AAAY8eAAFAAABbNABQ';4071819285 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '7 79'); 9' and ROWID = 'AAAY8eAAFAAABbNABR';4071819288 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '8 80'); 0' and ROWID = 'AAAY8eAAFAAABbNABS';4071819290 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '8 81'); 1' and ROWID = 'AAAY8eAAFAAABbNABT'; SCN TIMESTAMP SEG_NAME SEG_OWNER SQL_REDO SQL_UNDO---------- --------- ---------- ---------- ---------------------------------------- ----------------------------------------4071819292 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '8 82'); 2' and ROWID = 'AAAY8eAAFAAABbNABU';4071819294 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '8 83'); 3' and ROWID = 'AAAY8eAAFAAABbNABV';4071819296 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '8 84'); 4' and ROWID = 'AAAY8eAAFAAABbNABW';4071819298 11-SEP-13 TEST RAY insert into "RAY"."TEST"("ID") values (' delete from "RAY"."TEST" where "ID" = '8 85'); 5' and ROWID = 'AAAY8eAAFAAABbNABX';SQL> exec dbms_logmnr.end_logmnr;PL/SQL procedure successfully completed.For oracle log miner, we should pay attention to another main points.
- A shallow summary of oracle log miner
- Oracle Log Miner工具说明
- Oracle Log Miner不同的数据库
- Log Miner 使用
- log miner的使用
- 日志挖掘 log miner
- A Summary of RB Tree
- a summary of python crawler
- A Summary of word endings
- A summary of the interview
- A summary of dropout technique
- A Summary of Unity Attributes
- [转帖]A summary of OO Principles
- A Brief Summary of My Blog System
- a summary of local feature and decriptor
- A summary of asking how someone is
- A summary of the first semester
- A Summary of Staged Written Exam
- 常见android手机分辨率(xxhdpi,xhdpi)
- unix环境高级编程-4.11-chown，fchown和lchown函数
- linux设备上的Onvif 实现15: 连接RTSP获取视频流流程图
- base.js
- 交换两个变量的值，不用第三个变量
- A shallow summary of oracle log miner
- Oracle 数据泵详解
- python中decode和encode的使用
- 为了美观当网页图片不存在时不显示叉叉图片
- Android 中，应用程序需要的图片资源如何针对不同屏幕大小手机设计？
- SQL多条件模糊查询解决方案(类似百度搜索)
- 欧几里得算法 模板
- HDU 1171 多重背包
- 数据库sql的函数，sql学习（三）
