使用数据泵基于flashback_scn+OGG微服务零停机迁移12c到19c（双主）
使用数据泵基于flashback_scn+OGG微服务零停机迁移12c到19c（双主）
小麦苗DBA宝典
发布于 2022-11-07 19:20:12
发布于 2022-11-07 19:20:12
类似文章
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微服务
-- 创建专用网络
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 : -p -29005:-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 : --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 : --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 = M;
alter system set sga_max_size = g scope=spfile;
alter system set sga_target = g scope=spfile;
alter system set pga_aggregate_target=g;
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 -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)// 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表示运行在字符模式。
源端数据初始化日志：
[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 -v -cl
Connecting to : jdbc:oracle:thin:@//172.72.7.4/lhrsdb
Connected
Starting run
Starting script ../sql/soedgdrop2.sql
Script completed in hour(s) minute(s) second(s) millisecond(s)
Starting script ../sql/soedgcreatetables2.sql
Script completed in hour(s) minute(s) second(s) millisecond(s)
Starting script ../sql/soedgviews.sql
Script completed in hour(s) minute(s) second(s) millisecond(s)
Starting script ../sql/soedgsqlset.sql
Script completed in hour(s) minute(s) second(s) 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 ::
Completed processing table ADDRESSES_14 in ::
Completed processing table ADDRESSES_11 in ::
Completed processing table ADDRESSES_13 in ::
Completed processing table ADDRESSES_15 in ::
Completed processing table ADDRESSES_8 in ::
Completed processing table ADDRESSES_2 in ::
Completed processing table ADDRESSES_1 in ::
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 ::
Inserting data into table ADDRESSES_6
Inserting data into table CUSTOMERS_1
Completed processing table ADDRESSES_7 in ::entage completed : 12.60
Inserting data into table CUSTOMERS_2
Inserting data into table CUSTOMERS_5
Completed processing table ADDRESSES_3 in ::
Inserting data into table CUSTOMERS_9
Completed processing table ADDRESSES_16 in ::
Inserting data into table CUSTOMERS_7
Completed processing table WAREHOUSES in ::
Inserting data into table CUSTOMERS_6
Completed processing table ADDRESSES_4 in ::
Inserting data into table CUSTOMERS_12
Completed processing table ADDRESSES_5 in ::
Completed processing table ADDRESSES_6 in ::
Inserting data into table CUSTOMERS_4
Inserting data into table CUSTOMERS_15
Completed processing table ADDRESSES_12 in ::
Inserting data into table CUSTOMERS_13
Completed processing table CUSTOMERS_5 in ::
Inserting data into table CUSTOMERS_8
Completed processing table CUSTOMERS_15 in ::
Inserting data into table CUSTOMERS_3
Completed processing table CUSTOMERS_7 in ::
Inserting data into table CUSTOMERS_10
Completed processing table CUSTOMERS_3 in ::
Inserting data into table CUSTOMERS_14
Completed processing table CUSTOMERS_10 in ::
Inserting data into table CUSTOMERS_11
Completed processing table CUSTOMERS_8 in ::
Inserting data into table ORDER_ITEMS_801
Inserting data into table ORDERS_10
Completed processing table CUSTOMERS_14 in ::
Completed processing table CUSTOMERS_9 in ::
Inserting data into table ORDERS_7
Inserting data into table ORDER_ITEMS_534
Completed processing table CUSTOMERS_12 in ::
Completed processing table CUSTOMERS_2 in ::
Inserting data into table ORDER_ITEMS_1068
Completed processing table CUSTOMERS_1 in ::
Inserting data into table ORDER_ITEMS_356
Completed processing table CUSTOMERS_11 in ::
Completed processing table CUSTOMERS_13 in ::
Completed processing table CUSTOMERS_4 in ::
Completed processing table CUSTOMERS_16 in ::
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 ::
Inserting data into table ORDERS_1
Inserting data into table ORDERS_13
Completed processing table PRODUCT_INFORMATION in ::
Inserting data into table ORDER_ITEMS_178
Inserting data into table ORDERS_3
Completed processing table ORDER_ITEMS_534 in ::ge completed : 45.57
Completed processing table ORDER_ITEMS_712 in ::
Completed processing table ORDER_ITEMS_0 in ::
Inserting data into table ORDER_ITEMS_890
Inserting data into table ORDERS_11
Completed processing table ORDER_ITEMS_356 in ::
Inserting data into table ORDER_ITEMS_267
Inserting data into table ORDERS_4
Completed processing table ORDER_ITEMS_1068 in ::
Completed processing table ORDER_ITEMS_1335 in ::
Completed processing table ORDER_ITEMS_178 in ::
Inserting data into table ORDER_ITEMS_89
Inserting data into table ORDERS_2
Completed processing table ORDER_ITEMS_801 in ::
Completed processing table ORDER_ITEMS_890 in ::
Inserting data into table ORDER_ITEMS_1157
Inserting data into table ORDERS_14
Completed processing table PRODUCT_DESCRIPTIONS in ::
Completed processing table ORDERS_2 in ::
Inserting data into table ORDER_ITEMS_1246
Inserting data into table ORDERS_15
Completed processing table ORDERS_4 in ::
Completed processing table ORDERS_10 in ::
Completed processing table ORDERS_14 in ::
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 ::
Completed processing table ORDERS_9 in ::
Inserting data into table ORDERS_8
Inserting data into table ORDER_ITEMS_445
Completed processing table ORDERS_13 in ::
Inserting data into table ORDERS_6
Completed processing table ORDER_ITEMS_267 in ::
Completed processing table ORDERS_5 in ::
Inserting data into table CARD_DETAILS_16
Completed processing table ORDERS_3 in ::
Inserting data into table CARD_DETAILS_13
Completed processing table ORDERS_7 in ::
Inserting data into table CARD_DETAILS_11
Completed processing table ORDERS_16 in ::
Inserting data into table CARD_DETAILS_15
Completed processing table ORDERS_8 in ::
Inserting data into table CARD_DETAILS_10
Completed processing table ORDERS_1 in ::
Inserting data into table CARD_DETAILS_1
Completed processing table ORDERS_12 in ::
Inserting data into table CARD_DETAILS_2
Completed processing table ORDERS_15 in ::
Inserting data into table CARD_DETAILS_3
Completed processing table ORDERS_6 in ::
Inserting data into table CARD_DETAILS_5
Completed processing table ORDER_ITEMS_89 in ::
Inserting data into table CARD_DETAILS_8
Completed processing table ORDER_ITEMS_1157 in ::
Inserting data into table CARD_DETAILS_6
Completed processing table ORDER_ITEMS_445 in ::
Inserting data into table CARD_DETAILS_14
Completed processing table ORDER_ITEMS_1246 in ::
Completed processing table ORDER_ITEMS_623 in ::
Inserting data into table CARD_DETAILS_7
Inserting data into table CARD_DETAILS_12
Completed processing table ORDER_ITEMS_979 in ::
Inserting data into table CARD_DETAILS_9
Completed processing table CARD_DETAILS_7 in ::
Inserting data into table CARD_DETAILS_4
Completed processing table CARD_DETAILS_12 in ::
Completed processing table CARD_DETAILS_10 in ::
Completed processing table CARD_DETAILS_2 in ::
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 ::
Completed processing table CARD_DETAILS_5 in ::
Inserting data into table LOGON_11
Completed processing table CARD_DETAILS_3 in ::
Inserting data into table LOGON_12
Completed processing table CARD_DETAILS_9 in ::
Inserting data into table LOGON_13
Inserting data into table LOGON_14
Completed processing table CARD_DETAILS_14 in ::
Inserting data into table LOGON_10
Completed processing table CARD_DETAILS_1 in ::
Inserting data into table LOGON_8
Completed processing table CARD_DETAILS_8 in ::
Inserting data into table LOGON_5
Completed processing table CARD_DETAILS_4 in ::
Inserting data into table LOGON_3
Completed processing table CARD_DETAILS_6 in ::
Inserting data into table LOGON_4
Completed processing table CARD_DETAILS_13 in ::
Completed processing table CARD_DETAILS_16 in ::
Inserting data into table LOGON_9
Inserting data into table LOGON_2
Completed processing table CARD_DETAILS_11 in ::
Inserting data into table LOGON_15
Completed processing table LOGON_3 in ::
Completed processing table LOGON_1 in ::
Completed processing table LOGON_15 in ::
Completed processing table LOGON_2 in ::
Completed processing table LOGON_10 in ::
Completed processing table LOGON_12 in ::
Completed processing table LOGON_5 in ::
Inserting data into table LOGON_7
Completed processing table LOGON_9 in ::
Completed processing table LOGON_11 in ::
Completed processing table LOGON_8 in ::
Completed processing table LOGON_4 in ::
Completed processing table LOGON_16 in ::
Completed processing table LOGON_13 in ::
Completed processing table LOGON_7 in ::
Completed processing table LOGON_6 in ::
Completed processing table LOGON_14 in ::
Connection cache closed
Starting script ../sql/soedganalyzeschema2.sql
Script completed in hour(s) minute(s) second(s) millisecond(s)
Starting script ../sql/soedgconstraints2.sql
Script completed in hour(s) minute(s) second(s) millisecond(s)
Starting script ../sql/soedgindexes2.sql
Script completed in hour(s) minute(s) second(s) millisecond(s)
Starting script ../sql/soedgsequences2.sql
Script completed in hour(s) minute(s) second(s) millisecond(s)
Starting script ../sql/soedgpackage2_header.sql
Script completed in hour(s) minute(s) second(s) millisecond(s)
Starting script ../sql/soedgpackage2_body.sql
Script completed in hour(s) minute(s) second(s) millisecond(s)
Starting script ../sql/soedgsetupmetadata.sql
Script completed in hour(s) minute(s) second(s) millisecond(s)
============================================
| Datagenerator Run Stats |
============================================
Connection Time ::00.006
Data Generation Time ::25.528
DDL Creation Time ::46.255
Total Run Time ::11.795
Rows Inserted per sec ,
Data Generated (MB) per sec 0.9
Actual Rows Generated ,
Commits Completed
Batch Updates Completed ,
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
CUSTOMERS
CARD_DETAILS
WAREHOUSES
ORDER_ITEMS
ORDERS
INVENTORIES
PRODUCT_INFORMATION
LOGON
PRODUCT_DESCRIPTIONS
ORDERENTRY_METADATA
rows selected.
LHR@lhrsdb>
LHR@lhrsdb> select object_type,count(*) from dba_objects where owner='LHR' group by object_type;
OBJECT_TYPE COUNT(*)
----------------------- ----------
SEQUENCE
PACKAGE
PACKAGE BODY
TABLE
VIEW
INDEX
rows selected.
LHR@lhrsdb>
LHR@lhrsdb> select sum(bytes)// from dba_segments where owner='LHR';
SUM(BYTES)//
--------------------
279.5625
访问：http://192.168.66.35:29000 ，用户名：oggadmin，密码：lhr
创建身份证明
访问：http://192.168.66.35:29001/
目标端创建检查点表和心跳表（脉动表）
在目标端ora19c中，创建检查点表ogg.ckpt
创建脉动表（Heartbeattable）：
目标库查询：
OGG@lhrsdb> col tname format a2
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
rows selected.
源端添加trandata和脉动表
image-20220623150718680
查询验证：
image-20220623150802600
配置ora12c到ora19c的实时同步
创建extract进程
数据目录：/ogg213c/ogg_deploy/var/lib/data/dirdat
EXTRACT ext12c
USERIDALIAS ora12c DOMAIN OGGMA
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
TRANLOGOPTIONS EXCLUDETAG 99
EXTTRAIL ./dirdat/e1
table LHR.*;
创建replicate进程
REPLICAT rep19c
USERIDALIAS ora19c DOMAIN OGGMA
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
MAP LHR.*, TARGET LHR.*;
这里，注意不要运行！！！因为目标端的数据还没有初始化完成，等用expdp导入完成后，再根据csn号进行启用！
impdp+network导入目标端数据
-- 让源端一直产生事务
/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
----------------------------------------
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=
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
参考：https://www.xmmup.com/dbbao63xiangmufenxiangshiyongora2pgqianyioracle19cdaopg13bingshiyongoggpeizhishi.html
参数介绍：
-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 -rr 控制台刷新显示频率（单位：秒），默认每秒显示一次； -rt 设置负载运行时间，格式：hh:mm.sec，默认一直运行; -min设置内部事务最小思考时间；Min. Intra Delay Within Transactions(ms)，线程思考时间（暂停时间），用于模拟实际用户操作。 -max设置内部事务最大思考时间；Min. Intra Delay Within Transactions(ms)，线程思考时间（暂停时间），用于模拟实际用户操作。 -vo 输出测试结果到文件，默认输出到控制台； -env 打印出环境变量的信息 -bg是后台运行
执行过程：
基于csn号启用replicate进程
在启用之前，可以查询表数据，发现和源端数据量相差很大，因为源端一直在做压测产生新数据。
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
注意：这里的scn使用最小的scn号
数据已经实时同步了。
测试DDL和DML同步
在Oracle 12c端建表和插入数据：
create table t1 (id number primary key,name varchar2());
insert into t1 select object_id,object_name from dba_objects where object_id<=;
commit;
select count(*) from t1;
LHR@lhrsdb> select count() from t1;
COUNT()
----------
从源端查看：
image-20220623165641839
查看目标端：
image-20220623165708001
可见，DDL和DML同步都已经完成！
配置ora19c到ora12c的实时同步
创建extract进程
extract ext19c
USERIDALIAS ora19c, DOMAIN OGGMA
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
TRANLOGOPTIONS EXCLUDETAG
exttrail ./dirdat/e2
table lhr.*;
创建replicate进程
REPLICAT rep12c
USERIDALIAS ora12c DOMAIN OGGMA
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
DBOPTIONS SETTAG
MAP lhr.*, TARGET lhr.*;
测试DDL和DML同步
在Oracle 21c操作：
create table t2 (id number primary key,name varchar2());
insert into t2 select object_id,object_name from dba_objects where object_id<=;
commit;
select count() from t2;
可见，DDL和DML同步都已经完成！
反向同步也正常，可以作为回退方案！！！
OGG健康巡检
参考：https://www.xmmup.com/ogg-for-oraclejiankangxunjianguanfangjiaoben.html
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').
解决：
1、尽量选择数据库无事务的时候的scn号或业务低的时候进行导出，一般是半夜进行
2、可以尝试将scn号往后倒退一些
3、可以尝试参数HANDLECOLLISIONS，但可能会导致数据不一致
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
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;
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2022-10-24，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读