OracleRAC数据库测试报告

1、1. RAC配置1.1环境架构网上交易客户I 口 口客户日常业务RA集群业务直销中间件局部查询DSDBServer3DSDBServer4DSDBServer2丿Logical DataGuard硬件DSDBServer1-4 配置相同硬件供应商：HP型号CPUIn tel(R) Xeo n(R) CPU E7 883732内存64GB磁盘网络主机IPVIP私有IPdsdbserver1dsdbserver22dsdbserver33dsdbserver44对应网卡BOND0BONDO:1ETH2ETH0&&ETH1网速1000Mb/s100Mb/s私有网络为 百兆建议更换为千

2、兆网络连接操作系统DSDBServer1-4 配置相同操作系统版本：内核：内核参数:=65536=65536=68719476736=4294967296=4096=250 32000 100 128-max = 101365n et.ipv4.ip_local_port_ra nge = 9000 65500=1048576=1048576=262144=2621441.2数据库配置1.2.1 架构Public IPPublic VIPPublic IPPublic VIPTNSi!r1AMDB1, 1AMDBN1L_l -TNSPrivate IPCRSCRSASM DISKASM DIS

3、KRAW3EMCPOWERRAW3OCRVotingRAWJRAW2OCRVotingRAW1RAW：1.2.2 版本软件版本CRSORACLE1.2.3 Spfile核心参数参数项值sga target32Gsga max size32Gpga aggregate target16GProcesses1000sessi ons1100optimizer modeALL ROWSlocal liste ner(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521)remote liste nerLISTENERS AMDBspfile+DATA/amd

4、b/1.2.4 表空间分配表空间名字类型表空间大小(M)已使用(M)剩余可扩展空间(M)剩余可利用率DSSYNCPERMANENT1000EXAMPLEPERMANENT10032668STATPERMANENT1005020SYSAUXPERMANENT88031888SYSTEMPERMANENT47032298UNDOTBS1UNDO5520551927248UNDOTBS2UNDO165031118UNDOTBS3UNDO197530793USERSPERMANENT532763XNDSPERMANENT3091234624XNNSPERMANENT6016267521.2.5SQLN

5、et网络侦听节点 dsdbserver2 上 Listener.oraLISTENER_DSDBSERVER2 = (DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = dsdbservep2(PORT = 1521)(IP = FIRST)(ADDRESS = (PROTOCOL = TCP)(HOST = 10.88.3.12)(PORT = 1521)(IP = FIRST) )_)节点 dsdbserver2 上 tnsnames.oraLISTENERS_AMDB =(ADDRESS_LIST =(AD

6、DRESS = (PROTOCOL = TCP)(HOST = dsdbservep)(PORT = 1521)(ADDRESS = (PROTOCOL = TCP)(HOST = dsdbservvjp2(PORT = 1521)(ADDRESS = (PROTOCOL = TCP)(HOST = dsdbservep3(PORT = 1521)AMDB3 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = dsdbservep3(PORT = 1521) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_

7、NAME = AMDB)(INSTANCE_NAME = AMDB3)AMDB2 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = dsdbservep)(PORT = 1521) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = AMDB) (INSTANCE_NAME = AMDB2)AMDB1 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = dsdbservep)(PORT = 1521) (CONNECT_DATA =(SERVER

8、= DEDICATED)(SERVICE_NAME = AMDB) (INSTANCE_NAME = AMDB1)AMDB =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = dsdbservep)(PORT = 1521) (ADDRESS = (PROTOCOL = TCP)(HOST = dsdbservep2(PORT = 1521) (ADDRESS = (PROTOCOL = TCP)(HOST = dsdbservep)(PORT = 1521) (LOAD_BALANCE = yes)(CONNECT_DATA =(SERVER

9、= DEDICATED)(SERVICE_NAME = AMDB)AMDB_STANDBY=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = dsdbserver4)(PORT = 1521) )(CONNECT_DATA =(SERVICE_NAME = AMDB)2. 节点连接测试2.1单实例节点连接测试方法配置客户端对 AMDB2和AMDB3同样配置，sqlplus命令下逐一登录NAMDB1 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.88.3.51

10、)(PORT = 1521)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = AMDB)(INSTANCE_NAME = AMDB1)测试结果测试项测试结果备注AMDB1单节点连接正常AMDB2单节点连接正常AMDB3单节点连接正常2.2基于效劳的多地址连接测试方法配置客户端，sqlplus命令下登录NAMDB=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.88.3.51)(PORT = 1521) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.88.3.

11、52)(PORT = 1521) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.88.3.53)(PORT = 1521) (FAILOVER = ON)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = AMDB) (FAILOVER_MODE =(TYPE = SELECT)(METHOD = BASIC)(RETRIES = 20)(DELAY = 5)222测试结果测试项测试结果备注基于效劳的多地址连接正常3. 负载均衡测试3.1测试方法客户端配置NAMDB=(DESCRIPTION =(ADDRESS =

12、 (PROTOCOL = TCP)(HOST = 10.88.3.51)(PORT = 1521) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.88.3.52)(PORT = 1521) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.88.3.53)(PORT = 1521) (FAILOVER = ON)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = AMDB) (FAILOVER_MODE =(TYPE = SELECT)(METHOD = BASIC)(RETRIES =

13、20)(DELAY = 5) 1bat 脚本 test.batecho offfor /l %i in (1,1,1000) do (echo %isqlplus -s system/gfjj1234 namdb )pause2SQL脚本 test.sqlSet headi ng offcol"In sta nce_n ame format a20select in sta nce_n ame from v$in sta nee /Exit;3运行测试4脚本运行完后，统计结果3.2测试结果实例名连接次数AMDB1196AMDB2337AMDB34674. FailOver 测试4.1

14、测试方法1、分别利用以下四种配置连接数据库连接方式名Tnsn ames配己置NAMDBNAMDB=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.88.3.51)(PORT = 1521) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.88.3.52)(PORT = 1521) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.88.3.53)(PORT = 1521) (FAILOVER = ON)(CONNECT_DATA =(SERVER = DEDICATED)(SERVIC

15、E_NAME = AMDB) (FAILOVER_MODE =(TYPE = SELECT)(METHOD = BASIC)(RETRIES = 20)(DELAY = 5)NAMDB00NAMDB00=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.88.3.51)(PORT = 1521) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.88.3.52)(PORT = 1521) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.88.3.53)(PORT = 1521) (CON

16、NECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = AMDB)NAMDB01NAMDBO仁(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.88.3.51)(PORT = 1521)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = AMDB)NAMDB1NAMDB1 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.88.3.51)(PORT = 1521)(CONNECT_DATA =(SE

17、RVER = DEDICATED)(SERVICE_NAME = AMDB)(INSTANCE_NAME = AMDB1)2、查看连接的实际实例名select in sta nce_n ame from v$in sta nee;3、关闭实例Shutdow n immediate4、重新查看连接实例名select in sta nce_n ame from v$in sta nee;5、重连数据库4.2测试结果连接方式名切换结果重连数据库结果NAMDB成功切换正常NAMDB00断开正常NAMDB01断开正常NAMDB1断开拒绝5. 节点参加测试5.1测试方法前提：网络、操作系统级别SSH主机名

18、等RAC配置已完成，详见 RAC安装手册1添加CRS登录集群一节点老节点，执行ORA_CRS_HOME/oui/bin的脚本，按提示一步一步做2更新ONS登录集群一节点老节点，执行命令ocrdump -stdout -ke yn ame查看ONS端口，或者在 ORA_CRS_HOME/opmn/conf下的查看并记录端口号例如6200翻开ORA_CRS_HOME/bin目录执行命令./racg ons add_c onfig dsdbserver4:6200执行olsondes查看添加效果3添加数据库登录集群一节点老节点，翻开ORACLE_HOME/out/bin执行addNode.sh4更新

19、新节点网络配置登录集群一节点老节点，netca重新配置操作节点与新参加节点5配置新节点ASM登录集群一节点 老节点，执行 DBCA,选择 “ con figure automatic storage man ageme nt 选择新节点配置 ASM。6配置新节点实例登录集群一节点老节点用system用户登录数据库，执行如下语句alter system set LOG_ARCHIVE_CONFIG= ' SEND, RECEIVE, NODG_CONFIG' scope=both;alter system set log_archive_dest_1='LOCATION=

20、/opt/app/oracle/archive' scope=both;alter system set log archive dest 2=" scope=both;执行 DBCA命令->选择 “ Instanee Management ->"Add Instanee 7更新所有节点网络配置任一节点执行netca，对所有节点listener重新配置5.2测试结果节点正常参加，并完成压力测试6. 节点删除测试6.1测试方法登陆非删除节点1export ORACLE_SID=< 集群实例 >2执行 DBCA3 选择 Instance Mana

21、germent 实例管理 4 选择 Delete an Instance 删除实例5选择需要删除实例的 RAC数据库，数据sysdba用户和密码6选择需要删除的实例，并点击 Finish 完成，后面点击 Ok-OK-OK 执行： crs_stat 可查询7执行 NETCA8选择 Cluster configuration 集群配置9选择被删除节点10选择 Listener configuration 侦听配置11选择 Delete ，完成删除节点侦听 执行： crs_stat 可查询12执行 srvctl stop asm -n node13执行 srvctl remove asm -n no

22、de登陆删除节点14删除 ORACLE_BASEadmin+ASM 和 ORACLE_HOMEdbs*ASM* 附注彻底删除ASM的步骤：1, 删除 /etc/oratab 文件对应的 +ASM 记录2, 删除$0RACLE_BASE/admin/+ASM1-2下面的 a|b|c|u dump 目录3, 删除$ORACLE_HOME/dbs下面与asm有关的文件登陆删除节点，执行命令 ：15更新删除节点 $ORACLE_HOME/oui/bin ./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME CLUSTER_NODES="

23、;dsdbserver4" -local16执行数据库删除 可选$ORACLE_HOME/oui/bin/runInstaller -deinstall登陆非删除节点，执行命令 ：17更新非删除节点 $ORACLE_HOME/oui/bin ./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES=dsdbserver1,dsdbserver2,dsdbserver3"18在任意保存节点中执行racgons命令，删除ONS配置racgons remove_config node2:

24、remote_port例如：racgons remove_config dsdbserver4:6200remote port 可以登录被删除节点的 CRS_HOME/opmn/conf/ons.config登陆被删除节点执行以下命令 ：19停止和删除被删除节点的node applicationssrvctl stop nodeapps -n dsdbserver4srvctl remove nodeapps -n dsdbserver420更新被删除节点的 CRSB息$ORA_CRS_HOME/oui/bin./runinstaller -updateNodeList ORACLE_HOME

25、=$ORA_CRS_HOME "CLUSTER_NODES=dsdbserver4"CRS=TRUE -local登陆非删除节点，执行命令：21丨更新非删除节点的集群信息$ORA_CRS_HOME/oui/bin./runInstaller -updateNodeList ORACLE_HOME=$ORA_CRS_HOME"CLUSTER_NODES=dsdbserver1,dsdbserver2,dsdbserver3" CRS=TRUE22登录被删除节点，删除 CRS软件$ORA_CRS_HOME/oui/bin./runInstaller -dei

26、nstall23如果没有执行22步骤，那么登录被删除节点执行crsctl stop crs再登录任意非删除节点到$ORA_CRS_HOME/install 丨执行./rootdeletenodes dsdbserver4,46.2测试结果将DSDBServer4正常删除7. 压力测试7.1测试方法利用swingbench免费软件，版本安装 java后，不需配配置文件7.1.1 建立OE数据启动模板，生成 OE数据。按提示一步一步做，库存数据库 10G7.1.2 启动协调效劳器执行，启动协调效劳器，协调进程启动成功。7.1.3 注册节点到协调效劳器swin gbe nch.bat -co loc

27、alhost -cs 10.88.3.51:1521:AMDB1 -g AMDB1 -dt thinswingbench.bat -co localhost -cs 10.88.3.52:1521:AMDB2 -g AMDB2 -dt thinswingbench.bat -co localhost -cs 10.88.3.53:1521:AMDB3 -g AMDB3 -dt thin7.1.4 修改红色字体d 1 d 2 d 3 i != 一 i = 端端端端裁 户户户户户户J 7.<?xml version = '1.0' en codi ng = 'UTF

28、-8'?><ClusterOverviewC on figurati on xmln s=":domi ni cgiles/sw in gbe nch"><Coord in ator><HostName>localhost </HostName></Coordin ator><Prefere nces><MonitoredUsers> soe</MonitoredUsers> <RefreshPeriod>5</RefreshPeriod>

29、<Startl nOrder>false</Startl nOrder> <StabilitySamples>10</StabilitySamples> <StabilityTolera nce>2</StabilityTolera nce></Preferen ces><Mon itoredNodes><Mo nitoredNode><Groupld> AMDB1 </GroupId><DisplayName>AMDB1 </DisplayName><Co nn ectStri ng> 10.88.3.51:1521:AMDB1 </Co nn ectStri ng></Mo nitoredNode><Mo nitoredNode><Groupld>AMDB2</Groupld><DisplayName>AM