Goldengate的安装与配置(配置rac到单实例的同步)

àì夳堔傛蜴生んèń 2022-08-08 05:28 281阅读 0赞

环境：

源端：11204的双节点rac

目标端：11204的单实例数据库

Goldengate安装包版本：

121210\_fbo\_ggs\_Linux\_x64\_shiphome.zip

**RAC上安装OGG：**

1.      在rac两个节点创建ogg安装目录

mkdir /oracle/ogg

2.      设置环境变量

在用户参数文件中添加以下内容：

export GGATE\_HOME=/oracle/ogg

export LIBPATH=$GGATE\_HOME:$ORACLE\_HOME/lib

3.      用户asmca创建共享磁盘组并格式化为acfs文件系统挂载到/oracle/ogg目录

创建共享磁盘的步骤如下：

说明：

使用asmca创建acfs和advm时发现图形界面事acfs和volume选项卡为灰色不能使用这是由于acfs不支持centos系统导致的，所以要配置centos上使用acfs集群文件系统，步骤见文章

[http://ylw6006.blog.51cto.com/470441/1135987/][http_ylw6006.blog.51cto.com_470441_1135987]

[http://www.idevelopment.info/data/Oracle/DBA\_tips/Automatic\_Storage\_Management/ASM\_50.shtml][http_www.idevelopment.info_data_Oracle_DBA_tips_Automatic_Storage_Management_ASM_50.shtml]

配置完成后使用asmca工具图形化界面创建acfs文件系统

现在创建磁盘组ogg （需要为磁盘组准备单独的磁盘）

创建volume

创建acfs文件系统（截图省略）

在节点上执行挂载：

\[root@rac1 bin\]\# /sbin/mount.acfs -o all

\[root@rac2 bin\]\# /sbin/mount.acfs -o all

用df查看挂载点信息

节点1：

rac1->df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/sda3             5.8G  3.4G  2.1G  62% /

tmpfs                 1.4G  440M 940M  32% /dev/shm

/dev/sda1              97M   32M  61M  35% /boot

/dev/sda2              15G   14G 403M  98% /oracle

/dev/sda6             2.0G   38M 1.8G   3% /tmp

/dev/asm/oggvol-201   6.0G 666M  5.4G  11% /oracle/app/grid/ogg

节点2：

rac2->df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/sda3             5.8G  4.1G 1.5G  75% /

tmpfs                 1.4G  380M 999M  28% /dev/shm

/dev/sda1              97M   32M  61M  35% /boot

/dev/sda2              15G  9.5G 4.2G  70% /oracle

/dev/sda6             2.0G  232M 1.6G  13% /tmp

/dev/asm/oggvol-201   6.0G 666M  5.4G  11% /oracle/app/grid/ogg

在共享文件中创建文件验证挂载目录

\[root@rac2 ogg\]\# touch test  
touch: cannot touch \`test': Permission denied  
\[root@rac2 ogg\]\#

无法创建文件提示权限拒绝，原因是由于SElinux是开启的，关闭SElinux就可以了

4.      以grid用户安装ogg

上传安装包，并解压

cd disk1

执行

./runInstaller

图形化界面安装ogg

点击next

点击next忽略这个警告（截图省略）

注：

**单实例安装OGG:**

实例安装如rac安装一样

**配置源端数据库:**

1. 数据库模式配置

源端数据库必须开启归档模式

Alter database archivelog;

2. 开启最小附加日志

Alter database add supplemental log data;

使用SELECTSUPPLEMENTAL\_LOG\_DATA\_MIN FROM V$DATABASE;

可查看是否开启了最小附加日志;

3. 在源端和目标创建专用的用户表空间

创建表空间（根据实际情况进行修改）

create tablespace ogg datafile'/oradata/$ORACLE\_SID/ogg1.dbf' size 2000M ;

创建用户并授权

CREATE USER ggs IDENTIFIED BY ggs DEFAULTTABLESPACE ggs TEMPORARY TABLESPACE TEMP;

Grant dba to ggs ;

**配置asm的联通性：**

由于rac数据库的归档日志放在asm磁盘组中，所以要配置asm连接

检查rac两个节点的监听情况：

节点1：

rac1-> lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0- Production on 04-MAY-2015 16:40:10

Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

STATUS of the LISTENER

\------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.4.0- Production

Start Date                04-MAY-2015 15:20:44

Uptime                    0 days 1 hr. 19 min. 27 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /oracle/app/11.2.0/grid/network/admin/listener.ora

Listener Log File        /oracle/app/grid/diag/tnslsnr/rac1/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.105)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.2)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for thisservice...

Service "orcl" has 1 instance(s).

Instance "orcl1", status READY, has 1 handler(s) for thisservice...

Service "orclXDB" has 1instance(s).

Instance "orcl1", status READY, has 1 handler(s) for thisservice...

The command completed successfully

rac1->

节点2：

rac2-> lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0- Production on 04-MAY-2015 16:39:29

Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

STATUS of the LISTENER

\------------------------

Alias                    LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.4.0- Production

Start Date                04-MAY-2015 15:21:07

Uptime                    0 days 1 hr. 18 min. 23 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /oracle/app/11.2.0/grid/network/admin/listener.ora

Listener Log File        /oracle/app/grid/diag/tnslsnr/rac2/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.103)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.3)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM2", status READY, has 1 handler(s) for thisservice...

Service "orcl" has 1 instance(s).

Instance "orcl2", status READY, has 1 handler(s) for thisservice...

Service "orclXDB" has 1instance(s).

Instance "orcl2", status READY, has 1 handler(s) for thisservice...

The command completed successfully

配置tnsname.ora

在节点1的oracle用户和grid用户下的tnsname.ora文件中添加如下：

ASM =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.105)(PORT = 1521))

(CONNECT\_DATA =

(SERVER = DEDICATED)

(SERVICE\_NAME = +ASM)

(SID\_NAME = +ASM1)

)

在节点2的oracle用户和grid用户下的tnsname.ora文件中添加如下：

ASM =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.103)(PORT = 1521))

(CONNECT\_DATA =

(SERVER = DEDICATED)

(SERVICE\_NAME = +ASM)

(SID\_NAME = +ASM2)

)

**配置goldengate：**

1. 配置源端mgr

GGSCI (database) 4>  EDIT PARAMS mgr

PORT 7809

PURGEOLDEXTRACTS/oracle/oracle/ogg/dirdat/\*, USECHECKPOINTS,MINKEEPDAYS 1

2. 在目标端添加checkpoint

edit params ./globals

checkpointtable ogg.checkpoint

GGSCI (NOKGAMFVFDI3EDJ) 1> dbloginuserid ggs ,password ggs

Successfully logged into database.

GGSCI (NOKGAMFVFDI3EDJ as ggs@ogg1) 2>add checkpointtable ggs.checkpoint

3. 配置源端抽取进程

GGSCI (rac2) 88> edit params eora

extract eora

dynamicresolution

SETENV (ORACLE\_HOME ="/oracle/app/oracle/product/11.2.0/db\_1")

USERID ggs@orcl, PASSWORD ggs

TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORDoracle

THREADOPTIONS MAXCOMMITPROPAGATIONDELAY20000

exttrail /oracle/app/grid/ogg/dirdat/et

table scott.\*;

tableexclude scott.ww;

GGSCI > ADD EXTRACT eora, TRANLOG, BEGINNOW, *THREADS 2*

GGSCI > ADD EXTTRAIL  /oracle/app/grid/ogg/dirdat/et, EXTRACT eora

4. 配置源端pump投递进程

GGSCI (rac2) 89> edit params pump\_so

extract pump\_so

SETENV (ORACLE\_HOME = "/oracle/app/oracle/product/11.2.0/db\_1")

USERID ggs@orcl, PASSWORD ggs

TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORD oracle

rmthost 192.168.56.106,mgrport 7809

rmttrail /oracle/app/oracle/ogg/dirdat/mb

passthru

\--ddl include all

\--ddloptions addtrandata,report

table scott.\*;

GGSCI > ADD EXTRACT pump\_so, EXTTRAILSOURCE  /oracle/app/oracle/ogg/dirdat/et,

BEGIN now

GGSCI > ADD RMTTRAIL /oracle/app/oracle/ogg/dirdat/rt, EXTRACT pump\_so

5. 配置目标端replicat复制进程

GGSCI (rac3 as ggs@orcl) 17> edit paramsrepl

replicat repl

userid ggs, password ggs

assumetargetdefs

reperror default,discard

discardfile./dirrpt/rep1.dsc,append,megabytes 50

dynamicresolution

\--ddl include mapped

\--ddloptions report

map scott.\*,target scott.\*;

GGSCI> ADD REPLICAT rept, EXTTRAIL /oracle/ogg/dirdat/rt

上面报错

GGSCI(rac3 as ggs@orcl) 5> add replicat repl ,exttrail ./dirdat/mb

ERROR:No checkpoint table specified for ADD REPLICAT.

用下面的：

ADDREPLICAT repl,EXTTRAIL ./dirdat/mb,checkpointtable ggs.checkpoint

**配置支持DDL复制：**

停止源端的抽取进程和目标端的应用进程

源端：

GGSCI (rac2) 7> stop mgr

Manager process is required by other GGSprocesses.

Are you sure you want to stop it (y/n)?y

Sending STOP request to MANAGER ...

Request processed.

Manager stopped.

GGSCI (rac2) 8>

GGSCI (rac2) 8> info all

Program    Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER    STOPPED

EXTRACT    RUNNING     EORA        00:00:00      00:00:06

EXTRACT    RUNNING     PUMP\_SO     00:00:00      00:00:06

目标端：

GGSCI (rac3) 8> stop repl

Sending STOP request to REPLICAT REPL ...

Request processed.

GGSCI (rac3) 9> info all

Program    Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER    RUNNING

REPLICAT   STOPPED     REPL        00:00:00     00:00:28

\[oracle@sourcesrv gg\]$ sqlplus / as sysdba

SQL> @marker\_setup.sql

Marker setup script

You will beprompted for the name of a schema for the Oracle GoldenGate database objects.

NOTE: The schemamust be created prior to running this script.

NOTE: Stop all DDLreplication before starting this installation.

Enter OracleGoldenGate schema name:ggs

Marker setup tablescript complete, running verification script...

Please enter thename of a schema for the GoldenGate database objects:

Setting schema nameto GGS

MARKER TABLE

\--------------------------------------------------------------------------------

MARKER SEQUENCE

\--------------------------------------------------------------------------------

Script complete.

SQL>

SQL> @marker\_setup.sql

Oracle GoldenGateDDL Replication setup script

Verifying thatcurrent user has privileges to install DDL Replication...

You will beprompted for the name of a schema for the Oracle GoldenGate database objects.

NOTE: For an Oracle10g source, the system recycle bin must be disabled. For Oracle 11g and later,it can be enabled.

NOTE: The schemamust be created prior to running this script.

NOTE: Stop all DDLreplication before starting this installation.

Enter OracleGoldenGate schema name:ggs

Working, pleasewait ...

Spooling to fileddl\_setup\_spool.txt

Checking forsessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.

WARNING: TablespaceOGG does not have AUTOEXTEND enabled.

Using GGS as aOracle GoldenGate schema name.

Working, pleasewait ...

DDL replicationsetup script complete, running verification script...

Please enter thename of a schema for the GoldenGate database objects:

Setting schema nameto GGS

CLEAR\_TRACE STATUS:

Line/pos

\------------------------------------------------------------

Error

\-----------------------------------------------------------------

No errors

CREATE\_TRACESTATUS:

Line/pos

\------------------------------------------------------------

Error

\-----------------------------------------------------------------

No errors

TRACE\_PUT\_LINESTATUS:

Line/pos

\------------------------------------------------------------

Error

\-----------------------------------------------------------------

No errors

INITIAL\_SETUPSTATUS:

Line/pos

\------------------------------------------------------------

Error

\-----------------------------------------------------------------

No errors

DDLVERSIONSPECIFICPACKAGE STATUS:

Line/pos

\------------------------------------------------------------

Error

\-----------------------------------------------------------------

No errors

DDLREPLICATIONPACKAGE STATUS:

Line/pos

\------------------------------------------------------------

Error

\-----------------------------------------------------------------

No errors

DDLREPLICATIONPACKAGE BODY STATUS:

Line/pos

\------------------------------------------------------------

Error

\-----------------------------------------------------------------

No errors

DDL IGNORE TABLE

\---------------------------------------------------------------------------------------------------------

DDL IGNORE LOGTABLE

\---------------------------------------------------------------------------------------------------------

DDLAUX  PACKAGE STATUS:

Line/pos

\------------------------------------------------------------

Error

\-----------------------------------------------------------------

No errors

DDLAUX PACKAGE BODYSTATUS:

Line/pos

\------------------------------------------------------------

Error

\-----------------------------------------------------------------

No errors

SYS.DDLCTXINFO  PACKAGE STATUS:

Line/pos

\------------------------------------------------------------

Error

\-----------------------------------------------------------------

No errors

SYS.DDLCTXINFO  PACKAGE BODY STATUS:

Line/pos

\------------------------------------------------------------

Error

\-----------------------------------------------------------------

No errors

DDL HISTORY TABLE

\---------------------------------------------------------------------------------------------------------

DDL HISTORYTABLE(1)

\---------------------------------------------------------------------------------------------------------

DDL DUMP TABLES

\---------------------------------------------------------------------------------------------------------

DDL DUMP COLUMNS

\---------------------------------------------------------------------------------------------------------

DDL DUMP LOG GROUPS

\---------------------------------------------------------------------------------------------------------

DDL DUMP PARTITIONS

\---------------------------------------------------------------------------------------------------------

DDL DUMP PRIMARYKEYS

\---------------------------------------------------------------------------------------------------------

DDL SEQUENCE

\---------------------------------------------------------------------------------------------------------

GGS\_TEMP\_COLS

\---------------------------------------------------------------------------------------------------------

GGS\_TEMP\_UK

\---------------------------------------------------------------------------------------------------------

DDL TRIGGER CODESTATUS:

Line/pos

\------------------------------------------------------------

Error

\-----------------------------------------------------------------

No errors

DDL TRIGGER INSTALLSTATUS

\---------------------------------------------------------------------------------------------------------

DDL TRIGGER RUNNINGSTATUS

\------------------------------------------------------------------------------------------------------------------------

ENABLED

STAYMETADATA INTRIGGER

\----------------------------------------------------------------------------------------------------------------------

OFF

DDL TRIGGER SQLTRACING

\----------------------------------------------------------------------------------------------------------------------

DDL TRIGGER TRACELEVEL

\----------------------------------------------------------------------------------------------------------------------

LOCATION OF DDLTRACE FILE

\----------------------------------------------------------------------------------------------------------------------

/oracle/app/oracle/diag/rdbms/orcl/orcl2/trace/ggs\_ddl\_trace.log

Analyzinginstallation status...

VERSION OF DDLREPLICATION

\------------------------------------------------------------------------------------------------------------------------

OGGCORE\_12.1.2.1.0\_PLATFORMS\_140727.2135.1

STATUS OF DDLREPLICATION

\------------------------------------------------------------------------------------------------------------------------

SUCCESSFULinstallation of DDL Replication software components

Script complete.

SQL>

** **

SQL> @role\_setup.sql

GGS Role setupscript

This script willdrop and recreate the role GGS\_GGSUSER\_ROLE

To use a differentrole name, quit this script and then edit the params.sql script to change thegg\_role parameter to the preferred name. (Do not run the script.)

You will beprompted for the name of a schema for the GoldenGate database objects.

NOTE: The schemamust be created prior to running this script.

NOTE: Stop all DDLreplication before starting this installation.

Enter GoldenGateschema name:ggs

Wrote filerole\_setup\_set.txt

PL/SQL proceduresuccessfully completed.

Role setup scriptcomplete

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

GRANT GGS\_GGSUSER\_ROLETO <loggedUser>

where<loggedUser> is the user assigned to the GoldenGate processes.

开启ddl复制功能

SQL> @ddl\_enable

Trigger altered.

SQL>

验证ddl安装

SQL> @marker\_status.sql

Please enter the name of a schema for theGoldenGate database objects:

ggs

Setting schema name to GGS

MARKER TABLE

\---------------------------------------------------------------------------------------------

MARKER SEQUENCE

\---------------------------------------------------------------------------------------------

SQL>

在进程中配置支持DDL复制

在抽取进程中添加如下参数：

ddl include all

ddloptions addtrandata,report

在应用进程中添加如下参数：

ddl include mapped

ddloptions report

在源端执行创建表

SQL> create table jack(id number primary key ,name varchar2(20));

Table created.

在目标端查询：

SQL> desc jack

Name                                     Null?    Type

------------------------------------------------- ----------------------------

ID                                        NOTNULL NUMBER

NAME                                              VARCHAR2(20)

验证同步成功

[http_ylw6006.blog.51cto.com_470441_1135987]: http://ylw6006.blog.51cto.com/470441/1135987/
[http_www.idevelopment.info_data_Oracle_DBA_tips_Automatic_Storage_Management_ASM_50.shtml]: http://www.idevelopment.info/data/Oracle/DBA_tips/Automatic_Storage_Management/ASM_50.shtml