Oracle EXADATA健康检查指南

2012年8月1 前言............................... 错误!未指定书签。

一Exadata概述....................... 错误!未指定书签。

1Exadata简介....................... 错误!未指定书签。

2Exadata的配置及性能参数 ........... 错误!未指定书签。

二Exadata特性....................... 错误!未指定书签。

1SmartScan（智能扫描）.............. 错误!未指定书签。

2StorageIndex（存储索引）........... 错误!未指定书签。

3FlashCache（智能闪存）............. 错误!未指定书签。

4Compression（压缩）&EHCC（ExadataHybridColumnarCompression）错误!未指定书签。

5IORM（IO资源管理） ................ 错误!未指定书签。

三Exadata监控....................... 错误!未指定书签。

1Exadata特性监控常用指标 ........... 错误!未指定书签。

2如何查看指标....................... 错误!未指定书签。

四如何应用Exadata................... 错误!未指定书签。

1Exadata参数调整................... 错误!未指定书签。

2在Exadata上开发注意事项........... 错误!未指定书签。

3应用总结........................... 错误!未指定书签。

4Exadata总体总结................... 错误!未指定书签。

1前言1.1本文背景前期东软-甲骨文公司组织了一次针对社保系统的Exadata联合应用测试，本文内容是本次Exadata测试的经验总结，其中包含了与Oracle技术人员交流经验应用、Oracle相关技术文档应用及个人测试经验总结。

Rem chk_health.sqlRem 1.0Rem by Pond KaRemRem usage: sqlplus system/password_of_system@connect_string @chk_health.sql Rem only for single instance databaseRem not suitable for RACRem only tested under 9.2.0RemRemRem This script do health check .Rem Must run under system or user has dba privilege.Remset pages 0set lines 1000set trimspool onset head offset feedback offset echo offset verify offRem get report name based on database name and report dateRemcol logname noprint new_value log_nameselect lower(name)||to_char(sysdate,'yyyymmddhh24mi')||'.txt' lognamefrom v$database;spool &log_nameRemRem report headerRempromptselect 'Report produced at '||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;promptprompt Basic information:prompt --------------------------------------------------------------------------------RemRem Check database informationset head onset pages 45col dbid heading "Database|ID" format a11col name heading "Database|Name"col open_mode heading "Open |Mode"col force_logging heading "Force|Logging" format a7selectto_char(dbid,9999999999) dbid, name, open_mode, force_loggingfrom v$database;RemRem Check instance informationRemcol instance_name heading "Instance|Name" format a10col host_name heading "Host|Name" format a10col status heading "Instance|Status" format a8col archiver heading "Archiver|Status" format a8col up_time heading "Running Time" format a30selecthost_name, instance_name, status, archiver, trunc(sysdate - startup_time) || ' Days '|| trunc(mod(sysdate-startup_time, 1) *24) || ' Hours '|| trunc(mod((sysdate-startup_time)*24,1)*60) || ' Minutes 'up_timefrom v$instance;promptpromptprompt Check hit ratioprompt These value expected higher than 90%prompt --------------------------------------------------------------------------------RemRem Check buffer cache hit ratiocol pr heading "Physical|Reads" format 999,999,999col prd heading "Phy_Reads|Direct" format 999,999,999col prl heading "Phy_Reads|Direct_LOB" format 999,999,999col bg heading "Block|Gets" format 999,999,999,999col cg heading "Consistent|Gets" format 999,999,999,999col ht heading "Buffer|Hit Ratio"selectto_char((1-((pr - prd - prl) / (bg + cg - prd - prl))) * 100, '999.9')||'%' ht, bg, cg, pr, prd, prlfrom(select value pr from v$sysstat where name = 'physical reads') pr, (select value prd from v$sysstat where name = 'physical reads direct') prd, (select value prl from v$sysstat where name = 'physical reads direct (lob)') prl , (select value bg from v$sysstat where name = 'db block gets') bg, (select value cg from v$sysstat where name = 'consistent gets') cg;RemRem check library hit ratioRemcol ht heading "Libray|Hit Ratio" format a10selectto_char(sum(pinhits) / sum(pins) * 100, 999.9)||'%' htfrom v$librarycache;promptprompt Check session informationsprompt --------------------------------------------------------------------------------RemRem Check session high water markRemcol sessions_current heading "Sessions|Current" format 999,999,999col sessions_highwater heading "Sessions|High Water" format 999,999,999selectsessions_current, sessions_highwaterfrom v$license;RemRem Check session wait eventsRemcol username heading "User Name" format a15col program heading "Program" format a35col event heading "Wait Event" format a25promptprompt Session wait events, excluding waiting for user's message prompt _________________selectername, s.program, sw.eventfromv$session_wait sw, v$session swheresw.sid = s.sidand ername is not nulland event not in ('SQL*Net message from client');RemRem Check session statusRemcol status heading "Session|Status"col nu heading "Number|of Sessions"compute sum of nu on reportbreak on reportpromptprompt Session statusprompt -----------------selectstatus, count(*) nufrom v$sessionwhere username is not nullgroup by status;clear break;RemRem detail information for sessions which idle for more than 4 hours Remcol lc heading "Idle Time|(Hours)" format a8col username format a10 heading "Database|Username"col machine format a17 heading "Machine"col osuser format a10 heading "OS|Username"col prg format a35 heading "Program"promptprompt Idle sessionsprompt -----------------selectusername, machine, osuser, program prg, to_char(trunc(last_call_et/3600, 1), 99999.9) lcfrom v$sessionwhere last_call_et > 14400and username is not nullorder by last_call_et desc;promptprompt Redo log files informationprompt --------------------------------------------------------------------------------RemRem Online redo logfile informationRemcol grp format 99 heading "Log Group|Number"col bytes format 999,999.99 heading "Bytes|(M)"col status heading "Status"col member heading "Log File|Members" format a45break on grp on bytes on status skip 1promptprompt Online redo log fileprompt -----------------selectl.group# grp, l.bytes/1024/1024 bytes, l.status, lf.memberfrom v$log l, v$logfile lfwhere l.group# = lf.group#order by 1;clear break;RemRem Online redo logfile switch frequencyRemcol dt heading "Begin Time (1 hour)" format a25col cnt heading "Switch times" format 999promptprompt Switch frequencyprompt -----------------selectto_char(trunc(first_time, 'hh'), 'yyyy-mm-dd hh24:mi') dt, count(*) cntfromv$loghistwhere first_time > sysdate - 30group by trunc(first_time,'hh')order by 1;RemRem Space usage checkRempromptprompt Tablespace usageprompt --------------------------------------------------------------------------------col tbsn heading "Tablespace|Name" format a20col bytes heading "Current|Size(M)" format 999,999.99col max_b heading "Maximum|Size(M)" format 999,999.99col fre_b heading "Free Space|Size(M)" format 999,999.99col usg heading "Free Space|persentage" format a10col em heading "Extent|Management" format a10col ssm heading "Segment|Management" format a10selecttb.tbsn, bytes, fre_b, lpad(to_char(nvl(fre_b,0)/bytes*100, 999.99)||'%',10) usg, max_bfrom(select tablespace_name tbsn, sum(bytes)/1024/1024 bytes, sum( decode(AUTOEXTENSIBLE,'YES', greatest(bytes, maxbytes),bytes))/1024/1024 max_bfrom dba_data_filesgroup by tablespace_name) tb,(select tablespace_name tbsn, sum(bytes)/1024/1024 fre_bfrom dba_free_spacegroup by tablespace_name) frewhere tb.tbsn = fre.tbsn (+)order by 4;promptprompt Table usageprompt -----------------set serveroutput onexec dbms_output.enable(100000000);declaretype seg is record (seg_owner dba_segments.owner%type,seg_namedba_segments.segment_name%type,par_name d ba_segments.PARTITION_NAME%type,seg_type dba_segments.segment_type%type,tbs_name dba_segments.TABLESPACE_NAME%type);type usg is record (t_bck number,t_byt number,u_bck number,u_byt number,luefi number,luebi number,lub number);v_seg seg;v_usg usg;v_tbsname dba_tablespaces.tablespace_name%type;v_tbsseg dba_tablespaces.SEGMENT_SPACE_MANAGEMENT%type; cursor c_tbs isselecttablespace_name, SEGMENT_SPACE_MANAGEMENTfromdba_tablespaceswhereCONTENTS = 'PERMANENT'order by 1;cursor c_seg isselectowner, segment_name, PARTITION_NAME, segment_typefromdba_segmentswhereowner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'WMSYS')and tablespace_name = v_tbsnameorder by 1, 2, 3;beginopen c_tbs;fetch c_tbs into v_tbsname, v_tbsseg;while c_tbs%found loopdbms_output.put_line('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');dbms_output.put_line('Tablespace: '||v_tbsname);dbms_output.put_line (rpad('Owner',15)||rpad('Segment Name', 30)||rpad('Par', 8)||rpad('Seg Type', 10)||rpad('Bytes(K)',10)||'Used(K)');dbms_output.put_line (rpad('-',14, '-')||' '||rpad('-',29, '-')||' '||rpad('-',7, '-')||' '||rpad('-', 9, '-')||' '||rpad('-',9, '-')||' '||' ------------');open c_seg;fetch c_seg into v_seg.seg_owner, v_seg.seg_name, v_seg.par_name, v_seg.seg_type;while c_seg%found loopif v_seg.seg_type in ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION','INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'CLUSTER','LOB') thendbms_space.unused_space (v_seg.seg_owner, v_seg.seg_name, v_seg.seg_type, v_usg.t_bck, v_usg.t_byt, v_usg.u_bck, v_usg.u_byt, v_usg.luefi, v_usg.luebi, v_usg.lub, v_seg.par_name);dbms_output.put_line(rpad(v_seg.seg_owner,15)||rpad(v_seg.seg_name, 30)||rpad(substr(nvl(v_seg.par_name,'NULL'),1,7), 8)||rpad(substr(v_seg.seg_type, 1, 9), 10)||to_char(v_usg.t_byt/1024, '9,999,999')||to_char((v_usg.t_byt-v_usg.u_byt)/1024, '9,999,999'));end if;fetch c_seg into v_seg.seg_owner, v_seg.seg_name, v_seg.par_name, v_seg.seg_type;end loop;close c_seg;fetch c_tbs into v_tbsname, v_tbsseg;end loop;close c_tbs;end;/spool off;exit;。

这里简单介绍一下基本的操作和步骤:1. 把指令文件exachk.zip上传到一个数据库服务器节点上推荐路径/opt/oracle.SupportTools/exachk2. Unzip 解压exachk.zip3. 推荐使用root用户去执行exachk (可以考虑使用VNC避免网络中断)<从12.1.0.2.2版本起, Oracle推荐使用root去执行exachk>执行exachk的时候，会有一些提示信息需要输入Yes or No，确认您是否从系统收集数据，并给你一些选项，同时需要输入密码(exachk是不会保存密码文件到操作系统)，然后脚本开始工作，收集原始数据并在最后进行分析。

原数据和分析结果会被存放在以日期为结构的目录中。

详情请参考文档文件里的Exachk的使用手册。

Exachk有个watchdog进程，负责监控exachk的执行状态，它会设定一个默认的“超时”值，以防止exachkhung住。

在一个繁忙的系统中，如果在默认的时间内没有响应的话，检查将会被终止。

通过设置一些环境变量，可以延长默认的“超时”值。

（RAT_TIMEOUT 和RAT_ROOT_TIMEOUT）接下来我们看下执行exachk的过程：$ ./exachkCRS stack isrunning and CRS_HOME is not set. Do you want to set CRS_HOME to/u01/app/11.2.0/grid?[y/n][y]Exachk可以自己通过查询系统文件，来判断gridhome，如果路径正确，你可以输入“Y”来设定CRS_HOME，如果不正确，需要输入“N”然后手动设置正确的路径。

Checking ssh userequivalency settings on all nodes in clusterNode randomdb02 isconfigured for ssh user equivalency for oracle userExachk会验证ssh等价性是否可用，如果没有配置ssh等价性，它会需要你输入密码，并临时配置等价性。

0.作业概述
运行EXADATA Database Machine HealthCheck，检查软件、硬件、固件版本、配置等，生成健康检查报告。

1.作业对象
EXADATA Database Machine上的软件、硬件、固件版本、配置等。

2.作业前确认事项
3.作业具体操作步骤
3.1.下载最新软件
登录ORACLE SUPPORT网站： ，找到并打开ID为1070954.1的文章，下载最新的HealthCheck软件。

3.2.安装并做健康检查
1）将exachk.zip上传到一台数据库服务器上，如：hdexdb01；
2）以“oracle”用户登录数据库服务器，并执行：
•$ unzip exachk.zip
•$ chmod +x exachk
3）查看readme.txt 和UserGuide：
4）运行Exachk工具（必须以“oracle”用户执行）
•$./exachk
5）确认clusterware home (CRS_HOME)
6）检查数据库服务器上的SSH设置是否正确
7）选择要检查的数据库
8）软件环境检查结果显示
9）输入Storage Server 上root的密码
10）输入Database Server 上root的密码
11）输入Infiniband switch上root的密码
12）收集database server, storage server 和infiniband switche上的配置信息
13）分析系统状况
14）检查结果输出到文件
15）查看结果并处理错误
---手册完---。

Oracle Health-Check Report Oracle数据库健康巡检报告XX公司xx数据库系统健康巡检报告作者:文档创建日期: 2010-12-1上次修改日期:服务工程师:服务类型：现场客户联系人:目录健康检查记录 (3)数据库调整记录 (3)一、系统概况 (4)二、数据库趋势分析 (4)1、数据缓冲区和库缓冲区命中率趋势 (4)2、数据量变化趋势 (5)三、健康检查项目列表及结果 (5)1、操作系统 (5)1.1磁盘空间 (5)1.2系统性能信息 (8)2、数据库系统 (9)2.1安全性 (9)2.2稳定性 (9)2.3数据库性能 (9)3、健康检查 (11)3.1数据库版本信息 (11)3.2目前数据库参数 (11)3.3数据库资源限制 (12)3.4控制文件 (12)3.5日志文件 (13)3.6数据文件 (13)3.7临时文件 (13)3.8表空间使用率监控 (13)3.9无效索引 (14)四、巡检总结与调整建议 (14)1、巡检总结： (14)2、调整建议： (14)3、对管理人员的提醒： (14)前次巡检记录巡检日期市民卡公司工程师联创工程师本次巡检记录巡检日期市民卡公司工程师联创工程师数据库调整记录自上次巡检以来对本数据库调整记录如下：1)调整一2)调整二3)调整三一、系统概况项目值业务名称主机名硬件平台CPU物理内存操作系统数据库软件版本数据库名归档模式Blocksize数据库核心字符集控制文件镜像份数控制文件镜像是否在不同目录联机日志组数联机日志每组member数联机日志每组成员是否在不同磁盘每组日志大小时日志切换高峰注:天日志生成量高峰、时日志切换高峰:这里的高峰指的是redo生成高峰，非业务高峰。

全库export大小的计算方法是:统计全库中表的大小,这种方式计算出的表的大小包含了空的行记录,而export实际导出时不会导出空数据行,所以这里的export大小会大于实际的导出dmp文件的大小,具体误差多少取决与数据库中存在多少的空数据行(delete操作产生的空数据行).全库rman备份大小(10.2.0.1)的计算方法是:统计全库中所有对象的大小.而rman备份集是备份所有曾经被对象暂用过的空间,所以此种统计方法统计的数据和rman备份实际的大小的差异在很大程度上取决于被放入回收站对象的多少.二、数据库趋势分析1、数据缓冲区和库缓冲区命中率趋势[数据来源典型业务高峰时段statspack or awr]Buffer Nowait %: 100 Redo NoWait %: 100Buffer Hit %: 82 In-memory Sort %: 100Library Hit %: 99 Soft Parse %: 99Execute to Parse %: 65 Latch Hit %: 100123.4 % Non-Parse CPU: 99.4Parse CPU to ParseElapsd %:建议:数据库性能2、数据量变化趋势[]dmp全备份估计大小(G) RMAN全备份估计大小(G) 数据文件容量(G)81 137 348 建议:三、健康检查项目列表及结果1、操作系统[操作系统命令df-k 和prstat,top,topas,glance,sar输出]1.1磁盘空间[数据来源df -k]对操作系统的磁盘空间进行检查，是否有足够空间。

oracle 11g R2 RAC健康检查1.检查集群状态[grid@rac1 ~]$ iduid=501(grid) gid=501(oinstall) groups=501(oinstall),504(asmadmin),506(asmdba),507(asmoper) [grid@rac1 ~]$ crsctl check clusterCRS-4537: Cluster Ready Services is onlineCRS-4529: Cluster Synchronization Services is onlineCRS-4533: Event Manager is online[grid@rac1 ~]$2.检查rac下实例的状态[grid@rac1 ~]$ srvctl config database1.检查集群状态[grid@rac1 ~]$ iduid=501(grid) gid=501(oinstall) groups=501(oinstall),504(asmadmin),506(asmdba),507(asmoper) [grid@rac1 ~]$ crsctl check clusterCRS-4537: Cluster Ready Services is onlineCRS-4529: Cluster Synchronization Services is onlineCRS-4533: Event Manager is online[grid@rac1 ~]$2.检查rac下实例的状态[grid@rac1 ~]$ srvctl config databaseorcl[grid@rac1 ~]$ srvctl status database -d orclInstance orcl1 is running on node rac1Instance orcl2 is running on node rac2[grid@rac1 ~]$3.检查rac下某个实例状态[grid@rac1 ~]$ srvctl status instance -d orcl -i orcl1Instance orcl1 is running on node rac1[grid@rac1 ~]$ srvctl status instance -d orcl -i orcl2Instance orcl2 is running on node rac2[grid@rac1 ~]$4.检查rac各个节点应用程序状态（vip,network,gsd,ons,eons）[grid@rac1 ~]$ srvctl status nodeappsVIP rac1-vip is enabledVIP rac1-vip is running on node: rac1VIP rac2-vip is enabledVIP rac2-vip is running on node: rac2Network is enabledNetwork is running on node: rac1Network is running on node: rac2GSD is enabledGSD is running on node: rac1GSD is running on node: rac2ONS is enabledONS daemon is running on node: rac1ONS daemon is running on node: rac2eONS is enabledeONS daemon is running on node: rac1eONS daemon is running on node: rac2[grid@rac1 ~]$5.检查rac下的数据库配置[grid@rac1 ~]$ srvctl config database -d orcl -a Database unique name: orclDatabase name: orclOracle home: /u01/app/oracle/product/11.2.0/db_1 Oracle user: oracleSpfile: +DATA/orcl/spfileorcl.oraDomain:Start options: openStop options: immediateDatabase role: PRIMARYManagement policy: AUTOMATICServer pools: orclDatabase instances: orcl1,orcl2Disk Groups: DATA,BACKServices:Database is enabledDatabase is administrator managed[grid@rac1 ~]$6.检查rac下的ASM状态以及ASM配置[grid@rac1 ~]$ srvctl status asmASM is running on rac1,rac2[grid@rac1 ~]$ srvctl status asm -aASM is running on rac1,rac2ASM is enabled.[grid@rac1 ~]$ srvctl config asm -aASM home: /u01/app/grid/11.2.0/gridASM listener: LISTENERASM is enabled.[grid@rac1 ~]$7.检查rac下的TNS监听器状态以及配置[grid@rac1 ~]$ srvctl status listenerListener LISTENER is enabledListener LISTENER is running on node(s): rac1,rac2 [grid@rac1 ~]$ srvctl config listener -aName: LISTENERNetwork: 1, Owner: gridHome: <CRS home>/u01/app/grid/11.2.0/grid on node(s) rac2,rac1End points: TCP:1521[grid@rac1 ~]$8.检查rac下的SCAN状态以及配置[grid@rac1 ~]$ srvctl status scanSCAN VIP scan1 is enabledSCAN VIP scan1 is running on node rac1[grid@rac1 ~]$ srvctl config scanSCAN name: rac-cluster-scan, Network: 1/192.9.100.0/255.255.255.0/eth0 SCAN VIP name: scan1, IP: /rac-cluster-scan/192.9.100.36[grid@rac1 ~]$#SCAN192.9.100.36 rac-cluster-scan9.检查rac下的VIP各个节点的状态以及配置[grid@rac1 ~]$ srvctl status vip -n rac1VIP rac1-vip is enabledVIP rac1-vip is running on node: rac1[grid@rac1 ~]$ srvctl status vip -n rac2VIP rac2-vip is enabledVIP rac2-vip is running on node: rac2[grid@rac1 ~]$ srvctl config vip -n rac1VIP exists.:rac1VIP exists.: /rac1-vip/192.9.100.33/255.255.255.0/eth0[grid@rac1 ~]$ srvctl config vip -n rac2VIP exists.:rac2VIP exists.: /rac2-vip/192.9.100.35/255.255.255.0/eth0[grid@rac1 ~]$#VIP192.9.100.33 rac1-vip192.9.100.35 rac2-vip10.检查rac下各个节点应用程序配置情况 (VIP、GSD、ONS、监听器）[grid@rac1 ~]$ srvctl config nodeapps -a -g -s -l-l option has been deprecated and will be ignored.VIP exists.:rac1VIP exists.: /rac1-vip/192.9.100.33/255.255.255.0/eth0VIP exists.:rac2VIP exists.: /rac2-vip/192.9.100.35/255.255.255.0/eth0GSD exists.ONS daemon exists. Local port 6100, remote port 6200Name: LISTENERNetwork: 1, Owner: gridHome: <CRS home>/u01/app/grid/11.2.0/grid on node(s) rac2,rac1End points: TCP:152111.检查rac下的各个节点间的时钟同步情况[grid@rac1 ~]$ cluvfy comp clocksync -verboseVerifying Clock Synchronization across the cluster nodesChecking if Clusterware is installed on all nodes...Check of Clusterware install passedChecking if CTSS Resource is running on all nodes...Check: CTSS Resource running on all nodesNode N ame Status------------------------------------ ------------------------rac1 passedResult: CTSS resource check passedQuerying CTSS for time offset on all nodes...Result: Query of CTSS for time offset passedCheck CTSS state started...Check: CTSS stateNode N ame State------------------------------------ ------------------------rac1 ObserverCTSS is in Observer state. Switching over to clock synchronization checks using NTP Starting Clock synchronization checks using Network Time Protocol(NTP)...NTP Configuration file check started...The NTP configuration file "/etc/ntp.conf" is available on all nodesNTP Configuration file check passedChecking daemon liveness...Check: Liveness for "ntpd"Node N ame Running?------------------------------------ ------------------------rac1 yesResult: Liveness check passed for "ntpd"Checking NTP daemon command line for slewing option "-x"Check: NTP daemon command lineNode N ame Slewing O ption S et?------------------------------------ ------------------------rac1 yesResult:NTP daemon slewing option check passedChecking NTP daemon's boot time configuration, in file "/etc/sysconfig/ntpd", for slewing option "-x"Check: NTP daemon's boot time configurationNode N ame Slewing O ption S et?------------------------------------ ------------------------rac1 yesResult:NTP daemon's boot time configuration check for slewing option passedNTP common Time Server Check started...PRVF-5410 : Check of common NTP Time Server failedPRVF-5416 : Query of NTP daemon failed on all nodesResult: Clock synchronization check using Network Time Protocol(NTP) passedOracle Cluster Time Synchronization Services check passedVerification of Clock Synchronization across the cluster nodes was successful.[grid@rac1 ~]$12.检查rac下的所有正在运行的实例情况col status for a15;col database_status for a15;col instance_name for a15;col host_name for a15;col active_state for a15;SQL> set linesize 200;SQL> select instance_number, instance_name, parallel , status , database_status, active_state, host_name from gv$instance order by instance_number;INSTANCE_NUMBER INSTANCE_NAME PARALLEL STATUS DATABASE_STATUS ACTIVE_STATE HOST_NAME--------------- --------------- --------- --------------- --------------- --------------- ---------------1o rcl1YES OPEN ACTIVE NORMAL rac12o rcl2YES OPEN ACTIVE NORMAL rac2SQL> select * from v$active_instances;INST_NUMBER INST_NAME----------- --------------------------------------------------------------------1r ac1:orcl12r ac2:orcl213.检查数据文件及它们所在的 ASM磁盘组select name from v$datafileunionselect member from v$logfileunionselect name from v$controlfileunionselect name from v$tempfile;SQL> select name from v$datafile2 union3 select member from v$logfile4 union5 select name from v$controlfile6 union7 select name from v$tempfile;NAME--------------------------------------------------------------------------------+BACK/orcl/controlfile/current.299.768402009+BACK/orcl/onlinelog/group_1.298.768402013+BACK/orcl/onlinelog/group_2.297.768402015+BACK/orcl/onlinelog/group_3.296.768402261+BACK/orcl/onlinelog/group_4.295.768402263+DATA/orcl/controlfile/current.280.768402009+DATA/orcl/datafile/example.385.768402021........+DATA/orcl/tempfile/sms_db_temp.400.768403227+DATA/orcl/tempfile/temp.386.76840201914.检查ASM磁盘情况QL> select path from v$asm_disk;PATH--------------------------------------------------------------------------------/dev/oracleasm/disks/BACK_VOL1/dev/oracleasm/disks/DATA_VOL1/dev/oracleasm/disks/OCR_VOL3/dev/oracleasm/disks/OCR_VOL2/dev/oracleasm/disks/OCR_VOL1SQL> col path for a50;SQL> select path,disk_number,name from v$asm_disk;PATH DISK_NUMBER N AME-------------------------------------------------- ----------- -----------------/dev/oracleasm/disks/BACK_VOL10B ACK_0000/dev/oracleasm/disks/DATA_VOL10D ATA_0000/dev/oracleasm/disks/OCR_VOL32O CR_0002/dev/oracleasm/disks/OCR_VOL21O CR_0001/dev/oracleasm/disks/OCR_VOL10O CR_0000SQL> select group_number,name,offline_disks from v$asm_diskgroup;GROUP_NUMBER N AME OFFLINE_DISKS------------ ------------------------------ -------------1O CR02D ATA03B ACK0SQL> select group_number,name,total_mb,free_mb from v$asm_diskgroup;GROUP_NUMBER N AME TOTAL_MB FREE_MB------------ ------------------------------ ---------- ----------1O CR2859 19332D ATA568739 5568103B ACK571600 508953SQL>SQL> SELECT AS diskgroup, , t.stripe, t.redundancy, t.primary_region, t.mirror_region2 FROM V$ASM_DISKGROUP dg, V$ASM_TEMPLATE t3 WHERE dg.group_number = t.group_number ORDER BY ;rows will be truncatedDISKGROUP NAME STRIPE R EDUND PRIM------------------------------ ------------------------------ ------ ------ ----OCR ARCHIVELOG COARSE M IRROR COLDBACK ARCHIVELOG COARSE U NPROT COLDDATA ARCHIVELOG COARSE U NPROT COLDBACK ASMPARAMETERBAKFILE COARSE U NPROT COLDOCR ASMPARAMETERBAKFILE COARSE M IRROR COLDDATA ASMPARAMETERBAKFILE COARSE U NPROT COLDOCR ASMPARAMETERFILE COARSE M IRROR COLDBACK ASMPARAMETERFILE COARSE U NPROT COLDDATA ASMPARAMETERFILE COARSE U NPROT COLDOCR ASM_STALE COARSE H IGH COLDBACK ASM_STALE COARSE U NPROT COLDDISKGROUP NAME STRIPE R EDUND PRIM------------------------------ ------------------------------ ------ ------ ----DATA ASM_STALE COARSE U NPROT COLDOCR AUTOBACKUP COARSE M IRROR COLDDATA AUTOBACKUP COARSE U NPROT COLDBACK AUTOBACKUP COARSE U NPROT COLDBACK BACKUPSET COARSE U NPROT COLDOCR BACKUPSET COARSE M IRROR COLDDATA BACKUPSET COARSE U NPROT COLDOCR CHANGETRACKING COARSE M IRROR COLDBACK CHANGETRACKING COARSE U NPROT COLDDATA CHANGETRACKING COARSE U NPROT COLDBACK CONTROLFILE FINE UNPROT COLDDISKGROUP NAME STRIPE R EDUND PRIM------------------------------ ------------------------------ ------ ------ ----OCR CONTROLFILE FINE HIGH COLDDATA CONTROLFILE FINE UNPROT COLDBACK DATAFILE COARSE U NPROT COLDDATA DATAFILE COARSE U NPROT COLDOCR DATAFILE COARSE M IRROR COLDBACK DATAGUARDCONFIG COARSE U NPROT COLDOCR DATAGUARDCONFIG COARSE M IRROR COLDDATA DATAGUARDCONFIG COARSE U NPROT COLDOCR DUMPSET COARSE M IRROR COLDBACK DUMPSET COARSE U NPROT COLDDATA DUMPSET COARSE U NPROT COLDDISKGROUP NAME STRIPE R EDUND PRIM------------------------------ ------------------------------ ------ ------ ----OCR FLASHBACK COARSE M IRROR COLDDATA FLASHBACK COARSE U NPROT COLDBACK FLASHBACK COARSE U NPROT COLDOCR FLASHFILE COARSE M IRROR COLDDATA FLASHFILE COARSE U NPROT COLDBACK FLASHFILE COARSE U NPROT COLDOCR OCRBACKUP COARSE M IRROR COLDBACK OCRBACKUP COARSE U NPROT COLDDATA OCRBACKUP COARSE U NPROT COLDBACK OCRFILE COARSE U NPROT COLDOCR OCRFILE COARSE M IRROR COLDDISKGROUP NAME STRIPE R EDUND PRIM------------------------------ ------------------------------ ------ ------ ----DATA OCRFILE COARSE U NPROT COLDOCR ONLINELOG COARSE M IRROR COLDBACK ONLINELOG COARSE U NPROT COLDDATA ONLINELOG COARSE U NPROT COLDBACK PARAMETERFILE COARSE U NPROT COLDOCR PARAMETERFILE COARSE M IRROR COLDDATA PARAMETERFILE COARSE U NPROT COLDOCR TEMPFILE COARSE M IRROR COLDDATA TEMPFILE COARSE U NPROT COLDBACK TEMPFILE COARSE U NPROT COLDOCR XTRANSPORT COARSE M IRROR COLDDISKGROUP NAME STRIPE R EDUND PRIM------------------------------ ------------------------------ ------ ------ ----DATA XTRANSPORT COARSE U NPROT COLDBACK XTRANSPORT COARSE U NPROT COLD57 rows selected.SQL> set linesize 300;SQL> select name, path, mode_status, state, disk_number from v$asm_disk;NAME PATH MODE_ST S TATE DISK_NUMBER------------------------------ -------------------------------------------------- ------- -------- -----------BACK_0000 /dev/oracleasm/disks/BACK_VOL1 ONLINE NORMAL 0DATA_0000 /dev/oracleasm/disks/DATA_VOL1 ONLINE NORMAL 0OCR_0002 /dev/oracleasm/disks/OCR_VOL3 ONLINE NORMAL 2OCR_0001 /dev/oracleasm/disks/OCR_VOL2 ONLINE NORMAL 1OCR_0000 /dev/oracleasm/disks/OCR_VOL1 ONLINE NORMAL 0SQL> select name, state from v$asm_diskgroup;NAME STATE------------------------------ -----------OCR MOUNTEDDATA MOUNTEDBACK MOUNTED15.检查rac状态[grid@rac1 ~]$ crs_stat -t -vName Type R/RA F/FT Target State Host----------------------------------------------------------------------ora.BACK.dg ora....up.type0/5 0/ ONLINE ONLINE rac1ora.DATA.dg ora....up.type0/5 0/ ONLINE ONLINE rac1ora....ER.lsnr o ra....er.type0/5 0/ONLINE ONLINE rac1ora....N1.lsnr ora....er.type 0/5 0/0 ONLINE ONLINE rac1ora.OCR.dg ora....up.type0/5 0/ ONLINE ONLINE rac1ora.asm ora.asm.type 0/5 0/ ONLINE ONLINE rac1ora.eons ora.eons.type 0/3 0/ ONLINE ONLINE rac1ora.gsd ora.gsd.type 0/5 0/ ONLINE ONLINE rac1work o ra....rk.type0/5 0/ONLINE ONLINE rac1ora.oc4j ora.oc4j.type 0/5 0/0 ONLINE ONLINE rac2ora.ons ora.ons.type 0/3 0/ ONLINE ONLINE rac1 ora.orcl.db ora....se.type0/2 0/1 ONLINE ONLINE rac1 ora....SM1.asm a pplication 0/5 0/0 ONLINE ONLINE rac1 ora....C1.lsnr a pplication 0/5 0/0 ONLINE ONLINE rac1 ora.rac1.gsd application 0/5 0/0 ONLINE ONLINE rac1 ora.rac1.ons application 0/3 0/0 ONLINE ONLINE rac1 ora.rac1.vip ora....t1.type0/0 0/0 ONLINE ONLINE rac1 ora....SM2.asm a pplication 0/5 0/0 ONLINE ONLINE rac2 ora....C2.lsnr a pplication 0/5 0/0 ONLINE ONLINE rac2 ora.rac2.gsd application 0/5 0/0 ONLINE ONLINE rac2 ora.rac2.ons application 0/3 0/0 ONLINE ONLINE rac2 ora.rac2.vip ora....t1.type0/0 1/0 ONLINE ONLINE rac2 ora....ry.acfs o ra....fs.type0/5 0/ONLINE ONLINE rac1 ora.scan1.vip ora....ip.type0/0 0/0 ONLINE ONLINE rac1展开全文。

XXXXXXXXXXXXXXXXXXXXOracle数据库健康检查与评估XXXX巡检人：报告生成日期：yyyy-mm-dd文档控制此文档仅供江苏移动审阅，不得向与此无关的个人或机构传阅或复制。

修改记录分发者审阅记录相关文档目录1.检查介绍1.1检查系统系统主要包括1个数据库，具体情况如下：1.2检查范围本次检查仅限于数据库。

在这次检查中对数据库配置和数据库性能进行了分析。

本报告提供的检查和建议不涉及具体的安全分析和应用程序的具体细节。

以下提请注意：本次检查仅历时1天，其中还包括了提交分析报告的时间，所以在具体的应用程序性能方面并不加以深入。

以下列出系统主机的主要配置情况2.1主机配置建议：目前系统配置满足数据库要求，操作系统参数设置合理。

和数据库相关的操作系统配置将被检查，包括以下方面：●操作系统数据库相关要求补丁●存放oracle文件的硬盘区可用空间(oracle文件包括：数据文件，控制文件，在线redo logs，归档redo logs，运行情况文件和跟踪文件)。

●硬盘利用率。

●CPU利用率。

3.1操作系统数据库相关要求补丁建议：3.2硬盘可用空间硬盘可用情况如下示：数据库XXXX的硬盘使用率情况如下：Filesystem kbytes used avail %used Mounted on数据库YYYY的硬盘使用率情况如下：Filesystem kbytes used avail %used Mounted on建议：目前该数据库服务器中还没有其他硬盘空间使用率超过90%的分区。

如果有需要引起注意并且及时增加硬盘空间的容量。

3.3CPU 利用率CPU利用率的统计时间是：yyyy-mm-dd hh:mi---- yyyy-mm-dd hh:mi1.top / glance2.vmstat 2 20参考值：1.最大CPU使用率：60%--70%2.系统进程与用户进程占用CPU最大比率：40/60数据库XXXX:数据库YYYY:从上述的情况中看出，数据库:服务器CPU idle基本在75%以上，CPU资源较为空闲。

月 8年2012．1 前言 (3)一Exadata 概述 (4)1Exadata简介 (4)2Exadata的配置及性能参数 (4)二Exadata特性 (5)1Smart Scan（智能扫描） (5)2Storage Index（存储索引） (14)3Flash Cache（智能闪存） (24)4Compression（压缩） & EHCC（Exadata Hybrid Columnar Compression） 285IORM（IO资源管理） (34)三Exadata监控 (37)1Exadata特性监控常用指标 (37)2如何查看指标 (38)四如何应用Exadata (38)1Exadata参数调整 (38)2在Exadata上开发注意事项 (38)413 ................................................... 应用总结42........................................... 总体总结4. Exadata前言1本文背景1.1前期东软-甲骨文公司组织了一次针对社保系统的Exadata联合应用测试，本文内容是本次Exadata测试的经验总结，其中包含了与Oracle技术人员交流经验应用、Oracle相关技术文档应用及个人测试经验总结。

本文简介1.2本文是关于ORACLE Exadata的一些特性介绍和应用Exadata的一些指南；本文不会涉及太多传统ORACLE DataBase已经具有的而非Exadata专有的一些特性介绍。

通过本文，读者可以了解ORACLE推出 Exadata的目的和初衷，简单了解Exadata 架构体系，了解Exdata的一些设计思路，了解其特性及其原理；了解Exadata 的适合应用场景，不适合应用场景，以及在Exadata下开发的一些注意事项（尤其是做Exadata项目主要设计、开发人员一定要了解Exadata，不要把它完全当作传统ORACLE数据库）。