Exadata一體機(jī)使用的50個小技巧

1、Exadata 管理 3Exadata 性能優(yōu)化 3讓表使用 flash cache 3可以使用如下公式計算 Exadata 特性對 IO 的優(yōu)化 3可以使用如下公式計算 Exadata Storage Index 對 Disk IO 減少的共享 3可以使用如下計算 Flash Cache 的使用率 3收集 cell 級別的表緩存統(tǒng)計信息的方法 3確認(rèn)在使用 write back flash cache 4確認(rèn)所有的 griddisk 均為正常 online 狀態(tài) 4確認(rèn)所有的 flashdisk 均為正常 online 狀態(tài) 4啟用 write back flash cache 的方法 4確

2、認(rèn) Exadata 計算節(jié)點間的網(wǎng)絡(luò)帶寬 6檢測多個ORACLE_HOM是否RDS可用？ 6relink ORACLE_HOME的 RDS 6不同配置 Exadata 的推薦最大并行度 . 6Exadata EHCC 支持 6Exadata 壓縮信息 . 6針對寫日志 redo 特別多的應(yīng)用建議啟用 Smart Flash logging 特性 6Exadata DB 管理 7Exadata 存儲空間計算 7查看 cell 軟件版本 . 7了解 cell 的溫度 . 7cell 存儲節(jié)點的日志存放位置 7列出 cell 中的 alert history 7為 cell 創(chuàng)建一個告警閾值 . 8

3、cell 可用性監(jiān)控 8如何禁用 Smart Scan? 8如何禁用 storage index? 8如何禁用 flash cache? 8cell 相關(guān)的數(shù)據(jù)庫視圖有以下這些視圖 8配置 Inter-Database IORM 8如何禁用布隆過濾 Bloom Fliter 9Exadata 數(shù)據(jù)備份 9backup 備份速率 9recovery 應(yīng)用日志恢復(fù)速率 9standby database 搭建 9Exadata 恢復(fù) 9cell 救護(hù) . 9Exadata 部署 10onecommanc下載 10Exadata 安裝前準(zhǔn)備工作 10環(huán)境檢查 . 10驗證網(wǎng)絡(luò)連通性 . 10Exad

4、ata 監(jiān)控 11exachk 健康檢查腳本 12Exadata 文檔信息 12Exadata 硬件篇 12常規(guī) 12默認(rèn)密碼，以下是 Exadata 中 cell/db node IB 等的默認(rèn)密碼： 12硬件常規(guī)巡檢： . 12檢測 Exadata 數(shù)據(jù)庫機(jī)器上的硬件和固件版本是否匹配？ 13檢測軟件版本與平臺是否匹配？ . 13為 cell 啟用告警 . 13監(jiān)控 磁盤故障 . 13更換 Storage Cell 硬盤 . 13觀察 Database Server 磁盤狀態(tài) 14觀察 Database Server RAID 狀態(tài) . 14Storage Cell 加電啟動 14檢測me

5、mory ECC錯誤 14若發(fā)現(xiàn) Exadata 上存在磁盤損毀則：. 14檢測 cell server Cache Policy. 14Exadata 停機(jī)： . 16Exadata 啟動 . 16Infiniband 篇 16啟停 IBSwitch 17檢查 IB 鏈路狀態(tài) . 17查看 IB 網(wǎng)絡(luò)拓?fù)錉顟B(tài) . 17診斷 IB 鏈路沒有錯誤 . 17查看 IB 網(wǎng)絡(luò)連線 . 17查看 IB 健康狀態(tài) . 18IB 健康檢查 18IB 故障處理 18IB 硬件監(jiān)控 18Switch 端口錯誤 18Link 狀態(tài) 18Subnet manager . 19CISCO交換機(jī) 19例行維護(hù)操作 .

6、 19KVM 19PDU 19故障處理 . 19Exadata 管理Exadata性能優(yōu)化讓表使用flash cacheALTER TABLE vobject name storage (CELL_FLASH_CACHE KEEP);可以使用如下公式計算 Exadata特性對10的優(yōu)化1- (cell physical IO interconnect bytes returned by smart scan)/ (cell IO un compressed bytes + cell physical IO bytes saved by storagein dex) * 100可以使用如下公式計

7、算 Exadata Storage Index對Disk IO減少的共享 (cell physical IO bytes saved by storage index / physical read total bytes)* 100可以使用如下計算Flash Cache的使用率(cell flash cache read hit / physical read total IO requests) * 100收集cell級別的表緩存統(tǒng)計信息的方法SQL SELECT data_object_id FROM DBA_OBJECTS WHERE object_nameEMP;OBJECT_ID

8、57435CellCLI LIST FLASHCACHECONTENTWHERE objectNumber=57435 DETAIL cachedSize: 495438874 dbID: 70052hitCount: 415483missCou nt: 2059objectNumber: 57435tableSpaceNumber: 1確認(rèn)在使用 write back flash cache#dcli -g /cell_group -l root cellcli -e “ list cell attributes flashcachemode ”Results:flashCacheMode:

9、 WriteBack - write back flash cache is enabled flashCacheMode: WriteThrough - write back flash cache is not enabled確認(rèn)所有的 griddisk 均為正常 online 狀態(tài)# dcli -g cell_group -l root cellcli -e list griddisk attributes asmdeactivationoutcome, asmmodestatus確認(rèn)所有的 flashdisk 均為正常 online 狀態(tài)# dcli -g cell_group -l

10、root cellcli -e list flashcache detail啟用 write back flash cache 的方法A. Enable Write Back Flash Cache using a ROLLING method(RDBMS & ASM instanee is up - enabling write-back flashcache one cell at a time)Log onto the first cell that you wish to enable write-back FlashCache1. Drop the flash cache on th

11、at cell# cellcli -e drop flashcache2. Check if ASM will be OK if the grid disks go OFFLINE. The followingcommand should returnYes for the grid disks being listed:# cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome3. Inactivate the griddisk on the cell# cellcli e alter gri

12、ddisk all in active4. Shut down cellsrv service# cellcli -e alter cell shutdown services cellsrv5. Set the cell flashcache mode to writeback# cellcli -e “ alter cell flashCacheMode=writeback ”6. Restart the cellsrv service# cellcli -e alter cell startup services cellsrv7. Reactivate the griddisks on

13、 the cell# cellcli - e alter griddisk all active8. Verify all grid disks have been successfully put online using thefollowing command:# cellcli -e list griddisk attributes name, asmmodestatus9. Recreate the flash cache# cellcli -e create flashcache all10. Check the status of the cell to confirm that

14、 its now in WriteBackmode:# cellcli -e list cell detail | grep flashCacheMode11. Repeat these samesteps again on the next cell. However, before taking another storage server offline, execute the following making sure asmdeactivationoutcome displays YES:# cellcli -e list griddisk attributes name,asmm

15、odestatus,asmdeactivationoutcomeB . Enable Write Back Flash Cache using a NON-ROLLING method(RDBMS & ASM instances are down while enabling write-back flashcache)1. Drop the flash cache on that cell# cellcli -e drop flashcache2. Shut down cellsrv service# cellcli -e alter cell shutdown services cells

16、rv3. Set the cell flashcache mode to writeback# cellcli -e “alter cell flashCacheMode=writeback ”4. Restart the cellsrv service# cellcli -e alter cell startup services cellsrv5. Recreate the flash cache # cellcli -e create flashcache all確認(rèn) Exadata 計算節(jié)點間的網(wǎng)絡(luò)帶寬可以采用 nc nc-1.84-10.fc6.x86_64.rpm 獲得檢測多個OR

17、ACLE_HOM是否RDS可用？dcli -g /opt/oracle.SupportTools/onecommand/dbs_group -l oracle md5sum $ORACLE_HOME/lib/libskgxp11.sorelink ORACLE_HOME的 RDSdcli -g /opt/oracle.SupportTools/onecommand/dbs_group -l oracle “ export ORACLE_HOME=$ORACLE_HOME;cd pwd;makei*mk ipc_rds ”dcli -g /opt/oracle.SupportTools/onec

18、ommand/dbs_group -l oracle “ ex portORACLE_HOME=$ORACLE_HOME;cd pwd;makei*mk ioracle ” | egrep rm|mv.*oracle 不同配置 Exadata 的推薦最大并行度配置CPU個 數(shù)推薦最大 ParallelismFull Rack64 coreDOP=256Half Rack32 coreDOP=128Quarter Rack16 coreDOP=64Exadata EHCC 支持Exadata的EHC（支持寬表 最大支持1000個字段的表，而不像11.1中的壓縮僅 支持最多 255 列的表Exad

19、ata 壓縮信息通過 dbms_compression.get_compression_ratio 可以獲得表的壓縮信息針對寫日志 redo 特別多的應(yīng)用建議啟用 Smart Flash logging 特性CREATE FLASHLOG ALLCREATE FLASHLOG ALL SIZE=1GCREATE FLASHLOG CELLDlSK=1,fd2 CREATE FLASHLOG CELLD=1,fd2 SIZE=1GExadata DB 管理Exadata 存儲空間計算FreeMB（最大可用空間）=GridDisk*12*Num of Cells/RedundancyUsableM

20、B （支持1個CELL故障的最大可用空間）=GridDisk*12*（Num of Cells - 1） /Redu nda ncy查看 cell 軟件版本imagehistoryimageinfo了解 cell 的溫度dcli -g cell_group -l root“ipmitool sensor | grepInlet Amb Temp ”cell 存儲節(jié)點的日志存放位置$ADR_BASE/diag/asm/cell/host name/trace/alert.log$ADR_BASE/diag/asm/cell/host name/trace/ms-odl.*$ADR_BASE/di

21、ag/asm/cell/hostname/trace/svtrc_0.trc ps -ef | grep“ cellsrv 100 $ADR_BASE/diag/asm/cell/hostname/incident/*/var/log/messages*, dmesg /var/log/sa/*/var/log/cellos/*列出 cell 中的 alert historylist alerthistory where notificationState like 023 and severity like warning|critical and examinedBy = NULL;為 c

22、ell 創(chuàng)建一個告警閾值cellclicreate threshold CD_IO_ERRS_MIN warning=1, comparison= = , occurrences=1, observation=1;cell 可用性監(jiān)控一般建議使用 EMGC Oracle Exadata Storage Server Management Plug-In 控如何禁用 Smart Scan?設(shè)置 Cell_offload_processing=false如何禁用 storage index?設(shè)置 _kcfis_storageidx_disabled=true如何禁用 flash cache?11.

23、2.0.2 以后 設(shè)置 _kcfis_keep_in_cellfc_enabled=false11.2.0.1 中設(shè)置 _kcfis_control1=1cell 相關(guān)的數(shù)據(jù)庫視圖有以下這些視圖select * from sys.GV_$CELL_STATE;select * from sys.GV_$CELL;select * from sys.GV_$CELL_THREAD_HISTORY;select * from sys.GV_$CELL_REQUEST_TOTALS;select * from sys.GV_$CELL_CONFIG;配置 Inter-Database IORMCel

24、lCLI alter iormpla n dbpla n = (n ame = producti on, level = 1, allocati on = 100),-(n ame = test, level = 2, allocati on = 80),-(name = other, level = 2, allocation = 20)IORMPLAN successfully alteredCellCLI alter iormplan activeIORMPLAN successfully alteredCellCLI list iormplan detailname: cell4_IO

25、RMPLANcatPlan:dbPlan: name=production,level=1,allocation=100 name=test,level=2,allocation=80name=other,level=2,allocation=20status: active如何禁用布隆過濾 Bloom Fliter設(shè)置 _bloom_pruning_enabled=falseExadata 數(shù)據(jù)備份backup 備份速率Exadata下rman備份的速率從1通道到8通道 大約為1003MB/S到2081MB/S, 視乎配置不同也略微有區(qū)別recovery 應(yīng)用日志恢復(fù)速率exadata re

26、covery 的速率大約為每秒 6001000MB/s的歸檔日志Standby databaSe 搭建對于50TB的sta ndby database 搭建，若使用infini ba nd + 4rman通道大約耗費5.5小時，若使用GigE則在18個小時左右Exadata 恢復(fù)cell 救護(hù)可以通過 /opt/oracle.SupportTools/make_cellboot_usb 腳本創(chuàng)建部 USB cellboot_usb_in_rescure_modeExadata 部署on ecomma n(下載可以下載 patch (9935478) ONECOMMAND FOR Exadata

27、 11gR2Exadata 安裝前準(zhǔn)備工作1. 下載安裝介質(zhì)包括 Grid, Database,Patches 等2. 硬件設(shè)備到貨驗收并安裝就緒3. 規(guī)劃DBM用的管理網(wǎng)，生產(chǎn)網(wǎng)，ILOM等用的網(wǎng)段和IP地址4. 配置DNS服務(wù)器5. 將IP地址和域名注冊到DNSK務(wù)器6. 配置NTP服務(wù)器7. 網(wǎng)絡(luò)連線環(huán)境檢查1. 檢查DBM主機(jī)的ethO網(wǎng)卡是否可以通過cisco交換機(jī)被訪問2. 檢查 hardware and firmware profile 是否正確3. 驗證 InfiniBand Network驗證網(wǎng)絡(luò)連通性1. 登陸第一臺數(shù)據(jù)庫服務(wù)器使用 sh 腳本驗證網(wǎng)絡(luò)連通性2. 驗證DNS

28、是否正常3. 驗證 NTP 服務(wù)器是否正常安裝 Exadata Storage Server Image Patch (root user)1. 在 db server 和 cell server 上為 root 用戶配置 SSH# /opt/oracle.SupportTools/onecommand/setssh.sh -s -u root -p password-n N -h dbs_group2. 檢查當(dāng)前 Cell storage server 的 Exadata Image 版本3. 安裝最新的 Patch 具體步驟詳見 Readme4. 驗證當(dāng)前 Exadata Image ve

29、rsion#cd /opt/oracle.SupportTools/firstconf#dcli -l root -g quarter imagehistory | grep i Versio n使用OneComman工具完成DBM勺配置安裝1. #cd /opt/oracle.SupportTools/onecommand2. Display the onecommand steps# ./deploy112.sh -i - I3. The steps in order are Step 0 = ValidateThisNodeSetupStep 1 = SetupSSHForRootSte

30、p 2 = ValidateAllNodesStep 3 = UnzipFilesStep 4 = UpdateEtcHostsStep 5 = CreateCellipnitoraStep 6 = ValidateHWStep 7 = ValidateIBStep 8 = ValidateCellStep 9 = PingRdsCheckStep 10 = RunCalibrateStep 11 = ValidateTimeDateStep 12 = UpdateConfigStep 13 = CreateUserAccountsStep 14 = SetupSSHForUsersStep

31、15 = CreateOraHomesStep 16 = CreateGridDisksStep 17 = InstallGridSoftwareStep 18 = RunGridRootScriptsStep 19 = Install112DBSoftwareStep 20 = Create112ListenerStep 21 = RunAsmCaStep 22 = UnlockGIHomeStep 23 = UpdateOPatchStep 24 = ApplyBPStep 25 = RelinkRDSStep 26 = LockUpGIStep 27 = SetupCellEmailAl

32、ertsStep 28 = RunDbcaStep 29 = SetupEMDbControlStep 30 = ApplySecurityFixesStep 31 = ResecureMachineTo run a command#./deploy112.sh - i - s NWhere N corresponds to a step numberExample to run step 0Exadata 監(jiān)控exachk健康檢查腳本exachk腳本可以以daemon形式后臺運行./exachk - d start以 daemon形式 cluster support 運行./exachk -

33、 cluster no des no de1, node N- d start!Exadata文檔信息Exadata 的官方文檔 docs.oracle./cd/E50790_01/welcome.html另外文檔還保存在您 cell 的/opt/oracle/cell/doc/目錄下。Exadata硬件篇常規(guī)默認(rèn)密碼，以下是 Exadata中cell/db node IB等的默認(rèn)密碼:組件登陸默認(rèn)密碼Storage Cellsroot nm 2userwelcome1Infiniband Switchroot nm 2userwelcome1 cha ngemeDB節(jié)點rootwelcome

34、1CELL CLIcelladminwelcomeILOMrootwelcome1KVM SwitchAdm in or noneGigE switch初始安裝后asmsnmp的賬號一般也是welcome1硬件常規(guī)巡檢: 在機(jī)房例行檢查時，需要從 Exadata 機(jī)箱后方查看 Exadata 中是否有黃燈報警， 如果有，記錄位置，即時登錄OEM/ILOM集成的第三方監(jiān)控工具查明原因，定位部件，即時維護(hù)。Exadata 一體機(jī)健康檢查腳本 exachk，參考 document 1070954.1檢測 Exadata 數(shù)據(jù)庫機(jī)器上的硬件和固件版本是否匹配？/opt/oracle.SupportTo

35、ols/CheckHWnFWProfile返回如下結(jié)果說明版本匹配：SUCCESSThe hardware and firmware profile matches one of the supported profile檢測軟件版本與平臺是否匹配？/opt/oracle.SupportTools/CheckSWProfile.sh -c為 cell 啟用告警ALTER CELL smtpServer= mailserver.maildomain. , - smtpFromAddr=firstname.lastnamemaildomain.,smtpToAddr= firstname.last

36、namemaildomain.,smtpFrom= Exadata cell , -smtpPort= , smtpUseSSL= TRUE ,notificationPolicy= critical,warning,clear , notificationMethod=mail;alter cell validate mail;監(jiān)控 磁盤故障當(dāng)通過機(jī)房例行檢查發(fā)現(xiàn)硬件黃燈警告或通過監(jiān)控工具（命令行/ILOM/第三方工具）發(fā)現(xiàn)故障并確定位置后 , 可進(jìn)行更換操作。更換 Storage Cell 硬盤命令行登錄 Cell, 判斷故障硬盤 , 例如 :CellCLI LIST PHYSICALDI

37、SK WHERE diskType=HardDisk AND status=critical DETAIL觀察 Database Server 磁盤狀態(tài)rootdm01db01 # cd /opt/MegaRAID/MegaCli/rootdm01db01 MegaCli# ./MegaCli64 -Pdlist -aAll | grep“ Slot|Firmware ”觀察 Database Server RAID 狀態(tài)rootdmOldbOl MegaCIi# ./MegaCli64 -LdInfo -IAII- aAllStorage Cell 加電啟動遠(yuǎn)程登陸Storage Cell控

38、制器ILOM,執(zhí)行PowerOn,其它為系統(tǒng)的自動啟動過程, 知道 Storage Cell 就緒CellCLI LIST GRIDDISK若沒有 Active, 需:CellCLI ALTER GRIDDISK ALL ACTIVE等grid disk Active后,ASM會自動同步，使grid disk Online,查看狀態(tài)：CellCLI LIST GRIDDISK ATTRIBUTES name, asmmodestatus確認(rèn)AS贓據(jù)自動重新分布是否已經(jīng)開始或完成。Grid用戶登錄+ASMS例執(zhí)行:select * from v$asm_operation;通過 EM SYSLO

39、G Cellcli 、ILOM查看是否有告警解除信息檢測memory ECC錯昔誤ipmitool sel list | grep ECC | cut -f1 -d : | sort -u若發(fā)現(xiàn) Exadata 上存在磁盤損毀則：使用 /opt/oracle.SupportTools/sundiag.sh 收集詳細(xì)信息 并發(fā)給 oracle support檢測 cell server Cache Policycell08# MegaCli64 -LDInfo -Lall -aALL | grep Current CachePolicyCurrent Cache Policy: WriteThr

40、ough, ReadAheadNone, Direct, No Write Cache if Bad BBU cell09# MegaCli64 -LDInfo -Lall -aALL | grep Current CachePolicyCurrent Cache Policy: WriteBack, ReadAheadNone,Direct, No Write Cache if Bad BBUDefault Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBUCurrent Cache Policy

41、: WriteThrough, ReadAheadNone, Direct, No Write Cache if Bad BBUCache policy is in WBWould recommend proactive battery repalcement.Example :a. /opt/MegaRAID/MegaCli/MegaCli64 -LDGetProp -Cache -LALL -aALL#( Will list the cache policy)b. /opt/MegaRAID/MegaCli/MegaCli64 -LDSetProp -WB -LALL -aALL#( Wi

42、ll try to change teh policy from xx to WB)So policy Change to WB will not come into effect immediatelySet Write Policy to WriteBack on Adapter 0, VD 0 (target id: 0) successBattery capacity is below the threshold value檢測 cell BBU 備用電池狀態(tài)：cell08# /opt/MegaRAID/MegaCli/MegaCli64 -AdpBbuCmd -GetBbuStatu

43、s -a0BBU status for Adapter: 0BatteryType: iBBUVoltage: 4061 mVCurrent: 0 mATemperature: 36 CBBU Firmware Status:Charging Status : NoneVoltage : OKTemperature : OKLearnCycle Requested : NoLearn Cycle Active : NoLearn CycleStatus : OKLearn Cycle Timeout : NoI2c Errors Detected : NoBattery Pack Missin

44、g : NoBattery Replacement required : NoRemaining Capacity Low : YesPeriodic Learn Required : No Battery state:GasGuageStatus:Fully Discharged : NoFully Charged : YesDischarging : YesInitialized : YesRemaining Time Alarm : NoRemaining Capacity Alarm: NoDischarge Terminated : NoOver Temperature : NoCh

45、arging Terminated : NoOver Charged : NoRelative State of Charge: 99 %Charger System State: 49168Charger SystemCtrl: 0Charging current: 0 mAAbsolute state of charge: 21 %MaxError: 2 % Exit Code: 0x00批量檢測BBU信息：dcli -g /cell_group -l root -t uname-srm ; head -1 /etc/*release ; uptime | cut -d, -f1 ; im

46、agehistory ; ipmitool sunoem cli show /SP system_description system_identifier | grep =;ipmitool sun oem cli show /SP/policy FLASH_ACCELERATOR_CARD_INSTALLED /opt/MegaRAID/MegaCli/MegaCli64 -AdpBbuCmdGetBbuStatus -aO | egrep -i BBU|Battery|Charge:|Fully|Low|Learn; | tee /tmp/Exa lnfo.logExadata 停機(jī)：1

47、. 確認(rèn)無業(yè)務(wù)訪問，以root用戶登錄第1個數(shù)據(jù)庫服務(wù)器節(jié)點2停止數(shù)據(jù)庫（詳見RAC/ASM隹護(hù)之RAC啟停章節(jié)）3. 停止 Cluster# GRID_HOME/grid/bi n/crsctl stop cluster -all4. 停除本機(jī)以外的數(shù)據(jù)庫節(jié)點# dcli -l root -c dm01db02,dm01db03,dm01db04 shutdow n -h -y now5. 停存儲服務(wù)器cell_group 可自編輯，執(zhí)行時并可由root用戶讀取該文件（askmaclean.） 另需參考Storage Cell存儲維護(hù)Storage Cell停機(jī)章節(jié)信息后方可執(zhí)行下述 命令#

48、 dcli -l root -g cell_group shutdow n -h -y now6. 停本機(jī) # shutdow n -h -y now7. 此時可通過ILOM遠(yuǎn)程關(guān)機(jī)8. 整機(jī)下電（關(guān)PDU）Exadata 啟動1、為機(jī)柜加電（SWITCH自然加電）打開PDU開關(guān)進(jìn)行加電，服務(wù)器指示燈都變綠，慢閃 若需手工開機(jī)數(shù)據(jù)庫服務(wù)器、存儲服務(wù)器需要按住其開關(guān)5秒。也可在ILOM中點擊Cell的Poweron開關(guān)進(jìn)行開機(jī)，服務(wù)器指示燈為綠色長亮, 再點擊DB Server的Poweron開關(guān)進(jìn)行開機(jī)，服務(wù)器指示燈為綠色長亮。2、檢查是否有黃燈報警。3、啟動數(shù)據(jù)庫、應(yīng)用等。Infiniban

49、d 篇啟停 IBSwitch1. InfiniBand Switch 電源的開啟或關(guān)閉InfiniBand Switch提供冗余電源,分別插在Exadata的2個冗余PDU電源上,并 隨PDU機(jī)柜電源開啟或關(guān)閉 , 若關(guān)閉 InfiniBand Switch 需斷掉 InfiniBand Switch 的的冗余電 源。2.查看OEM等是否有相關(guān)報警。ILOM無法報警從 cell1 的 cellcli 中查看 list alerthistory 可以看到3. 從 db01 查看網(wǎng)絡(luò)拓?fù)錉顟B(tài)rootdm01db01 # cd /opt/oracle.SupportTools/ibdiagtoolsrootdm01db01 ibdiagtools# ./verify-topology -t halfrack4. 插入 InfiniBand 電源線 , 查看 InfiniBand Switch 正常啟動檢查 IB 鏈路狀態(tài)# /opt