Database Software

Hello everybody,

as the subject says i have a problem when i try to generate replication support to the objects of the master group. Let me tell you what i have done step by step because i'm new in oracle administration.

First of all i have an Oracle database ver 9.2.0.1.0 in Windows 2003 Server. I want to replicate this database to another Win 2003 Server as a Materialized View. I found somewhere a step by step manual with PL/SQL scripts and here are the steps that i followed:

Step 1
Create replication administrator at the database that i will use as master, let's call it MASTER_DB.

CREATE USER repadmin IDENTIFIED BY repadmin;

Step 2
Grant privileges to replication administrator at MASTER_DB.

BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => 'repadmin');
END;

Step 3
Register propagator at MASTER_DB.

BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
username => 'repadmin');
END;

Step 4
Register receiver at MASTER_DB.

BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'repadmin',
privilege_type => 'receiver',
list_of_gnames => NULL);
END;

Step 5
Schedule purge at master site MASTER_DB.

CONNECT repadmin/repadmin@MASTER_DB
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/24',
delay_seconds => 0);
END;

Step 6
If you plan to create materialized view sites based on this master site, then create proxy master site users at MASTER_DB that correspond to users at the materialized view site by completing the following steps:
Create proxy materialized view administrator.
The proxy materialized view administrator performs tasks at the target master site on behalf of the materialized view administrator at the materialized view site.

CONNECT SYSTEM/MANAGER@MASTER_DB
CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;
BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'proxy_mviewadmin',
privilege_type => 'proxy_snapadmin',
list_of_gnames => NULL);
END;

Step 7
Create proxy refresher. The proxy refresher performs tasks at the master site on behalf of the refresher at the materialized view site.

CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;
GRANT CREATE SESSION TO proxy_refresher;
GRANT SELECT ANY TABLE TO proxy_refresher;

In the Materialized View Site, MV_DB

Step 1
Connect as SYSTEM at materialized view site at MV_DB.

CONNECT SYSTEM/MANAGER@MV_DB

Step 2
Create materialized view site users at MV_DB. Several users must be created at the materialized view site. These users are:
Materialized view administrator
Propagator
Refresher
Receiver (if the site will serve as a master materialized view site for other materialized views, as MV_DB is)
Complete the following steps to create these users.
Create materialized view administrator. The materialized view administrator is responsible for creating and managing the materialized view site. Execute the GRANT_ADMIN_ANY_SCHEMA procedure to grant the materialized view administrator the appropriate privileges.

create user MVIEWADMIN identified by MVIEWADMIN;

BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => 'mviewadmin');
END;

GRANT COMMENT ANY TABLE TO mviewadmin;
GRANT LOCK ANY TABLE TO mviewadmin;

If you want your mviewadmin to be able to connect to the Replication Management tool, then grant SELECT ANY DICTIONARY to mviewadmin:

GRANT SELECT ANY DICTIONARY TO mviewadmin;

Create propagator.
The propagator is responsible for propagating the deferred transaction queue to the target master site.

CREATE USER propagator IDENTIFIED BY propagator;

BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
username => 'propagator');
END;

GRANT COMMENT ANY TABLE TO propagator;
GRANT LOCK ANY TABLE TO propagator;

GRANT SELECT ANY DICTIONARY TO propagator;

Create refresher.
The refresher is responsible for "pulling" changes made to the replicated tables at the target master site to the materialized view site. This user refreshes one or more materialized views. If you want the mviewadmin user to be the refresher, then this step is not required.

CREATE USER refresher IDENTIFIED BY refresher;
GRANT CREATE SESSION TO refresher;
GRANT ALTER ANY MATERIALIZED VIEW TO refresher;

Register receiver.
The receiver receives the propagated deferred transactions sent by the propagator from materialized view sites. The receiver is necessary only if the site will function as a master materialized view site for other materialized view sites.

BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'mviewadmin',
privilege_type => 'receiver',
list_of_gnames => NULL);
END;

Step 3
Create database links to the master site by completing the following steps.
Create public database link.

CONNECT SYSTEM/MANAGER@MV_DB
CREATE PUBLIC DATABASE LINK MASTER_DB USING 'MASTER_DB';

Create materialized view administrator database link.
You need to create a database link from the materialized view administrator at the materialized view site to the proxy materialized view administrator at the master site.

CONNECT mviewadmin/mviewadmin@MV_DB;
CREATE DATABASE LINK MASTER_DB
CONNECT TO proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

Create propagator/receiver database link.
You need to create a database link from the propagator at the materialized view site to the receiver at the master site. The receiver was defined when you created the master site.

CONNECT propagator/propagator@MV_DB
CREATE DATABASE LINK MASTER_DB
CONNECT TO repadmin IDENTIFIED BY repadmin;

Step 4
Schedule purge at the MV_DB materialized view site.

CONNECT mviewadmin/mviewadmin@MV_DB

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/24',
delay_seconds => 0,
rollback_segment => '');
END;

Step 5
If the materialized view site has a constant connection to its master site, then you can optionally schedule push at the mv1.world materialized view site.

CONNECT mviewadmin/mviewadmin@MV_DB

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => 'MASTER_DB',
interval => 'SYSDATE + 1/24',
next_date => SYSDATE,
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 0);
END;

Step 6
Create proxy users at the MV_DB materialized view site by completing the following steps.
Create proxy materialized view administrator.
The proxy materialized view administrator performs tasks at the target master materialized view site on behalf of the materialized view administrator at the materialized view sites based on this materialized view site. This user is not required if the site will not function as a master materialized view site for other materialized view sites.

CONNECT SYSTEM/MANAGER@MV_DB

CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'proxy_mviewadmin',
privilege_type => 'proxy_snapadmin',
list_of_gnames => NULL);
END;

Create proxy refresher.
The proxy refresher performs tasks at the master materialized view site on behalf of the
refresher at the materialized view sites based on this materialized view site. This user is not required if the site will not function as a master materialized view site for other materialized view sites.

CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;
GRANT CREATE SESSION TO proxy_refresher;
GRANT SELECT ANY TABLE TO proxy_refresher;

Step 7
Create master group.

CONNECT repadmin/repadmin@MASTER_DB

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP (
gname => 's01101_repg');
END;

Step 8
Add objects to master group. I just added only tables. They were a lot of tables, more than 1000.

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => '"S01101_REPG"',
type => 'TABLE',
oname => '"AAA"',
sname => '"S01101"',
copy_rows => FALSE,
use_existing_object => TRUE);
END;

Step 9
Generate replication support.

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(
type => 'TABLE',
oname => '"AAA"',
sname => '"S01101"',
min_communication => TRUE);
END;

Now here i have problem, in some tables i got the error below:
*** SCRIPT START : Session:SYS@MASTER_DB 18/1/2007 16:29:12 ***
Processing ...
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(

type => 'TABLE',
oname => '"ZSBF_LOG"',
sname => '"S01101"',

min_communication => TRUE);
END;
BEGIN
*
ORA-23308: object S01101.ZSBF_LOG does not exist or is invalid
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2906
ORA-06512: at "SYS.DBMS_REPCAT", line 766
ORA-06512: at line 2
*** SCRIPT END : Session:SYS@MASTER_DB 18/1/2007 16:29:12 ***

The tables do exist but i don't know how to make them valid. Any idea?