Oracle Advanced Replication (2)

1 Sep

urutan – urutan script oracle advance replikasi

========================================

1 — unlock user scott & setup global
ORACLE_SID=iscatm; export ORACLE_SID
sqlplus / as sysdba
alter user scott account unlock;
alter user scott identified by tiger;
conn scott/tiger;

2 conn system/ORACLE0@iscatm
alter system set global_names=TRUE;
alter system set job_queue_processes = 1;

3 — 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;

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

5 GRANT SELECT ANY DICTIONARY TO repadmin;

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

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

8 — Schedule job to push transactions to all master sites with appropriate intervals
CONNECT repadmin/repadmin@iscatm

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
— interval 1 jam
interval => ‘SYSDATE + 1/24’,
delay_seconds => 0);
END;
/
select to_char(sysdate , ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
select to_char(sysdate + 1/24, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;

9 — unlock user scott & setup global
ORACLE_SID=iscatm2; export ORACLE_SID
sqlplus / as sysdba
alter user scott account unlock;
alter user scott identified by tiger;
conn scott/tiger;

10 conn system/ORACLE0@iscatm2
alter system set global_names=TRUE;
alter system set job_queue_processes = 1;

11 — 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;

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

13 GRANT SELECT ANY DICTIONARY TO repadmin;

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

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

16 — Schedule job to push transactions to all master sites with appropriate intervals
CONNECT repadmin/repadmin@iscatm2

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
— interval 1 jam
interval => ‘SYSDATE + 1/24’,
delay_seconds => 0);
END;
/
select to_char(sysdate , ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
select to_char(sysdate + 1/24, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;

— Create private db links for all repadmin users
17 conn system/ORACLE0@iscatm
create public database link iscatm2 using ‘iscatm2’;
conn repadmin/repadmin@iscatm
create database link iscatm2 connect to repadmin identified by repadmin;

18 conn repadmin/repadmin@iscatm
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => ‘ISCATM2.LOCALDOMAIN’,
— interval 10 menit
interval => ‘SYSDATE + (1/144)’,
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1500,
delay_seconds => 1200);
END;
/
select to_char(sysdate , ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
select to_char(sysdate + 1/144, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
— Create private db links for all repadmin users
19 conn system/ORACLE0@iscatm2
create public database link iscatm using ‘iscatm’;
conn repadmin/repadmin@iscatm2
create database link iscatm connect to repadmin identified by repadmin;

20 conn repadmin/repadmin@iscatm2
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => ‘ISCATM.LOCALDOMAIN’,
— interval 10 menit
interval => ‘SYSDATE + (1/144)’,
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1500,
delay_seconds => 1200);
END;
/
select to_char(sysdate , ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
select to_char(sysdate + 1/144, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
— Create replication group for MASTERDEF site
— Register objects within the group
— Add master desination sites
21 conn repadmin/repadmin@iscatm
begin
dbms_repcat.create_master_repgroup (
gname => ‘scott_repg’);
end;
/

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

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

24 BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => ‘scott_repg’,
master => ‘iscatm2’,
use_existing_objects => TRUE,
copy_rows => FALSE,
propagation_mode => ‘ASYNCHRONOUS’);
END;
/

25 conn repadmin/repadmin@iscatm
SELECT DBLINK FROM DBA_REPSITES WHERE GNAME = ‘SCOTT_REPG’;

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

27 BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘scott’,
oname => ‘dept’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/
28 — nilai harus 0
conn repadmin/repadmin@iscatm
SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = ‘SCOTT_REPG’;
— jika nilai tidak 0
— jalankan repmon.sql catat job id
conn repadmin/repadmin@iscatm
BEGIN
dbms_job.run(49);
dbms_job.run(50);
dbms_job.run(51);
END;
/
SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = ‘SCOTT_REPG’;
SELECT GNAME, STATUS FROM DBA_REPGROUP;
29 — nilai harus 0
conn repadmin/repadmin@iscatm2
SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = ‘SCOTT_REPG’;
— jika nilai tidak 0
— jalankan repmon.sql catat job id
conn repadmin/repadmin@iscatm2
BEGIN
dbms_job.run(49);
dbms_job.run(50);
dbms_job.run(51);
END;
/
SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = ‘SCOTT_REPG’;
SELECT GNAME, STATUS FROM DBA_REPGROUP;
30 — Start Replication
conn repadmin/repadmin@iscatm
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => ‘scott_repg’);
END;
/
— stop replikasi jika ingin mematikan
conn repadmin/repadmin@iscatm
— Stop Replication
BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => ‘scott_repg’);
END;
/
31 — status replikasi
conn repadmin/repadmin@iscatm
SELECT GNAME, STATUS FROM DBA_REPGROUP;
conn repadmin/repadmin@iscatm2
SELECT GNAME, STATUS FROM DBA_REPGROUP;
—- NORMAL: Normal unquiesced state.
—- QUIESCING: Being quiesced, but some non-DBA sessions are still active.
QUIESCED: Quiesced; no non-DBA sessions are active or allowed.

32 — test replikasi
conn scott/tiger@iscatm
select * from dept;
insert into dept values (50,’DUMMY’,’DUMMY’);
commit;
select * from dept;
select * from dept@iscatm2;
33 — paksa supaya segera replikasi
conn repadmin/repadmin@iscatm
BEGIN
dbms_job.run(49);
dbms_job.run(50);
dbms_job.run(51);
END;
/
select to_char(sysdate, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
SELECT GNAME, STATUS FROM DBA_REPGROUP;
conn scott/tiger@iscatm2
select * from dept order by deptno;
34 — test replikasi 2
conn scott/tiger@iscatm2
delete dept where deptno > 40;
commit;
select * from dept;
select * from dept@iscatm;
35 — paksa supaya segera replikasi
conn repadmin/repadmin@iscatm2
BEGIN
dbms_job.run(49);
dbms_job.run(50);
dbms_job.run(51);
END;
/
select to_char(sysdate, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
SELECT GNAME, STATUS FROM DBA_REPGROUP;
conn scott/tiger@iscatm
select * from dept order by deptno;

2 Responses to “Oracle Advanced Replication (2)”

  1. iwan March 17, 2010 at 11:30 am #

    Malam Pak,

    saya saat ini sangat membutuhkan untuk replikasi dengan oracle 10g.
    boleh saya minta step untuk pembuatan replikasi oracle 10g

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: