Home » Server Options » Replication » Materialized Views
| Materialized Views [message #75568] |
Tue, 02 November 2004 03:37 |
vx
Messages: 2
Registered: December 2003
|
Junior Member |
|
|
<DIV id=intelliTxt style="PADDING-RIGHT: 5px; PADDING-LEFT: 5px">Hi Guys
Im having problems setting up Oracle Replication. Im trying to add a Master Site and a Materialized view from a site. Can some one PLEAAAAAAAASE help me. Im using oracle 9 and its giving me a error (parameter value proxy_repadmin is not appropriate) i marked it im the script.
This is the script im using ...
--MASTER1= master site
--SNAP1 = snapshot site
/**********************
CREATE REPADMIN USER
**********************/
CONNECT system/oracle@MASTER1
create user repadmin identified by repadmin;
alter user repadmin default tablespace PEO_DATA;
alter user repadmin TEMPORARY tablespace temp;
grant connect, resource to repadmin;
grant comment any table to repadmin;
grant lock any table to repadmin;
execute dbms_repcat_admin.grant_admin_any_schema('repadmin');
/**********************
CREATE REPPROXY USER
**********************/
create user repproxy identified by repproxy;
alter user repproxy default tablespace PEO_DATA;
alter user repproxy temporary tablespace temp;
grant create session to repproxy;
grant select any table to repproxy;
--When I run this i get the error ...
-- parameter value proxy_repadmin is not appropriate
BEGIN
dbms_repcat_admin.register_user_repgroup(
username => 'repproxy',
privilege_type => 'proxy_repadmin',
list_of_gnames => NULL);
END;
/
BEGIN
dbms_repcat_admin.register_user_repgroup(
username => 'repproxy',
privilege_type => 'receiver',
list_of_gnames => NULL);
END;
/
/*************************************************************************
SETUP THE SNAP1 SITE USERS
Note: 2 Users is required. REPADMIN and REPPROXY
REPADMIN = administer replication and maintenance
*************************************************************************/
/**********************
CREATE REPADMIN USER
**********************/
connect system/oracle@MASTER1;
create user repadmin profile default identified by repadmin default tablespace PEO_DATA
temporary tablespace temp account unlock;
grant alter any materialized view to repadmin;
grant create any materialized view to repadmin;
grant connect to repadmin;
grant comment any table to repadmin;
grant lock any table to repadmin;
execute dbms_repcat_admin.grant_admin_any_schema('repadmin');
execute dbms_defer_sys.register_propagator('repadmin');
grant connect, resource to sysadm;
grant create table to sysadm;
grant create materialized view to sysadm;
/*************************************************************************
CREATE DATABASE LINKS
Note: database links are required only from the SNAP1 site to the master site
*************************************************************************/
/**********************
CREATE PUBLIC LINK WITH THE SNAP1 DB
**********************/
connect system/oracle@MASTER1;
create public database link SNAP1 using 'SNAP1';
/**********************
CREATE a PRIVATE USER LINK
**********************/
connect repadmin/repadmin@MASTER1;
create database link SNAP1 connect to repproxy identified by repproxy;
**********************
CHECK THE LINK
Note: You should get the letter X and D column if the
link worked
**********************/
select * from dual@SNAP1;
/**********************
STEP 5.3
CREATE a PRIVATE DATABASE LINK
**********************/
connect sysadm/sysadm@MASTER1
create database link SNAP1 connect to repproxy identified by repproxy;
/**********************
CHECK THE LINK
**********************/
select * from dual@SNAP1;
/*************************************************************************
CREATE THE MATERIALIZED VIEW LOG
Note: a MV Log must be created on eac of the SNAP1 replicated tables
*************************************************************************/
create materialized view log on sysadm.EMP tablespace PEO_DATA with
primary key including new values;
/*************************************************************************
CREATE THE MASTER REPLICATION GROUP
Note: a MV Log must be created on eac of the SNAP1 replicated tables
*************************************************************************/
/**********************
CREATE THE MASTER REPLICATION GROUP
**********************/
connect repadmin/repadmin@SNAP1
begin
dbms_repcat.create_master_repgroup(
gname => '"REP_GP1"',
qualifier => '',
group_comment => '');
end;
/
/**********************
ADD THE TABLES FROM THE SYSADM SCHEMA
TO THE MASTER REPLICATION GROUP
**********************/
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => '"Rep_GP1"',
type => 'TABLE',
oname => '"EMP"',
sname => '"SYSADM"');
END;
/
/**********************
GENERATE REPLICATION SUPPORT FOR THE TBALE OBJECT
**********************/
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(
sname => '"SYSADM"',
oname => '"EMP"',
type => 'TABLE',
min_communication => TRUE);
END;
/
/**********************
CHECK THE DBA_REPCATLOG VIEW FOR ERRORS
Note: it should be empty
**********************/
select count(*) from dba_repcatlog;
/**********************
ADD THE REMAINING TABLES TO THE REPLICATION GROUP
**********************/
connect repadmin/repadmin@SNAP1
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => '"Rep_GP1"',
type => 'TABLE',
oname => '"EMP_ADD"',
sname => '"SYSADM"',
COPY_ROWS => TRUE);
END;
/
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(
sname => '"SYSADM"',
oname => '"EMP_ADD"',
type => 'TABLE',
min_communication => TRUE);
END;
/
/**********************
NOTIFY THE MASTER DB TO TRACK ACTIVITY ON THE REP_GP1
OBJECT BY RESUMING REPLICATION ACTIVITY
**********************/
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY(
gname => '"REP_GP1"');
END;
/
/**********************
check if the status = normal
**********************/
SELECT SNAME, MASTER, STATUS FROM DBA_REPGROUP;
/*************************************************************************
CREATE THE UPDATABLE MV AT THE REMOTE DB
*************************************************************************/
/**********************
CREATE A REFRESH GROUP
**********************/
begin
dbms_refresh.make(
name => '"REPADMIN".GROUPA"',
list => '',
next_date => SYSDATE,
interval => '/*1:Mins*/ sysdate + 1/(60*24)',
implicit_destroy => FALSE,
lax => FALSE,
job => 0,
rollback_seg => NULL,
push_deferred_rpc => TRUE,
refresh_alter_errors => TRUE,
purge_option => NULL,
parallelism => NULL,
heap_size => NULL);
end;
/
/**********************
CREATE THE MV GROUP
**********************/
begin
dbms_repcat.create_mview_repgroup(
gname => '"REP_GP1"',
master => 'SNAP1',
propagation_mode => 'ASYNCHRONOUS');
end;
/
/**********************
CREATE THE TABLE UPDATABLE MV
**********************/
create materialized view "SYSADM"."EMP"
refresh fast for update
as select * from "SYSADM"."EMP"@SNAP1 c
/**********************
ADD THE OTHER TABLES TO THE SNAP1 REFRESH GROUP
**********************/
begin
dbms_refresh.add(
name => '"REPADMIN"."GROUPA"',
list =>'"SYSADM"."EMP"',
lax => TRUE);
end;
/
/**********************
UPDATE THE SNAP1 REPLICATION GROUP
**********************/
begin
dbms_repcat.create_mview_repobject(
gname => '"REP_GP1"',
sname => '"SYSADM"',
oname => '"EMP"',
type => 'SNAPSHOT',
min_communication => TRUE);
end;
/
/**********************
ADD OTHER SNAP1s
**********************/
create materialized view "SYSADM"."EMP_ADD"
refresh fast for update
as select * from "SYSADM"."EMP_ADD"@SNAP1 c
begin
dbms_refresh.add(
name => '"REPADMIN"."GROUPA"',
list =>'"SYSADM"."EMP_ADD"',
lax => TRUE);
end;
/
begin
dbms_repcat.create_mview_repobject(
gname => '"REP_GP1"',
sname => '"SYSADM"',
oname => '"EMP_ADD"',
type => 'SNAPSHOT',
min_communication => TRUE);
end;
/
commit;
Any help will be appreciated
VX
</DIV>
|
|
|
|
Goto Forum:
Current Time: Thu Feb 06 14:16:19 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
