jump to navigation

ADVANCE REPLIKASI with LIMITED BANDWIDTH Agustus 29, 2009

Posted by setijoagus in Mikrotik, REPLICATION, oracle.
Tags: , ,
1 comment so far

Seberapa banyak bandwidth yang dibutuhkan untuk melakukan replikasi sebenarnya ?, sejujurnya saya juga nggak tahu, walaupun pertimbangannya adalah berapa banyak jumlah transaksi yang terjadi dalam satuan waktu tertentu.
Berikut saya melakukan percobaan kecil – kecilan dengan kondisi seperti skema ini

replication_01
dari hasil test bandwidth yang sudah saya lakukan didapat angka bandwidth yang tidak pernah mencapai lebih dari 16 Kbps

replication_02
Test segera dimulai
a. Pada server ORCL1, dilakukan perintah insert
SQL> insert into dept values (51,’dept’,'dept’);
SQL> insert into dept values (52,’dept’,'dept’);
………………………………………..
SQL> insert into dept values (96,’dept’,'dept’);
SQL> insert into dept values (97,’dept’,'dept’);
SQL> insert into dept values (98,’dept’,'dept’);
SQL> insert into dept values (99,’dept’,'dept’);
SQL> commit;

SQL> select to_char(sysdate, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Sat 29-Aug-2009 14:44:40

SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
51 dept dept
52 dept dept
53 dept dept
54 dept dept
55 dept dept
56 dept dept
57 dept dept
58 dept dept
………………….
97 dept dept
98 dept dept
99 dept dept
53 rows selected.

b. Pada server ORCL2, beberapa saat kemudian [belum terjadi replikasi data]
SQL> select to_char(sysdate, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Sat 29-Aug-2009 14:45:34

SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

c. Pada server ORCL2, dulangi langkah diatas [sudah terjadi replikasi data]
SQL> select to_char(sysdate, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Sat 29-Aug-2009 14:45:36

SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
51 dept dept
52 dept dept
53 dept dept
54 dept dept
55 dept dept
56 dept dept
57 dept dept
58 dept dept
………………….
97 dept dept
98 dept dept
99 dept dept
53 rows selected.

jadi [Sat 29-Aug-2009 14:24:40] – [Sat 29-Aug-2009 14:25:36] = 56 detik
jadi 53 rows – 4 rows = 49 rows

d. Pada server ORCL1, dilakukan perintah delete
SQL> delete dept where deptno > 40;
49 rows deleted.
SQL> commit;
Commit complete.
SQL> select to_char(sysdate, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Sat 29-Aug-2009 14:34:17
SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

e. Pada server ORCL2, beberapa saat kemudian
SQL> select to_char(sysdate, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Sat 29-Aug-2009 14:34:35

SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
51 dept dept
52 dept dept
53 dept dept
54 dept dept
55 dept dept
56 dept dept
57 dept dept
58 dept dept
………………….
97 dept dept
98 dept dept
99 dept dept
53 rows selected.

f. Pada server ORCL2, dulangi langkah diatas
SQL> select to_char(sysdate, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Sat 29-Aug-2009 14:34:36
SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

jadi [Sat 29-Aug-2009 14:34:17] – [Sat 29-Aug-2009 14:34:36] = 19 detik
jadi 53 rows – 4 rows = 49 rows

Create Advance Replication [AGAIN & SIMPLE] Agustus 29, 2009

Posted by setijoagus in REPLICATION, oracle.
Tags: ,
add a comment

Ini tulisan Create Advance Replication, yang ketiga kali di blog ini, ada beberapa pertanyaan soal create replikasi yang sulit.
Berikut cara cepat create replikasi [2 server Oracle, tabel scott.DEPT & scott.EMP] , dengan syarat :
a. 2 oracle db server telah disetup dengan nama SID = ORCL1 & ORCL2
b. 2 oracle db telah startup
c. tnsnames.ora di masing – masing server telah tersetup dengan benar, contoh

Picture0001d. jalankan script, di ORCL1

Picture0002e. periksa apakah replikasi sudah bisa dieksekusi ?, jika COUNT masih belum bernilai = 0, maka replikasi belum dpt dijalankan

Picture0003f. tunggu beberapa saat, periksa kembali sampai dengan COUNT bernilai = 0

Picture0004g. start replikasi

Picture0005e. lampiran script bikin_replikasi.sql
————————————————————————
– orcl1 orcl1 orcl1 orcl1 orcl1 orcl1 orcl1 orcl1 orcl1 orcl1 orcl1
————————————————————————
– 1. unlock user scott & setup global
————————————————————————
conn system/oracle007@orcl1
alter user scott account unlock;
alter user scott identified by tiger;
conn scott/tiger;

conn system/oracle007@orcl1
alter system set global_names=TRUE;
alter system set job_queue_processes = 1;

————————————————————————
– 2. create admin replikasi
– Grant privs to the propagator, to propagate changes to remote sites
– Grant privs to the receiver to apply deferred transactions
– Authorise the administrator to administer replication groups and schemas
————————————————————————
CREATE USER repadmin IDENTIFIED BY repadmin;

BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => ‘repadmin’);
END;
/

GRANT SELECT ANY DICTIONARY TO repadmin;

BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
username => ‘repadmin’);
END;
/

BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => ‘repadmin’,
privilege_type => ‘receiver’,
list_of_gnames => NULL);
END;
/

————————————————————————————-
– 3. Schedule job to purge
————————————————————————————-
CONNECT repadmin/repadmin@orcl1
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
– interval 1 menit
interval => ’sysdate + 1*(1/(24*60))’,
delay_seconds => 0);
END;
/

————————————————————————
– 4. Create private db links for all repadmin users
————————————————————————
conn system/oracle007@orcl1
create public database link orcl2 using ‘orcl2′;
conn repadmin/repadmin@orcl1
create database link orcl2 connect to repadmin identified by repadmin;

————————————————————————
– 5. Schedule job to push
————————————————————————
conn repadmin/repadmin@orcl1
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => ‘orcl2′,
– interval 1 menit
interval => ’sysdate + 1*(1/(24*60))’,
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1500,
delay_seconds => 1200);
END;
/

————————————————————————
– 6. Create replication group for MASTERDEF site
– Register objects within the group
————————————————————————
conn repadmin/repadmin@orcl1
begin
dbms_repcat.create_master_repgroup (
gname => ’scott_repg’);
end;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ’scott_repg’,
type => ‘TABLE’,
oname => ‘EMP’,
sname => ’scott’,
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ’scott_repg’,
type => ‘TABLE’,
oname => ‘DEPT’,
sname => ’scott’,
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

conn repadmin/repadmin@orcl1
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ’scott’,
oname => ‘emp’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ’scott’,
oname => ‘dept’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/

————————————————————————
– orcl2 orcl2 orcl2 orcl2 orcl2 orcl2 orcl2 orcl2 orcl2 orcl2 orcl2
————————————————————————
– 6. unlock user scott & setup global
————————————————————————
conn system/oracle007@orcl2
alter user scott account unlock;
alter user scott identified by tiger;
conn scott/tiger;

conn system/oracle007@orcl2
alter system set global_names=TRUE;
alter system set job_queue_processes = 1;

————————————————————————
– 7. create admin replikasi
– Grant privs to the propagator, to propagate changes to remote sites
– Grant privs to the receiver to apply deferred transactions
– Authorise the administrator to administer replication groups and schemas
————————————————————————
CREATE USER repadmin IDENTIFIED BY repadmin;

BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => ‘repadmin’);
END;
/

GRANT SELECT ANY DICTIONARY TO repadmin;

BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
username => ‘repadmin’);
END;
/

BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => ‘repadmin’,
privilege_type => ‘receiver’,
list_of_gnames => NULL);
END;
/

————————————————————————————-
– 8. Schedule job to purge
————————————————————————————-
CONNECT repadmin/repadmin@orcl2
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
– interval 1 menit
interval => ’sysdate + 1*(1/(24*60))’,
delay_seconds => 0);
END;
/

————————————————————————
– 9. Create private db links for all repadmin users
————————————————————————
conn system/oracle007@orcl2
create public database link orcl1 using ‘orcl1′;
conn repadmin/repadmin@orcl2
create database link orcl1 connect to repadmin identified by repadmin;

————————————————————————
– 10. Schedule job to push
————————————————————————
conn repadmin/repadmin@orcl2
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => ‘orcl1′,
– interval 1 menit
interval => ’sysdate + 1*(1/(24*60))’,
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1500,
delay_seconds => 1200);
END;
/

————————————————————————
– 11. Add master desination sites
————————————————————————
conn repadmin/repadmin@orcl1
BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => ’scott_repg’,
master => ‘orcl2′,
use_existing_objects => TRUE,
copy_rows => FALSE,
propagation_mode => ‘ASYNCHRONOUS’);
END;
/

Demo Advance Replication with Metode Conflicts Resolution Agustus 29, 2009

Posted by setijoagus in REPLICATION, oracle.
Tags: , , ,
add a comment

Setelah dilakukan perubahan pada setting Advance Replication, terutama dengan penambahan kolom TIMESTAMP, seperti yang terlihat pada gambar di bawah ini
Picture001
Sekarang waktunya untuk melakukan test.
a. di sisi server ORCL1 dilakukan perubahan data pada baris yang bersesuaian
SQL> conn scott/tiger@orcl2
SQL> update dept set dname = ‘ORCL1′, timestamp = systimestamp
where deptno = 40 ;
SQL> commit;

b. demikiam juga di sisi server ORCL2 dilakukan perubahan data pada baris yang bersesuaian
SQL> conn scott/tiger@orcl1
SQL> update dept set dname = ‘ORCL1′, timestamp = systimestamp
where deptno = 40 ;
SQL> commit;

c. setelah dilakukan test hasilnya seperti terlihat dibawah ini, terlihat ada perbedaan isi.
Picture002d. beberapa saat kemudian hasil yang dianggap valid adalah “LATEST TIMESTAMP” pada orcl1, yang pada akhirnya terupdate hasil server ORCL2

Picture003

Create Advance Replication with Metode Conflicts Resolution Agustus 28, 2009

Posted by setijoagus in REPLICATION, oracle.
Tags: ,
add a comment

Untuk membuat agar tabel – tabel yang telah di replikasi, memiliki Metode Conflicts Resolution, maka perlu ada penambahan kolom, pada contoh di bawah ini ditunjukkan bagaimana membuat Metode Conflicts Resolution – LATEST TIMESTAMP.
Jika pada contoh http://www.dba-oracle.com/art_select_repl.pdf, menggunakan sysdate sebagai Metode Conflicts Resolution – LATEST TIMESTAMP, pada contoh ini saya menggunakan systimestamp.

a. siapkan terlebih dulu agar server – server memiliki ukuran waktu yang sama, bisa menggunakan teknologi ntp server, detail ntp server bisa dilihat di http://setijoagus.wordpress.com/2009/03/27/membangun-server-client-network-time-protocol-ntp/

setup_01

b. melakukan re-konfigurasi kembali agar server oracle support Metode Conflicts Resolution – LATEST TIMESTAMP, langkah – langkahnya :
1. stop replikasi
2. alter tabel TIMESTAMP
3. Register objects within the group
4. conflict resolution & column group
5. start replikasi

setup_02

setup_03

c. Script lengkap

————————————————————————
– orcl1 orcl1 orcl1 orcl1 orcl1 orcl1 orcl1 orcl1 orcl1 orcl1 orcl1
————————————————————————
– 1. stop replikasi
————————————————————————
CONNECT repadmin/repadmin@orcl1
BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => ’scott_repg’);
END;
/

————————————————————————
– 2. alter tabel TIMESTAMP
————————————————————————
BEGIN
DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
sname => ’scott’,
oname => ‘emp’,
type => ‘TABLE’,
ddl_text => ‘ALTER TABLE SCOTT.EMP ADD (TIMESTAMP TIMESTAMP(6) WITH LOCAL TIME ZONE)’);
END;
/

BEGIN
DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
sname => ’scott’,
oname => ‘dept’,
type => ‘TABLE’,
ddl_text => ‘ALTER TABLE SCOTT.DEPT ADD (TIMESTAMP TIMESTAMP(6) WITH LOCAL TIME ZONE)’);
END;
/

————————————————————————
– 3. Register objects within the group
————————————————————————
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ’scott’,
oname => ‘emp’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ’scott’,
oname => ‘dept’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/

————————————————————————
– 4. conflict resolution & column group
————————————————————————
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ’scott_repg’,
type => ‘TRIGGER’,
oname => ‘insert_time’,
sname => ’scott’,
ddl_text => ‘CREATE OR REPLACE TRIGGER scott.insert_time
BEFORE
INSERT OR UPDATE ON scott.emp FOR EACH ROW
BEGIN
IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
:NEW.TIMESTAMP := SYSTIMESTAMP;
END IF;
END;’);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ’scott_repg’,
type => ‘TRIGGER’,
oname => ‘insert_time2′,
sname => ’scott’,
ddl_text => ‘CREATE OR REPLACE TRIGGER scott.insert_time2
BEFORE
INSERT OR UPDATE ON scott.dept FOR EACH ROW
BEGIN
IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
:NEW.TIMESTAMP := SYSTIMESTAMP;
END IF;
END;’);
END;
/

BEGIN
DBMS_REPCAT.MAKE_COLUMN_GROUP (
sname => ’scott’,
oname => ‘emp’,
column_group => ‘emp_timestamp_cg’,
list_of_column_names => ‘ename,job,mgr,hiredate,sal,comm,deptno,timestamp’);
END;
/

BEGIN
DBMS_REPCAT.MAKE_COLUMN_GROUP (
sname => ’scott’,
oname => ‘dept’,
column_group => ‘dept_timestamp_cg’,
list_of_column_names => ‘dname,loc,timestamp’);
END;
/

BEGIN
DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
sname => ’scott’,
oname => ‘emp’,
column_group => ‘emp_timestamp_cg’,
sequence_no => 1,
method => ‘LATEST TIMESTAMP’,
parameter_column_name => ‘timestamp’);
END;
/

BEGIN
DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
sname => ’scott’,
oname => ‘dept’,
column_group => ‘dept_timestamp_cg’,
sequence_no => 1,
method => ‘LATEST TIMESTAMP’,
parameter_column_name => ‘timestamp’);
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ’scott’,
oname => ‘emp’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ’scott’,
oname => ‘dept’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/

————————————————————————
– 5. start replikasi
————————————————————————
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => ’scott_repg’);
END;
/

Advance Replication Metode Conflicts Resolution Agustus 28, 2009

Posted by setijoagus in REPLICATION, oracle.
Tags: , ,
add a comment

Menurut http://www.dba-oracle.com/art_select_repl.pdf, untuk mengatasi Conflicts, pada Advance Replication ada beberapa cara :
art_01

Latest Timestamp Value

art_02

Site Priority Value

art_03

Advance Replication Conflicts Agustus 27, 2009

Posted by setijoagus in REPLICATION, oracle.
Tags:
add a comment

menurut http://download-east.oracle.com/docs/cd/B13789_01/server.101/b10732/repconfl.htm , jika menggunakan Advance Replication maka akan terjadi Conflict, type – tye conflict yang mungkin terjadi adalah :
1. Uniqueness Conflicts
2. Update Conflicts
3. Delete Conflicts

contoh Uniqueness Conflicts
misalkan pada saat bersamaan [selisih milli second / second / belum sampai schedulle push terjadi] di sisi kantor pusat [sid = orcl1] dengan kantor cabang [sid = orcl2], dilakukan insert data dengan kolom PK yang sama
SQL> conn scott/tiger@orcl1
SQL> insert into dept values (60,’orcl1′,’orcl1′);
SQL> commit;

SQL> conn scott/tiger@orcl2
SQL> insert into dept values (60,’orcl2′,’orcl2′);
SQL> commit;

maka akan terjadi Uniqueness Conflicts
replikasi_conflicts_01

replikasi_conflicts_02

replikasi_conflicts_03

Advance Replication Server Down ? Agustus 27, 2009

Posted by setijoagus in Linux, REPLICATION, oracle.
Tags:
add a comment

Apa yang terjadi jika Advance Replikasi, yang menghubungkan kantor pusat [sid = orcl1] dengan kantor cabang [sid = orcl2] salah satu servernya Down ? berikut demonya.
a. dimana server kantor cabang [sid = orcl2] down
replikasi_db_down_01
b. server kantor pusat [sid = orcl1] up dan sedang melakukan transaksi [insert data]
SQL> conn scott/tiger@orcl1
SQL> insert into dept values (51,’dept’,'dept’,systimestamp);
SQL> insert into dept values (52,’dept’,'dept’,systimestamp);
SQL> insert into dept values (53,’dept’,'dept’,systimestamp);
SQL> insert into dept values (54,’dept’,'dept’,systimestamp);
SQL> insert into dept values (55,’dept’,'dept’,systimestamp);
SQL> commit;
replikasi_db_down_02
c. beberapa saat kemudian server kantor cabang [sid = orcl2] up kembali
replikasi_db_down_03
d. jreng – jreng ……
replikasi_db_down_04

OEM Java Console 10G Agustus 7, 2009

Posted by setijoagus in REPLICATION, oracle.
Tags: , , ,
add a comment

untuk me-menage replikasi, akan lebih “pas”, jika menggunakan OEM, untuk Oracle 10G karena OEM dalam masa peralihan (dari java ke web based), ada beberapa fungsi yang belum ada di web based salah satunya “replication”.
untuk itu kita perlu melakukan install oem Java Console untuk 10G

caranya adalah sebagai berikut :
1. download dari otn
http://download-llnw.oracle.com/otn/nt/oracle10g/10201/10201_client_win32.zip

2. install

oem01

3. jalankan

oem02

4. contoh – contoh tampilan OEM untuk replication

oem03

oem04

sqlloader II with date Juni 30, 2009

Posted by setijoagus in oracle.
Tags: ,
add a comment

sqlloader II, adalah kelanjutan sqlloader I , dengan menggunakan type data DATE

##########################################################################
# sqlloader II with date
##########################################################################
1. login sqlplus di oracle server I [orcl1]
[oracle@orcl1 ~]$ sqlplus scott/tiger@orcl1

2. periksa struktur
SQL> desc emp;
Name Null? Type
—————————————– ——– —————————-
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

3. membuat script sql untuk export ke text file
SQL> host
[oracle@orcl1 ~]$ vi emp_txt.sql
set linesize 120;
set feedback off;
set HEADING off;
set pagesize 0;
SELECT
EMPNO || ‘,’ || ENAME || ‘,’ || JOB || ‘,’ || MGR || ‘,’ || to_char(HIREDATE,’DD-MON-YYYY’) || ‘,’ || SAL || ‘,’ || COMM || ‘,’ || DEPTNO
FROM EMP;
[oracle@orcl1 ~]$ exit

4. data di table dept ditambahkan
SQL> SET LIN 150
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

SQL> insert into emp values (9001,’SEAG’,'PRG’,7839,TO_DATE(‘2009/06/29′,’YYYY/MM/DD’),1000,50,41);
SQL> insert into emp values (9002,’WARI’,'PRG’,7839,TO_DATE(‘2009/06/29′,’YYYY/MM/DD’),1000,50,41);
SQL> commit;

SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
9001 SEAG PRG 7839 29-JUN-09 1000 50 41
9002 WARI PRG 7839 29-JUN-09 1000 50 41
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> exit

5. lakukan export ke text file
[oracle@orcl1 ~]$ cat emp_txt.sql | sqlplus -s scott/tiger@orcl1 > emp_1.txt
[oracle@orcl1 ~]$ cat emp_1.txt
9001,SEAG,PRG,7839,29-JUN-2009,1000,50,41
9002,WARI,PRG,7839,29-JUN-2009,1000,50,41
7369,SMITH,CLERK,7902,17-DEC-1980,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-1981,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-1981,1250,500,30
7566,JONES,MANAGER,7839,02-APR-1981,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-1981,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-1981,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-1981,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-1987,3000,,20
7839,KING,PRESIDENT,,17-NOV-1981,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-1981,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-1987,1100,,20
7900,JAMES,CLERK,7698,03-DEC-1981,950,,30
7902,FORD,ANALYST,7566,03-DEC-1981,3000,,20
7934,MILLER,CLERK,7782,23-JAN-1982,1300,,10

6. untuk melakukan sqlloader ke server ke 2 [orcl2], perlu dibuat file controlnya
[oracle@orcl1 ~]$ vi emp.ctl
load data
INFILE emp_1.txt
APPEND
into table emp
fields terminated by “,”
TRAILING NULLCOLS
(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)

7. periksa keadaan tabel sebelum di sqlloader
[oracle@orcl1 ~]$ sqlplus scott/tiger@orcl2
SQL> SET LIN 150
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> exit

8. menjalankan sqlloader
[oracle@orcl1 ~]$ sqlldr userid=scott/tiger@orcl2 control=emp.ctl log=emp.log

9. periksa keadaan tabel sesudah di sqlloader
[oracle@orcl1 ~]$ sqlplus scott/tiger@orcl2
SQL> SET LIN 150
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
9001 SEAG PRG 7839 29-JUN-09 1000 50 41
9002 WARI PRG 7839 29-JUN-09 1000 50 41
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> exit

sqlloader I Juni 30, 2009

Posted by setijoagus in oracle.
Tags: ,
add a comment

sqlloader digunakan untuk 2 hal :

1. memindahkan dari database selain oracle ke oracle

2. memindahkan database dari kantor cabang ke kantor pusat, dimana kantor cabang ke kantor pusat tidak terhubung secara online karena keterbatasan bandwidth  internet.

Pada contoh dibawah ini, saya lengkapi script untuk memindahkan database oracle [database 1 / kantor cabang / sid : orcl1 / langkah 1 sd 5] ke format text file, kemudian saya lakukan sqlloader ke server database lainnya [database 2 / kantor pusat / sid : orcl2 / langkah 6 sd 9]

##########################################################################
# sqlloader I
##########################################################################
1. login sqlplus di oracle server I [orcl1]
[oracle@orcl1 ~]$ sqlplus scott/tiger@orcl1

2. periksa struktur
SQL> desc dept;
Name Null? Type
—————————————– ——– —————————-
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

3. membuat script sql untuk export ke text file
SQL> host
[oracle@orcl1 ~]$ vi above_file.sql
set linesize 120;
set feedback off;
set HEADING off;
set pagesize 0;
SELECT
DEPTNO || ‘,’ || DNAME || ‘,’ || LOC
FROM
dept;
[oracle@orcl1 ~]$ exit

4. data di table dept ditambahkan
SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> insert into dept values (41,’IT’,'SURABAYA’);
SQL> insert into dept values (42,’MKT’,'SURABAYA’);
SQL> commit;

SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
41 IT SURABAYA
42 MKT SURABAYA
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

5. lakukan export ke text file
SQL> exit
[oracle@orcl1 ~]$ cat above_file.sql | sqlplus -s scott/tiger@orcl1 > dept_290609.txt
[oracle@orcl1 ~]$ cat dept_290609.txt
41,IT,SURABAYA
42,MKT,SURABAYA
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

6. untuk melakukan sqlloader ke server ke 2 [orcl2], perlu dibuat file controlnya
[oracle@orcl1 ~]$ vi dept.ctl
load data
INFILE dept_290609.txt
APPEND
into table DEPT
fields terminated by “,”
TRAILING NULLCOLS
(DEPTNO,DNAME,LOC)

7. periksa keadaan tabel sebelum di sqlloader
[oracle@orcl1 ~]$ sqlplus scott/tiger@orcl2
SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> exit

8. menjalankan sqlloader
[oracle@orcl1 ~]$ sqlldr userid=scott/tiger@orcl2 control=dept.ctl log=dept.log

9. periksa keadaan tabel sesudah di sqlloader
[oracle@orcl1 ~]$ sqlplus scott/tiger@orcl2
SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
41 IT SURABAYA
42 MKT SURABAYA
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> exit