Goldengate的安装与配置(配置rac到单实例的同步)_error: no checkpoint table specified for add repli-CSDN博客

本文链接：https://blog.csdn.net/weiwangsisoftstone/article/details/45536297

环境：

源端：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://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)

验证同步成功