« 解出Oracle日志文件中的Redo SQL语句之十 »
Research » http://www.anysql.net/research/my_oracle_logminer_10.html 2007-05-30已花了两周时间搞日志格式研究, 应当收尾了, 这东西长期搞下去没有出路. 没想到仅花了两周时间, 就可以给大家做个演示了. 我在Oracle中运行了以下角本:
SQL> insert into mylog values (‘My Log 10g’,-1,sysdate);
SQL> insert into mylog select object_name, object_id, created
2 from user_objects where rownum < 4;
SQL> update mylog set created = created + 1;
SQL> delete mylog;
SQL> select object_id from user_objects where object_name=’MYLOG’;OBJECT_ID
———-
10576
在运行MyLOG程序的目录下建一个LOGTAB.TXT文件, 包含一行记录(要将第二个值替换成你的测试表的Object ID):
3,10576,MYLOG,
在运行MyLOG程序的目录下建一个LOGCOL.TXT文件, 包含三行记录(应换为你的测试表的表结构记录):
3,1,OBJECT_NAME,VARCHAR2
3,2,OBJECT_ID,NUMBER
3,3,CREATED,DATE
现在在MyLog中解出所有的这些操作:
Start extract redo SQL …
RBA=0×000069.0000095d.0010, XID=0x000a.007.0000009b, RID=AAAClQAAEAAAAKMAAA
INSERT INTO MYLOG ( OBJECT_NAME , OBJECT_ID , CREATED ) VALUES (‘My Log 10g’,-1,’2007-05-30 16:16:33′);RBA=0×000069.00000960.0010, XID=0×0006.001.00000096
RID=AAAClQAAEAAAAKMAAB
INSERT INTO MYLOG ( OBJECT_NAME , OBJECT_ID , CREATED ) VALUES (‘TIME_DIM’,9771,’2006-10-09 21:22:08′);
RID=AAAClQAAEAAAAKMAAC
INSERT INTO MYLOG ( OBJECT_NAME , OBJECT_ID , CREATED ) VALUES (‘P_DEMO’,10267,’2007-03-27 21:17:05′);
RID=AAAClQAAEAAAAKMAAD
INSERT INTO MYLOG ( OBJECT_NAME , OBJECT_ID , CREATED ) VALUES (‘EXT_DATE’,10159,’2007-03-01 16:11:23′);RBA=0×000069.00000962.0010, XID=0×0009.003.00000093
RID=AAAClQAAEAAAAKMAAA
UPDATE MYLOG SET CREATED = ’2007-05-31 16:16:33′ WHERE CREATED = ’2007-05-30 16:16:33′;
RID=AAAClQAAEAAAAKMAAB
UPDATE MYLOG SET CREATED = ’2007-05-31 16:16:33′ WHERE CREATED = ’2007-05-30 16:16:33′;
RID=AAAClQAAEAAAAKMAAC
UPDATE MYLOG SET CREATED = ’2007-05-31 16:16:33′ WHERE CREATED = ’2007-05-30 16:16:33′;
RID=AAAClQAAEAAAAKMAAD
UPDATE MYLOG SET CREATED = ’2007-05-31 16:16:33′ WHERE CREATED = ’2007-05-30 16:16:33′;RBA=0×000069.00000964.0010, XID=0×0001.028.0000009b, RID=AAAClQAAEAAAAKMAAA
DELETE MYLOG WHERE OBJECT_NAME = ’My Log 10g’ AND OBJECT_ID = -1 AND CREATED = ’2007-05-31 16:16:33′;RBA=0×000069.00000964.0010, XID=0×0001.028.0000009b, RID=AAAClQAAEAAAAKMAAB
DELETE MYLOG WHERE OBJECT_NAME = ’TIME_DIM’ AND OBJECT_ID = 9771 AND CREATED = ’2006-10-10 21:22:08′;RBA=0×000069.00000964.0010, XID=0×0001.028.0000009b, RID=AAAClQAAEAAAAKMAAC
DELETE MYLOG WHERE OBJECT_NAME = ’P_DEMO’ AND OBJECT_ID = 10267 AND CREATED = ’2007-03-28 21:17:05′;RBA=0×000069.00000964.0010, XID=0×0001.028.0000009b, RID=AAAClQAAEAAAAKMAAD
DELETE MYLOG WHERE OBJECT_NAME = ’EXT_DATE’ AND OBJECT_ID = 10159 AND CREATED = ’2007-03-02 16:11:23′;
有兴趣的话可以下载玩玩, 要10g的数据库的.
效率如何?如果产品化，效率是很重要的。
尽量少去访问源库, 才是最要的.
效率也是很高的, 当然如果你的数据库写非常历害, 就算shareplex这样的商业软件也是顶不住的.
就这样结束啦, 产品还没有成形哩.
遗憾.
一个人的精力总是有限的,10g的LOG我也搞了一下,就剩最后解析语句了,环境影响也挺大
很不错的东西，还能共享一下源码
不好意思, 这个程序不共享源代码.
支持10gr2的库吗，我试了，好像不行
extract start 2 table=mylog to c:\a.txt
RBA=0×001844.000001a4.0010, XID=0×0006.003.000073cd, RID=AAAAHiAABAAAA67AAQ
UPDATE OBJ_482 SET COL1 = ? WHERE COL1 = ?;
RBA=0×001844.000001a6.0010, XID=0×0008.02d.0000c1f2, RID=AAAAHiAABAAAA67AAR
UPDATE OBJ_482 SET COL1 = ? WHERE COL1 = ?;
RBA=0×001844.000001a8.0010, XID=0×0007.01b.00007ec0, RID=AAAAHiAABAAAA67AAT
UPDATE OBJ_482 SET COL1 = ?,COL2 = ?,COL3 = ?,COL4 = ?,COL5 = ?,COL6 = ? WHERE COL1 = ? AND COL2 = ? AND COL3 = ? AND COL4 = ? AND COL5 = ? AND COL6 = ?;
RBA=0×001844.000001ad.0010, XID=0×0005.021.0000ec83, RID=AAAABEAABAAAAHyAAE
UPDATE OBJ_68 SET COL1 = ?,COL2 = ?,COL3 = ?,COL4 = ?,COL5 = ?,COL6 = ?,COL7 = ?,COL8 = ?,COL9 = ? WHERE COL1 = ? AND COL2 = ? AND COL3 = ? AND COL4 = ? AND COL5 = ? AND COL6 = ? AND COL7 = ? AND COL8 = ? AND COL9 = ?;
RBA=0×001844.000001af.0010, XID=0×0009.011.000089eb, RID=AAAADtAABAAAAZ6AAA
UPDATE OBJ_237 SET COL5 = ? WHERE COL5 = ?;
RBA=0×001844.000001b1.0010, XID=0×0008.000.0000c1f5, RID=AAAMRzAADAAAGbNAB3
INSERT INTO OBJ_50291 (COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8) VALUES (?,?,?,?,?,?,?,?);
RBA=0×001844.000001b3.0010, XID=0×0005.02d.0000ec5a, RID=AAAMMZAADAAAFxQAAI
UPDATE OBJ_49945 SET COL7 = ? WHERE COL7 = ?;
extract start 2 table=mylog to c:\a.txt
table后面用空格, 不是用等于符.
你的这个工具其实很有用
特别是在数据库的容灾方面
因为这样的复制结构 在从库上可以打开状态
在主库生成的归档文件传输到备库上，备库分析成sql语句然后插入
可以做得有点类似oracle的下游stream捕获
作者应该继续产品话这个东西，现在基于日志分析，挖掘的容灾软件，动不动都是几十上百万，如果作者的东西能产品话，我想市场一定是有的。
那些几十上百万的容灾软件客户不一定能接受，毕竟大型的数据库用户都倾向于存储设备的容灾，况且几十上百万的投资也够硬件购置了。
而一些小的应用又非常需要这样的软件来实现，我想有这样需求的人一定很多的。我看好作者的这个东西。
本人拙见
我要做出来, 也得几十万一套的.
这样有前途的事情，应该继续啊。
试了一下，导出的全是类似
RBA=0×000001.00000061.0178, XID=0×0001.009.0000031c
RID=AAAAACAABAAADUWAAA
UPDATE OBJ_154 SET COL12 = ? WHERE COL12 = ?;
RID=AAAAACAABAAADUWAAB
UPDATE OBJ_154 SET COL12 = ? WHERE COL12 = ?;
RID=AAAAACAABAAADUWAAC
UPDATE OBJ_154 SET COL12 = ? WHERE COL12 = ?;
RID=AAAAACAABAAADUWAAD
UPDATE OBJ_154 SET COL12 = ? WHERE COL12 = ?;
RID=AAAAACAABAAADUWAAE
UPDATE OBJ_154 SET COL12 = ? WHERE COL12 = ?;
RID=AAAAACAABAAADUWAAF
UPDATE OBJ_154 SET COL12 = ? WHERE COL12 = ?;
RID=AAAAACAABAAADUWAAG
UPDATE OBJ_154 SET COL12 = ? WHERE COL12 = ?;
RBA=0×000001.00000063.00c0, XID=0×0001.009.0000031c
RID=AAAAACAABAAADUYAAA
UPDATE OBJ_154 SET COL12 = ? WHERE COL12 = ?;
RID=AAAAACAABAAADUYAAB
UPDATE OBJ_154 SET COL12 = ? WHERE COL12 = ?;
这样的语句啊
extract 49725 start 2 to D:\a.txt
extract start 2 table test to D:\b.txt
版本是１０g R1
被一个项目逼急了，9i的二进制日志解析已经全部完成。最后测试的时候发现LogMiner是有Bug的，开始还以为是我们做错，查证之后才知道Oracle的LogMiner是另一家公司做的，Oracle通过日志恢复也不通过LogMiner。