AnySQL.net English

    Let's review the first compare method (by table). We provide the table list in the configuration file, it is not so convenient if we have a lot of tables need to be compared. Usually we need to compare all the tables with the same name across multiple databases, I will introduce a wild char to replace the table list, this utility will find out the tables which reside on all the databases, and perform the comparation.

    Take a look at the following configuration file, it will compare all the tables which resides on both the databases.

TABLE: anysql/anysql@prod, scott/tiger@test | *

    There are totally 4 tables which reside on both the databases. Let's check the compare log file.

Comparing structure by TABLE BONUS ...
Comparing structure by TABLE DEPT ...
Comparing structure by TABLE EMP ...
     [MISMATCH] ENAME VARCHAR2(20) :
                       scott@test
     [MISMATCH] ENAME VARCHAR2(30) :
                       anysql@prod
     [MISSING ] COL_TEST :
               anysql@prod
Comparing structure by TABLE SALGRADE ...

    The wild char make this script more useful. By this utility, I identified out a lot of inconsistent tables in our system, and got very good feedbacks from my leaders.

    I spent some time in researching the Oracle log file format. Previous research was done about two years ago for Oracle 9i. Fortunately I did get something for these days research. And wrote a tool called "MyLOG" for a demo utility. I run the following SQLs in the 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

    Create a text file named "LOGTAB.TXT", contain the following lines (The second column is the object id of the table).

3,10576,MYLOG,

    And create a text file named "LOGCOL.TXT", contain the following lines (The second column is the column storage order number, the SETCOL# column of COL$).

3,1,OBJECT_NAME,VARCHAR2
3,2,OBJECT_ID,NUMBER
3,3,CREATED,DATE

    Now I run the MyLOG to extract the redo SQLs.

LOG> extract start 2 table 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';

    You can download the windows binary. Only valid for 10g log file. Row chain and row migrating is not processed yet, and there must be a lot of bugs.

    I got some requests of data recovery by drop operation, including drop tables and drop tablespaces. For this kind of recovery, the most complex step is to map the table with the data object id found from the remaining data files. For example, if you move a table, oracle actually allocate new extent and assign a new data object id to the table, and the old data object id still exists in the data file, when you have a lot of this kind of operation (move, drop, create), you will get more and more data object id from data files than the table number you required. One time, the customer said there are 400 tables in their application, but from the data files they finally get more than 1500 data object ids.

    Most of the recovery time is spent on the mapping of the data object id with the table name. But now, if you have the oracle log file (online log or archive log) exists, I can use MyLOG utility to find out the delete operation performed on SYS.OBJ$ table, then got the mapping of the data object id with table name as quickly as possible. So next time when you drop something by mistake, please back up the data file and the log file immediately for quick recovery.

    Add the following lines to file "LOGTAB.TXT" for MyLOG.

9999,18,OBJ$,

    Add the following lines to file "LOGCOL.TXT" for MyLOG.

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

    Then open the oracle log file with MyLOG utility, issue command "EXTRACT START 2 TABLE OBJ$", then you will get the lines as following :

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;

    I don't know whether Oracle's log miner can extract the SQLs for system dictionary tables.

    After announcing the test version of offline oracle log miner for 10g, I spent a few day's free time in testing and bug fixing. Also I try to modify the code to support 8i/19 version oracle log files. Fortunately it's not so difficult thing to make the code work for 8i/9i, only few code need to be changed. Now you can download the MyLOG binary for oracle 8i/9i.

    No new command added, all the same as MyLOG 10g version.

    I also provide the binary file for RedHat (x86) and Solaris (Sparc64), for other platforms, I haven't the compile environments. But you can open log file from any platform by setting the BYTE_ORDER option. For example, I open an Solaris oracle log file on windows computer.

LOG> set BYTE_ORDER BIG
BYTE_ORDER = BIG
LOG> open c:\mydul\utility\gbcust1_24112.arc
DBID = 0x9d671cf9 = 2640780537
GROUP      = 8, SEQUENCE   = 24112
File Type  = 2, Next Block = 16059
Start SCN  = 0x031f.05c00824 = 3431775340580
Start Time = 2006-08-23 19:08:21
End SCN    = 0x031f.05c00990 = 3431775340944
End Time   = 2006-08-23 19:08:58
LOG>

    Do not use this script for any data recovery, it's just for testing, and no replace of Oracle's log miner work.

    Oracle will release 11g soon, from the white paper, we can see that Oracle made some big changes to the compress tables, it will support for OLTP system, and help you to save about 50% of the storage. Because of CPU are faster and faster, but IO are really slowly improved compare to the CPU technology. So I have to enable the compress table support for my data recovery tool -- AUL/MyDUL. This is really a big challenge for me.

    I spent some time to continue the oracle log format research, and now I can basically extract the SQL from the redo log file (Sample). Because Oracle 11g will support open-recovery standby database, so this research have less and less value to me to convert it to a useful disaster recovery tools. I have very less resource and cannot get it done by personal. So I decide to open source it, anybody have experience of the open source? I want somebody to join the development, or anybody have fun?

    Is open source good for a people who are busy in making the live.