oracle 的flashback version query和logminer
来源:互联网 发布:淘宝网免费开店流程 编辑:程序博客网 时间:2024/06/05 09:53
有时候需要知道已经做过的DML的详细信息,比如说执行的语句,语句提交的时间点等,我们可以通过两种方式查询,logminer和version query。
做了如下操作
创建一个表
SCOTT@DCSSS > create table test_logmnr (id number, val varchar2(10));Table created.
插入两个记录,要注意的是VALUE用的是大写字母,等下看看从logmnr中找回来的是大写还是小写。
SCOTT@DCSSS > insert into test_logmnr VALUES(1,'first');1 row created.SCOTT@DCSSS > insert into test_logmnr VALUES(2,'second');1 row created.SCOTT@DCSSS > commit;Commit complete.
删除记录
SCOTT@DCSSS > delete from test_logmnr;2 rows deleted.SCOTT@DCSSS > commit;Commit complete.SCOTT@DCSSS >
现在操作完毕,先看一下logmnr的查询
用logminer要首先确定log,也就是你要用logminer去查看哪个log
SYS@DCSSS > select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 7915 524288000 2 YES INACTIVE 1.1656E+12 19-JUN-12 2 1 7916 524288000 2 YES INACTIVE 1.1656E+12 19-JUN-12 3 1 7917 524288000 2 NO CURRENT 1.1656E+12 19-JUN-12SYS@DCSSS > select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_---------- ------- ------- -------------------------------------------------------------------------------- --- 3 ONLINE /u11/oradata/DCSSS/redo03_1.rdo NO 3 ONLINE /u11/oradata/DCSSS/redo03_2.rdo NO 2 ONLINE /u11/oradata/DCSSS/redo02_1.rdo NO 2 ONLINE /u11/oradata/DCSSS/redo02_2.rdo NO 1 ONLINE /u11/oradata/DCSSS/redo01_1.rdo NO 1 ONLINE /u11/oradata/DCSSS/redo01_2.rdo NO
看上面的查询,发现当前的log,是group3,但不知道当时做操作的时候log应该是哪个。所以再做下面的查询
SYS@DCSSS > select group#,members,status,to_char(first_time,'YYYY-MM-DD HH24-MI-SS') from v$log; GROUP# MEMBERS STATUS TO_CHAR(FIRST_TIME,---------- ---------- ---------------- ------------------- 1 2 INACTIVE 2012-06-19 07-36-46 2 2 INACTIVE 2012-06-19 10-41-46 3 2 CURRENT 2012-06-19 13-51-46
把date类型用to_char处理一下,就清晰很多了,这样根据时间可以知道,第三行的log就是需要的,再结合v$logfile,得到该log。
接下来,用logminer处理该log。 需要做的步骤是
1. 添加log
2.执行logminer进行分析
3.查询结果
下面逐步进行
1.添加log
SYS@DCSSS > exec dbms_logmnr.add_logfile( logfilename=>'/u11/oradata/DCSSS/redo03_1.rdo' , options=>dbms_logmnr.new);PL/SQL procedure successfully completed.
如果是第一个添加的log文件,options部分是dbms_logmnr.new。
如果是增加多个文件,从第二个开始,options部分是dbms_logmnr.addfile
2.执行logminer进行分析
SYS@DCSSS > exec dbms_logmnr.start_logmnr( options=>dbms_logmnr.dict_from_online_catalog);PL/SQL procedure successfully completed.
注意,这里可以加上starttime 和 endtime参数,这样如果redo太大,我们可以只分析某个时间段
3.查询结果
查询结果要查询v$logmnr_contents
SYS@DCSSS > SELECT TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') AS TIME , USERNAME , SQL_REDO,TABLE_name FROM V$LOGMNR_CONTENTS WHERE USERNAME='SCOTT' and table_name='TEST_LOGMNR' ORDER BY TIME;TIME USERNAME SQL_REDO TABLE_NAME------------------- ---------- -------------------------------------------------------------------------------- ---------------2012-06-19 15:00:41 SCOTT create table test_logmnr (id number, val varchar2(10)); TEST_LOGMNR2012-06-19 15:01:52 SCOTT insert into "SCOTT"."TEST_LOGMNR"("ID","VAL") values ('1','first'); TEST_LOGMNR2012-06-19 15:02:10 SCOTT insert into "SCOTT"."TEST_LOGMNR"("ID","VAL") values ('2','second'); TEST_LOGMNR2012-06-19 15:02:25 SCOTT delete from "SCOTT"."TEST_LOGMNR" where "ID" = '1' and "VAL" = 'first' and ROWID TEST_LOGMNR = 'AABPSQAANAAAOY0AAA';2012-06-19 15:02:25 SCOTT delete from "SCOTT"."TEST_LOGMNR" where "ID" = '2' and "VAL" = 'second' and ROWI TEST_LOGMNR D = 'AABPSQAANAAAOY0AAB';
上面是通过 logminer来查看,如果想快速的查看,可以通过flashback version query来查询。原理就是查询undo数据,但是一个db的undo保存期限是有一定时间的,而且undo的空间也有限,所以这种情况下,有可能因为时间太久等查不到,具体查询如下。
1. 对test_logmnr 执行一个DML
SYS@DCSSS > INSERT INTO SCOTT.TEST_LOGMNR VALUES(9,'NINE');1 row created.SYS@DCSSS > COMMIT;Commit complete.
2. 进行version query
SYS@DCSSS > SELECT ID,VAL,VERSIONS_STARTTIME,VERSIONS_ENDTIME,VERSIONS_OPERATION FROM SCOTT.TEST_LOGMNR 2 VERSIONS BETWEEN TIMESTAMP 3 TO_TIMESTAMP('2012-06-19 16:59:00','YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2012-06-19 17:00:00','YYYY-MM-DD HH24:MI:SS') 4 ORDER BY VERSIONS_STARTTIME 5 / ID VAL VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_OPERATION---------- ---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- 9 NINE 19-JUN-12 04.59.29 PM I 8 eight
- oracle 的flashback version query和logminer
- Oracle Flashback Version Query
- Flashback Version Query和Flashback Transaction Query!
- oracle: flashback version query示例
- Flashback Query, Flashback Version Query
- 学习logminer和Flashback
- [Oracle 9i & 10g] Flashback Query, Flashback Version Query, Flashback Table, etc
- Flashback version query and Flashback transaction query
- Flashback Version Query && Flashback Transaction Query
- Flashback Query Flashback Version Query Flashback Table Flashback Transaction
- Oracle Flashback之Flashback Query
- Oracle Flashback之Flashback Query
- Oracle flashback之flashback query
- Flashback version Query 找到表,记录最后提交的开始和最后scn号
- flashback六大技术之flashback version query
- Oracle中Flashback Query的使用
- oracle flashback query
- Oracle Flashback Transaction Query
- 学习2
- linq实现Left join
- Hibernate入门BLOG [五、hibernate的组件]
- ADROID 2.2 语言定制
- VC编译的exe程序, Debug版会有重定位表, Release版就没有重定位表
- oracle 的flashback version query和logminer
- JavaScript学习—原型和继承
- 一个奇怪的PE文件
- TabWidget去除底部下划线
- EXCEL常用公式(一)
- Hibernate入门BLOG [六、Hibernate的关键字总结]
- Selenium2 Firefox缓存太大导致找不到element问题
- Activity的生命周期
- 浅谈C#托管程序中的资源释放问题