Create Advance Replication [AGAIN & SIMPLE]

29 Aug

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

2 Responses to “Create Advance Replication [AGAIN & SIMPLE]”

  1. iwan March 18, 2010 at 6:35 am #

    Siang Pak?
    saya sedang mencoba replikasi seperti yang ada di sample.

    saya menggunakan Oracle 10g.

    saat menjalankan script ini :
    CREATE USER repadmin IDENTIFIED BY repadmin;

    BEGIN
    DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
    username => “repadmin”);
    END;

    ada error :
    BEGIN
    DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
    username => “repadmin”);
    END;
    Error at line 3
    ORA-06550: line 3, column 13:
    PLS-00201: identifier ‘repadmin’ must be declared
    ORA-06550: line 2, column 1:
    PL/SQL: Statement ignored

    kira-kira kesalahan nya apa ya?

  2. setijo agus September 24, 2010 at 7:52 am #

    pada perintah CREATE USER repadmin IDENTIFIED BY repadmin;
    bikinnya pakai user apa ?, kalau pakai system mestinya nggak error

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: