While using multimaster replication often you would be bombarded with errors like “No data found”, “Unique Key Violated” etc .
These are purely data mismatch errors and a user intervention needed to resolve them.
I often come across these errors when we run applications at multiple sites which are part of same replication group/tables when they are designed to run at one replication site at any given time.
You can get list of these errors at DEFERROER view with transaction ids but there is no easy way to locate the parameters,columns and tables on which these errors occurred.
Thankfully there is a solution if you have EM installed.
Login as database user
We had to relocate one of the replication node to another location. During move, other master nodes had a huge queue build up i.e more than 1.2 million waiting transactions recorded at deftran on 5 other nodes. When the server was finally back, transaction queues were replicated to original server but we hit performance problem.
We often get thousands of replication conflits/errors at current system. A resolution is generally looking at EM console and resolving each error manually. Please see examples below.
Using the DIFFERENCES Procedure
The DIFFERENCES procedure compares two replicas of a table, and determines all rows in the first replica that are not in the second and all rows in the second that are not in the first. The output of this procedure is stored in two user-created tables. The first table stores the values of the missing rows, and the second table is used to indicate which site contains each row.
To strop replication and not to place a system into read only status please execute following steps.
SELECT substr(DBLINK,1,25), AVG_LATENCY “Average Latency”
WHERE upper(DBLINK) like ‘&1′;
SQL will dispaly rate of x’actions entering at DEFTRAN Queue for last Hour
SQL> select to_char(trunc(start_time,’MI’),’DD-MON-YY HH24:MI:SS’),count(*) from deftran where start_time > sysdate -(1/24)
group by to_char(trunc(start_time,’MI’),’DD-MON-YY HH24:MI:SS’)
order by 1 desc
SELECT DECODE(TXNS_ENQUEUED, 0, ‘No Transactions Enqueued’,
(CALLS_ENQUEUED / TXNS_ENQUEUED)) “Average Number of Row Changes”
FROM V$REPLQUEUE; Read more…
If this query returns ‘No transactions’ in both columns, then no transactions have been propagated to the specified remote site since the statistics were last cleared. Read more…
Find out total number of error X’actions
SELECT COUNT ( * )
FROM (SELECT DEFERRED_TRAN_ID,
TO_CHAR (START_TIME, ‘DD-Mon-YYYY hh24:mi:ss’)
This call will delete all Errors from replication Queue i.e deferror & deftran
I am trying to add a new master database into existing replication cluster using API DBMS_REPCAT.ADD_MASTER_DATABASE. The system returned error : ORA-23493: “REPC.US.ORACLE.COM” is not a new site for extension request “57C8B3C5C100528AE0440060B0C193C6″ Read more…
Count of Objects replicated in Database
SELECT base_sname “OWNER”, COUNT (UNIQUE base_oname) NUMBER_OF_OBJECTS
GROUP BY base_sname;
The dbms_repcat.resume_master_activity() package has an override parameter that defaults to FALSE. This parameter can be set to TRUE to allow the
group to become normal even if there are pending admin requests in DBA_REPCATLOG for this group.
Dropping Multi-master Replication
Execute following SQL at Master Defn Site (here repa)
$ sqlplus repadmin/repadmin@repa
I have 2 databases (repA and repB) which are part of multi master replication cluster server A and Server B. I now need to add another Server C with database name of repC. Consider this as a Live system and require a very minimum downtime.
1. Tracking the Average Number of Row Changes in a Replication Transaction
1. Listing General Information About Administrative Requests at the Master
2. Determining the Cause of Administrative Request Errors at the Master
3. Listing Information About the Job that Executes Administrative Requests at the Master
You can retrieve following details :
- The number of unpropagated deferred transaction-destination pairs. Each deferred transaction can have multiple destinations to which it will be propagated, and each destination is a single deferred transaction-destination pair.
- The number of deferred transaction errors (error transactions) for each master group
- The number of administrative requests for each master group
- The number of administrative request errors for each master group
Here I am going to configure Oracle to use latest timestamp method to settle conflict ON SCOTT.EMP table’s EMPNO column.