ogg实现oracle-oracle-windows 级联复制

1,818 阅读8分钟

1.环境介绍

项目 环境1 环境2 环境2
操作系统 CentOS release 6.5 CentOS release 6.5 win7
IP地址 130.9.46.19 130.9.46.220 130.9.46.110
数据库及版本 ORACLE 11.2.0.1.0 ORACLE 11.2.0.1.0 ORACLE 11.2.0.1.0
数据库字符集 ZHS16GBK ZHS16GBK ZHS16GBK
ORACLE SID ora11 ora11 ora11
Goldengate用户 ogg ogg ogg
Goldengate 版本 122020 122020 122020

备注

  • 查看linux系统版本:cat /etc/redhat-release

  • 查看数据库字符集:``

  • oracle默认密码

    system默认:manager

    sys默认:change_on_install as sysdba

2.linux环境安装

linux系统如果没有图形界面的话,我们采用静默安装的方式

2.1 安装

  • 在oracle官网下载123014_fbo_ggs_Linux_x64_shiphome.zip(注意,ogg版本和oracle版本强绑定,下载时请注意版本的一致性),将zip压缩包拷贝到linux目录中
  • 创建目录
# 创建目录
[root@VTSXN-SHKF java]# mkdir -p /u01/install/ogg/
# 上传解压介质
[root@VTSXN-SHKF java]# unzip 123014_fbo_ggs_Linux_x64_shiphome.zip /u01/install/ogg/
# 进入目录
[root@VTSXN-SHKF java]# cd /u01/install/ogg/fbo_ggs_Linux_x64_shiphome/Disk1/
# 创建goldengate目录
[root@VTSXN-SHKF Disk1]# mkdir -p /u01/ogg/12.3.0/oggcore_1/
# root授权
[root@VTSXN-SHKF Disk1]# chown oracle.oinstall -R /u01/ogg/
# 启动OUI安装程序
[root@VTSXN-SHKF Disk1]# ./runInstaller 

如果说出现monitor报错

[oracle@VTSXN-SHKF Disk1]$ ./runInstaller 
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 34100 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 8015 MB    Passed
Checking monitor: must be configured to display at least 256 colors
 >>> Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set.    Failed <<<<

Some requirement checks failed. You must fulfill these requirements before

continuing with the installation,

解决方法

[oracle@VTSXN-SHKF Disk1]$ su
Password: 
[root@VTSXN-SHKF Disk1]# export DISPLAY=:0.0
[root@VTSXN-SHKF Disk1]# xhost +
access control disabled, clients can connect from any host
[root@VTSXN-SHKF Disk1]# su oracle
  • 采取静默安装
# 编辑响应文件
[root@VTSXN-SHKF Disk1]# vi /u01/install/ogg/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp

opyright(c) Oracle Corporation 2017. All rights reserved.     ##
##                                                                ##
## Specify values for the variables listed below to customize     ##
## your installation.                                             ##
##                                                                ##
## Each variable is associated with a comment. The comment        ##
## can help to populate the variables with the appropriate        ##
## values.                                                        ##
##                                                                ##
## IMPORTANT NOTE: This file should be secured to have read       ##
## permission only by the oracle user or an administrator who     ##
## own this installation to protect any sensitive input values.   ##
##                                                                ##
####################################################################

#-------------------------------------------------------------------------------
# Do not change the following system generated value.
#-------------------------------------------------------------------------------
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2


################################################################################
##                                                                            ##
## Oracle GoldenGate installation option and details                          ##
##                                                                            ##
################################################################################

#-------------------------------------------------------------------------------
# Specify the installation option.
# Specify ORA12c for installing Oracle GoldenGate for Oracle Database 12c and
#         ORA11g for installing Oracle GoldenGate for Oracle Database 11g
#-------------------------------------------------------------------------------
INSTALL_OPTION=ORA11g

#-------------------------------------------------------------------------------
# Specify a location to install Oracle GoldenGate
#-------------------------------------------------------------------------------
SOFTWARE_LOCATION=/u01/ogg/12.3.0/oggcore_1/

#-------------------------------------------------------------------------------
# Specify true to start the manager after installation.
#-------------------------------------------------------------------------------
START_MANAGER=

#-------------------------------------------------------------------------------
# Specify a free port within the valid range for the manager process.
# Required only if START_MANAGER is true.
#-------------------------------------------------------------------------------
MANAGER_PORT=

#-------------------------------------------------------------------------------
# Specify the location of the Oracle Database.
# Required only if START_MANAGER is true.
#-------------------------------------------------------------------------------
DATABASE_LOCATION=/home/app/oracle/product/11.2.0/dbhome_1


################################################################################
##                                                                            ##
## Specify details to Create inventory for Oracle installs                    ##
## Required only for the first Oracle product install on a system.            ##
##                                                                            ##
################################################################################

#-------------------------------------------------------------------------------
# Specify the location which holds the install inventory files.
# This is an optional parameter if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
INVENTORY_LOCATION=/u01/app/oraInventory

#-------------------------------------------------------------------------------
# Unix group to be set for the inventory directory.
# This parameter is not applicable if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
UNIX_GROUP_NAME=dba

请注意:UNIX_GROUP_NAME的内容通过id oracle就行查看

[root@VTSXN-SHKF Disk1]# id oracle uid=501(oracle) gid=5001(dba) groups=5001(dba)

进行安装

[oracle@VTSXN-SHKF Disk1]$  ./runInstaller -silent -responseFile /u01/install/ogg/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp 
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 32717 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 8015 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2019-06-06_01-53-09PM. Please wait ...

环境变量配置

[root@VTSXN-SHKF ~]# cat /etc/profile
# /etc/profile

# System wide environment and startup programs, for login setup
# Functions and aliases go in /etc/bashrc

# It's NOT a good idea to change this file unless you know what you
# are doing. It's much better to create a custom.sh shell script in
# /etc/profile.d/ to make custom changes to your environment, as this
# will prevent the need for merging in future updates.

pathmunge () {
    case ":${PATH}:" in
        *:"$1":*)
            ;;
        *)
            if [ "$2" = "after" ] ; then
                PATH=$PATH:$1
            else
                PATH=$1:$PATH
            fi
    esac
}


if [ -x /usr/bin/id ]; then
    if [ -z "$EUID" ]; then
        # ksh workaround
        EUID=`id -u`
        UID=`id -ru`
    fi
    USER="`id -un`"
    LOGNAME=$USER
    MAIL="/var/spool/mail/$USER"
fi

# Path manipulation
if [ "$EUID" = "0" ]; then
    pathmunge /sbin
    pathmunge /usr/sbin
    pathmunge /usr/local/sbin
else
    pathmunge /usr/local/sbin after
    pathmunge /usr/sbin after
    pathmunge /sbin after
fi

HOSTNAME=`/bin/hostname 2>/dev/null`
HISTSIZE=1000
if [ "$HISTCONTROL" = "ignorespace" ] ; then
    export HISTCONTROL=ignoreboth
else
    export HISTCONTROL=ignoredups
fi

export PATH USER LOGNAME MAIL HOSTNAME HISTSIZE HISTCONTROL

# By default, we want umask to get set. This sets it for login shell
# Current threshold for system reserved uid/gids is 200
# You could check uidgid reservation validity in
# /usr/share/doc/setup-*/uidgid file
if [ $UID -gt 199 ] && [ "`id -gn`" = "`id -un`" ]; then
    umask 002
else
    umask 022
fi

for i in /etc/profile.d/*.sh ; do
    if [ -r "$i" ]; then
        if [ "${-#*i}" != "$-" ]; then
            . "$i"
        else
            . "$i" >/dev/null 2>&1
        fi
    fi
done

unset i
unset -f pathmunge

JAVA_HOME=/usr/java/jdk1.8
ORACLE_HOME=/home/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID=ora11
LD_LIBRARY_PATH=$ORACLE_HOME/lib
PATH=$JAVA_HOME/bin:$ORACLE_HOME/bin:$PATH
CLASSPATH=$JAVA_HOME/jre/lib/ext:$JAVA_HOME/lib/tools.jar
export PATH JAVA_HOME CLASSPATH ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH

2.2 测试

创建ogg用户,赋予权限

create user ogg identified by ogg;
grant all privileges to ogg; 

登录

[oracle@VTSXN-SHKF oggcore_1]$ ./ggsci 

Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.4 OGGCORE_12.3.0.1.0_PLATFORMS_180415.0359_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 15 2018 21:16:09
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.

如果使用ogg登录出现以下问题:

GGSCI (VTSXN-SHKF) 2> dblogin userid ogg,password ogg

2019-06-06 10:39:44  WARNING OGG-25108  Failed to set the Oracle session tag: ORA-04060: insufficient privileges to execute DBMS_STREAMS.SET_TAG
ORA-06512: at "SYS.DBMS_STREAMS", line 16
ORA-06512: at line 1.
ERROR: Unable to connect to database using user ogg. Ensure that the necessary privileges are granted to the user.
OCI Error ORA (status = 942-ORA-00942: table or view does not exist

采取做法

grant dba to ogg;
grant all privileges to ogg;

ogg安装可能出现的问题

WARNING OGG-01988 Could not find schematrandata function in source database: failed to find function.

输入archive log list出现:ORA-01507: ??????

SQL> connect sys/admin as sysdba
Connected.
SQL> archive log list; 
ORA-01507: ??????
SQL> shutdown immediate;
ORA-01507: ??????


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1620115456 bytes
Fixed Size                  2213816 bytes
Variable Size            1291847752 bytes
Database Buffers          318767104 bytes
Redo Buffers                7286784 bytes
Database mounted.
SQL> alter database open;

Database altered.

SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1620115456 bytes
Fixed Size                  2213816 bytes
Variable Size            1291847752 bytes
Database Buffers          318767104 bytes
Redo Buffers                7286784 bytes
Database mounted.
Database opened.
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1973
Current log sequence           1975

如果要让OGG支持DDL操作,还需要额外运行几个脚本,这些脚本是OGG带的而不是ORACLE带的,在OGG的安装目录都可以找到,如果在OGG的安装目录登录数据库,可以直接@加脚本的名字,源端与目标端都需要运行,如下:

*** Could not open error log ggserr.log (error 13,Permission denied) ***

修改ggserr.log文件的组

chown oracle:dba ggserr.log

[oggcore_1]# ./ggsci ./ggsci: error while loading shared libraries: libnnz11.so: cannot open shared object file: No such file or directory

export LD_LIBRARY_PATH=/home/app/oracle/product/11.2.0/dbhome_1/lib

oracle默认sys密码

connect sys/change_on_install as sysdba

查看数据库字符集

SELECT * from nls_database_parameters

归档日志爆了

[oracle@VTSXN-SHKF dbhome_1]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Aug 10 15:34:25 2019

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> connect /as sysdba
Connected to an idle instance.
SQL> create pfile from spfile
  2  ;

File created.

SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1620115456 bytes
Fixed Size                  2213816 bytes
Variable Size            1342179400 bytes
Database Buffers          268435456 bytes
Redo Buffers                7286784 bytes
Database mounted.
SQL> show parameter db_recovery_file_dest_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 3882M
SQL> select * from v$flash_recovery_area_usage f where f.file_type='ARCHIVE_LOG';

no rows selected

SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES
---------------
CONTROL FILE                          0                         0
              0

REDO LOG                              0                         0
              0

ARCHIVED LOG                      99.01                         0
             96


FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES
---------------
BACKUP PIECE                          0                         0
              0

IMAGE COPY                            0                         0
              0

FLASHBACK LOG                         0                         0
              0


FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES
---------------
FOREIGN ARCHIVED LOG                  0                         0
              0


7 rows selected.

SQL> select * from v$flash_recovery_area_usage f where f.file_type='ARCHIVED_LOG'
  2  ;

no rows selected

SQL> select * from v$flash_recovery_area_usage f where f.file_type='ARCHIVED LOG';

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES
---------------
ARCHIVED LOG                      99.01                         0
             96




3.windows环境安装

傻瓜式的安装,一步步进行操作即可

4.进程配置

ogg相关进程简单介绍

Oracle GoldenGate主要由如下组件组成

● Extract

● Data pump

● Trails

● Collector

● Replicat

● Manager

Oracle GoldenGate 数据复制过程如下:

利用抽取进程(Extract Process)在源端数据库中读取Online Redo Log或者Archive Log,然后进行解析,只提取其中数据的变化信息,比如DML操作——增、删、改操作,将抽取的信息转换为GoldenGate自定义的中间格式存放在队列文件(trail file)中。再利用传输进程将队列文件(trail file)通过TCP/IP传送到目标系统。

目标端有一个进程叫Server Collector,这个进程接受了从源端传输过来的数据变化信息,把信息缓存到GoldenGate 队列文件(trail file)当中,等待目标端的复制进程读取数据。

GoldenGate 复制进程(replicat process)从队列文件(trail file)中读取数据变化信息,并创建对应的SQL语句,通过数据库的本地接口执行,提交到目标端数据库,提交成功后更新自己的检查点,记录已经完成复制的位置,数据的复制过程最终完成。

注意:

  • 编辑GLOBALS参数文件,切换到GoldenGate安装目录下,执行命令:

GGSCI>EDIT PARAMS ./GLOBALS

在文件中添加以下内容(可选):

  • 利用默认的密钥,生成密文:

GGSCI>encrypt password ogg encryptkey default

Encrypted password: AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB

​ 记录这个密文,将在以下进程参数的配置中使用。

4.1 源端添加表级的TRANDATA

添加表级的trandata可以理解为需要将哪些用户的哪些表和目标库同步,其实也是添加表级的supplemental log,但是只有上文打开的minimal supplemental log后,这个才生效。

使用OGG用户从OGG登录源端数据库。

GGSCI (dbdream) 2> DBLOGIN USERID ogg, PASSWORD ogg

Successfully logged into database.

源端添加表级TRANDATA,本案例使用EMP_OGG和DEPT_OGG表。

GGSCI (dbdream) 3> add trandata scott.emp_ogg

Logging of supplemental redo data enabled for table SCOTT.EMP_OGG.

GGSCI (dbdream) 4> add trandata scott.dept_ogg

Logging of supplemental redo data enabled for table SCOTT.DEPT_OGG.

成功添加表级TRANDATA后,可以通过INFO命令查看哪些表被添加了TRANDATA.

GGSCI (dbdream) 5> INFO TRANDATA scott.*

Logging of supplemental redo log data is disabled for table SCOTT.BONUS.

Logging of supplemental redo log data is disabled for table SCOTT.DEPT.

Logging of supplemental redo log data is enabled for table SCOTT.DEPT_OGG.

Columns supplementally logged for table SCOTT.DEPT_OGG: DEPTNO.

Logging of supplemental redo log data is disabled for table SCOTT.EMP.

Logging of supplemental redo log data is enabled for table SCOTT.EMP_OGG.

Columns supplementally logged for table SCOTT.EMP_OGG: SAL, HIREDATE, MGR, COMM, DEPTNO, ENAME, EMPNO, JOB.

Logging of supplemental redo log data is disabled for table SCOTT.SALGRADE.

4.2 配置MGR管理进程

源端:

GGSCI (dbdream) 8> EDIT PARAMS MGR

# 加三行内容
PORT 7809
PURGEOLDEXTRACTS /ogg/dirdat, USECHECKPOINTS
userid ogg, password AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB, ENCRYPTKY default

目标端:

GGSCI (stream) 2> EDIT PARAMS MGR

# 加入以下三行内容
PORT 7809
PURGEOLDEXTRACTS /ogg/dirdat, USECHECKPOINTS
userid ogg, password AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB, ENCRYPTKY default

参数说明:

PORT 7809:OGG管理进程监控端口。

PURGEOLDEXTRACTS:清除不需要的trail文件。

/ogg/dirdat:trail文件存放位置。

USECHECKPOINTS:使用检查点队列。

password:之前加密过的密码

本实验只用到上述参数,MGR其他参数详见下表,摘自OGG官方文档。

Manager parameters: General

名字 用途
CHARSET Specifies a multibyte character set for the process to usinstead of the operating system default when reading theparameter 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 toaccess the database.
SYSLOG Filters the types of Oracle GoldenGate messages that arewritten 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 listensfor 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 delaysuntil performing main processing activities. This parametersupports 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 andgenerates a warning to the error log.
LAGINFO Specifies a lag threshold at which an informational messageis 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 areno longer needed.
PURGEDDLHISTORYALT Purges rows from the alternate Oracle DDL history table thatkeeps track of partition IDs that are associated with atable 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 periodof time.
STARTUPVALIDATIONDELAY[CSECS] Sets a delay time after which Manager checks that processesare still running after startup.

配置完MGR管理进程后,就可以启动MGR管理进程(源端和目标端都需要启动)。

GGSCI (dbdream) 9> start mgr
Manager started.

GGSCI (dbdream) 9> start mgr

Manager started.

可以通过INFO命令查看进程的状态(最好每次启动时在两端都查看下)。

GGSCI (dbdream) 10> info mgr

Manager is running (IP port dbdream.7809).

4.3 配置初始化进程

4.3.1 源端extract进程配置

源端的测试表有数据,而目标端的测试表只有结构,没有数据,所以需要初始化目标端的数据,所谓初始化,就是让目标端的数据和源端的数据在这个时间点是一模一样的,所以初始化工作并不需要一定使用OGG,也可以使用EXP、EXPDP、SQLLOAD等其他工具,本文主要介绍如何使用OGG进行数据初始化,下面在源端配置捕获进程EINI_1。

GGSCI (dbdream) 11> ADD EXTRACT EINI_1, SOURCEISTABLE

EXTRACT added.

查看EINI_1进程状态。

GGSCI (dbdream) 12> INFO EXTRACT *, TASKS

EXTRACT    EINI_1    Initialized   2012-11-13 22:45   Status STOPPED

Checkpoint Lag       Not Available

Log Read Checkpoint  Not Available

First Record         Record 0

Task                 SOURCEISTABLE

由于只是添加了捕获进程EINI_1,还没有进行配置和启动这个进程,所以现在的状态是STOPPED状态。

源端编辑捕获进程EINI_1。

GGSCI (dbdream) 13> EDIT PARAMS EINI_1

–加入以下内容

EXTRACT EINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

RMTHOST 192.168.249.99, MGRPORT 7809

RMTTASK REPLICAT, GROUP RINI_1

TABLE scott.EMP_OGG;

TABLE scott.DEPT_OGG;

参数介绍:

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。

4.3.2 目标端replicat配置

目标端:

GGSCI (stream) 7> EDIT PARAMS RINI_1

–加入以下内容

REPLICAT RINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

ASSUMETARGETDEFS

USERID ogg, PASSWORD ogg

DISCARDFILE /ogg/dirrpt/RINIaa.dsc, PURGE

MAP scott., TARGET scott.;

参数介绍:

REPLICAT RINI_1:说明这是REPLICAT应用进程,名字叫RINI_1

SETENV:语言变量,同捕获进程EINI_1

ASSUMETARGETDEFS:告诉OGG目标端和源端需要同步的表的结构完全一致,不需要OGG去检查表的结构,包括表名、字段名、字段类型、字段长度等,如果目标端和源端同步的表的结构不一样,需要使用SOURCEDEFS参数,详见OGG官方文档。

USERID、PASSWORD:同捕获进程EINI_1参数介绍

DISCARDFILE:错误信息存放位置及命名规则

MAP:源端捕获的表的名字

TARGET:目标端同步的表的名字,可以不在同一SCHEMA。

4.3.3 初始化数据

配置好目标端的应用进程RINI_1后,就可以启动源端的捕获进程进行捕获数据了,而目标端的应用进程RINI_1不需要手动去启动,也就是说目标端RINI_1进程不需要管。

GGSCI (dbdream) 14> START EXTRACT EINI_1

Sending START request to MANAGER …

EXTRACT EINI_1 starting

启动源端的捕获进程EINI_1后,正常情况下(如果配置没问题),源端的数据已经传送到目标端了,可以通过VIEW命令查看源端捕获进程EINI_1的工作状态。

GGSCI (dbdream) 11> VIEW REPORT EINI_1

–在最后部分会看到

Output to RINI_1:

From Table SCOTT.EMP_OGG:

# inserts: 14

# updates: 0

# deletes: 0

# discards: 0

From Table SCOTT.DEPT_OGG:

# inserts: 4

# updates: 0

# deletes: 0

# discards: 0

REDO Log Statistics

Bytes parsed 0

Bytes output 3056

如果配置正确,会看到上面的日志,日志会告诉你在目标端SCOTT.EMP_OGG表INSERT了14条记录,SCOTT.DEPT_OGG表INSERT了4条记录。如果在上面日志的最后部分出现ERROR,就需要去检查OGG的安装目录下的ggserr.log日志,这个日志相当于数据库的告警日志。

4.3.4 验证初始化数据

登录目标端数据库,查看初始化是否成功。

初始化之后,上文提到的初始化进程(EINI_1、RINI_1)自动停止,因为通常情况下初始化数据工作只会做一次。可以通过INFO命令查看进程的状态。

源端:

GGSCI (dbdream) 9> INFO EXTRACT EINI_1

EXTRACT EINI_1 Last Started 2012-11-14 02:03 Status STOPPED

Checkpoint Lag Not Available

Log Read Checkpoint Table SCOTT.DEPT_OGG

2012-11-14 02:03:22 Record 4

Task SOURCEISTABLE

目标端:

GGSCI (stream) 1> INFO REPLICAT RINI_1

REPLICAT RINI_1 Initialized 2012-11-14 20:30 Status STOPPED

Checkpoint Lag 00:00:00 (updated 03:16:37 ago)

Log Read Checkpoint Not Available

Task SPECIALRUN

4.4 配置检查点

目的:为了让ogg在网络中断、服务器宕机、断电的情况下能够正确断电续传

源端和目标端都需配置。

GGSCI (stream) 2> EDIT PARAMS ./GLOBALS

–加入以下信息

CHECKPOINTTABLE ogg.ggschkpt

这就告诉OGG检查点存放到OGG用户下的GGSCHKPT表中,但是还需要使用OGG用户登录数据库,创建检查点表,此时需要退出OGG,重新登录,否则可能会遇到下面的错误。

GGSCI (stream) 4> ADD CHECKPOINTTABLE

ERROR: Missing checkpoint table specification.

退出OGG,重新登录,并使用OGG用户登录数据库,源端和目标端都需要执行。

GGSCI (stream) 5> exit

[ogg@stream ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (stream) 1> dblogin userid ogg,password ogg

Successfully logged into database.

只需要执行ADD CHECKPOINTTABLE命令,OGG会自动在ORACLE的OGG用户下创建检查点,源端和目标端都需要执行。

GGSCI (stream) 2> ADD CHECKPOINTTABLE

No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)…

Successfully created checkpoint table ogg.ggschkpt.

此时用OGG登录数据库,就可以看到OGG创建的检查点表。

4.5 源端extract进程

下面在源端配置捕获进程。

GGSCI (dbdream) 10> EDIT PARAMS EORA_1

–加入以下内容

EXTRACT EORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

EXTTRAIL /ogg/dirdat/aa

TABLE scott.EMP_OGG;

参数介绍:

以上参数基本都在上文提过,这里不在重复,说下EXTTRAIL参数,EXTTRAIL参数是TRAIL队列文件存放的路径和命名格式,TRAIL文件可以理解为存放捕获进程捕获的日志文件。此时还需要将捕获进程EORA_1添加到OGG。

GGSCI (dbdream) 11> ADD EXTRACT EORA_1, TRANLOG, BEGIN NOW

EXTRACT added.

GGSCI (dbdream) 12> ADD EXTTRAIL /ogg/dirdat/aa, EXTRACT EORA_1, MEGABYTES 5

EXTTRAIL added.

上面的两个命名告诉OGG,捕获进程从启动起开始捕获,捕获数据保存到TRAIL文件,及TRAIL文件的路径、命名格式,单个TRAIL文件最大大小。

现在就可以启动源端捕获进程EORA_1了。

GGSCI (dbdream) 13> START EXTRACT EORA_1

Sending START request to MANAGER …

EXTRACT EORA_1 starting

使用INFO命令查看EORA_1进程是否是RUNNING状态。

GGSCI (dbdream) 14> INFO EXTRACT EORA_1

EXTRACT EORA_1 Last Started 2012-11-14 02:06 Status RUNNING

Checkpoint Lag 00:00:58 (updated 00:00:08 ago)

Log Read Checkpoint Oracle Redo Logs

2012-11-14 02:05:58 Seqno 19, RBA 38097424

SCN 0.0 (0)

4.6 源端pump进程

不配置传输进程,OGG会通过EXTRACT进程传输TRAIL队列文件,但是和检查点队列一样,为了保证断点续传ORACLE建议配置PUMP传输进程。

GGSCI (dbdream) 15> EDIT PARAMS PORA_1

–加入以下内容

EXTRACT PORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

PASSTHRU

RMTHOST 192.168.249.99, MGRPORT 7809

RMTTRAIL /ogg/dirdat/pa

TABLE scott.EMP_OGG;

TABLE scott.DEPT_OGG;

添加PUMP进程PORA_1到OGG,并指定本地的TRAIL文件。

GGSCI (dbdream) 16> ADD EXTRACT PORA_1, EXTTRAILSOURCE /ogg/dirdat/aa

EXTRACT added.

此时用INFO命令可以看到PUMP进程PORA_1的状态。

GGSCI (dbdream) 17> INFO EXTRACT PORA_1

EXTRACT PORA_1 Initialized 2012-11-14 02:10 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:00:10 ago)

Log Read Checkpoint File /ogg/dirdat/aa000000

First Record RBA 0

为PUMP进程PORA_1指定将本地TRAIL文件传输到目标端后保存成目标端TRAIL文件的名字。

GGSCI (dbdream) 18> ADD RMTTRAIL /ogg/dirdat/pa, EXTRACT PORA_1, MEGABYTES 5

RMTTRAIL added.

下面就可以启动PUMP进程PORA_1了,启动后看下PORA_1的状态。

GGSCI (dbdream) 19> START EXTRACT PORA_1

Sending START request to MANAGER …

EXTRACT PORA_1 starting

–查看状态

GGSCI (dbdream) 20> INFO EXTRACT PORA_1

EXTRACT PORA_1 Last Started 2012-11-14 02:11 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:03 ago)

Log Read Checkpoint File /ogg/dirdat/aa000000

First Record RBA 0

此时在目标端/ogg/dirdat/目录下,就会看到由源端PUMP进程PORA_1传输过来的TRAIL文件。

[ogg@stream dirdat]$ pwd

/ogg/dirdat

[ogg@stream dirdat]$ ls

pa000000

[ogg@stream dirdat]$

4.7 目标端同步进程REPLICAT

GGSCI (stream) 5> ADD REPLICAT RORA_1, SPECIALRUN

REPLICAT added.

目标端编辑RORA_1进程。

GGSCI (stream) 7> EDIT PARAMS RORA_1

–加入以下内容

REPLICAT RORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE /ogg/dirrpt/RORA_aa.DSC, PURGE

MAP scott.emp_ogg, TARGET scott.emp_ogg;

目标端编辑同步进程RORA_1后,就可以启动RORA_1进程。

GGSCI (stream) 5> START REPLICAT RORA_1

Sending START request to MANAGER …

REPLICAT RORA_1 starting

启动RORA_1进程后,查看进程状态。

GGSCI (stream) 6> INFO REPLICAT RORA_1=

REPLICAT RORA_1 Last Started 2012-11-15 00:08 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:05 ago)

Log Read Checkpoint File /ogg/dirdat/pa000000

First Record RBA 0

5.总结

ogg的安装过程相当麻烦,可能会遇到各种各样的问题,出现这些问题时,多去网上搜索官方文档,查看官网介绍,能够更快解决自己的问题。

国内各个博客存在说明不清晰,操作遗漏等各种问题,不可轻信

6.参考文档

  1. ogg oracle官网下载
  2. ogg oracle官网文档
  3. 『OGG 01』Win7 配置 Oracle GoldenGate 踩坑指南
  4. OGG基础知识整理-云栖社区-阿里云
  5. Oracle Golden Date(ogg)的搭建和管理
  6. 利用OGG实现Oracle数据库到MySQL数据库的单向同步

觉得不错,请点个赞吧

zan