有没有免费开网站的,返利网站做鹊桥推广,打开百度,西安115个高风险区降为低风险#x1f4e2;#x1f4e2;#x1f4e2;#x1f4e3;#x1f4e3;#x1f4e3; 哈喽#xff01;大家好#xff0c;我是【IT邦德】#xff0c;江湖人称jeames007#xff0c;10余年DBA及大数据工作经验 一位上进心十足的【大数据领域博主】#xff01;#x1f61c; 哈喽大家好我是【IT邦德】江湖人称jeames00710余年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操作系统内存/空间角色说明ora19c172.18.12.90centos7.82c/18G 400G源端Oracle 19.3postgres14172.18.12.50centos7.82c/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 [rootjemora19c ~]# su - oracle [oraclejemora19c ~]$ cd /soft/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/ ./runInstaller -silent oracle.install.responseFileVersion/oracle/install/rspfmt_ogginstall_response_schema_v21_1_0 INSTALL_OPTIONORA19c SOFTWARE_LOCATION/ogg START_MANAGERtrue MANAGER_PORT7809 DATABASE_LOCATION/opt/oracle/product/19c/dbhome_1 INVENTORY_LOCATION/opt/oracle/oraInventory UNIX_GROUP_NAMEoinstall ✨ 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 ggscicd $OGG_HOME;ggsci
EOFsource /home/oracle/.bash_profile[oraclejemora19c ~]$ 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 创建目录 oraclejemora19c ~]$ 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 ggscicd $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_replicationtrue; 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_SIDoradb) setenv (ORACLE_HOME/opt/oracle/product/19c/dbhome_1) setenv (NLS_LANG“AMERICAN_AMERICA.AL32UTF8”) userid PGOGGoradb,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_SIDoradb) 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.OWNERSTEST order by num_rows desc ;SYSoradb delete from STEST.LOGON;
2383 rows deleted.SYSoradb 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
