AnySQL.net

    我在Oracle中运行了以下语句:

SQL> CREATE TABLE MYLOG10G (COL1 NUMBER, COL2 VARCHAR2(20), COL3 DATE);
SQL> insert into mylog10g values (100, 'Fangxin', sysdate);
SQL> update mylog10g set col2='FANGXIN' where col1=100;
SQL> delete mylog10g where col1=100;
SQL> select object_id from dba_objects where object_name='MYLOG10G';

OBJECT_ID
----------
     10566

    我在MyLOG中试图解出原始的SQL:

LOG> extract start 2 table mylog10g
Start extract redo SQL ...
RBA=0x000066.000000b7.0010,  XID=0x0005.005.00000092, RID=AAAClGAAEAAAAKGAAA
    INSERT INTO MYLOG10G ( COL1 , COL2 , COL3 ) VALUES (100,'Fangxin','2007-05-29 10:50:32');

RBA=0x000066.000000ba.0010 LEN=0x0238 VLD=0x0d
    Chgid=1 Length=164, Piece=7
        Piece=2 Length=60, Size=60
        Piece=3 Length=20, Size=20
        Piece=4 Length=28, Size=28
        Piece=5 Length=2, Size=4
        Piece=6 Length=4, Size=4
        Piece=7 Length=8, Size=8
    Chgid=2 Length=60, Piece=2
        Piece=2 Length=32, Size=32
    Chgid=3 Length=72, Piece=4
        Piece=2 Length=20, Size=20
        Piece=3 Length=16, Size=16
        Piece=4 Length=4, Size=4
    Chgid=4 Length=204, Piece=9
        Piece=2 Length=20, Size=20
        Piece=3 Length=72, Size=72
        Piece=4 Length=2, Size=4
        Piece=5 Length=20, Size=20
        Piece=6 Length=28, Size=28
        Piece=7 Length=2, Size=4
        Piece=8 Length=4, Size=4
        Piece=9 Length=8, Size=8

RBA=0x000066.000000bc.0010,  XID=0x0009.02c.00000090, RID=AAAClGAAEAAAAKGAAA
    DELETE MYLOG10G WHERE  COL1 =  100 AND  COL2 =  'FANGXIN' AND  COL3 =  '2007-05-29 10:50:32';

    Update操作没被解出来, 原因呢, 是因为10g中这个Update有了新的记录类型.

LOG> oradump block 0xba
Start extract redo SQL ...
RBA=0x000066.000000ba.0010 LEN=0x0238 VLD=0x0d
     Chgid=1 Length=164, Piece=7, OP=11.19
     Chgid=2 Length=60, Piece=2, OP=5.2
     Chgid=3 Length=72, Piece=4, OP=5.4
     Chgid=4 Length=204, Piece=9, OP=5.1

    做这个东东怎么就这么难呢?

    初步完成了对Quick Multi-Insert操作的解释, 虽然在Oracle中是用一句话来进行Insert的, 但解出来时还是折分成一个一个的Insert语句了. 来看一下解出来的结果:

RBA=0x000069.0000008d.0010,  XID=0x0005.006.00000093
   RID=AAAClAAAEAAAAJ2AAA
   INSERT INTO EMP ( EMPNO , ENAME , JOB , MGR , HIREDATE , SAL , COMM , DEPTNO ) VALUES (7369,'SMITH','CLERK',7902,'1980-12-17 00:00:00',800, NULL ,20);
   RID=AAAClAAAEAAAAJ2AAB
   INSERT INTO EMP ( EMPNO , ENAME , JOB , MGR , HIREDATE , SAL , COMM , DEPTNO ) VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20 00:00:00',1600,300,30);
   RID=AAAClAAAEAAAAJ2AAC
   INSERT INTO EMP ( EMPNO , ENAME , JOB , MGR , HIREDATE , SAL , COMM , DEPTNO ) VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22 00:00:00',1250,500,30);
   RID=AAAClAAAEAAAAJ2AAD
   INSERT INTO EMP ( EMPNO , ENAME , JOB , MGR , HIREDATE , SAL , COMM , DEPTNO ) VALUES (7566,'JONES','MANAGER',7839,'1981-04-02 00:00:00',2975, NULL ,20);
   RID=AAAClAAAEAAAAJ2AAE
   INSERT INTO EMP ( EMPNO , ENAME , JOB , MGR , HIREDATE , SAL , COMM , DEPTNO ) VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28 00:00:00',1250,1400,30);
   RID=AAAClAAAEAAAAJ2AAF
   INSERT INTO EMP ( EMPNO , ENAME , JOB , MGR , HIREDATE , SAL , COMM , DEPTNO ) VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01 00:00:00',2850, NULL ,30);
   RID=AAAClAAAEAAAAJ2AAG
   INSERT INTO EMP ( EMPNO , ENAME , JOB , MGR , HIREDATE , SAL , COMM , DEPTNO ) VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09 00:00:00',2450, NULL ,10);
   RID=AAAClAAAEAAAAJ2AAH
   INSERT INTO EMP ( EMPNO , ENAME , JOB , MGR , HIREDATE , SAL , COMM , DEPTNO ) VALUES (7788,'SCOTT','ANALYST',7566,'1987-04-19 00:00:00',3000, NULL ,20);
   RID=AAAClAAAEAAAAJ2AAI
   INSERT INTO EMP ( EMPNO , ENAME , JOB , MGR , HIREDATE , SAL , COMM , DEPTNO ) VALUES (7839,'KING','PRESIDENT', NULL ,'1981-11-17 00:00:00',5000, NULL ,10);
   RID=AAAClAAAEAAAAJ2AAJ
   INSERT INTO EMP ( EMPNO , ENAME , JOB , MGR , HIREDATE , SAL , COMM , DEPTNO ) VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08 00:00:00',1500,0,30);
   RID=AAAClAAAEAAAAJ2AAK
   INSERT INTO EMP ( EMPNO , ENAME , JOB , MGR , HIREDATE , SAL , COMM , DEPTNO ) VALUES (7876,'ADAMS','CLERK',7788,'1987-05-23 00:00:00',1100, NULL ,20);
   RID=AAAClAAAEAAAAJ2AAL
   INSERT INTO EMP ( EMPNO , ENAME , JOB , MGR , HIREDATE , SAL , COMM , DEPTNO ) VALUES (7900,'JAMES','CLERK',7698,'1981-12-03 00:00:00',950, NULL ,30);
   RID=AAAClAAAEAAAAJ2AAM
   INSERT INTO EMP ( EMPNO , ENAME , JOB , MGR , HIREDATE , SAL , COMM , DEPTNO ) VALUES (7902,'FORD','ANALYST',7566,'1981-12-03 00:00:00',3000, NULL ,20);
   RID=AAAClAAAEAAAAJ2AAN
   INSERT INTO EMP ( EMPNO , ENAME , JOB , MGR , HIREDATE , SAL , COMM , DEPTNO ) VALUES (7934,'MILLER','CLERK',7782,'1982-01-23 00:00:00',1300, NULL ,10);

    从数据库中查询出ROWID进行比对一下:

SQL> SELECT EMPNO,ROWID FROM ANYSQL.EMP;

     EMPNO ROWID
---------- ------------------
      7369 AAAClAAAEAAAAJ2AAA
      7499 AAAClAAAEAAAAJ2AAB
      7521 AAAClAAAEAAAAJ2AAC
      7566 AAAClAAAEAAAAJ2AAD
      7654 AAAClAAAEAAAAJ2AAE
      7698 AAAClAAAEAAAAJ2AAF
      7782 AAAClAAAEAAAAJ2AAG
      7788 AAAClAAAEAAAAJ2AAH
      7839 AAAClAAAEAAAAJ2AAI
      7844 AAAClAAAEAAAAJ2AAJ
      7876 AAAClAAAEAAAAJ2AAK
      7900 AAAClAAAEAAAAJ2AAL
      7902 AAAClAAAEAAAAJ2AAM
      7934 AAAClAAAEAAAAJ2AAN

    好象是对的了, 还留下一个已知的Quick Multi-Delete操作和未知类型的操作需要解释了.

    已花了两周时间搞日志格式研究, 应当收尾了, 这东西长期搞下去没有出路. 没想到仅花了两周时间, 就可以给大家做个演示了. 我在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=0x000069.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=0x000069.00000960.0010,  XID=0x0006.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=0x000069.00000962.0010,  XID=0x0009.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=0x000069.00000964.0010,  XID=0x0001.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=0x000069.00000964.0010,  XID=0x0001.028.0000009b, RID=AAAClQAAEAAAAKMAAB
   DELETE MYLOG WHERE  OBJECT_NAME =  'TIME_DIM' AND  OBJECT_ID =  9771 AND  CREATED =  '2006-10-10 21:22:08';

RBA=0x000069.00000964.0010,  XID=0x0001.028.0000009b, RID=AAAClQAAEAAAAKMAAC
   DELETE MYLOG WHERE  OBJECT_NAME =  'P_DEMO' AND  OBJECT_ID =  10267 AND  CREATED =  '2007-03-28 21:17:05';

RBA=0x000069.00000964.0010,  XID=0x0001.028.0000009b, RID=AAAClQAAEAAAAKMAAD
   DELETE MYLOG WHERE  OBJECT_NAME =  'EXT_DATE' AND  OBJECT_ID =  10159 AND  CREATED =  '2007-03-02 16:11:23';

    有兴趣的话可以下载玩玩, 要10g的数据库的.

    接到过几次由于Drop误操作引起的恢复请求, 在那种情况下, 最主要的问题是没有办法定位被Drop的表的Data Object ID的值. 由于Oracle在执行Drop操作时并不真正地对数据文件清行清理(这是我们能恢复的前提), 从儿当你的应用中有很多的临时表, 并经常Drop来Drop去的话, 在恢复时进行整个数据文件扫描, 会发现很多的Data Object ID, 而你不知道那个是包括了有用的数据.

    事实上这种情况下, 恢复的主要式作是去验证这些Data Object ID是不是包含了真正的数据. 现在我们有了MyLOG, 到可以用来做这件事, 当前要求你能提供执行Drop误操作时所用的联机日志或归档日志文件.

    在LOGTAB.TXT文件中加入下面一行:

9999,18,OBJ$,

    在LOGCOL.TXT文件中加入下面几行:

9999,1,OBJ#,NUMBER
9999,2,DATAOBJ#,NUMBER
9999,3,OWNER#,NUMBER
9999,4,NAME,VARCHAR2
9999,5,NAMESPACE,NUMBER
9999,6,SUBNAME,VARCHAR2
9999,7,TYPE#,NUMBER
9999,8,CTIME,DATE
9999,9,MTIME,DATE
9999,10,STIME,DATE
9999,11,STATUS,NUMBER
9999,12,REMOTEOWNER,VARCHAR2
9999,13,LINKNAME,VARCHAR2
9999,14,FLAGS,NUMBER
9999,15,OID$,RAW
9999,16,SPARE1,NUMBER
9999,17,SPARE2,NUMBER
9999,18,SPARE3,NUMBER
9999,19,SPARE4,VARCHAR2
9999,20,SPARE5,VARCHAR2
9999,21,SPARE6,DATE

    用MyLOG打开联机或归档日志文件, 执行"EXTRACT START 2 TABLE OBJ$"命令, 你很容易从解出来的Redo SQL中找到有用的信息:

RBA=0x000069.000010b4.00b0,  XID=0x0009.016.00000093, RID=AAAAASAABAAAGsmAAI
   DELETE OBJ$ WHERE  OBJ# =  10566 AND  DATAOBJ# =  10566 AND  OWNER# =  25 AND  NAME =  'MYLOG10G' AND  NAMESPACE =  1 AND  SUBNAME =   NULL  AND  TYPE# =  2 AND  CTIME =  '2007-05-29 10:50:12' AND  MTIME =  '2007-05-29 10:50:12' AND  STIME =  '2007-05-29 10:50:12' AND  STATUS =  1 AND  REMOTEOWNER =   NULL  AND  LINKNAME =   NULL  AND  FLAGS =  0 AND  OID$ =   NULL  AND  SPARE1 =  6 AND  SPARE2 =  1;

    不知道Oracle的Log Miner是不是能解出这些对数据字典的操作?

    在LOGTAB.TXT中加入如下行:

10000,21,COL$,

    在LOGCOL.TXT中加入如下行, 不过由于COL$是Cluster表, 因此这里列出来的比真实的表列数少一列, 刚好少Cluster的那列:

10000,1,COL#,NUMBER
10000,2,SEGCOL#,NUMBER
10000,3,SEGCOLLENGTH,NUMBER
10000,4,OFFSET,NUMBER
10000,5,NAME,VARCHAR2
10000,6,TYPE#,NUMBER
10000,7,LENGTH,NUMBER
10000,8,FIXEDSTORAGE,NUMBER
10000,9,PRECISION#,NUMBER
10000,10,SCALE,NUMBER
10000,11,NULL$,NUMBER
10000,12,DEFLENGTH,NUMBER
10000,13,SPARE6,DATE
10000,14,INTCOL#,NUMBER
10000,15,PROPERTY,NUMBER
10000,16,CHARSETID,NUMBER
10000,17,CHARSETFORM,NUMBER
10000,18,SPARE1,NUMBER
10000,19,SPARE2,NUMBER
10000,20,SPARE3,NUMBER
10000,21,SPARE4,VARCHAR2
10000,22,SPARE5,VARCHAR2
10000,23,DEFAULT$,LONG

    没有办法知道这个操作是对那个对象进行的, 因为OBJ#列的变更不记录在这儿. 终于明白为什么Shareplex不支持Cluster表了, 不过Single Hash Cluster表应当是可以支持的.

RBA=0x000069.000010b0.0018,  XID=0x0009.016.00000093, RID=AAAAACAABAAAGq4AAR
   DELETE COL$ WHERE  COL# =  3 AND  SEGCOL# =  3 AND  SEGCOLLENGTH =  7 AND  OFFSET =  0 AND  NAME =  'COL3' AND  TYPE# =  12 AND  LENGTH =  7 AND  FIXEDSTORAGE =  0 AND  PRECISION# =   NULL  AND  SCALE =   NULL  AND  NULL$ =  0 AND  DEFLENGTH =   NULL  AND  SPARE6 =   NULL  AND  INTCOL# =  3 AND  PROPERTY =  0 AND  CHARSETID =  0 AND  CHARSETFORM =  0 AND  SPARE1 =  0 AND  SPARE2 =  0 AND  SPARE3 =  0;

    所有的Cluster表都存在这个问题, 因此大部份系统表将不能正常解出Redo SQL.