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.
Read more…
(Release 12)
Login as database user
cd $ORACLE_HOME/appsutil/scripts/<SID_hostname>
Read more…
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.
Read more…
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.
Read more…
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.
Read more…
To strop replication and not to place a system into read only status please execute following steps.
Execute DBMS_REPUTIL.replication_OFF;
Read more…
SELECT substr(DBLINK,1,25), AVG_LATENCY “Average Latency”
FROM DEFSCHEDULE
WHERE upper(DBLINK) like ‘&1′;
Read more…
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
Read more…
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,
ORIGIN_TRAN_DB,
DESTINATION,
TO_CHAR (START_TIME, ‘DD-Mon-YYYY hh24:mi:ss’)
TIME_OF_ERROR,
ERROR_NUMBER,
ERROR_MSG
FROM DEFERROR);
Read more…
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
FROM REPCAT$_GENERATED
GROUP BY base_sname;
Read more…
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.
Read more…
Dropping Multi-master Replication
Execute following SQL at Master Defn Site (here repa)
$ sqlplus repadmin/repadmin@repa
Read more…
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.
Read more…
1. Tracking the Average Number of Row Changes in a Replication Transaction
Read more…
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
Read more…
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
Read more…
Here I am going to configure Oracle to use latest timestamp method to settle conflict ON SCOTT.EMP table’s EMPNO column.
Read more…