.::: Migration MariaDB / MySQL to Oracle Using SQL Developer and Metode Copy to Oracle (Only Table) :::.

A. Pre Upgrade
1. Download SQL Developer & Extract
2. To connect to third-party database (MySQL, Microsoft SQL Server, Sybase Adaptive Server,  Microsoft Access, IBM DB2) using SQL Developer, we need jTDS driver. You can download needed jTDS driver from following link. http://sourceforge.net/projects/jtds/files/jtds/1.2/jtds-1.2-dist.zip/download  . Extract the dowloaded zip file named jtds-1.2-dist.zip

 
3. Click “Tools” and then “Preferences”.

4. Select “Third Party JDBC Drivers” and click “Add Entry” button to add jTDS driver for SQL Server.

5. Select jar file located in jTDS driver folder. & Ok

6. capture MariaDB / MySQL  (Source)
use tka;
select * from barang;
select * from customer;
select * from suplier;
select * from pasok;
select * from pembelian;

Setting environment for MariaDB 10.1 (x64)
C:\windows\System32>mysql -u root -p
Enter password: ****
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 27
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 tka;
Database changed
MariaDB [tka]> select * from barang;
+-------------+-------------+---------------+-------------+
| KODE_BARANG | 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 in set (0.00 sec)
MariaDB [tka]>

7. Capture oracle (Destination)

C:\Users\teguh>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 12 06:43:59 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> 

B. Migration Database metode copy to Oracle
1. create user di system as sysdba (optional) on oracle
create user aisyah identified by hanin ;
grant CONNECT to aisyah;
grant resource to aisyah;
grant oem_monitor to aisyah;
grant SELECT ANY DICTIONARY to aisyah;
grant SELECT ANY TABLE to aisyah;
grant SELECT ANY SEQUENCE to aisyah;
grant CREATE ANY PROCEDURE to aisyah;
grant CREATE ANY TRIGGER to aisyah;
grant CREATE ANY TYPE to aisyah;
grant EXECUTE ANY PROCEDURE to aisyah;
grant CREATE ANY TABLE  to aisyah;
grant ALTER ANY TABLE to aisyah;
grant INSERT ANY TABLE  to aisyah;
grant UPDATE ANY TABLE to aisyah;
grant DROP ANY TABLE to aisyah;
grant CREATE ANY INDEX to aisyah;
grant ALTER ANY INDEX to aisyah;
grant DROP ANY INDEX to aisyah;
grant CREATE ANY TRIGGER  to aisyah;
grant ALTER ANY TRIGGER to aisyah;
grant DROP ANY TRIGGER to aisyah;

2. create connection DB Destination (Oracle)

3. Create connection DB Source (MariaDB/MySQL)

4. select table source database

5. Right Click -> Copy To Oracle 

6. Option 

select Destination Connection Name (Database) = dest-tka (SID TKA)

Copy DDL = Do Not Replace

Check Copy Data = Append

7. Summary & OK

Dragged Objects:

sa.tgh.dbo.TABLE.barang

sa.tgh.dbo.TABLE.customer

sa.tgh.dbo.TABLE.pasok

sa.tgh.dbo.TABLE.pembelian

sa.tgh.dbo.TABLE.suplier

Drop Target: dest-tk

Copy DDL: Yes

Do Not Replace Existing Objects

Copy Data: Yes

Append Existing Objects

Task Succeeded.

5 tables copied.

    Created table customer and inserted 6 rows

    Created table pembelian and inserted 15 rows

    Created table suplier and inserted 6 rows

    Created table pasok and inserted 12 rows

    Created table barang and inserted 6 rows

8. Copy to Oracle Success

C. Capture after migration with metode Copy to Oracle 

1. capture MariaDB (Source)

C:\windows\System32>mysql -u root -p

Enter password: ****

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 27

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 tka;

Database changed

MariaDB [tka]> select * from barang;

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

| KODE_BARANG | 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 in set (0.00 sec)

MariaDB [tka]>

2. Capture oracle (Destination)

C:\windows\System32>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 2 10:30:04 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 aisyah.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>

3. Test insert result migration MariaDB/ MySQL to Oracle 

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  

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>