天道酬勤

说说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

Read the rest of this entry »

方法来自Piner的新书《构建Oracle高可用环境》
1.先增加一个字段

sql>Alter TABLE table_name ADD field_name number;

2.把该字段修改为有默认值

sql>Alter TABLE table_name MODIFY field_name DEFAULT 0;

该语句并不修改以前的记录，只是标记以后的记录将会有默认值0,所以，这个语句的执行很快，不会引发阻塞。
3.修改以前的记录，让其有默认值（每修改1000条记录 commit一次）

1
2
3
4
5
6
7
8
9
10
11
12

DECLARE
       row_num NUMBER := 0;
BEGIN
       FOR c_test IN (SELECT ROWID rid FROM table_name t WHERE field_name IS NULL) LOOP
             UPDATE table_name t SET t.field_name = 0 WHERE ROWID = c_test.rid;
             row_num := row_num + 1;
             IF MOD(row_num,1000) =0 THEN
                  COMMIT;
             END IF;
        END LOOP;
        COMMIT;
END;