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>
Previous Topic: Foreign key in a table of diffrent database
Next Topic: ORA-23375: feature is incompatible with database version
Goto Forum:
  


Current Time: Thu Mar 28 06:16:36 CDT 2024