Page 1 of 212

Multimaster Replication :How to resolve Replication Errors

Posted by Sagar Patil

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…

How to start database and Apps

Posted by Sagar Patil

(Release 12)

Login as database user
cd $ORACLE_HOME/appsutil/scripts/<SID_hostname>

Read more…

Replication views taking longer to return results/ Truncate views

Posted by Sagar Patil

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…

API to Retrieve runtime Replication Parameters

Posted by Sagar Patil

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…

Rectify Differences Between Replicated Tables

Posted by Sagar Patil

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…

Loading or Re-Synchronising Data in a Replication Cluster

Posted by Sagar Patil

To strop replication and not to place a system into read only status please execute following steps.

Execute DBMS_REPUTIL.replication_OFF;

Read more…

Replication Packages/Triggers Missing at new site : ORA-04067: stored procedure OBJECT_NAME$RP does not exist

Posted by Sagar Patil

I built a 6 node cluster using notes at http://www.oracledbasupport.co.uk/adding-a-new-master-node-in-an-existing-multi-master-cluster/

Read more…

Average Amount of Time to Apply Transactions at Remote Sites

Posted by Sagar Patil

SELECT substr(DBLINK,1,25), AVG_LATENCY “Average Latency”
FROM DEFSCHEDULE
WHERE upper(DBLINK) like ‘&1′;

Read more…

Tracking the Rate of Transactions Entering the Deferred Transactions Queue

Posted by Sagar Patil

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…

Tracking the Average Number of Row Changes in a Replication Transaction

Posted by Sagar Patil

SELECT DECODE(TXNS_ENQUEUED, 0, ‘No Transactions Enqueued’,
         (CALLS_ENQUEUED / TXNS_ENQUEUED)) “Average Number of Row Changes”
                  FROM V$REPLQUEUE; Read more…

Determining the Average Network Traffic Created to Propagate a Transaction

Posted by Sagar Patil

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…

Listing General Information About the Error Transactions at a Replication Site

Posted by Sagar Patil

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…

Delete Errors (Transaction Queue, Errors)

Posted by Sagar Patil

This call will delete all Errors from replication Queue i.e deferror & deftran

Delete MASTER Node: Rollback ADD_NEW_MASTERS Procedure

Posted by Sagar Patil

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…

Replication SQL Scripts

Posted by Sagar Patil

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…

Push Replication Group to NORMAL Status in an Exceptional Case

Posted by Sagar Patil

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…

Drop/Delete Multi-Master Replication

Posted by Sagar Patil

Dropping Multi-master Replication

Execute following SQL at Master Defn Site (here repa)
$ sqlplus repadmin/repadmin@repa

Read more…

Add a new node using ADD_MASTER_DATABASE : Group Quiescing Method

Posted by Sagar Patil

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…

Monitoring Performance in a Replication Environment

Posted by Sagar Patil

1. Tracking the Average Number of Row Changes in a Replication Transaction

Read more…

Monitoring Purges of Successfully Propagated Transactions

Posted by Sagar Patil
  • Listing General Information About the Purge Job
  • Checking the Definition of the Purge Job
  • Determining the Amount of Time Since the Last Purge
  • Determining the Total Number of Purged Transactions

Read more…

Monitoring the Deferred Transactions Queue

Posted by Sagar Patil
  • Listing the Number of Deferred Transactions for Each Destination Master Site

    Read more…

Oracle Advanced Replication :Monitoring Administrative Requests

Posted by Sagar Patil

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…

List General Information About Master Groups & replication activity

Posted by Sagar Patil

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…

Oracle Advanced Replication | Listing the Master Sites Participating in a Master Group

Posted by Sagar Patil

COLUMN GNAME HEADING ‘Master Group’ FORMAT A20
COLUMN DBLINK HEADING ‘Sites’ FORMAT A25
COLUMN MASTERDEF HEADING ‘Master|Definition|Site?’ FORMAT A10

Read more…

An example of Conflict Resolution Method

Posted by Sagar Patil

Here I am going to configure Oracle to use latest timestamp method to settle conflict ON SCOTT.EMP table’s EMPNO column.

Read more…

Top of Page

Top menu