Re: Desperately having problem ora-23375 with 9i

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sun, 16 Jun 2002 09:42:38 +0200
Message-ID: <o8gogu0036gi01evj99k73jnafj51hjedc@4ax.com>

On Sat, 15 Jun 2002 21:22:41 +0200, "Peter B." <chief.o.brien_at_spacemail.com> wrote:

>----[ Providing details ]----
>I'm running two databases on Oracle 9i enterprise on Linux Suse 7.3
>
>those 2 databases were created using dbca and are based on the "data
>warehouse" template.
>I've downloaded the example replication scripts "repprep.sql" and
>"repdef.sql" scripts from oracle and modified just
>the database names - since it accesses default tables from scott it should
>work, shouldn't it?
>
>I've already run "catrep.sql" before I tried any further steps.
>
>I've had oracle 9i installed from the enterprise CDs, but didn't install it
>as enterprise, because I didn't know that I was
>gonna need the advanced replication feature - which I desperately tried to
>post-install after I encountered an error which
>told me that the replication feature wasn't installed (in v$option it was
>set FALSE) - I had to reinstall the whole oracle
>package, because installing this package only didn't work at all.
>
>I've already deleted and recreated both databases (always exactly identical)
>several times with different templates.
>
>Here are the 2 scripts I try to run:
>
>----------------------------------------------------------------------------
>--------------------------------------------
>rem -----------------------------------------------------------------------
>rem Filename: repprep.sql
>rem Purpose: Setup users, DB Links and schedules for Oracle Advanced
>rem replication. Run this script on all replication sites.
>rem In this example replication is between sites redbull and
>replica
>rem Date: 03-Oct-2000
>rem Author: Frank Naude (frank_at_ibi.co.za)
>rem -----------------------------------------------------------------------
>
>set pages 50000
>spool repprep
>
>connect sys/change_on_install_at_redbull as sysdba
>
>REM -- @?/rdbms/admin/catrep.sql
>
>REM Check if INIT.ORA parameters are OK for replication
>select name, value from sys.v_$parameter
>where name in ('job_queue_processes', 'job_queue_interval',
> 'global_name')
>
>REM Assign global name to the current DB
>alter database rename global_name to redbull; -- Change to your DB name +
>domain
>
>REM Create public db link to the other master databses
>drop database link replica;
>create public database link replica using 'REPLICA';
>
>REM Create replication administrator / propagator / receiver
>drop user repadmin cascade;
>create user repadmin identified by repadmin
> default tablespace USERS
> temporary tablespace TEMP
> quota unlimited on USERS;
>
>REM Grant privs to the propagator, to propagate changes to remote sites
>execute dbms_defer_sys.register_propagator(username=>'REPADMIN');
>
>REM Grant privs to the receiver to apply deferred transactions
>grant execute any procedure to repadmin;
>
>REM Authorise the administrator to administer replication groups
>execute dbms_repcat_admin.grant_admin_any_repgroup('REPADMIN');
>
>REM Authorise the administrator to lock and comment tables
>grant lock any table to repadmin;
>grant comment any table to repadmin;
>
>connect repadmin/repadmin_at_redbull
>
>REM Create private db links for repadmin
>drop database link replica;
>create database link replica
> connect to repadmin identified by repadmin using 'replica';
>
>REM Schedule job to push transactions to master sites
>execute dbms_defer_sys.schedule_push( -
> destination => 'replica', -
> interval => 'sysdate+1/24/60', -
> next_date => sysdate+1/24/60, -
> stop_on_error => FALSE, -
> delay_seconds => 0, -
> parallelism => 1);
>
>REM Schedule job to delete successfully replciated transactions
>execute dbms_defer_sys.schedule_purge( -
> next_date => sysdate+1/24, -
> interval => 'sysdate+1/24');
>
>REM Test database link
>select global_name from global_name_at_replica;
>
>spool off
>----------------------------------------------------------------------------
>--------------------------------------------
>
>
>----------------------------------------------------------------------------
>--------------------------------------------
>rem -----------------------------------------------------------------------
>rem Filename: repdef.sql
>rem Purpose: Define replication groups with replication objects
>rem Run on Master Definition site only. Run repprep.sql
>rem on all your databases before running this script.
>rem Date: 03-Oct-2000
>rem Author: Frank Naude (frank_at_ibi.co.za)
>rem -----------------------------------------------------------------------
>
>set pages 50000
>spool repdef
>
>connect repadmin/repadmin_at_redbull
>
>REM Create replication group for MASTERDEF site
>execute dbms_repcat.create_master_repgroup('MYREPGRP');
>
>REM Register objects within the group
>execute dbms_repcat.create_master_repobject('SCOTT', -
> 'EMP', 'TABLE', gname=>'MYREPGRP');
>
>execute dbms_repcat.make_column_group( -
> sname => 'SCOTT', -
> oname => 'EMP', -
> column_group => 'EMP_COLGRP', -
> list_of_column_names => 'EMPNO');
>
>execute dbms_repcat.add_update_resolution( -
> sname => 'SCOTT', -
> oname => 'EMP', -
> column_group => 'EMP_COLGRP', -
> sequence_no => 1, -
> method => 'LATEST TIMESTAMP', -
> parameter_column_name => 'EMPNO');
>
>REM Add master desination sites
>execute dbms_repcat.add_master_database('MYREPGRP', 'REPLICA');
>##this is the line that causes the 23375 error!
>
>REM Generate replication support for objects within the group
>execute dbms_repcat.generate_replication_support('SCOTT', 'EMP', 'table');
>
>exec dbms_repcat.resume_master_activity(gname=>'MYREPGRP');
>----------------------------------------------------------------------------
>--------------------------------------------
>

From Metalink

From: Oracle, Rowena Serna 21-Mar-02 20:34 Subject: Re : 9i Master to Master Replication Feature Incompatible error

You may be getting this for other reasons, not necessarily a compatibility error.

Some things to check on:

privileges given to repadmin
are both servers Enterprise Edition? are you sure standard edition is not being used?
make sure you database link has no reserved word in it.

Other than that, can you please email me your setup script, including all database link creations and privileges granted to owners of replicated tables and to repadmin.

see
<NOTE:117434.1> Initial steps required to a create Multi Master Replication environment v8.1
<NOTE:117437.1> Scripts to create Trusted / Untrusted Multi Master Replication v8.1

for reference. There isn't a script available for 9i yet on metalink, but you can use the above for reference.

And also see your replication manual.

Regards,
Rowena Serna
Oracle Corporation