Exadata一体机使用的50个小技巧.doc

Exadata管理3Exadata性能优化3让表使用flash cache3可以使用如下公式计算Exadata特性对IO的优化3可以使用如下公式计算Exadata Storage Index对Disk IO减少的共享3可以使用如下计算Flash Cache的使用率3收集cell级别的表缓存统计信息的方法3确认在使用write back flash cache4确认所有的griddisk均为正常online状态4确认所有的flashdisk均为正常online状态4启用write back flash cache的方法4确认Exadata 计算节点间的网络带宽5检测多个ORACLE_HOME是否RDS可用？6relink ORACLE_HOME的RDS6不同配置Exadata的推荐最大并行度6Exadata EHCC支持6Exadata 压缩信息6针对写日志redo特别多的应用建议启用Smart Flash logging特性6Exadata DB管理6Exadata存储空间计算7查看cell软件版本7了解cell的温度7cell存储节点的日志存放位置7列出cell中的alert history7为cell创建一个告警阈值7cell可用性监控8如何禁用Smart Scan?8如何禁用storage index?8如何禁用flash cache?8cell相关的数据库视图有以下这些视图8配置Inter-Database IORM8如何禁用布隆过滤Bloom Fliter9Exadata数据备份9backup备份速率9recovery应用日志恢复速率9standby database搭建9Exadata恢复9cell 救护9Exadata部署9onecommand下载9Exadata安装前准备工作10环境检查10验证网络连通性10Exadata监控11exachk健康检查脚本11Exadata文档信息12Exadata硬件篇12常规12默认密码，以下是Exadata中cell/db node IB等的默认密码：12硬件常规巡检：12检测Exadata数据库机器上的硬件和固件版本是否匹配？13检测软件版本与平台是否匹配？13为cell启用邮件告警13监控磁盘故障13更换Storage Cell硬盘13观察Database Server 磁盘状态13观察Database Server RAID状态13Storage Cell加电启动14检测memory ECC错误14若发现Exadata上存在磁盘损毁则：14检测 cell server Cache Policy14Exadata 停机：15Exadata 启动16Infiniband篇16启停IBSwitch16检查IB链路状态17查看IB网络拓扑状态17诊断IB链路没有错误17查看IB网络连线17查看IB健康状态17IB健康检查17IB故障处理17IB硬件监控18Switch端口错误18Link状态18Subnet manager18CISCO交换机18例行维护操作18KVM19PDU19故障处理19Exadata管理Exadata性能优化让表使用flash cacheALTER TABLE storage (CELL_FLASH_CACHE KEEP);可以使用如下公式计算Exadata特性对IO的优化 1 (cell physical IO interconnect bytes returned by smart scan)/ (cell IO uncompressed bytes + cell physical IO bytes saved by storage index) * 100可以使用如下公式计算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级别的表缓存统计信息的方法SQL SELECT data_object_id FROM DBA_OBJECTS WHERE object_name=EMP;OBJECT_ID57435CellCLI LIST FLASHCACHECONTENT WHERE objectNumber=57435 DETAIL cachedSize: 495438874dbID: 70052hitCount: 415483missCount: 2059objectNumber: 57435tableSpaceNumber: 1确认在使用write back flash cache#dcli -g /cell_group -l root cellcli -e “list cell attributes flashcachemode”Results:flashCacheMode: WriteBack - write back flash cache is enabledflashCacheMode: WriteThrough - write back flash cache is not enabled确认所有的griddisk均为正常online状态# dcli -g cell_group -l root cellcli -e list griddisk attributes asmdeactivationoutcome, asmmodestatus确认所有的flashdisk均为正常online状态# dcli -g cell_group -l root cellcli -e list flashcache detail启用write back flash cache的方法A. Enable Write Back Flash Cache using a ROLLING method(RDBMS & ASM instance 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 that cell# cellcli -e drop flashcache2. Check if ASM will be OK if the grid disks go OFFLINE. The following command should return Yes for the grid disks being listed:# cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome3. Inactivate the griddisk on the cell# cellcli e alter griddisk all inactive4. 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 the cell# cellcli e alter griddisk all active8. Verify all grid disks have been successfully put online using the following 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 its now in WriteBack mode:# cellcli -e list cell detail | grep flashCacheMode11. Repeat these same steps 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,asmmodestatus,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 cellsrv3. 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确认Exadata 计算节点间的网络带宽可以采用nc nc-1.84-10.fc6.x86_64.rpm获得检测多个ORACLE_HOME是否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;make f i*mk ipc_rds”dcli -g /opt/oracle.SupportTools/onecommand/dbs_group -l oracle “export ORACLE_HOME=$ORACLE_HOME;cd pwd;make f i*mk ioracle” | egrep rm|mv.*oracle不同配置Exadata的推荐最大并行度配置CPU个数推荐最大ParallelismFull Rack64 coreDOP=256Half Rack32 coreDOP=128Quarter Rack16 coreDOP=64Exadata EHCC支持Exadata的EHCC支持宽表 最大支持1000个字段的表，而不像11.1中的压缩仅支持最多255列的表Exadata 压缩信息通过dbms_compression.get_compression_ratio 可以获得表的压缩信息针对写日志redo特别多的应用建议启用Smart Flash logging特性CREATE FLASHLOG ALLCREATE FLASHLOG ALL SIZE=1GCREATE FLASHLOG CELLDISK=fd1,fd2CREATE FLASHLOG CELLDISK=fd1,fd2 SIZE=1GExadata DB管理Exadata存储空间计算FreeMB(最大可用空间) =GridDisk*12*Num of Cells/RedundancyUsableMB (支持1个CELL故障的最大可用空间) =GridDisk*12*(Num of Cells 1) /Redundancy查看cell软件版本imagehistoryimageinfo了解cell的温度dcli -g cell_group -l root “ipmitool sensor | grep Inlet Amb Temp”cell存储节点的日志存放位置$ADR_BASE/diag/asm/cell/hostname/trace/alert.log $ADR_BASE/diag/asm/cell/hostname/trace/ms-odl.* $ADR_BASE/diag/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;为cell创建一个告警阈值cellclicreate threshold CD_IO_ERRS_MIN warning=1, comparison=, occurrences=1, observation=1;cell可用性监控一般建议使用 EMGC Oracle Exadata Storage Server Management Plug-In 监控如何禁用Smart Scan?设置 Cell_offload_processing=false如何禁用storage index?设置 _kcfis_storageidx_disabled=true如何禁用flash cache? 以后 设置_kcfis_keep_in_cellfc_enabled=false中设置_kcfis_control1=1cell相关的数据库视图有以下这些视图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 IORMCellCLI alter iormplan dbplan = (name = production, level = 1, allocation = 100), (name = test, level = 2, allocation = 80), (name = other, level = 2, allocation = 20)IORMPLAN successfully alteredCellCLI alter iormplan activeIORMPLAN successfully alteredCellCLI list iormplan detailname: cell4_IORMPLANcatPlan:dbPlan: name=production,level=1,allocation=100name=test,level=2,allocation=80name=other,level=2,allocation=20status: active如何禁用布隆过滤Bloom Fliter设置_bloom_pruning_enabled=falseExadata数据备份backup备份速率Exadata下rman备份的速率从1通道到8通道 大约为1003MB/s 到 2081MB/s，视乎配置不同也略微有区别recovery应用日志恢复速率exadata recovery的速率大约为每秒6001000MB/s的归档日志standby database搭建对于50TB的standby database搭建，若使用infiniband + 4rman通道大约耗费5.5小时，若使用GigE则在18个小时左右Exadata恢复cell 救护可以通过 /opt/oracle.SupportTools/make_cellboot_usb脚本创建内部USB cellboot_usb_in_rescure_modeExadata部署onecommand下载可以下载patch (9935478) ONECOMMAND FOR Exadata 11gR2Exadata安装前准备工作1. 下载安装介质包括Grid, Database,Patches等2. 硬件设备到货验收并安装就绪3. 规划DBM用的管理网，生产网，ILOM等用的网段和IP地址4. 配置DNS服务器5. 将IP地址和域名注册到DNS服务器6. 配置NTP服务器7. 网络连线环境检查1. 检查DBM主机的eth0网卡是否可以通过cisco交换机被访问2. 检查hardware and firmware profile是否正确3. 验证InfiniBand Network验证网络连通性1. 登陆第一台数据库服务器使用sh脚本验证网络连通性2. 验证DNS是否正常3. 验证NTP 服务器是否正常安装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. 检查当前Cell storage server的Exadata Image 版本3. 安装最新的Patch具体步骤详见Readme4. 验证当前Exadata Image version#cd /opt/oracle.SupportTools/firstconf#dcli -l root -g quarter imagehistory | grep i Version使用OneCommand工具完成DBM的配置安装1. #cd /opt/oracle.SupportTools/onecommand2. Display the onecommand steps# ./deploy112.sh -i l3. The steps in order areStep 0 = ValidateThisNodeSetupStep 1 = SetupSSHForRootStep 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 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 = SetupCellEmailAlertsStep 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监控exachk健康检查脚本exachk脚本可以以daemon形式后台运行./exachk d start以daemon形式cluster support运行./exachk clusternodes node1,node N d start!Exadata文档信息Exadata的官方文档 /cd/E50790_01/welcome.html另外文档还保存在您cell 的 /opt/oracle/cell/doc/ 目录下。Exadata硬件篇常规默认密码，以下是Exadata中cell/db node IB等的默认密码：组件登陆默认密码Storage Cellsroot nm2userwelcome1Infiniband Switchroot nm2userwelcome1 changemeDB节点rootwelcome1CELL CLIcelladminwelcomeILOMrootwelcome1KVM SwitchAdmin or noneGigE switch初始安装后asmsnmp的账号一般也是welcome1硬件常规巡检：在机房例行检查时，需要从Exadata机箱后方查看Exadata中是否有黄灯报警，如果有，记录位置，即时登录OEM/ILOM/集成的第三方监控工具查明原因，定位部件，即时维护。Exadata一体机健康检查脚本exachk，参考document 1070954.1检测Exadata数据库机器上的硬件和固件版本是否匹配？/opt/oracle.SupportTools/CheckHWnFWProfile返回如下结果说明版本匹配：SUCCESS The hardware and firmware profile matches one of the supported profile检测软件版本与平台是否匹配？/opt/oracle.SupportTools/CheckSWProfile.sh -c为cell启用邮件告警ALTER CELL smtpServer=, smtpFromAddr=, smtpToAddr=, smtpFrom=Exadata cell, smtpPort=, smtpUseSSL=TRUE, notificationPolicy=critical,warning,clear, notificationMethod=mail;alter cell validate mail;监控磁盘故障当通过机房例行检查发现硬件黄灯警告或通过监控工具(命令行/ILOM/第三方工具)发现故障并确定位置后,可进行更换操作。更换Storage Cell硬盘命令行登录Cell,判断故障硬盘,例如:CellCLI LIST PHYSICALDISK WHERE diskType=HardDisk AND status=critical DETAIL观察Database Server 磁盘状态rootdm01db01 # cd /opt/MegaRAID/MegaCli/rootdm01db01 MegaCli# ./MegaCli64 -Pdlist -aAll | grep “Slot|Firmware”观察Database Server RAID状态rootdm01db01 MegaCli# ./MegaCli64 -LdInfo -lAll aAllStorage Cell加电启动远程登陆Storage Cell控制器ILOM,执行Power On,其它为系统的自动启动过程,知道Storage Cell就绪CellCLI LIST GRIDDISK若没有Active,需:CellCLI ALTER GRIDDISK ALL ACTIVE等grid disk Active后,ASM会自动同步,使grid disk Online,查看状态: CellCLI LIST GRIDDISK ATTRIBUTES name, asmmodestatus确认ASM数据自动重新分布是否已经开始或完成。 Grid用户登录+ASM实例执行:select * from v$asm_operation; 通过EM、SYSLOG、Cellcli、ILOM查看是否有告警解除信息检测memory ECC错误ipmitool sel list | grep ECC | cut -f1 -d : | sort -u若发现Exadata上存在磁盘损毁则：使用/opt/oracle.SupportTools/sundiag.sh 收集详细信息 并发给oracle support检测 cell server Cache Policycell08# MegaCli64 -LDInfo -Lall -aALL | grep Current Cache PolicyCurrent Cache Policy: WriteThrough, ReadAheadNone, Direct, No Write Cache if Bad BBUcell09# MegaCli64 -LDInfo -Lall -aALL | grep Current Cache PolicyCurrent Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBUDefault Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBUCurrent Cache Policy: 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 #( Will try to change teh policy from xx to WB) So policy Change to WB will not come into effect immediately Set Write Policy to WriteBack on Adapter 0, VD 0 (target id: 0) success Battery capacity is below the threshold value 检测cell BBU备用电池状态：cell08# /opt/MegaRAID/MegaCli/MegaCli64 -AdpBbuCmd -GetBbuStatus -a0BBU status for Adapter: 0BatteryType: iBBUVoltage: 4061 mVCurrent: 0 mATemperature: 36 CBBU Firmware Status:Charging Status : NoneVoltage : OKTemperature : OKLearn Cycle Requested : NoLearn Cycle Active : NoLearn Cycle Status : OKLearn Cycle Timeout : NoI2c Errors Detected : NoBattery Pack Missing : NoBattery Replacement required : NoRemaining Capacity Low : YesPeriodic Learn Required : NoBattery state:GasGuageStatus:Fully Discharged : NoFully Charged : YesDischarging : YesInitialized : YesRemaining Time Alarm : NoRemaining Capacity Alarm: NoDischarge Terminated : NoOver Temperature : NoCharging Terminated : NoOver Charged : NoRelative State of Charge: 99 %Charger System State: 49168Charger System Ctrl: 0Charging current: 0 mAAbsolute state of charge: 21 %Max Error: 2 %Exit Code: 0x00 批量检测BBU 信息: dcli -g /cell_group -l root -t uname -srm ; head -1 /etc/*release ; uptime | cut -d, -f1 ; imagehistory ;ipmitool sunoem cli show /SP system_description system_identifier | grep = ;ipmitool sunoem cli show /SP/policy FLASH_ACCELERATOR_CARD_INSTALLED/opt/MegaRAID/MegaCli/MegaCli64 -AdpBbuCmd -GetBbuStatus -a0 | egrep -iBBU|Battery|Charge:|Fully|Low|Learn ; | tee /tmp/ExaInfo.logExadata 停机：1. 确认无业务访问，以root 用户登录第1 个数据库服务器节点2. 停止数据库（详见RAC/ASM 维护之RAC 启停章节）3. 停止Cluster# GRID_HOME/grid/bin/crsctl stop cluster -all4. 停除本机以外的数据库节点# dcli -l root -c dm01db02,dm01db03,dm01db04 shutdown -h -y now5. 停存储服务器cell_group 可自编辑，执行时并可由root 用户读取该文件()另需参考Storage Cell 存储维护Storage Cell 停机章节信息后方可执行下述命令# dcli -l root -g cell_group shutdown -h -y now6. 停本机# shutdown -h -y now7. 此时可通过ILOM 远程关机8. 整机下电(关PDU)Exadata 启动1、为机柜加电（SWITCH 自然加电）打开PDU开关进行加电，服务器指示灯都变绿，慢闪若需手工开机数据库服务器、存储服务器需要按住其开关5秒。也可在ILOM中点击Cell的Poweron开关进行开机，服务器指示灯为绿色长亮，再点击DB Server的Poweron开关进行开机，服务器指示灯为绿色长亮。2、检查是否有黄灯报警。3、启动数据库、应用等。Infiniband篇启停IBSwitch1. InfiniBand Switch电源的开启或关闭InfiniBand Switch提供冗余电源,分别插在Exadata的2个冗余PDU电源上,并随PDU机柜电源开启或关闭,若关闭InfiniBand Switch需断掉InfiniBand Switch的的冗余电源。 2. 查看OEM等是否有相关报警。ILOM无法报警从cell1的cellcli中查看list alerthistory可以看到3. 从db01查看网络拓扑状态rootdm01db01 # cd /opt/oracle.SupportTools/ibdiagtoolsrootdm01db01 ibdiagtools# ./verify-topology -t halfrack4. 插入InfiniBand电源线,查看InfiniBand Switch正常启