goldengate配置(rac向单实例包含ddl)(5)

2020-02-21 11:16

GRANT GGS_GGSUSER_ROLE TO

where is the user assigned to the GoldenGate processes. SQL> GRANT GGS_GGSUSER_ROLE TO goldengate;

Grant succeeded. SQL> @ddl_enable

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

OBJECT_NAME

------------------------------------------------------------------------------------------------------------------------ OBJECT_TYPE -------------------

DBMS_SHARED_POOL PACKAGE

DBMS_SHARED_POOL PACKAGE BODY

2 rows selected.

SQL> @ddl_pin goldengate

PL/SQL procedure successfully completed.

如果以oracle用户的话，无需配置监听，直接使用操作系统认证即可，但是oracle用户对/opt/app/ogg没有访问权限（使用asmca建立的acfs系统oracle无权限访问），所以使用grid用户来执行。网上有的使用asmca来创建acfs文件系统后，依旧oracle用户来执行这些脚本，不知道他们是更改了目录的权限还是用asmca创建完acfs后oracle就可以直接访问，不过需要配置访问ASM的tns，但是使用grid用户无需配置可直接访问asm，此处如果想让oracle可以访问/opt/app/ogg，更改该目录权限即可，先以grid用户配置一番，如果不行，再使用oracle也未尝不可。

在开始配置goldengate前，先在源数据库创建测试表：接下来在源数据库上创建一个测试用户以及测试表: SQL> create user test identified by test; User created.

SQL> grant dba to test; Grant succeeded.

SQL> connect test/test@ggora Connected.

SQL> create table test( stuid number(8),

stuname varchar2(20), stupasswd varchar2(20) );

Table created.

然后再进入/opt/app/ogg，运行./ggsci [grid@rac1 ~]$ cd /opt/app/ogg [grid@rac1 ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO Linux, x64, 64bit (optimized), Oracle 11g on Sep 25 2013 00:31:13 Operating system character set identified as UTF-8.

DYNAMICPORTLIST 7840-7914

PURGEOLDEXTRACTS /opt/app/ogg/trails/bc*, USECHECKPOINTS, MINKEEPFILES 10 AUTORESTART ER *, RETRIES 3, WAITMINUTES 5

USERID GOLDENGATE@ggora, PASSWORD goldengate

PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30 PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30 然后

GGSCI (rac1) 16> edit params ./globals 加入如下内容：

GGSCHEMA goldengate

登录源端数据库：

GGSCI (rac1) 18> dblogin userid goldengate@ggora,password goldengate Successfully logged into database.

GGSCI (rac1) 19> add extract bcext,tranlog,threads 2,begin now EXTRACT added.

GGSCI (rac1) 21> edit params bcext

加入如下内容： EXTRACT bcext

USERID goldengate@ggora, PASSWORD goldengate EXTTRAIL /opt/app/ogg/trails/bc

DISCARDFILE bcextdsc,APPEND,MEGABYTES 5

TRANLOGOPTIONS ALTARCHIVELOGDEST +RECOVERY_DG TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT %t_%s_%r.dbf TRANLOGOPTIONS DBLOGREADER DDL INCLUDE MAPPED

DDLOPTIONS ADDTRANDATA

FETCHOPTIONS, USESNAPSHOT, NOUSELATESTVERSION, MISSINGROW REPORT STATOPTIONS REPORTFETCH

WARNLONGTRANS 1H, CHECKINTERVAL 5M TABLE test.*; 然后执行：

GGSCI (rac1) 11> add exttrail /opt/app/ogg/trails/bc,extract bcext, MEGABYTES 100 EXTTRAIL added.

GGSCI (rac1) 12> ADD EXTRACT bcextdp, EXTTRAILSOURCE /opt/app/ogg/trails/bc, BEGIN now EXTRACT added.

GGSCI (rac1) 13> edit params bcextdp 加入如下内容： EXTRACT bcextdp

USERID GOLDENGATE@ggora, PASSWORD goldengate RMTHOST 192.168.200.187, MGRPORT 5898 RMTTRAIL /opt/app/ogg/trails/bc

DISCARDFILE bcextdpdsc,APPEND,MEGABYTES 5 TABLE test.*; 然后：

GGSCI (rac1) 15> add rmttrail /opt/app/ogg/trails/bc,extract bcextdp, megabytes 100 RMTTRAIL added. 目标端配置步骤：

同源端相同，先安装goldengate（略）然后配置mgr：

GGSCI (racogg) 3> edit param mgr

加入如下内容： PORT 5898

DYNAMICPORTLIST 7840-7914

USERID GOLDENGATE, PASSWORD goldengate

PURGEOLDEXTRACTS /opt/app/ogg/trails/bc*, USECHECKPOINTS, MINKEEPFILES 10 AUTORESTART ER *, RETRIES 3, WAITMINUTES 5

然后创建goldengate表空间和用户

SQL> create tablespace goldengate datafile '/opt/app/oracle/oradata/goldengate.dbf'size 50M

autoextend on; Tablespace created.

SQL> create user goldengate identified by goldengate default tablespace goldengate temporary tablespace TEMP quota unlimited on goldengate; User created.

SQL> grant dba to goldengate Grant succeeded. 然后执行：

GGSCI (racogg) 7> dblogin userid goldengate,password goldengate Successfully logged into database.

GGSCI (racogg) 8> add checkpointtable goldengate.chkpoint

Successfully created checkpoint table goldengate.chkpoint. GGSCI (racogg) 9> edit params ./globals

加入如下内容：

GGSCHEMA goldengate

CHECKPOINTTABLE goldengate.chkpoint

然后执行：

GGSCI (racogg) 10> add replicat bcrep,exttrail /opt/app/ogg/trails/bc,checkpointtable goldengate.chkpoint REPLICAT added.

GGSCI (racogg) 11> edit params bcrep 加入如下内容： REPLICAT bcrep

ASSUMETARGETDEFS

USERID GOLDENGATE, PASSWORD goldengate DISCARDFILE bcrepdsc,APPEND,MEGABYTES 5 DDL INCLUDE MAPPED DDLOPTIONS REPORT

DBOPTIONS DEFERREFCONST

DBOPTIONS LOBWRITESIZE 102400

#DBOPTIONS NOSUPPRESSTRIGGERS（11024版本不支持SUPPRESSTRIGGERS，会报错ERROR OGG-06472 Failed to enable DBOPTIONS SUPPRESSTRIGGERS，使用NOSUPPRESSTRIGGERS.） DDLERROR DEFAULT DISCARD RETRYOP MAXRETRIES 5 RETRYDELAY 20 MAP test.* , TARGET test.* ; 然后创建目标端的测试表：

SQL> create user test identified by test; User created.

SQL> grant dba to test; Grant succeeded.

SQL> connect test/test@ggora Connected.

SQL> create table test(

stuid number(8),

stuname varchar2(20), stupasswd varchar2(20) );

Table created.

之后执行：

GGSCI (racogg) 16> dblogin userid goldengate,password goldengate Successfully logged into database.

GGSCI (racogg) 17> add trandata test.*

2014-06-12 16:23:31 WARNING OGG-06439 No unique key is defined for table TEST. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table TEST.TEST.

TRANDATA for scheduling columns has been added on table 'TEST.TEST'.

Ps:在11024版本的数据库上，还需要设置“enable_goldengate_replication”参数为true，否则会报错误：ERROR OGG-02091 Operation not supported because enable_goldengate_replication is not set to true.无法启动进程：所以在源端和目标端都执行：

SQL> alter system set enable_goldengate_replication=true scope=both;

System altered.

测试：在源端：

[grid@rac1 ~]$ sqlplus test/test@ggora

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 12 17:29:36 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

SQL> select * from test;

no rows selected

SQL> insert into test values(1,'test1','test1');

共6页:

goldengate配置(rac向单实例包含ddl)(5).doc 将本文的Word文档下载到电脑下载失败或者文档不完整，请联系客服人员解决！

下载这篇word文档