.::: Migration SQL Server to Oracle Using SQL Developer with Migration Metode :::.

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 “Prefenrences”.

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 MSSQL (Source)
exe sp_column barang;
select * from barang 

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 tgh.barang;
select * from tgh.barang                  *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from tgh.customer;
select * from tgh.customer
                  *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from tgh.suplier;
select * from tgh.suplier
                  *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from tgh.pasok;
select * from tgh.pasok
                  *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from tgh.pembelian;
select * from tgh.pembelian
                  *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from barang;
select * from barang
              *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from customer;
select * from customer
              *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from suplier;
select * from suplier
              *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from pasok;
select * from pasok
              *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from pembelian;
select * from pembelian
              *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>

B. Migration Database
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 repository on oracle 
CREATE USER demox IDENTIFIED BY demox DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE, CREATE SESSION, CREATE VIEW TO demox;
grant resource to demox with admin option;
grant create role to demox with admin option;
grant alter any trigger to demox with admin option;
grant create user to demox with admin option;

3. create connection DB Source (SQL Server)

4. create connection DB Repository (Oracle)

5. Create connection DB Destination (Oracle)

6. Create repository 
select database repository (example demox) -> Migration Repository -> Associate Migration Report

7. Check repository status 

8. Migration Wizard
introduction -> next

9. Repository 
Connection = demox 

10. Project
Name = test-migration  
c:\

10. Source Database (SQL Server)
Mode = Online
Connection sa

11. Capture 
select database will migrate ( example tgh )

12. convert
Check Microsoft SQL Server : Is Quoted Identifier On

13. Translate 
Select all 

14. Target Database
Mode = Online 
Connection = dest-tk 

15. move database connection
Mode = Online 
Source = sa 
Target = dest-tk

16. Summary 

17. waiting & Finish

C. Capture after migration with metode migration 
1. capture MSSQL (Source)
select * from INFORMATION_SCHEMA.TABLES
exe sp_column barang;
select * from barang 

2. 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 tgh.barang;

3. Test insert resutl migratio MSSQL to Oracle

select * from tgh.barang where KODE_tgh.barang ='ELK-99';

select * from tgh.suplier where KODE_tgh.suplier ='EJ-99';

select * from tgh.customer where KODE_tgh.customer ='J-0099';

select * from tgh.pasok where KODE_tgh.pasok ='PAS-E099';

select * from tgh.pembelian where KODE_tgh.pembelian ='BEL-E099';

insert into tgh.barang(KODE_tgh.barang,NAMA_tgh.barang,SATUAN_tgh.barang,STOK_tgh.barang) values('ELK-99','tgh.barang - After Migration','BUAH',20);

insert into tgh.suplier(KODE_tgh.suplier,NAMA_tgh.suplier,ALAMAT_tgh.suplier,KOTA_tgh.suplier,TELEPON_tgh.suplier) values ('EJ-99','tgh.suplier - After Migration','JL THAMRIN 12','JAKARTA','(021) 850-2301');

insert into tgh.customer(KODE_tgh.customer,NAMA_tgh.customer,ALAMAT_tgh.customer,KOTA_tgh.customer,TELEPON_tgh.customer) values('J-0099','tgh.customer - After Migration','JL CIMANGGIS 34','JAKARTA','(021) 856-4209');

insert into tgh.pasok(KODE_tgh.pasok,KODE_tgh.barang,KODE_tgh.suplier,TANGGAL_tgh.pasok,JUMLAH_tgh.pasok) values('PAS-E099','ELK-99','EJ-99','1-Jan-02',8);

insert into tgh.pembelian(KODE_tgh.pembelian,KODE_tgh.barang,KODE_tgh.customer,TANGGAL_tgh.pembelian,JUMLAH_tgh.pembelian) values('BEL-E099','ELK-99','J-0099','20-May-02',3);

select * from tgh.barang where KODE_tgh.barang ='ELK-99';

select * from tgh.suplier where KODE_tgh.suplier ='EJ-99';

select * from tgh.customer where KODE_tgh.customer ='J-0099';

select * from tgh.pasok where KODE_tgh.pasok ='PAS-E099';

select * from tgh.pembelian where KODE_tgh.pembelian ='BEL-E099';

delete from tgh.barang where kode_tgh.barang = 'ELK-99';

delete from tgh.suplier where kode_tgh.suplier = 'EJ-99';

delete from tgh.customer where kode_tgh.customer = 'J-0099';

delete from tgh.pasok where kode_tgh.pasok = 'PAS-E099';

delete from tgh.pembelian where kode_tgh.pembelian = 'BEL-E099';

select * from tgh.barang where KODE_tgh.barang ='ELK-99';

select * from tgh.suplier where KODE_tgh.suplier ='EJ-99';

select * from tgh.customer where KODE_tgh.customer ='J-0099';

select * from tgh.pasok where KODE_tgh.pasok ='PAS-E099';

select * from tgh.pembelian where KODE_tgh.pembelian ='BEL-E099';

log  

SQL> select * from tgh.barang where KODE_tgh.barang ='ELK-99';

no rows selected

SQL> select * from tgh.suplier where KODE_tgh.suplier ='EJ-99';

no rows selected

SQL> select * from tgh.customer where KODE_tgh.customer ='J-0099';

no rows selected

SQL> select * from tgh.pasok where KODE_tgh.pasok ='PAS-E099';

no rows selected

SQL> select * from tgh.pembelian where KODE_tgh.pembelian ='BEL-E099';

no rows selected

SQL>

SQL> insert into tgh.barang(KODE_tgh.barang,NAMA_tgh.barang,SATUAN_tgh.barang,STOK_tgh.barang) values('ELK-99','tgh.barang - After Migration','BUAH',20);

1 row created.

SQL> insert into tgh.suplier(KODE_tgh.suplier,NAMA_tgh.suplier,ALAMAT_tgh.suplier,KOTA_tgh.suplier,TELEPON_tgh.suplier) values ('EJ-99','tgh.suplier - After Migration','JL THAMRIN 12','JAKARTA','(021) 850-2301');

1 row created.

SQL> insert into tgh.customer(KODE_tgh.customer,NAMA_tgh.customer,ALAMAT_tgh.customer,KOTA_tgh.customer,TELEPON_tgh.customer) values('J-0099','tgh.customer - After Migration','JL CIMANGGIS 34','JAKARTA','(021) 856-4209');

1 row created.

SQL> insert into tgh.pasok(KODE_tgh.pasok,KODE_tgh.barang,KODE_tgh.suplier,TANGGAL_tgh.pasok,JUMLAH_tgh.pasok) values('PAS-E099','ELK-99','EJ-99','1-Jan-02',8);

1 row created.

SQL> insert into tgh.pembelian(KODE_tgh.pembelian,KODE_tgh.barang,KODE_tgh.customer,TANGGAL_tgh.pembelian,JUMLAH_tgh.pembelian) values('BEL-E099','ELK-99','J-0099','20-May-02',3);

1 row created.

SQL>

SQL> select * from tgh.barang where KODE_tgh.barang ='ELK-99';

KODE_B NAMA_tgh.barang               SATUAN_tgh.barang        STOK_tgh.barang

------ ------------------------- -------------------- -----------

ELK-99 tgh.barang - After Migration  BUAH                          20

SQL> select * from tgh.suplier where KODE_tgh.suplier ='EJ-99';

KODE_ NAMA_tgh.suplier                   ALAMAT_tgh.suplier   KOTA_tgh.suplier  TELEPON_tgh.suplier

----- ------------------------------ ---------------- ------------- ---------------

EJ-99 tgh.suplier - After Migration      JL THAMRIN 12    JAKARTA       (021) 850-2301

SQL> select * from tgh.customer where KODE_tgh.customer ='J-0099';

KODE_C NAMA_tgh.customer                  ALAMAT_tgh.customer  KOTA_tgh.customer  TELEPON_CUSTOME

------ ------------------------------ ---------------  -------------- ---------------

J-0099 tgh.customer - After Migration     JL CIMANGGIS 34  JAKARTA        (021) 856-4209

SQL> select * from tgh.pasok where KODE_tgh.pasok ='PAS-E099';

KODE_tgh.pasok KODE_B KODE_ TANGGAL_P JUMLAH_tgh.pasok

---------- ------ ----- --------- ------------

PAS-E099   ELK-99 EJ-99 01-JAN-02            8

SQL> select * from tgh.pembelian where KODE_tgh.pembelian ='BEL-E099';

KODE_PEMBE KODE_B KODE_C TANGGAL_P JUMLAH_tgh.pembelian

---------- ------ ------ --------- ----------------

BEL-E099   ELK-99 J-0099 20-MAY-02                3

SQL>

SQL> delete from tgh.barang where kode_tgh.barang = 'ELK-99';

1 row deleted.

SQL> delete from tgh.suplier where kode_tgh.suplier = 'EJ-99';

1 row deleted.

SQL> delete from tgh.customer where kode_tgh.customer = 'J-0099';

1 row deleted.

SQL> delete from tgh.pasok where kode_tgh.pasok = 'PAS-E099';

1 row deleted.

SQL> delete from tgh.pembelian where kode_tgh.pembelian = 'BEL-E099';

1 row deleted.

SQL>