使用GoldenGate 迁移Oracle到PostgreSQL/LightDB
发布时间：2022-08-25 20:10:14 所属栏目：点评 来源：
导读： 说明1.1数据库国产化背景Oracle迁移到PostgreSQL/LightDBOGG软件介质：Oracle GoldenGate V11.2.1.0.1 for Oracle 10g o
说明1.1数据库国产化背景Oracle迁移到PostgreSQL/LightDBOGG软件介质：Oracle GoldenGate V11.2.1.0.1 for Oracle 10g on Linux x86-64 84.7 MB 利用OGG+ora2pg迁移 搭建目标数据库和OGG软件2.1创建OGG安装目录(两端)源和目标两台服务器都需要创建ogg的安装目录 ggs_Linux_x64_PostgreSQL_64bit.tar 2.2创建ogg的管理用户，使用oracle作为ogg的管理用户。（源端）在oracle环境变量配置文件中增加以下内容： export EDITOR=viexport GGATE=/home/oracle/ogg19cexport NLS_LANG=AMERICAN_AMERICA.AL32UTF8export ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=/home/oracleexport ORACLE_SID=testexport PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$GGATE:$PATHexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:$GGATE:$LD_LIBRARY_PATHalias sqlplus='rlwrap sqlplus'alias ggsci='rlwrap ggsci' 2.3建立OGG表空间和用户(源端)create tablespace ogg datafile '/data2/oradata/TEST/ogg01.dbf' size 50M autoextend on;create user ogg identified by ogg default tablespace ogg;grant connect, resource TO ogg;grant select any dictionary, select any table TO ogg;grant flashback any table TO ogg;grant execute on dbms_flashback TO ogg;grant ALTER ANY TABLE to ogg;grant insert any table to ogg;grant update any table to ogg;grant delete any table to ogg;grant create table,create sequence to ogg;grant execute on utl_file to ogg; 2.4添加附加日志和force logging，并切换日志(源端)如果源端不开启归档，需添加多组日志。 alter database force logging;alter database add SUPPLEMENTAL log data;SQL> select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;NAME OPEN_MODE FORCE_LOGGING SUPPLEME--------- -------------------- ------------------- --------ORCL READ WRITE YES YES 2.5 创建OGG的管理工作目录（源端）在配置OGG进程之前，首先需要创建OGG的管理目录，执行以下操作： ggsciGGSCI (hs-10-20-30-199) 2> create subdirs##19c会默认创建，无需执行 只有提交事务的record才会被捕获。抽取、投递、应用日志的单位是record，而不是trail文件。 2.6配置MGR参数文件，并启动mgr（源端）mgr进程是这些进程的管理和守护进程，目标端的mgr进程还与源端进行通信 源端：edit params mgrPORT 7809PURGEOLDEXTRACTS /home/oracle/ogg19c/dirdat/*,USECHECKPOINTS, minkeepdays 7--不清除trail文件AUTORESTART EXTRACT *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60GGSCI (hs-10-20-30-199) 6> start mgrGGSCI (hs-10-20-30-199) 7> info all 2.7添加extract进程（源端）抽取进程：负责抓取需要传输的数据（添加进程的时候为抽取点，而不是启动进程的时候） TEST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.30.199)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = test))) 配置extract进程 edit params ext_1EXTRACT ext_1SETENV (NLS_LANG=AMERICAN_AMERICA.UTF8)SETENV (ORACLE_HOME=/home/oracle)SETENV (ORACLE_SID=test)USERID ogg, PASSWORD oggDISCARDFILE /home/oracle/ogg19c/dirrpt/ext_1.dsc,APPEND,MEGABYTES 1024EXTTRAIL /home/oracle/ogg19c/dirdat/aaTABLE ta6_pub11*;# 创建 extract 组；ADD EXTRACT ext_1, TRANLOG, BEGIN NOW# 创建本地 trail 文件ADD EXTTRAIL /home/oracle/ogg19c/dirdat/aa, EXTRACT ext_1, MEGABYTES 5# 启动extract进程start extract ext_1GGSCI (hs-10-20-30-199) 16> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNINGEXTRACT RUNNING EXT_1 00:00:15 00:00:02 2.8投递进程（源端）投递进程： EDIT PARAMS pump_1# 复制如下内容至文件内，酌情修改参数保存EXTRACT pump_1SETENV (NLS_LANG=AMERICAN_AMERICA.UTF8)PASSTHRURMTHOST 10.20.30.199, MGRPORT 7810RMTTRAIL /home/lightdb/ogg/dirdat/paTABLE ta6_pub11.*;# 用 add extract 指定本地 trail 文件ADD EXTRACT pump_1, EXTTRAILSOURCE /home/oracle/ogg19c/dirdat/aa# 用 add rmttrail 指定远程 trail 文件ADD RMTTRAIL /home/lightdb/ogg/dirdat/pa, EXTRACT pump_1, MEGABYTES 5# 启动Pump 进程START EXTRACT pump_1 2.9生成define文件在异构的同步复制；需要生成define文件。用于数据兼容不同数据库。 # 编辑参数文件GGSCI (oracle221) 16> edit params defgen# 参数文件内容defsfile /home/oracle/ogg19c/dirdef/defgen.defuserid ogg, password oggTABLE ta6_pub11.*;# 退出ggsci命令GGSCI (oracle221) 17> exit# root用户下生成文件defgen paramfile /home/oracle/ogg19c/dirprm/defgen.prm[oracle@hs-10-20-30-199 ogg19c]$ defgen paramfile /home/oracle/ogg19c/dirprm/defgen.prm***********************************************************************Oracle GoldenGate Table Definition Generator for OracleVersion 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 15:32:20Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.Starting at 2022-06-30 10:31:23***********************************************************************Operating System Version:LinuxVersion #1 SMP Wed Apr 28 21:49:45 UTC 2021, Release 3.10.0-1160.25.1.el7.x86_64Node: hs-10-20-30-199Machine: x86_64soft limit hard limitAddress Space Size : unlimited unlimitedHeap Size : unlimited unlimitedFile Size : unlimited unlimitedCPU Time : unlimited unlimitedProcess id: 106662************************************************************************* Running with the following parameters *************************************************************************defsfile /home/oracle/ogg19c/dirdef/defgen.defuserid ogg, password ***TABLE ta6_pub11.*;Expanding wildcard table specification ta6_pub11.*:Retrieving definition for ta6_pub11.BONUS.2022-06-30 10:31:24 WARNING OGG-06439 No unique key is defined for table BONUS. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.Retrieving definition for ta6_pub11.DEPARTMENTS.2022-06-30 10:31:24 WARNING OGG-06439 No unique key is defined for table DEPARTMENTS. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.Retrieving definition for ta6_pub11.DEPT.Retrieving definition for ta6_pub11.EMP.Retrieving definition for ta6_pub11.ORDERS.Retrieving definition for ta6_pub11.SALGRADE.2022-06-30 10:31:25 WARNING OGG-06439 No unique key is defined for table SALGRADE. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.Retrieving definition for ta6_pub11.USERS.Definitions generated for 7 tables in /home/oracle/ogg19c/dirdef/defgen.def.# 传送到目标端scp /home/oracle/ogg19c/dirdef/defgen.def lightdb@10.20.30.199:/home/lightdb/ogg/dirdef 第三章 LightDB端ogg配置3.1解压软件并配置环境变量(目标端)配置环境变量 # User specific aliases and functionsexport LD_LIBRARY_PATH=$PGHOME/lib:$PGHOME/lib/ltext:$LD_LIBRARY_PATHexport PATH=$PGHOME/bin:$PATHexport LIGHTDB_PORT=5435export PGUSER=lightdbexport LIGHTDB_HOST=10.20.30.199export PGHOME=/data1/customer_do_not_delete/ta/lightdb-x/13.3-22.1/export PGDATA=/data1/customer_do_not_delete/ta/lightdb-x/13.3-22.1/data/defaultClusterexport PATH=${PGHOME}/bin:${PGHOME}/tools/bin:${PGHOME}/tools/sbin:${PATH}export LD_LIBRARY_PATH=${PGHOME}/lib:${PGHOME}/lib/ltext:${PGHOME}/tools/lib64:${LD_LIBRARY_PATH}export GGATE=/home/oracle/oggexport PATH=$PGHOME/bin:$HOME/bin:$GGATE:$PATHexport LD_LIBRARY_PATH=$PGHOME/lib:$GGATE/lib:$LD_LIBRARY_PATHexport ODBCINI=$GGATE/odbc.inialias ggsci='rlwrap ggsci'3.2创建目标端ogg用户lightdb@postgres=# create database ogg;CREATE DATABASElightdb@postgres=# create user ogg superuser password 'ogg';CREATE ROLElightdb@postgres=#lightdb@postgres=#lightdb@postgres=#lightdb@postgres=# /c ogg oggYou are now connected to database "ogg" as user "ogg".ogg@ogg=# create schema ogg;CREATE SCHEMA 3.3创建DSNogg for postgresql使用的是ODBC来连接数据库，因此需要指明ODBC dirver vi /home/lightdb/ogg/odbc.ini[ODBC Data Sources] //定义的数据库别名，后面的【postgre】部分与之对应GG_Postgres=DataDirect 22.1 LightDB Wire Protocol[ODBC]IANAAppCodePage=106 //字符集，应该与数据库的字符集对应，106代表UTF-8InstallDir=/home/lightdb/ogg //ogg安装目录[GG_Postgres]Driver=/home/lightdb/ogg/lib/GGpsql25.so //OGG安装包里有内置ODBC驱动Description=DataDirect LightDB Wire Protocol //介绍Database=ta6_pub11 //表空间HostName=10.20.30.199 //一般是本地地址PortNumber=5435 //端口LogonID=ta6_pub11 //数据库用户名Password=ta6_pub11 //数据库密码 3.4创建GoldenGate工作目录cd /home/lightdb/oggggsciGGSCI (hs-10-20-30-199) 2> create subdirsCreating subdirectories under current directory /home/lightdb/oggParameter file /home/lightdb/ogg/dirprm: created.Report file /home/lightdb/ogg/dirrpt: created.Checkpoint file /home/lightdb/ogg/dirchk: created.Process status files /home/lightdb/ogg/dirpcs: created.SQL script files /home/lightdb/ogg/dirsql: created.Database definitions files /home/lightdb/ogg/dirdef: created.Extract data files /home/lightdb/ogg/dirdat: created.Temporary files /home/lightdb/ogg/dirtmp: created.Credential store files /home/lightdb/ogg/dircrd: created.Master encryption key wallet files /home/lightdb/ogg/dirwlt: created.Dump files /home/lightdb/ogg/dirdmp: created. 3.5配置MGR进程组# 编辑mgrGGSCI (oracle221) 1>EDIT param mgr# 复制以下内容，保存（此端口要与源端Pump进程里配置的对应）PORT 7810# 启动mgrGGSCI (hs-10-20-30-199) 4> start mgrGGSCI (hs-10-20-30-199) 5> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING 3.6添加checkpoint表为了保证源和目标端在传数据的时候不会重复或者少传，在目标端添加checkpoin表 dblogin sourcedb gg_Postgres userid ogg , password ogg add checkpointtable public.checkpointtab 3.7配置replicate进程# 编辑参数文件edit params rep_1# 复制如下内容，酌情修改参数，保存REPLICAT rep_1SOURCEDEFS /home/lightdb/ogg/dirdef/defgen.defSETENV(PGCLIENTENCODING = "UTF8" )SETENV(ODBCINI="/home/lightdb/ogg/odbc.ini" )SETENV(NLS_LANG="AMERICAN_AMERICA.AL32UTF8")TARGETDB GG_Postgres,userid ogg,password oggDISCARDFILE /home/lightdb/ogg/dirrpt/rep_1.rpt, appendMAP ta6_pub11.*, TARGET ta6_pub11.*;# 添加 Replicat 进程add replicat rep_1,exttrail /home/lightdb/ogg/dirdat/pa,begin now,checkpointtable public.checkpointtab# 启动 Replicat 进程GGSCI (lottu02) 1> start REPLICAT rep_1GGSCI (hs-10-20-30-199) 1> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNINGREPLICAT RUNNING REP_1 00:00:00 00:00:07 第四章 数据验证源端执行 SQL> delete from TBFUNDCHANGELIMIT; 1379 rows deleted. SQL> commit; Commit complete. 目标端 ta6_pub11@ta6_pub11=# select count(*) from tbfundchangelimit;count-------1379(1 row)ta6_pub11@ta6_pub11=# select count(*) from tbfundchangelimit;count-------(1 row) 数据初始同步，可以使用ora2pg （编辑：泰州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
推荐文章
站长推荐