|
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:
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
(Mysore).
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@mysore
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@MYSORE
Connected.
SQL> CREATE
PUBLIC DATABASE LINK US.EDKAL USING 'US.EDKAL';
Database
link created.
SQL> Connect
mviewadmin/mviewadmin@mysore
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@mysore
Connected.
SQL> CREATE
DATABASE LINK US.EDKAL CONNECT TO repadmin IDENTIFIED BY repadmin USING 'US.EKDAL';
Database
link created.
SQL> connect
mviewadmin/mviewadmin@mysore
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@MYSORE
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.
| 
