 |
|
ORA-23357: the propagator does not exist tips
Oracle Tips by Burleson Consulting
December 21, 2007
|
Question: I'm trying to define a propagator for a
materialized view and I'm getting the "ORA-23357: the propagator does not
exist" error:
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
3 gname => 'emp_repg',
4 sname => 'SCOTT',
5 oname => 'EMP',
6 type => 'SNAPSHOT',
7 min_communication => TRUE);
8 END;
9 /
BEGIN
*
ERROR at line 1:
ORA-23357: the propagator does not exist
How do I check the status of materialized view propagators?
Answer: Please see Col. Garmany's notes on
defining a
replication propagator for a materialized view, especially the part about
the repadmin user. Also see these notes on
creating materialized view
refresh groups.
The docs note this on the ORA-23357
error, a generic note that the propagator is missing:
ORA-23357: the propagator does
not exist
Cause: The propagator does not exist.
Action: Register a new propagator.
On the remote site, replication is administered by
repadmin. Repadmin is the snapshot administrator, the propagator, and the
refresher. A database link connects repadmin with repproxy. Using a private link
restricts user access to the base tables on the master site. Repproxy is granted
rights to the objects being replicated. Repadmin (on the remote site) contacts
repproxy and pushes changes, which repproxy applies. Oracle provides these views
for monitoring replication:
DBA_REPSCHEMA. This view contains details for
the replication schema.
DBA_REPCATLOG. This view provides a log of all replication
activities.
DBA_JOBS. Use this view to monitor all scheduled job in the database.
DBA_REPCAT. This view shows the replication catalog.
ALL_REPCONFLICT. This view provides a list of all replication
conflicts.
ALL_REPRESOLUTION. For systems defined with pre-defined conflict
resolution, this view lists the resolution of every conflict.
DBA_REPOBJECT. This view gives you a list of al replicated objects.
DBA_REPSITES. This view provides is a list of replicated sites.
This query can check the status of the MV propagator:
select
username,
status,
date_created
from
defpropagator;
Also, these scripts check the staus of replication:
connect repadmin/repadmin
set pages 50000
col sname format a20 head "SchemaName"
col masterdef format a10 head "MasterDef?"
col oname format a20 head "ObjectName"
col gname format a20 head "GroupName"
col object format a35 trunc
col dblink format a35 head "DBLink"
col message format a25
col broken format a6 head "Broken?"
prompt Replication schemas/ sites
select
sname,
masterdef,
dblink
from
sys.dba_repschema;
prompt RepCat Log (after a while you should see no entries):
select
request,
status,
message,
errnum
from
sys.dba_repcatlog;
prompt Entries in the job queue
select
job,
last_date,
last_sec,
next_date,
next_sec,
broken,
failures,
what
from
sys.dba_jobs
where
schema_user = 'REPADMIN';
prompt Replication Status:
select
sname,
master,
status
from
sys.dba_repcat;
prompt Returns all conflict resolution methods
select * from all_repconflict;
prompt Returns all resolution methods in use
select * from all_represolution;
prompt Objects registered for replication
select
gname,
type||' '||sname||'.'||oname object,
status
from
sys.dba_repobject;
select * from dba_repsites;
For more details and working
scripts to define replication, see the books on
Oracle
Replication and
Oracle Streams.