Oracle – Linux – Network

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;