AnySQL.net English

    ociuldr is a free OCI utility to extract table rows from Oracle database to flat file. In previous release, when extracting long type column, it will read maximum 32767 bytes, but now I have add a new command line option "long=maxbytes" to control the maximum length to extract. This could save some client memory when extract short long column.

    The new default maximum value is 4000, for example:

$ociuldr user=anysql/anysql@prod sql=test.sql
    4100 bytes allocated for column COL1 (1)
  400100 bytes allocated for column COL2 (2)

    Test the new command line option, you will found the difference.

$ociuldr user=anysql/anysql@prod sql=test.sql long=8000
    4100 bytes allocated for column COL1 (1)
  800100 bytes allocated for column COL2 (2)

    New binary have been uploaded, you could download or update it.

    How to get the first SCN, next SCN, first time, next time of archived log? the first way should be check rows from V$LOG_HISTORY or V$ARCHIVED_LOG, but these view just keep information of few latest archived logs, it depend on the control file record keep time setting, in quite a few databases, it did not keep long time than one week. The another ways is to dump the log file header, the check the trace file, you may find the following lines:

FILE HEADER:
......
Low scn: 0x031f.05c00824 08/23/2006 19:08:21
Next scn: 0x031f.05c00990 08/23/2006 19:08:58
......

    But dump log is quite complex, I have spent some time on log format, so just spent few minutes to write a new utility "lslog" to get these information, it's native C program, no Oracle environment is required:

$> lslog file=PROD_26375.arc
26375,3436537083312,2006-11-12 18:13:57,3436537256045,2006-11-12 21:09:24

    It print out five fields split by "," as following:

1, Log Sequence
2, Start SCN
3, Start Time
4, Next Log Start SCN
5, Next Log Start Time

    This feature is platform independent, and support 8i/9i/10g's archive log file. as following:

SQL> SELECT FIRST_CHANGE#,FIRST_TIME,NEXT_CHANGE#
  2  FROM V$LOG_HISTORY WHERE SEQUENCE#=26375;

   FIRST_CHANGE# FIRST_TIME              NEXT_CHANGE#
---------------- ------------------- ----------------
   3436537083312 2006-11-12 18:13:57    3436537256045

00:36:55 SQL> select FIRST_TIME from  v$log_history where SEQUENCE#=26376;

FIRST_TIME
-------------------
2006-11-12 21:09:24

    Now powerful utility, download it?

    I add a new command line option for my free text unload utility (ociuldr):

table = table name in the sqlldr control file

    The default value is NULL, it will not generate SQLLDR control file for you. If you define a value for this option, it will create a file named "{value}_sqlldr.ctl" which contains the SQLLDR instructors, you could use it to load the text file into new database.

C:\TEMP>ociuldr user=anysql/anysql query="select * from tab" table=tab

       0 rows exported at 2006-11-16 21:49:12
       5 rows exported at 2006-11-16 21:49:12

    Now we can check the contents of SQLLDR control file:

C:\TEMP>type tab_sqlldr.ctl
--
-- Generated by OCIULDR
--
OPTIONS(BINDSIZE=4194304,READSIZE=4194304,ERRORS=-1,ROWS=50000)
LOAD DATA
INFILE 'uldrdata.txt' "STR X'0a'"
INTO TABLE tab
FIELDS TERMINATED BY X'2c' TRAILING NULLCOLS
(
  TNAME CHAR(30),
  TABTYPE CHAR(7),
  CLUSTERID CHAR(40)
)

    This is very useful improvement, usually we cannot remember correctly how to write SQLLDR control file. I often use this utility now.

    The ociuldr binary is avaiable in the zip file, and the latest source code is also available.

    One of my friends use ociuldr to unload 5 billion rows to text file, and finally got a 50GB text file. It's hard to process such a large text file by perl. And he finally made a suggestion to let ociuldr generate few small files. I added this option to implement this feature, a batch in ociuldr means 500000 rows, if you specified a value for this option, it will switch file name after specified batch, the default value is 0 (generate all in one file).

    If you specify the batch option, please also specify the file option for file name pattern (use %d to specify the file sequence, start from 1). Watch the following example:

SQL> SELECT COUNT(*) FROM T_OBJID;

  COUNT(*)
----------
   2430720

    Unload with the following command and check log output:

C:\TEMP>ociuldr user=anysql/anysql query="select * from t_objid" batch=2 file=test_%d.txt

       0 rows exported at 2006-11-23 21:09:19
  500000 rows exported at 2006-11-23 21:09:20
1000000 rows exported at 2006-11-23 21:09:21
         output file test_1.txt closed at 1000000 rows.
  500000 rows exported at 2006-11-23 21:09:21
1000000 rows exported at 2006-11-23 21:09:22
         output file test_2.txt closed at 1000000 rows.
  430720 rows exported at 2006-11-23 21:09:22
         output file test_3.txt closed at 430720 rows.

    Please download the latest binary file, or download source code and compile it. For Linux/Unix compilation, specify the 64-bit IO compile option for large file support.

    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.