Oracle 11g 安装配置GoldenGate.docx

1、Oracle 11g 安装配置GoldenGateOracle 11gR2 RAC和GoldenGate都是Oracle比较热门的产品，经过简单的学习和阅读文档，配置单节点的GoldenGate进行数据的复制相信不是什么太有难度的事情，但是对于利用GoldenGate进行RAC系统到RAC系统的复制，还是有些配置的技巧和策略设置的，前阵子就遇到一个这样一个问题：假设源和目标分别是两节点的RAC系统，如何保证目标部分节点失效的时候replicate会自动切换？ 其实如果了解GG的工作机制和RAC的资源管理，问题的解决就十分清晰了。今天就从系统的介绍下11gR2 RAC上OGG (Oracle G

2、oldenGate的简称，下同)的完整配置步骤，并简单谈谈如何解决上面这个场景的问题。第一阶段：下载OGG（可以参考之前的单节点的复制例子，不再赘述）OGG的下载地址第二阶段：OGG的安装1）登录源端的RAC系统中的任一个节点，并在ACFS上建立一个供OGG使用的共享目录，比如叫/cloudfs/goldengate2）解压OGG的安装包到/cloudfs/goldengate目录3) 设置好OGG工作的环境变量，比如export LIBRARY_PATH=/cloudfs/goldengate:$ORACLE_HOME/lib:$LD_LIBRARY_PATH4）启动ggsci并创建目录，然

3、后进行必要的设置，启动manager$ ggsci GGSCI create subdirs (optional, support for DDL/Sequence) Create and edit the parameter file for GLOBALS: GGSCI EDIT PARAMS ./GLOBALSAdd this line to GLOBALS parameter file: GGSCHEMA ggs NOTE: ggs is the example OGG user and will be used in the rest of this document. GGSCI

4、EDIT PARAMS mgr Add the following lines to Manager parameter file: PORT 7809 AUTOSTART ER * AUTORESTART ER * GGSCI START mgr5）在目标端重复上面的步骤1-4，注意目录名的使用，我们在目标端使用/mycloudfs/goldengate以示区分。第三阶段：源和目标RAC数据库准备步骤1） Create OGG user ggs on both the source and target database, connect to database using SQL*Plus

5、 as SYSDBA: SQL CREATE USER ggs IDENTIFIED BY ggs; SQL GRANT CONNECT,RESOURCE,DBA TO ggs; 2）(optional, add Oracle sequence replication support) On both source and target database, go to OGG directory and run this SQL, enter OGG user ggs as prompted: SQL sequence.sql 3） Enable supplemental logging on

6、 source ODA database: SQL ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; SQL ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; SQL ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS; SQL ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS; SQL ALTER SYSTEM SWITCH LOGFILE;

7、(Optional) Add Oracle DDL replication support 4） On the source system, go to OGG directory, connect to database using SQL*Plus as SYSDBA. SQL GRANT EXECUTE ON utl_file TO ggs; 5 On the source system, run the following script, provide OGG user ggs as prompted. SQL marker_setup.sql SQL ddl_setup.sql N

8、OTE: enter INITIALSETUP when prompted for the mode of installation. SQL role_setup.sql SQL ddl_enable.sql SQL ddl_pin ggs NOTE: ggs here is the OGG user.第四阶段：配置源端的extract group1） Issue the following command to log on to the database. GGSCI DBLOGIN USERID ggs, PASSWORD ggs 2） Create a primary Extract

9、 group myext: GGSCI ADD EXTRACT myext, TRANLOG, BEGIN NOW, THREADS 2 NOTE: THREADS value is the number of your RAC instances. 3） Create a local trail. The primary Extract writes to this trail, and the data-pump Extract reads it. GGSCI ADD EXTTRAIL /cloudfs/goldengate/dirdat/et, EXTRACT myext NOTE: e

10、t is the example trail identifier for Extract myext. 4） Create and edit the parameter file for Extract myext: GGSCI EDIT PARAMS myext Add following lines to this parameter file: EXTRACT myext SETENV (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) USERID ggsggdb, PASSWORD ggs TRANLOGOPTIONS D

11、BLOGREADER THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000 EXTTRAIL /cloudfs/goldengate/dirdat/etDYNAMICRESOLUTION DDL INCLUDE ALL TABLE hr.*; NOTE 1: make sure the SQL*Net connection string ggdb works. NOTE 2: hr is the example schema which will be synchronized to the target system.第五阶段：在源端配置data pum

12、p extract group1）Create a data pump group mypump: GGSCI ADD EXTRACT mypump, EXTTRAILSOURCE /cloudfs/goldengate/dirdat/et, BEGIN now 2） Specify a remote trail that will be created on the target system. GGSCI ADD RMTTRAIL /mycloudfs/goldengate/dirdat/rt, EXTRACT mypump NOTE: rt is the example trail id

13、entifier for Extract mypump, and use the target OGG directory /mycloudfs/goldengate here. 3） Create and edit the parameter file for Extract mypump: GGSCI EDIT PARAMS mypump Add following lines to this parameter file: EXTRACT mypump RMTHOST rac12box-scan, MGRPORT 7809 RMTTRAIL /mycloudfs/goldengate/d

14、irdat/rtPASSTHRU TABLE hr.*; NOTE: RMTHOST is the target host. If you also prefer to set up HA on the target system, specify the VIP for your target system as RMTHOST, otherwise just use the IP address/hostname of your target system. 4） Start Extract myext and mypump: GGSCI START myext GGSCI START m

15、ypump 5） Check the status of OGG processes: GGSCI info all第六阶段：在目标端配置Replicat group1） Create a Replicat group rept, which reads trails from Extract mypump: GGSCI ADD REPLICAT rept, EXTTRAIL /mycloudfs/goldengate/dirdat/rt, nodbcheckpoint 2） Create and edit the parameter file for Replicat rept: GGSCI

16、 EDIT PARAMS rept Add following lines to this parameter file, assume the same ORACLE_HOME and target database ggdb as in source ODA environment: REPLICAT rept SETENV (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) USERID ggsggdb,PASSWORD ggs ASSUMETARGETDEFS HANDLECOLLISIONS REPERROR (DEFAUL

17、T, DISCARD) DDLERROR DEFAULT DISCARD DDLOPTIONS REPORT DISCARDFILE /mycloudfs/goldengate/repsz.dsc,append,megabytes 100 MAP hr.*, TARGET hr.*; NOTE: make sure the SQL*Net connection string ggdb works. 3） Start Replicat rept: GGSCI START rept 4） Check the status of OGG processes: GGSCI info all第七阶段：验

18、证Goldengate功能1） Log on to source database as user hr, do some simple DDL and DML operations.2） Check the data change has been captured by Extract on source system: GGSCI STATS myextGGSCI STATS mypump 3）Log on to target system as oracle user, check the status of Replicat rept: GGSCI STATS rept 4） Com

19、pare the output and make sure data change is synchronized.5）(optional, for further HA setup) Stop OGG on source system: GGSCI STOP myextGGSCI STOP mypumpGGSCI STOP mgr 6）(optional, for further HA setup) Stop OGG on target system: GGSCI STOP reptGGSCI STOP mgr上回书说到在11gR2上配置GoldenGate，今天应要求继续贴出下篇，下面的步

20、骤是针对GoldenGate的HA配置第一步 Add VIP resource for OGG1. Create new VIP resource, use IP address 10.245.48.47 as example, login as root and run:# /u01/app/11.2.0/grid/bin/appvipcfg create -network=1 -ip=10.245.48.47 -vipname=ggatevip -user=root NOTE 1: /u01/app/11.2.0/grid is the default Oracle GRID infras

21、tructure software directory.NOTE 2: The example VIP resource name is ggatevip and will be used in the rest of this document.NOTE 3: -network refers to the network number, default value is 1. And you can find the network number using this command: # /u01/app/11.2.0/grid/bin/crsctl stat res -p |grep -

22、ie .network -iesubnet |grep -ie name -ie subnetNAME=workUSR_ORA_SUBNET=10.245.48.0net1 indicates this is network 1, and the second line indicates the subnet on which the VIP will be created. 2. Allow oracle user to start the VIP, run this command as root:# /u01/app/11.2.0/grid/bin/crsctl setperm res

23、ource ggatevip -u user:oracle:r-x 3. Log on as oracle user, start the VIP resource:$ /u01/app/11.2.0/grid/bin/crsctl start resource ggatevip 4. Validate VIP is running:$ /u01/app/11.2.0/grid/bin/crsctl status resource ggatevipNAME=ggatevipTYPE=app.appvip.typeTARGET=ONLINESTATE=ONLINE on node452 NOTE

24、: The TARGET and STATE should both be ONLINE. 5. Ping the VIPs IP address(10.245.48.47) and make sure its reachable.NOTE: If you also prefer to set up HA on target system, follow the same steps, and make necessary changes to directory, VIP resource name, IP address, etc.第二步 Develop an agent script1.

25、 Save the script in a file 11gr2_gg_action.scr and copy it to OGG directory.NOTE: Make necessary changes to GGS_HOME(OGG directory) and ORACLE_HOME. 2. Make sure the script is executable:$ chmod +x 11gr2_gg_action.scrNOTE: If you also prefer to set up HA on target system, follow the same steps, and

26、make necessary changes to GGS_HOME, ORACLE_HOME in this file.第三步 Register OGG resource in Oracle Clusterware1. Register OGG as a resource in Oracle Clusterware:$ /u01/app/11.2.0/grid/bin/crsctl add resource ggateapp -type cluster_resource -attr ACTION_SCRIPT=/cloudfs/goldengate/11gr2_gg_action.scr,

27、CHECK_INTERVAL=30, START_DEPENDENCIES=hard(ggatevip,ora.ggdb.db) pullup(ggatevip), STOP_DEPENDENCIES=hard(ggatevip) NOTE 1: ggateapp is the example OGG resource name and will be used in the rest of this document.NOTE 2: ora.ggdb.db is the resource name for database ggdb. 2. Check the ggateapp resour

28、ce is available as Oracle Clusterware resource:$ /u01/app/11.2.0/grid/bin/crsctl status resource ggateappNAME=ggateappTYPE=cluster_resourceTARGET=OFFLINESTATE=OFFLINE NOTE: If you also prefer to set up HA on target system, follow the same steps, and make necessary changes to directory, VIP and datab

29、ase resourcename, etc.第四步 Start the OGG resource1. Start the resource, from now on you should always use Oracle Clusterware to start OGG: $ /u01/app/11.2.0/grid/bin/crsctl start resource ggateapp 2. Check the status of OGG resource:$ /u01/app/11.2.0/grid/bin/crsctl status resource ggateappNAME=ggate

30、appTYPE=cluster_resourceTARGET=ONLINESTATE=ONLINE on node452NOTE: TARGET and STATE should both be ONLINE. 3. Check status of OGG processes:GGSCI info allProgram Status Group LagTime Since ChkptMANAGER RUNNINGEXTRACT RUNNING MYEXT 00:00:0700:00:04EXTRACT RUNNING MYPUMP 00:00:00 00:00:05 NOTE: If you also prefer to set up HA on target system, follow the same steps.第五步. Test OGG resource failover1. Log on to node source node as oracle user:$ /u01/app/11.2.0/grid/bin/crsctl relocate resource ggateapp -fCRS-2673: Attempting to stop ggateapp on node452