天道酬勤

说说08年的一个案例，Oracle的高级复制，尽管现在大家都不用了。公司扩大业务，在HB建了分公司，要将SZ这边的一部分数据（其实有一千多张表）同步到HB，在配置好高级复制之后测试，由于要复制的数据量增长比较快，以至于表空间飞快增长，当我们想要停止和删除复制环境的时候，就被挂起，google的很多文章都不见效，最后救助Oracle TS解决，以下是解决方案：

SOLUTION / ACTION PLAN
To implement the solution, please execute the following steps:

For each of the 5 groups,just do the actions one by one.
1. if posible,please stop the application for a while. But do not stop the resouce oracle using
(including network,listener,etc)

2. Stop all the replication push and purge job. Stop current running push&purge job first.note:
1)in 2 sites,run the whole step 2
2)no special comments, all user please use ‘repadmin’

2.1 Check if the push job is currently running

1
2
3
4
5
6

sql>select /*+ ORDERED */ j.job, j.sid, d.dblink,
SUBSTR(TO_CHAR(J.THIS_DATE,'MM/DD/RRRR HH24:MI:SS'),1,20) START_DATE
FROM defschedule d, dba_jobs_running j
WHERE j.job IN (SELECT job FROM dba_jobs
WHERE UPPER(what) LIKE '%DBMS_DEFER_SYS.PUSH%')
AND j.job = d.job;

       JOB SID DBLINK START_DATE
       ---------- ---------- ------------------------------
        44 9 DB2.WORLD 05/16/2002 12:14:47

- if exist,then note the sid
- Kill the Job Queue Process from the Operating System. To do this usethe sid to identify the
process in v$session, v$process and v$bgprocess. The process will generally be named
SNPx or Jxxx.
- After killing the process, wait approximately 1 minute, to ensure the job is removed from
dba_jobs_running.

You can kill the corresponding session after finding its serial# from v$session:

sql>select serial# FROM v$session WHERE sid=9;
       SERIAL#
      ----------
        24909

sql>alter system KILL SESSION '8,24909';

2.2 stop all push&purge job in 2 sites.

sql>select job, what,broken FROM dba_jobs WHERE UPPER(what) LIKE '%DBMS_DEFER_SYS%';
 
      JOB   WHAT BROKEN
      ----   --------------------------------------------------------------------------
       43     DECLARE rc binary_integer;
                BEGIN rc := sys.dbms_defer_sys.purge( N
                delay_seconds=>0); END;
       44     DECLARE rc binary_integer;
                BEGIN rc := sys.dbms_defer_sys.push(d N
                estination=>'DB2.WORLD', stop_on_error=>FALSE, delay_seconds=>0,
                parallelism=>2); END;

- Break the job with :
execute dbms_job.broken(, true);–need be changed from the previous query result.commit;
Query again to confirm the push & purge job has all been stopped

3. remove the deferred queue

1
2

sql>select constraint_name, TABLE_NAME
      FROM sys.dba_constraints WHERE TABLE_NAME = 'DEF$_CALLDEST';

       CONSTRAINT_NAME TABLE_NAME
       ------------------------------ ------------------
       DEF$_CALLDEST_PRIMARY DEF$_CALLDEST
       DEF$_CALL_DESTINATION DEF$_CALLDEST

Note: As of Oracle8, system.DEF$_CALLDEST_CALL in not being used and system.DEF$_CALL has been changed to system.DEF$_AQCALL (this is due to the advanced queueing features in Oracle8)

1
2
3
4
5
6
7
8
9
10
11

sql>truncate TABLE system.DEF$_AQCALL;
sql>truncate TABLE system.DEF$_CALLDEST;
sql>truncate TABLE system.DEF$_ERROR;
sql>truncate TABLE system.DEF$_AQERROR;
sql>truncate TABLE system.DEF$_LOB; -- if there are LOBs replicated.
sql>select * FROM DEFCALL;
       no ROWS
sql>select * FROM DEFTRAN;
       no ROWS
sql>select * FROM DEFERROR;
       no ROWS

4. When All the steps are implemented on 2 sites,we can try to suspend the master group from the master definition site.
It wont take a lot of time. Then,you can remove the replcation object and the w
hole replication enviroment.

1
2
3

sql>select gname, request, STATUS, errnum
     FROM dba_repcatlog
     ORDER BY id, gname;

Additional:
If the below step be done,u must start again and u do above steps .

1
2
3
4
5
6
7
8

DBMS_DEFER_SYS.SET_DISABLED (
                         destination IN VARCHAR2,
                         disabled IN BOOLEAN := TRUE,
                         catchup IN RAW := '00',
                         override IN BOOLEAN := FALSE);
DBMS_DEFER_SYS.SET_DISABLED (
                         Destination => ‘meslf’,
                         disabled = TRUE);