The following example shows Oracle to MySQL replication. In this example, Oracle and MySQL are running on a single server.
Environment: Virtualbox using Pre-Built Developer VMs (for Oracle VM VirtualBox)
Template name: Database App Development VM
Oracle Linux 5 (32bit)
Oracle RDBMS 11gR2
MySQL Server 5.0.77
MySQL is not installed on the pre-built VM, but this can be installed with:
yum mysql mysql-server
Start MySQL
/etc/init.d/mysqld start
Install Dbvisit Replicate using the 32bit RPM installation file (as root):
rpm -ivh dbvisit_replicate-2.0.02-1.i386.rpm
Preparing... ########################################## [100%]
1:dbvisit_replicate ########################################### [100%]
The following Oracle table is going to be replicated. This is a copy of the sys.dba_objects table but created in the SCOTT schema. Create this table in Oracle:
CREATE TABLE "SCOTT"."AVI_OBJECTS"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(30)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
Create this same table in MySQL. The syntax is slightly different to Oracle.
First user scott (or database) has to be created first in MySQL.
mysql> create database scott;
Query OK, 1 row affected (0.00 sec)
mysql> use scott;
Database changed
Now the MySQL table can be created:
DROP TABLE IF EXISTS `avi_objects`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `avi_objects` (
`OWNER` varchar(30) default NULL,
`OBJECT_NAME` varchar(128) default NULL,
`SUBOBJECT_NAME` varchar(30) default NULL,
`OBJECT_ID` bigint(20) default NULL,
`DATA_OBJECT_ID` bigint(20) default NULL,
`OBJECT_TYPE` varchar(19) default NULL,
`CREATED` date default NULL,
`LAST_DDL_TIME` date default NULL,
`TIMESTAMP` varchar(19) default NULL,
`STATUS` varchar(7) default NULL,
`TEMPORARY` varchar(1) default NULL,
`GENERATED` varchar(1) default NULL,
`SECONDARY` varchar(1) default NULL,
`NAMESPACE` bigint(20) default NULL,
`EDITION_NAME` varchar(30) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;