No. 11690
Oracle8.x(8i포함)에서의 multi-master replication setup script를 살펴본다.
setup 절차를 수행하기 전에 invalidate된 package 등은 모두 recompile한다.
select owner, object_name, object_type
from all_objects
where status = 'INVALID';
본 예에서 두개의 master db는 REP1, REP2를 이용한다.
------cut------
RUN THIS SECTION FIRST AT THE MASTER DEFINITION SITE!!!
# Connect as SYSTEM user on Master Definition Site...
connect system/manager@REP1;
# Verifying Master Definition Site Global Name REP1
select * from global_name;
# Create user REPADMIN...
create user REPADMIN identified by REPADMIN
default tablespace users temporary tablespace temp;
# Grant administrator privileges to REPADMIN
# at Master Definition Site...
begin
dbms_repcat_admin.grant_admin_any_schema(username => 'REPADMIN');
end;
/
# Grant comment any table and lock any table to REPADMIN at Master
# Definition Site...
grant comment any table to repadmin;
grant lock any table to repadmin;
# Register REPADMIN as a propagator at MASTER DEFINITION site REP1...
begin
dbms_defer_sys.register_propagator(username => 'REPADMIN');
end;
/
# Grant execute privileges to REPADMIN...
grant execute any procedure to REPADMIN;
# Now create a PUBLIC database link to site REP2 as the SYSTEM user...
create public database link REP2
using 'REP2';
# Now connect as the REPADMIN user and create a private database link
# along with scheduling the purge and push jobs.
connect repadmin/repadmin@REP1;
# Create a private database link as REPADMIN to REP2...
create database link REP2
connect to repadmin identified by repadmin;
# Schedule a PUSH job to push transactions to MASTER site REP2 Use 2
# parallel slave servers and push the queue at 3 minute intervals...
begin
dbms_defer_sys.schedule_push(
destination => 'rep2',
interval => '/* 3:Mins*/ sysdate + 3/(60*24)',
next_date => sysdate,
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 2);
end;
/
# Schedule a PURGE job to purge already propagated transactions
# from the local queue on REP1 at 5 minute intervals...
begin
dbms_defer_sys.schedule_purge(
next_date => sysdate,
interval => '/* 5:Mins*/ sysdate + 5/(60*24)',
delay_seconds => 0,
rollback_segment => '');
end;
/
# Connect as the SYSTEM user on REP1 Create user SCOTT...
connect system/manager@REP1;
create user scott identified by tiger
default tablespace users
temporary tablespace temp;
# Grant privileges to user SCOTT...
grant alter session to SCOTT;
grant create cluster to SCOTT;
grant create database link to SCOTT;
grant create sequence to SCOTT;
grant create session to SCOTT;
grant create synonym to SCOTT;
grant create table to SCOTT;
grant create view to SCOTT;
grant create procedure to SCOTT;
grant create trigger to SCOTT;
grant unlimited tablespace to SCOTT;
grant create type to SCOTT;
grant create any snapshot to SCOTT;
grant alter any snapshot to SCOTT;
# Connect as user SCOTT. Create SCOTT's EMP and DEPT tables,
# add and enable constraints, and populate tables...
connect scott/tiger@REP1;
# Create and populate table DEPT...
CREATE TABLE DEPT (
DEPTNO NUMBER(2) NOT NULL,
DNAME CHAR(14),
LOC CHAR(13),
CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY (DEPTNO));
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
commit;
# Create and poplulate table EMP...
CREATE TABLE EMP (
EMPNO NUMBER(4) NOT NULL,
ENAME CHAR(10),
JOB CHAR(9),
MGR NUMBER(4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) NOT NULL,
CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO)
REFERENCES DEPT (DEPTNO),
CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO));
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20);
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
commit;
# End of steps for part one to be done on the Master Definition Site
------cut------
# RUN THIS PART SECOND AT THE MASTER SITE!!!
# Connect as SYSTEM user on Master Site...
connect system/manager@rep2;
# Verify Master Site Global Name rep2...
select * from global_name;
# Create user REPADMIN...
create user repadmin identified by repadmin
default tablespace users
temporary tablespace temp;
# Grant administrator privileges to REPADMIN at Master Site rep2...
begin
dbms_repcat_admin.grant_admin_any_schema(
username => 'REPADMIN');
end;
/
# Grant comment any table and lock any table to REPADMIN
grant comment any table to repadmin;
grant lock any table to repadmin;
# Register REPADMIN as a propagator at MASTER site rep2...
begin
dbms_defer_sys.register_propagator(username => 'REPADMIN');
end;
/
# Grant execute privileges to REPADMIN...
grant execute any procedure to REPADMIN;
# Now create a PUBLIC database link to Master Definition Site REP1
# as the SYSTEM user...
create public database link REP1
using 'REP1';
# Now connect as the REPADMIN user and create a private database link
# along with scheduling the purge and push jobs.
connect repadmin/repadmin@rep2;
# Create a private database link as REPADMIN to REP1...
create database link REP1
connect to repadmin identified by repadmin;
#test the database link:
connect repadmin/repadmin@REP2
select * from global_name@REP1;
(do the same thing to the other db links - and also from the other direction - connecting to REP1 and test the other direction)
# If you receive ora-2085, check the global_name of the instance
# you are trying to connect to.
# Any other errors, you should examine the user you were when you
# created the database link,
# you should also try to drop and recreate the link exactly as
# it is stated in this document before continuing.
# Schedule a PUSH job to push transactions to MASTER DEFINITION site
# REP1 Use 2 parallel slave servers and push the queue at 3 minute
# intervals...
begin
dbms_defer_sys.schedule_push(
destination => 'REP1',
interval => '/* 3:Mins*/ sysdate + 3/(60*24)',
next_date => sysdate,
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 2);
end;
/
# Schedule a PURGE job to purge already propagated transactions from
# the local queue on REP2 at 5 minute intervals...
begin
dbms_defer_sys.schedule_purge(
next_date => sysdate,
interval => '/* 5:Mins*/ sysdate + 5/(60*24)',
delay_seconds => 0,
rollback_segment => '');
end;
/
# Connect as the SYSTEM user on REP2 Create user SCOTT...
connect system/manager@REP2;
create user scott identified by tiger
default tablespace users
temporary tablespace temp;
# Grant privileges to user SCOTT...
grant alter session to SCOTT;
grant create cluster to SCOTT;
grant create database link to SCOTT;
grant create sequence to SCOTT;
grant create session to SCOTT;
grant create synonym to SCOTT;
grant create table to SCOTT;
grant create view to SCOTT;
grant create procedure to SCOTT;
grant create trigger to SCOTT;
grant unlimited tablespace to SCOTT;
grant create type to SCOTT;
grant create any snapshot to SCOTT;
grant alter any snapshot to SCOTT;
commit;
# End of steps to be done in part two on the Master Site
------cut------
RUN THESE STEPS THIRD AT THE MASTER DEFINITION SITE!!!
# Connect as REPADMIN user on Master Definition Site...
connect repadmin/repadmin@REP1;
# Creat master replication group 'SCOTT'...
begin
dbms_repcat.create_master_repgroup(gname => 'SCOTT',
qualifier => '',
group_comment => '');
end;
/
# Add DEPT table to master group 'SCOTT' with create_master_repobject
# procedure...
begin
dbms_repcat.create_master_repobject(
gname => '"SCOTT"',
type => 'TABLE',
oname => '"DEPT"',
sname => '"SCOTT"',
use_existing_object => TRUE,
copy_rows => TRUE);
end;
/
# Add EMP table to master group 'SCOTT' with create_master_repobject
# procedure...
begin
dbms_repcat.create_master_repobject(
gname => '"SCOTT"',
type => 'TABLE',
oname => '"EMP"',
sname => '"SCOTT"',
use_existing_object => TRUE,
copy_rows => TRUE);
end;
/
# Add master database 'REP2' to master group 'SCOTT' using
# add_master_database procedure...
begin
dbms_repcat.add_master_database(
gname => '"SCOTT"',
master => 'REP2',
use_existing_objects => TRUE,
copy_rows => TRUE,
propagation_mode => 'ASYNCHRONOUS');
end;
/
# Generate replication support for table 'DEPT' with
# generate_replication_support procedure...
begin
dbms_repcat.generate_replication_support(
sname => '"SCOTT"',
oname => '"DEPT"',
type => 'TABLE',
min_communication => TRUE);
end;
/
# Generate replication support for table 'EMP' with
# generate_replication_support procedure...
begin
dbms_repcat.generate_replication_support(
sname => '"SCOTT"',
oname => '"EMP"',
type => 'TABLE',
min_communication => TRUE);
end;
/
# Resume replication on group 'SCOTT' from QUIESCED mode to NORMAL
# mode using procedure resume_master_activity...
begin
dbms_repcat.resume_master_activity(
gname => '"SCOTT"');
end;
/
commit;
# This is the end of the steps to take for part three
+++++++++++++++++++++ Final Notes and Actions ++++++++++++++++++++++
At this time the do_deferred_repcat_admin job on each site should be processing the requests in the sys.dba_repcatlog view.
Connect as REPADMIN on each side (MASTER DEF and MASTER) and run the following query:
select count(*) from sys.dba_repcatlog;
When this query returns 0 rows on both sites, all entries are processed and you are ready to replicate data.
Run the following query on both sites also as REPADMIN to ensure a NORMAL status prior to replicating data.
select gname, status from sys.dba_repcat;
Once status is 'NORMAL' you can proceed with your DML statements on your replicated tables (INSERT, UPDATE, DELETE) and watch the data get propagated between sites.
As noted above run the queries on the sys.dba_repcatlog andsys.dba_repcat views to ensure you are ready to replicate data.