Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    2

    Unanswered: Replication DEF$_AQCALL Issue

    I have configured Multi-Master Advanced Replication for 3 master sites (Using Oracle Enterprise Edition 9.2.0.1.0 on MS Windows2K). But I have observed a problem during this week that the size of SYSTEM tablespace has been growing rapidly. After further analysis it was observed that a major portion of the tablespace is covered by DEF$_AQCALL. It has nearly 1,763,626 rows on master definition site and increasing day by day while there are just less that 400 rows in participating other master sites. Due to this larger size when I try to observe the Error Queue using OEM, It takes more than 5 minutes to just show the Errors transactions list. Please have a look on the following script, I have run:-

    SQL> select table_name,blocks,empty_blocks,initial_Extent
    2 from user_tables where table_name='DEF$_AQCALL';

    TABLE_NAME BLOCKS EMPTY_BLOCKS INITIAL_EXTENT
    ------------- ------ ------------ --------------
    DEF$_AQCALL 60102 313 65536

    SQL> select segment_name,bytes,blocks from user_Segments
    2 where segment_name='DEF$_AQCALL';

    SEGMENT_NAME BYTES BLOCKS
    -------------- --------- -------
    DEF$_AQCALL 503316480 61440

    SQL> select count(*) from def$_aqcall;
    COUNT(*)
    ----------
    1763626


    SQL> select count(*) from deferror;
    COUNT(*)
    ----------
    3

    SQL> select count(*) from deftrandest;
    COUNT(*)
    ----------
    1466

    Transactions are being propagated normally. But If I have to see the Error Queue to check the error transactions for possible data conflicts, it takes a long time. I have moved this table to USERS tablespace and then move again to SYSTEM tablespace but alas its size doesn't vary. Can I truncate this table i.e. DEF$_AQCALL. As its size is going to be bigger and bigger due to increasing number of rows in it. what should I do ?

    Thanks,
    Faran

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    from http://metalink.oracle.com/metalink/..._id=610645.995

    I'm not sure what kind of setup you have, but if you have that many records in def$_aqcall, and the purge job has been running regularly without errors, there are three possibilities:

    1. The purge job doesn't have enough information to safely purge entries based on information available to it. The default purge job is a so-called 'lazy purge' that decides what can be safely purged based on the information available to it locally. Sometimes this is not enough, and a 'precise purge' must be used, where the purge actually goes and checks with other sites to see what can be purged or not. This involves running the DBMS_DEFER_SYS.PURGE procedure with the parameter purge_method => 2. You can tell if this is a problem or not by comparing the amount of rows in the deftran view and in the deftrandest view. Deftran contains all deferred transactions in the instance, whether they have been propagated or not or if they are in error or not, while deftrandest contains unpropagated transactions. If the count in deftrandest is low compared to deftran, you have a purge problem.

    2. One of your master sites has been unavailable for a while, and undelivered transactions are accumulating. In this scenario, deftran and deftrandest would have close to the same amount of rows.

    3. You have many unresolved error transactions. In this case, the count in the view 'deferror' would be very high.

    Since you have so many rows in def$_aqcall, you are correct in saying that it isn't feasible to resolve this by normal means. You need to determine and resolve what the problem is, then truncate the def$_aqcall table (and others) under guidance from your TAR, and then reinstantiate your environment.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Posts
    2

    Post Re: Replication DEF$_AQCALL Issue

    Excellant Anacedent. You have described very beautifully the 3 possible scenarios.
    * Yes, I have configured 4 multimaster sites in the begining. Then a server of fourth site was crashed. But as were in the process of winding up that site and we have had no more need of replica there. So I have just removed the push jobs related to that site from other 3 sites including the master definition site. But now there are normal number of rows in def$_aqcall at other two sites but the number of rows at def$_aqcall there is a high number of rows.

    SQL> select count(*) from system.def$_aqcall;

    COUNT(*)
    ----------
    2573139

    * Yes, I have quite less number of rows in deftrandest as compared to deftran.

    SQL> SELECT COUNT(*)
    2 FROM DEFTRAN;

    COUNT(*)
    ----------
    917857

    SQL> SELECT COUNT(*)
    2 FROM DEFTRANDEST;

    COUNT(*)
    ----------
    1997

    I have monitored from the OEM that the purged job is running periodically after each hour. So I have run the following procedure as per your instructions.

    SQL> BEGIN
    2 DBMS_DEFER_SYS.PURGE(purge_method => 2);
    3 END;
    4 /
    DBMS_DEFER_SYS.PURGE(purge_method => 2);
    *
    ERROR at line 2:
    ORA-06550: line 2, column 1:
    PLS-00221: 'PURGE' is not a procedure or is undefined
    ORA-06550: line 2, column 1:
    PL/SQL: Statement ignored

    SQL> show user
    USER is "REPADMIN"

    SQL> BEGIN
    2 DBMS_DEFER_SYS.SCHEDULE_PURGE (
    3 next_date => SYSDATE,
    4 interval => 'SYSDATE + (1/144)',
    5 purge_method => 2,
    6 delay_seconds => 500);
    7 END;
    8 /

    PL/SQL procedure successfully completed.
    ------------------------------------------
    ------------ After 5 minutes --------------
    ------------------------------------------
    SQL> SELECT COUNT(*)
    2 FROM DEFTRAN;

    COUNT(*)
    ----------
    923898

    Still a greater number of rows in deftran So comrade! what would you like to suggest me at this point.

    A bundle of thanks,
    Faran

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >So comrade! what would you like to suggest me at this point.
    You should open an iTAR and get Oracle's Tech.Support to assist.
    Or take good backups & start making changes;
    like TRUNCATE TABLE system.def$_aqcall;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2007
    Posts
    1

    Hi

    I having the same problem too. The replication job working fine but It's taking long time to complete. It's the same as to open the error page. Anyone please help or advice? The jobs run every 10 min and its many records in the DEF$_AQCALL table and eat up tablespace ..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •