类似文章
OGG有传统的经典架构,也有最新的微服务,2个都可以远程捕获和应用数据,对数据库服务器是0侵入,而传统的经典架构是纯命令行模式,最新的微服务架构是图形化界面操作,几乎所有操作都可以在界面进行。相关文章可以参考:
使用ogg 12.2 replicate参数DBOPTIONS ENABLE_INSTANTIATION_FILTERING
(推荐)。从ogg 12.2开始通过设置replicate参数DBOPTIONS ENABLE_INSTANTIATION_FILTERING
,可以使得OGG能够自动识别每张表导入后的csn,自动应用csn之后的数据,从而可以使导出数据不再需要flashback_scn号了。
-- 创建专用网络
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 object_type,status,count(*) from dba_objects where owner='LHR' group by object_type,status;
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 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/
ogg@.../lhrsdb
创建脉动表(Heartbeattable):
目标库查询:
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
image-20220623150718680
查询验证:
image-20220623150802600
源端查询:
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;
数据目录:/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.*;
image-20220624151707580
REPLICAT rep19c
USERIDALIAS ora19c DOMAIN OGGMA
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
MAP LHR.*, TARGET LHR.*;
这里,注意不要运行!!!因为目标端的数据还没有初始化完成,等用expdp导入完成后,再根据csn号进行启用!
-- 让源端一直产生事务
/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
导入完成后,查看目标库:
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进行应用。
在启用之前,可以查询表数据,发现和源端数据量相差很大,因为源端一直在做压测产生新数据。
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
数据已经实时同步了。
在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同步都已经完成!
extract ext19c
USERIDALIAS ora19c, DOMAIN OGGMA
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
TRANLOGOPTIONS EXCLUDETAG
exttrail ./dirdat/e2
table lhr.*;
REPLICAT rep12c
USERIDALIAS ora12c DOMAIN OGGMA
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
DBOPTIONS SETTAG
MAP lhr.*, TARGET lhr.*;
在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同步都已经完成!
反向同步也正常,可以作为回退方案!!!
参考: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
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需要在目标端配置检查点表!!!