SELECT n.MSISDN,
(SELECT ptc2.PRIMARY_ACCOUNT_NUMBER
FROM p_topup_cards ptc2
WHERE ptc2.NUMR_MSISDN = n.MSISDN
--AND ptc2.CARD_TYPE = 1
AND ptc2.PRIMARY_ACCOUNT_NUMBER LIKE '894428%'
AND ROWNUM < 2) pan
FROM numbers n  ,p_number_history pnh
WHERE n.MSISDN = pnh.NUMR_MSISDN
AND n.STATUS = 'A'
AND n.Barred = 'N'
AND n.spid_spid = '416'
--AND n.first_transaction IS NOT NULL
--AND pnh.END_TIMESTAMP IS NULL
AND pnh.PLFM_PLTP_CODE = 'IN'
AND ROWNUM <= 2000

Plan

SELECT STATEMENT  FIRST_ROWS Cost: 758,319 Bytes: 72,000  Cardinality: 2,000

3 COUNT STOPKEY

2 TABLE ACCESS BY INDEX ROWID LIVEUSER.PREPAY_TOPUP_CARDS Cost: 6  Bytes: 32  Cardinality: 1

1 INDEX RANGE SCAN UNIQUE LIVEUSER.PPTC_PK Cost: 4  Cardinality: 1

8 COUNT STOPKEY

7 NESTED LOOPS  Cost: 758,319 Bytes: 8,591,616  Cardinality: 238,656

5 TABLE ACCESS BY INDEX ROWID LIVEUSER.NUMBERS Cost: 46,110  Bytes: 4,748,060  Cardinality: 237,403

4 INDEX RANGE SCAN NON-UNIQUE LIVEUSER.NUMR_SPID_FK_I Cost: 3,682  Cardinality: 949,610

6 INDEX RANGE SCAN UNIQUE LIVEUSER.PFM_NUM_HS_PK Cost: 3  Bytes: 16  Cardinality: 1

Initial Analysis
This SQL needs to be re-written to avoid any join and mainly try and avoid statement “n.spid_spid = ‘416’”

Why?
This simple stmt “select * from numbers where spid_spid = ‘416’;” and it shows the cost of 46K+

Plan

SELECT STATEMENT FIRST_ROWS Cost: 46,110 Bytes: 40,833,230 Cardinality: 949,610

2 TABLE ACCESS BY INDEX ROWID CPI_SYSTEM.NUMBERS Cost: 46,110 Bytes: 40,833,230 Cardinality: 949,610

1 INDEX RANGE SCAN NON-UNIQUE CPI_SYSTEM.NUMR_SPID_FK_I Cost: 3,682 Cardinality: 949,610

SQL> desc v$mystat
Name Null? Type
—————————————– ——– —————————-
SID NUMBER
STATISTIC# NUMBER
VALUE NUMBER

SQL> select distinct sid from v$mystat;

SID
———-
139

SQL> select sid, serial# from v$session
2 where audsid=SYS_CONTEXT(‘USERENV’,’SESSIONID’);

SID SERIAL#
———- ———-
139 6

SQL> select dbms_session.unique_session_id from dual;

UNIQUE_SESSION_ID
——————————————————————————–
008B00060001

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

Execute DBMS_REPUTIL.replication_OFF;

You need to execute this API at each site. This will disable all triggers and you can manually load data at respective sites.

Once the job is done enable Triggers using

Execute DBMS_REPUTIL.replication_On;

Now all further changes would be replicated.

What is a difference between QUISCED Group and Replication off?
A quisced group puts all sites under read only status while above API could be used on individual replication master sites.

If you want to sync Table Data at Multimaster Replicated Sites please see  this post

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/

Everything was going OK but then noticed some tables are returning errors like

ORA-04067: not executed, stored procedure “SYSTEM.VOUCHER_NO$RP” does not exist
ORA-01085: preceding errors in deferred rpc to “SYSTEM.VOUCHER_NO$RP.REP_INSERT”
ORA-02063: preceding 2 lines from REPAA

Solution : Run following SQL to locate which tables are missing the much needed replication packages which should be generated as a part of GENERATE_REPLICATION_SUPPORT API. A replication support was pushed by Master Defn site when we add a new master/new objects into existing replication group.

— List if replication packages are available at Master as well as remote node
COLUMN group HEADING ‘Replication_Group’ FORMAT A20;
COLUMN count(*) HEADING ‘NUMBER_OF_TABLES’ FORMAT 9999999;
select gname Replication_Group,count(*) NUMBER_OF_TABLES
from REPCAT$_REPOBJECT where type= 2 group by gname order by 1;

REPLICATION_GROUP NUMBER_OF_TABLES
—————————— —————-
O1_A-B_ALL 14< BR> O1_ERROR_A-B 7

Above SQL will return how many objects have been replicated. Let’s make sure this is consistent with the remote site.

repA> select object_name from dba_objects where object_name like ‘%$R%’
minus
select object_name from dba_objects@repAA where object_name like ‘%$R%’;

OBJECT_NAME
——————————————————————————–
ACCOUNT$RP
OFFER$RP
OFFER$RP
OFFER$RP
SERVICE$RP
SUBSCRIBE$RP
SUBSCRIBE$RP
TRANSACTION$RP

8 rows selected, so 8 objects are missing replication support.

I can also use “select sname,oname,type,STATUS from DBA_REPOBJECT minus
select sname,oname,type,STATUS from DBA_REPOBJECT<at>remote_db;”

What does $RP package contain?
This package have following procedures
– rep_delete
– rep_insert
– rep_update

Let’s generate the packages missing at remote repAA site

At Master Defn Site > BEGIN
Master> DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (gname => ‘O1_BROKER_A-B’);
END;

Master> BEGIN
DBMS_REPCAT.generate_replication_package (sname => ‘BROKER_SYSTEM’,
oname =>’&1′);
END;

This went fine and did generate required packages. Above SQL returned no differences this time, still replication status was in “ERROR” at EM console

Next I tried generating a complete replication support as below.

Master/Remote DB> analyze table broker_system.account_status validate structure;

Table analyzed.
Master> BEGIN
dbms_repcat.generate_replication_support (
sname => ‘BROKER_SYSTEM’,
oname => ‘ACCOUNT_STATUS’,
type => ‘TABLE’,
distributed => TRUE);
END;
/
BEGIN
*
ERROR at line 1:
ORA-23308: object BROKER_SYSTEM.ACCOUNT_STATUS does not exist or is invalid
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.DBMS_REPCAT_UTL”, line 2874

I also followed oracle Note:176913.1 and compared every stats on these tables. These databases were copied as offline backup and had exact mirror image of each other. I am using 3 sites A,AA,AAA and above error was reported from remote system AAA. A and AA were running OK.

At last I used following procedure to get them VALID

Master repA> BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (gname => ‘O1_BROKER_A-B’);
END;

Master repA> EXECUTE Dbms_Repcat.Drop_Master_Repobject(‘BROKER_SYSTEM’, ‘OFFER_CATEGORY’,’TABLE’);
Master repA> EXECUTE Dbms_Repcat.Drop_Master_Repobject(‘BROKER_SYSTEM’, ‘OFFER_STATUS’, ‘TABLE’);
Master repA> EXECUTE Dbms_Repcat.Drop_Master_Repobject(‘BROKER_SYSTEM’, ‘OFFER_SUB_CATEGORY’, ‘TABLE’);
Master repA> EXECUTE Dbms_Repcat.Drop_Master_Repobject(‘BROKER_SYSTEM’, ‘SERVICE_PROVIDER’, ‘TABLE’);
Master repA> EXECUTE Dbms_Repcat.Drop_Master_Repobject(‘BROKER_SYSTEM’, ‘SUBSCRIBER_OFFER’, ‘TABLE’);
Master repA> EXECUTE Dbms_Repcat.Drop_Master_Repobject(‘BROKER_SYSTEM’, ‘SUBSCRIBER_PLAN’, ‘TABLE’);
Master repA> EXECUTE Dbms_Repcat.Drop_Master_Repobject(‘BROKER_SYSTEM’, ‘TRANSACTION_TYPE’, ‘TABLE’);

Disable all Foreign key constraints accessing these tables else truncate would fail with error “ORA-02266: unique/primary keys in table referenced by enabled foreign key”.

repAA/repAAA>truncate table OFFER_CATEGORY;
repAA/repAAA>truncate table OFFER_STATUS ;
repAA/repAAA>truncate table OFFER_SUB_CATEGORY ;
repAA/repAAA>truncate table SERVICE_PROVIDER ;
repAA/repAAA>truncate table SUBSCRIBER_OFFER ;
repAA/repAAA>truncate table SUBSCRIBER_PLAN ;
repAA/repAAA>truncate table TRANSACTION_TYPE ;

Master repA> BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ‘O1_BROKER_A-B’,
type => ‘TABLE’,
oname => ‘&1’,
sname => ‘BROKER_SYSTEM’,
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/
Enter value for 1: OFFER_CATEGORY
old 4: oname => ‘&1’,
new 4: oname => ‘OFFER_CATEGORY’,
Run above API for all tables

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘BROKER_SYSTEM’,
oname => ‘&1’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/
Enter value for 1: OFFER_CATEGORY
old 4: oname => ‘&1’,
new 4: oname => ‘OFFER_CATEGORY’,
Run above API for all tables

Run DBMS_JOB dbms_repcat.do_deferred_repcat_admin(‘”O1_BROKER_A-B”‘, FALSE); to propogate the ADMINISTRATIVE requests. I often found Oracle EM doesn’t work well so run this job manually at all sites.

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

Average latency is the average number of seconds between the first call of a transaction on the current site and the confirmation that the transaction was applied at the remote site. The

SQL> SELECT substr(DBLINK,1,25), AVG_LATENCY “Average Latency”
2 FROM DEFSCHEDULE
3 WHERE upper(DBLINK) like ‘&1’;
Enter value for 1: CPI%
old 3: WHERE upper(DBLINK) like ‘&1’
new 3: WHERE upper(DBLINK) like ‘CPI%’

SUBSTR(DBLINK,1,25) Average Latency
————————- —————
CPI_L1_A.O2
CPI_L1_B.O2 546244624
CPI_L2_A.O2 546860293
CPI_L2_B.O2 546860298

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

TO_CHAR(TRUNC(STAR   COUNT(*)
—————— ———-
15-DEC-08 20:32:00       1481
15-DEC-08 20:31:00       2510
15-DEC-08 20:30:00       2482
15-DEC-08 20:29:00       2543
15-DEC-08 20:28:00       2468
15-DEC-08 20:27:00       2458
15-DEC-08 20:26:00       2490
15-DEC-08 20:25:00       2505
15-DEC-08 20:24:00       2488
15-DEC-08 20:23:00       2443
15-DEC-08 20:22:00       2532
15-DEC-08 20:21:00       2555
15-DEC-08 20:20:00       2485
15-DEC-08 20:19:00        757

SQL> SELECT (R.TXNS_ENQUEUED / ((SYSDATE – I.STARTUP_TIME)*24*60*60)) “Average TPS”
2 FROM V$REPLQUEUE R, V$INSTANCE I;

Average TPS
———–
3.01341941

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

SQL> SELECT (R.TXNS_ENQUEUED / ((SYSDATE – I.STARTUP_TIME)*24*60*60)) “Average TPS”
FROM V$REPLQUEUE R, V$INSTANCE I;

Average TPS
———–
3.01351231

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.

set linesize 120;
COLUMN AV_BYTES HEADING ‘Average Bytes’ FORMAT 999999999
COLUMN AV_TRIPS HEADING ‘Average Round Trips’ FORMAT 9999999
SELECT
substr(DBLINK,1,30) Server_Name , DECODE(TOTAL_TXN_COUNT, 0, ‘No Transactions’,
round((TOTAL_BYTES_SENT + TOTAL_BYTES_RECEIVED) / TOTAL_TXN_COUNT)) AV_BYTES,
DECODE(TOTAL_TXN_COUNT, 0, ‘No Transactions’, (TOTAL_ROUND_TRIPS / TOTAL_TXN_COUNT)) AV_TRIPS
FROM DEFSCHEDULE WHERE DBLINK like ‘%&1%’;

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);

Locate Earliest date for the Error X’actions

SELECT   TO_CHAR (MAX (START_TIME), ‘DD-Mon-YYYY hh24:mi:ss’) TIME_OF_ERROR
FROM   DEFERROR;

Locate Latest date for the Error X’actions

SELECT   TO_CHAR (MIN (START_TIME), ‘DD-Mon-YYYY hh24:mi:ss’) TIME_OF_ERROR
FROM   DEFERROR;

Locate details of Error X’actions

COLUMN DBLINK HEADING ‘SERVER NAME’ FORMAT AAAAAA
COLUMN AVG_LATENCY  HEADING ‘Average Latency’ FORMAT 9999999
COLUMN DEFERRED_TRAN_ID HEADING ‘Deferred|Transaction|ID’ FORMAT A13
COLUMN ORIGIN_TRAN_DB HEADING ‘Origin|Database’ FORMAT A20
COLUMN DESTINATION HEADING ‘Destination|Database’ FORMAT A20
COLUMN TIME_OF_ERROR HEADING ‘Time of|Error’ FORMAT A12
COLUMN ERROR_NUMBER HEADING ‘Oracle|Error|Number’ FORMAT 999999
COLUMN ERROR_MSG HEADING ‘Oracle|Error|Message’ FORMAT A35
COLUMN RECEIVER HEADING ‘Receiver’ FORMAT A25

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
ORDER BY   START_TIME;

Deferred                                                              Oracle Oracle
Transaction   Origin               Destination          Time of        Error Error
ID            Database             Database             Error         Number Message
————- ——————– ——————– ———— ——- ———————————–
7.26.5257664  CPI_L1_B.O2          CPI_L1_A.O2          30-Jul-2008     1403 ORA-01403: no data found
04:39:37

4.9.5280783   CPI_L1_B.O2          CPI_L1_A.O2          30-Jul-2008     1403 ORA-01403: no data found
04:39:46

4.19.5280577  CPI_L1_B.O2          CPI_L1_A.O2          30-Jul-2008     1403 ORA-01403: no data found

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

Also look at this slightly more complicated example. This example will first try to re-apply the error before deleting it:

Here’s how to delete all errors having XYZ.COM as a destination:

Above API can only delete records. If you had built up a massive replication queue then Deftran, DefError, DefTranDest would grow to enormous size. Even after deleting all records a simple query “select count(*) from Deftrans, DefError£ can takes hours to return results. Under this case , please run following truncate to release space back to system.

truncate table SYSTEM.def$_aqerror

truncate table SYSTEM.def$_aqcall