vicker313 tech blog

July 11, 2009

Setup Oracle Replication (Part 1: Master Site)

Filed under: Oracle — Tags: , — vicker313 @ 10:39 am

Setup Oracle Replication involves two parts: setup at master site, where the actual data is store, and the materialized view site (MV site), where the data replicated to. Materialized view is combination of view and table, which it selects data from another table, and store the selected data as its owned. This is also the major part of Oracle Replication.

BEWARE: Master Site Server needs to be stay static (no data changes) during the whole setup process, from Master Site Setup to MV Site Setup.

  1. Create replication administrator at master site and grant necessary privileges. Replication administrator is needed to be created in each server that participate the replication (Connect as SYSTEM).
    CREATE USER repadmin IDENTIFIED BY repadmin;
    EXECUTE DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA ('repadmin');
    GRANT COMMENT ANY TABLE TO repadmin;
    GRANT LOCK ANY TABLE TO repadmin;
    GRANT SELECT ANY DICTIONARY TO repadmin;
  2. Register the propagator at master site, which responsible for propagating the deferred transaction queue to other master sites (SYSTEM).
    EXECUTE DBMS_DEFER_SYS.REGISTER_PROPAGATOR('repadmin');
  3. Schedule purge at master site, in order to keep the size of the deferred transaction queue in check (Connect as REPADMIN).
    BEGIN
    DBMS_DEFER_SYS.SCHEDULE_PURGE (
    next_date => SYSDATE,
    interval => 'SYSDATE + 7',
    delay_seconds => 0);
    END;
    /
  4. Create proxy master site users at master site (SYSTEM).
    CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;
    EXECUTE DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP ('proxy_mviewadmin', 'proxy_snapadmin', NULL);
    GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;
  5. Create the proxy refresher. The proxy refresher performs tasks at the master site on behalf of the refresher at the materialized view site (SYSTEM).
    CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;
    GRANT CREATE SESSION TO proxy_refresher;
    GRANT SELECT ANY TABLE TO proxy_refresher;
  6. Create the master group (REPADMIN).
    EXECUTE DBMS_REPCAT.CREATE_MASTER_REPGROUP('master_repg');
  7. Add objects (that wanted to be replicated to MV site) into master group (REPADMIN).
    BEGIN
    DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
    gname => '"MASTER_REPG"',
    type => '[TABLE|INDEX|VIEW|FUNCTION]',
    oname => '"[OBJECT NAME]"',
    sname => '"[SCHEMA]"',
    copy_rows => TRUE,
    use_existing_object => TRUE);
    END;
    /
  8. Generate replication support to tables that needed to be replicated to MV site (REPADMIN).
    BEGIN
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(
    sname => '"[SCHEMA]"',
    oname => '"[OBJECT NAME]"',
    type => '[TABLE]',
    min_communication => TRUE,
    generate_80_compatible => FALSE);
    END;
    /
  9. Start the replication (REPADMIN).
    BEGIN
    DBMS_REPCAT.RESUME_MASTER_ACTIVITY (gname => 'master_repg');
    END;
    /
  10. Create the necessary materialized view logs, if they do not exist (REPADMIN).
    create materialized view log on [table name];

Continue to Setup MV Site.

Advertisements

2 Comments »

  1. […] (Part 2: MV Site) Now continue to setup the Materialized View site (MV site). Please refer here to setup master […]

    Pingback by Setup Oracle Replication (Part 2: MV Site) « vicker313 tech blog — July 11, 2009 @ 10:42 am

  2. […] here to setup Master […]

    Pingback by Setup Oracle Replication (Part 3: Offline Export and Import) « vicker313 tech blog — July 19, 2009 @ 11:44 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: