OGG新增DataPump进程下发（多个进程共用队列文件）

一、背景

新增下发表，考虑到源数据库较多，且中转站系统A进程复杂，决定在系统A上新增DataPump进程，将数据投递给系统B。

系统A抽取进程EXTFZZS已抽取相关表，不必配置抽取进程。

工作思路整理：

1、系统A新增DataPump进程DPFZSJFX，系统B新增复制应用进程REPFZZS。使用expdp、impdp进行数据初始化。

2、多个投递进程共用队列文件，投递进程DPFZSJFX在启动需指定队列文件，本文指定如下：alter DPFZSJFX extseqno 0098 ,extrba 0

3、数据初始化时，由于系统B无导入数据用户，需新建，同时需新建表空间。

二、工作步骤

（一）系统A的工作

1、新建投递进程

GGSCI> view param DPFZSJFX

EXTRACT DPFZSJFX

--PASSTHRU
DYNAMICRESOLUTION

USERID goldengate password 123456, encryptkey default

RMTHOST xxx.xx.x.xxx,MGRPORT 7809
RMTTRAIL ./dirdat/fz

DISCARDFILE ./dirrpt/DPFZSJFX.dsc,APPEND,MEGABYTES 100
DISCARDROLLOVER AT 6:00

REPORTROLLOVER AT 6:00
REPORTCOUNT EVERY 1 HOURS,RATE

TABLE TESTA.T_QS_ZHXT_TRADE     ;
TABLE TESTA.T_QS_ZHXT_TRADER    ;

--for ogg veridata of xj
TABLE GOLDENGATE.GGS_COMMAND; 

2、指定队列文件

GGSCI> info DPFZBY1    --同一队列文件的进程

EXTRACT    DPFZBY1   Last Started 2017-08-14 10:51   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint  File ./dirdat/testA/fz000098
                     2017-08-15 10:31:38.000000  RBA 76075492

GGSCI> add extract DPFZSJFX, exttrailsource ./dirdat/testA/fz  --添加DataPump进程，指定使用Trail文件的位置

GGSCI> add RMTTRAIL ./dirdat/fz, extract DPFZSJFX, megabytes 1024  --添加Trail文件投递到目标端的位置，名称和大小

GGSCI> alter DPFZSJFX extseqno 0098 ,extrba 0

GGSCI> start DPFZSJFX

3、在目标端数据库B的ogg目录中查看是否有相关队列文件被投递，案例中在/oracle/ogg/dirdat查看

[oracle@db dirdat]$ ls
fz000003  gs000005  js002056  js002057  js002058  js002059  js002060  sb000394  sb000395

4、停止进程准备数据导出

GGSCI> stop DPFZSJFX

5、预估数据大小 --> 确认SCN号 -->  数据导出

--预估导出数据大小
SQL> select sum(bytes/1024/1024) MB from dba_segments where owner='TESTA' and segment_name='T_QS_ZHXT_TRADE';

        MB
----------
        20

SQL> select sum(bytes/1024/1024) MB from dba_segments where owner='TESTA' and segment_name='T_QS_ZHXT_TRADER';

        MB
----------
        20

--指定SCN号
SQL> col current_scn for 9999999999999999
SQL> select current_scn from v$database;

      CURRENT_SCN
-----------------
      15389355109734

--编辑导出脚本
vi expdp_testA_sjfx_2tb2_20170811.par
USERID='/ as sysdba'
DIRECTORY=IMPDP
LOGFILE=expdp_testA_sjfx_2tb2_20170811.log
FLASHBACK_SCN=15389355109734
DUMPFILE=expdp_testA_sjfx_2tb2_20170811.dmp
tables=(TESTA.T_QS_ZHXT_TRADE,TESTA.T_QS_ZHXT_TRADER)

--执行脚本
nohup expdp parfile=expdp_testA_sjfx_2tb2_20170811.par > expdp_testA_sjfx_2tb2_20170811.par.out &

6、dmp文件scp传至目的端数据库

scp expdp_testA_sjfx_2tb2_20170811.dmp oracle@196.2.12.111:/oracle/expdp_dir

（二）系统B的工作

1、新建复制进程REPFZZS

GGSCI> view param REPFZZS

REPLICAT REPFZZS

SETENV (ORACLE_HOME = "/oracle/app/product/11.2.0/db_1" )
SETENV (ORACLE_SID = "xxxxx")
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")

USERID goldengate password 123456, encryptkey default

ASSUMETARGETDEFS

DBOPTIONS DEFERREFCONST
DBOPTIONS SUPPRESSTRIGGERS

DISCARDFILE ./dirrpt/REPFZZS.dsc, APPEND, MEGABYTES 1000
DISCARDROLLOVER AT 6:00

REPERROR (DEFAULT, ABEND)
--REPERROR (24344, DISCARD)                                                                               -
DDL INCLUDE MAPPED , OBJTYPE 'TABLE' &
INCLUDE MAPPED OBJTYPE 'INDEX'
DDLOPTIONS REPORT


REPORTROLLOVER AT 6:00
REPORTCOUNT EVERY 30 MINUTES, RATE

REPORT AT  0:01
REPORT AT  1:01
REPORT AT  2:01
REPORT AT  3:01
REPORT AT  4:01
REPORT AT  5:01
REPORT AT  6:01
REPORT AT  7:01
REPORT AT  8:01
REPORT AT  9:01
REPORT AT 10:01
REPORT AT 11:01
REPORT AT 12:01
REPORT AT 13:01
REPORT AT 14:01
REPORT AT 15:01
REPORT AT 16:01
REPORT AT 17:01
REPORT AT 18:01
REPORT AT 19:01
REPORT AT 20:01
REPORT AT 21:01
REPORT AT 22:01
REPORT AT 23:01
STATOPTIONS RESETREPORTSTATS

NUMFILES 150
DYNAMICRESOLUTION
ALLOWNOOPUPDATES
GROUPTRANSOPS 1000

MAP TESTA.T_QS_ZHXT_TRADE           , TARGET  TESTA.T_QS_ZHXT_TRADE        ;
MAP TESTA.T_QS_ZHXT_TRADER          , TARGET  TESTA.T_QS_ZHXT_TRADER       ;

 
 
--for ogg veridata of xj
obey ./dirprm/gg_cmd_rep.obey

GGSCI> add replicat REPFZZS,exttrail ./dirdat/fz  -->添加Replicat进程，指定读取的Trail文件位置

2、查看是否有相关的用户和表空间

--查看用户
SQL> select username from dba_users;
--查看表空间
SQL> set line 1000;
set pages 1000;        
SELECT d.status "Status",
d.tablespace_name "Tablespace_Name",
d.contents "Type",
d.extent_management "Extent Management",
to_char(nvl(a.bytes / 1024 / 1024, 0), '99999999.999') "Total Size (M)",
to_char(nvl(a.bytes - nvl(f.bytes, 0), 0) / 1024 / 1024,
'99999999.999') "Used (M)",
to_char(nvl(nvl(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') "Free (M)",
to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0),
'990.00') "Used %"
FROM sys.dba_tablespaces d,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT
(d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY')
UNION ALL
SELECT d.status "Status",
d.tablespace_name "Name",
d.contents "Type",
d.extent_management "Extent Management",
to_char(nvl(a.bytes / 1024 / 1024, 0), '99999990.900') "Size (M)",
to_char(nvl(t.bytes, 0) / 1024 / 1024, '99999999.999') "Used (M)",
to_char((nvl(a.bytes / 1024 / 1024, 0)) -
(nvl(t.bytes, 0) / 1024 / 1024),
'99999999.999') "Free (M)",
to_char(nvl(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_temp_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes_cached) bytes
FROM v$temp_extent_pool
GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management LIKE 'LOCAL'
AND d.contents LIKE 'TEMPORARY'
ORDER BY "Used %" DESC;

3、无相关用户及表空间，需进行创建，在系统A获取相关创建语句并进行修改后在系统B中执行创建用户及表空间

SQL> set long 99999
SQL> select dbms_metadata.get_ddl('USER','TESTA') from dual;

DBMS_METADATA.GET_DDL('USER','TESTA')
--------------------------------------------------------------------------------

   CREATE USER "TESTA" IDENTIFIED BY VALUES 'S:26CBCC491854574418AC12A07121D4EC98
6198E43033CDED5EE423D6F8DE;41F47D344420FD7A'
      DEFAULT TABLESPACE "TESTA_DATA"
      TEMPORARY TABLESPACE "TEMP"--系统A中用户TESTA的创建语句，由于系统B中的临时文件为TEMP1，所以在重新创建用户时指定临时表空间应改为TEMP1



SQL> select dbms_metadata.get_ddl('TABLESPACE','TESTA_DATA') from dual;

DBMS_METADATA.GET_DDL('TABLESPACE','TESTA_DATA')
--------------------------------------------------------------------------------

  CREATE TABLESPACE "TESTA_DATA" DATAFILE
  '/oracle/app/oradata/testa/TESTA_data_01.dbf' SIZE 10737418240
  AUTOEXTEND ON NEXT 314572800 MAXSIZE 32767M,
  '/oracle/app/oradata/testa/TESTA_data_02.dbf' SIZE 34351349760,
  '/oracle/app/oradata/testa/TESTA_data_03.dbf' SIZE 10737418240
  AUTOEXTEND ON NEXT 314572800 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
   ALTER DATABASE DATAFILE
  '/oracle/app/oradata/testa/TESTA_data_01.dbf' RESIZE 34358689792
--系统A中表空间TESTA_DATA的创建语句，由于系统B的导入数据才40M左右，所以不必创建那么多数据文件，其余可全部删除只保留一个

4、将数据导入系统B中

--新建导入脚本
vi impdp_testA_sjfx_2tb2_20170811.par
userid='/ as sysdba'
directory=XJDIR
TABLE_EXISTS_ACTION=replace
dumpfile=expdp_testA_sjfx_2tb2_20170811.dmp
logfile=impdp_testA_sjfx_2tb2_20170811.log 

--执行导入脚本
nohup impdp parfile=impdp_testA_sjfx_2tb2_20170811.par > impdp_testA_sjfx_2tb2_20170811.par.out &

（三）开启ogg进程，先启动源端投递进程，再启动目标端复制进程

--系统A投递进程
GGSCI> start DPFZSJFX

--系统B应用进程，使用SCN号启动
GGSCI> start replicat REPFZZS,aftercsn 15389355109734

（四）两端数据对比确认是否成功同步

--在两端数据库中查询下表数据量，比对数据量是否一致
SQL> select count(1) from TESTA_T_QS_ZHXT_TRADE;
SQL> select count(1) from TESTA_T_QS_ZHXT_TRADER;

三、总结

1、导出导入时需先预估数据大小，可借助dba_segments视图，并且在导入之前确认相关用户及表空间

2、本文要点主要为指定队列文件启动投递进程