Replication - Moving/Re-organize replication system tables

========================================================================
= Moving/Re-organize replication system tables for an existing
replicated database =
========================================================================


Reference
=========
Note:1037317.6

Issues
======
(Seen in GENP as an example)

1. Some DEF$ table in SYSTEM tablespace

system@GENP> select table_name, tablespace_name from user_tables where
table_name like 'DEF$%';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEF$_AQCALL GEN_REP_DATA
DEF$_AQERROR GEN_REP_DATA
DEF$_CALLDEST GEN_REP_DATA
DEF$_DEFAULTDEST GEN_REP_DATA
DEF$_DESTINATION GEN_REP_DATA
DEF$_ERROR GEN_REP_DATA
DEF$_LOB SYSTEM
DEF$_ORIGIN GEN_REP_DATA
DEF$_PROPAGATOR SYSTEM
DEF$_PUSHED_TRANSACTIONS SYSTEM
DEF$_TEMP$LOB SYSTEM


2. DEF$_AQCALL grows too large (HWM too high)

SEGMENT_NAME size(M)
-------------------------------------------------- ----------
DEF$_AQCALL 5403.625
SYS_LOB0000002581C00003$$ 3241
SYS_C00120258 411.5
DEF$_TRANORDER 282.4375
DEF$_CALLDEST_PRIMARY 7.625


Prepare
========

Before starting this procedure, perform the following:

1) Make sure all objects owned by SYS and SYSTEM are valid
2) Backup of the database


Procedure
=========

1) Make sure queue is clear
select count(*) from deftran;
select count(*) from deftrandest;
select count(*) from defcalldest;
select count(*) from deferror;


2) Quiesce your environment to guarantee no new transactions are created
during this process.


3) Perform a TABLE level export

exp system/manager file=repexp.dmp
TABLES=(DEF$_ERROR,
REPCAT$_REPSCHEMA,
DEF$_DESTINATION,
DEF$_CALLDEST,
DEF$_DEFAULTDEST,
DEF$_LOB,
DEF$_TEMP$LOB,
DEF$_ORIGIN,
DEF$_PUSHED_TRANSACTIONS,
DEF$_PROPAGATOR)
LOG=<path>/exprep.log

Note: DEF$_PROPAGATOR added by Denis

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

drop table system.DEF$_LOB;
drop table system.DEF$_TEMP$LOB;

drop table system.DEF$_PUSHED_TRANSACTIONS;
drop table system.DEF$_PROPAGATOR;


5) Connect to SQL*Plus as SYSTEM or REPADMIN and issue:

execute dbms_aqadm.drop_queue_table('SYSTEM.DEF$_AQCALL',TRUE);
execute dbms_aqadm.drop_queue_table('SYSTEM.DEF$_AQERROR',TRUE);


6) Connect to SQL*Plus as SYS and issue:

alter user system default tablespace gen_rep_data
quota unlimited on gen_rep_data;

alter user system quota 0 on system;
revoke unlimited tablespace from system;

7) Connect to SQL*Plus as SYS and run the script "catdefrt.sql" located
in
the "$ORACLE_HOME/rdbms/admin" directory. This recreates all of the
def$_ tables in the new tablespace, as well as associated queues and
indexes.


@?/rdbms/admin/catdefrt

8) Import the tables in the order listed from your export file from step
3
above.

DEF$_ERROR
REPCAT$_REPSCHEMA
DEF$_DESTINATION
DEF$_CALLDEST
DEF$_DEFAULTDEST
DEF$_LOB
DEF$_TEMP$LOB
DEF$_ORIGIN
DEF$_PUSHED_TRANSACTIONS
DEF$_PROPAGATOR


9) After you have imported all replication tables, verify that the
tables are
contained in the new tablespace.

connect system/<password>;
select TABLE_NAME, TABLESPACE_NAME from USER_TABLES;

10) Change tablespace privs

alter user system default tablespace system quota unlimited on
system;
grant unlimited tablespace to system;

11) Check the dba_objects view for invalid objects. If there are, run
utlrp.sql
connected as sysdba.

select count(*) from dba_objects where status = 'INVALID';

count(*)
--------------------
32 <---- (You may see a count of 42 in Oracle9i
9.x)

If count(*) is greater than zero, run as SYS:

@$ORACLE_HOME/rdbms/admin/utlrp.sql

This should recompile the relocated objects. If problems persist see
Note 106206.1 which addresses ORA-4068 errors from Oracle packages.