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
5. if need. you can buu license
B. Capture before migration
1. Capture source Database (MSSQL)
use tgh;
select * from barang;
select * from customer;
select * from suplier;
select * from pasok;
select * from pembelian;
2. Capture destination Target (Oracle)
use tgh;
select * from barang;
select * from customer;
select * from suplier;
select * from pasok;
select * from pembelian;
C:\windows\System32>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 8 14:54:02 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 tab;
no rows selected
SQL>
C. Migration MSSQL to Oracle
1. open sqldataw SQLLine Data. Tab Connection. complete form (as example)
Source MSSQL
SID/Service Name = tgh
User = sa
password = xxx
press button Test Connection
Destination Oracle
SID/Service Name = tka
User = aisyah
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
5. Migration success
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 (31 ms)
Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Ok, 0 ms)
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production (Ok, 31 ms)
Reading the database schema (5 tables, 344 ms)
Dropping foreign keys on selected tables in target (0 keys, 15 ms)
Transferring database (4 concurrent sessions):
dbo.barang - Started (1 of 5, session 1)
dbo.barang - Open cursor (6 rows read, 32 ms, session 1)
dbo.customer - Started (2 of 5, session 2)
dbo.customer - Open cursor (6 rows read, 0 ms, session 2)
dbo.barang - Drop target table (not exists, 31 ms, session 1)
dbo.customer - Drop target table (not exists, 16 ms, session 2)
dbo.pasok - Started (3 of 5, session 3)
dbo.pembelian - Started (4 of 5, session 4)
dbo.pasok - Open cursor (12 rows read, 63 ms, session 3)
dbo.pembelian - Open cursor (15 rows read, 0 ms, session 4)
dbo.pasok - Drop target table (not exists, 0 ms, session 3)
dbo.pembelian - Drop target table (not exists, 15 ms, session 4)
dbo.customer - Create target table (250 ms, session 2)
dbo.barang - Create target table (375 ms, session 1)
dbo.pasok - Create target table (31 ms, session 3)
dbo.pembelian - Create target table (16 ms, session 4)
dbo.customer - Data transfer complete (session 2)
Rows read: 6 (0 rows/sec)
Rows written: 6 (32 rows/sec, 299 bytes, 1.6 KB/sec)
Transfer time: 657 ms (0 ms read, 188 ms write)
dbo.suplier - Started (5 of 5, session 2)
dbo.pasok - Data transfer complete (session 3)
Rows read: 12 (190 rows/sec)
Rows written: 12 (800 rows/sec, 385 bytes, 25.1 KB/sec)
Transfer time: 344 ms (63 ms read, 15 ms write)
dbo.barang - Data transfer complete (session 1)
Rows read: 6 (188 rows/sec)
Rows written: 6 (95 rows/sec, 128 bytes, 2.0 KB/sec)
Transfer time: 704 ms (32 ms read, 63 ms write)
dbo.pembelian - Data transfer complete (session 4)
Rows read: 15 (0 rows/sec)
Rows written: 15 (0 rows/sec, 497 bytes, 0 bytes/sec)
Transfer time: 281 ms (0 ms read, 0 ms write)
dbo.suplier - Open cursor (6 rows read, 16 ms, session 2)
dbo.suplier - Drop target table (not exists, 0 ms, session 2)
dbo.suplier - Create target table (15 ms, session 2)
dbo.suplier - Data transfer complete (session 2)
Rows read: 6 (375 rows/sec)
Rows written: 6 (0 rows/sec, 325 bytes, 0 bytes/sec)
Transfer time: 47 ms (16 ms read, 0 ms write)
Summary:
Tables: 5 (5 Ok, 0 failed)
Target DDL: 10 (10 Ok, 0 failed)
Rows read: 45
Rows written: 45
Transfer time: 813 ms (55 rows/sec, 1.6 KB, 2.0 KB/sec)
Logs:
Execution log: sqldata.log
DDL SQL statements: sqldata_ddl.sql
D. Capture after migration
1. Capture source Database (MSSQL)
use tgh;
select * from barang;
select * from customer;
select * from suplier;
select * from pasok;
select * from pembelian;
2. Capture destination Target (Oracle)
use tgh;
select * from barang;
select * from customer;
select * from suplier;
select * from pasok;
select * from pembelian;
C:\windows\System32>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 8 15:02:22 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> select * from customer;
KODE_C NAMA_CUSTOMER ALAMAT_CUSTOMER
------ ------------------------------ ------------------------------
KOTA_CUSTOMER TELEPON_CUSTOME
--------------- ---------------
B-0001 TOKO WARNA JL ABC 234
BANDUNG (022) 432-6635
B-0002 TOKO SURYA JL ABC 309
BANDUNG (022) 432-6024
B-0003 TOKO MAHARANI JL KOPO 333
BANDUNG (022) 856-3333
KODE_C NAMA_CUSTOMER ALAMAT_CUSTOMER
------ ------------------------------ ------------------------------
KOTA_CUSTOMER TELEPON_CUSTOME
--------------- ---------------
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
6 rows selected.
SQL> select * from suplier;
KODE_ NAMA_SUPLIER ALAMAT_SUPLIER
----- ------------------------------ ------------------------------
KOTA_SUPLIER TELEPON_SUPLIER
--------------- ---------------
EB-01 PT ULTRASOUND JL SUKARNO HATTA 103
BANDUNG (021) 522-3305
EB-02 PT SUPERTRON JL INDUSTRI 37
BANDUNG (021) 660-4091
EB-03 PT OCHANG JL KOSAMBI 44
BANDUNG (022) 850-4444
KODE_ NAMA_SUPLIER ALAMAT_SUPLIER
----- ------------------------------ ------------------------------
KOTA_SUPLIER TELEPON_SUPLIER
--------------- ---------------
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
6 rows selected.
SQL> select * from pasok;
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK
---------- ------ ----- --------- ------------
PAS-E001 ELK-01 EJ-01 01-JAN-02 8
PAS-E002 ELK-01 EJ-02 01-JAN-02 5
PAS-E003 ELK-02 EJ-01 01-FEB-02 2
PAS-E004 ELK-02 EJ-02 02-FEB-02 3
PAS-E005 ELK-02 EB-02 01-JAN-02 2
PAS-E006 ELK-03 EJ-01 03-MAR-02 5
PAS-E007 ELK-03 EJ-01 04-MAR-02 2
PAS-E008 ELK-03 EJ-01 03-MAR-02 3
PAS-E009 ELK-03 EB-01 13-MAR-02 4
PAS-E010 ELK-03 EB-01 13-MAR-02 3
PAS-E011 ELK-04 EB-01 22-APR-02 12
PAS-E012 ELK-04 EB-02 30-APR-02 9
12 rows selected.
SQL> select * from pembelian;
KODE_PEMBE KODE_B KODE_C TANGGAL_P JUMLAH_PEMBELIAN
---------- ------ ------ --------- ----------------
BEL-E001 ELK-01 J-0001 20-MAY-02 3
BEL-E002 ELK-01 J-0001 21-MAY-02 4
BEL-E003 ELK-01 J-0002 20-MAY-02 2
BEL-E004 ELK-01 B-0001 20-MAY-02 2
BEL-E005 ELK-01 B-0002 22-MAY-02 3
BEL-E006 ELK-02 J-0001 24-JUN-02 1
BEL-E007 ELK-02 J-0002 24-JUN-02 1
BEL-E008 ELK-02 B-0001 25-JUN-02 2
BEL-E009 ELK-02 B-0002 25-JUN-02 2
BEL-E010 ELK-03 J-0001 20-JUN-02 5
BEL-E011 ELK-03 J-0002 02-JUL-02 4
BEL-E012 ELK-03 B-0001 02-JUL-02 6
BEL-E013 ELK-03 J-0001 10-JUL-02 5
BEL-E014 ELK-04 J-0002 15-JUL-02 12
BEL-E015 ELK-04 B-0002 17-JUL-02 15
15 rows selected.
SQL>
3. Test insert resutl migration MSSQL 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
C:\windows\System32>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 8 15:05:41 2021
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
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 where KODE_BARANG ='ELK-99';
no rows selected
SQL> select * from suplier where KODE_SUPLIER ='EJ-99';
no rows selected
SQL> select * from customer where KODE_CUSTOMER ='J-0099';
no rows selected
SQL> select * from pasok where KODE_PASOK ='PAS-E099';
no rows selected
SQL> select * from pembelian where KODE_PEMBELIAN ='BEL-E099';
no rows selected
SQL>
SQL> insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-99','Barang - After Migration','BUAH',20);
1 row created.
SQL> 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');
1 row created.
SQL> 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');
1 row created.
SQL> insert into pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E099','ELK-99','EJ-99','1-Jan-02',8);
1 row created.
SQL> insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E099','ELK-99','J-0099','20-May-02',3);
1 row created.
SQL>
SQL> select * from barang where KODE_BARANG ='ELK-99';
KODE_B NAMA_BARANG SATUAN_BARANG STOK_BARANG
------ ------------------------- -------------------- -----------
ELK-99 Barang - After Migration BUAH 20
SQL> select * from suplier where KODE_SUPLIER ='EJ-99';
KODE_ NAMA_SUPLIER ALAMAT_SUPLIER
----- ------------------------------ ------------------------------
KOTA_SUPLIER TELEPON_SUPLIER
--------------- ---------------
EJ-99 Suplier - After Migration JL THAMRIN 12
JAKARTA (021) 850-2301
SQL> select * from customer where KODE_CUSTOMER ='J-0099';
KODE_C NAMA_CUSTOMER ALAMAT_CUSTOMER
------ ------------------------------ ------------------------------
KOTA_CUSTOMER TELEPON_CUSTOME
--------------- ---------------
J-0099 Customer - After Migration JL CIMANGGIS 34
JAKARTA (021) 856-4209
SQL> select * from pasok where KODE_PASOK ='PAS-E099';
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK
---------- ------ ----- --------- ------------
PAS-E099 ELK-99 EJ-99 01-JAN-02 8
SQL> select * from pembelian where KODE_PEMBELIAN ='BEL-E099';
KODE_PEMBE KODE_B KODE_C TANGGAL_P JUMLAH_PEMBELIAN
---------- ------ ------ --------- ----------------
BEL-E099 ELK-99 J-0099 20-MAY-02 3
SQL>
SQL> delete from barang where kode_barang = 'ELK-99';
1 row deleted.
SQL> delete from suplier where kode_suplier = 'EJ-99';
1 row deleted.
SQL> delete from customer where kode_customer = 'J-0099';
1 row deleted.
SQL> delete from pasok where kode_pasok = 'PAS-E099';
1 row deleted.
SQL> delete from pembelian where kode_pembelian = 'BEL-E099';
1 row deleted.
SQL>
SQL> select * from barang where KODE_BARANG ='ELK-99';
no rows selected
SQL> select * from suplier where KODE_SUPLIER ='EJ-99';
no rows selected
SQL> select * from customer where KODE_CUSTOMER ='J-0099';
no rows selected
SQL> select * from pasok where KODE_PASOK ='PAS-E099';
no rows selected
SQL> select * from pembelian where KODE_PEMBELIAN ='BEL-E099';
no rows selected
SQL>
No comments:
Post a Comment