Migrate data may be a regular job for a DBA, sometime from this Oracle server to another Oracle server, and sometime migrate from Oracle to MySQL or other database, or other database to Oracle. With good utility it can be a easy job, that's why tools can improve life quality.
Migration usually have three steps for large data volume moving.
1, Data initialization2, Capture the data change and apply it to the target database.3, Do an application fail over to access the new database.
For step one, you can open a standby and then take long time to do it, asyncdata script will not cover this step. And will not cover step three also, this is application's role. This script can cover the second step.
Every table should have a physical primary key or logical primary key to uniquely identify a row. Let's look through the insert / update / delete operations. We will use a log table to capture the changes of the source table.
For insert, record the new primary key values into a log table.
For update, if primary key columns update, record a delete of the old primary key values into the log table, and then record a insert operation with the new primary key values into the log table. If none primary key columns are changed, then just record an update operation into the log table.
For delete, record the old primary key values into a log table.
So the log table should have following columns.
1, Sequence, the DML operation sequence.2, DML type, the DML operation type, Insert / Update / Delete.The primary key columns of the source table.
For Oracle database, the materialized view log can be used to as the log table. Actually I write this script based on how Oracle refresh a materialized view in fast mode. Let's take a quick sample. Create the following tables between database test1 (source) and database test2 (target).
CREATE TABLE ASYNCDATA
(
col1 number(38) not null primary key,
col2 varchar2(30)
);
Then create materialized view log on the source database.
SQL> CREATE MATERIALIZED VIEW LOG ON ASYNCDATA WITH SEQUENCE, PRIMARY KEY;
Materialized view log created.
Check the schema test1, we found that table MLOG$_ASYNCDATA is the log table used to capture the DML change.
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
------------------------------ -------------- ----------
ASYNCDATA TABLE
MLOG$_ASYNCDATA TABLE
RUPD$_ASYNCDATA TABLE
Then download the perl script (asyncdata), and install the Perl and DBI/DBD on the machine where run the perl. And config the Oracle network TNS alias. Then prepare the following configuration file (test.cfg) for this script, you can include multiple tables in one config file.
#Source #Primary key #Log Table #Target Table
ASYNCDATA #COL1 #MLOG$_ASYNCDATA #ASYNCDATA
Then run the script with the following command in a screen or a separate window.
$ asyncdata.pl -s test#test#Oracle:test1 -t test#test#Oracle:test2 -c test.cfg
08/10 19:51:19 - Replication started, 1 tables in configuration file.
Then do some changes to the source table, and check whether this changes is copied to the target table.