一点一滴努力,满仓满谷收成

有些时侯主从数据不一致时需要对复制做一次全同步，简明步骤如下：

1 查看现有复制状态

iSQL> set linesize 156
iSQL> set colsize 40
iSQL> select a.REP_NAME , a. SENDER_IP, a.PEER_IP,
2 decode(b.IS_STARTED,1,’Running’,0,’Not Running’)
3 from V$REPSENDER a, SYSTEM_.SYS_REPLICATIONS_ b
4 where a.REP_NAME=b.REPLICATION_NAME;

2 充许DDL锁,删原有复制
主库/从库：

iSQL> alter system set DDL_LOCK_TIMEOUT=10;
Alter success.

iSQL> alter replication rep_cqocs stop;
Alter success.

iSQL>drop replication rep_cqocs ;

3 truncate从库各表
从库：
查看复制中包含哪些表：

iSQL> select REPLICATION_NAME ,LOCAL_USER_NAME,LOCAL_TABLE_NAME from system_.SYS_REPL_ITEMS_;

truncate各个复制表
4 建立复制
主/从库:

iSQL> @create_rep.sql
iSQL> create replication REP_CQOCS with ‘192.168.200.177’,30302
from IBILLAPP.CELL_REMIND to IBILLAPP.CELL_REMIND,
from IBILLAPP.DISCOUNT_REL_INFO to IBILLAPP.DISCOUNT_REL_INFO,
from IBILLAPP.MINIMUM_CONSUM to IBILLAPP.MINIMUM_CONSUM,
from IBILLAPP.MM_WEED_INFO to IBILLAPP.MM_WEED_INFO,
from IBILLAPP.SERV to IBILLAPP.SERV,
from IBILLAPP.SERV_CITY_CODE_INFO to IBILLAPP.SERV_CITY_CODE_INFO,
from IBILLAPP.SERV_LOCATION_CODE_INFO to IBILLAPP.SERV_LOCATION_CODE_INFO,
from SYS.TB_TEST1 to SYS.TB_TEST1,
from IBILLAPP.USER_BILL_PLAN to IBILLAPP.USER_BILL_PLAN,
from IBILLAPP.USER_FAV to IBILLAPP.USER_FAV,
from IBILLAPP.USER_FREE_RESOURCE to IBILLAPP.USER_FREE_RESOURCE,
from IBILLAPP.user_bill_plan_attr to IBILLAPP.user_bill_plan_attr,
from IBILLAPP.serv_attr to IBILLAPP.serv_attr,
from IBILLAPP.no_transfer to IBILLAPP.no_transfer,
from IBILLAPP.user_bill_transfer to IBILLAPP.user_bill_transfer,
from IBILLAPP.user_fav_Residual to IBILLAPP.user_fav_Residual,
from IBILLAPP.USER_RELATION_PHONE to IBILLAPP.USER_RELATION_PHONE;
Create success.

5 开始同步数据
主库：

iSQL> alter system set REPLICATION_SYNC_TUPLE_COUNT=20000000;
Alter success.

iSQL> alter replication rep_cqocs sync parallel 16;
Alter success.

6 查看主从差

iSQL> select rep_gap from v$repgap;
REP_GAP
———————–
882792
1 row selected.

7 收尾
把从库的复制启动
从库：

iSQL> alter replication rep_cqocs start;
Alter success.

全同步后主库同步自动启动，手动把从库的启动即可

关闭DDL锁

alter system set DDL_LOCK_TIMEOUT=0;