Goldengate_For_Oracle10[1].2_RAC_and_ASM_配置与管理

Goldengate_For_Oracle10[1].2_RAC_and_ASM_配置与管理 GoldenGate For Oracle10.2 .0.1 RAC and ASM 配置和管理 一、测试环境: 源数据库 10.51.0.2/10.51.0.3(RAC)ASM文件系统 Linux 4.7 x32 Oracle 10.2.0.1 目标数据库 Linux 4.7 x32 10.51.0.111 Oracle 10.2.0.1 二、安装(略) 三.GoldenGate配置 以下操作需要在source和target数据库端均操作 1 1.配置mgr进程 GGSCI (RAC1)>edit param mgr 输入:port 8900 2.配置GLOBALS GGSCI (RAC1)>edit param ./GLOBALS 输入: GGSCHEMA ggs CHECKPOINTTABLE ggs.ggs_checkpoint 3.启动mgr进程 GGSCI (RAC1) 5> start mgr Manager started. 4.我们可以通过下面的命令看到状态: GGSCI (RAC1) 6> info all Program Status Group Lag Time Since Chkpt 2 MANAGER RUNNING 四.DML同步测试 测试数据使用GoldenGate自带测试脚本生成，脚本位于安装目录(/home/oracle/ggs)下: 注意:经过测试，GoldenGate管理所用schema与数据同步的schema应该分离，使用不用的schema，并且source和target应该 使用同名的tablespace。 在源端进行: [oracle@RAC1 ggs]$ sqlplus / as sysdba SQL> create user test identified by hrss; User created. SQL> grant connect,resource,dba to hrss; Grant succeeded. SQL> conn hrss/hrss SQL> @ demo_ora_create.sql -----(创建tcustmer、tcustord两 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf ) DROP TABLE tcustmer 3 Table created. Table created. SQL> select table_name from user_tables; TABLE_NAME ------------------------------ TCUSTMER TCUSTORD SQL> conn ggs/ggs Connected. SQL> @ chkpt_ora_create.sql Table created. GGSCI (RAC1) 8> dblogin userid ggs, password ggs Successfully logged into database. GGSCI (RAC1) 10> add trandata hrss.* Logging of supplemental redo data enabled for table HRSS.TCUSTMER. Logging of supplemental redo data enabled for table HRSS.TCUSTORD. 4 GGSCI (RAC1) 12> start mgr MGR is already running. GGSCI (RAC1) 11> edit params racext extract racext userid ggs,password ggs exttrail ./dirdat/dd tranlogoptions altarchivelogdest instance wlcbhrss1 +data/wlcbhrss/archive/arch1,altarchivelogdest instance wlcbhrss2 +data/wlcbhrss/archive/arch2 TRANLOGOPTIONS ASMUSER sys@ASM1, ASMPASSWORD wlcbhrss table hrss.*; 由于source服务器是rac环境，两节点archivelog分别在共享存储上，归档日志必须要能够同时读到，由于source服务器使用ASM存放数据文件，在extract中必须这定ASM实例的登录用户sys和密码，此处ASM1是tnsnames.ora中的串名。 GGSCI (RAC1) 15> add extract racext,tranlog,begin now,threads 2 EXTRACT added. threads 2表示，extract是由RAC的两个节点中抽取数据 5 GGSCI (RAC1) 16> add exttrail ./dirdat/dd,extract racext,megabytes 10 EXTTRAIL added. megabytes 10表示，extract生成的文件每个大小为10M 下面配置pump用于传输extract生成的日志文件到target端: GGSCI (RAC1) 17> edit param racpump extract racpump passthru userid ggs,password ggs rmthost 10.51.0.111,mgrport 8900 rmttrail ./dirdat/dd table hrss.*; 6 GGSCI (RAC1) 18> add extract racpump,exttrailsource ./dirdat/dd EXTRACT added. GGSCI (RAC1) 19> add rmttrail ./dirdat/dd,extract racpump,megabytes 10 RMTTRAIL added. GGSCI (RAC1) 20> start racext Sending START request to MANAGER ... EXTRACT RACEXT starting GGSCI (RAC1) 21> start racpump 7 Sending START request to MANAGER ... EXTRACT RACPUMP starting GGSCI (RAC1) 22> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING RACEXT 00:00:00 00:00:06 EXTRACT RUNNING RACPUMP 00:00:00 00:00:01 在目标端进行: 配置mgr进程 GGSCI (DG1)1>edit param mgr 输入:port 8900 GGSCI (DG1) 2> start mgr Manager started. 8 GGSCI (DG1) 3> dblogin userid ggs , password ggs Successfully logged into database. GGSCI (DG1) 4> edit params ./GLOBALS checkpointtable ggs.GGS_CHECKPOINT ~ ~"./GLOBALS" [New] 1L, 35C written GGSCI (DG1) 5> exit DG1-> ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.1.1.0.0 Build 078 9 Linux, x86, 32bit (optimized), Oracle 10 on Jul 28 2010 13:24:18 Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved. GGSCI (DG1) 1> dblogin userid ggs, password ggs Successfully logged into database. GGSCI (DG1) 2> add checkpointtable No checkpoint table specified, using GLOBALS specification (ggs.GGS_CHECKPOINT)... Successfully created checkpoint table GGS.GGS_CHECKPOINT. GGSCI (DG1) 3> edit params racrep replicat racrep 10 userid ggs,password ggs handlecollisions ASSUMETARGETDEFS DISCARDFILE ./dirrpt/rorabb.dsc,PURGE MAP hrss.*, TARGET ggs.*; GGSCI (DG1) 4> start racrep Sending START request to MANAGER ... REPLICAT RACREP starting GGSCI (DG1) 5> info all Program Status Group Lag Time Since Chkpt 11 MANAGER RUNNING REPLICAT RUNNING RACREP 00:00:00 00:00:02 GGSCI (DG1) 6> add replicat racrep,checkpointtable ggs.ggs_checkpoint,exttrail ./dirdat/dd REPLICAT added. GGSCI (DG1)7> start racrep Sending START request to MANAGER ... REPLICAT RACREP starting 进行测试: 12 首先在原端: [oracle@RAC1 ggs]$ sqlplus hrss/hrss SQL*Plus: Release10.2.0.1.0 - Production on Fri Oct 29 18:44:55 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database10gEnterprise Edition Release10.2.0.1.0 - Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options SQL> @demo_ora_insert.sql 13 1 row created. 1 row created. 1 row created. 1 row created. Commit complete. SQL> select count(*) from TCUSTMER; COUNT(*) ---------- 2 登陆目标端: 14 SQL> select count(*) from TCUSTMER; COUNT(*) ---------- 2 SQL> select * from TCUSTMER; CUST NAME CITY ST ---- ------------------------------ -------------------- -- WILL BG SOFTWARE CO. SEATTLE WA JANE ROCKY FLYER INC. DENVER CO 测试完成， 15 维护管理: 如果目标机发生故障宕机，重新启动后做什么样的维护工作 在目标端: 启动监听 启动数据库 启动ggsci 启动mgr 启动 start racrep 在源端 info racrep ,showch 启动传输进程 16 补充ASM实例配置: (1)在几个节点上编辑listener.ora和tnsnames.ora 文件，添加红字内容。 rac1-> vim listener.ora # listener.ora.rac1 Network Configuration File: /oracle/product/10.2.0/db/network/admin/listener.ora.rac1 # Generated by Oracle configuration tools. LISTENER_RAC1 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521)(IP = FIRST)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.51.0.2)(PORT = 1521)(IP = FIRST)) ) ) SID_LIST_LISTENER_RAC1 = (SID_LIST = 17 (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /oracle/product/10.2.0/db) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_NAME = ASM) (ORACLE_HOME = /oracle/product/10.2.0/db) (SID_NAME = +ASM1) ) ) rac1-> vi tnsnames.ora # tnsnames.ora Network Configuration File: /oracle/product/10.2.0/db/network/admin/tnsnames.ora # Generated by Oracle configuration tools. 18 LISTENERS_WLCBHRSS = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521)) ) WLCBHRSS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) b ) ) 19 SI = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = si) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5) ) ) ) 20 WLCBHRSS2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = wlcbhrss) (INSTANCE_NAME = wlcbhrss2) ) ) WLCBHRSS1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = wlcbhrss) 21 (INSTANCE_NAME = wlcbhrss1) ) ) ASM1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ASM) (INSTANCE_NAME = +ASM1) (UR=A) ----允许远程调用,这个必须有，否则ASM实例不连接。 ) ) ASM2 = (DESCRIPTION = 22 (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ASM) (INSTANCE_NAME = +ASM2) (UR=A) ) ) EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) 23 ) ) (2)在几个节点上重新启动监听实例 rac1-> lsnrctl stop LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 23-MAR-2011 13:59:59 Copyright (c) 1991, 2005, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) The command completed successfully rac1-> lsnrctl start LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 23-MAR-2011 14:00:23 Copyright (c) 1991, 2005, Oracle. All rights reserved. 24 Starting /oracle/product/10.2.0/db/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 10.2.0.1.0 - Production System parameter file is /oracle/product/10.2.0/db/network/admin/listener.ora Log messages written to /oracle/product/10.2.0/db/network/log/listener.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521))) Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production Start Date 23-MAR-2011 14:00:26 Uptime 0 days 0 hr. 0 min. 3 sec Trace Level off Security ON: Local OS Authentication 25 SNMP OFF Listener Parameter File /oracle/product/10.2.0/db/network/admin/listener.ora Listener Log File /oracle/product/10.2.0/db/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521))) The listener supports no services The command completed successfully rac1-> (3)测试ASM的连接 rac1-> export ORACLE_SID=+ASM1 rac1-> sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 23 14:10:41 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn sys@asm1 as sysdba Enter password: Connected. 26 补充Oracle 10gR2 RAC+ASM 归档设置 从Oracle 10gR2开始,对于RAC数据库设置归档模式非常方便,只需要在其中任何一个节点上操作,剩余节点instance关闭即可,下面是一个Oracle 10.2.0.2版本2个节点的RAC数据库打开归档模式的示例,归档存放在+data磁盘组下的wlcbhrss目录下 第一步:规划目录 实例1的归档路径:+DATA/wlcbhrss/archive/arch1 实例2的归档路径:+DATA/wlcbhrss/archive/arch2 [oracle@rac02] /home/oracle> export ORACLE_SID=+ASM2 [oracle@rac02] /home/oracle> asmcmd -p ASMCMD> ls DATA/ ASMCMD> cd data/wlcbhrss ASMCMD> mkdir archive ASMCMD> cd archive 27 ASMCMD> mkdir arch1 arch2 ASMCMD> ls -ltr Type Redund Striped Time Sys Name N arch1/ N arch2/ ASMCMD> 第二步:关闭实例2 [oracle@rac02] /home/oracle> sqlplus "/as sysdba" SQL*Plus: Release 10.2.0.2.0 - Production on Sat Oct 9 18:07:16 2010 Copyright (c) 1982, 2005, Oracle. All Rights Reserved. 28 Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options 在节点1上确认实例2已经关闭 [oracle@rac01] /home/oracle> crs_stat -t Name Type Target State Host ------------------------------------------------------------ 29 ora.demo.db application ONLINE ONLINE rac02 ora....o1.inst application ONLINE ONLINE rac01 ora....o2.inst application OFFLINE OFFLINE ora....SM1.asm application ONLINE ONLINE rac01 ora....01.lsnr application ONLINE ONLINE rac01 ora.rac01.gsd application ONLINE ONLINE rac01 ora.rac01.ons application ONLINE ONLINE rac01 ora.rac01.vip application ONLINE ONLINE rac01 ora....SM2.asm application ONLINE ONLINE rac02 ora....02.lsnr application ONLINE ONLINE rac02 ora.rac02.gsd application ONLINE ONLINE rac02 ora.rac02.ons application ONLINE ONLINE rac02 ora.rac02.vip application ONLINE ONLINE rac02 第三步:设置归档参数 在实例1上操作即可 [oracle@rac01] /home/oracle> sqlplus "/as sysdba" 30 SQL*Plus: Release 10.2.0.2.0 - Production on Sat Oct 9 18:07:51 2010 Copyright (c) 1982, 2005, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options SQL> show parameter cluster_database NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cluster_database boolean TRUE cluster_database_instances integer 2 SQL> show parameter instance_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ 31 instance_name string demo1 SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 3 Current log sequence 4 SQL> alter system set log_archive_dest_1='LOCATION=+data/wlcbhrss/archive/arch1' sid='wlcbhrss1'; System altered. SQL> alter system set log_archive_dest_1='LOCATION=+data/wlcbhrss/archive/arch2' sid='wlcbhrss2'; System altered. SQL> show parameter log_archive_dest_1 NAME TYPE VALUE ------------------------------------ ----------- ---------------------------------------- log_archive_dest_1 string LOCATION=+DATA/wlcbhrss/archive/arch1 32 log_archive_dest_10 string 第四步:打开归档模式 继续在实例1上操作 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 218103808 bytes Fixed Size 1260032 bytes Variable Size 75499008 bytes Database Buffers 138412032 bytes Redo Buffers 2932736 bytes Database mounted. SQL> alter database archivelog; 33 Database altered. SQL> alter database open; Database altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination +data/wlcbhrss/archive/arch1 Oldest online log sequence 3 Next log sequence to archive 4 Current log sequence 4 SQL> 好了,到这里就按照我预先的规划把这个2个节点的RAC数据库打开到归档模式下了,归档文件存放在FRA磁盘组中. 第五步:打开剩余实例 34 [oracle@rac01] /home/oracle> crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.demo.db application ONLINE ONLINE rac02 ora....o1.inst application ONLINE ONLINE rac01 ora....o2.inst application OFFLINE OFFLINE ora....SM1.asm application ONLINE ONLINE rac01 ora....01.lsnr application ONLINE ONLINE rac01 ora.rac01.gsd application ONLINE ONLINE rac01 ora.rac01.ons application ONLINE ONLINE rac01 ora.rac01.vip application ONLINE ONLINE rac01 ora....SM2.asm application ONLINE ONLINE rac02 ora....02.lsnr application ONLINE ONLINE rac02 ora.rac02.gsd application ONLINE ONLINE rac02 ora.rac02.ons application ONLINE ONLINE rac02 ora.rac02.vip application ONLINE ONLINE rac02 [oracle@rac01] /home/oracle> srvctl start instance -d wlcbhrss -i wlcbhrss2 35 [oracle@rac01] /home/oracle> crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.demo.db application ONLINE ONLINE rac02 ora....o1.inst application ONLINE ONLINE rac01 ora....o2.inst application ONLINE ONLINE rac02 ora....SM1.asm application ONLINE ONLINE rac01 ora....01.lsnr application ONLINE ONLINE rac01 ora.rac01.gsd application ONLINE ONLINE rac01 ora.rac01.ons application ONLINE ONLINE rac01 ora.rac01.vip application ONLINE ONLINE rac01 ora....SM2.asm application ONLINE ONLINE rac02 ora....02.lsnr application ONLINE ONLINE rac02 ora.rac02.gsd application ONLINE ONLINE rac02 ora.rac02.ons application ONLINE ONLINE rac02 ora.rac02.vip application ONLINE ONLINE rac02 [oracle@rac01] /home/oracle> 36 第六步:测试验证 [oracle@rac01] /home/oracle> sqlplus "/as sysdba" SQL*Plus: Release 10.2.0.2.0 - Production on Sat Oct 9 18:26:51 2010 Copyright (c) 1982, 2005, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options SQL> alter system archive log current; System altered. SQL>ASMCMD> pwd +FRA/demo/arch1 37 ASMCMD> ls -ltr Type Redund Striped Time Sys Name N 1_4_731958872.dbf => +FRA/demo/ARCHIVELOG/2010_10_09/thread_1_seq_4.256.731960815 ASMCMD> cd .. ASMCMD> cd arch2 ASMCMD> ls -ltr Type Redund Striped Time Sys Name N 2_2_731958872.dbf => +FRA/demo/ARCHIVELOG/2010_10_09/thread_2_seq_2.257.731960817 ASMCMD> [oracle@rac02] /home/oracle> sqlplus "/as sysdba" SQL*Plus: Release 10.2.0.2.0 - Production on Sat Oct 9 18:34:17 2010 Copyright (c) 1982, 2005, Oracle. All Rights Reserved. 38 Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination +data/wlcbhrss/archive/arch2 Oldest online log sequence 2 Next log sequence to archive 3 Current log sequence 3 SQL> 39