Create Advance Replication with Metode Conflicts Resolution

28 Aug

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 https://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;
/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: