【Oracle】Oracle数据同步链路的搭建

---

1、前期准备工作

1.1、源端：

1.1.1、检查源端数据库是否为归档模式，若为非归档模式，将其改为归档模式：

SQL>archive log list;

1.1.2、检查源端数据库附加日志是否打开：

SQL>select supplemental_log_data_min,force_logging from v$database;

1.1.3、将数据库附加日志打开：

SQL>alter database add supplemental log data;

1.1.4、切换日志以使附加日志生效：

SQL>alter system archive log current; 

1.1.5、开启force logging模式：

SQL>alter database force logging;

2、链路配置

2.1、创建extract进程

ADD EXTRACT EPBPTJH INTEGRATED TRANLOG, BEGIN NOW
ADD EXTTRAIL /data/ogg21/EPBPTJH/trail/ea, EXTRACT EPBPTJH 

2.2.1、编辑extract进程配置文件

EXTRACT EPIE8AB1
USERID username, PASSWORD 
AACAAAAAAAAAAANAMISHOEYDWBYEMJPGLBOCIHQISIPDKCLI, ENCRYPTKEY DEFAULT
LOGALLSUPCOLS
cachemgr cachesize 5G, CACHEDIRECTORY /oggdata/oggdump/EPIE8AB2/cache
BR BRDIR /oggdata/oggdump/PIE8AB2/br
WARNLONGTRANS 2h, CHECKINTERVAL 30m
UPDATERECORDFORMAT COMPACT
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
GETTRUNCATES
TRANLOGOPTIONS GETCTASDML
EXTTRAIL /oggdata/oggdump/EPIE8AB2/trail/ea

SOURCECATALOG zypx
table *.*

2.2.2、建立目录

mkdir -p /oggdata/oggdump/EPIE8AB2/cache
mkdir -p /oggdata/oggdump/EPIE8AB2/br
mkdir -p /oggdata/oggdump/EPIE8AB2/trail/

2.2.3、加schema级别附加日志

USERID username, PASSWORD 
AACAAAAAAAAAAANAMISHOEYDWBYEMJPGLBOdgCIHQISIPDKCLI, ENCRYPTKEY DEFAULT
ADD SCHEMATRANDATA zypx.*  ALLCOLS 

2.2.4、注册extract进程

REGISTER extract EPIE8AB2 DATABASE container(zypx)

2.2.5、启动extract进程

start EPIE8AB2

2.3、创建pump进程

ADD EXTRACT PPIE8AB2  EXTTRAILSOURCE 
/oggdata/oggdump/EPIE8AB2/trail/ea
ADD RMTTRAIL /oggdata/oggdump/EPIE8AB2/trail/ep , EXTRACT PPIE8AB2

2.3.1、编辑pump进程参数

EXTRACT PPIE8AB2
RMTHOST gsg, MGRPORT 7810
RMTTRAIL /oggdata/oggdump/EPIE8AB2/trail/ep
SOURCECATALOG zypx
TABLE *.*;

2.3.2、启动pump进程

start PPIE8AB1

2.4、创建目标库checkpointtable

USERID username, PASSWORD 
AACAAAAAAAAAAANAMISHOEYDWBYEMJPGLBOCIHQISIPDKCLI, ENCRYPTKEY DEFAULT

add CHECKPOINTTABLE oggadmin.checkpoint

2.5、编辑GLOBALS文件

非必要，如果源端投递进程报错，提示目录不可写的时候添加

ALLOWOUTPUTDIR /oggdata/oggdump/EPIE8AB2/trail

2.6、创建目标端replicat进程

ADD REPLICAT ZYPX,  PARALLEL INTEGRATED, EXTTRAIL /oggdata/oggdump/EPIE8AB2/trail/ep, checkpointtable oggadmin.checkpoint

2.6.1、配置replicat进程参数

##### 配置进程 GDYCZ

REPLICAT GDYCZ

USERID username, PASSWORD 
AACAAAAAAAAAAAeNAMISgsgHOEYDWBYEMJPGLBOCIHQISIPDKCLI, ENCRYPTKEY DEFAULT

--DDLERROR 6063 IGNORE   --未知异常，无法判断
--DDLERROR 904  IGNORE   --invalid identify 不能配置，出现错误时候再判断
--DDLERROR 2149 IGNORE   --分区不存在，不能配置
--DDLERROR 1435 IGNORE   --用户不存在，可以配置
--DDLERROR 2019 IGNORE   --找不到远程链接，dblink类型的DDL ERROR。 可以配置
--DDLERROR 4063 IGNORE   --视图或者存储过程异常。可以配置
--DDLERROR 942  IGNORE   --表不存在。如果删除会有大量的临时表需要运维手工处理。
--DDLERROR 955  IGNORE   --对象名已经存在。 可以配置
--DDLERROR 4052 IGNORE   --远程对象没找到。 可以配置
--DDLERROR 1918 IGNORE   --用户不存在， 可以配置
--DDLERROR 12170 IGNORE  --TNS链接超时，可以配置
--DDLERROR 1418 IGNORE   --索引对象不存在，可以配置
--DDLERROR 2431 IGNORE   --disable的约束对象不存在，可以配置
--DDLERROR 25129 IGNORE  --modify的约束对象不存在，可以配置
--DDLERROR 2443 IGNORE   --drop的约束对象不存在，可以配置

MAP_PARALLELISM 2
MIN_APPLY_PARALLELISM 2
MAX_APPLY_PARALLELISM 8


BATCHSQL
DDLOPTIONS REPORT
REPLACEBADCHAR SKIP
DDL INCLUDE MAPPED
REPORTCOUNT EVERY 1 MINUTES, RATE
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
REPERROR DEFAULT, ABEND
APPLYNOOPUPDATES
DISCARDFILE ./dircrd/gdyczypx.dsc, APPEND, MEGABYTES 1024
DISCARDROLLOVER

MAP GDYCZYPX.OGDT.*,    TARGET OGDT.*;

MAP GDYCZYPX.GDT.*,     TARGET GDT.*;  

2.7、初始化数据（通过数据泵进行数据的初始化）

2.7.1、创建目标端连接TNS

zypx =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = zypx_srv)
    )
  )

2.7.2、数据初始化导入

impdp username/passwd@tywwb  DUMPFILE=tywwb_%U.dmp DIRECTORY=rzdir 
metrics=y LOGFILE=imp_tywwb.log cluster=N TABLE_EXISTS_ACTION=REPLACE 
PARALLEL=16

2.7.3、检查是否有定时任务及触发器

-- 触发器
select table_owner,count(1) as trg_num from dba_triggers where 
table_owner in (select username from dba_users where 
ORACLE_MAINTAINED='N') and status='ENABLED' group by table_owner;

-- 定时任务
select schema_user,count(1) as job_num from dba_jobs where 
schema_user in (select username from dba_users where 
ORACLE_MAINTAINED='N') and BROKEN='N' group by schema_user;

-- 用户作业
select owner,count(1) as sjob_num from dba_scheduler_jobs where owner 
in (select username from dba_users where ORACLE_MAINTAINED='N') and 
ENABLED='TRUE' group by owner;

2.7.4、禁用定时任务及触发器

declare
begin
    for cur in (select t.OWNER,t.TRIGGER_NAME from dba_triggers t 
where t.status='ENABLED' and t.OWNER in (select username from 
dba_users where ORACLE_MAINTAINED='N'))
    loop
          execute immediate 'alter trigger '|| cur.owner || '.' || 
cur.trigger_name ||' disable';
          COMMIT;
    end loop;
    
       for cur in (select t.SCHEMA_USER,t.job from dba_jobs t where 
t.BROKEN='N' and t.SCHEMA_USER in (select username from dba_users 
where ORACLE_MAINTAINED='N'))
    loop
          execute immediate 'begin 
dbms_job.broken('||cur.JOB||',true); end;';
          COMMIT;
    end loop;
   
    FOR V IN(SELECT * FROM DBA_SCHEDULER_JOBS T WHERE OWNER in 
(select username from dba_users where ORACLE_MAINTAINED='N') AND 
T.ENABLED='TRUE')
    LOOP
          DBMS_SCHEDULER.DISABLE(V.OWNER||'.'||V.JOB_NAME);
          COMMIT;
    END LOOP;
end;
/

2.7.5、二次检查是否有任务及触发器

select table_owner,count(1) as trg_num from dba_triggers where 
table_owner in (select username from dba_users where 
ORACLE_MAINTAINED='N') and status='ENABLED' group by table_owner;

select schema_user,count(1) as job_num from dba_jobs where 
schema_user in (select username from dba_users where 
ORACLE_MAINTAINED='N') and BROKEN='N' group by schema_user;

select owner,count(1) as sjob_num from dba_scheduler_jobs where owner 
in (select username from dba_users where ORACLE_MAINTAINED='N') and 
ENABLED='TRUE' group by owner;

初始化完成，开始增量同步数据

3、启动 replicat 同步增量

已配置了DBOPTIONS ENABLE_INSTANTIATION_FILTERING 参数，不用指定SCN

start ZYPX