Teguh Triharto Learning Center: .::: Migration Oracle to MariaDB/MySQL using SQLines Data Migration :::.

A. Prepare software migration
1. Download SQLines Data & extract
http://www.sqlines.com/download
SQLines Data is a tool for data transfer, schema conversion and data migration validation/testing.
Note:
SQLines Data tool does not convert views, stored procedures, functions, and triggers, use SQLines SQL Converter to convert them.

2. Download SQLines SQL Converter (Optional)
SQLines SQL Converter
SQLines SQL Converter helps you convert database schema (DDL), queries and DML statements, views, stored procedures, packages, functions and triggers.

3. Extract SQLlines Data
4. download libmysql.dll and extract to directory SQLLine
https://www.dll-files.com/libmysql.dll.html

5. if need. you can buu license http://www.sqlines.com/purchase

B. Capture before migration
1. Capture source target (Oracle)
C:\Users\teguh>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sat Feb 27 06:28:41 2021
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: aisyah
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from barang;
KODE_B NAMA_BARANG SATUAN_BARANG STOK_BARANG
------ ------------------------- -------------------- -----------
ELK-01 RICE COOKER BUAH 20
ELK-02 LEMARI ES UNIT 8
ELK-03 TELEVISI UNIT 30
ELK-04 RADIO/TAPE BUAH 35
ELK-05 KOMPUTER UNIT 28
ELK-06 KIPAS ANGIN BUAH 38
6 rows selected.
SQL>

C. Migration OracleDB to MariaDB/MySQL
1. open sqldataw SQLine Data Migration
2. Tab Connection. complete form (as example)
Source Oracle
SID/Service Name = tka ( or localhost:1521/tka)
User = aisyah
password = xxx
press button Test Connection

Destination MariaDB/MySQL
SID/Service Name = tghth
User = root
password = xxx
press button Test Connection

2. Tab Setting
Transfer option = Table recreate (Default)
Table list = *barang*,*customer*,*suplier*,*pasok*,*pembelian* (sample)

3. Press button Transfer & see tab Transfer log

4. Waiting & see tab log to detail

SQLines Data 3.3.107 x64 - Database Migration Tool.
Copyright (c) 2020 SQLines. All Rights Reserved.
FOR EVALUATION USE ONLY, MAY SKIP 1 ROW FROM A TABLE
Connecting to databases (2.5 sec)
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production (Ok, 2.5 sec)
MySQL 10.1.14-MariaDB mariadb.org binary distribution x64 Win64 system YaSSL 2.3.9b (Ok, 2.1 sec)
Reading the database schema (5 tables, 375 ms)
Transferring database (4 concurrent sessions):
AISYAH.CUSTOMER - Started (1 of 5, session 1)
AISYAH.CUSTOMER - Open cursor (6 rows read, 0 ms, session 1)
AISYAH.CUSTOMER - Drop target table (469 ms, session 1)
AISYAH.CUSTOMER - Create target table (531 ms, session 1)
AISYAH.CUSTOMER - Data transfer complete (session 1)
Rows read: 6 (0 rows/sec)
Rows written: 5 (0 rows/sec, 277 bytes, 0 bytes/sec)
Transfer time: 1.3 sec (0 ms read, 0 ms write)
AISYAH.PEMBELIAN - Started (2 of 5, session 1)
AISYAH.PEMBELIAN - Open cursor (15 rows read, 0 ms, session 1)
AISYAH.PEMBELIAN - Drop target table (391 ms, session 1)
AISYAH.SUPLIER - Started (3 of 5, session 2)
AISYAH.SUPLIER - Open cursor (6 rows read, 0 ms, session 2)
AISYAH.PEMBELIAN - Create target table (656 ms, session 1)
AISYAH.PEMBELIAN - Data transfer complete (session 1)
Rows read: 15 (0 rows/sec)
Rows written: 14 (298 rows/sec, 534 bytes, 11.1 KB/sec)
Transfer time: 1.4 sec (0 ms read, 47 ms write)
AISYAH.PASOK - Started (4 of 5, session 1)
AISYAH.PASOK - Open cursor (12 rows read, 0 ms, session 1)
AISYAH.SUPLIER - Drop target table (1.0 sec, session 2)
AISYAH.SUPLIER - Create target table (328 ms, session 2)
AISYAH.PASOK - Drop target table (860 ms, session 1)
AISYAH.SUPLIER - Data transfer complete (session 2)
Rows read: 6 (0 rows/sec)
Rows written: 5 (0 rows/sec, 291 bytes, 0 bytes/sec)
Transfer time: 1.7 sec (0 ms read, 0 ms write)
AISYAH.BARANG - Started (5 of 5, session 2)
AISYAH.BARANG - Open cursor (6 rows read, 0 ms, session 2)
AISYAH.PASOK - Create target table (485 ms, session 1)
AISYAH.PASOK - Data transfer complete (session 1)
Rows read: 12 (0 rows/sec)
Rows written: 11 (70 rows/sec, 408 bytes, 2.5 KB/sec)
Transfer time: 2.2 sec (0 ms read, 157 ms write)
AISYAH.CUSTOMER - Add PRIMARY KEY constraint (1.6 sec, session 3)
AISYAH.BARANG - Drop target table (1.8 sec, session 2)
AISYAH.BARANG - Create target table (391 ms, session 2)
AISYAH.SUPLIER - Add PRIMARY KEY constraint (1.3 sec, session 1)
AISYAH.BARANG - Data transfer complete (session 2)
Rows read: 6 (0 rows/sec)
Rows written: 5 (313 rows/sec, 125 bytes, 7.6 KB/sec)
Transfer time: 2.6 sec (0 ms read, 16 ms write)
AISYAH.BARANG - Add PRIMARY KEY constraint (547 ms, session 3)
Read/write row count differences (5 tables):
1. AISYAH.CUSTOMER (6 rows read, 5 rows written, 1 rows difference)
2. AISYAH.PEMBELIAN (15 rows read, 14 rows written, 1 rows difference)
3. AISYAH.SUPLIER (6 rows read, 5 rows written, 1 rows difference)
4. AISYAH.PASOK (12 rows read, 11 rows written, 1 rows difference)
5. AISYAH.BARANG (6 rows read, 5 rows written, 1 rows difference)
Please contact us at support@sqlines.com for any assistance.
Summary:
Tables: 5 (5 Ok, 0 failed)
Target DDL: 13 (13 Ok, 0 failed)
Rows read: 45
Rows written: 40 (5 row difference)
Transfer time: 7.2 sec (6 rows/sec, 1.6 KB, 226 bytes/sec)
Logs:
Execution log: sqldata.log
DDL SQL statements: sqldata_ddl.sql

5. Migration success

D. Capture after migration
1. Capture source target (Oracle)
C:\Users\teguh>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sat Feb 27 06:28:41 2021
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: aisyah
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from barang;
KODE_B NAMA_BARANG SATUAN_BARANG STOK_BARANG
------ ------------------------- -------------------- -----------
ELK-01 RICE COOKER BUAH 20
ELK-02 LEMARI ES UNIT 8
ELK-03 TELEVISI UNIT 30
ELK-04 RADIO/TAPE BUAH 35
ELK-05 KOMPUTER UNIT 28
ELK-06 KIPAS ANGIN BUAH 38
6 rows selected.
SQL>

2. Capture destination Target (MariaDB/MySQL)
C:\windows\System32>mysql -u root -p
Enter password: ****
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 59
Server version: 10.1.14-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use tghth;
Database changed
MariaDB [tghth]> select * from barang;
+-------------+-------------+---------------+-------------+
| KODE_BARANG | NAMA_BARANG | SATUAN_BARANG | STOK_BARANG |
+-------------+-------------+---------------+-------------+
| ELK-02 | LEMARI ES | UNIT | 8 |
| ELK-03 | TELEVISI | UNIT | 30 |
| ELK-04 | RADIO/TAPE | BUAH | 35 |
| ELK-05 | KOMPUTER | UNIT | 28 |
| ELK-06 | KIPAS ANGIN | BUAH | 38 |
+-------------+-------------+---------------+-------------+
5 rows in set (0.00 sec)
MariaDB [tghth]> select * from tghth.barang;
+-------------+-------------+---------------+-------------+
| KODE_BARANG | NAMA_BARANG | SATUAN_BARANG | STOK_BARANG |
+-------------+-------------+---------------+-------------+
| ELK-02 | LEMARI ES | UNIT | 8 |
| ELK-03 | TELEVISI | UNIT | 30 |
| ELK-04 | RADIO/TAPE | BUAH | 35 |
| ELK-05 | KOMPUTER | UNIT | 28 |
| ELK-06 | KIPAS ANGIN | BUAH | 38 |
+-------------+-------------+---------------+-------------+
5 rows in set (0.00 sec)
MariaDB [tghth]> select * from customer;
+---------------+---------------+-----------------+---------------+------------------+
| KODE_CUSTOMER | NAMA_CUSTOMER | ALAMAT_CUSTOMER | KOTA_CUSTOMER | TELEPON_CUSTOMER |
+---------------+---------------+-----------------+---------------+------------------+
| B-0002 | TOKO SURYA | JL ABC 309 | BANDUNG | (022) 432-6024 |
| B-0003 | TOKO MAHARANI | JL KOPO 333 | BANDUNG | (022) 856-3333 |
| B-0004 | TOKO MULYA | JL OTISTA 555 | BANDUNG | (022) 422-5555 |
| J-0001 | TOKO KARISMA | JL CIMANGGIS 34 | JAKARTA | (021) 856-4209 |
| J-0002 | TOKO AYU | JL CIMANGGIS 12 | JAKARTA | (021) 856-1321 |
+---------------+---------------+-----------------+---------------+------------------+
5 rows in set (0.00 sec)
MariaDB [tghth]> select * from suplier;
+--------------+---------------------+---------------------+--------------+-----------------+
| KODE_SUPLIER | NAMA_SUPLIER | ALAMAT_SUPLIER | KOTA_SUPLIER | TELEPON_SUPLIER |
+--------------+---------------------+---------------------+--------------+-----------------+
| EB-02 | PT SUPERTRON | JL INDUSTRI 37 | BANDUNG | (021) 660-4091 |
| EB-03 | PT OCHANG | JL KOSAMBI 44 | BANDUNG | (022) 850-4444 |
| EB-04 | PT TUNGGAL JAYA | JL KIARA CONDONG 77 | BANDUNG | (022)740-7777 |
| EJ-01 | PT ACTRON | JL THAMRIN 12 | JAKARTA | (021) 850-2301 |
| EJ-02 | PT MULYA ELEKTRONIK | JL SUDIRMAN 45 | JAKARTA | (021) 855-4262 |
+--------------+---------------------+---------------------+--------------+-----------------+
5 rows in set (0.00 sec)
MariaDB [tghth]> select * from pasok;
+------------+-------------+--------------+---------------+--------------+
| KODE_PASOK | KODE_BARANG | KODE_SUPLIER | TANGGAL_PASOK | JUMLAH_PASOK |
+------------+-------------+--------------+---------------+--------------+
| PAS-E002 | ELK-01 | EJ-02 | 2002-01-01 | 5 |
| PAS-E003 | ELK-02 | EJ-01 | 2002-02-01 | 2 |
| PAS-E004 | ELK-02 | EJ-02 | 2002-02-02 | 3 |
| PAS-E005 | ELK-02 | EB-02 | 2002-01-01 | 2 |
| PAS-E006 | ELK-03 | EJ-01 | 2002-03-03 | 5 |
| PAS-E007 | ELK-03 | EJ-01 | 2002-03-04 | 2 |
| PAS-E008 | ELK-03 | EJ-01 | 2002-03-03 | 3 |
| PAS-E009 | ELK-03 | EB-01 | 2002-03-13 | 4 |
| PAS-E010 | ELK-03 | EB-01 | 2002-03-13 | 3 |
| PAS-E011 | ELK-04 | EB-01 | 2002-04-22 | 12 |
| PAS-E012 | ELK-04 | EB-02 | 2002-04-30 | 9 |
+------------+-------------+--------------+---------------+--------------+
11 rows in set (0.00 sec)
MariaDB [tghth]> select * from pembeliab;
ERROR 1146 (42S02): Table 'tghth.pembeliab' doesn't exist
MariaDB [tghth]> select * from pembelian;
+----------------+-------------+---------------+-------------------+------------------+
| KODE_PEMBELIAN | KODE_BARANG | KODE_CUSTOMER | TANGGAL_PEMBELIAN | JUMLAH_PEMBELIAN |
+----------------+-------------+---------------+-------------------+------------------+
| BEL-E002 | ELK-01 | J-0001 | 2002-05-21 | 4 |
| BEL-E003 | ELK-01 | J-0002 | 2002-05-20 | 2 |
| BEL-E004 | ELK-01 | B-0001 | 2002-05-20 | 2 |
| BEL-E005 | ELK-01 | B-0002 | 2002-05-22 | 3 |
| BEL-E006 | ELK-02 | J-0001 | 2002-06-24 | 1 |
| BEL-E007 | ELK-02 | J-0002 | 2002-06-24 | 1 |
| BEL-E008 | ELK-02 | B-0001 | 2002-06-25 | 2 |
| BEL-E009 | ELK-02 | B-0002 | 2002-06-25 | 2 |
| BEL-E010 | ELK-03 | J-0001 | 2002-06-20 | 5 |
| BEL-E011 | ELK-03 | J-0002 | 2002-07-02 | 4 |
| BEL-E012 | ELK-03 | B-0001 | 2002-07-02 | 6 |
| BEL-E013 | ELK-03 | J-0001 | 2002-07-10 | 5 |
| BEL-E014 | ELK-04 | J-0002 | 2002-07-15 | 12 |
| BEL-E015 | ELK-04 | B-0002 | 2002-07-17 | 15 |
+----------------+-------------+---------------+-------------------+------------------+
14 rows in set (0.00 sec)
MariaDB [tghth]>

3. Test insert resutl migration Oracle to MariaDB/ MySQL

select * from barang where KODE_BARANG ='ELK-99';

select * from suplier where KODE_SUPLIER ='EJ-99';

select * from customer where KODE_CUSTOMER ='J-0099';

select * from pasok where KODE_PASOK ='PAS-E099';

select * from pembelian where KODE_PEMBELIAN ='BEL-E099';

insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-99','Barang - After Migration','BUAH',20);

insert into suplier(KODE_SUPLIER,NAMA_SUPLIER,ALAMAT_SUPLIER,KOTA_SUPLIER,TELEPON_SUPLIER) values ('EJ-99','Suplier - After Migration','JL THAMRIN 12','JAKARTA','(021) 850-2301');

insert into customer(KODE_CUSTOMER,NAMA_CUSTOMER,ALAMAT_CUSTOMER,KOTA_CUSTOMER,TELEPON_CUSTOMER) values('J-0099','Customer - After Migration','JL CIMANGGIS 34','JAKARTA','(021) 856-4209');

insert into pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E099','ELK-99','EJ-99','1-Jan-02',8);

insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E099','ELK-99','J-0099','20-May-02',3);

select * from barang where KODE_BARANG ='ELK-99';

select * from suplier where KODE_SUPLIER ='EJ-99';

select * from customer where KODE_CUSTOMER ='J-0099';

select * from pasok where KODE_PASOK ='PAS-E099';

select * from pembelian where KODE_PEMBELIAN ='BEL-E099';

delete from barang where kode_barang = 'ELK-99';

delete from suplier where kode_suplier = 'EJ-99';

delete from customer where kode_customer = 'J-0099';

delete from pasok where kode_pasok = 'PAS-E099';

delete from pembelian where kode_pembelian = 'BEL-E099';

select * from barang where KODE_BARANG ='ELK-99';

select * from suplier where KODE_SUPLIER ='EJ-99';

select * from customer where KODE_CUSTOMER ='J-0099';

select * from pasok where KODE_PASOK ='PAS-E099';

select * from pembelian where KODE_PEMBELIAN ='BEL-E099';

log

MariaDB [tghth]> select * from barang where KODE_BARANG ='ELK-99';

Empty set (0.03 sec)

MariaDB [tghth]> select * from suplier where KODE_SUPLIER ='EJ-99';

Empty set (0.00 sec)

MariaDB [tghth]> select * from customer where KODE_CUSTOMER ='J-0099';

Empty set (0.00 sec)

MariaDB [tghth]> select * from pasok where KODE_PASOK ='PAS-E099';

Empty set (0.00 sec)

MariaDB [tghth]> select * from pembelian where KODE_PEMBELIAN ='BEL-E099';

Empty set (0.00 sec)

MariaDB [tghth]> insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-99','Barang - After Migration','BUAH',20);

Query OK, 1 row affected (0.21 sec)

MariaDB [tghth]> insert into suplier(KODE_SUPLIER,NAMA_SUPLIER,ALAMAT_SUPLIER,KOTA_SUPLIER,TELEPON_SUPLIER) values ('EJ-99','Suplier - After Migration','JL THAMRIN 12','JAKARTA','(021) 850-2301');

Query OK, 1 row affected (0.04 sec)

MariaDB [tghth]> insert into customer(KODE_CUSTOMER,NAMA_CUSTOMER,ALAMAT_CUSTOMER,KOTA_CUSTOMER,TELEPON_CUSTOMER) values('J-0099','Customer - After Migration','JL CIMANGGIS 34','JAKARTA','(021) 856-4209');

Query OK, 1 row affected (0.04 sec)

MariaDB [tghth]> insert into pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E099','ELK-99','EJ-99','1-Jan-02',8);

Query OK, 1 row affected (0.04 sec)

MariaDB [tghth]> insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E099','ELK-99','J-0099','20-May-02',3);

Query OK, 1 row affected (0.12 sec)

MariaDB [tghth]>

MariaDB [tghth]> select * from barang where KODE_BARANG ='ELK-99';

+-------------+--------------------------+---------------+-------------+

+-------------+--------------------------+---------------+-------------+

| ELK-99 | Barang - After Migration | BUAH | 20 |

+-------------+--------------------------+---------------+-------------+

1 row in set (0.00 sec)

MariaDB [tghth]> select * from suplier where KODE_SUPLIER ='EJ-99';

+--------------+---------------------------+----------------+--------------+-----------------+

+--------------+---------------------------+----------------+--------------+-----------------+

+--------------+---------------------------+----------------+--------------+-----------------+

1 row in set (0.00 sec)

MariaDB [tghth]> select * from customer where KODE_CUSTOMER ='J-0099';

+---------------+----------------------------+-----------------+---------------+------------------+

+---------------+----------------------------+-----------------+---------------+------------------+

+---------------+----------------------------+-----------------+---------------+------------------+

1 row in set (0.00 sec)

MariaDB [tghth]> select * from pasok where KODE_PASOK ='PAS-E099';

+------------+-------------+--------------+---------------+--------------+

+------------+-------------+--------------+---------------+--------------+

| PAS-E099 | ELK-99 | EJ-99 | 1-Jan-02 | 8 |

+------------+-------------+--------------+---------------+--------------+

1 row in set (0.00 sec)

MariaDB [tghth]> select * from pembelian where KODE_PEMBELIAN ='BEL-E099';

+----------------+-------------+---------------+-------------------+------------------+

+----------------+-------------+---------------+-------------------+------------------+

| BEL-E099 | ELK-99 | J-0099 | 20-May-02 | 3 |

+----------------+-------------+---------------+-------------------+------------------+

1 row in set (0.00 sec)

MariaDB [tghth]>

MariaDB [tghth]> delete from barang where kode_barang = 'ELK-99';

Query OK, 1 row affected (0.04 sec)

MariaDB [tghth]> delete from suplier where kode_suplier = 'EJ-99';

Query OK, 1 row affected (0.07 sec)

MariaDB [tghth]> delete from customer where kode_customer = 'J-0099';

Query OK, 1 row affected (0.09 sec)

MariaDB [tghth]> delete from pasok where kode_pasok = 'PAS-E099';

Query OK, 1 row affected (0.04 sec)

MariaDB [tghth]> delete from pembelian where kode_pembelian = 'BEL-E099';

Query OK, 1 row affected (0.03 sec)

MariaDB [tghth]>

MariaDB [tghth]> select * from barang where KODE_BARANG ='ELK-99';

Empty set (0.00 sec)

MariaDB [tghth]> select * from suplier where KODE_SUPLIER ='EJ-99';

Empty set (0.00 sec)

MariaDB [tghth]> select * from customer where KODE_CUSTOMER ='J-0099';

Empty set (0.00 sec)

MariaDB [tghth]> select * from pasok where KODE_PASOK ='PAS-E099';

Empty set (0.00 sec)

MariaDB [tghth]> select * from pembelian where KODE_PEMBELIAN ='BEL-E099';

Empty set (0.00 sec)

MariaDB [tghth]>

Teguh Triharto Learning Center

Blog List

Saturday, February 27, 2021

.::: Migration Oracle to MariaDB/MySQL using SQLines Data Migration :::.

No comments:

Post a Comment

Popular Posts