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
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;
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. select table source database & Right Click -> Copy To Oracle
9. Option
select Destination Connection Name = demox
Copy DDL = Do Not Replace
Check Copy Data = Append
10. Summary & OK
11. Copy to Oracle Success
C. Capture after migration with metode Copy to Oracle
1. capture MSSQL (Source)
select * from INFORMATION_SCHEMA.TABLES
exe sp_column barang;
select * from barang
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*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 barang;
3. Test insert result migration MSSQL 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>
No comments:
Post a Comment