Hello,
Today I got below error message in my replication env.
ORA-00001: unique constraint
As per my investication
1/ There is NO duplicate records in my base table & materialized view.
In my master & materialized view site having some database trigger to generate auto-increment values using trigger.
As per my trigger from mview site some values generated; the same values generated from master site.
That is the reason implemented DBMS_MVIEW.I_AM_A_REFRESH package from mview site.
For Example:
Master Site
SQL> create table tst_tri (a int);
Table created.
SQL> create or replace trigger tst_tri_after after insert on tst_tri
2 begin
3 delete from tst_tri;
4 end ;
5 /
Trigger created.
SQL> insert into tst_tri values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tst_tri;
no rows selected
From Materialized View site:
SQL> create or replace trigger tst_tri_after after insert on babu.tst_tri
2 begin
3 if dbms_mview.i_am_a_refresh then
4 delete from babu.tst_tri;
5 end if;
6 end;
7 /
Trigger created.
SQL> select * from babu.tst_tri;
no rows selected
SQL> insert into babu.tst_tri values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from babu.tst_tri;
A
----------
1
SQL> drop trigger sys.tst_tri_after;
Trigger dropped.
Showing posts with label Replication. Show all posts
Showing posts with label Replication. Show all posts
Monday, December 1, 2008
Monday, February 11, 2008
Replication + Logical
Replication –Logical things
I was facing some new thing in replication. I want to share my experience to you.
Actually my master and materialized view site its different oracle version. Master size 10.2.0.3 and materialized view site one is 10.2.0.3 and another one is 9.2.0.8.
One day I dropped one materialized view and again re-created materialized view because I add some column in base table in mater site. But dropped materialized view objects name I’m not removed in replication group. So, no need to re-add materialized view name in replication group.
Once completed everything I try to transfer data. I’m getting the below error message.
Error 2091 trapped in 2PC on transaction 8.10.95082. Cleaning up.
Error stack returned to user:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SAMIINVENTORY.FK_GRNMST_SUP) violated - parent key not found
Error 3113 trapped in 2PC on transaction 6.44.93771. Cleaning up.
Tue Jan 01
DISTRIB TRAN KNGDB.SAMIINDIA.6db37e82.6.44.93771
is local tran 6.44.93771 (hex=06.2c.16e4b)
insert pending collecting tran, scn=379132697 (hex=0.16991b19)
Tue Jan 01
Error stack returned to user:
ORA-02050: transaction 6.44.93771 rolled back, some remote DBs may be in-doubt
ORA-03113: end-of-file on communication channel
ORA-23324: error ORA-00604: error occurred at recursive SQL level
ORA-02063: preceding line from SAMIDB, while creating deferror entry at "SAMIDB.SAMIINDIA" with error -604
Tue Jan 01
Actually problem is while transferring data, some transaction roll backing its called 2PC on transaction error. But you can able to check what are pending transaction using DBA_2PC_PENDING and DBA_2PC_NEIGHBORS. But in my case NO values return (I know it’s happens). We found two solutions.
- Deferred Constraints
In master site we have lot referable tables (parent and child table). Here no need to set deferrable constraints but the same we are replicated in materialized view site. Here we should be use deferrable constraints in parent and child tables.
Because replication is going on for a refresh group; we have no control over the order in which the tables inside the refresh group gets refreshed. Sometimes, child tables may get refreshed first and then parent tables. So in such cases we need to defer the enforcement of the constraint till commit time. Hence we need to use Deferrable constraints.
- DBMS_MVIEW.I_AM_A_REFRESH
Application related reasons, we don’t want the trigger (especially those which update new table (this table it’s not replicated)) to execute while data comes from other materialized view site. So to see whether the replication operation is going on or not, we are using the above mentioned function. Do note that our triggers are NOT replicated triggers, we are creating triggers locally. Also our stock table is a local table, NOT a replicated table.
Configuring Conflict Resolution
Conflict Resolution in Advanced Replication:
In replication, the most common data conflict occurs when the same row at two or more different sites are updated at nearly the same time, or before the deferred transaction from one site was successfully propagated to the other sites.
One method to avoid update conflicts is to implement a synchronous replication environment, though this solution requires large network resource.
The other solution is to use the Oracle conflict resolution methods to deal with update conflicts that can occur when the same row receives two or more updates.
Now we need to avoid this error in replication. Next question is; How to avoid.
There are several methods to avoid this issue in replication but before we need to know how to replication data transfer working? If you understand this concept you never face any error in replication.
How working data transfer between two or more site:
Once you stat data transfer using dbms_refresh.refresh. First of all checking old values in my object.
For example:
I have one table like “EMP” it’s replicated (updatable). Once start data transfer what’s the old values in EMP objects? Those values available in both site or not. If available means next that data checking current values I mean new values. Suppose both end old records not available sense you should be face “ORA-12034: materialized view log younger than last refresh”. If you face this error you need complete refresh other than no never solve this issue.
Some time in both site updating same row. This time you should be face “ORA-23402: refresh was aborted because of conflicts caused by deferred txns” If you face this error you need to implement conflict resolution.
For example:
SQL> show user;
USER is "BABU@WASIM" – Master Site
SQL> select * from master;
FIRSTNAME LASTNAME ID
-------------------- -------------------- ----------
BABU SRIKANT 101
WASIM SRI 102
SQL> update master set firstname='WASIM LATIF' where FIRSTNAME='WASIM';
1 row updated.
SQL> commit;
Commit complete.
SQL> CONNECT BABU@MV - Materialized view site.
Enter password:
Connected.
SQL> SELECT * FROM MASTER;
FIRSTNAME LASTNAME ID
-------------------- -------------------- ----------
BABU SRIKANT 101
WASIM SRI 102
SQL> update master set firstname='WASIM LATIF' where FIRSTNAME='WASIM';
1 row updated.
SQL> commit;
Data Transfer between master and materialized view database:
MviewAdmin@mv.
SQL> exec dbms_refresh.refresh('mviewadmin.tst_rep');
BEGIN dbms_refresh.refresh('mviewadmin.tst_rep'); END;
*
ERROR at line 1:
ORA-23402: refresh was aborted because of conflicts caused by deferred txns
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
Configure conflict resolution:
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => 'BABU');
END;
/
/*Create Column group for target table*/
BEGIN
DBMS_REPCAT.MAKE_COLUMN_GROUP (
sname => 'BABU',
oname => 'MASTER',
column_group => 'BABU',
list_of_column_names => 'firstname,lastname'); //this is the column available
for both end
END;
/
/*Define conflict resolution method*/
BEGIN
DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
sname => 'BABU',
oname => 'MASTER',
column_group => 'BABU',
sequence_no => 1,
method => 'DISCARD',
parameter_column_name => 'firstname,lastname');
END;
/
/*regenerate replication support for conflict resolution method*/
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'BABU',
oname => 'MASTER',
type => 'TABLE',
min_communication => TRUE);
END;
/
/*start replication activity*/
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => 'BABU');
END;
/
Commit;
Conflict Resolution Methods:
There are several method to configure conflict resolutions. The above example I explained “DISCARD” methods.
- Discard – Already explained.
- Minimum and Maximum :
When Advanced Replication detects a conflict with a column group and calls either the minimum or maximum value conflict resolution methods, it compares the new value from the originating site with the current value from the destination site for a designated column in the column group. You must designate this column when you define your conflict resolution method.
If the new value of the designated column is less than or greater than (depending on the method used) the current value, then the column group values from the originating site are applied at the destination site, assuming that all other errors were successfully resolved for the row. Otherwise the rows remain unchanged.
- Timestamp Conflict resolution method.
The earliest timestamp and latest timestamp methods are variations on the minimum and maximum value methods. To use the timestamp method, you must designate a column in the replicated table of type DATE. When an application updates any column in a column group, the application must also update the value of the designated timestamp column with the local SYSDATE. For a change applied from another site, the timestamp value should be set to the timestamp value from the originating site.
Two elements are needed to make timestamp conflict resolution work well:
· Synchronized time settings between computers
· Timestamp field and trigger to automatically record timestamp
2 DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY
3 (
4 GNAME => 'BABU'
5 );
6 END;
7 /
PL/SQL procedure successfully completed.
REPADMIN@wasim.samiindia> COMMIT;
/* Altering my based using alter_master_repobject procedure */
REPADMIN@wasim.samiindia> BEGIN
2 DBMS_REPCAT.ALTER_MASTER_REPOBJECT
3 (
4 SNAME => 'BABU',
5 ONAME => 'MENUMASTER',
6 TYPE => 'TABLE',
7 DDL_TEXT => 'ALTER TABLE BABU.MENUMASTER ADD (MODIFYED_ON DATE )'
8 );
9* END;
REPADMIN@wasim.samiindia> /
PL/SQL procedure successfully completed.
REPADMIN@wasim.samiindia> COMMIT;
Commit complete.
REPADMIN@wasim.samiindia> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT
3 (
4 SNAME => 'BABU',
5 ONAME => 'MENUMASTER',
6 TYPE => 'TABLE',
7 MIN_COMMUNICATION => TRUE,
8 generate_80_compatible => FALSE
9 );
10* END;
REPADMIN@wasim.samiindia> /
PL/SQL procedure successfully completed.
REPADMIN@wasim.samiindia> COMMIT;
Commit complete.
/* This is called creating new column group */
REPADMIN@wasim.samiindia> BEGIN
2 DBMS_REPCAT.MAKE_COLUMN_GROUP
3 (
4 SNAME => 'BABU',
5 ONAME => 'MENUMASTER',
6 COLUMN_GROUP => 'QC',
7 LIST_OF_COLUMN_NAMES => '*'
8 );
9 END;
10 /
PL/SQL procedure successfully completed.
REPADMIN@wasim.samiindia> COMMIT;
Commit complete.
/* This is latest timestamp method */
REPADMIN@wasim.samiindia> BEGIN
2 DBMS_REPCAT.ADD_UPDATE_RESOLUTION
3 (
4 SNAME => 'BABU',
5 ONAME => 'MENUMASTER',
6 COLUMN_GROUP => 'QC',
7 SEQUENCE_NO => 1,
8 METHOD => 'LATEST TIMESTAMP',
9 PARAMETER_COLUMN_NAME => 'MODIFYED_ON'
10 );
11* END;
REPADMIN@wasim.samiindia> /
PL/SQL procedure successfully completed.
REPADMIN@wasim.samiindia> COMMIT;
Commit complete.
REPADMIN@wasim.samiindia> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT
3 (
4 SNAME => 'BABU',
5 ONAME => 'MENUMASTER',
6 TYPE => 'TABLE',
7 MIN_COMMUNICATION => TRUE,
8 generate_80_compatible => FALSE
9 );
10* END;
REPADMIN@wasim.samiindia> /
PL/SQL procedure successfully completed.
REPADMIN@wasim.samiindia> COMMIT;
Commit complete.
REPADMIN@wasim.samiindia> BEGIN
2 DBMS_REPCAT.RESUME_MASTER_ACTIVITY
3 (
4 GNAME => 'BABU'
5 );
6 END;
7 /
PL/SQL procedure successfully completed.
REPADMIN@wasim.samiindia> COMMIT;
- Addtive and Average conflict methods.
The additive and average methods work with column groups consisting of a single numeric column only. Instead of "accepting" one value over another, this conflict resolution method either adds the two compared values together or takes an average of the two compared values.
Complete the following steps to create an additive or average conflict resolution method. This example averages the commission percentage for an employee in the event of a conflict.
- Priority Groups Conflict Resolution Methods:
Priority groups allow you to assign a priority level to each possible value of a particular column. If Oracle detects a conflict, then Oracle updates the table whose "priority" column has a lower value using the data from the table with the higher priority value.
- Site Priority Conflict Resolution Methods
Site priority is a specialized form of a priority group. Therefore, many of the procedures associated with site priority behave similarly to the procedures associated with priority groups. Instead of resolving a conflict based on the priority of a field's value, the conflict is resolved based on the priority of the sites involved.
Monitoring Conflict Resolution:
· DBA_REPCONFLICT describes the conflict resolution method for all the tables in the database on which a conflict resolution method has been defined.
· USER_REPCONFLICT describes the conflict resolution method for all the tables owned by the current user on which a conflict resolution method has been defined.DBA_REPPARAMETER_COLUMN - Using this data dictionary we can able to find what are they column groups, which method using and what are they parameter colunm used those infomarion we can find using this data dictionary table.
DBA_REPRESOLUTION - Using this data dictionary we can find conflict resolution method type and table name
DBA_REPRESOLUTION_METHOD - Using this data dictionary we can find method name.
DBA_REPRESOLUTION_STATISTICS - View lists information about successfully resolved update, uniqueness, and delete conflicts for all replicated tables in the database.
DBA_REPSITES view lists the members of each replication group in the database.
DBA_REPCOLUMN - List of column name using group
DBA_REPCOLUMN_GROUP - List of group name using per column;
DBA_REPDDL -contains the DDL for each replication object in the database. Its columns are the same as those in ALL_REPDDL.
DBA_REPCONFLICT - view displays the name of each table in the database on which a conflict resolution method has been defined and the type of conflict that the method is used to resolve.
Thursday, November 29, 2007
Adding LOB Objects in Repliation
How to Add LOB Objects in Replication site.
If you want add lob objects in replication site. (You must be already done Advanced Replication “Click here “)
That objects must be contains primary key constraints.
SQL> DESC MASTER
Name Null? Type
----------------------------------------- -------- ----------------------------
COMPANY_CODE VARCHAR2(10)
COMPANY_NAME NOT NULL VARCHAR2(100)
COMPANYLOGO BLOB
SHORT_NAME NOT NULL VARCHAR2(10)
PRINT_NAME NOT NULL VARCHAR2(100)
Here you need temporary stop master site activity and add those objects in master site.
SQL> BEGIN
2 DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
3 gname => 'us_rep'
4 );
5 END;
6 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'us_rep',
4 sname => 'SCOTT',
5 oname => 'MASTER',
6 type => 'TABLE',
7 copy_rows => TRUE,
8 use_existing_object => TRUE );
9 END;
10 /
PL/SQL procedure successfully completed.
SQL> ED
Wrote file afiedt.buf
1 BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'us_rep',
4 sname => 'SCOTT',
5 oname => 'MASTER_PK',
6 type => 'INDEX',
7 copy_rows => TRUE,
8 use_existing_object => TRUE );
9* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> CREATE MATERIALIZED VIEW LOG ON SCOTT.MASTER;
Materialized view log created.
SQL> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'SCOTT',
4 oname => 'MASTER',
5 type => 'TABLE',
6 min_communication => true,
7 generate_80_compatible => FALSE);
8* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
3 gname => 'us_rep'
4 );
5 END;
6 /
PL/SQL procedure successfully completed.
Once complete start your master activity.
Create materialized view’s in your replication site.
SQL> CREATE MATERIALIZED VIEW MASTER REFRESH FAST WITH PRIMARY KEY UPDATABLE AS SELECT * FROM SCOTT.MASTER@US.EDKAL;
Materialized view created.
SQL> BEGIN
DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(
gname => '"US_REP"',
sname => '"SCOTT"',
oname => '"MASTER"',
type => 'SNAPSHOT',
min_communication => TRUE);
END;
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_REFRESH.ADD(
name => '"MVIEWADMIN"."US_REP"',
list => '"SCOTT"."MASTER"',
lax => TRUE);
END;
PL/SQL procedure successfully completed.
Friday, November 2, 2007
Adding Objects in Replication
Alter Replication:
If you want add objects in replication site. (You must be already done Advanced Replication “Click here “)
That objects must be contains primary key constraints.
SQL> CREATE TABLE U_MASTER
(UNO INT CONSTRAINT U_MASTER_PK PRIMARY KEY,
USERNAME VARCHAR2 (22),
U_PASSWORD VARCHAR2 (22));
Table created.
Here you need temporary stop master site activity and add those objects in master site.
SQL> BEGIN
2 DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
3 gname => 'us_rep'
4 );
5 END;
6 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'us_rep',
4 sname => 'SCOTT',
5 oname => 'U_MASTER',
6 type => 'TABLE',
7 copy_rows => TRUE,
8 use_existing_object => TRUE );
9 END;
10 /
PL/SQL procedure successfully completed.
SQL> ED
Wrote file afiedt.buf
1 BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'us_rep',
4 sname => 'SCOTT',
5 oname => 'U_MASTER_PK',
6 type => 'INDEX',
7 copy_rows => TRUE,
8 use_existing_object => TRUE );
9* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> CREATE MATERIALIZED VIEW LOG ON SCOTT.U_MASTER;
Materialized view log created.
SQL> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'SCOTT',
4 oname => 'U_MASTER',
5 type => 'TABLE',
6 min_communication => true,
7 generate_80_compatible => FALSE);
8* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
3 gname => 'us_rep'
4 );
5 END;
6 /
PL/SQL procedure successfully completed.
Once complete start your master activity.
Create materialized view’s in your replication site.
SQL> CREATE MATERIALIZED VIEW U_MASTER REFRESH FAST WITH PRIMARY KEY UPDATABLE AS SELECT * FROM SCOTT.U_MASTER@US.EDKAL;
Materialized view created.
SQL> BEGIN
DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(
gname => '"US_REP"',
sname => '"SCOTT"',
oname => '"U_MASTER"',
type => 'SNAPSHOT',
min_communication => TRUE);
END;
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_REFRESH.ADD(
name => '"MVIEWADMIN"."US_REP"',
list => '"SCOTT"."U_MASTER"',
lax => TRUE);
END;
PL/SQL procedure successfully completed.
Subscribe to:
Posts (Atom)
