OracleRAC数据库测试报告

1、1.RAC配置1.1环境i.i.i架构日常业务部分查询DSDBServer2DSDBServer3DSDBServer4LogicalDataGuard1.1.2硬件DSDBServer1-4配置相同硬件供给商：HP型号：CPU:Intel(R)Xeon(R)CPUE7883732内存：64GB磁盘：1.1.3网络IPVIP私有IPdsdbserver1dsdbserver22dsdbserver33dsdbserver44对应网卡BOND0BOND0:1ETH2ETH0Ð1网速1000Mb/s100Mb/s私有网络为百兆建议更换为千兆网络连接1.1.4操作系统DSDBServer1-4

2、配置相同操作系统版本：内核：内核参数:=65536=65536=68719476736=4294967296=4096=25032000100128-max=101365net.ipv4.ip_local_port_range=900065500=1048576=1048576=262144=2621441.2数据库配置1.2.1架构PublicIPPublicVIPPublicIPPublicVIPRAW1RAW2RAW3TNSEMCPOWERASMDISKOCRVotingAMDBNASMDISKRAW3TNSAMDB1PrivateIPCRSCRSOCRVotingRAW1RAW21.2

3、.2版本软件版本CRSORACLE1.2.3Spfile核心参数参数项值sga_target32Gsga_max_size32Gpga_aggregate_target16GProcesses1000sessions1100optimizer_modeALL_ROWSlocal_listener(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521)remote_listenerLISTENERS_AMDBspfile+DATA/amdb/1.2.4表空间分配表空间名字类型表空间大小(M)已使用(M)剩余可扩展空间(M)剩余可利用率DSSYNCPERMANENT100

4、0EXAMPLEPERMANENT10032668STATPERMANENT1005020SYSAUXPERMANENT88031888SYSTEMPERMANENT47032298UNDOTBS1UNDO5520551927248UNDOTBS2UNDO165031118UNDOTBS3UNDO197530793USERSPERMANENT532763XNDSPERMANENT3091234624XNNSPERMANENT6016267521.2.5SQLNet网络侦听节点dsdbserver2上Listener.oraLISTENER_DSDBSERVER2=(DESCRIPTION_LI

5、ST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dsdbservvjp(PORT=1521)(IP=FIRST)(ADDRESS=(PROTOCOL=TCP)(HOST=10.88.3.12)(PORT=1521)(IP=FIRST)_)节点dsdbserver2上tnsnames.oraLISTENERS_AMDB=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dsdbserv?jp)(PORT=1521)(ADDRESS=(PROTOCOL=TCP)(HOST=dsdbservvjp(PORT=1521)(A

6、DDRESS=(PROTOCOL=TCP)(HOST=dsdbserv?jp)(PORT=1521)AMDB3=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dsdbserv?jp)(PORT=1521)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=AMDB)(INSTANCE_NAME=AMDB3)AMDB2=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dsdbserveip)(PORT=1521)(CONNECT_DATA=(SERVER=DEDICATED)(SER

7、VICE_NAME=AMDB)(INSTANCE_NAME=AMDB2)AMDB1=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dsdbservzjp)(PORT=1521)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=AMDB)(INSTANCE_NAME=AMDB1)AMDB=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dsdbservzjp)(PORT=1521)(ADDRESS=(PROTOCOL=TCP)(HOST=dsdbserv(PORT=1521)(AD

8、DRESS=(PROTOCOL=TCP)(HOST=dsdbservvjpKPORT=1521)(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=AMDB)AMDB_STANDBY=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dsdbserver4)(PORT=1521)(CONNECT_DATA=(SERVICE_NAME=AMDB)节点连接测试2.1单实例节点连接2.1.1测试方法配置客户端对AMDB2和AMDB3同样配置，sqlplus命令下逐

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

10、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=20)(DELAY=5)2.2.2测试结果测试项测试结果备注基于服务的多地址连接正常负载均衡测试3.1测试方法客户端配置NAMDB=(DESCRI

11、PTION=(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)(SERVICE_NAME=AMDB)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20)(DELAY=5)1bat脚本test.bat

12、echoofffor/l%iin(1,1,1000)do(echo%isqlplus-ssystem/gfjj1234namdb)pause2SQL脚本test.sqlSetheadingoffcolInstance_nameformata20selectinstance_namefromv$instance/Exit;3运行测试4脚本运行完后，统计结果3.2测试结果实例名连接次数AMDB1196AMDB2337AMDB34672. FailOver测试4.1测试方法1、分别利用以下四种配置连接数据库连接方式名Tnsnames配置NAMDBNAMDB=(DESCRIPTION=(ADDRESS

13、=(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=20)(DELAY=5)NAMDB00NAMDB00=(DESCRIPTION=

14、(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)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=AMDB)NAMDB01NAMDB01=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.88.3.51)(PORT=1521)(CONNECT_DATA=(SE

15、RVER=DEDICATED)(SERVICE_NAME=AMDB)NAMDB1NAMDB1=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.88.3.51)(PORT=1521)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=AMDB)(INSTANCE_NAME=AMDB1)2、查看连接的实际实例名selectinstance_namefromv$instance;3、关闭实例Shutdownimmediate4、重新查看连接实例名selectinstance_namefromv$instance;5、重

16、连数据库4.2测试结果连接方式名切换结果重连数据库结果NAMDB成功切换正常NAMDB00断开正常NAMDB01断开正常NAMDB1断开拒绝节点加入测试5.1测试方法前提：网络、操作系统级别SSH主机名等RAC配置已完成，详见RAC安装手册1）添加CRS登录集群一节点老节点，执行ORA_CRS_HOME/oui/bin的脚本，按提示一步一步做2）更新ONS登录集群一节点老节点，执行命令ocrdump-stdout-keyname查看ONS端口，或者在ORA_CRS_HOME/opmn/conf下的查看并记录端口号例如6200打开ORA_CRS_HOME/bin目录执行命令./racgonsad

17、d_configdsdbserver4:6200执行olsondes查看添加效果3）添加数据库登录集群一节点老节点，打开ORACLE_HOME/out/bin执行addNode.sh4）更新新节点网络配置5）配置新节点登录集群一节点选择新节点配置登录集群一节点老节点，netca重新配置操作节点与新加入节点ASM老节点，执行DBCA,选择configureautomaticstoragemanagementASM。6）配置新节点实例登录集群一节点老节点用system用户登录数据库，执行如下语句altersystemsetLOG_ARCHIVE_CONFIG=SEND,RECEIVE,NODG_C

18、ONFIGscope=both;altersystemsetlog_archive_dest_1=LOCATION=/opt/app/oracle/archivescope=both;altersystemsetlogarchivedest2=scope=both;执行DBCA命令-选择aInstanceManagement-AddInstance7）更新所有节点网络配置任一节点执行netca,对所有节点listener重新配置5.2测试结果节点正常加入，并完成压力测试节点删除测试6.1测试方法登陆非删除节点1exportORACLE_SID=2执行DBCA3选择InstanceManager

19、ment（实例管理）4选择DeleteanInstance删除实例5选择需要删除实例的RAC数据库，数据sysdba用户和密码6选择需要删除的实例，并点击Finish完成，后面点击Ok-OK-OK执行：crs_stat可查询7执行NETCA8选择Clusterconfiguration集群配置9选择被删除节点10选择Listenerconfiguration侦听配置11选择Delete,完成删除节点侦听执行：crs_stat可查询12执行srvctlstopasm-nnode13执行srvctlremoveasm-nnode登陆删除节点14删除ORACLE_BASEadmin+ASM和ORAC

20、LE_HOMEdbs*ASM*附注彻底删除ASM的步骤：删除/etc/oratab文件对应的+ASM记录删除$ORACLE_BASE/admin/+ASM1-2下面的a|b|c|udump目录删除$ORACLE_HOME/dbs下面与asm有关的文件登陆删除节点，执行命令：15更新删除节点$ORACLE_HOME/oui/bin./runInstaller-updateNodeListORACLE_HOME=$ORACLE_HOMECLUSTER_NODES=dsdbserver4-local16执行数据库删除（可选）$ORACLE_HOME/oui/bin/runInstaller-dein

21、stall登陆非删除节点，执行命令：17更新非删除节点$ORACLE_HOME/oui/bin./runInstaller-updateNodeListORACLE_HOME=$ORACLE_HOMECLUSTER_NODES=dsdbserver1,dsdbserver2,dsdbserver318在任意保留节点中执行racgons命令，删除ONS配置racgonsremove_confignode2:remote_port例如:racgonsremove_configdsdbserver4:6200remoteport可以登录被删除节点的CRS_HOME/opmn/conf/ons.con

22、fig登陆被删除节点执行以下命令：19停止和删除被删除节点的nodeapplicationssrvctlstopnodeapps-ndsdbserver4srvctlremovenodeapps-ndsdbserver420更新被删除节点的CRS言息$ORA_CRS_HOME/oui/bin./runInstaller-updateNodeListORACLE_HOME=$ORA_CRS_HOMECLUSTER_NODES=dsdbserver4”CRS=TRUE-local登陆非删除节点，执行命令：21更新非删除节点的集群信息$ORA_CRS_HOME/oui/bin./runInstall

23、er-updateNodeListORACLE_HOME=$ORA_CRS_HOMECLUSTER_NODES=dsdbserver1,dsdbserver2,dsdbserver3CRS=TRUE22登录被删除节点，删除CRS件$ORA_CRS_HOME/oui/bin./runInstaller-deinstall23如果没有执行22步骤，则登录被删除节点执行crsctlstopcrs再登录任意非删除节点到$ORA_CRS_HOME/install执行./rootdeletenodesdsdbserver4,46.2测试结果将DSDBServer4正常删除压力测试7.1测试方法利用swingbench免费软件，版本安装java后，不需配配置文件建立OE数据启动模板，生成OE数据。按提示一步一步做，库存数据库10G启动协调服务器执行，启动协调服务器，协调进程启动成功。7.1.1 注册节点到协调服务器swingbench.bat-colocalhost-cs10.88.3.51:1521:AMDB1-gAMDB1-dtthinswingbench.ba