AnySQL.net English

    Create the demo table and materialized view log in Oracle:

CREATE TABLE T_OBJECTS AS SELECT * FROM DBA_OBJECTS WHERE ROWNUM < 1;
ALTER TABLE T_OBJECTS MODIFY OBJECT_ID NOT NULL;
ALTER TABLE T_OBJECTS ADD PRIMARY KEY (OBJECT_ID);
CREATE MATERIALIZED VIEW LOG ON T_OBJECTS WITH PRIMARY KEY, SEQUENCE;

    Create the relative demo table in MySQL:

CREATE TABLE t_objects (
  OBJECT_ID decimal(10,0) NOT NULL,  OWNER varchar(30) ,
  OBJECT_NAME varchar(128) ,   SUBOBJECT_NAME varchar(30) ,
  DATA_OBJECT_ID decimal(10,0) ,  OBJECT_TYPE varchar(18) ,
  CREATED datetime ,   LAST_DDL_TIME datetime ,
  TIMESTAMP varchar(19) ,  STATUS varchar(7) ,
  TEMPORARY varchar(1) ,  GENERATED varchar(1) ,
  SECONDARY varchar(1) ,  PRIMARY KEY  (OBJECT_ID)
);

    Create a configuration file (demo.conf):

# SOURCE  # PKEY      # MVIEW Log       # TARGET
T_OBJECTS # OBJECT_ID # MLOG$_T_OBJECTS # T_OBJECGS

    Run refresh_mysql.pl to start replication (I run it on windows with ActivePerl):

perl refresh_mysql.pl -s username#password#Oracle:tnsname -t username#password#MySQL:database:hostip -c demo.conf

    Now you can run some DML statement on t_objects from Oracle database, and then check whether the changes are applied to MySQL by this script.

    refresh_mysql.pl is originally designed to replicate changed data from Oracle to other databases. But now I find that it can be used to copy or replicate data between different databases such as from MySQL to Oracle. We just need to maintain a table with the same structure as the materialized view log table in Oracle, then manually populate the log rows (data copy), or by triggers (data replication) if the source database support it. So powerful it is for this simple Perl script.

    The log table must contains all the primary key columns and two extra columns ("SEQUENCE$$" and "DMLTYPE$$"). I have test it in the MySQL that the dollar character can appear in column name without any problem. The sample create syntax looks as following :

create table temp_table
{
    SEQUENCE$$    INT NOT NULL PRIMARY KEY,
    DMLTYPE$$     VARCHAR(1) DEFAULT 'I',
    pkey_col1     column_type,
    pkey_col...   column_type
);

    Create a configuration file for it, it will work for you. For multiple database support, I have to remove the double quote in the column name, because it will be treated as an constant string in MySQL.

    After some changes of the code, I have copy some data from MySQL to Oracle database. Because I don't know how to create trigger in MySQL, so I did not test data replication to Oracle, but I believe that it will work fine for tables with small data volume.

    I have nothing to do at last weekend, so I decided to make some changes to the MyLOG utility, I had added the search by redo layer/opcode feature two weeks ago, so I tried my best to add the search by object id feature. However the search by object id can only be performed on redo record of layer 11, which is the table data operations. It's a big improve for me, for the MyLOG utility.

    If I focus on the layer 11 and master all the details, I should be able to write a logical replication utility like Quest SharePlex or GoldenGate. The fact is that I have to spend a lot of time in making money for the whole family by doing something not relative to this subject. Following is the search command syntax :

SEARCH option value [option value]
TAIL   option value [option value]
DUMP   option value [option value]
   OPTION
       OP       layer
       SUBOP    opcode
       START    start redo block
       END      end redo block
       BLOCK    in given block
       OBJECT   object id

    Let's try each of these command with the enhanced search feature!

LOG> tail object 61 start 2
Start tailing redo operation ...

0x00005e30.00003eb8.0010 2006-08-23 19:08:58
          [05.02] [Trans Undo ] Update rollback segment header - KTURDH
          [05.01] [Trans Undo ] Undo block or under segment header - KTURDB
          [11.05] [Row Access ] Update Row Piece
          [05.19] [Trans Undo ] Transaction start audit log record
LOG> search object 61 start 2
Start search opcode = [00.00] ...
Seq=24112, Start=16056, Offset=0x0010=16, End=16057, Time=2006-08-23 19:08:58
LOG> dump object 61 start 2
Start dump redo operation ...
REDO RECORD -- RBA=0x00005e30.00003eb8.0010 LEN=0x0284 VLD=0x01 SCN=0x031f.05c0098c
  CHANGE#=0x01 OP=5.2 SEQ=1 TYP=0 CLS=43 OFFS=0x000c LEN=0x003c DBA=0x6280b402 ...
    PIECE#=2 OFFS=0x001c LEN=0x0020 SIZE=0x0020
    ktudh redo: slt=0x0045 sqn=0x000daa80 flag=0x0412 siz=228 fbi=0
                uba=0x3b41c2dd.3bce.00  pxid=0x0000.000.00000000
  CHANGE#=0x02 OP=5.1 SEQ=8 TYP=0 CLS=44 OFFS=0x0048 LEN=0x0114 DBA=0x3b41c2dd ...
    PIECE#=2 OFFS=0x0038 LEN=0x0014 SIZE=0x0014
    ktudb redo: siz=0x00e4 spc=0x000000ac flag=0x0012 seq=0x00ce rec=0x00
                xid=0x000e.045.000daa80
    PIECE#=3 OFFS=0x004c LEN=0x0030 SIZE=0x0030
    ktubu redo: slt=69 rci=0 opc=11.1 objn=61 objd=61 tns=0
    PIECE#=4 OFFS=0x007c LEN=0x0020 SIZE=0x0020
    ......

    With these powerful command, I can make more improvement in log format research, and more smoothly.

    ociuldr is more and more widely used by DBAs. Someone sent me a email few days ago said that he had successfully implemented this tool in one of his project. Even it's just a very small utility, it take me some time to add lot's of option to make it more useful. Today I will introduce the LOG option to you.

log = log file name, prefix with + to append mode

    For example :

ociuldr user=ansyql/anysql@s8i query="select * from tab" log=tab.log
ociuldr user=ansyql/anysql@s8i query="select * from tab" log=+tab.log

    If the LOG option is provided, ociuldr will not print any message to the screen (standard out put device), they will be printed to the log file specified by the LOG option. If the LOG option value is prefixed with a plus char, ociuldr will append the message to the log file, else create a new one. This option is very useful when you schedule ociuldr in a crontab job, keep all the message output for trouble shooting.

    Welcome to new version. You can download source code to compile your binary, I only provide the binarys of Windows(x86), RedHat Linux (x86) and Solairs Sparc 64.

    About half years ago, someone told me to add this. I was afraid of change any code at that time, so just left him along. Today I made some change to the source code, the return or exit code is implemented as following :

0 = Successful
1 = Cannot login to database
2 = Cannot create cursor handle
3 = Cannot prepare SQL statement
4 = Cannot execute SQL query
5 = Cannot get the metadata of the result set
6 = Cannot create output file
7 = Oracle error found when fetching rows, such as ORA-01555 etc.

    Let's begin verification. When we cannot connect to database :

bash-2.03$ ./ociuldr.bin user=anysql/anysq1@test  query="select * from tab"
Cannot connect as anysql/anysq1@s8i.
Connection failed.  Exiting...
bash-2.03$ echo $?
1

    When wrong SQL is provided :

bash-2.03$ ./ociuldr.bin user=anysql/anysql@test  query="select * from tab1"
ORA-00942: table or view does not exist
bash-2.03$ echo $?
3

    When no permission of creating text file.

bash-2.03$ ./ociuldr.bin user=anysql/anysql@test  query="select * from tab" file=/test.txt
   15500 bytes allocated for column TNAME (1)
    4000 bytes allocated for column TABTYPE (2)
   20500 bytes allocated for column CLUSTERID (3)

ERROR -- Cannot write to file : /test.txt
bash-2.03$ echo $?
6

    Hope he can read this article, sorry for the later.