|
Item |
Source System |
Target System |
|
Platform |
CENTOS7 - 64bit |
CENTOS7 - 64bit |
|
Database |
Oracle 11.2.0.4 |
Oracle 11.2.0.4 |
|
Character Set |
AL32UTF8 |
AL32UTF8 |
|
ORACLE_SID |
cd |
sz |
|
Listener Name/Port |
LISTENER/1521 |
LISTENER/1521 |
|
Goldengate User |
ogg |
ogg |
1.安装OGG，创建目录（源库和目标库均有此操作）
[oracle@localhost oracle]$ mkdir ogg [oracle@localhost oracle]$ cd ogg/ [oracle@localhost ogg]$ unzip V34339-01.zip [oracle@localhost ogg]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar [oracle@localhost ogg]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (localhost.localdomain) 1> create subdirs Creating subdirectories under current directory /dbfile/oracle/ogg Parameter files /dbfile/oracle/ogg/dirprm: already exists Report files /dbfile/oracle/ogg/dirrpt: created Checkpoint files /dbfile/oracle/ogg/dirchk: created Process status files /dbfile/oracle/ogg/dirpcs: created SQL script files /dbfile/oracle/ogg/dirsql: created Database definitions files /dbfile/oracle/ogg/dirdef: created Extract data files /dbfile/oracle/ogg/dirdat: created Temporary files /dbfile/oracle/ogg/dirtmp: created Stdout files /dbfile/oracle/ogg/dirout: created
各目录用途如下表：
| 名字 | 用途 |
| dirprm | 存放OGG参数各的配置信息 |
| dirrpt | 存放进程报告文件 |
| dirchk | 存放检查点文件 |
| dirpcs | 存放进程状态文件 |
| dirsql | 存放SQL脚本文件 |
| dirdef | 存放DEFGEN工具生成的数据定义文件 |
| dirdat | 存放Trail文件，也就是Capture进程捕获的日志文件 |
| dirtmp | 当事物需要的内存超过已分配内存时，默认存储在这个目录 |
2.为Goldengate 配置Oracle 数据库
2.1在源库（cd）创建OGG用户并赋权
create tablespace OGG datafile '/dbfile/oracle/oradata/cd/OGG.DBF' size 2G; create user OGG identified by "OGG" default tablespace OGG temporary tablespace TEMP quota unlimited on 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;
2.2在源库创建测试表，插入数据，添加主键约束
create table SCOTT.TO_BASE ( mo_number VARCHAR2(30) not null, creater VARCHAR2(20), createdate DATE default SYSDATE ); alter table SCOTT.TO_BASE add constraint PK_C_MO_BASE primary key (MO_NUMBER); insert into SCOTT.TO_BASE (MO_NUMBER, CREATER, CREATEDATE) values ('111', 'LEO', to_date('08-11-2021 18:51:23', 'dd-mm-yyyy hh24:mi:ss')); insert into SCOTT.TO_BASE (MO_NUMBER, CREATER, CREATEDATE) values ('222', 'TOM', to_date('08-11-2021 18:51:23', 'dd-mm-yyyy hh24:mi:ss')); commit;
2.3在目标库（sz）创建OGG用户并赋权
create tablespace OGG datafile '/dbfile/oracle/oradata/sz/OGG.DBF' size 3G; create user OGG identified by "OGG" default tablespace OGG temporary tablespace TEMP quota unlimited on 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;
2.4在目标库创建测试表结构（空表即可）
create table SCOTT.TO_BASE ( mo_number VARCHAR2(30) not null, creater VARCHAR2(20), createdate DATE default SYSDATE ); alter table SCOTT.TO_BASE add constraint PK_C_MO_BASE primary key (MO_NUMBER);
2.5源库开启补充日志模式和强制记录模式
SQL> alter database add supplemental log data; Database altered. SQL> alter database force logging; Database altered. SQL> alter system switch logfile; System altered. SQL> select supplemental_log_data_min from v$database; SUPPLEME -------- YES
2.6源库开启归档日志
[oracle@localhost ~]$ mkdir /db/arch SQL> alter system set log_archive_dest_1='location=/db/arch'; SQL> shutdown immediate; SQL> startup mount; SQL> alter database archivelog; SQL> alter database open; SQL> alter system archive log current; SQL> archive log list;
2.7源库添加表级TRANDATA
GGSCI (localhost.localdomain) 2> dblogin userid OGG,password OGG Successfully logged into database. GGSCI (localhost.localdomain) 3> add trandata SCOTT.TO_BASE Logging of supplemental redo data enabled for table SCOTT.TO_BASE. GGSCI (localhost.localdomain) 4> info trandata SCOTT.TO_BASE Logging of supplemental redo log data is enabled for table SCOTT.TO_BASE. Columns supplementally logged for table SCOTT.TO_BASE: MO_NUMBER.
2.8配置源库MGR管理进程
GGSCI (localhost.localdomain) 5> edit params mgr PORT 7809 PURGEOLDEXTRACTS ./dirdat, USECHECKPOINTS GGSCI (localhost.localdomain) 6> start mgr Manager started. GGSCI (localhost.localdomain) 7> info mgr Manager is running (IP port localhost.localdomain.7809).
2.9配置目标库MGR管理进程
GGSCI (localhost.localdomain) 2> edit params mgr PORT 7809 PURGEOLDEXTRACTS /dbfile/oracle/ogg/dirdat, USECHECKPOINTS GGSCI (localhost.localdomain) 3> start mgr Manager started. GGSCI (localhost.localdomain) 4> info mgr Manager is running (IP port localhost.localdomain.7809).
参数说明：
PORT 7809：OGG管理进程监控端口。
PURGEOLDEXTRACTS：清除不需要的trail文件。
/ogg/dirdat：trail文件存放位置。
USECHECKPOINTS：使用检查点队列。
本实验只用到上述参数，MGR其他参数详见下表，摘自OGG官方文档。
Manager parameters: General
| 名字 | 用途 |
| CHARSET | Speci fies a multibyte character set for the process to us instead of the operating system default when reading the parameter file. |
| COMMENT | Allows insertion of comments in a parameter file. |
| SOURCEDB | Specifies a data source name as part of the login |
| USERID | Provides login information for Manager when it needs to access the database. |
| SYSLOG | Filters the types of Oracle GoldenGate messages that are written to the system logs. |
Manager parameters: Port management
| 名字 | 用途 |
| DYNAMICPORTLIST | Specifies the ports that Collector can dynamically allocate. |
| PORT | Establishes the TCP/IP port number on which Manager listens for requests. |
Manager parameters: Process management
| 名字 | 用途 |
| AUTORESTART | Specifies processes to be restarted by Manager after a |
| AUTOSTART | Specifies processes to be started when Manager starts. |
| BOOTDELAYMINUTES | Determines how long after system boot time Manager delays until performing main processing activities. This parameter supports Windows. |
| UPREPORT | Determines how often process heartbeat messages are reported. |
Manager parameters: Event management
| 名字 | 用途 |
| DOWNCRITICAL | Reports processes that stopped gracefully or abnormally. |
| DOWNREPORT | Controls the frequency for reporting stopped processes. |
| LAGCRITICAL | Specifies a lag threshold that is considered critical and generates a warning to the error log. |
| LAGINFO | Specifies a lag threshold at which an informational message is reported to the error log. |
| LAGREPORT | Sets an interval for reporting lag time to the error log. |
Manager parameters: Maintenance
| 名字 | 用途 |
| CHECKMINUTES | Determines how often Manager cycles through maintenance |
| PURGEDDLHISTORY | Purges rows from the Oracle DDL history table when they are no longer needed. |
| PURGEDDLHISTORYALT | Purges rows from the alternate Oracle DDL history table that keeps track of partition IDs that are associated with a table ID. |
| PURGEMARKERHISTORY | Purges Oracle marker table rows that are no longer needed. |
| PURGEOLDEXTRACTS | Purges trail data that is no longer needed. |
| PURGEOLDTASKS | Purges Extract and Replicat tasks after a specified period of time. |
| STARTUPVALIDATIONDELAY[CSECS] | Sets a delay time after which Manager checks that processes are still running after startup. |
2.10配置初始化数据进程
添加一个名为EINI_1 的Extract 进程：
GGSCI (localhost.localdomain) 8> add extract eini_1,sourceistable EXTRACT added. GGSCI (localhost.localdomain) 9> info extract *,tasks EXTRACT EINI_1 Initialized 2021-11-08 19:44 Status STOPPED Checkpoint Lag Not Available Log Read Checkpoint Not Available First Record Record 0 Task SOURCEISTABLE GGSCI (localhost.localdomain) 10> edit params eini_1 -- GoldenGate Initial Data Capture -- for SCOTT.TO_BASE EXTRACT EINI_1 SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) USERID OGG, PASSWORD OGG RMTHOST 192.168.1.102, MGRPORT 7809 RMTTASK REPLICAT, GROUP RINI_1 TABLE SCOTT.TO_BASE;
参数介绍：
EXTRACT EINI_1：说明这是EXTRACT进程，名字是EINI_1
SETENV：环境变量，一定要设置和数据库字符集一样，否则可能会乱码
USERID：数据库OGG用户
PASSWORD：数据库用户OGG的密码
RMTHOST：目标端地址，如果在/etc/hosts文件里已经设置解析，可以写主机名
MGRPORT：目标端MGR管理进程监听的端口
RMTTASK REPLICAT：目标端REPLICAT应用进程的组和名字
TABLE：源端要初始化数据的表的名字
编辑好捕获进程EINI_1后，还需要在目标端配置REPLICAT应用进程，名字要和源端的捕获进程EINI_1里面RMTTASK REPLICAT参数配置的一样，也就是还需要在目标端配置RMTTASK REPLICAT RINI_1。
2.11配置目标端REPLICAT进程
GGSCI (localhost.localdomain) 5> add replicat rini_1,specialrun REPLICAT added. GGSCI (localhost.localdomain) 6> info replicat *,tasks REPLICAT RINI_1 Initialized 2021-11-08 19:49 Status STOPPED Checkpoint Lag 00:00:00 (updated 00:00:04 ago) Log Read Checkpoint Not Available Task SPECIALRUN GGSCI (localhost.localdomain) 7> edit params rini_1 -- GoldenGate Initial Load Delivery REPLICAT RINI_1 SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) ASSUMETARGETDEFS USERID OGG, PASSWORD OGG DISCARDFILE ./dirrpt/RINIaa.dsc, PURGE MAP scott.*, TARGET scott.*;
2.12初始化数据
在源库中启动Initial Load 进程EINI_1，然后目标库中的RINI_1 进程将自动启动：
GGSCI (localhost.localdomain) 11> start extract eini_1 Sending START request to MANAGER ... EXTRACT EINI_1 starting GGSCI (localhost.localdomain) 12> info extract eini_1 EXTRACT EINI_1 Initialized 2021-11-08 19:44 Status RUNNING Checkpoint Lag Not Available Log Read Checkpoint Not Available First Record Record 0 Task SOURCEISTABLE
启动源端的捕获进程EINI_1后，正常情况下(如果配置没问题)，源端的数据已经传送到目标端了，可以通过VIEW命令查看源端捕获进程EINI_1的工作状态。
GGSCI (localhost.localdomain) 13> view report eini_1
最后看到两条测试数据已插入
Output to RINI_1: From Table SCOTT.TO_BASE: # inserts: 2 # updates: 0 # deletes: 0 # discards: 0 REDO Log Statistics Bytes parsed 0 Bytes output 222
亦可在目标库查看日志
GGSCI (localhost.localdomain) 9> view report rini_1
初始加载后，提取进程EINI_1 和复制进程RINI_1 自动停止，通常初始化数据工作只会做一次，可以通过INFO命令查看进程的状态。
源库
GGSCI (localhost.localdomain) 14> info extract eini_1 EXTRACT EINI_1 Last Started 2021-11-08 19:51 Status STOPPED Checkpoint Lag Not Available Log Read Checkpoint Table SCOTT.TO_BASE 2021-11-08 19:51:50 Record 2 Task SOURCEISTABLE
目标库
GGSCI (localhost.localdomain) 11> info replicat rini_1 REPLICAT RINI_1 Initialized 2021-11-08 19:49 Status STOPPED Checkpoint Lag 00:00:00 (updated 00:11:32 ago) Log Read Checkpoint Not Available Task SPECIALRUN
2.13源端配置捕获进程
GGSCI (localhost.localdomain) 15> edit params eora_1 -- Change Capture parameter file to capture SCOTT.TO_BASE changes EXTRACT EORA_1 SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) USERID ogg, PASSWORD OGG EXTTRAIL ./dirdat/la TABLE SCOTT.TO_BASE; GGSCI (localhost.localdomain) 16> view params eora_1 -- Change Capture parameter file to capture SCOTT.TO_BASE changes EXTRACT EORA_1 SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) USERID ogg, PASSWORD OGG EXTTRAIL ./dirdat/la TABLE SCOTT.TO_BASE;
2.14源库中执行以下命令以添加 Primary Extract 组
GGSCI (localhost.localdomain) 17> add extract eora_1,tranlog,begin now
EXTRACT added.
2.15源库定义 GoldenGate 本地路径
GGSCI (localhost.localdomain) 18> ADD EXTTRAIL ./dirdat/la, EXTRACT EORA_1, MEGABYTES 5
EXTTRAIL added.
2.16源库启动主 Extract 进程
GGSCI (localhost.localdomain) 19> start extract eora_1 Sending START request to MANAGER ... EXTRACT EORA_1 starting GGSCI (localhost.localdomain) 20> info extract eora_1 EXTRACT EORA_1 Last Started 2021-11-09 09:09 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:09 ago) Log Read Checkpoint Oracle Redo Logs 2021-11-09 09:16:00 Seqno 64, RBA 303616 SCN 0.788791 (788791) GGSCI (localhost.localdomain) 21> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EORA_1 00:00:00 00:00:04
启动 eora_1后在./dirdat/文件夹下生成la000000文件
[root@localhost ogg]# ls -ll dirdat/ total 4 -rw-rw-rw- 1 oracle oinstall 1470 Nov 9 09:10 la000000
2.17在源库中配置pump进程
GGSCI (localhost.localdomain) 22> edit params pora_1 -- Data Pump parameter file to read the local trail of SCOTT.TO_BASE changes EXTRACT PORA_1 SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) PASSTHRU RMTHOST 192.168.1.102, MGRPORT 7809 RMTTRAIL ./dirdat/ra TABLE SCOTT.TO_BASE;
2.18在源库添加数据泵提取组
GGSCI (localhost.localdomain) 23> add extract pora_1,exttrailsource ./dirdat/la EXTRACT added.
2.19在源库添加远程路径
GGSCI (localhost.localdomain) 24> add rmttrail ./dirdat/ra,extract pora_1,megabytes 5 RMTTRAIL added.
2.20在源库启动PUMP传输进程
GGSCI (localhost.localdomain) 25> start extract pora_1 Sending START request to MANAGER ... EXTRACT PORA_1 starting GGSCI (localhost.localdomain) 26> info extract pora_1 EXTRACT PORA_1 Last Started 2021-11-09 11:53 Status RUNNING Checkpoint Lag 01:46:28 (updated 00:00:01 ago) Log Read Checkpoint File ./dirdat/la000000 2021-11-09 10:07:12.000000 RBA 1866 GGSCI (localhost.localdomain) 27> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EORA_1 00:00:00 00:00:06 EXTRACT RUNNING PORA_1 00:00:00 00:00:00
2.21在目标库配置checkpoint表
GGSCI (localhost.localdomain) 13> edit params ./GLOBALS CHECKPOINTTABLE ogg.ggschkpt
GGSCI (localhost.localdomain) 14> exit
2.22在目标系统中添加复制检查点表
GGSCI (localhost.localdomain) 1> dblogin userid ogg,password OGG Successfully logged into database. GGSCI (localhost.localdomain) 2> add checkpointtable No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)... Successfully created checkpoint table ogg.ggschkpt.
2.23在目标库添加复制组
GGSCI (localhost.localdomain) 3> add replicat rora_1,exttrail ./dirdat/ra REPLICAT added.
2.24在目标库配置同步进程RORA_1
GGSCI (localhost.localdomain) 4> edit params rora_1 -- Change Delivery parameter file to apply SCOTT.TO_BASE Changes REPLICAT RORA_1 SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) USERID ogg, PASSWORD OGG HANDLECOLLISIONS ASSUMETARGETDEFS DISCARDFILE ./dirrpt/RORAaa.DSC, PURGE MAP SCOTT.TO_BASE, TARGET SCOTT.TO_BASE;
可通过view查看内容
GGSCI (localhost.localdomain) 5> view params rora_1 -- Change Delivery parameter file to apply SCOTT.TO_BASE Changes REPLICAT RORA_1 SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) USERID ogg, PASSWORD OGG HANDLECOLLISIONS ASSUMETARGETDEFS DISCARDFILE ./dirrpt/RORAaa.DSC, PURGE MAP SCOTT.TO_BASE, TARGET SCOTT.TO_BASE;
2.25启动复制过程
GGSCI (localhost.localdomain) 8> start replicat rora_1 Sending START request to MANAGER ... REPLICAT RORA_1 starting GGSCI (localhost.localdomain) 9> info replicat rora_1 REPLICAT RORA_1 Last Started 2021-11-09 13:55 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:08 ago) Log Read Checkpoint File ./dirdat/ra000000 First Record RBA 0 GGSCI (localhost.localdomain) 10> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RORA_1 00:00:00 00:00:01
此时在源库中对表SCOTT.TO_BASE的增删改操作都会同步到目标库
关机步骤
源库： GGSCI (localhost.localdomain) 1> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EORA_1 00:00:00 00:00:02 EXTRACT RUNNING PORA_1 00:00:00 00:00:00 GGSCI (localhost.localdomain) 2> stop extract eora_1 Sending STOP request to EXTRACT EORA_1 ... Request processed. GGSCI (localhost.localdomain) 3> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED EORA_1 00:00:00 00:00:03 EXTRACT RUNNING PORA_1 00:00:00 00:00:06 GGSCI (localhost.localdomain) 4> stop extract pora_1 Sending STOP request to EXTRACT PORA_1 ... Request processed. GGSCI (localhost.localdomain) 5> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED EORA_1 00:00:00 00:00:27 EXTRACT STOPPED PORA_1 00:00:00 00:00:02 GGSCI (localhost.localdomain) 6> stop mgr Manager process is required by other GGS processes. Are you sure you want to stop it (y/n)? y Sending STOP request to MANAGER ... Request processed. Manager stopped.
备库： GGSCI (localhost.localdomain) 1> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RORA_1 00:00:00 00:00:05 GGSCI (localhost.localdomain) 2> stop replicat rora_1 Sending STOP request to REPLICAT RORA_1 ... Request processed. GGSCI (localhost.localdomain) 3> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT STOPPED RORA_1 00:00:00 00:00:02 GGSCI (localhost.localdomain) 4> stop mgr Manager process is required by other GGS processes. Are you sure you want to stop it (y/n)? y Sending STOP request to MANAGER ... Request processed. Manager stopped.