AnySQL.net English

    We found an relink error when installing Oracle 9.2.0.7 on Solaris platform, the second time we found relink error for libsrvm module. However it did not report any error when just "relink oracle" or "relink client", just appear when you type "relink all" command. Detailed error messages listed here:

ld: fatal: file ${ORACLE_HOME}/lib/libskgxn9.so: wrong ELF class: ELFCLASS64
ld: fatal: File processing errors. No output written to libsrvm.so
*** Error code 1
make: Fatal error: Command failed for target `libsrvm.so'

    I used "file" command to check the properties of file "${ORACLE_HOME}/lib/libskgxn9.so", found that it's an 64 bit library, while libsrvm.so is a 32 bit dynamic library, that's why the error is reported. So I make a small change to "env_rdbms.mk" under $ORACLE_HOME/srvm/lib directory as following:

# EXSYSLIBS = -lposix4 -lm -lskgxn9 #original line
EXSYSLIBS = -lposix4 -lm

    Now it works well when I issue "make -f ins_rdbms.mk isrvm" command to compile the single module, and also works well when typing "relink all".

    AUL V4 support Oracle BLOB/CLOB data types compared to AUL V3. I started this enhancement since Dec, 2006. And release the AUL V4 beta version at the beginning of Jan, 2007. I supposed to announce the formal release after a successful recovery of LOB data, it's time to announce this great news.

    Few days ago, AUL V4 recovered a LOB table of my customer, totally 12292 images, PDF/DOC files recovered, and the result was perfect, just found some white PDF file, it should not be AUL V4's problem. So I formally announce the retire of AUL V3, and the latest version is 4.0.2.

    I want to give special thanks to my customers, initially we use the beta version for recovery, and get some bugs. My customer gave me enough debug information and enough support for the fixing work. This customer read across my site carefully to avoid asking too much question of the usage of AUL software. Mainly two bugs are fixed :

• 1, Cannot recover LOB value stored in chained or migrated row.
    Reason: did not pass the correct LOB index information to the subroutine.
• 2, Cannot recover LOB value except for the first LOB column of table.
    Reason: did not get correct LOB index information for the LOB columns.

    I have stopped download of AUL V4, the old link has been pointed to the AUL 4 binary. The next version will support compress tables & indexes, but still no time schedule for it.

    On Oracle 10g, I got the following wait of two sessions, session 32 blocked session 29. It's interesting and dangerous operation in OLTP system.

SQL> SELECT SID, EVENT FROM V$SESSION
  2  WHERE USERNAME='ANYSQL';

       SID EVENT
---------- -------------------------------------
        29 library cache lock
        32 SQL*Net message from client

    What I executed in session 32 is :

SQL> exec dbms_mview.begin_table_reorganization('ANYSQL','T_IOT');

PL/SQL procedure successfully completed.

    And what it's executing in session 29 is a delete statement, which was blocked by library cache lock :

SQL> delete t_iot;

    Seems some of Oracle new features are dangerous, however if I submit a commit statement in session 32, then session 29 will continue to execute, the library cache lock will go away. Why not Oracle issue a default commit in this procedure?

    We could add enable or disable storage in row attribute for Oracle CLOB/BLOB columns, by default it's enabled. If the LOB value is less than 4000 bytes, the LOB value will be stored inline the row piece, else both of these type will store the value in LOB segment. Is there any difference between them when LOB value is stored out of row? Let's start a demo, I create this demo on a 10g database, create a demo table contains two CLOB columns, and populate them with the same value, the table just contains one row, as following :

SQL> create table t_lobtest (col1 clob, col2 clob)
  2  lob (col2) store as (disable storage in row);

Table created.

ASQL> SELECT DBMS_LOB.GETLENGTH(COL1) COL1_LEN,
    2        DBMS_LOB.GETLENGTH(COL2) COL2_LEN
    3 FROM T_LOBTEST;

COL1_LEN COL2_LEN
-------- --------
    7989     7989

1 rows returned.

    The LOB value length is greater than 4000, so the value are stored out of row for both columns, then let's select them in SQL*Plus and do a statistics trace, you will see that consistent gets for the two LOB columns are different.

SQL> select col1 from t_lobtest;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          4  physical reads
          0  redo size
       1213  bytes sent via SQL*Net to client
        829  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select col2 from t_lobtest;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         19  consistent gets
          4  physical reads
          0  redo size
       1141  bytes sent via SQL*Net to client
        781  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

    However the physical reads are both 4 due to direct path read of LOB, but for consistent gets, the disabled column has more gets than the enabled column. According to my research of LOB value, for LOB columns with enable storage in row attribute, if the chunks are less than 12, then the chunk address will be stored in row piece ( LOB_ID, CHUNK1, CHUNK2, ..., CHUNK12 ), and the LOB index will not store the chunk address which already stored in the row piece. for LOBs with disable storage in row attribute, only LOB_ID is stored in the row piece, every chunk addresses are stored in the LOB index. For this case, when accessing column "COL1", no LOB index access is required, but for column "COL2", oracle have to touch LOB index's block, that's where the extra consistent gets come from.

    For high execution frequency SQLs, take this into account when tuning. You can define the proper chunk size to reduce the entries in LOB index for performance improvement.

    I did some research on the materialized view and materialized view log, by trace the refresh procedure of Oracle, I found out how materialized view works. Then I started to write a Perl script to replicate rows from Oracle to MySQL based on materialized view log which help me to capture the changed rows, it took me half of day to get the first version done. The interesting thing is that it worked fine when I were testing it. In this release, the table name can be different between source and target database, but the column name must be same (column order is not important), and table must have a primary key, and must create a materialized view log with "with primary key, sequence" option on the table.

    Then we need to write a configuration file to maintain the tables which need to be replicated, it contains four columns (split by pouch "#") :

1, source table name in Oracle.
2, primary key columns, split by ",".
3, the materialized view log table name, usually "MLOG$_tablename".
4, the target table name in MySQL.

    Now start testing by running the script refresh_mysql.pl :

C:\AnySQL>perl refresh_mysql.pl -s anysql#anysql#Oracle:test -t root#mysql#mysql:test -c myrefresh.conf
02/13 12:52:25 - 1 tables will be processed.
02/13 12:53:06 - Start replication from T_MVLOG to T_MVLOG with sequence 10196 to 10199 ...
02/13 12:53:07 - End replication from T_MVLOG to T_MVLOG with sequence 10196 to 10199.
02/13 12:53:26 - Start replication from T_MVLOG to T_MVLOG with sequence 10200 to 10203 ...
02/13 12:53:27 - End replication from T_MVLOG to T_MVLOG with sequence 10200 to 10203.
02/13 12:54:11 - Start replication from T_MVLOG to T_MVLOG with sequence 10204 to 10204 ...
02/13 12:54:12 - End replication from T_MVLOG to T_MVLOG with sequence 10204 to 10204.
02/13 12:56:13 - Start replication from T_MVLOG to T_MVLOG with sequence 10205 to 10207 ...
02/13 12:56:14 - End replication from T_MVLOG to T_MVLOG with sequence 10205 to 10207.
02/13 13:11:41 - Start replication from T_MVLOG to T_MVLOG with sequence 10208 to 10211 ...
02/13 13:11:42 - End replication from T_MVLOG to T_MVLOG with sequence 10208 to 10211.

    I inserted some rows to the demo table in Oracle, and perform a query to verify :

SQL> SELECT * FROM T_MVLOG;

COL1                 COL2
-------------------- ------------------------------
MLOG$_T_MVLOG        TABLE
MV_T_MVLOG2          TABLE
RUPD$_T_MVLOG        TABLE
T_MVLOG              TABLE

    After waiting 3 to 5 seconds, I query the demo table in MySQL, and get the expected result, it's really working .

mysql> select * from t_mvlog;
+---------------+-------+
| COL1          | COL2  |
+---------------+-------+
| MLOG$_T_MVLOG | TABLE |
| MV_T_MVLOG2   | TABLE |
| RUPD$_T_MVLOG | TABLE |
| T_MVLOG       | TABLE |
+---------------+-------+
4 rows in set (0.00 sec)

    The next to do is to perform more test, and test under higher load pressure. And there are lots to be improved. I don't tell you how to install the Perl and DBD-Oracle DBD-MySQL here, that's not my job.