Replication problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Replication problem

  1. #1
    Join Date
    Mar 2002
    Posts
    48

    Exclamation

    We are using asynch propogation for one of the master group and using queue table def$_aqcall in system tablespace.
    As there is more I/O generated on system tablespace, we want to move que table to other tablespace.
    If we stop replication ,create another table in other table space unser same schema with same table defination ,drop original and rename the new table to def_aqcall, Will it effect any thing ?
    This is very critical so please help.


  2. #2
    Join Date
    Mar 2002
    Posts
    2
    This is a common problem. Moving def$_aqcall table to another tablespace should be fine.

    You may want to take a look at the following steps:

    Moving the SYSTEM catalog tables in Oracle8:
    ============================================

    1. Take a backup of the site where the SYSTEM catalog tables are
    going to be moved.

    2. Make sure deftran, defcall, deferror and dba_repcatlog on the
    site where the tables are going to be moved to are empty.

    3. Connect to Sql*Plus as REPADMIN and issue for EACH repgroup:

    SQL> EXECUTE DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY('GNAME');

    4. Export user SYSTEM.

    $ exp system/manager file=move.dmp

    5. Connect to Sql*Plus as SYSTEM. Drop the following tables in
    the order specified below:

    drop table SYSTEM.DEF$_CALLDEST;
    drop table SYSTEM.DEF$_DEFAULTDEST;
    drop table SYSTEM.DEF$_ERROR;
    drop table SYSTEM.DEF$_ORIGIN;
    drop table SYSTEM.REPCAT$_REPSCHEMA;
    drop table SYSTEM.DEF$_DESTINATION ;

    6. Connect to Sql*Plus as REPADMIN and issue:

    SQL> EXECUTE DBMS_AQADM.DROP_QUEUE_TABLE('SYSTEM.DEF$_AQCALL',TRUE);

    SQL> EXECUTE DBMS_AQADM.DROP_QUEUE_TABLE('SYSTEM.DEF$_AQERROR',TRUE);

    7. Connect to Sql*Plus as SYS and issue:

    SQL> ALTER USER SYSTEM DEFAULT TABLESPACE
    QUOTA UNLIMITED ON ;

    SQL> ALTER USER SYSTEM QUOTA 0 ON SYSTEM;

    SQL> REVOKE UNLIMITED TABLESPACE FROM SYSTEM;

    8. Connect to Sql*Plus as SYSTEM and run the script "catdefrt.sql"
    located in the "$ORACLE_HOME/rdbms/admin" directory.

    Note: The errors on some CREATE TABLE statements and CREATE
    INDEXES of mature tables or indexes indicating they
    already exist can be safely ignored.

    9. Import the tables in the order listed from your export file
    from step 4 above.

    SYSTEM.DEF$_DESTINATION
    SYSTEM.DEF$_CALLDEST
    SYSTEM.DEF$_DEFAULTDEST
    SYSTEM.DEF$_ERROR
    SYSTEM.DEF$_ORIGIN
    SYSTEM.REPCAT$_REPSCHEMA

    Example:

    $ imp
    Username: system
    Password:

    Connected to: Oracle8 Enterprise Edition Release 8.0.3.0.0
    - Production
    With the Partitioning and Objects options
    PL/SQL Release 8.0.3.0.0 - Production

    Import file: expdat.dmp > move.dmp


    Enter insert buffer size (minimum is 4096) 30720>

    Export file created by EXPORT:V08.00.03 via conventional path
    List contents of import file only (yes/no): no >

    Ignore create error due to object existence (yes/no): no >

    Import grants (yes/no): yes >

    Import table data (yes/no): yes >

    Import entire export file (yes/no): no >
    Username: system

    Enter table(T) or partition(T:P) names. Null list means all tables for user
    Enter table(T) or partition(T:P) name or . if done: def$_destination

    Enter table(T) or partition(T:P) name or . if done: def$_calldest

    Enter table(T) or partition(T:P) name or . if done: def$_defaultdest

    Enter table(T) or partition(T:P) name or . if done: def$_error

    Enter table(T) or partition(T:P) name or . if done: def$_origin

    Enter table(T) or partition(T:P) name or . if done: repcat$_repschema

    Enter table(T) or partition(T:P) name or . if done: .


    . importing SYSTEM's objects into SYSTEM
    . . importing table "DEF$_CALLDEST" 0 rows imported
    . . importing table "DEF$_DEFAULTDEST" 0 rows imported
    . . importing table "DEF$_DESTINATION" 2 rows imported
    . . importing table "DEF$_ERROR" 0 rows imported
    . . importing table "DEF$_ORIGIN" 0 rows imported
    . . importing table "REPCAT$_REPSCHEMA" 6 rows imported
    About to enable constraints...
    Import terminated successfully with warnings.

    10. Connect as user SYS and issue:

    SQL> GRANT UNLIMITED TABLESPACE TO SYSTEM;
    .

  3. #3
    Join Date
    May 2001
    Location
    India
    Posts
    55
    Hi vaniram,

    I am also faced same problem. But I have solved the problem very easily. by default DEF$_AQCALL will reside in system tablespace. This will grow very fast.
    just move the table to another tablespace.
    frequently you have truncate the table.

    If you are not able to do the above things plz let me know I will forward you the commands.

    My id is shankar_gk@hotmail.com or kgshangs@yahoo.com

    Regards,

    G.Shankar.

Posting Permissions

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



Click Here to Expand Forum to Full Width