OGG不同表指定不同extract、dump、replicat

注意：每个extract对应一个dump和replicat，有多个个extract就有多少个dump和replicat

1.1 环境准备

create tablespace test_tbs datafile '/u01/app/oracle/oradata/ggg11g/test_tbs01.dbf' size 10M autoextend on maxsize unlimited;
create user test identified by test account unlock default tablespace test_tbs;
grant  unlimited tablespace to test;
grant connect,resource to test;
grant select any dictionary to test;
grant select any table to test;
grant create view to test;

-- 创建相关测试对象
conn test/test;
create table t1 as select * from scott.emp;
create table t2 as select * from scott.dept;
create table t3 as select * from dba_objects;
create table t4 as select * from dba_users;
create index idx_t1_sal on t1(sal);
create or replace view v_sel_t1_empno_ename_sal as select empno,ename,sal from t1;

-- 查询test下的对象

col object_name for a25;
select object_name,object_type from dba_objects where owner = upper('test');

OBJECT_NAME               OBJECT_TYPE
------------------------- -------------------
T1                        TABLE
T2                        TABLE
T3                        TABLE
IDX_T1_SAL                INDEX
V_SEL_T1_EMPNO_ENAME_SAL  VIEW
T4                        TABLE

1.2 目标端创建需要被同步的对象的表空间

-- 查看test用户下对象关联的表空间，需要手动在目标端创建
col tablespace_name for a30;
select distinct owner,
       tablespace_name
from   dba_segments
where  owner in (select username
                 from   dba_users 
                 where  username = 'TEST'
                 and    account_status = 'OPEN');


OWNER      TABLESPACE_NAME
---------- ------------------------------
TEST       TEST_TBS


-- 查看对应表空间的大小

set line 121;
set pagesize 49999;
col tablespace_name format a25; 
col used_percent format a12;
col used_gb format 99999;
col size_gb format 99999;
select a.tablespace_name,
       round(a.tablespace_size * b.value / 1024 / 1024 / 1024, 2) TOTAL_GB,
       round(a.used_space * b.value / 1024 / 1024 / 1024, 2) USED_GB,
       round(a.tablespace_size * b.value / 1024 / 1024 / 1024, 2) -
       round(a.used_space * b.value / 1024 / 1024 / 1024, 2) FREE_GB,
       ROUND(A.used_percent, 2)|| '%' used_percent
 from  dba_tablespace_usage_metrics a, v$parameter b
 where b.NAME = 'db_block_size'
and    a.tablespace_name in (select distinct tablespace_name
                             from   dba_segments
                             where  owner in (select username
                                              from   dba_users 
                                              where  username = 'TEST'
                                              and    account_status = 'OPEN'))
 order by used_percent DESC;

TABLESPACE_NAME             TOTAL_GB USED_GB    FREE_GB USED_PERCENT
------------------------- ---------- ------- ---------- ------------
TEST_TBS                          32       0      31.99 .03%


-- Target DB创建TEST_TBS表空间

create tablespace TEST_TBS datafile '/u01/app/oracle/oradata/GGG19C/pdb01/test_tbs01.dbf' size 10M autoextend on maxsize unlimited;

1.3 源端配置抽取进程、DUMP进程

-- 查询test下的对象 (需要被同步的对象)

col object_name for a25;
select object_name,object_type from dba_objects where owner = upper('test');

OBJECT_NAME               OBJECT_TYPE
------------------------- -------------------
T1                        TABLE
T2                        TABLE
T3                        TABLE
IDX_T1_SAL                INDEX
V_SEL_T1_EMPNO_ENAME_SAL  VIEW
T4                        TABLE

/* 

1. T3、T4假定为大表使用单独的抽取、DUMP、复制组
2. 其他的对象使用抽取、DUMP、复制组

*/

1.3.1 配置Extract

1. 配置抽取所有对象(除了T3和T4)的extract进程


add extract ettest1,tranlog begin now,threads 1
add exttrail ./dirdat/et,extract ettest1

edit params ettest1

-- 抽取所有对象(除了T3和T4)

-- 抽取进程名称
extract ettest1

-- 设置环境变量
setenv (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK") 
setenv (ORACLE_HOME = "/u01/app/oracle/product/11.2.0.4/dbhome_1")

-- 设置登录源库用户、TNS和密码
userid ogg_admin@tns_ggg11g, password ogg_admin
-- ASM环境才会用到该参数
-- tranlogoptions asmuser sys@tns_xxxdgpri_asm,asmpassword oracle
-- 设置传输延时
threadoptions maxcommitpropagationdelay 20000
-- 设置抽取文件
-- .代表$OGG_HOME
exttrail ./dirdat/et
-- 动态解析
dynamicresolution
-- DDL复制
ddl include all
-- gettruncates
-- 复制表
table test.*;
-- 是否同步sequence
-- sequence ogg_test.*;

tableexclude test.t3;
tableexclude test.t4;


2. 配置抽取T3和T4的extract进程


add extract ettest2,tranlog begin now,threads 1
add exttrail ./dirdat/eh,extract ettest2

edit params ettest2
-- 抽取T3和T4

-- 抽取进程名称
extract ettest2

-- 设置环境变量
setenv (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK") 
setenv (ORACLE_HOME = "/u01/app/oracle/product/11.2.0.4/dbhome_1")

-- 设置登录源库用户、TNS和密码
userid ogg_admin@tns_ggg11g, password ogg_admin
-- ASM环境才会用到该参数
-- tranlogoptions asmuser sys@tns_xxxdgpri_asm,asmpassword oracle
-- 设置传输延时
threadoptions maxcommitpropagationdelay 20000
-- 设置抽取文件
-- .代表$OGG_HOME
exttrail ./dirdat/eh
-- 动态解析
dynamicresolution
-- DDL复制
ddl include all
-- gettruncates
-- 复制表
-- table test.*;
-- 是否同步sequence
-- sequence ogg_test.*;

table test.t3;
table test.t4;

1.3.2 配置DUMP进程

add extract dptest1,exttrailsource ./dirdat/et,begin now
-- 添加数据泵进程对应远程追踪文件
-- 该追踪文件和目标段复制追踪文件一致
add rmttrail ./dirdat/rt,extract dptest1

edit params dptest1

extract dptest1
rmthost 192.168.59.71,mgrport 7809
rmttrail ./dirdat/rt
passthru
-- gettruncates
ddl include all
table test.*;
-- sequence ogg_test.*;
tableexclude test.t3;
tableexclude test.t4;




add extract dptest2,exttrailsource ./dirdat/eh,begin now
-- 添加数据泵进程对应远程追踪文件
-- 该追踪文件和目标段复制追踪文件一致
add rmttrail ./dirdat/rh,extract dptest2

edit params dptest2

extract dptest2
rmthost 192.168.59.71,mgrport 7809
rmttrail ./dirdat/rh
passthru
-- gettruncates
ddl include all
-- table test.*;
-- sequence ogg_test.*;
table test.t3;
table test.t4;


1.4 源端添加test用户对象的补充日志

GGSCI (lissen-11g) 29> dblogin userid ogg_admin@tns_ggg11g,password ogg_admin
Successfully logged into database.

GGSCI (lissen-11g as ogg_admin@ggg11g) 30> add trandata test.*

2022-02-12 04:24:44  WARNING OGG-06439  No unique key is defined for table T1. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

2022-02-12 04:24:44  INFO    OGG-15130  No key found for table TEST.T1.  All viable columns will be logged.

2022-02-12 04:24:45  INFO    OGG-15132  Logging of supplemental redo data enabled for table TEST.T1.

2022-02-12 04:24:45  INFO    OGG-15133  TRANDATA for scheduling columns has been added on table TEST.T1.

2022-02-12 04:24:45  INFO    OGG-15135  TRANDATA for instantiation CSN has been added on table TEST.T1.

2022-02-12 04:24:45  WARNING OGG-06439  No unique key is defined for table T2. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

2022-02-12 04:24:45  INFO    OGG-15130  No key found for table TEST.T2.  All viable columns will be logged.

2022-02-12 04:24:45  INFO    OGG-15132  Logging of supplemental redo data enabled for table TEST.T2.

2022-02-12 04:24:45  INFO    OGG-15133  TRANDATA for scheduling columns has been added on table TEST.T2.

2022-02-12 04:24:45  INFO    OGG-15135  TRANDATA for instantiation CSN has been added on table TEST.T2.

2022-02-12 04:24:45  WARNING OGG-06439  No unique key is defined for table T3. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

2022-02-12 04:24:45  INFO    OGG-15130  No key found for table TEST.T3.  All viable columns will be logged.

2022-02-12 04:24:45  INFO    OGG-15132  Logging of supplemental redo data enabled for table TEST.T3.

2022-02-12 04:24:46  INFO    OGG-15133  TRANDATA for scheduling columns has been added on table TEST.T3.

2022-02-12 04:24:46  INFO    OGG-15135  TRANDATA for instantiation CSN has been added on table TEST.T3.

2022-02-12 04:24:46  WARNING OGG-06439  No unique key is defined for table T4. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

2022-02-12 04:24:46  INFO    OGG-15130  No key found for table TEST.T4.  All viable columns will be logged.

2022-02-12 04:24:46  INFO    OGG-15132  Logging of supplemental redo data enabled for table TEST.T4.

2022-02-12 04:24:46  INFO    OGG-15133  TRANDATA for scheduling columns has been added on table TEST.T4.

2022-02-12 04:24:46  INFO    OGG-15135  TRANDATA for instantiation CSN has been added on table TEST.T4.

GGSCI (lissen-11g as ogg_admin@ggg11g) 31> info trandata test.*

Logging of supplemental redo log data is enabled for table TEST.T1.

Columns supplementally logged for table TEST.T1: "COMM", "DEPTNO", "EMPNO", "ENAME", "HIREDATE", "JOB", "MGR", "SAL".

Prepared CSN for table TEST.T1: 1208368
Logging of supplemental redo log data is enabled for table TEST.T2.

Columns supplementally logged for table TEST.T2: "DEPTNO", "DNAME", "LOC".

Prepared CSN for table TEST.T2: 1208405
Logging of supplemental redo log data is enabled for table TEST.T3.

Columns supplementally logged for table TEST.T3: "CREATED", "DATA_OBJECT_ID", "EDITION_NAME", "GENERATED", "LAST_DDL_TIME", "NAMESPACE", "OBJECT_ID", "OBJECT_NAME", "OBJECT_TYPE", "OWNER", "SECONDARY", "STATUS", "SUBOBJECT_NAME", "TEMPORARY", "TIMESTAMP".

Prepared CSN for table TEST.T3: 1208442
Logging of supplemental redo log data is enabled for table TEST.T4.

Columns supplementally logged for table TEST.T4: "ACCOUNT_STATUS", "AUTHENTICATION_TYPE", "CREATED", "DEFAULT_TABLESPACE", "EDITIONS_ENABLED", "EXPIRY_DATE", "EXTERNAL_NAME", "INITIAL_RSRC_CONSUMER_GROUP", "LOCK_DATE", "PASSWORD", "PASSWORD_VERSIONS", "PROFILE", "TEMPORARY_TABLESPACE", "USERNAME", "USER_ID".

Prepared CSN for table TEST.T4: 1208477
GGSCI (lissen-11g as ogg_admin@ggg11g) 32> 


1.5 目标端配置Replicat进程


-- 登录TargetDB
dblogin,userid ogg_admin@tns_pdb01,password ogg_admin
-- 创建检查点表
add checkpointtable ogg_admin.chkpt_tab			-- 如果检查表存在就不用重复添加了
-- 添加复制进程组
-- ./dirdat/ra对应源端DMP进程参数配置



add replicat rptest1,exttrail ./dirdat/rt,begin now checkpointtable ogg_admin.chkpt_tab

edit params rptest1


-- replicat进程名称
replicat rptest1

-- 设置环境变量ORACLE_HOME
setenv (ORACLE_HOME="/u01/app/oracle/product/19.3.0.0/dbhome_1")

-- 设置语言
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")

-- 设置用于复制的账号密码、TNS
userid ogg_admin@tns_pdb01,password ogg_admin

-- ASSUMETARGETDEFS is ignored in OGG 12.2
-- 源端和目标端数据库类型一致，无需生成数据定义文件
assumetargetdefs

-- 目标端存在数据时忽略
-- 只在初始化时使用，初始化完成后建议关闭该参数
-- handlecollisions

-- 复制错误
reperror (default,discard)

-- DDL错误报告
ddlerror default discard

-- DDL操作添加到报告
ddloptions report

-- DDL操作只包含MAPPED的对象
ddl include mapped

-- 定义discardfile文件位置，如果处理中有记录出错会写入到此文件中
discardfile ./repsz.dsc,append,megabytes 1024

-- 映射源端和目标端对象
map test.*,target test.*;

mapexclude test.t3;
mapexclude test.t4;


-- 单表恢复
-- map ogg_test.t4, target ogg_test.t4, filter (@GETENV('transaction','csn') > 1046510);

-- map ogg_test.t5, target ogg_test.t5, filter (@GETENV('transaction','csn') > 1183545);

DBOPTIONS ENABLE_INSTANTIATION_FILTERING




add replicat rptest2,exttrail ./dirdat/rh,begin now checkpointtable ogg_admin.chkpt_tab


edit params rptest2

-- replicat进程名称
replicat rptest2

-- 设置环境变量ORACLE_HOME
setenv (ORACLE_HOME="/u01/app/oracle/product/19.3.0.0/dbhome_1")

-- 设置语言
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")

-- 设置用于复制的账号密码、TNS
userid ogg_admin@tns_pdb01,password ogg_admin

-- ASSUMETARGETDEFS is ignored in OGG 12.2
-- 源端和目标端数据库类型一致，无需生成数据定义文件
assumetargetdefs

-- 目标端存在数据时忽略
-- 只在初始化时使用，初始化完成后建议关闭该参数
-- handlecollisions

-- 复制错误
reperror (default,discard)

-- DDL错误报告
ddlerror default discard

-- DDL操作添加到报告
ddloptions report

-- DDL操作只包含MAPPED的对象
ddl include mapped

-- 定义discardfile文件位置，如果处理中有记录出错会写入到此文件中
discardfile ./repsz.dsc,append,megabytes 1024

-- 映射源端和目标端对象
map test.t3,target test.t3;
map test.t4,target test.t4;

-- 单表恢复
-- map ogg_test.t4, target ogg_test.t4, filter (@GETENV('transaction','csn') > 1046510);
-- map ogg_test.t5, target ogg_test.t5, filter (@GETENV('transaction','csn') > 1183545);

DBOPTIONS ENABLE_INSTANTIATION_FILTERING


1.6 初始化数据

S:

expdp \'\/ as sysdba\' directory=dir1 dumpfile=test.dmp schemas=test


-- 复制数据到Target DB
[oracle@lissen-11g dir1]$ scp test.dmp oracle@192.168.59.71:/home/oracle/dir1/
oracle@192.168.59.71's password: 
test.dmp 

-- 模拟生成环境业务变化
S:

TEST@ggg11g>select count(*) from t3;

  COUNT(*)
----------
     87027

TEST@ggg11g>delete from t3 where rownum < 20000;

19999 rows deleted.

TEST@ggg11g>commit;

Commit complete.

TEST@ggg11g>select count(*) from t3;

  COUNT(*)
----------
     67028

TEST@ggg11g>select count(*) from t4;

  COUNT(*)
----------
        39

TEST@ggg11g>delete from t4 where rownum < 11;

10 rows deleted.

TEST@ggg11g>commit;

Commit complete.

TEST@ggg11g>select count(*) from t4;

  COUNT(*)
----------
        29


T:

impdp \'sys\/oracle\@tns_pdb01 as sysdba\' directory=dir1 dumpfile=test.dmp



1.7 启动extract和replicat

start extract *
start replicat *