合 使用数据泵+OGG微服务新参数ENABLE_INSTANTIATION_FILTERING零停机迁移12c到19c
Tags： OracleOGG数据迁移数据泵OGG微服务零停机ENABLE_INSTANTIATION_FILTERING
👉 本文共约2803个字，系统预计阅读时间或需11分钟。
目录
类似文章
OGG有传统的经典架构，也有最新的微服务，2个都可以远程捕获和应用数据，对数据库服务器是0侵入，而传统的经典架构是纯命令行模式，最新的微服务架构是图形化界面操作，几乎所有操作都可以在界面进行。相关文章可以参考：
- 使用OGG for MySQL微服务快速双向同步RDS数据库：https://www.dbaup.com/shiyongoggweifuwukuaisushuangxiangtongburdsshujuku.html
- OGG用于跨云RDS for MySQL之间配置双主实时同步–OGG远程捕获和投递：https://www.dbaup.com/oggyongyukuayunrdszhijianpeizhishuangzhushishitongbuyuanchengbuhuohetoudi.html
- otter用于跨云RDS for mysql之间配置双主实时同步：https://www.dbaup.com/otteryongyukuayunrdszhijianpeizhishuangzhushishitongbu.html
- OGG用于PG数据库之间双主实时同步（RDS for PG亦可）–OGG远程捕获和投递：https://www.dbaup.com/oggyongyupgshujukuzhijianshuangzhushishitongburds-for-pgyikeoggyuanchengbuhuohetoudi.html
- 使用OGG for PG微服务快速双向同步RDS数据库（双主）：https://www.dbaup.com/shiyongogg-for-pgweifuwukuaisushuangxiangtongburdsshujukushuangzhu.html
- 使用阿里的开源工具otter，这个方案经过验证是可行的。阿里数据同步工具Otter和Canal简介请参考：https://www.dbaup.com/alishujutongbugongjuotterhecanaljianjie.html
- 使用OGG for Oracle微服务双向同步Oracle数据库搭建双主架构（含DDL）：https://www.dbaup.com/shiyongogg-for-oracleweifuwushuangxiangtongbuoracleshujukuhanddl.html
- 使用OGG for mysql微服务搭建双主架构（含DDL）：https://www.dbaup.com/shiyongogg-for-mysqlweifuwudajianshuangzhujiagouhanddl.html
- 使用数据泵基于flashback_scn+OGG微服务零停机迁移12c到19c：https://www.dbaup.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号了。
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.dbaup.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 |
导入完成后，查看目标库：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
