OracleRAC數(shù)據(jù)庫測試報(bào)告

1、1. RAC配置1.1環(huán)境架構(gòu)網(wǎng)上交易客戶I 口 口客戶日常業(yè)務(wù)RA集群業(yè)務(wù)直銷中間件局部查詢DSDBServer3DSDBServer4DSDBServer2丿Logical DataGuard硬件DSDBServer1-4 配置相同硬件供應(yīng)商：HP型號(hào)CPUIn tel(R) Xeo n(R) CPU E7 883732內(nèi)存64GB磁盤網(wǎng)絡(luò)主機(jī)IPVIP私有IPdsdbserver1dsdbserver22dsdbserver33dsdbserver44對應(yīng)網(wǎng)卡BOND0BONDO:1ETH2ETH0&&ETH1網(wǎng)速1000Mb/s100Mb/s私有網(wǎng)絡(luò)為 百兆建議更換為千

2、兆網(wǎng)絡(luò)連接操作系統(tǒng)DSDBServer1-4 配置相同操作系統(tǒng)版本：內(nèi)核：內(nèi)核參數(shù):=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數(shù)據(jù)庫配置1.2.1 架構(gòu)Public IPPublic VIPPublic IPPublic VIPTNSi!r1AMDB1, 1AMDBN1L_l -TNSPrivate IPCRSCRSASM DISKASM DIS

3、KRAW3EMCPOWERRAW3OCRVotingRAWJRAW2OCRVotingRAW1RAW：1.2.2 版本軟件版本CRSORACLE1.2.3 Spfile核心參數(shù)參數(shù)項(xiàng)值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)剩余可擴(kuò)展空間(M)剩余可利用率DSSYNCPERMANENT1000EXAMPLEPERMANENT10032668STATPERMANENT1005020SYSAUXPERMANENT88031888SYSTEMPERMANENT47032298UNDOTBS1UNDO5520551927248UNDOTBS2UNDO165031118UNDOTBS3UNDO197530793USERSPERMANENT532763XNDSPERMANENT3091234624XNNSPERMANENT6016267521.2.5SQLN

5、et網(wǎng)絡(luò)偵聽節(jié)點(diǎn) 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) )_)節(jié)點(diǎn) 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. 節(jié)點(diǎn)連接測試2.1單實(shí)例節(jié)點(diǎn)連接測試方法配置客戶端對 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)測試結(jié)果測試項(xiàng)測試結(jié)果備注AMDB1單節(jié)點(diǎn)連接正常AMDB2單節(jié)點(diǎn)連接正常AMDB3單節(jié)點(diǎn)連接正常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測試結(jié)果測試項(xiàng)測試結(jié)果備注基于效勞的多地址連接正常3. 負(fù)載均衡測試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運(yùn)行測試4腳本運(yùn)行完后，統(tǒng)計(jì)結(jié)果3.2測試結(jié)果實(shí)例名連接次數(shù)AMDB1196AMDB2337AMDB34674. FailOver 測試4.1

14、測試方法1、分別利用以下四種配置連接數(shù)據(jù)庫連接方式名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、查看連接的實(shí)際實(shí)例名select in sta nce_n ame from v$in sta nee;3、關(guān)閉實(shí)例Shutdow n immediate4、重新查看連接實(shí)例名select in sta nce_n ame from v$in sta nee;5、重連數(shù)據(jù)庫4.2測試結(jié)果連接方式名切換結(jié)果重連數(shù)據(jù)庫結(jié)果NAMDB成功切換正常NAMDB00斷開正常NAMDB01斷開正常NAMDB1斷開拒絕5. 節(jié)點(diǎn)參加測試5.1測試方法前提：網(wǎng)絡(luò)、操作系統(tǒng)級(jí)別SSH主機(jī)名

18、等RAC配置已完成，詳見 RAC安裝手冊1添加CRS登錄集群一節(jié)點(diǎn)老節(jié)點(diǎn)，執(zhí)行ORA_CRS_HOME/oui/bin的腳本，按提示一步一步做2更新ONS登錄集群一節(jié)點(diǎn)老節(jié)點(diǎn)，執(zhí)行命令ocrdump -stdout -ke yn ame查看ONS端口，或者在 ORA_CRS_HOME/opmn/conf下的查看并記錄端口號(hào)例如6200翻開ORA_CRS_HOME/bin目錄執(zhí)行命令./racg ons add_c onfig dsdbserver4:6200執(zhí)行olsondes查看添加效果3添加數(shù)據(jù)庫登錄集群一節(jié)點(diǎn)老節(jié)點(diǎn)，翻開ORACLE_HOME/out/bin執(zhí)行addNode.sh4更新

19、新節(jié)點(diǎn)網(wǎng)絡(luò)配置登錄集群一節(jié)點(diǎn)老節(jié)點(diǎn)，netca重新配置操作節(jié)點(diǎn)與新參加節(jié)點(diǎn)5配置新節(jié)點(diǎn)ASM登錄集群一節(jié)點(diǎn) 老節(jié)點(diǎn)，執(zhí)行 DBCA,選擇 “ con figure automatic storage man ageme nt 選擇新節(jié)點(diǎn)配置 ASM。6配置新節(jié)點(diǎn)實(shí)例登錄集群一節(jié)點(diǎn)老節(jié)點(diǎn)用system用戶登錄數(shù)據(jù)庫，執(zhí)行如下語句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;執(zhí)行 DBCA命令->選擇 “ Instanee Management ->"Add Instanee 7更新所有節(jié)點(diǎn)網(wǎng)絡(luò)配置任一節(jié)點(diǎn)執(zhí)行netca，對所有節(jié)點(diǎn)listener重新配置5.2測試結(jié)果節(jié)點(diǎn)正常參加，并完成壓力測試6. 節(jié)點(diǎn)刪除測試6.1測試方法登陸非刪除節(jié)點(diǎn)1export ORACLE_SID=< 集群實(shí)例 >2執(zhí)行 DBCA3 選擇 Instance Mana

21、germent 實(shí)例管理 4 選擇 Delete an Instance 刪除實(shí)例5選擇需要?jiǎng)h除實(shí)例的 RAC數(shù)據(jù)庫，數(shù)據(jù)sysdba用戶和密碼6選擇需要?jiǎng)h除的實(shí)例，并點(diǎn)擊 Finish 完成，后面點(diǎn)擊 Ok-OK-OK 執(zhí)行： crs_stat 可查詢7執(zhí)行 NETCA8選擇 Cluster configuration 集群配置9選擇被刪除節(jié)點(diǎn)10選擇 Listener configuration 偵聽配置11選擇 Delete ，完成刪除節(jié)點(diǎn)偵聽 執(zhí)行： crs_stat 可查詢12執(zhí)行 srvctl stop asm -n node13執(zhí)行 srvctl remove asm -n no

22、de登陸刪除節(jié)點(diǎn)14刪除 ORACLE_BASEadmin+ASM 和 ORACLE_HOMEdbs*ASM* 附注徹底刪除ASM的步驟：1, 刪除 /etc/oratab 文件對應(yīng)的 +ASM 記錄2, 刪除$0RACLE_BASE/admin/+ASM1-2下面的 a|b|c|u dump 目錄3, 刪除$ORACLE_HOME/dbs下面與asm有關(guān)的文件登陸刪除節(jié)點(diǎn)，執(zhí)行命令 ：15更新刪除節(jié)點(diǎn) $ORACLE_HOME/oui/bin ./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME CLUSTER_NODES="

23、;dsdbserver4" -local16執(zhí)行數(shù)據(jù)庫刪除 可選$ORACLE_HOME/oui/bin/runInstaller -deinstall登陸非刪除節(jié)點(diǎn)，執(zhí)行命令 ：17更新非刪除節(jié)點(diǎn) $ORACLE_HOME/oui/bin ./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES=dsdbserver1,dsdbserver2,dsdbserver3"18在任意保存節(jié)點(diǎn)中執(zhí)行racgons命令，刪除ONS配置racgons remove_config node2:

24、remote_port例如：racgons remove_config dsdbserver4:6200remote port 可以登錄被刪除節(jié)點(diǎn)的 CRS_HOME/opmn/conf/ons.config登陸被刪除節(jié)點(diǎn)執(zhí)行以下命令 ：19停止和刪除被刪除節(jié)點(diǎn)的node applicationssrvctl stop nodeapps -n dsdbserver4srvctl remove nodeapps -n dsdbserver420更新被刪除節(jié)點(diǎn)的 CRSB息$ORA_CRS_HOME/oui/bin./runinstaller -updateNodeList ORACLE_HOME

25、=$ORA_CRS_HOME "CLUSTER_NODES=dsdbserver4"CRS=TRUE -local登陸非刪除節(jié)點(diǎn)，執(zhí)行命令：21丨更新非刪除節(jié)點(diǎn)的集群信息$ORA_CRS_HOME/oui/bin./runInstaller -updateNodeList ORACLE_HOME=$ORA_CRS_HOME"CLUSTER_NODES=dsdbserver1,dsdbserver2,dsdbserver3" CRS=TRUE22登錄被刪除節(jié)點(diǎn)，刪除 CRS軟件$ORA_CRS_HOME/oui/bin./runInstaller -dei

26、nstall23如果沒有執(zhí)行22步驟，那么登錄被刪除節(jié)點(diǎn)執(zhí)行crsctl stop crs再登錄任意非刪除節(jié)點(diǎn)到$ORA_CRS_HOME/install 丨執(zhí)行./rootdeletenodes dsdbserver4,46.2測試結(jié)果將DSDBServer4正常刪除7. 壓力測試7.1測試方法利用swingbench免費(fèi)軟件，版本安裝 java后，不需配配置文件7.1.1 建立OE數(shù)據(jù)啟動(dòng)模板，生成 OE數(shù)據(jù)。按提示一步一步做，庫存數(shù)據(jù)庫 10G7.1.2 啟動(dòng)協(xié)調(diào)效勞器執(zhí)行，啟動(dòng)協(xié)調(diào)效勞器，協(xié)調(diào)進(jìn)程啟動(dòng)成功。7.1.3 注冊節(jié)點(diǎn)到協(xié)調(diào)效勞器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