Oracle – Linux – Network

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

d. jalankan script, di ORCL1

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

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

g. start replikasi

e. 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;
/