ITPub博客

首页 > Linux操作系统 > Linux操作系统 > RAC + GG

RAC + GG

原创 Linux操作系统 作者:wshxgxiaoli 时间:2012-07-05 13:55:47 0 删除 编辑



1.环境说明:
rac 双节点:
10.10.10.31
10.10.10.32
单实例:10.10.10.88
ORACLE 10.2.0.1

2.配置时间同步:  严格要求时间进行同步。
RAC1:
vi /etc/ntp.conf
restrict default nomodify
restrict -6 default kod nomodify notrap nopeer noquery

restrict 127.0.0.1
restrict -6 ::1


server 0.centos.pool.ntp.org
server 1.centos.pool.ntp.org
server 2.centos.pool.ntp.org


server  10.10.10.31
fudge   127.127.1.0 stratum 10

driftfile /var/lib/ntp/drift
broadcastdelay 0.008

keys /etc/ntp/keys

启动NTP服务:
service ntpd restart
chkconfig --level 345 ntpd on

RAC2:
restrict default kod nomodify notrap nopeer noquery
restrict -6 default kod nomodify notrap nopeer noquery

restrict 127.0.0.1
restrict -6 ::1


server 0.centos.pool.ntp.org
server 1.centos.pool.ntp.org
server 2.centos.pool.ntp.org


server  10.10.10.31
fudge   10.10.10.31 stratum 10

driftfile /var/lib/ntp/drift
broadcastdelay 0.008

keys /etc/ntp/keys

启动NTPD服务:
service ntpd restart
chkconfig --level 345 ntpd on
需要注意的是,配置完ntp后大概需要5-10分钟的时候,才会开始进行同步。可以通过ntpq -p 命令来查看
[root@rac1 ~]# ntpq -p
     remote           refid      st t when poll reach   delay   offset  jitter
==============================================================================
 rac1-priv       .INIT.          16 u    - 1024    0    0.000    0.000   0.000
*LOCAL(0)        .LOCL.          10 l  172   64  377    0.000    0.000   0.002

[root@rac2 ~]# ntpq -p
     remote           refid      st t when poll reach   delay   offset  jitter
==============================================================================
 rac1-priv       LOCAL(0)        11 u  124   64  377    0.001  -78225. 86383.5

在单实例节点上做:
ntpdate 10.10.10.31
并在CRONTAB里写好计划任务:
*/1 * * * * /usr/sbin/ntpdate 10.10.10.31

3.准备环境。
格式化我们刚刚为GG建的共享磁盘。
在rac1上做: fdisk /dev/sdg
然后再执行: mkfs.ext3 /dev/sdg1
mkdir -p /opt/gg  只有这一步也同时在三个点都执行
mount /dev/sdg1 /opt/gg
echo "mount /dev/sdg1 /opt/gg" >> /etc/rc.local
给三个节点上的/opt/gg 目录权限:
chown -R oracle:oinstall /opt/gg
chmod -R 775 /opt/gg
在RAC1和TARGET端建GG目录,然后将文件解压至/opt/gg/goldengate  用ORACLE 用户操作
cd /opt/gg
mkdir goldengate
tar -xzvf gg.tar.gz
配置环境变量:
RAC1:
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin
export PS1="`/bin/hostname -s `-> "
export ORACLE_SID=devdb1
export ORACLE_BASE=/home/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORA_CRS_HOME=$ORACLE_BASE/product/10.2.0/crs_1
export GG_HOME=/opt/gg/goldengate
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GG_HOME:/usr/lib
export PATH=$ORA_CRS_HOME/bin:$ORACLE_HOME/bin:$PATH

export PATH

RAC2:
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin
export PS1="`/bin/hostname -s `-> "
export ORACLE_SID=devdb2
export ORACLE_BASE=/home/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORA_CRS_HOME=$ORACLE_BASE/product/10.2.0/crs_1
export GG_HOME=/opt/gg/goldengate
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GG_HOME:/usr/lib
export PATH=$ORACLE_HOME/bin:$ORA_CRS_HOME/bin:$PATH

export PATH

单实例:
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin
export ORACLE_SID=orcl
export ORACLE_BASE=/home/oracle
export ORACLE_HOME=/home/oracle/product/10.2.1/db_1
export GG_HOME=/opt/gg/goldengate
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GG_HOME:/usr/lib
export PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin

export PATH

在三节点都执行 source .bash_profile
然后进行环境检查:
RAC1:
rac1-> cd /opt/gg/goldengate/
rac1-> ldd ggsci
        linux-gate.so.1 =>  (0x006be000)
        libdl.so.2 => /lib/libdl.so.2 (0x00b1b000)
        libicui18n.so.38 => ./libicui18n.so.38 (0x007fe000)
        libicuuc.so.38 => ./libicuuc.so.38 (0x00b2c000)
        libicudata.so.38 => ./libicudata.so.38 (0xb743a000)
        libpthread.so.0 => /lib/libpthread.so.0 (0x00110000)
        libnnz10.so => /home/oracle/product/10.2.0/db_1/lib/libnnz10.so (0x0047c000)
        libclntsh.so.10.1 => /home/oracle/product/10.2.0/db_1/lib/libclntsh.so.10.1 (0x00c4d000)
        libstdc++.so.6 => /usr/lib/libstdc++.so.6 (0x001a1000)
        libm.so.6 => /lib/libm.so.6 (0x00af2000)
        libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x00127000)
        libc.so.6 => /lib/libc.so.6 (0x009ac000)
        /lib/ld-linux.so.2 (0x00989000)
        libnsl.so.1 => /lib/libnsl.so.1 (0x00133000)
单实例:
[oracle@localhost ~]$ cd /opt/gg/goldengate/
[oracle@localhost goldengate]$ ldd ggsci
        linux-gate.so.1 =>  (0x0048e000)
        libdl.so.2 => /lib/libdl.so.2 (0x00a51000)
        libicui18n.so.38 => ./libicui18n.so.38 (0x00110000)
        libicuuc.so.38 => ./libicuuc.so.38 (0x00a90000)
        libicudata.so.38 => ./libicudata.so.38 (0xb74e0000)
        libpthread.so.0 => /lib/libpthread.so.0 (0x00a57000)
        libnnz10.so => /home/oracle/product/10.2.1/db_1/lib/libnnz10.so (0x00d09000)
        libclntsh.so.10.1 => /home/oracle/product/10.2.1/db_1/lib/libclntsh.so.10.1 (0x00f0d000)
        libstdc++.so.6 => /usr/lib/libstdc++.so.6 (0x00271000)
        libm.so.6 => /lib/libm.so.6 (0x00a28000)
        libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x00cd6000)
        libc.so.6 => /lib/libc.so.6 (0x008e2000)
        /lib/ld-linux.so.2 (0x008bf000)
        libnsl.so.1 => /lib/libnsl.so.1 (0x007f1000)

4.数据库环境准备:
源库必须于归档模式。必须为FORCELOGGIN, 打开supplemental_log
如何修改归档这里就不说了。
查询数据库是否为FORCELOGGIN的状态。
SQL> select force_logging from v$database;

FOR
---
NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FOR
---
YES

查询数据库是否打开supplemental_log
SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
NO

SQL> alter database add supplemental log data;

Database altered.


切换日志,使更改生效
SQL> alter system switch logfile;

System altered.

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES

在源端关闭数据库回收站:
官方的说明是,由于一个已知的问题,回收站会对DDL触发器产生影响,因此需要关闭。由此可见,我们只需要在源库中关闭回收站即可。

SQL> show parameter recyclebin

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on


SQL> alter system set recyclebin=off;

System altered.

SQL> show parameter recyclebin

NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
recyclebin string
OFF

确保GOLDENGATE能够连接到数据库的ASM实例(源端RAC两节点)
在两节点的TNSNAMES.ORA中添加以下内容:
ORADB_ASM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = +ASM)
        (UR=A)
    )
  )
测试连通:
rac1-> sqlplus sys/admin@ORADB_ASM as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 28 18:21:48 2012

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL>

字符集: 目标数据库的字符集必须是源数据库字符集的超集
             数据库字符集必须为客户端应用程序字符集的超集
RAC:
SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16CGB231280

单实列数据库:
SQL> SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16CGB231280
字符集一样,则不需要修改, 如不一样(可修改, 但不知道方法,求高手指教!), 一定要保持和源库一样。

创建GOLDENGATE用户:
RAC:
create tablespace goldengate;
create user goldengate identified by goldengate default tablespace goldengate;
grant dba to goldengate;
grant execute on utl_file to goldengate;

单实例:
create tablespace goldengate datafile'/home/oracle/oradata/orcl/gg01.dbf' size 1g;
create user goldengate identified by goldengate default tablespace goldengate;
grant dba to goldengate;
grant execute on utl_file to goldengate;

UNDO设置
goldengate使用flashback query从源数据库中读取undo表空间中的数据,以重建基于scn或时间点的读一致性。


建议设置如下:
UNDO_MANAGEMENT=AUTO
UNDO_RETENTION=86400
undo表空间的大小按如下公式估计设置
= * +
is the number of undo blocks.
is the value of the UNDO_RETENTION parameter (in seconds).
is the number of undo blocks for each second.
is the minimal overhead for metadata (transaction tables, etc.).
Use the system view V$UNDOSTAT to estimate and .

该步骤在源端数据库执行即可


SQL> show parameter undo


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1


SQL> alter system set undo_retention=86400;


System altered.

5.安装DDL_OBJECTS
在源端,以oracle用户登录sqlplus,执行以下脚本


执行marker_setup

确保goldengate的相关进程 关闭状态,任何使用oracle的应用程序都已关闭,且不会有新的会话产生。然后执行下面命令

cd /opt/gg/goldengate/
sqlplus / as sysdba

SQL> @marker_setup

Marker setup script

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:goldengate

……

Script. complete.

SQL>


执行ddl_setup

确保所有的会话都已关闭

SQL> @ddl_setup

GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...
Checking user sessions...

Check complete.

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:goldengate

You will be prompted for the mode of installation.
To install or reinstall DDL replication, enter INITIALSETUP
To upgrade DDL replication, enter NORMAL
Enter mode of installation:INITIALSETUP

Working, please wait ...
Spooling to file ddl_setup_spool.txt

Using GOLDENGATE as a GoldenGate schema name, INITIALSETUP as a mode of installation.

Working, please wait ...

RECYCLEBIN must be empty.
This installation will purge RECYCLEBIN for all users.
To proceed, enter yes. To stop installation, enter no.

Enter yes or no:yes

……

Script. complete.
SQL>


执行role_setup

创建一个名为ggs_ggsuser_role的角色,包含了ddl objects需要的权限
SQL> @role_setup

GGS Role setup script

This script. will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script. and then edit the params.sql script. to change the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:goldengate
Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.

Role setup script. complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO

where is the user assigned to the GoldenGate processes.
SQL>


将创建的角色授权给goldengate用户

SQL> grant ggs_ggsuser_role to goldengate;

Grant succeeded.

SQL>


启用DDL触发器

SQL> @ddl_enable

Trigger altered.

SQL>


安装可选的性能工具

安装dbms_shared_pool包

如果系统中不存在dbms_shared_pool包,则手动执行脚本安装。如下

SQL> select object_name,object_type from dba_objects where object_name='DBMS_SHARED_POOL';

no rows selected

SQL> @?/rdbms/admin/dbmspool

Package created.

Grant succeeded.

View created.

Package body created.

SQL> select object_name,object_type from all_objects where object_name='DBMS_SHARED_POOL';

OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
DBMS_SHARED_POOL PACKAGE
DBMS_SHARED_POOL PACKAGE BODY

2 rows selected.


ddl_pin


ddl_pin将触发器用到的plsql包放进内存中

SQL> @ddl_pin goldengate

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL>

6.配置GOLDENGATE
创建GOLDENGATE工作目录:(在RAC1 和目标库分别执行)
cd $GG_HOME
./ggsci
create subdirs

创建TRAIL文件存放目录:(源(RAC1)和目标端)
mkdir /opt/gg/trails

配置MANAGER:(源端(RAC1)和目标端)
DYNAMICPORTLIST中配置了GoldenGate(extract和replicat)进程使用的端口范围
PORT参数指定MANAGER使用的端口
AUTORESTART参数使抽取/复制进程失败后自动重启
配置MANAGER的参数,PURGEOLDEXTRACTS参数指定:当根据checkpoint发现已经完成抽取和复制的trail文件将被自动删除,但保留最近10个。
PURGEDDLHISTORY和PURGEMARKERHISTORY分别删除DDL历史表和marker表中的过期数据,以控制它们不会变得过于庞大。

edit params mgr(源端(RAC1)和目标端一样)

DYNAMICPORTLIST 7840-7914
PORT 5898
PURGEOLDEXTRACTS /opt/gg/trails/w1*, USECHECKPOINTS, MINKEEPFILES 10
PURGEOLDEXTRACTS /opt/gg/trails/w2*, USECHECKPOINTS, MINKEEPFILES 10
AUTORESTART ER *, RETRIES 3, WAITMINUTES 5
PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30
PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30

全局参数设置:
源端(RAC1)
edit params ./globals

GGSCHEMA goldengate

目标端:
创建一个checkpoint表
replicat通过这个表来维护trail文件中的read position。这不是个必须的操作,如果没有这个表,则通过一个磁盘文件来维护

GGSCI (localhost.localdomain) 2> dblogin userid goldengate,password goldengate
Successfully logged into database.

GGSCI (localhost.localdomain) 3> add checkpointtable goldengate.chkpoint

Successfully created checkpoint table GOLDENGATE.CHKPOINT.

edit params ./globals

GGSCHEMA goldengate
CHECKPOINTTABLE goldengate.chkpoint

配置源端EXTRACT
为了避免primary extract受到网络的影响,我们在源端和目标端之间增加一个data pump,这样的话,primary extract负责将数据从源数据中抽取出来,存在本地的trail文件中,然后data pump进程负责将本地trail文件中的数据传输到目标端的trail文件里。这样能提高更高的灵活性和可用性(当源和目标端之间的网络出现故障时,primary extract会继续抽取数据存到本地的trail中)
我们这里要同步2个用户下的所有表:hr、scott

首先添加两个primary extract group,因为这是一个两节点的RAC,我们需要指定threads 2选项
GGSCI (rac1) 4> add extract w1ext,tranlog,threads 2,begin now

EXTRACT added.

GGSCI (rac1) 5> info all

Program Status Group Lag Time Since Chkpt

MANAGER STOPPED
EXTRACT STOPPED DYEXT 00:00:00 00:00:07

编辑w1ext的参数文件:

1. EXTTRAIL参数指定该抽取进程对应的exttrail
2. DISCARDFILE参数指定一个文件,用来记录不能正常处理的记录,这里使用追加方式,最大为5MB
3. TRANLOGOPTIONS ALTARCHIVELOGDEST指定源数据库归档所在的路径。如果不确定,使用该SQL*Plus命令:show parameter log_archive_dest_1
4. TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT指定了归档文件的命名格式,可以通过SQL*Plus命令show parameter log_archive_format 来确定该格式
5. 由于源数据使用了ASM,这里通过tranlogoptions asmuser来提供登录ASM实例的用户名和密码(用户名必须是SYS)
6. DDL INCLUDE MAPPED表示只捕获MAPPED范围内的DDL操作
7. DDLOPTIONS ADDTRANDATA :当创建新的表时,自动为其启用追加日志
8. FETCHOPTIONS:MISSINGROW REPORT表示当extract需要获取的行在源库中无法定位时,extract进程继续运行,相关的错误信息会保存在discardfile参数指定的文件中;USESNAPSHOT表示extract使用flashback查询来从undo从获取一些数据,比如无法从redo中直接获取的UDT、嵌套表、XMLtype以及9i中的LOB;NOUSELATESTVERSION使得extract当无法从undo中获取数据时,忽略该条件而不是从源表中获取当前值。
9. STATOPTIONS REPORTFETCH:使用ggsci命令stats时,显示获取的行的统计信息
10. WARNLONGTRANS 1H, CHECKINTERVAL 5M:当发现超过1个小时的长事务时,会在错误日志中产生一条warning,5分钟检测一次

edit params w1ext

EXTRACT w1ext
USERID goldengate, PASSWORD goldengate
EXTTRAIL /opt/gg/trails/w1
DISCARDFILE w1extdsc,APPEND,MEGABYTES 5
TRANLOGOPTIONS ALTARCHIVELOGDEST /home/oracle/archivelog1
TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT %t_%s_%r.dbf
TRANLOGOPTIONS ASMUSER sys@ORADB_ASM,ASMPASSWORD admin
DDL INCLUDE MAPPED
DDLOPTIONS ADDTRANDATA
FETCHOPTIONS, USESNAPSHOT, NOUSELATESTVERSION, MISSINGROW REPORT
STATOPTIONS REPORTFETCH
WARNLONGTRANS 1H, CHECKINTERVAL 5M
TABLE hr.*;
TABLE scott.*;

添加该extract对应的trail文件,用来存储抽取的数据。单个文件大小设置为100MB
GGSCI (rac1) 7> add exttrail /opt/gg/trails/w1,extract w1ext, MEGABYTES 100
EXTTRAIL added.

添加secondary extract group,即data pump:

GGSCI (rac1) 8> ADD EXTRACT w1extdp, EXTTRAILSOURCE /opt/gg/trails/w1, BEGIN now
EXTRACT added.

编辑参数文件
RMTHOST后面跟目标端的ip或主机名(需在hosts文件中有对应ip解析)和manager进程的端口号;RMTTRAIL指定目标端的trail文件所在位置

GGSCI (rac1) 9> edit params w1extdp
EXTRACT w1extdp
USERID goldengate, PASSWORD goldengate
RMTHOST 10.10.10.88, MGRPORT 5898
RMTTRAIL /opt/gg/trails/w1
DISCARDFILE w1extdpdsc,APPEND,MEGABYTES 5
TABLE hr.*;
TABLE scott.*;

GGSCI (rac1) 10> add rmttrail /opt/gg/trails/w1,extract w1extdp, megabytes 100
RMTTRAIL added.


配置目标REPLICATE
回到目标端,使用ORACLE用户登录到ggsci命令行中,对应前面的extract和data pump增加replicat

GGSCI (ggdb) 6> add replicat w1rep,exttrail /opt/gg/trails/w1,checkpointtable goldengate.chkpoint
REPLICAT added.

这里的handlecollisions参数在目标端数据初始化并同步之后去掉

编辑参数文件:
1. assumetargetdefs:由于在这里我们源端和目标端的表结构是完全一致的,因此使用这个参数来使replicat不用去查看相关的定义文件,从而提高效率
2. DDLOPTIONS REPORT:将ddl的具体信息写入到报告文件中
3. BATCHSQL:将相似的SQL语句放到一个数组中以加快执行速度。在normal模式下,repliat同一时间只应用一条sql语句。
4. DBOPTIONS DEFERREFCONST:将完整性约束推迟到replicat事务提交以后再检测
5. 如果数据库版本在10.2.0.5或11.2.0.2以后,可以使用DBOPTIONS SUPPRESSTRIGGERS在replicat会话中禁用触发器。如果不是,应该在目标端数据库中禁用触发器(触发器产生的DML操作会从源端同步到目标端)
6. DBOPTIONS LOBWRITESIZE :将要写入目标库的LOB数据缓存在内存中,当达到参数中指定的大小时写入数据,以减少I/O。这个值的范围是2KB到1MB,默认为32KB
7. DDLERROR DEFAULT DISCARD RETRYOP MAXRETRIES 5 RETRYDELAY 20:当出现DDL错误,重试5次,时间间隔为20秒。如果失败,replicat会继续运行,但相关信息会记录在discardfile中。
8. HANDLECOLLISIONS:当replicat往表中插入一条记录,而该记录已经存在,则进行覆盖;当replicat在表中试图更新或删除一条记录,而该记录不存在,则该操作被丢弃。这个参数一般在initial-data load中使用,在源和目标端的数据同步之后应该将该参数删除
9. MAP TARGET:源表和目标表之间的映射,可以使用通配符

edit params w1rep

REPLICAT w1rep
ASSUMETARGETDEFS
USERID goldengate, PASSWORD goldengate
DISCARDFILE w1repdsc,APPEND,MEGABYTES 5
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
BATCHSQL
DBOPTIONS DEFERREFCONST
DBOPTIONS LOBWRITESIZE 102400
HANDLECOLLISIONS
DDLERROR DEFAULT DISCARD RETRYOP MAXRETRIES 5 RETRYDELAY 20
MAP hr.* , TARGET hr.* ;
MAP scott.* , TARGET scott.* ;

增加传输表:(在源端操作)
在启动goldengate相关进程之前,在目标端使用trandata对源数据库中需要同步的表启用对象级别的追加日志

GGSCI (rac1) 17> dblogin userid goldengate,password goldengate
Successfully logged into database.

GGSCI (rac1) 18> add trandata hr.*
Logging of supplemental redo data enabled for table DYCOMMONDATABASE20.AUDITCONFIG.

Logging of supplemental redo data enabled for table DYCOMMONDATABASE20.AUDITHISTORY.
……

如果表中没有主键和唯一键,会产生一条警告,例如:
2011-12-08 22:53:11 WARNING OGG-00869 No unique key is defined for table UDPPACKPORTSET. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

GGSCI (rac1) 19> add trandata scott.*
……


业务表中没有主键和唯一键,这是个很糟糕的习惯,这里就不批判了...

数据初始化:
在进行同步以前,要先对目标端的数据进行初始化
在线迁移有几种方法,这里采取比较靠谱的两种:通过数据库工具(expdp/impdp)和通过文件导入到replicat。采用EXP方式应用在一组抽取/复制对应的4个数据库用户上。w1ext对应的hr用户和scott使用前者。

准备工作

1. 禁用DDL的抽取和复制
在extract和replicat参数中将ddl去掉。实际上,若能保证在expdp导出数据期间不会有ddl操作,可以忽略此项
启动MGR
start mgr (在两端执行)
在源端,打开extract进程
GGSCI (rac1) 35> start ext w1ext

Sending START request to MANAGER ...
EXTRACT W1EXT starting

GGSCI (rac1) 36> start ext w1extdp

Sending START request to MANAGER ...
EXTRACT W1EXTDP starting


GGSCI (rac1) 37> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING W1EXT 00:00:00 00:00:05
EXTRACT RUNNING W1EXTDP 00:00:00 02:00:51

如果这里的状态为STOP请仔细查看日志:/opt/gg/goldengate/ggserr.log

在源端执行EXP操作:(因为我这是测试库, 两边不同的就是HR多一张TEST的表)
exp hr/hr file=/home/oracle/datapump/test.dmp log=log.log tables=test
scp test.dmp oracle@10.10.10.88:/home/oracle
然后在目标库进行导入:
imp hr/hr fromuser=hr touser=hr file=test.dmp log=log.log ignore=y

如果数据库版本是10.2.0.4则需要在目标库中禁用我们要同步的用户中的触发器
begin
for x in (select owner,trigger_name from dba_triggers
where owner in ('hr', 'scott'))
loop
execute immediate 'alter trigger '||x.owner||'.'||x.trigger_name||' disable';
end loop;
end;
/

在目标端启动复制进程:
start rep w1rep
可用info replicat w1rep 查看复制状态
GGSCI (localhost.localdomain) 2> info replicat w1rep

REPLICAT   W1REP     Last Started 2012-05-28 16:36   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint  File /opt/gg/trails/w1000000
                     2012-05-28 17:05:38.663159  RBA 57168
在目标端的数据变化追上initial-load结束的时间点后,关闭handlecollisions参数: 先使用SEND REPLICAT命令使之对运行中的replicat生效,然后修改参数文件,使其在下一次启动时生效。
Sending NOHANDLECOLLISIONS request to REPLICAT W1REP ...
W1REP No tables found matching GOLDENGATE.* to set NOHANDLECOLLISIONS.

然后修改w1rep参数文件,去掉handlecollisions参数
可以试着在源数据库中进行一些ddl或dml操作,在目标端使用ggsci命令stats或在数据库中查看更改是否被应用了。

这里GoldenGate就配置完成了
测试:
源端:
SQL> conn hr/hr
Connected.
SQL> create table test2 as select * from employees;

Table created.

SQL> select count(*) from test2;

  COUNT(*)
----------
       107

目标端:
SQL> conn hr/hr
Connected.
SQL> select count(*) from test2;

  COUNT(*)
----------
       107
可以看见DDL操作已经同步。

再试试DML操作。
SQL> insert into test2 select * from employees;

107 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from test2;

  COUNT(*)
----------
       214

目标端:
SQL>  select count(*) from test2;

  COUNT(*)
----------
       214

测试结束。

日常管理和维护


启动关闭使用start和stop ggsci命令,查看状态使用info和status命令,查看统计信息使用stats命令,注意查看discard文件和其他日志文件(比如goldengate安装目录下的ggserror.log和源端user_dump_dest下的ggs_ddl_trace.log)这里不再赘述


故障切换

在RAC环境中,最好将GoldenGate相关的二进制文件、参数文件、trail文件等都放在共享存储上。正常情况下,由其中一个节点来执行GoldenGate进程。当这个节点发生故障时,可以将GoldenGate文件所在目录重新挂载到另一个节点中,这样就可以从中断的地方继续工作。

在这个测试中,extract进程运行在rac1中,如果rac1发生了故障,rac2准备接管(相关os用户和目录应该在先前已经建好)

挂载到rac2上:
[root@rac2 ~]# mount /dev/sdg1 /opt/gg
[root@rac2 ~]# ls -l /opt/gg
total 24
drwxr-xr-x 14 goldengate oinstall 4096 Nov 28 01:57 goldengate
drwxrwxr-x 2 goldengate oinstall 16384 Nov 28 01:05 lost+found
drwxr-xr-x 2 goldengate oinstall 4096 Nov 28 01:57 trails

然后切换到goldengate用户,启动manager和extract进程

[root@rac2 ~]# su - goldengate
[goldengate@rac2 ~]$ cd /opt/gg/goldengate/
[goldengate@rac2 goldengate]$ ./ggsci

GGSCI (rac2) 1> start mgr

Manager started.

GGSCI (rac2) 2> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
EXTRACT ABENDED DYEXT 00:00:00 00:01:19
EXTRACT ABENDED DYEXTDP 00:00:00 00:01:18

GGSCI (rac2) 3> start ext *

Sending START request to MANAGER ...
EXTRACT DYEXT starting

Sending START request to MANAGER ...
EXTRACT DYEXTDP starting

GGSCI (rac2) 4> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DYEXT 00:00:00 00:00:01
EXTRACT ABENDED DYEXTDP 00:00:00 00:14:53

此时primary extract进程开始正常工作,data pump进程处于abended状态。这是正常的,这是因为rac1发生了节点故障,相关事务在rac2上执行回滚,但extract不能捕获到回滚动作,这些事务还存在于extract事务列表中。
默认情况下,GoldenGate会花一定的时间确认该事务(默认为10分钟),然后从列表中清除它们。然后再启动data pump进程,可以正常运行:

GGSCI (rac2) 24> start ext w1extdp

Sending START request to MANAGER ...
EXTRACT DYEXTDP starting


GGSCI (rac2) 25> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING W1EXT 00:00:00 00:00:02
EXTRACT RUNNING W1EXTDP 00:00:00 08:23:02


注意rac2中tnsnames.ora和listener.ora里关于ASM已经正确配置,否则extract进程会起不来。



一些错误和警告信息

错误:


OGG-00052 Oracle GoldenGate Delivery for Oracle, dyrep.prm: No replication maps specified.
replicat配置文件中必须指定map , target参数

OGG-00212 Oracle GoldenGate Delivery for Oracle, dyrep.prm: Invalid option for MAP:xxx
replicat参数中map子句语法不正确(,/等符号前加上空格)

OGG-00259 Oracle GoldenGate Delivery for Oracle, dyrep.prm: Schema name cannot be wildcarded.
replicat参数的map ,target参数中,schema不能使用通配符

dblogin登录失败:
dblogin userid goldengate,password goldengate报错 ERROR: Failed to open data source for user GOLDENGATE
以sys、system或其它具有DBA权限的用户可以使用dblogin登录,然后再登录goldengate能成功.
解决办法:授予goldengate用户select any dictionary权限


警告:

OGG-00869 Oracle GoldenGate Command Interpreter for Oracle: No unique key is defined for table xxx. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
xxx表没有唯一键,若数据库中不能对表添加主键或唯一约束,可以使用goldengate的KEYCOLS参数指定能准确标识行的列。否则该表的所有字段都将被保存到redo中用以标识更改的行。

OGG-01756 Oracle GoldenGate Command Interpreter for Oracle: Cannot register EXTRACT DYEXT with database because of the following SQL error: OCI Error ORA-06550: line 1, column 3128: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 1, column 3117: PL/SQL: SQL Statement ignored (status = 0). See Extract user privileges in the Oracle GoldenGate for Oracle Installation and Setup Guide. You can manually register this group with the REGISTER EXTRACT command.

本文参考页面:http://blog.csdn.net/wildwave/article/details/7056500  。






来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22740983/viewspace-734629/,如需转载,请注明出处,否则将追究法律责任。

上一篇: OCR AND VOTINGDISK
请登录后发表评论 登录
全部评论

注册时间:2012-03-30

  • 博文量
    33
  • 访问量
    51777