Get the current Session ID
SQL> desc v$mystat
Name Null? Type
—————————————– ——– —————————-
SID NUMBER
STATISTIC# NUMBER
VALUE NUMBER
Loading or Re-Synchronising Data in a Replication Cluster
To strop replication and not to place a system into read only status please execute following steps.
Execute DBMS_REPUTIL.replication_OFF;
Replication Packages/Triggers Missing at new site : ORA-04067: stored procedure OBJECT_NAME$RP does not exist
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/
List Transactions Currently Being Propagated to a Remote Master
SELECT /*+ ORDERED */ P .XID "Tran Being Propagated", (MAX (C.CALLNO) + 1) "Number of Calls in Tran", (P.SEQUENCE / MAX (C.CALLNO) + 1) * 100 "% Processed Calls" FROM V$REPLPROP P, DEFCALL C WHERE P.NAME LIKE '%SLAVE%' AND P.DBLINK = '&1' AND C.DEFERRED_TRAN_ID = P.XID GROUP BY P.XID, P.SEQUENCE;
Average Amount of Time to Apply Transactions at Remote Sites
SELECT substr(DBLINK,1,25), AVG_LATENCY “Average Latency”
FROM DEFSCHEDULE
WHERE upper(DBLINK) like ‘&1′;
Tracking the Rate of Transactions Entering the Deferred Transactions Queue
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
Tracking the Average Number of Row Changes in a Replication Transaction
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
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
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);
Delete Errors (Transaction Queue, Errors)
This call will delete all Errors from replication Queue i.e deferror & deftran
Tweet