OGG单向复制实践教程

2023-12-23 18:01:03

前言
OGG用于Oracle数据库间，进行数据同步。
搭建环境：
操作系统：centos7.6
两台服务器（均部署了单节点的Oracle11G）
一、安装goldengate软件
1.1OGG软件 在源端数据库所在服务器和目标端数据库所在服务器 都要安装。
1.2设置环境变量，修改hosts文件

Item Source System Target System
Platform Red Hat 5.10 Red Hat 5.10
Hostname bjcuug tjcuug
Database Oracle 11.2.0.1.0 Oracle 11.2.0.1.0
NLS_LANG AMERICAN_AMERICA.AL32UTF8 AMERICAN_AMERICA.AL32UTF8
ORACLE_SID prod prod
Listener Name/Port LISTENER/1521 LISTENER/1521
Goldengate User ogg ogg
IP 192.168.3.55 192.168.3.66

可以在原来Oracle数据库的环境变量基础上进行修改：

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/network/lib

1.3.安装OGG软件
创建软件目录 /u01/app/ogg，赋权
上传ogg软件,解压文件
tar -xvf fbo_ogg_Linux_x64_ora11g_64bit.tar
1.4 OGG目录创建
[oracle@root ogg]$./oggci
OGGCI (oracle) 3> create subdirs （会自动创建）
Creating subdirectories under current directory /u01/app/ogg

Parameter files /u01/app/ogg/dirprm: created
Report files /u01/app/ogg/dirrpt: created
Checkpoint files /u01/app/ogg/dirchk: created
Process status files /u01/app/ogg/dirpcs: created
SQL script files /u01/app/ogg/dirsql: created
Database definitions files /u01/app/ogg/dirdef: created
Extract data files /u01/app/ogg/dirdat: created
Temporary files /u01/app/ogg/dirtmp: created
Veridata files /u01/app/ogg/dirver: created
Veridata Lock files /u01/app/ogg/dirver/lock: created
Veridata Out-Of-Sync files /u01/app/ogg/dirver/oos: created
Veridata Out-Of-Sync XML files /u01/app/ogg/dirver/oosxml: created
Veridata Parameter files /u01/app/ogg/dirver/params: created
Veridata Report files /u01/app/ogg/dirver/report: created
Veridata Status files /u01/app/ogg/dirver/status: created
Veridata Trace files /u01/app/ogg/dirver/trace: created
Stdout files /u01/app/ogg/dirout: created

二、数据库环境准备
[root@bjcuug u01]# mkdir -p arch
[root@bjcuug u01]# chmod -R 775 arch/
[root@bjcuug u01]# chown -R oracle:oinstall arch/
2.1开启数据库归档模式

检查数据库是否归档模式，如果不是修改为归档模式（下图所示的是已经开启归档）。
Enable archive log mode:
[oracle@db ogg] sqlplus / as sysdba
SQL> alter system set log_archive_dest=’ /u01/arch’;
如果出错则执行：SQL> alter system set db_recovery_file_dest=’’;

SQL> archive log list

Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
1)sql>shutdown immediate;
2)sql>startup mount;
3)sql>alter database archivelog;
4)sql>alter database open;
5)sql>archive log list;

没开启归档的话如下所示：

SQL> archive log list
Database log mode 	No Archive Mode
Automatic archival 	Disabled
Archive destination 	USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 9
Current log sequence 11

2.2启动数据库附加日志
Check supplemental log mode is enabled or not by following query:
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEMENTAL_LOG_DATA_MI

NO
If not, enable it:
SQL> alter database add supplemental log data;
SQL> alter system switch logfile;

开启数据库追加模式
Check force logging mode is enabled or not by following query:
通过以下查询检查强制记录模式是否启用:
SQL> SELECT force_logging FROM v$database;
FORCE_LOG

NO
Enable force logging mode:
SQL> alter database force logging;

2.3创建数据库ogg用户
[oracle@db ogg] sqlplus / as sysdba
create tablespace tbs_gguser datafile ‘/u01/oradata/prod/gguser.dbf’ size 50M autoextend on;
create user ogg identified by ogg default tablespace tbs_gguser temporary tablespace TEMP quota unlimited on tbs_gguser;
2.4 Goldengate赋权
可以直接赋予个dba权限 grant dba to ogg;或者按照下面给权限
源端：

grant CONNECT, RESOURCE to ogg ;
grant CREATE SESSION, ALTER SESSION to ogg ;
grant SELECT ANY DICTIONARY, SELECT ANY TABLE to ogg ;
grant ALTER ANY TABLE to ogg ;
grant FLASHBACK ANY TABLE to ogg ;
grant EXECUTE ON DBMS_FLASHBACK to ogg ;
grant SELECT ON DBA_CLUSTERS to ogg ;
grant SELECT ANY TRANSACTION to ogg ;

Create empty test table and grant DML privilege to Goldengate user:

SQL> alter user scott identified by tiger account unlock;
SQL> conn scott/tiger
SQL> grant INSERT, UPDATE, DELETE on scott.emp to ogg;
SQL> grant INSERT, UPDATE, DELETE on scott.dept to ogg;

目标端：

grant CONNECT, RESOURCE to ogg ;
grant CREATE SESSION, ALTER SESSION to ogg ;
grant SELECT ANY DICTIONARY, SELECT ANY TABLE to ogg ;
grant CREATE TABLE to ogg ;
grant INSERT ANY TABLE to ogg ;
grant UPDATE ANY TABLE to ogg ;
grant DELETE ANY TABLE to ogg ;

三、OGG链路配置
3.1Enable transaction data change capture for these two tables in Source system:
在源系统中为这两个表启用事务数据更改捕获:
GGSCI (ogg) 3> DBLOGIN USERID ogg, PASSWORD ogg
Successfully logged into database.

GGSCI (ogg) 5> ADD TRANDATA scott.emp
Logging of supplemental redo data enabled for table SCOTT.EMP.

GGSCI (ogg) 6> ADD TRANDATA scott.dept
Logging of supplemental redo data enabled for table SCOTT.EMP.
Verify that supplemental logging has been turned on for these tables.
验证这些表的补充日志记录是否已打开。
GGSCI (cdcjp63vm3.cn.oracle.com) 7> INFO TRANDATA scott.*
Logging of supplemental redo log data is enabled for table SCOTT.EMP
Logging of supplemental redo log data is enabled for table SCOTT.EMP

3.2MANAGER（管理）进程配置
配置MGR参数文件
Create the Manager parameter file.(创建管理参数文件)
[oracle@db ogg] ./ggsci
GGSCI (ogg) 1> EDIT PARAMS MGR
管理进程配置文件如下:

port 7809
dynamicportlist 7810-8000
autorestart er *, retries 5, waitminutes 3
purgeoldextracts ./dirdat/*,usecheckpoints, minkeepdays 7
LAGREPORTHOURS 1
LAGINFOMINUTES 10
LAGCRITICALMINUTES 15

OGGCI (ogg) 8> info all 查看进程状态

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
3.3抽取进程

源端配置抽取进程参数文件

OGGCI (ogg) 11> EDIT PARAMS ehx2al
抽取进程参数文件如下：

extract ehx2al
SETENV (ORACLE_SID=prod)                        
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg ,password ogg
REPORT AT 01:59
REPORTROLLOVER AT 02:00
CACHEMGR, CACHESIZE 256MB
EXTTRAIL ./dirdat/ed
NUMFILES 3000
EOFDELAYCSECS 30
DISCARDFILE ./dirrpt/extya.dsc, APPEND, MEGABYTES 1024
--BR BRINTERVAL 2H , BRDIR BR
GETUPDATEBEFORES
GETTRUNCATES
--NOCOMPRESSDELETES / COMPRESSDELETES
--WARNLONGTRANS 3H, CHECKINTERVAL 3M

TABLE scott.emp;
TABLE scott.dept;

源端添加配置进程

OGGCI > add extract ehx2al,tranlog,threads 1,begin now
OGGCI > add exttrail ./dirdat/ed,extract ehx2al,megabytes 5

3.4投递进程

源端配置投递进程参数文件

OGGCI (ogg) 11> edit params phx2al

投递进程参数文件如下（要更改目标服务器的IP）：

extract phx2al
SETENV (ORACLE_SID=prod)                         
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
Userid ogg,PASSWORD ogg
REPORT AT 01:59
REPORTROLLOVER AT 02:00
CACHEMGR, CACHESIZE 256MB
FLUSHCSECS 30
NUMFILES 3000
EOFDELAYCSECS 30
RMTHOST tjcuug,MGRPORT 7809, TCPBUFSIZE 100000, TCPFLUSHBYTES 300000
RMTTRAIL ./dirdat/pd
GETTRUNCATES
PASSTHRU (pass through)
DYNAMICRESOLUTION
GETUPDATEBEFORES

TABLE scott.emp;
TABLE scott.dept;

源端添加配置进程

OGGCI > Add extract phx2al exttrailsource ./dirdat/ed
OGGCI > Add rmttrail ./dirdat/pd extract phx2al megabytes 5

3.5复制进程

目标端复制（应用）进程配置。

OGGCI (ogg) 11> edit params rhx2al

复制进程配置文件内容如下：

replicat rhx2al
SETENV (ORACLE_SID=prod)
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
Userid ogg,PASSWORD ogg
REPORT AT 01:59
REPORTROLLOVER AT 02:00
CACHEMGR, CACHESIZE 256MB
REPERROR DEFAULT,ABEND /DISCARD
DISCARDFILE ./dirrpt/extya.dsc, APPEND, MEGABYTES 1024
GETTRUNCATES
NUMFILES 3000
EOFDELAYCSECS 30
ASSUMETARGETDEFS
ALLOWNOOPUPDATES
DYNAMICRESOLUTION
GETUPDATEBEFORES
GROUPTRANSOPS 1
MAXTRANSOPS 1
HANDLECOLLISIONS
MAP scott.*, TARGET scott.*;

OGGCI (ogg) 12> info all 查看目前进程情况
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
REPLICAT STOPPED RHX2AL 00:00:00 00:00:03
OGGCI (ogg) 17> start manager 启动管理进程
Manager started.
OGGCI (ogg) 18> start rhx2al 启动复制进程
Sending START request to MANAGER …
REPLICAT RHX2AL starting
OGGCI (ogg) 19> info all 发现没起来

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
REPLICAT STOPPED RHX2AL 00:00:00 00:00:38
发现复制进程 rhx2al 没有起来，执行view report rhx2al，查看报错情况：
报错情况：直接看后面的error
… …
2020-05-15 10:37:05 ERROR OGG-00446 Checkpoint table OGG.CHECKPOINT does not exist. Please create the table or recreate the RHX2AL group using the correct table.

2020-05-15 10:37:05 ERROR OGG-01668 PROCESS ABENDING.

3.6查看最后报错，发现是没有检查表的事儿。所以在目标数据库，建立检查表

OGGCI (ogg) 31> dblogin userid ogg password ogg 连接目标数据库 用户为ogg 密码为ogg
Successfully logged into database.

OGGCI (ogg) 32> add checkpointtable ogg.checkpoint 添加检查表

Successfully created checkpoint table OGG.CHECKPOINT.

OGGCI (ogg) 33> start rhx2al

Sending START request to MANAGER …
REPLICAT RHX2AL starting

OGGCI (ogg) 34> info all 正常启动了

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING RHX2AL 00:00:00 00:00:01

四、测试同步情况

源端数据库插入一条数据，目标端数据自动同步
五、报错信息汇总
4.1ERROR OGG-01224 Address already in use.

具体报错信息：

Source Context :
SourceModule : [mgr.main]
SourceID : [/scratch/aime1/adestore/views/aime1_dadvfg0702/oggcore/OpenSys/src/app/mgr/mgr.c]
SourceFunction : [init_functions]
SourceLine : [3361]
ThreadBacktrace : [8] elements
: [./mgr(CMessageContext::AddThreadContext()+0x26) [0x60c9a6]]
: [./mgr(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, …)+0x7b2) [0x603472]]
: [./mgr(_MSG_ERR_TCP_GENERIC(CSourceContext*, char const*, CMessageFactory::MessageDisposition)+0x92) [0x5ddb62]]
: [./mgr(init_functions(int, char**)+0x916) [0x4c3bd6]]
: [./mgr(main_loop(int, char**)+0x42) [0x4cdd62]]
: [./mgr(main+0x1dc) [0x4ceebc]]
: [/lib64/libc.so.6(__libc_start_main+0x100) [0x383b81ed20]]
: [./mgr(__gxx_personality_v0+0x162) [0x4bab6a]]

2020-05-14 10:48:04 ERROR OGG-01224 Address already in use.

2020-05-14 10:48:04 ERROR OGG-01668 PROCESS ABENDING.

解决办法：
原因是之前启动了一个进程，杀掉，重新启动即可

[root@ogg~]# ps -ef | grep mgr
root 11 2 0 10:02 ? 00:00:00 [async/mgr]
postfix 1674 1667 0 10:03 ? 00:00:00 qmgr -l -t fifo -u
ogg 1981 1 0 10:09 ? 00:00:00 ./mgr PARAMFILE /goldengate/dirprm/mgr.prm REPORTFILE /goldengate/dirrpt/MGR.rpt PROCESSID MGR
root 2447 2426 0 10:52 pts/1 00:00:00 grep mgr
[root@ogg~]# kill -9 1981

4.2:OGG-01453 Database login information not specified in parameter file.

具体报错信息如下：

Bounded Recovery Parameter:
Options = BRRESET
BRINTERVAL = 4HOURS
BRDIR = /goldengate

CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 256M
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 512M
CACHESIZEMAX (strict force to disk): 433M

Source Context :
SourceModule : [er.main]
SourceID : [/scratch/sganti/view_storage/sganti_core_lin64/oggcore/OpenSys/src/app/er/rep.c]
SourceFunction : [main]
SourceLine : [26012]
ThreadBacktrace : [6] elements
: [/goldengate/extract(CMessageContext::AddThreadContext()+0x26) [0x66a416]]
: [/goldengate/extract(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, …)+0x7b2) [0x660ee2]]
: [/goldengate/extract(_MSG_ERR_STARTUP_DB_CONN_INFO_UNSPECIFIED(CSourceContext*, CMessageFactory::MessageDisposition)+0x87) [0x648977]]
: [/goldengate/extract(main+0xe11) [0x526111]]
: [/lib64/libc.so.6(__libc_start_main+0x100) [0x383b81ed20]]
: [/goldengate/extract(__gxx_personality_v0+0x1f2) [0x4f2bda]]

2020-05-14 11:16:57 ERROR OGG-01453 Database login information not specified in parameter file.

2020-05-14 11:16:57 ERROR OGG-01668 PROCESS ABENDING.

投递进程文件配置错误，未配置数据库相关信息
解决办法：

`SETENV (ORACLE_SID=prod)` #此处为源端数据库实例名
Userid ogg,PASSWORD ogg

4.3 运行状态都是running ，但是投递进程P不能够将数据发送

view report phx2aj 查看警告信息如下：

Waiting at EOF on input trail file ./dirdat/ed000003, which is not marked as complete; but succeeding trail file ./dirdat/ed000004 exists. If ALTERETROLLOVER has been performed on source extract, ALTER EXTSEQNO must be performed on each corresponding downstream reader.
解决办法：
上面文件到00003，我跳过这个文件，从4开始投递、复制
OGGCI (ogg) 54> alter extract phx2al extseqno 4 extrba 0 = =源端OGG执行
OGGCI (ogg) 54> alter replicat rhx3al,extseqno 0,extrba 0 = =目标端OGG执行
五、常用简单命令

info all --查看所有进程
start 进程名称 --启动进程
stop 进程名称 --停止进程
info 进程名称 --查看该进程的状态 可以看一下处理到哪个文件、RBA号
edit params 进程名称 --编辑进程配置文件
view params 进程名称 --查看进程配置文件
delete 进程名称 --删除进程
view report 进程名称 --查看进程状态报告 当OGG不能正常同步数据时，排查报错情况
上一篇：ORACLE LINUX 7.7 安装ORACLE 11.2.0.4.0 RAC


下一篇：linux scp放后台执行方法