本文主要是介绍OGG实现Oracle19C到postgreSQL14的实时同步，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10余年DBA及大数据工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前服务于工业互联网
擅长主流Oracle、MySQL、PG、高斯及Greenplum运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️
文章目录
- 前言
- 📣 1.架构设计
- 📣 2.OGG下载
- 📣 3.安装OGG for Oracle
- ✨ 3.1 解压授权
- ✨ 3.2. 静默安装
- ✨ 3.3 环境变量
- ✨ 3.4 创建目录
- 📣 4.安装OGG for postgreSQL
- ✨ 4.1 解压及授权
- ✨ 4.2 修改环境变量
- ✨ 4.3 创建目录
- 📣 5.Oracle配置
- ✨ 5.1 开归档
- ✨ 5.2 打开附加日志
- 📣 6.MGR管理进程
- 📣 7.抽取进程extract
- 📣 8.传送进程pump
- 📣 9.目标端配置
- 📣 10.测试同步
前言
本文详细介绍了我的OGG旅程、从OGG实现Oracle19C到postgreSQL14的实时同步📣 1.架构设计
| 主机名 | IP | 操作系统 | 内存/空间 | 角色 | 说明 |
|---|---|---|---|---|---|
| ora19c | 172.18.12.90 | centos7.8 | 2c/18G 400G | 源端 | Oracle 19.3 |
| postgres14 | 172.18.12.50 | centos7.8 | 2c/18G 400G | 目标库 | postgreSQL 14 |
📣 2.OGG下载
1.官方下载
https://www.oracle.com/middleware/technologies/goldengate-downloads.html
1.OGG for oracle安装包
213000_fbo_ggs_Linux_x64_Oracle_shiphome.zip
2.OGG for PostgreSQL安装包
213000_ggs_Linux_x64_PostgreSQL_64bit.zip
📣 3.安装OGG for Oracle
✨ 3.1 解压授权
mkdir /ogg
mkdir /soft
chown oracle.oinstall /ogg
unzip /opt/213000_fbo_ggs_Linux_x64_Oracle_shiphome.zip -d /soft
cd /soft/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/
chown -R oracle.oinstall *
✨ 3.2. 静默安装
#使用oracle用户安装ogg
[root@jemora19c ~]# su - oracle
[oracle@jemora19c ~]$ cd /soft/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/
./runInstaller -silent
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v21_1_0
INSTALL_OPTION=ORA19c
SOFTWARE_LOCATION=/ogg
START_MANAGER=true
MANAGER_PORT=7809
DATABASE_LOCATION=/opt/oracle/product/19c/dbhome_1
INVENTORY_LOCATION=/opt/oracle/oraInventory
UNIX_GROUP_NAME=oinstall
✨ 3.3 环境变量
cat >> /home/oracle/.bash_profile <<"EOF"
export OGG_HOME=/ogg
export PATH=$OGG_HOME:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$OGG_HOME:$ORACLE_HOME/lib:$LD_LIBRARYPATH
alias ggsci='cd $OGG_HOME;ggsci'
EOFsource /home/oracle/.bash_profile[oracle@jemora19c ~]$ ggsciOracle GoldenGate Command Interpreter for Oracle
Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047_FBO
Oracle Linux 7, x64, 64bit (optimized), Oracle Database 21c and lower supported versions on Jul 29 2021 03:59:23
Operating system character set identified as UTF-8.Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.GGSCI (jemora19c) 1> info allProgram Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
✨ 3.4 创建目录
oracle@jemora19c ~]$ ggsci
GGSCI (jemora19c) 2> create subdirs
📣 4.安装OGG for postgreSQL
✨ 4.1 解压及授权
mkdir /ogg
unzip /opt/213000_ggs_Linux_x64_PostgreSQL_64bit.zip
tar vxf ggs_Linux_x64_PostgreSQL_64bit.tar -C /ogg
chown -R postgres.postgres /ogg
✨ 4.2 修改环境变量
cat >> /var/lib/pgsql/.bash_profile <<"EOF"
export OGG_HOME=/ogg
export PATH=$OGG_HOME:$PATH
export LD_LIBRARY_PATH=$OGG_HOME/lib:$LD_LIBRARY_PATH
alias ggsci='cd $GG_HOME;ggsci'
EOFsource /var/lib/pgsql/.bash_profile
✨ 4.3 创建目录
GGSCI (postgres14) 2> create subdirs
📣 5.Oracle配置
✨ 5.1 开归档
– mount阶段
alter database archivelog;
archive log list;
✨ 5.2 打开附加日志
lter system set enable_goldengate_replication=true;
alter database add supplemental log data;
SELECT supplemental_log_data_min min,
supplemental_log_data_pk pk,
supplemental_log_data_ui ui,
supplemental_log_data_fk fk,
supplemental_log_data_all allc
FROM v$database;
📣 6.MGR管理进程
cat > /ogg/dirprm/mgr.prm << “EOF”
PORT 7809
DYNAMICPORTLIST 7810-7830
AUTOSTART EXTRACT *
AUTORESTART EXTRACT , RETRIES 100, WAITMINUTES 2, RESETMINUTES 10
PURGEOLDEXTRACTS ./dirdat/, usecheckpoints, minkeepdays 30
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
EOF
📣 7.抽取进程extract
cat > /ogg/dirprm/ext_ora.prm << “EOF”
extract ext_ora
setenv (ORACLE_SID=oradb)
setenv (ORACLE_HOME=/opt/oracle/product/19c/dbhome_1)
setenv (NLS_LANG=“AMERICAN_AMERICA.AL32UTF8”)
userid PGOGG@oradb,password oracle
exttrail /ogg/dirdat/ex
table PGOGG.test01;
table STEST.*;
EOF
– 添加进程
add extract ext_ora, tranlog, begin now
add exttrail /ogg/dirdat/ex, extract ext_ora
📣 8.传送进程pump
cat > /ogg/dirprm/pump_ora.prm << “EOF”
extract pump_ora
setenv (ORACLE_SID=oradb)
setenv (ORACLE_HOME=/opt/oracle/product/19c/dbhome_1)
setenv (NLS_LANG=“AMERICAN_AMERICA.AL32UTF8”)
rmthost 172.18.12.50,mgrport 7809,compress
rmttrail /ogg/dirdat/ex
table PGOGG.test01;
table STEST.*;
EOF
📣 9.目标端配置
cat > /ogg/dirprm/rep_pg.prm <<“EOF”
replicat rep_pg
SOURCEDEFS ./dirdef/defgen_PG.prm
SETENV(PGCLIENTENCODING = “UTF8” )
SETENV(ODBCINI=“/ogg/odbc.ini” )
SETENV(NLS_LANG=“AMERICAN_AMERICA.AL32UTF8”)
TARGETDB PGDSN, userid stest, password post
DISCARDFILE ./discard/rep_pg.dsc, append
–ap lhrogg.test01, target lhrogg.test01;
map stest., target stest.;
EOF
📣 10.测试同步
-- oracle查询
col table_name format a30
SELECT a.table_name,a.num_rows FROM dba_tables a where a.OWNER='STEST' order by num_rows desc ;SYS@oradb> delete from STEST.LOGON;
2383 rows deleted.SYS@oradb> select count(*) from STEST.LOGON;COUNT(*)
----------0jemdb=# select count(*) from stest.LOGON;count
-------0
(1 row)-- 在pg中查询数据量
select nspname AS schemaname,relname,reltuples::numeric as rowcount,pg_size_pretty (pg_total_relation_size ( '"' || nspname || '"."' || relname || '"' )) AS SIZE
from pg_class C LEFT JOIN pg_namespace N ON ( N.oid = C.relnamespace )
where nspname NOT IN ( 'pg_catalog', 'information_schema' ) AND relkind = 'r'
ORDER by reltuples DESC
LIMIT 20;schemaname | relname | rowcount | size
------------+----------------------+----------+------------stest | inventories | 899441 | 433 MBstest | order_items | 7341 | 1072 kBstest | addresses | 1500 | 264 kBstest | card_details | 1500 | 264 kBstest | orders | 1430 | 376 kBstest | product_information | 1000 | 400 kBstest | warehouses | 1000 | 192 kBstest | customers | 1000 | 440 kBstest | product_descriptions | 1000 | 288 kBstest | logon | 0 | 16 kBstest | orderentry_metadata | -1 | 8192 bytespgogg | test01 | -1 | 8192 bytesstest | checkpointtab | -1 | 32 kBstest | checkpointtab_lox | -1 | 16 kB
(14 rows)GGSCI (postgres14) 2> stats rep_pg,table stest.logon, totalSending STATS request to Replicat group REP_PG ...Start of statistics at 2023-12-02 00:27:37.Replicating from STEST.LOGON to stest.logon:*** Total statistics since 2023-12-02 00:24:44 ***Total inserts 0.00Total updates 0.00Total deletes 2383.00Total upserts 0.00Total discards 0.00Total operations 2383.00
这篇关于OGG实现Oracle19C到postgreSQL14的实时同步的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
