Oracle RAC 到单机 OGG 配置测试

最新推荐文章于 2022-12-23 16:30:21 发布

雨花石~

最新推荐文章于 2022-12-23 16:30:21 发布

阅读量8.5k

点赞数 2

分类专栏： Oracle学习笔记

本文链接：https://blog.csdn.net/shiyu1157758655/article/details/62428173

版权

Oracle学习笔记专栏收录该内容

198 篇文章 20 订阅

订阅专栏

1. 测试环境：

源端：

OS：Centos 6.8

数据库：11.2.0.4

实例： rac1 rac2 rac3

Goldengate：fbo_ggs_Linux_x64_shiphome.zip

Rac与单实例不同之处在于rac上的goldengate 需要安装在共享存储上

目的端：

OS：Centos 6.8

数据库：11.2.0.4 　

实例：test

2. 源端配置步骤

1) 配置ACFS，

具体的可参考：http://blog.csdn.net/shiyu1157758655/article/details/62215468

这里就直接用asmca 进行创建（也可以用命令进行操作）

[root@rac1 ~]# su - grid

+ASM1:/home/grid@rac1>export DISPLAY=192.168.180.186:0.0

+ASM1:/home/grid@rac1>asmca

这里已经创建数据磁盘组的名字为“ACFS”，这里就省略创建磁盘组的步骤

磁盘组创建完成后，再点击Volumes标签页，创建卷：

卷创建完成后，为该卷创建ACFS文件系统，点击ASM Cluster File System：

至此ACFS就创建完了

在各个节点查看是否挂载上：

2）下载安装ogg

下载可以直接到Oracle 官网上下载即可

将下载好的ogg安装包上传到刚才建立的/ogg 共享磁盘下

注意这里我们将以oracle用户安装（之前测试用grid安装，遇到P进程起不来，也没找到好的方法解决）

rac1:/ogg@rac1>unzip fbo_ggs_Linux_x64_shiphome.zip

rac1:/home/oracle@rac1>cd /ogg/

rac1:/ogg@rac1>cd fbo_ggs_Linux_x64_shiphome/Disk1/

rac1:/ogg/fbo_ggs_Linux_x64_shiphome/Disk1@rac1>exportDISPLAY=192.168.180.186:0.0

rac1:/ogg/fbo_ggs_Linux_x64_shiphome/Disk1@rac1>./runInstaller

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB. Actual 28177 MB Passed

Checking swapspace: must be greater than 150 MB. Actual 3258 MB Passed

Checking monitor: must be configuredto display at least 256 colors. Actual16777216 Passed

Preparing to launch Oracle Universal Installer from/tmp/OraInstall2017-03-12_12-59-46PM. Please wait ...

注意这里提示我的/ogg/install 下不为空，可以直接选择忽略就可以了，不会有影响的

安装好以后，进入ogg安装目录执行：ggsci

rac1:/ogg@rac1>cd /ogg/install/

rac1:/ogg/install@rac1>./ggsci

OracleGoldenGate Command Interpreter for Oracle

Version12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

Linux,x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38

Operatingsystem character set identified as UTF-8.

GGSCI (rac1) 1>

注意这里一定配置: LD_LIBRARY_PATH,否则会报以下的错误：

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

只要在.bash_profile 里设置LD_LIBRARY_PATH就行了，如下：

exportLD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32/:/usr/lib:/usr/lib64:/usr/local/lib:/lib:/lib64

3）开启DB归档以及最小附加日志（在其中任意一个节点上执行就可以了）

rac1:/home/oracle@rac1>sqlplus/ as sysdba

SQL*Plus:Release 11.2.0.4.0 Production on Sat Mar 11 09:01:31 2017

Connectedto:

OracleDatabase 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With thePartitioning, Real Application Clusters, Automatic Storage Management, OLAP,

DataMining and Real Application Testing options

SQL>archive log list;

Databaselog mode Archive Mode

Automaticarchival Enabled

Archivedestination +FRA_ARC

Oldestonline log sequence 85

Next logsequence to archive 86

Currentlog sequence 86

SQL>select force_logging from v$database;

FOR

---

SQL>select supplemental_log_data_min from v$database;

SUPPLEME

--------

SQL>alter database force logging;

Databasealtered.

SQL>alter database add supplemental log data;

Databasealtered.

SQL>select force_logging from v$database;

FOR

---

YES

SQL>select supplemental_log_data_min from v$database;

SUPPLEME

--------

YES

SQL>select log_mode,supplemental_log_data_min,force_logging from v$database;

LOG_MODE SUPPLEME FOR

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

ARCHIVELOG YES YES

注意：关于开启归档，请参考：http://blog.csdn.net/shiyu1157758655/article/details/55095760

SQL> altersystem set enable_goldengate_replication = true scope=both;

System altered.

注意：这里要重启数据库，使参数生效

4）创建表空间以及用户（在其中任意一个节点上执行）

rac1:/home/oracle@rac1>sqlplus/ as sysdba

SQL*Plus:Release 11.2.0.4.0 Production on Sat Mar 11 09:18:58 2017

Connectedto:

OracleDatabase 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With thePartitioning, Real Application Clusters, Automatic Storage Management, OLAP,

DataMining and Real Application Testing options

SQL>create tablespace ogg;

Tablespacecreated.

SQL>create user ogg identified by ogg default tablespace ogg;

Usercreated.

SQL>grant connect,resource,dba to ogg;

Grantsucceeded.

SQL> grant execute on utl_file to ogg;

Grant succeeded.

SQL> execdbms_streams_auth.grant_admin_privilege('ogg');

PL/SQL procedure successfully completed.

SQL> grant insert on system.logmnr_restart_ckpt$ to ogg;

Grant succeeded.

SQL> grant update on sys.streams$_capture_process to ogg;

Grant succeeded.

SQL> grant becomeuser to ogg;

Grant succeeded.

SQL> grant flashback any table to ogg;

Grant succeeded.

注意：红色标记的可以不用执行，

5）配置TNS（各节点都要执行）

RAC =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = rac)

)

ASM =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = +ASM)

(SID_NAME = +ASM1)

)

6）安装ddl同步支持

rac1:/home/oracle@rac1>cd /ogg/install/ --进入ogg安装目录

rac1:/ogg/install@rac1>sqlplus/ as sysdba

SQL*Plus:Release 11.2.0.4.0 Production on Sun Mar 12 08:56:20 2017

Connectedto:

OracleDatabase 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With thePartitioning, Real Application Clusters, Automatic Storage Management, OLAP,

DataMining and Real Application Testing options

SQL> @marker_setup --该脚本用于创建DDL的marker表GGS_MARKER，用于存储DDL信息，该表只进行insert操作

Markersetup script

You willbe prompted for the name of a schema for the Oracle GoldenGate databaseobjects.

NOTE: Theschema must be created prior to running this script.

NOTE:Stop all DDL replication before starting this installation.

EnterOracle GoldenGate schema name:ogg

Markersetup table script complete, running verification script...

Pleaseenter the name of a schema for the GoldenGate database objects:

Settingschema name to OGG

MARKERTABLE

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

MARKERSEQUENCE

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

Scriptcomplete.

SQL> @ddl_setup --该脚本创建了进行DDL复制抽取和复制所需的对象

OracleGoldenGate DDL Replication setup script

Verifyingthat current user has privileges to install DDL Replication...

You willbe prompted for the name of a schema for the Oracle GoldenGate databaseobjects.

NOTE: Foran Oracle 10g source, the system recycle bin must be disabled. For Oracle 11gand later, it can be enabled.

NOTE: Theschema must be created prior to running this script.

NOTE:Stop all DDL replication before starting this installation.

EnterOracle GoldenGate schema name:ogg

Working,please wait ...

Spoolingto file ddl_setup_spool.txt

Checkingfor sessions that are holding locks on Oracle Golden Gate metadata tables ...

Checkcomplete.

Using OGGas a Oracle GoldenGate schema name.

Working,please wait ...

DDLreplication setup script complete, running verification script...

Pleaseenter the name of a schema for the GoldenGate database objects:

Settingschema name to OGG

CLEAR_TRACESTATUS:

Line/pos Error

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

No errors No errors

CREATE_TRACESTATUS:

Line/pos Error

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

No errors No errors

TRACE_PUT_LINESTATUS:

Line/pos Error

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

No errors No errors

INITIAL_SETUPSTATUS:

Line/pos Error

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

No errors No errors

DDLVERSIONSPECIFICPACKAGE STATUS:

Line/pos Error

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

No errors No errors

DDLREPLICATIONPACKAGE STATUS:

Line/pos Error

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

No errors No errors

DDLREPLICATIONPACKAGE BODY STATUS:

Line/pos Error

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

No errors No errors

DDLIGNORE TABLE

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

DDLIGNORE LOG TABLE

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

DDLAUX PACKAGE STATUS:

Line/pos Error

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

No errors No errors

DDLAUXPACKAGE BODY STATUS:

Line/pos Error

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

No errors No errors

SYS.DDLCTXINFO PACKAGE STATUS:

Line/pos Error

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

No errors No errors

SYS.DDLCTXINFO PACKAGE BODY STATUS:

Line/pos Error

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

No errors No errors

DDLHISTORY TABLE

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

DDLHISTORY TABLE(1)

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

DDL DUMPTABLES

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

DDL DUMPCOLUMNS

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

DDL DUMPLOG GROUPS

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

DDL DUMPPARTITIONS

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

DDL DUMPPRIMARY KEYS

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

DDLSEQUENCE

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

GGS_TEMP_COLS

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

GGS_TEMP_UK

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

DDLTRIGGER CODE STATUS:

Line/pos Error

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

No errors No errors

DDLTRIGGER INSTALL STATUS

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

DDLTRIGGER RUNNING STATUS

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

ENABLED

STAYMETADATAIN TRIGGER

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

OFF

DDLTRIGGER SQL TRACING

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

DDLTRIGGER TRACE LEVEL

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

NONE

LOCATIONOF DDL TRACE FILE

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

/u01/app/oracle/diag/rdbms/rac/rac1/trace/ggs_ddl_trace.log

Analyzinginstallation status...

VERSIONOF DDL REPLICATION

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

OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401

STATUS OFDDL REPLICATION

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

SUCCESSFULinstallation of DDL Replication software components

Scriptcomplete.

SQL> @role_setup --创建DDL复制所需的对象

GGS Rolesetup script

Thisscript will drop and recreate the role GGS_GGSUSER_ROLE

To use adifferent role name, quit this script and then edit the params.sql script tochange the gg_role parameter to the preferred name. (Do not run the script.)

You willbe prompted for the name of a schema for the GoldenGate database objects.

NOTE: Theschema must be created prior to running this script.

NOTE:Stop all DDL replication before starting this installation.

EnterGoldenGate schema name:ogg

Wrotefile role_setup_set.txt

PL/SQLprocedure successfully completed.

Rolesetup script complete

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

GRANTGGS_GGSUSER_ROLE TO <loggedUser>

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

SQL> GRANT GGS_GGSUSER_ROLE TO ogg;

Grantsucceeded.

SQL> @ddl_enable -- 启用DDL复制，实质上是创建触发器，用以想MARKER和HISTORY表插入DDL信息

Triggeraltered.

SQL>exit

安装性能优化工具 (可选项)

要提高DDL触发器的性能，可以通过ddl_pin脚本，该脚本会将触发器使用的包加载到内存，以此提高效率。该脚本执行时需要引用dbms_shared_pool系统包，因此在使用ddl_pin脚本前需要确保dbms_shared_pool可用。

SQL>@?/rdbms/admin/dbmspool.sql

Packagecreated.

Grantsucceeded.

Viewcreated.

Packagebody created.

执行ddl_pin脚本需要指定GoldenGate管理员schema名称，例如：

SQL>@ddl_pin ogg

PL/SQLprocedure successfully completed.

7) 开启表级别、日志传输，创建检查点表

开启归档，最小附加日志和强制归档之后，ogg可以同步insert，delete等部分操作，但遇到update 时将会挂掉，报错说找不到相关记录，因此，还需要再对每一个table都在源端开启表级日志传输。（必须要登陆ogg才可以执行）

rac1:/home/oracle@rac1>cd/ogg/install/

rac1:/ogg/install@rac1>./ggsci

OracleGoldenGate Command Interpreter for Oracle

Version12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

Linux,x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38

Operatingsystem character set identified as UTF-8.

GGSCI(rac1) 1> dblogin userid ogg@rac,password ogg

Successfullylogged into database.

GGSCI(rac1 as ogg@rac1) 19> add trandata TEST.TEST

2017-03-1209:13:41 WARNING OGG-06439 No unique key is defined for table TEST. Allviable columns will be used to represent the key, but may not guaranteeuniqueness. KEYCOLS may be used to define the key.

Loggingof supplemental redo log data is already enabled for table TEST.TEST.

TRANDATA for instantiation CSN has been added on table'TEST.TEST'.

注意：这里只是做测试，就只有一张test的表，如果有其他的表，都要执行。

再执行如下命令创建检查点表

GGSCI (oracle11g as ogg@test)118> add checkpointtable ogg.chkpt

Successfully created checkpointtable ogg.chkpt.

8）配置OGG 进程

rac1:/ogg/install@rac1>./ggsci

Oracle GoldenGate CommandInterpreter for Oracle

Version 12.2.0.1.1OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

Linux, x64, 64bit (optimized),Oracle 11g on Dec 12 2015 00:54:38

Operating system character setidentified as UTF-8.

GGSCI (rac1) 3> dblogin useridogg,password ogg

Successfully logged into database.

GGSCI (rac1) 1> view params mgr

PORT 7809

GGSCI (rac1) 2> edit params mgr

PORT7809

DYNAMICPORTLIST7810-7850 //动态端口号，7809被占用时会动态使用

PURGEOLDEXTRACTS/ogg/install/extlt/rac/lt*,USECHECKPOINTS,MINKEEPDAYS 7//自动管理删除 local trail文件，保留7天

PURGEOLDEXTRACTS/ogg/install/replt/rac/lt*,USECHECKPOINTS, MINKEEPDAYS 7

里面添加上面的内容

添加抽取进程：

GGSCI (rac1 as ogg@rac1)4> add extract E_RAC,tranlog,threads 3 begin now

EXTRACT added.

GGSCI (rac1 as ogg@rac1) 6> add exttrail /ogg/install/dirdat/extlt/rac/lt,extract E_RAC

EXTTRAIL added.

配置E_RAC参数文件

GGSCI (rac1 as ogg@rac1)7> edit params E_RAC

EXTRACT E_RAC

SETENV (ORACLE_HOME ="/u01/app/oracle/product/11.2.0/db_1")

SETENV (ORACLE_SID = "rac")

USERID ogg@rac,PASSWORD ogg

EXTTRAIL /ogg/install/dirdat/extlt/rac/lt

DISCARDFILE/ogg/install/dirrpt/e_rac.dsc,APPEND,MEGABYTES 5

TRANLOGOPTIONS ALTARCHIVELOGDEST +FRA_ARC

TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT '%t_%s_%r.arc'

TRANLOGOPTIONS DBLOGREADER

DDL INCLUDE MAPPED

DDLOPTIONS ADDTRANDATA

FETCHOPTIONS, USESNAPSHOT, NOUSELATESTVERSION,MISSINGROW REPORT

STATOPTIONS REPORTFETCH

WARNLONGTRANS 1H, CHECKINTERVAL 5M

OBEY /ogg/install/dirprm/ogg_tables_RAC.txt --载入所要抽取的table list；里面内容为: table TEST.TEST;

添加传送到目标端的p进程 P_TEST

注意关于目的端ogg和RAC安装一样，按上面的操作在目标端安装ogg，这里就不再赘述。

GGSCI (rac1 as ogg@rac1) 9> add extract P_TEST,exttrailsource /ogg/install/dirdat/extlt/rac/lt

EXTRACT added.

GGSCI (rac1 as ogg@rac1)10> add rmttrail /ogg/dirdat/replt/rac/lt, extract P_TEST

配置P_TEST的参数文件

GGSCI (rac1 as ogg@rac1) 13> edit paramsP_TEST

EXTRACT P_TEST

rmthost 192.168.180.14, mgrport 7809 --目的端的IP

rmttrail /ogg/dirdat/replt/rac/lt --trail文件传送过去的存放路径

dynamicresolution --动态解析表名，为缺省参数，可不显示指定

passthru --使用该参数告诉ogg同步的两端表结构一致

OBEY /ogg/install/dirprm/ogg_tables_RAC.txt --载入所要传送的table list，和上面的同一个

3. 目的端操作：

关于目的端ogg的安装可以参考上述步骤，这里不在赘述。以下是几个注意点

1) 开启归档，最小附件日志

2) 创建表空间及授权

3) 配置TNS

4) 安装ddl同步支持

5) 开启表级日志传输（如果是单向的就不要执行）

6) 创建检查点表

7) 执行alter system set enable_goldengate_replication = true scope=both;

目标端安装完ogg之后，开始配置ogg进程

test:/ogg@oracle11g>cd/ogg/ --这里ogg安装目录为/ogg

test:/ogg@oracle11g>./ggsci

OracleGoldenGate Command Interpreter for Oracle

Version12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

Linux, x64,64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38

Operating systemcharacter set identified as UTF-8.

GGSCI(oracle11g) 1> edit params mgr

PORT 7809

DYNAMICPORTLIST 7840-7914

PURGEOLDEXTRACTS /ogg/dirdat/extlt/lt*, USECHECKPOINTS,MINKEEPFILES 10

AUTORESTART ER *, RETRIES 3, WAITMINUTES 5

USERID ogg@rac, PASSWORD ogg

PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5,FREQUENCYMINUTES 30

PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5,FREQUENCYMINUTES 30

GGSCI(oracle11g) 2>

添加rep进程R_RAC

GGSCI (oracle11gas ogg@test) 63> add replicat R_RAC, exttrail/ogg/dirdat/replt/rac/lt,checkpointtable ogg.chkpt

REPLICAT added.

配置R_RAC 的参数文件

GGSCI (oracle11g) 2> edit paramsR_RAC

REPLICAT R_RAC

SETENV(ORACLE_HOME = "/u01/app/oracle2/product/11.2.0/db_1")

SETENV (ORACLE_SID= "test")

ASSUMETARGETDEFS

USERID ogg,PASSWORD ogg

DISCARDFILE/ogg/dirrpt/r_rac.dec,APPEND,MEGABYTES 5

DDL INCLUDE MAPPED

DDLOPTIONS REPORT

DBOPTIONSDEFERREFCONST

DBOPTIONSLOBWRITESIZE 102400

OBEY /ogg/dirprm/ogg_tables_RAC_REP.txt –该里面的内容为：map TEST.TEST,target TEST.TEST;

GGSCI (oracle11g) 3>

以上配置结束执行，依次开启刚才配置的E_RAC,P_TEST,R_RAC三个进程

源端开启E_RAC,P_TEST进程：

GGSCI (rac1 as ogg@rac1) 16> start E_RAC

Sending START request to MANAGER ...

EXTRACT E_RAC starting

GGSCI(rac1) 27> start P_TEST

SendingSTART request to MANAGER ...

EXTRACT P_TEST starting

目的端开启R_RAC 进程

GGSCI (oracle11g as ogg@test) 82> start R_RAC

Sending START request to MANAGER ...

REPLICAT R_RAC starting

4. 测试

在源端3个节点分别在test里插入一笔数据

注意：这里要在目的端也要有相同结构的test表

节点1：

SQL> insert into test.testvalues(1,'rac1');

1 row created.

SQL> commit;

Commit complete

节点2：

SQL> insert into test.test values(2,'rac2');

1 row created.

SQL> commit;

Commit complete

节点3：

SQL> insert into test.test values(3,'rac3');

1 row created.

SQL> commit;

Commit complete

再目的端查看：

test:/ogg/dirdat/replt/rac@oracle11g>sqlplus/ as sysdba

SQL*Plus: Release 11.2.0.4.0 Production onThu Mar 16 16:48:27 2017

Connected to:

Oracle Database 11g Enterprise EditionRelease 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

SQL> select * from test.test;

IDNAME

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

1 rac1

3 rac3

2 rac2

SQL>

至此RAC 到单机的OGG 配置完成。

注意这里提示我的/ogg/install 下不为空，可以直接选择忽略就可以了，不会有影响的

雨花石~

关注

2
点赞
踩
12

收藏

觉得还不错? 一键收藏
打赏
3
评论
Oracle RAC 到单机 OGG 配置测试

RAC OGG RAC到单机 ACFS OGG 创建ACFS fbo_ggs_Linux_x64_shiphome.zip RAC OGG 单实例Rac与单实例不同之处在于rac上的goldengate 需要安装在共享存储上asmca 创建ACFS chekpointtable add extract E_RAC,tranlog,threads 3 begin nowadd exttrail /ogg/install/dirdat/extlt/rac/lt,extract E_RAC add
复制链接

扫一扫