使用数据泵基于flashback_scn+OGG微服务零停机迁移12c到19c
👉 本文共约3848个字，系统预计阅读时间或需15分钟。
类似文章
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
使用数据泵+OGG微服务
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 | -- 创建专用网络 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 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 '|| D.TABLE_NAME ||' DROP constraint '|| D.CONSTRAINT_NAME||';' FROM user_constraints d where d.CONSTRAINT_TYPE='R'; |
-s是静默，-create表示创建数据，-c是指定配置文件，就是oewizard.xml，-version指定swingbench版本，-cs表示数据库连接串，-dba表示连接到oracle数据库的用户，-dbap表示连接到oracle数据库的密码，-dt表示连接类型，-ts是指定表空间为users, -u表示压测的用户，-p表示压测用户的密码，-scale是数据量，1就是1G，0.001就是1Mb数据，-tc 是并发度，-allindexes是支持所有的索引类型，-v显示详细信息，-cl表示运行在字符模式。
源端数据初始化日志：
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 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 | [root@docker35 ~]# docker exec -it lhrdbbench bash [root@lhrdbbench /]# /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 Connecting to : jdbc:oracle:thin:@//172.72.7.4/lhrsdb Connected Starting run Starting script ../sql/soedgdrop2.sql Script completed in 0 hour(s) 0 minute(s) 0 second(s) 541 millisecond(s) Starting script ../sql/soedgcreatetables2.sql Script completed in 0 hour(s) 0 minute(s) 0 second(s) 514 millisecond(s) Starting script ../sql/soedgviews.sql Script completed in 0 hour(s) 0 minute(s) 0 second(s) 62 millisecond(s) Starting script ../sql/soedgsqlset.sql Script completed in 0 hour(s) 0 minute(s) 0 second(s) 217 millisecond(s) Inserting data into table PRODUCT_INFORMATION Inserting data into table PRODUCT_DESCRIPTIONS Inserting data into table INVENTORIES Inserting data into table WAREHOUSES Inserting data into table ADDRESSES_16 Inserting data into table ADDRESSES_10 Inserting data into table ADDRESSES_7 Inserting data into table ADDRESSES_11 Inserting data into table ADDRESSES_14 Inserting data into table ADDRESSES_8 Inserting data into table ADDRESSES_15 Inserting data into table ADDRESSES_2 Inserting data into table ADDRESSES_1 Inserting data into table ADDRESSES_9 Inserting data into table ADDRESSES_13 Inserting data into table ADDRESSES_3 Completed processing table ADDRESSES_9 in 0:00:11 Completed processing table ADDRESSES_14 in 0:00:11 Completed processing table ADDRESSES_11 in 0:00:11 Completed processing table ADDRESSES_13 in 0:00:11 Completed processing table ADDRESSES_15 in 0:00:11 Completed processing table ADDRESSES_8 in 0:00:11 Completed processing table ADDRESSES_2 in 0:00:11 Completed processing table ADDRESSES_1 in 0:00:11 Inserting data into table ORDERS_16 Inserting data into table ORDER_ITEMS_1335 Inserting data into table ADDRESSES_5 Inserting data into table CUSTOMERS_16 Inserting data into table ADDRESSES_4 Inserting data into table ADDRESSES_12 Completed processing table ADDRESSES_10 in 0:00:11 Inserting data into table ADDRESSES_6 Inserting data into table CUSTOMERS_1 Completed processing table ADDRESSES_7 in 0:00:11entage completed : 12.60 Inserting data into table CUSTOMERS_2 Inserting data into table CUSTOMERS_5 Completed processing table ADDRESSES_3 in 0:00:11 Inserting data into table CUSTOMERS_9 Completed processing table ADDRESSES_16 in 0:00:11 Inserting data into table CUSTOMERS_7 Completed processing table WAREHOUSES in 0:00:11 Inserting data into table CUSTOMERS_6 Completed processing table ADDRESSES_4 in 0:00:00 Inserting data into table CUSTOMERS_12 Completed processing table ADDRESSES_5 in 0:00:00 Completed processing table ADDRESSES_6 in 0:00:00 Inserting data into table CUSTOMERS_4 Inserting data into table CUSTOMERS_15 Completed processing table ADDRESSES_12 in 0:00:00 Inserting data into table CUSTOMERS_13 Completed processing table CUSTOMERS_5 in 0:00:00 Inserting data into table CUSTOMERS_8 Completed processing table CUSTOMERS_15 in 0:00:00 Inserting data into table CUSTOMERS_3 Completed processing table CUSTOMERS_7 in 0:00:00 Inserting data into table CUSTOMERS_10 Completed processing table CUSTOMERS_3 in 0:00:00 Inserting data into table CUSTOMERS_14 Completed processing table CUSTOMERS_10 in 0:00:00 Inserting data into table CUSTOMERS_11 Completed processing table CUSTOMERS_8 in 0:00:00 Inserting data into table ORDER_ITEMS_801 Inserting data into table ORDERS_10 Completed processing table CUSTOMERS_14 in 0:00:00 Completed processing table CUSTOMERS_9 in 0:00:00 Inserting data into table ORDERS_7 Inserting data into table ORDER_ITEMS_534 Completed processing table CUSTOMERS_12 in 0:00:00 Completed processing table CUSTOMERS_2 in 0:00:00 Inserting data into table ORDER_ITEMS_1068 Completed processing table CUSTOMERS_1 in 0:00:00 Inserting data into table ORDER_ITEMS_356 Completed processing table CUSTOMERS_11 in 0:00:00 Completed processing table CUSTOMERS_13 in 0:00:00 Completed processing table CUSTOMERS_4 in 0:00:00 Completed processing table CUSTOMERS_16 in 0:00:00 Inserting data into table ORDER_ITEMS_712 Inserting data into table ORDERS_9 Inserting data into table ORDER_ITEMS_0 Inserting data into table ORDERS_5 Completed processing table CUSTOMERS_6 in 0:00:00 Inserting data into table ORDERS_1 Inserting data into table ORDERS_13 Completed processing table PRODUCT_INFORMATION in 0:00:12 Inserting data into table ORDER_ITEMS_178 Inserting data into table ORDERS_3 Completed processing table ORDER_ITEMS_534 in 0:00:00ge completed : 45.57 Completed processing table ORDER_ITEMS_712 in 0:00:00 Completed processing table ORDER_ITEMS_0 in 0:00:00 Inserting data into table ORDER_ITEMS_890 Inserting data into table ORDERS_11 Completed processing table ORDER_ITEMS_356 in 0:00:00 Inserting data into table ORDER_ITEMS_267 Inserting data into table ORDERS_4 Completed processing table ORDER_ITEMS_1068 in 0:00:00 Completed processing table ORDER_ITEMS_1335 in 0:00:00 Completed processing table ORDER_ITEMS_178 in 0:00:00 Inserting data into table ORDER_ITEMS_89 Inserting data into table ORDERS_2 Completed processing table ORDER_ITEMS_801 in 0:00:00 Completed processing table ORDER_ITEMS_890 in 0:00:00 Inserting data into table ORDER_ITEMS_1157 Inserting data into table ORDERS_14 Completed processing table PRODUCT_DESCRIPTIONS in 0:00:12 Completed processing table ORDERS_2 in 0:00:00 Inserting data into table ORDER_ITEMS_1246 Inserting data into table ORDERS_15 Completed processing table ORDERS_4 in 0:00:00 Completed processing table ORDERS_10 in 0:00:00 Completed processing table ORDERS_14 in 0:00:00 Inserting data into table ORDER_ITEMS_979 Inserting data into table ORDER_ITEMS_623 Inserting data into table ORDERS_12 Completed processing table ORDERS_11 in 0:00:00 Completed processing table ORDERS_9 in 0:00:00 Inserting data into table ORDERS_8 Inserting data into table ORDER_ITEMS_445 Completed processing table ORDERS_13 in 0:00:00 Inserting data into table ORDERS_6 Completed processing table ORDER_ITEMS_267 in 0:00:00 Completed processing table ORDERS_5 in 0:00:00 Inserting data into table CARD_DETAILS_16 Completed processing table ORDERS_3 in 0:00:00 Inserting data into table CARD_DETAILS_13 Completed processing table ORDERS_7 in 0:00:00 Inserting data into table CARD_DETAILS_11 Completed processing table ORDERS_16 in 0:00:00 Inserting data into table CARD_DETAILS_15 Completed processing table ORDERS_8 in 0:00:00 Inserting data into table CARD_DETAILS_10 Completed processing table ORDERS_1 in 0:00:00 Inserting data into table CARD_DETAILS_1 Completed processing table ORDERS_12 in 0:00:00 Inserting data into table CARD_DETAILS_2 Completed processing table ORDERS_15 in 0:00:00 Inserting data into table CARD_DETAILS_3 Completed processing table ORDERS_6 in 0:00:00 Inserting data into table CARD_DETAILS_5 Completed processing table ORDER_ITEMS_89 in 0:00:00 Inserting data into table CARD_DETAILS_8 Completed processing table ORDER_ITEMS_1157 in 0:00:00 Inserting data into table CARD_DETAILS_6 Completed processing table ORDER_ITEMS_445 in 0:00:00 Inserting data into table CARD_DETAILS_14 Completed processing table ORDER_ITEMS_1246 in 0:00:00 Completed processing table ORDER_ITEMS_623 in 0:00:00 Inserting data into table CARD_DETAILS_7 Inserting data into table CARD_DETAILS_12 Completed processing table ORDER_ITEMS_979 in 0:00:00 Inserting data into table CARD_DETAILS_9 Completed processing table CARD_DETAILS_7 in 0:00:00 Inserting data into table CARD_DETAILS_4 Completed processing table CARD_DETAILS_12 in 0:00:00 Completed processing table CARD_DETAILS_10 in 0:00:00 Completed processing table CARD_DETAILS_2 in 0:00:00 Inserting data into table LOGON_1 Inserting data into table LOGON_16 Inserting data into table LOGON_6 Completed processing table CARD_DETAILS_15 in 0:00:00 Completed processing table CARD_DETAILS_5 in 0:00:00 Inserting data into table LOGON_11 Completed processing table CARD_DETAILS_3 in 0:00:00 Inserting data into table LOGON_12 Completed processing table CARD_DETAILS_9 in 0:00:00 Inserting data into table LOGON_13 Inserting data into table LOGON_14 Completed processing table CARD_DETAILS_14 in 0:00:00 Inserting data into table LOGON_10 Completed processing table CARD_DETAILS_1 in 0:00:00 Inserting data into table LOGON_8 Completed processing table CARD_DETAILS_8 in 0:00:00 Inserting data into table LOGON_5 Completed processing table CARD_DETAILS_4 in 0:00:00 Inserting data into table LOGON_3 Completed processing table CARD_DETAILS_6 in 0:00:00 Inserting data into table LOGON_4 Completed processing table CARD_DETAILS_13 in 0:00:00 Completed processing table CARD_DETAILS_16 in 0:00:00 Inserting data into table LOGON_9 Inserting data into table LOGON_2 Completed processing table CARD_DETAILS_11 in 0:00:00 Inserting data into table LOGON_15 Completed processing table LOGON_3 in 0:00:00 Completed processing table LOGON_1 in 0:00:00 Completed processing table LOGON_15 in 0:00:00 Completed processing table LOGON_2 in 0:00:00 Completed processing table LOGON_10 in 0:00:00 Completed processing table LOGON_12 in 0:00:00 Completed processing table LOGON_5 in 0:00:00 Inserting data into table LOGON_7 Completed processing table LOGON_9 in 0:00:00 Completed processing table LOGON_11 in 0:00:00 Completed processing table LOGON_8 in 0:00:00 Completed processing table LOGON_4 in 0:00:00 Completed processing table LOGON_16 in 0:00:00 Completed processing table LOGON_13 in 0:00:00 Completed processing table LOGON_7 in 0:00:00 Completed processing table LOGON_6 in 0:00:00 Completed processing table LOGON_14 in 0:00:00 Connection cache closed Starting script ../sql/soedganalyzeschema2.sql Script completed in 0 hour(s) 0 minute(s) 9 second(s) 371 millisecond(s) Starting script ../sql/soedgconstraints2.sql Script completed in 0 hour(s) 0 minute(s) 15 second(s) 303 millisecond(s) Starting script ../sql/soedgindexes2.sql Script completed in 0 hour(s) 0 minute(s) 18 second(s) 168 millisecond(s) Starting script ../sql/soedgsequences2.sql Script completed in 0 hour(s) 0 minute(s) 0 second(s) 267 millisecond(s) Starting script ../sql/soedgpackage2_header.sql Script completed in 0 hour(s) 0 minute(s) 0 second(s) 325 millisecond(s) Starting script ../sql/soedgpackage2_body.sql Script completed in 0 hour(s) 0 minute(s) 0 second(s) 286 millisecond(s) Starting script ../sql/soedgsetupmetadata.sql Script completed in 0 hour(s) 0 minute(s) 1 second(s) 61 millisecond(s) ============================================ | Datagenerator Run Stats | ============================================ Connection Time 0:00:00.006 Data Generation Time 0:00:25.528 DDL Creation Time 0:00:46.255 Total Run Time 0:01:11.795 Rows Inserted per sec 35,959 Data Generated (MB) per sec 0.9 Actual Rows Generated 917,133 Commits Completed 227 Batch Updates Completed 4,725 Connecting to : jdbc:oracle:thin:@//172.72.7.4/lhrsdb Connected Post Creation Validation Report =============================== The schema appears to have been created successfully. Valid Objects ============= Valid Tables : 'ORDERS','ORDER_ITEMS','CUSTOMERS','WAREHOUSES','ORDERENTRY_METADATA','INVENTORIES','PRODUCT_INFORMATION','PRODUCT_DESCRIPTIONS','ADDRESSES','CARD_DETAILS' Valid Indexes : 'PRD_DESC_PK','PROD_NAME_IX','PRODUCT_INFORMATION_PK','PROD_SUPPLIER_IX','PROD_CATEGORY_IX','INVENTORY_PK','INV_PRODUCT_IX','INV_WAREHOUSE_IX','ORDER_PK','ORD_SALES_REP_IX','ORD_CUSTOMER_IX','ORD_ORDER_DATE_IX','ORD_WAREHOUSE_IX','ORDER_ITEMS_PK','ITEM_ORDER_IX','ITEM_PRODUCT_IX','WAREHOUSES_PK','WHS_LOCATION_IX','CUSTOMERS_PK','CUST_EMAIL_IX','CUST_ACCOUNT_MANAGER_IX','CUST_FUNC_LOWER_NAME_IX','ADDRESS_PK','ADDRESS_CUST_IX','CARD_DETAILS_PK','CARDDETAILS_CUST_IX' Valid Views : 'PRODUCTS','PRODUCT_PRICES' Valid Sequences : 'CUSTOMER_SEQ','ORDERS_SEQ','ADDRESS_SEQ','LOGON_SEQ','CARD_DETAILS_SEQ' Valid Code : 'ORDERENTRY' Schema Created LHR@lhrsdb> col TABLE_NAME format a30 LHR@lhrsdb> SELECT a.table_name,a.num_rows FROM dba_tables a where a.OWNER='LHR' ; TABLE_NAME NUM_ROWS ------------------------------ ---------- ADDRESSES 1500 CUSTOMERS 1000 CARD_DETAILS 1500 WAREHOUSES 1000 ORDER_ITEMS 7141 ORDERS 1430 INVENTORIES 899179 PRODUCT_INFORMATION 1000 LOGON 2383 PRODUCT_DESCRIPTIONS 1000 ORDERENTRY_METADATA 0 11 rows selected. LHR@lhrsdb> LHR@lhrsdb> select object_type,count(*) from dba_objects where owner='LHR' group by object_type; OBJECT_TYPE COUNT(*) ----------------------- ---------- SEQUENCE 5 PACKAGE 1 PACKAGE BODY 1 TABLE 11 VIEW 2 INDEX 27 6 rows selected. LHR@lhrsdb> LHR@lhrsdb> select sum(bytes)/1024/1024 from dba_segments where owner='LHR'; SUM(BYTES)/1024/1024 -------------------- 279.5625 |
访问：http://192.168.66.35:29000 ，用户名：oggadmin，密码：lhr
创建身份证明
访问：http://192.168.66.35:29001/
目标端创建检查点表和心跳表（脉动表）
在目标端ora19c中，创建检查点表ogg.ckpt
创建脉动表（Heartbeattable）：
目标库查询：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | OGG@lhrsdb> col tname format a20 OGG@lhrsdb> select * from tab; TNAME TABTYPE CLUSTERID -------------------- -------------------------- ---------- CKPT TABLE CKPT_LOX TABLE GG_HEARTBEAT TABLE GG_HEARTBEAT_HISTORY TABLE GG_HEARTBEAT_SEED TABLE GG_LAG VIEW GG_LAG_HISTORY VIEW 7 rows selected. |
源端添加trandata和脉动表
查询验证：
配置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 | REPLICAT rep19c USERIDALIAS ora19c DOMAIN OGGMA 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 29 30 31 32 33 34 35 36 37 38 39 | -- 让源端一直产生事务 /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" -- 选择最小的scn号 SELECT TO_CHAR(MIN(start_scn) ) AS "Please select the minimum SCN" FROM v$transaction UNION ALL SELECT TO_CHAR(current_scn) FROM v$database; Please select the minimum SCN ---------------------------------------- 2410272 2410467 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 )))'; -- 这里的flashback_scn选择最小的scn号 impdp lhr/lhr@127.0.0.1/lhrsdb directory=D1 \ NETWORK_LINK=DBL flashback_scn=2410272 exclude=statistics parallel=12 \ cluster=no schemas=LHR logfile=impdp_dbl.log 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 FLASHBACK_SCN=2410272 scp /opt/oracle/admin/lhrsdb/dpdump/LHR.dmp 172.72.7.21:/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 |
参数介绍：
-c 使用配置文件：SOE_Server_Side_V2.xml
-cs 指定数据库连接字符串：//172.72.7.20/lhrsdb
-a 自动运行
-v 显示运行统计值（使用逗号分隔，不能有空格）,就是对应的指标
- Users：用户数;
- Tpm：每分钟事务数;
- Tps：每秒事务数;
- Trans：事务信息，对应swingbench的Transactions页签中的Short Name
- Dml：包括：查询、插入、更新、删除、提交
-r 指定测试结果文件名称，默认为results.xml
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！-rr 控制台刷新显示频率（单位：秒），默认每秒显示一次；
-rt 设置负载运行时间，格式：hh:mm.sec，默认一直运行;
-min
设置内部事务最小思考时间；Min. Intra Delay Within Transactions(ms)，线程思考时间（暂停时间），用于模拟实际用户操作。 -max
设置内部事务最大思考时间；Min. Intra Delay Within Transactions(ms)，线程思考时间（暂停时间），用于模拟实际用户操作。 -vo 输出测试结果到文件，默认输出到控制台；
-env 打印出环境变量的信息
-bg是后台运行
执行过程：
基于csn号启用replicate进程
在启用之前，可以查询表数据，发现和源端数据量相差很大，因为源端一直在做压测产生新数据。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | LHR@ora12c> select count(*) from "LHR"."ORDERS"; COUNT(*) ---------- 53051 LHR@ora19c> select count(*) from "LHR"."ORDERS"; COUNT(*) ---------- 47436 su - oracle adminclient CONNECT http://127.0.0.1:9000 deployment deploy213 as oggadmin password lhr start REPLICAT REP19c , AFTERCSN 2410272 |
注意：这里的scn使用最小的scn号
数据已经实时同步了。
测试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 |
报错
若源端事务量很大，例如本文的压测，则replicate应用的时候有一定几率会报错：
WARNING OGG-01154 SQL error 1403 mapping source table LHR.ORDERS to target tableLHR.ORDERS. Database error: No data found.
WARNING OGG-02544 Unhandled error (ORA-26787: The row with key ("ORDER_ID") = (3245) does not exist in table LHR.ORDERS ORA-01403: no data found) while processing the record at SEQNO 0, RBA 11148203 in Integrated mode. REPLICAT will retry in Direct mode.
ERROR OGG-01296 Error mapping from LHR.ORDERS to LHR.ORDERS.
ERROR OGG-00664 OCI Error ORA-26804: Apply "OGG$REP19C" is disabled. (status = 26804-Sending commit command to database inbound server, 'OGG$REP19C').
解决：
1、尽量选择数据库无事务的时候的scn号或业务低的时候进行导出，一般是半夜进行
2、可以尝试将scn号往后倒退一些
3、可以尝试参数HANDLECOLLISIONS，但可能会导致数据不一致
4、查询源端和目标端所有的表是否存在外键关系，我的环境就是因为存在外键导致以上的报错（心累，这个排查花了好几天。。。）。
1 2 3 4 | SELECT 'ALTER TABLE '|| D.TABLE_NAME ||' DROP constraint '|| D.CONSTRAINT_NAME||';' FROM user_constraints d where d.CONSTRAINT_TYPE='R'; ALTER TABLE ORDERS DROP constraint ORDERS_CUSTOMER_ID_FK; |
5、使用ogg 12.2 replicate参数DBOPTIONS ENABLE_INSTANTIATION_FILTERING（推荐）。从ogg 12.2开始通过设置replicate参数DBOPTIONS ENABLE_INSTANTIATION_FILTERING，可以使得OGG能够自动识别每张表导入后的csn，自动应用csn之后的数据，从而可以使导出数据不再需要flashback_scn号了。
其它SQL
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 | select count(*) from ORDER_ITEMS union all select count(*) from LOGON union all select count(*) from CUSTOMERS union all select count(*) from ORDERS union all select count(*) from PRODUCT_DESCRIPTIONS union all select count(*) from ORDERENTRY_METADATA union all select count(*) from CARD_DETAILS union all select count(*) from PRODUCT_INFORMATION union all select count(*) from ADDRESSES union all select count(*) from WAREHOUSES union all select count(*) from INVENTORIES ; 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; |
