Re: Materialized view replication problem

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Thu, 14 Apr 2005 15:18:35 +0200
Message-ID: <d3lqcn$ch7$1@news4.zwoll1.ov.home.nl>

Bing Wu wrote:
> Frank van Bortel wrote:
>

>> And *how* did you come to the conclusion it doesn't
>> work? Refreshes every minute may not happen as your
>> standard mask rounds to the day (that is, 12 o'clock
>> noon).
>> Did you check when the next job was scheduled?

>
>
> It is a very small table and no single auto-refreshes seems worked since
> I setup a couple of days ago. I have traced down to the alert log and it
> reports this for a few minutes then stoped:
>
> ORA-12012: error on auto execute of job 46
> ORA-01017: invalid username/password; logon denied
> ORA-06512: at "SYS.DBMS_SNAPSHOT", line 1883
> ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2089
> ORA-06512: at "SYS.DBMS_IREFRESH", line 683
> ORA-06512: at "SYS.DBMS_REFRESH", line 195
>
> I use repadmin as the replication user and tested working under sqlplus.
> Does the above job use different user instead? I would guess it was
> 'sys'. Any idea to change / fix this?
>
> Bing
>

Ah - trusted/untrusted model? Why else use a repadmin user? Do you plan to use templates for instantiation? Conflict resolution?

Anyway - I cannot see why you have a password error in your job - could be on the remote side, could be explicit grants are missing.
What I used to do (this is from 3 years back - runs on 8i): is according to doc id 118594.1 and 112075.1:

/* Setting up a replication enviroment Doc Id 112075.1 */

connect system/manager as sysdba
/* create REPADMIN user */
create user repadmin identified by repadmin default tablespace tools
temporary tablespace temp;

--
/* Grant privileges to REPADMIN */
grant connect, resource to repadmin;
grant comment any table, lock any table to REPADMIN;
--
grant execute any procedure to REPADMIN;

/* Granting admin privileges to REPADMIN */

BEGIN
   DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA(
	username => 'REPADMIN');
END;
/
/*Connecting as user REPADMIN...*/
connect repadmin/repadmin

/* Scheduling purge  */

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PURGE(
   next_date => SYSDATE,
   interval => '/*2 Hr*/ sysdate + 2/24',
   delay_seconds => 0,
   rollback_segment => '');
END;
/
/* Setting up Replication Snapshot */
execute dbms_reputil.replication_off;

/* connect as schema owner */
connect scott/tiger

create snapshot log on emp
with primary key;
--
/***
*** Setting up Replication ***
***/
-- Set up Replication Group
connect repadmin/repadmin
--
BEGIN
  DBMS_REPCAT.CREATE_MASTER_REPGROUP('MG');
END;
/
/* Column Group Definition */
-- maakt GEEN onderdeel uit van conflict
-- resolution, maar normale setup.
--
-- Dit moet voor col group...
/* Adding Master tables to Master Group */
BEGIN
  DBMS_REPCAT.CREATE_MASTER_REPOBJECT('SCOTT','EMP','TABLE',
   gname => 'MG');
END;
/
/***
*** Setting up Replication ***
***/
-- Set up Replication Group
connect repadmin/repadmin
--
BEGIN
  DBMS_REPCAT.CREATE_MASTER_REPGROUP('MG');
END;
/

/* Adding Master tables to Master Group */
BEGIN
  DBMS_REPCAT.CREATE_MASTER_REPOBJECT('SCOTT','EMPLOYEE','TABLE',
   gname => 'MG');
END;
/

commit;

/* Adding Replication Support on Master Tables */
BEGIN
  DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT('SCOTT','EMP','TABLE');
END;
/
--
/* Resume Activity */
BEGIN
  DBMS_REPCAT.RESUME_MASTER_ACTIVITY('MG');
END;
/
/* Template stuff - Doc Id 118594.1 */
/****
 explicit grants for REPADMIN on master tables...
****/
connect scott/tiger
grant select on emp to REPADMIN;

-- 
Regards,
Frank van Bortel

Received on Thu Apr 14 2005 - 08:18:35 CDT