Advanced Replication

This document only creating “Materialized View Replication ( You need one Master Site and more than on Materialized view site)” not for “Multimaster Replication”

Here I am using one master site called “US” and one materialized view site called “MV01”

The general architecture for Advanced Replication is

The above architecture only helps to configure “Advanced Replication”

Replication environment primary condition:

Primary Keys and Replicated Tables

If possible, each replicated table should have a primary key. Where a primary key is not possible, each replicated table must have a set of columns that can be used as a unique identifier for each row of the table. If the tables that you plan to use in your replication environment do not have a primary key or a set of unique columns, then alter these tables accordingly. In addition, if you plan to create any primary key materialized views based on a master table or master materialized view, then that master must have a primary key.

Foreign Keys and Replicated Tables

When replicating tables with foreign key referential constraints, Oracle recommends that you always index foreign key columns and replicate these indexes, unless no updates and deletes are allowed in the parent table. Indexes are not replicated automatically. To replicate an index, add it to the master group containing its table using either the Replication Management tool or the CREATE_MASTER_REPOBJECT procedure in the DBMS_REPCAT package.

Data types:

· VARCHAR2

· NVARCHAR2

· NUMBER

· DATE

· TIMESTAMP

· TIMESTAMP WITH TIME ZONE

· TIMESTAMP LOCAL TIME ZONE

· INTERVAL YEAR TO MONTH

· INTERVAL DAY TO SECOND

· RAW

· ROWID

· CHAR

· NCHAR

· User-defined datatypes

Oracle also supports the replication of tables and materialized views with columns that use the following large object types:

· Binary LOB (BLOB)

· Character LOB (CLOB)

· National character LOB (NCLOB)

The deferred and synchronous remote procedure call mechanism used for multimaster replication propagates only the piece-wise changes to the supported LOB datatypes when piece-wise updates and appends are applied to these LOB columns. Also, you cannot reference LOB columns in a WHERE clause of a materialized view's defining query.

You can replicate tables and materialized views that use user-defined types, including column objects, object tables, REFs, varrays, and nested tables.

Oracle does not support the replication of columns that use the LONG and LONG RAW datatypes. You should convert LONG datatypes to LOBs.

Oracle also does not support the replication of external or file-based LOBs (BFILEs). Attempts to configure tables containing columns of this datatype as master tables return an error message.

Oracle also does not support the replication of UROWID columns in master tables or updatable materialized views. However, UROWID columns are allowed in read-only materialized views.

Initialization Parameters

Configure the bellow parameter in Master and Materialized view sites.

SQL> ALTER SYSTEM SET GLOBAL_NAMES=TRUE SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10 SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET PARALLEL_MAX_SERVERS=20 SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET PARALLEL_MIN_SERVERS=0 SCOPE=SPFILE;

System altered.

Net Configuration:

Before you start replication you must configure master and replication site.

SQLNET.ORA

SQLNET.AUTHENTICATION_SERVICES= (NONE)

#NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

NAMES.DEFAULT_DOMAIN=EDKAL

SQLNET.EXPIRE_TIME=10

TNSNAMES.ORA

US.EDKAL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.100.100.168)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = US.EDKAL)

)

)

MV01.EDKAL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.100.100.105)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = MV01.EDKAL)

)

)

Master Site Replication:

Master site (US), I want to replicate “BABU” schema to Materialized view site (MV01).

Use the above diagram step by step you can configure Advanced Replication.

Step1: Master Site

SQL> connect system@US

Connected.

SQL> show parameter db_name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name string US

SQL> REM Create Replication administrator user and give necessary privilege to this user. This Replication must be create in each site.

SQL> CREATE USER REPADMIN IDENTIFIED BY REPADMIN;

User created.

SQL> REM Execute GRANT_ADMIN_ANY_SCHEMA privilege to Replication Administrator

SQL> BEGIN

2 DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (

3 username => 'repadmin' );

4 END;

5 /

PL/SQL procedure successfully completed.

SQL> REM If you want create any materialized view log in Replicaton administrator in master site. Execute the below privillege.

SQL> GRANT COMMENT ANY TABLE TO REPADMIN;

Grant succeeded.

SQL> GRANT LOCK ANY TABLE TO REPADMIN;

Grant succeeded.

SQL> REM If want connect Replication Management Tool, Execute the bellow privilege.

SQL> GRANT SELECT ANY DICTIONARY TO REPADMIN;

Grant succeeded.

SQL> REM This is responsable for propagater deferred transaction queue.

SQL> BEGIN

2 DBMS_DEFER_SYS.REGISTER_PROPAGATOR ( username => 'repadmin') ;

3 END;

4 /

PL/SQL procedure successfully completed.

SQL> REM If want to recevie propagater deferred transaction sent by propagater.

SQL> BEGIN

2 DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (

3 username => 'repadmin',

4 privilege_type => 'receiver',

5 list_of_gnames => NULL );

6 END;

7 /

PL/SQL procedure successfully completed.

SQL> REM Connnect Replication Administrator

SQL> Connect repadmin/repadmin@US

Connected.

SQL> REM You need to schedule automated purge process.

SQL> BEGIN

2 DBMS_DEFER_SYS.SCHEDULE_PURGE (

3 next_date => SYSDATE,

4 interval => 'SYSDATE+1/24',

5 delay_seconds => 0);

6 END;

7 /

PL/SQL procedure successfully completed.

SQL> REM Connect System user in Master site.

SQL> Connect System@US

Connected.

SQL> REM If you plan create materialized view site based on master site you should be create proxy materialized view admin.

SQL>

SQL> CREATE USER proxy_mviewadmin identified by proxy_mviewadmin;

User created.

SQL> BEGIN

2 DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (

3 username => 'proxy_mviewadmin',

4 privilege_type => 'proxy_snapadmin',

5 list_of_gnames => NULL );

6 END;

7 /

PL/SQL procedure successfully completed.

SQL> GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;

Grant succeeded.

SQL> REM Suppose you no need proxy materialized view administrator in your Replicatoin management tool, You should be create

SQL> REM Proxy refresher, It's usefull for refreshing master site and materialized view site.

SQL> CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;

User created.

SQL> GRANT CREATE SESSION TO proxy_refresher;

Grant succeeded.

SQL> GRANT SELECT ANY TABLE TO proxy_refresher;

Grant succeeded.

SQL> Spool off

Step2: Master Group

Before you start master group, Let us assume “BABU” Schema exist in US.Edkal. I want replicate following objects…

EMP – table

DEPT – table

And the above two objects dependent objects like ‘Index etc…”

SQL> REM Installation for master group

SQL> connect repadmin/repadmin@US

Connected.

SQL> REM Create master group in your replication administrator environment.

SQL> BEGIN

2 DBMS_REPCAT.CREATE_MASTER_REPGROUP ( gname => 'babu_rep' );

3 END;

4 /

PL/SQL procedure successfully completed.

SQL> REM Add objects in your master group.

SQL>

SQL>

SQL> BEGIN

2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (

3 gname => 'babu_rep',

4 sname => 'BABU',

5 oname => 'EMP',

6 type => 'TABLE',

7 use_existing_object => TRUE ,

8 copy_rows => FALSE );

9 END;

10 /

PL/SQL procedure successfully completed.

SQL> ED

Wrote file afiedt.buf

1 BEGIN

2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (

3 gname => 'babu_rep',

4 sname => 'BABU',

5 oname => 'DEPT',

6 type => 'TABLE',

7 use_existing_object => TRUE ,

8 copy_rows => FALSE );

9* END;

SQL> /

PL/SQL procedure successfully completed.

SQL> ED

Wrote file afiedt.buf

1 BEGIN

2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (

3 gname => 'babu_rep',

4 sname => 'BABU',

5 oname => 'pro_make_dt_script',

6 type => 'procedure',

7 use_existing_object => TRUE ,

8 copy_rows => FALSE );

9* END;

SQL> /

PL/SQL procedure successfully completed.

SQL> ed

Wrote file afiedt.buf

1 BEGIN

2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (

3 gname => 'babu_rep',

4 sname => 'BABU',

5 oname => 'Fun_ExperimentName',

6 type => 'function',

7 use_existing_object => TRUE ,

8 copy_rows => FALSE );

9* END;

SQL> /

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

SQL> ED

Wrote file afiedt.buf

1 BEGIN

2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (

3 sname => 'BABU',

4 oname => 'RDSECURITY',

5 type => 'TABLE',

6 min_communication => TRUE);

7* END;

SQL> /

PL/SQL procedure successfully completed.

1 BEGIN

2 DBMS_REPCAT.DROP_MASTER_REPOBJECT (

3 sname => 'BABU',

4 oname => 'SWSETTINGS',

5 type => 'TABLE');

6* END;

SQL> /

PL/SQL procedure successfully completed.

SQL> BEGIN

2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (

3 gname => 'babu_rep',

4 sname => 'BABU',

5 oname => 'SWSETTINGS',

6 type => 'TABLE',

7 use_existing_object => TRUE ,

8 copy_rows => FALSE );

9 END;

10 /

PL/SQL procedure successfully completed.

SQL> BEGIN

2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (

3 sname => 'BABU',

4 oname => 'SWSETTINGS',

5 type => 'TABLE',

6 min_communication => TRUE);

7 END;

8 /

PL/SQL procedure successfully completed.

SQL> BEGIN

2 DBMS_REPCAT.RESUME_MASTER_ACTIVITY ( gname => 'babu_rep' );

3 end;

4 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> spool off

Step3: Materialized view Site configuration

SQL> connect system@MV01

Connected.

SQL> CREATE USER mviewadmin IDENTIFIED BY mviewadmin;

User created.

SQL> BEGIN

2 DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA ( username => 'mviewadmin' );

3 END;

4 /

PL/SQL procedure successfully completed.

SQL> GRANT COMMENT ANY TABLE, LOCK ANY TABLE TO mviewadmin;

Grant succeeded.

SQL> GRANT SELECT ANY DIcTIONARY TO mviewadmin;

Grant succeeded.

SQL> CREATE USER propagator IDENTIFIED BY propagator;

User created.

SQL> BEGIN

2 DBMS_DEFER_SYS.REGISTER_PROPAGATOR ( username => 'propagator' );

3 END;

4 /

PL/SQL procedure successfully completed.

SQL> rem he 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.

SQL> CREATE USER refresher IDENTIFIED BY refresher;

User created.

SQL> GRANT CREATE SESSION, ALTER ANY MATERIALIZED VIEW TO refresher;

Grant succeeded.

SQL> REM Register

SQL> REM It's recevie propagator deferred transaction sent by propagator.

SQL> BEGIN

2 DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (

3 username => 'mviewadmin',

4 privilege_type => 'receiver',

5 list_of_gnames => NULL );

6 END;

7 /

PL/SQL procedure successfully completed.

SQL> REM Create DB Link for master site.

SQL> CONNECT SYSTEM@MV01

Connected.

SQL> CREATE PUBLIC DATABASE LINK US.EDKAL USING 'US.EDKAL';

Database link created.

SQL> Connect mviewadmin/mviewadmin@MV01

Connected.

SQL> CREATE DATABASE LINK US.EDKAL CONNECT TO proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin USING 'US.EKDAL';

Database link created.

SQL> REM 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.

SQL> connect propagator/propagator@MV01

Connected.

SQL> CREATE DATABASE LINK US.EDKAL CONNECT TO repadmin IDENTIFIED BY repadmin USING 'US.EKDAL';

Database link created.

SQL> connect mviewadmin/mviewadmin@MV01

Connected.

SQL> ED

Wrote file afiedt.buf

1 BEGIN

2 DBMS_DEFER_SYS.SCHEDULE_PURGE (

3 next_date => SYSDATE,

4 interval => 'SYSDATE+1/24',

5 delay_seconds => 0,

6 rollback_segment => '');

7* END;

SQL> /

PL/SQL procedure successfully completed.

SQL> CONNECT MVIEWADMIN/MVIEWADMIN@MV01

Connected.

SQL> BEGIN

2 DBMS_DEFER_SYS.SCHEDULE_PUSH (

3 destination => 'US.EDKAL',

4 interval => 'SYSDATE+1/24',

5 next_date => SYSDATE,

6 stop_on_error => FALSE,

7 delay_seconds => 0,

8 parallelism => 0 );

9 END;

10 /

PL/SQL procedure successfully completed.

SQL> CONNECT SYSTEM@MV01

Connected.

SQL> CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

User created.

SQL> BEGIN

2 DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (

3 username => 'proxy_mviewadmin',

4 privilege_type => 'proxy_snapadmin',

5 list_of_gnames => NULL);

6 END;

7 /

PL/SQL procedure successfully completed.

SQL> GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;

Grant succeeded.

SQL> CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;

User created.

SQL> GRANT CREATE SESSION TO proxy_refresher;

Grant succeeded.

SQL> GRANT SELECT ANY TABLE TO proxy_refresher;

Grant succeeded.

STEP4: Setup Materialized view Group Configuration

Before that you can create materialized view in your master site.

SQL> SHOW PARAMETER DB_NAME

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name string MV01

SQL>

SQL>

SQL> ED

Wrote file afiedt.buf

1 BEGIN

2 DBMS_REPCAT.CREATE_MVIEW_REPGROUP (

3 gname => 'US_REP',

4 master => 'US.EDKAL',

5 propagation_mode => 'ASYNCHRONOUS');

6* END;

SQL> /

PL/SQL procedure successfully completed.

SQL> ED

Wrote file afiedt.buf

1 BEGIN

2 DBMS_REFRESH.MAKE (

3 name => 'mviewadmin.us_rep',

4 list => '',

5 next_date => SYSDATE,

6 interval => 'SYSDATE + 1/24',

7 implicit_destroy => FALSE,

8 rollback_seg => '',

9 push_deferred_rpc => TRUE,

10 refresh_after_errors => FALSE);

11* END;

SQL> /

PL/SQL procedure successfully completed.

SQL> CREATE MATERIALIZED VIEW BABU.EMP REFRESH FAST WITH PRIMARY KEY AS SELECT * FROM SCOTT.EMP@US.EDKAL;

Materialized view created.

SQL> CREATE MATERIALIZED VIEW BABU.DEPT REFRESH FAST WITH PRIMARY KEY AS SELECT * FROM SCOTT.DEPT@US.EDKAL;

Materialized view created.

SQL> CREATE MATERIALIZED VIEW BABU.SALGRADE REFRESH FAST WITH PRIMARY KEY AS SELECT * FROM SCOTT.SALGRADE@US.EDKAL;

Materialized view created.

SQL> SHOW USER

USER is "MVIEWADMIN"

SQL> BEGIN

2 DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (

3 gname => 'us_rep',

4 sname => 'SCOTT',

5 oname => 'EMP',

6 type => 'SNAPSHOT',

7 min_communication => TRUE);

8 END;

9 /

PL/SQL procedure successfully completed.

SQL> ED

Wrote file afiedt.buf

1 BEGIN

2 DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (

3 gname => 'us_rep',

4 sname => 'SCOTT',

5 oname => 'DEPT',

6 type => 'SNAPSHOT',

7 min_communication => TRUE);

8* END;

SQL> /

PL/SQL procedure successfully completed.

Monitoring Replication:

DBA_REPQUESTS – It’s administration purpose, you can able to find out administration error, no of deferred transaction etc.

DBA_REPCATLOG – Contains the interim status of any asynchronous administrative requests and any error messages generated at each master site

DBA_REPGROUP - Find out master group in your master site.

DBA_REPOBJECT – Find out objects in mater group

DEFERROR - It’s transaction error msg.

DEFTRANDEST - Find out total number transaction.