使用数据泵+OGG微服务新参数ENABLE_INSTANTIATION_FILTERING零停机迁移12c到19c
Tags： ENABLE_INSTANTIATION_FILTERINGOGGOGG微服务Oracle数据泵数据迁移零停机
👉 本文共约2783个字，系统预计阅读时间或需11分钟。
类似文章
OGG有传统的经典架构，也有最新的微服务，2个都可以远程捕获和应用数据，对数据库服务器是0侵入，而传统的经典架构是纯命令行模式，最新的微服务架构是图形化界面操作，几乎所有操作都可以在界面进行。相关文章可以参考：
- 使用OGG for MySQL微服务快速双向同步RDS数据库：https://www.xmmup.com/shiyongoggweifuwukuaisushuangxiangtongburdsshujuku.html
- OGG用于跨云RDS for MySQL之间配置双主实时同步–OGG远程捕获和投递：https://www.xmmup.com/oggyongyukuayunrdszhijianpeizhishuangzhushishitongbuyuanchengbuhuohetoudi.html
- otter用于跨云RDS for mysql之间配置双主实时同步：https://www.xmmup.com/otteryongyukuayunrdszhijianpeizhishuangzhushishitongbu.html
- OGG用于PG数据库之间双主实时同步（RDS for PG亦可）–OGG远程捕获和投递：https://www.xmmup.com/oggyongyupgshujukuzhijianshuangzhushishitongburds-for-pgyikeoggyuanchengbuhuohetoudi.html
- 使用OGG for PG微服务快速双向同步RDS数据库（双主）：https://www.xmmup.com/shiyongogg-for-pgweifuwukuaisushuangxiangtongburdsshujukushuangzhu.html
- 使用阿里的开源工具otter，这个方案经过验证是可行的。阿里数据同步工具Otter和Canal简介请参考：https://www.xmmup.com/alishujutongbugongjuotterhecanaljianjie.html
- 使用OGG for Oracle微服务双向同步Oracle数据库搭建双主架构（含DDL）：https://www.xmmup.com/shiyongogg-for-oracleweifuwushuangxiangtongbuoracleshujukuhanddl.html
- 使用OGG for mysql微服务搭建双主架构（含DDL）：https://www.xmmup.com/shiyongogg-for-mysqlweifuwudajianshuangzhujiagouhanddl.html
- 使用数据泵基于flashback_scn+OGG微服务零停机迁移12c到19c：https://www.xmmup.com/shiyongshujubengjiyuflashback_scnoggweifuwulingtingjiqianyi12cdao19c.html
使用数据泵+OGG微服务
使用ogg 12.2 replicate参数DBOPTIONS ENABLE_INSTANTIATION_FILTERING（推荐）。从ogg 12.2开始通过设置replicate参数DBOPTIONS ENABLE_INSTANTIATION_FILTERING，可以使得OGG能够自动识别每张表导入后的csn，自动应用csn之后的数据，从而可以使导出数据不再需要flashback_scn号了。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 | -- 创建专用网络 docker network create --subnet=172.72.7.0/24 ora-network -- OGG微服务 docker pull lhrbest/ogg213maoracle:v1.0 docker rm -f lhrogg213maoracle docker run -d --name lhrogg213maoracle -h lhrogg213maoracle \ --net=ora-network --ip 172.72.7.25 \ -p 9391:3389 -p 29000-29005:9000-9005 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/ogg213maoracle:v1.0 \ /usr/sbin/init -- oracle 压测工具 docker pull lhrbest/lhrdbbench:1.0 docker rm -f lhrdbbench docker run -d --name lhrdbbench -h lhrdbbench \ --net=ora-network --ip 172.72.7.26 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrdbbench:1.0 \ /usr/sbin/init -- ora12c docker rm -f ora12c docker run -d --name ora12c -h ora12c \ -p 1554:1521 --net=ora-network --ip 172.72.7.4 \ --privileged=true \ lhrbest/oracle_12cr2_ee_lhr_12.2.0.1:2.0 init -- ora19c docker rm -f ora19c docker run -d --name ora19c -h ora19c \ -p 1555:1521 --net=ora-network --ip 172.72.7.5 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/oracle19clhr_rpm_db_12.2.0.3:2.0 \ /usr/sbin/init -- 数据库配置 1.开启数据库归档--如果没有开启 2.开启数据库级别附加日志--如果没有开始最小附加日志 3.开启强制日志--如果没有开启强制日志 4.设置ENABLE_GOLDENGATE_REPLICAT参数为TRUE 5.创建OGG用户包括包括源端用户、目标端用户以及OGG抽取用户 -- 2个库做配置 alter database add supplemental log data; alter database add supplemental log data (all) columns; alter database force logging; alter system set enable_goldengate_replication=TRUE; select name,supplemental_log_data_min , force_logging, log_mode from v$database; alter system set streams_pool_size = 128M; alter system set sga_max_size = 2g scope=spfile; alter system set sga_target = 2g scope=spfile; alter system set pga_aggregate_target=1g; startup force -- OGG管理用户 CREATE USER ogg identified by lhr; GRANT DBA to ogg; grant SELECT ANY DICTIONARY to ogg; GRANT EXECUTE ON SYS.DBMS_LOCK TO ogg; grant select any transaction to ogg; grant select any table to ogg; grant flashback any table to ogg; grant alter any table to ogg; exec dbms_goldengate_auth.grant_admin_privilege('OGG','*',TRUE); -- 业务用户 CREATE USER lhr identified by lhr; GRANT DBA to lhr ; grant SELECT ANY DICTIONARY to lhr; GRANT EXECUTE ON SYS.DBMS_LOCK TO lhr; -- 启动监听 lsnrctl start lsnrctl status -- 源端数据初始化 /usr/local/swingbench/bin/oewizard -s -create -c /usr/local/swingbench/wizardconfigs/oewizard.xml -create \ -version 2.0 -cs //172.72.7.4/lhrsdb -dba "sys as sysdba" -dbap lhr -dt thin \ -ts users -u lhr -p lhr -allindexes -scale 0.001 -tc 16 -v -cl col TABLE_NAME format a30 SELECT a.table_name,a.num_rows FROM dba_tables a where a.OWNER='LHR' ; select object_type,count(*) from dba_objects where owner='LHR' group by object_type; select object_type,status,count(*) from dba_objects where owner='LHR' group by object_type,status; select sum(bytes)/1024/1024 from dba_segments where owner='LHR'; -- 检查键是否正确：https://www.xmmup.com/ogg-01296-biaoyouzhujianhuoweiyijiandanshirengranshiyongquanbulielaijiexixing.html -- 否则OGG启动后，会报错：OGG-01296、OGG-06439、OGG-01169 Encountered an update where all key columns for target table LHR.ORDER_ITEMS are not present. select owner, constraint_name, constraint_type, status, validated from dba_constraints where owner='LHR' and VALIDATED='NOT VALIDATED'; select 'alter table lhr.'||TABLE_NAME||' enable validate constraint '||CONSTRAINT_NAME||';' from dba_constraints where owner='LHR' and VALIDATED='NOT VALIDATED'; -- 删除外键 SELECT 'ALTER TABLE LHR.'|| D.TABLE_NAME ||' DROP constraint '|| D.CONSTRAINT_NAME||';' FROM DBA_constraints d where owner='LHR' and d.CONSTRAINT_TYPE='R'; select count(*) from LHR.ORDER_ITEMS union all select count(*) from LHR.LOGON union all select count(*) from LHR.CUSTOMERS union all select count(*) from LHR.ORDERS union all select count(*) from LHR.PRODUCT_DESCRIPTIONS union all select count(*) from LHR.ORDERENTRY_METADATA union all select count(*) from LHR.CARD_DETAILS union all select count(*) from LHR.PRODUCT_INFORMATION union all select count(*) from LHR.ADDRESSES union all select count(*) from LHR.WAREHOUSES union all select count(*) from LHR.INVENTORIES ; |
访问：http://192.168.66.35:29000 ，用户名：oggadmin，密码：lhr
创建身份证明
访问：http://192.168.66.35:29001/
1 | ogg@172.72.7.4/lhrsdb |
目标端创建心跳表（脉动表）
创建脉动表（Heartbeattable）：
目标库查询：
1 2 3 4 5 6 7 8 9 10 11 | OGG@lhrsdb> col tname format a20 OGG@lhrsdb> select * from tab; TNAME TABTYPE CLUSTERID -------------------- -------------------------- ---------- GG_HEARTBEAT TABLE GG_HEARTBEAT_HISTORY TABLE GG_HEARTBEAT_SEED TABLE GG_LAG VIEW GG_LAG_HISTORY VIEW |
源端添加trandata和脉动表
查询验证：
源端查询：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | OGG@lhrsdb> col TABLE_OWNER format a12 OGG@lhrsdb> SELECT * FROM dba_capture_prepared_tables d where TABLE_OWNER='LHR'; TABLE_OWNER TABLE_NAME SCN TIMESTAMP SUPPLEME SUPPLEME SUPPLEME SUPPLEME ------------ ------------------------------ ---------- ------------------- -------- -------- -------- -------- LHR CUSTOMERS 1546665 2022-06-24 12:40:05 NO NO NO NO LHR ADDRESSES 1546659 2022-06-24 12:40:05 NO NO NO NO LHR CARD_DETAILS 1546662 2022-06-24 12:40:05 NO NO NO NO LHR WAREHOUSES 1546689 2022-06-24 12:40:05 NO NO NO NO LHR ORDER_ITEMS 1546680 2022-06-24 12:40:05 NO NO NO NO LHR ORDERS 1546677 2022-06-24 12:40:05 NO NO NO NO LHR INVENTORIES 1546668 2022-06-24 12:40:05 NO NO NO NO LHR PRODUCT_INFORMATION 1546686 2022-06-24 12:40:05 NO NO NO NO LHR LOGON 1546671 2022-06-24 12:40:05 NO NO NO NO LHR PRODUCT_DESCRIPTIONS 1546683 2022-06-24 12:40:05 NO NO NO NO LHR ORDERENTRY_METADATA 1546674 2022-06-24 12:40:05 NO NO NO NO 11 rows selected. -- select * from sys.streams$_prepare_object; |
配置ora12c到ora19c的实时同步
创建extract进程
数据目录：/ogg213c/ogg_deploy/var/lib/data/dirdat
1 2 3 4 5 6 7 | EXTRACT ext12c USERIDALIAS ora12c DOMAIN OGGMA DDL INCLUDE MAPPED DDLOPTIONS REPORT TRANLOGOPTIONS EXCLUDETAG 99 EXTTRAIL ./dirdat/e1 table LHR.*; |
创建replicate进程
1 2 3 4 5 6 | REPLICAT rep19c USERIDALIAS ora19c DOMAIN OGGMA DBOPTIONS ENABLE_INSTANTIATION_FILTERING DDL INCLUDE MAPPED DDLOPTIONS REPORT MAP LHR.*, TARGET LHR.*; |
这里，注意不要运行！！！因为目标端的数据还没有初始化完成，等用expdp导入完成后，再根据csn号进行启用！
impdp+network导入目标端数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | -- 让源端一直产生事务 /usr/local/swingbench/bin/charbench -c /usr/local/swingbench/configs/SOE_Server_Side_V2.xml \ -u lhr -p lhr -cs //172.72.7.4/lhrsdb -dt thin -uc 100 \ -a -v "users,tpm,tps,dml,cpu" \ -rr 5 -rt "00:30" -min 50 -max 50 -r "/tmp/test_lhrsdb.xml" -- 目标端 create directory d1 as '/home/oracle/'; grant all on directory d1 to public; create public database link DBL connect to system identified by lhr using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 172.72.7.4)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = lhrsdb )))'; impdp lhr/lhr@127.0.0.1/lhrsdb directory=D1 \ NETWORK_LINK=DBL exclude=statistics parallel=16 \ cluster=no schemas=LHR logtime=ALL \ TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y TABLE_EXISTS_ACTION=REPLACE -- 也可以使用expdp+impdp expdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=LHR.dmp SCHEMAS=LHR scp /opt/oracle/admin/lhrsdb/dpdump/LHR.dmp 172.72.7.5:/home/oracle/ conn lhr/lhr@127.0.0.1/pdb2 create directory d1 as '/home/oracle/'; grant all on directory d1 to public; impdp lhr/lhr@127.0.0.1/pdb2 DIRECTORY=D1 schemas=LHR dumpfile=LHR.dmp TABLE_EXISTS_ACTION=REPLACE |
导入完成后，查看目标库：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | alter PACKAGE "LHR"."ORDERENTRY" compile; alter PACKAGE "LHR"."ORDERENTRY" compile body; SYS@lhrsdb> col SOURCE_OBJECT_NAME format a30 SYS@lhrsdb> select source_object_name, instantiation_scn, ignore_scn from dba_apply_instantiated_objects; SOURCE_OBJECT_NAME INSTANTIATION_SCN IGNORE_SCN ------------------------------ ----------------- ---------- WAREHOUSES 2159700 0 ORDERENTRY_METADATA 2159687 0 PRODUCT_INFORMATION 2159670 0 PRODUCT_DESCRIPTIONS 2159652 0 ORDER_ITEMS 2159623 0 ORDERS 2159612 0 LOGON 2159584 0 CUSTOMERS 2159557 0 CARD_DETAILS 2159551 0 ADDRESSES 2159539 0 INVENTORIES 2158976 0 11 rows selected. |
后续启用replicate进程后，会自动从这些scn进行应用。
启用replicate进程
在启用之前，可以查询表数据，发现和源端数据量相差很大，因为源端一直在做压测产生新数据。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | LHR@ora12c> select count(*) from "LHR"."ORDERS"; COUNT(*) ---------- 13019 LHR@ora19c> select count(*) from "LHR"."ORDERS"; COUNT(*) ---------- 4562 su - oracle adminclient CONNECT http://127.0.0.1:9000 deployment deploy213 as oggadmin password lhr start REPLICAT REP19c |
数据已经实时同步了。
测试DDL和DML同步
在Oracle 12c端建表和插入数据：
1 2 3 4 5 6 7 8 9 10 11 | create table t1 (id number primary key,name varchar2(255)); insert into t1 select object_id,object_name from dba_objects where object_id<=20; commit; select count(*) from t1; LHR@lhrsdb> select count(8) from t1; COUNT(8) ---------- 19 |
从源端查看：
查看目标端：
可见，DDL和DML同步都已经完成！
配置ora19c到ora12c的实时同步
创建extract进程
1 2 3 4 5 6 7 | extract ext19c USERIDALIAS ora19c, DOMAIN OGGMA DDL INCLUDE MAPPED DDLOPTIONS REPORT TRANLOGOPTIONS EXCLUDETAG 99 exttrail ./dirdat/e2 table lhr.*; |
创建replicate进程
1 2 3 4 5 6 | REPLICAT rep12c USERIDALIAS ora12c DOMAIN OGGMA DDL INCLUDE MAPPED DDLOPTIONS REPORT DBOPTIONS SETTAG 99 MAP lhr.*, TARGET lhr.*; |
测试DDL和DML同步
在Oracle 21c操作：
1 2 3 4 | create table t2 (id number primary key,name varchar2(255)); insert into t2 select object_id,object_name from dba_objects where object_id<=20; commit; select count(8) from t2; |
可见，DDL和DML同步都已经完成！
反向同步也正常，可以作为回退方案！！！
OGG健康巡检
参考：https://www.xmmup.com/ogg-for-oraclejiankangxunjianguanfangjiaoben.html
1 2 3 4 5 | cd $OGG_HOME/lib/sql/healthcheck cd /ogg213c/ogg_ma/lib/sql/healthcheck sqlplus sys/lhr@172.72.7.4/lhrsdb as sysdba @ogghc_install.sql @ogghc_run.sql |
其它SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SELECT * FROM dba_apply_error; SELECT * FROM dba_apply_progress; SELECT * FROM dba_capture; SELECT * FROM dba_capture_prepared_schemas; -- 源端 SELECT * FROM dba_capture_prepared_tables d where TABLE_OWNER='LHR'; -- 目标端scn select source_object_name, instantiation_scn, ignore_scn from dba_apply_instantiated_objects; -- 捕获进程对数据库中表的支持级别的信息 SELECT * FROM dba_goldengate_support_mode d where d.owner='LHR'; -- 显示所有没有主索引和非空唯一索引的表 SELECT * FROM DBA_GOLDENGATE_NOT_UNIQUE d where d.owner='LHR'; |
总结
1、从ogg 12.2开始通过设置replicate参数DBOPTIONS ENABLE_INSTANTIATION_FILTERING，可以使得OGG能够自动识别每张表导入后的csn，自动应用csn之后的数据，从而可以使导出数据不再需要flashback_scn号了。
2、检查点表只在目标端配置，而且只针对经典的replicate配置，对于集成的replicat不需要配置，但是并行复制replicat需要在目标端配置检查点表！！！
