Oracle11gRAC数据库巡检手册
张浩
数据库检测
硬件机型
HP DL580 G7
是否集群
是
系统实际用户数
10
数据库进程
进入操作系统，登陆Oracle用户，命令：su - oracle
Process
进程情况
进入操作系统，登陆Oracle用户
命令：ps -ef|grep ora_
说明
Oracle 10g 后台进程
SMON(System Monitor)用于执行历程恢复、合并空间碎片并释放临时段。
查看日志
命令：
More/u01/app/oracle/product/11.2.0/db_1/network/log/sqlnet.log
有错误才有日志
Tnsname
配置情况
标准配置
位置：/u01/app/oracle/product/11.2.0/db_1/network/admin
运行情况
正常
查看状态
from
dba_free_space
group by
tablespace_name
) fs
where df.tablespace_name=fs.tablespace_name
order by "Pct Free"
/
运行情况
正常
Sessions
并发数
select count(*) from v$session where status='ACTIVE';
锁表有时候是瞬间的，长时间锁定的表才可能是死锁。
select l.*, s.OSUSER, s.ACTION, o.OBJECT_NAME
from gv$locked_object l, gv_$session s, all_objects o
where l.SESSION_ID=s.SID
and o.OBJECT_ID=l.OBJECT_ID
Redo
使用情况
select b.THREAD#,a.GROUP#,a.STATUS,a.MEMBER,b.BYTES,b.ARCHIVED,b.STATUS
from v$logfile a,v$log b where a.GROUP#=b.GROUP#;
Performance
配置情况
察看数据库锁表
LMON-Global Enqueue Service Monitor全局查询服务监视进程
LCK0 - Instance Enqueue Process 实例查询进程
进程状态
Space
使用情况
df -h
Listener
监听状态
命令：lsnrctl status
配置正常
运行情况
命令：sqlplus“/as sysdba”
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "FreeMB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct Free"
from
(select
PMON(Process Monitor)用于监视服务器进程的执行，摒弃在服务器进程失败时清除该服务器进程。
DBWR(Database Writer)用于将数据库缓存的脏缓冲区数据写入到数据文件中。
LGWR(Log Writer)用于将重做日志缓冲区所记载的全部内容写入到充作日志文件中。
CKPT(Checkpoint Process)用于发出检查点(Checkpoint)，检查点会同步数据库的数据文件、控制文件和重做日志，当发出检查点时，后台进程CKPT将检查点时刻的SCN(System Change Number)写入到控制文件和数据文件头部，同时促使后台进程DBWR将所有脏缓冲区写入到数据文件中。
死锁
set linesize 200
column oracle_username for a16
column os_user_name for a12
column object_name for a30
SELECT l.xidusn, l.object_id,l.oracle_username,l.os_user_name,l.process,
等待事件
set linesize 200
column username for a12
column program for a30
column event for a28
column p1text for a15
column p1 for 999,999,999,999,999
select ername,s.program,sw.event,sw.p1text,sw.p1 from v$session s,v$session_wait sw
ARCH(Archive Process)用于将重做日志的事物变化复制到归档日志中，该后台进程只有在archivelog模式下才有意义。
Oracle 11g新增的后台进程
DBRM
说明: 数据库资源管理进程(The database resource manager process),负责设置资源计划和其他的资源管理的工作.
select name,status from v$datafile;
控制文件状态
select status,name from v$controlfile;
日志文件状态
select group#,members,archived,status from v$log;
表空间使用率
set pagesize 50
SMCO
说明: space management coordinator，该进程负责空间管理协调管理工作，负责执行空间的分配和回收。
Wnnn
说明: 命名为W000，W001，W002.....，由smcO动态产生执行上述相关任务。
VKTM
说明: virtual keeper of time，用于提供wall-clock time，（每秒钟更新一次）。提供每二十毫秒更新一次的
Select * from v$parameter;
运行情况
正常
使用资源情况
select * from v$resource_limit；
ASM
使用情况select grop_number,name,total_mb,free_mb from v$asm_diskgroup;
DBfile
数据文件状态
select owner,segment_name,segment_type,file_id,block_id from dba_extents
select ername||' '||t2.sid||' '||t2.serial#||' '||t2.logon_time||' '||t3.sql_text
from v$locked_object t1,v$session t2,v$sqltext t3
where t1.session_id=t2.sid
tablespace_name,
round(sum(bytes)/1048576) TotalSpace
from
dba_data_files
group by
tablespace_name
) df,
(select
tablespace_name,
round(sum(bytes)/1048576) FreeSpace
column "Tablespace" format a13
column "UsedMB" format 99,999,999
column "FreeMB" format 99,999,999
column "Total MB" format 99,999,999
select
fs.tablespace_name "Tablespace",
资源运行情况
crs_stat–t
用来查看RAC中各节点上resources的运行状况,Resources的属性等
OCR
运行情况
ocrcheck
验证OCR的状态以及空间使用情况
Voting
Disk
运行情况
crsctl query css votedisk
Votingdisk状态查询
ASM
运行情况
crs_stat -t | grep asm
l.session_id,s.serial#, l.locked_mode,o.object_name
FROM v$locked_object l,dba_objects o,v$session s
where l.object_id = o.object_id and s.sid = l.session_id;
where s.sid=sw.sid and s.status='ACTIVE'
order by sw.p1;
select event,p1 "File #",p2 "Block #",p3 "Reason Code" from v$session_wait
order by event;
where event = 'buffer busy waits';
DIAG
说明: 数据库诊断进程(The diagnosibility process),负责维护管理各种用于诊断的转储文件,并执行oradebug命令。
oracle rac11g 基本操作
oracle rac11g 基本操作摘要：1.Oracle RAC 11g 简介2.安装和配置Oracle RAC 11g3.Oracle RAC 11g 基本操作4.管理和监控Oracle RAC 11g5.优化Oracle RAC 11g 性能6.故障排除与维护正文：一、Oracle RAC 11g 简介Oracle RAC 11g（Real Application Clusters 11g）是Oracle 数据库的一个版本，它采用了分布式架构，可以将多个服务器连接在一起，形成一个共享存储的集群。
这种架构大大提高了数据库的性能、可伸缩性和容错能力。
本文将重点介绍Oracle RAC 11g 的一些基本操作。
二、安装和配置Oracle RAC 11g1.准备工作：确保服务器硬件和软件满足Oracle RAC 11g 的最低要求。
2.下载并安装Oracle 11g 软件：从Oracle 官方网站下载相应版本的软件，并根据提示进行安装。
3.配置Oracle RAC 环境：设置Grid Infrastructure 和Oracle Home，创建数据库实例。
4.配置网络和存储：设置存储网络，配置ASM（Automatic StorageManagement）和VIP（Virtual Interface Pool）。
三、Oracle RAC 11g 基本操作1.启动和关闭数据库：使用Oracle 命令启动和关闭数据库实例。
2.创建和管理表空间：使用SQL 语句创建表空间，配置存储参数。
3.创建和管理用户：使用SQL 语句创建用户，设置权限和角色。
4.备份与恢复：使用RMAN（Recovery Manager）进行数据库备份和恢复。
四、管理和监控Oracle RAC 11g1.使用Grid Control：Oracle 提供的一个集中式管理工具，用于监控和控制RAC 环境。
2.使用OEM（Oracle Enterprise Manager）：一个集成化的管理平台，用于监控数据库性能、配置参数等。
Oracle巡检手册
如果有记录返回，则说明存在无效对象。若这些对象与应用相关，那么需要重新编译生成这个对象。
3.6检查回滚段情况
3.6.1检查回滚段状态
输出结果中所有回滚段的“STATUS”该为“ONLINE”。
3.6.2检查是否有回滚段争用
发生回滚段争用时，需要审视争用情况，即哪些用户正在使用回滚段资源
检查监听进程是否存在：
二、检查系统和Oracle日志文件
检查相关的日志文件，包含：检查操作系统的日志文件，检查Oracle日志文件，检查Oracle核心转储目录，检查root用户和oracle用户的email，检查数据库的字符集，总共五个部分。
2.1检查操作系统日志文件
查看是否有与Oracle用户相关的出错信息。
3.3检查Oracle表空间情况
输出结果中STATUS应该都为ONLINE。
3.4检查Oracle所有数据文件情况
输出结果中“STATUS”应该都为“ONLINE”。
或者：
输出结果中“STATUS”应该都为“AVAILABLE”。
3.5检查对象情况
3.5.1检查对象类别和大小
合理分配资源，将冗余以及临时表定期进行清理，释放资源空间。
表空间不够
增加数据文件到相应的表空间
出现ORA-600
根据日志文件的内容查看相应的TRC文件，如果是Oracle的bug，要及时打上相应的补丁
Listener日志：$ORACLE_HOME/network/log/sqlnet.log
2.3检查Oracle核心转储目录
如果上面命令的结果每天都在增长，则说明Oracle进程经常发生核心转储。这说明某些用户进程或者数据库后台进程由于无法处理的原因而异常退出。频繁的核心转储特别是数据库后台进程的核心转储会导致数据库异常终止。
oracle 11g RAC巡检过程说明
oracle rac巡检过程一 RAC环境RAC架构，2节点信息节点1SQL> show parameter instanceNAME TYPE VALUE------------------------------------ ----------- -----------------------------------------------active_instance_count integercluster_database_instances integer 2 instance_groups stringinstance_name string RACDB1 instance_number Integer 1 instance_type string RDBMS open_links_per_instance integer 4 parallel_instance_group stringparallel_server_instances integer 2节点2SQL> show parameter instanceNAME TYPE VALUE------------------------------------ ----------- ------------------------------------------active_instance_count integercluster_database_instances integer 2 instance_groups stringinstance_name string RACDB2instance_number integer 2instance_type string RDBMSopen_links_per_instance integer 4parallel_instance_group stringparallel_server_instances integer 2数据库版本SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0 ProductionTNS for Linux: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - Production操作系统信息节点1[oracle@rac1 ~]$ uname -aLinux rac1 2.6.18-53.el5 #1 SMP Wed Oct 10 16:34:02 EDT 2007 i686 i686 i386 GNU/Linux 节点2[oracle@rac2 ~]$ uname -aLinux rac2 2.6.18-53.el5 #1 SMP Wed Oct 10 16:34:02 EDT 2007 i686 i686 i386 GNU/LinuxRAC所有资源信息[oracle@rac2 ~]$ crs_stat -tName Type Target State Host----------------------------------------------------------------------------------------------ora....B1.inst application ONLINE ONLINE rac1ora....B2.inst application ONLINE ONLINE rac2ora....DB1.srv application ONLINE ONLINE rac2ora.....TAF.cs application ONLINE ONLINE rac2ora.RACDB.db application ONLINE ONLINE rac2ora....SM1.asm application ONLINE ONLINE rac1ora....C1.lsnr application ONLINE ONLINE rac1ora.rac1.gsd application ONLINE ONLINE rac1ora.rac1.ons application ONLINE ONLINE rac1ora.rac1.vip application ONLINE ONLINE rac1ora....SM2.asm application ONLINE ONLINE rac2ora....C2.lsnr application ONLINE ONLINE rac2ora.rac2.gsd application ONLINE ONLINE rac2ora.rac2.ons application ONLINE ONLINE rac2ora.rac2.vip application ONLINE ONLINE rac2二模拟两个节点内联网不通，观察RAC会出现什么现象？给出故障定位的整个过程本小题会模拟RAC的私有网络不通现象，然后定位故障原因，最后排除故障。
oracle健康检查(巡检)手册
性能检查
数据库性能
检查数据库的整体性能，包括响应时间、吞吐量 和资源利用率等。
查询性能
检查特定查询的性能，包括执行计划、索引和查 询优化等方面。
锁和争用
检查数据库中的锁和争用情况，以发现潜在的性 能瓶颈和问题。
03 Oracle数据库巡检方法
手动巡检方法
数据库日志检查
检查Oracle数据库的日志文件，包括警告日志、跟踪文件等，以发现 潜在的问题和错误。
远程巡检方法
远程监控
01
通过远程监控工具，实时监控Oracle数据库的运行状态和性能
指标。
远程诊断
02
通过远程诊断工具，远程连接到数据库服务器，对数据库进行
故障排除和诊断。
远程备份与恢复
03
通过远程备份与恢复工具，远程备份和恢复Oracle数据库的数
据和日志文件。
04 Oracle数据库巡检结果分 析
Oracle Enterprise Manager (OEM)： OEM是一个集成的平台，用于自动监控、 诊断和优化Oracle数据库的性能。
Automatic Database Diagnostic Monitor (ADDM)：ADDM是一个 自动化的性能诊断工具，可以自动 发现和解决性能问题。
在此添加您的文本16字
内存优化
在此添加您的文本16字
调整内存参数：根据数据库的实际需求，合理配置内存参 数，如SGA和PGA的大小。
在此添加您的文本16字
内存泄漏检测：定期检查内存使用情况，发现内存泄漏并 及时处理。
软件优化建议
调整初始化参数
根据数据库的性能需求，调整初始化参数，如打开表的数量、共 享池的大小等。
巡检结果数据收集
oracle数据库巡检内容
精品.资料 oracle 数据库巡检内容1.检查数据库基本状况在本节中主要对数据库的基本状况进行检查，其中包含：检查Oracle 实例状态，检查Oracle 服务进程，检查Oracle 监听进程，共三个部分。
SQL> select instance_name,host_name,startup_time,status,database_status from v$instance;INSTANCE_NAME HOST_NAME STARTUP_TIME STATUS DATABASE_STATUS---------------- ------------------- -------------------- ---------- ------------ ----CKDB AS14 2009-5-7 9:3 OPEN ACTIVE其中“STATUS”表示Oracle 当前的实例状态，必须为“OPEN”；“DATABASE_STATUS”表示Oracle 当前数据库的状态，必须为“ACTIVE”。
SQL> select name,log_mode,open_mode from v$database;NAME LOG_MODE OPEN_MODE--------- ------------ -----------------CKDB ARCHIVELOG READ WRITE其中“LOG_MODE”表示Oracle 当前的归档方式。
“ARCHIVELOG”表示数据库运行在归档模式下，“NOARCHIVELOG”表示数据库运行在非归档模式下。
在我们的系统中数据库必须运行在归档方式下。
$ps -ef|grep ora_|grep -v grep&&ps -ef|grep ora_|grep -v grep|wc –loracle 2960 1 0 May07 ? 00:01:02 ora_pmon_CKDB oracle 2962 1 0 May07 ? 00:00:22 ora_psp0_CKDB oracle 2964 1 0 May07 ? 00:00:00 ora_mman_CKDB oracle 2966 1 0 May07 ? 00:03:20 ora_dbw0_CKDB oracle 2968 1 0 May07 ? 00:04:29 ora_lgwr_CKDB oracle 2970 1 0 May07 ? 00:10:31 ora_ckpt_CKDB oracle 2972 1 0 May07 ? 00:03:45 ora_smon_CKDB oracle 2974 1 0 May07 ? 00:00:00 ora_reco_CKDB oracle 2976 1 0 May07 ? 00:01:24 ora_cjq0_CKDB oracle 2978 1 0 May07 ? 00:06:17 ora_mmon_CKDB oracle 2980 1 0 May07 ? 00:07:26 ora_mmnl_CKDB oracle 2982 1 0 May07 ? 00:00:00 ora_d000_CKDB oracle 2984 1 0 May07 ? 00:00:00 ora_s000_CKDB oracle 2994 1 0 May07 ? 00:00:28 ora_arc0_CKDB oracle 2996 1 0 May07 ? 00:00:29 ora_arc1_CKDB oracle 3000 1 0 May07 ? 00:00:00 ora_qmnc_CKDB oracle 3625 1 0 May07 ? 00:01:40 ora_q000_CKDB oracle 31594 1 0 Jul20 ? 00:00:00 ora_q003_CKDB oracle 23802 1 0 05:09 ? 00:00:33 ora_j000_CKDB 19在检查Oracle的进程命令输出后，输出显示至少应包括以下一些进程：. Oracle写数据文件的进程，输出显示为：“ora_dbw0_CKDB”精品.资料精品.资料 . Oracle 写日志文件的进程，输出显示为：“ora_lgwr_ CKDB”. Oracle 监听实例状态的进程，输出显示为：“ora_smon_ CKDB”. Oracle监听客户端连接进程状态的进程，输出显示为：“ora_pmon_ CKDB”. Oracle 进行归档的进程，输出显示为：“ora_arc0_ CKDB”. Oracle 进行检查点的进程，输出显示为：“ora_ckpt_ CKD B ”. Oracle 进行恢复的进程，输出显示为：“ora_reco_ CKDB”/home/oracle>lsnrctl statusLSNRCTL for Linux: Version 10.2.0.2.0 - Production on 23-JUL-2009 14:11:53Copyright (c) 1991, 2005, Oracle. All rights reserved.Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 10.2.0.2.0 - ProductionStart Date 07-MAY-2009 09:35:52Uptime 77 days 4 hr. 36 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /data/oracle/product/10.2.0/network/admin/listener.oraListener Log File /data/oracle/product/10.2.0/network/log/listener.logListening Endpoints Summary...(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AS14)(PORT=1521)))Services Summary...Service "CKDB" has 1 instance(s).Instance "CKDB", status READY, has 1 handler(s) for this service...Service "CKDBXDB" has 1 instance(s).Instance "CKDB", status READY, has 1 handler(s) for this service...Service "CKDB_XPT" has 1 instance(s).Instance "CKDB", status READY, has 1 handler(s) for this service...The command completed successfully“Services Summary”项表示Oracle的监听进程正在监听哪些数据库实例，输出显示中至少应该有“CKDB”这一项。
Oracle数据库安装及检查指导手册v1
Oracle数据库安装及检查指导手册1.文档说明面向人员：业务平台部署和维护人员版本说明：面向Oracle10g、11g版本2.数据库安装2.1.版本选择1）操作系统版本使用64位操作系统（unix、linux、Windows）具体要求版本及补丁安装请参考安装文档。
2）数据库版本10g:10.2.0.5（64位）11g:11.2.0.3（64位）2.2.数据库规划1）总体规划序号内容建议说明1 数据库安装路径ORACLE_BASE=/opt/oracleORACLE_HOME=/opt/oracle/product/10.2.0/db_ 12 数据文件路径/opt/oracle/oradata/<sid> Sid为实例名。
使用磁盘阵列应放到磁盘阵列上3 数据库软件类型双机热备数据库软件安装在本机磁盘，数据库文件安装在共享存储上4 操作系统要求包括操作系统版本补丁、内核参数，内存、交换分区、/tmp大小等参考官方安装手册54）数据库参数序号内容建议说明1 字符集选择UTF-8需要支持多国语言文字（日、韩等）建议用UTF8,仅中文环境也可以用GBK2 数据库内存大小物理内存×80%3 SGA大小数据库内存×80%4 PGA大小数据库内存×20% 根据应用情况调整5 Session数300 根据应用情况调整6 Redo log 500M 根据应用情况调整3.数据库检查表3.1.操作系统参数检查序号检查内容检查方法接收标准1 版本Redhat Linux:cat /etc/redhat-releaseUname -aWindows:附件：《查看windows操作系统是32位或者64位的方法》64位操作系统，补丁要求参考官方安装手册3.2.数据库版本检查序号检查内容检查方法接收标准1 版本select * from v$version64位企业版3.3.数据库重要参数检查序号检查内容检查方法接收标准1 字符集select value"数据库字符集" fromnls_database_parameters whereparameter='NLS_CHARACTERSET'AL32UTF8 ZHS16GBK2 Control_files3 Sga大小select value/1024/1024"sga size" fromv$parameter where name='sga_target' 4 Pga大小select value/1024/1024"pga size" fromv$parameter wherename='pga_aggregate_target'5 Redo log select b.MEMBER "redo文件名",a.BYTES/1024/1024"文件size" fromv$log a,v$logfile bwhere a.GROUP#=b.GROUP#3.4.数据库运行检查序号检查内容检查方法接收标准1 数据库监听器状态检查以oracle用户执行lsnrctl status结果应该显示连接listener成功信息，并且显示一个和数据库同名的instance为ready或者unknown状态2 数据库告警日志检查在$ORACLE_BASE/admin/<sid>/bdump目录下检查Alert<sid>.log如果没有错误需要反馈，而且此时文件已经超过10m,则将当前Alert<sid>.log重命名为Alert<sid>_当前日期.log，并删除bdump,udump下不需要的.trc文件检查以“ORA-”开头的错误，将近期在日志中持续出现的ORA-错误以及所产生的TRACE文件反馈给公司。
数据库服务巡检操作说明
数据库服务巡检操作说明一、检查cpu使用率1、Cpu正常范围<=70%2、Cpu的检查1,分析关键应用程序的性能2,定位问题的根源是在客户端、服务器、应用程序还是网络3,哪些应用程序占用大量带宽3、是Windows任务管理器，可以通过同时按Ctrl+Alt+Delete三个按键调出如图二、检查数据库开启情况1、数据库服务为已启动2、数据库服务的检查主要是确定服务正常启动，分析数据库无法连接等问题3、操作1.我的电脑--右键—管理如图：2.服务和应用程序---服务打开找到oraclesevice如图：查看OoracleService的状态是否是已启动状态三、用hostmonitor监控RMA进程和hostmonitor日志的查看1、Rma进程为正常运行，日志查看无异常2、数据库服务的检查主要是确定服务正常运行，时时监控进行的开关状态，报错日志的分析3、操作详细操作看《Hostmonitor操作手册》四、检查系统时间1、系统时间为当前时间2、数据库服务的检查主要是确定系统的时间匹配3、操作1.系统开始----运行-----cmd2.在命令输入框输入date3.在命令输入框输入time五、查看表空间使用情况1、数据库表空间的剩余空间是否足够2、数据库服务的检查主要是确定数据库空间足够3、操作1.系统开始----运行----cmd2.在命令输入框sqlplus username/password@oraclename 连接数据库如图：3.再在命令输入框输入select A.tablespace_name as "tablespace name",A.total_size as "total size",round(B.total_free_size,1) as "total free size",round((A.total_size - B.total_free_size),2) as "used size",to_char(100*B.total_free_size/A.total_size,'99.99')||'%' as "percent free" from (select tablespace_name,sum(bytes)/1024/1024 as total_size from dba_data_files group by tablespace_name) A,(select tablespace_name,sum(bytes/1024/1024) as total_free_size from dba_free_space group by tablespace_name) B where A.tablespace_name = B.tablespace_name;结果如图：六、查看表空间的状态1、数据库表空间状态为ONLINE是正常2、数据库服务的检查主要是确定数据库表空间的状态3、操作1.系统开始----运行----cmd2.在命令输入框sqlplus username/password@oraclename 连接数据库如图‘五‘一样3.再在命令输入框输入select tablespace_name,status from dba_tablespaces; 查询结果如图：七、查看日志切换间隔1、数据库日志切换间隔在3-5分钟为最好2、数据库服务的检查主要是确定数据库表空间的状态3、操作1.系统开始----运行----cmd2.在命令输入框sqlplus username/password@oraclename 连接数据库如图‘五‘一样3.再在命令输入框输入SELECTB.RECID,B.FIRST_TIME,A.FIRST_TIME,ROUND((A.FIRST_TIME-B.FIRST_TIME)*24*60,2) MINATESFROM V$LOG_HISTORY A,V$LOG_HISTORY BWHERE A.RECID=B.RECID +1 AND A.FIRST_TIME>SYSDATE - 20 ANDROUND((A.FIRST_TIME-B.FIRST_TIME)*24*60,2)<30ORDER BY A.FIRST_TIME DESC;查询结果如图：八、数据库缓冲区命中率1、正常值是大于或等于95％，如果发现该值低于90%就是不正常的2、数据库服务的检查数据字典缓冲区是Oracle特地为数据字典准备的一块缓冲池，供Oracle内部使用3、操作1.系统开始----运行----cmd2.在命令输入框sqlplus username/password@oraclename 连接数据库如图‘五‘一样3.再在命令输入框输入SELECT(1 - (SUM(DECODE(NAME, 'physical reads', VALUE, 0)) /(SUM(DECODE(NAME, 'db block gets', VALUE, 0))+SUM(DECODE(NAME, 'consistent gets', VALUE, 0))))) * 100 "缓冲区命中率"FROM V$SYSSTAT;查询结果如图：九、检查数据库定时作业的完成情况1、FAILURES列是一个大于0的数的话，说明JOB运行失败2、数据库服务的检查主要是用来完成一些定时作业3、再在命令输入框输入select job,log_user,last_date,failures from dba_jobs;查询结果：十、查看数据库的报警日志文件1、看有没有错误记录2、报警日志文件的检查主要是记录数据库后台进程和报警错误的信息3、操作1、打开路径：数据库所在盘符／数据库文件夹／admin／orcl2、第一个文件是后台进程的跟踪文件，同时也是存放警告日志所在的文件夹、第二个文件夹是用户进程的跟踪文件。
oracle巡检 手册
Oracle巡检手册第一部分数据库状态监控首先检查oracle的log，在sqlplus中：show parameter background_dump_dest;select * from v$diag_info;可以得到日志路径1:检查oracle 监听lsnrtcl statusPs –ef|grep ora2:检查oracle初始化参数Select * from v$parameter;3:检查oracle实例状态Select instance_name,version,status,database_status from v$instance;select inst_id,instance_name,host_name,VERSION,TO_CHAR(startup_time,'yyyy-mm-dd hh24:mi:ss')startup_time,status,archiver,database_status FROM gv$instance;4:检查后台进程状态：select name,Description From v$BGPROCESS Where Paddr<>'00'5:查看系统全局区SGA信息select * from v$sga;6: 查看SGA各部分占用内存情况：select * from v$sgastat;select request_misses,request_failures from v$shared_pool_reserved;比较好的状态：REQUEST_MISSES REQUEST_FAILURES为0或者接近0REQUEST_MISSES REQUEST_FAILURES-------------- ----------------007：查看系统SCN号select (select dbms_flashback.get_system_change_number from dual)scn,current_scn,scn_to_timestamp(current_scn)from v$database;8：检查数据库状态：select name,log_mode,open_mode,platform_name from v$database;select inst_id,dbid,name,to_char(created,'yyyy-mm-dd hh24:mi:ss')created,log_mode,to_char(version_time,'yyyy-mm-ddhh24:mi:ss')version_time,open_mode from gv$database;第二部分：数据库空间监控检查表空间使用率select A.tablespace_name, (1 - (A.total) / B.total) * 100 used_percentfrom (select tablespace_name, sum(bytes) totalfrom dba_free_spacegroup by tablespace_name) A,(select tablespace_name, sum(bytes) totalfrom dba_data_filesgroup by tablespace_name) Bwhere A.tablespace_name = B.tablespace_name;检查system表空间内的内容select distinct (owner)from dba_tableswhere tablespace_name = 'SYSTEM'and owner != 'SYS'and owner != 'SYSTEM'unionselect distinct (owner)from dba_indexeswhere tablespace_name = 'SYSTEM'and owner != 'SYS'and owner != 'SYSTEM';输出：no rows selected分析：如果有记录返回，则表明system表空间内存在一些非system和sys用户的对象。
Oracle Database 11g Oracle Tuning Pack数据手册说明书
ORACLE TUNING PACKFEATURES• SQL Tuning Advisor• Automatic SQL TuningAdvisor• Real-Time SQL Monitoring• SQL Profiles• SQL Access Advisor• SQL Tuning Sets• Object Reorganization WizardBENEFITS• Comprehensive solution forapplication and SQL tuningthat eliminates need formanual tuning.• Provides automatic tuning ofSQL statements.• Enhances systemperformance and reliabilityand significantly lowersmanagement costs.Oracle Tuning Pack, an add-on pack for management of OracleDatabase 11g, offers an extremely cost effective and easy-to-use solution that automates the entire application tuning process. Enhancement of SQL performance is achieved through real-time monitoring and SQL Advisors that are seamlessly integrated with the Enterprise Manager, and together provide a comprehensive solution for automating the complex and time-consuming task of application tuning. For database administrators and application developers, application tuning is a critically important area and a considerable amount of their time is spent performing this very important function. A poorly tuned business application can potentially affect not just a few users but an entire business operation and for this reason companies invest significant resources to ensure smooth running of applications vital for their businesses. SQL Access Advisor The design of the database schema can have a big impact on the overall application performance. SQL Access Advisor, provides comprehensive advice on how to optimize schema design in order to maximize application performance. SQL Access and SQL Tuning Advisors, together, provide a complete solution for tuning database applications. The SQL Access Advisor accepts input from all possible sources of interest, such as the cursor cache, the Automatic Workload Repository (AWR), any user-defined workload, and will even generate a hypothetical workload if a schema contains dimensions or primary/foreign key relationships. It comprehensively analyzes the entire workload and provides recommendations to create new partitions or indexes if required, drop any unused indexes, create new materialized views and materialized view logs. Determining the optimal partitioning or indexing strategy for a particular workload is a complicated process that requires expertise and time. SQL Access Advisor considers the cost of insert/update/delete operations in addition to the queries on the workload and makes appropriate recommendations, accompanied by a quantifiable measure of expected performance gain as well as scripts needed to implement the recommendations. The SQL Access Advisor takes the mystery out of access structure design process. By automating this very critical function, SQL Access Advisor obviates the need for the error-prone, lengthy, and expensive manual tuning process. SQL Tuning Advisor Manual SQL tuning is a complex process that presents many challenges. It requires expertise in several areas, is very time consuming, and requires an intimateknowledge of the schema structures and the data usage model of the application. All these factors make manual SQL tuning a challenging and resource intensive task that is ultimately very expensive for businesses.SQL Tuning Advisor is Oracle’s answer to all the pitfalls and challenges of manual SQL tuning. It automates the SQL tuning process by comprehensively exploring all the possible ways of tuning a SQL statement. The analysis and tuning is performed by the database engine’s significantly enhanced query optimizer. Four types of analysis are performed by the SQL Tuning Advisor:• Statistics Analysis: In this analysis objects with stale or missing statistics are identified and appropriate recommendations are made to remedy the problem.• SQL Profiling: This feature, introduced in Oracle Database 10g, revolutionizes the approach to SQL tuning. SQL Profiling tunes SQL statements withoutrequiring any change to the application code.• Access Path Analysis: In this analysis new indexes that can significantlyenhance query performance are identified and recommended.The output of this analysis is in the form of recommendations, along with a rationale for each recommendation and its expected performance benefit.The SQL Tuning Advisor offers a powerful, intuitive, and user-friendly way for performing SQL tuning. Tuning of SQL statements no longer has to be the domain of experts. Oracle has built a tuning expert inside the database engine to perform this very important function for the database administrators in a fraction of the time and cost needed to carry out the same task manually.Automatic SQL Tuning AdvisorThe SQL Tuning Advisor also runs in automatic mode. In this mode, the advisor runs automatically during system maintenance windows as a maintenance task. During each run, the advisor selects high-load SQL queries in the system, and generates recommendations on how to tune them.Figure 1: Automatic SQL Tuning Results SummaryThe Automatic SQL Tuning Advisor can be configured to auto-implement SQL Profile recommendations. If you enable automatic implementation, the advisor will create SQL Profiles for only those SQL statements where performance improvement would be at least threefold. Other types of recommendations such as to create newindexes, refresh optimizer statistics or restructure SQL can only be implemented manually. DML statements are not considered for tuning by the Automatic SQL Tuning Advisor.You can view a summary of the automatic SQL tuning results over a specified period, and can view a detailed report on recommendations made for all SQL statements processed. The recommendations can then be implemented selectively by a manual process. You can also view the recommendations that were automatically implemented.Real-time SQL MonitoringThe first step in SQL tuning is identifying poor SQL that is consuming excessive system resources. Traditionally, DBAs have always struggled with long-running SQL in live production environments because they never had the tools to figure out if the long-running query was moments away from completion or a run-away query that could take an inordinate amount of time to complete. Real-Time SQL Monitoring introduced in Oracle Database 11g provides the fastest and easiest way to identify and fix performance problems with long running SQL statements. Live visual displays track the details of SQL execution using new, fine-grained SQL statistics that are updated automatically at no cost to the performance of production systems.By default, SQL monitoring is automatically started when a SQL statement runs parallel, or when it has consumed at least 5 seconds of CPU or IO time in a single execution. Once monitoring is initiated, an entry is added to performance views. This entry tracks row source information at each step of the execution collecting key performance metrics, including the elapsed time, CPU time, number of reads and writes, I/O wait time and various other wait times. This allows the DBAs to decide whether to let the query complete or terminate the query.Figure 1: Real Time SQL MonitoringReal Time SQL Monitoring has been enhanced in Oracle Database 11g Release 2 to support execution plans that are being executed in part by the Oracle Databasemachine, Exadata. DBAs now have a powerful tool to track complex execution plans, identify poor indexing mechanisms and identify skew in parallel queries– all in real time. Object Reorganization Wizard Oracle Tuning Pack also provides the ability to reorganize objects. Managing the space usage of your tablespaces efficiently by removing wasted space is not only a good space management practice but it also enhances performance by reducing unnecessary disk I/Os. Reorganization is used for: 1) rebuilding indexes and tables that are fragmented, 2) relocating objects to another tablespace, and 3) recreating objects with optimal storage attributes. Contact Us For more information about [insert product name], please visit or call +1.800.ORACLE1 to speak to an Oracle representative. Copyright © 2010, Oracle and/or its affiliates. All rights reserved. This document is provided for information purposes only and the contents hereof are subject to change without notice. This document is not warranted to be error-free, nor subject to any other warranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchantability or fitness for a particular purpose. We specifically disclaim any liability with respect to this document and no contractual obligations are formed either directly or indirectly by this document. This document may not be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without our prior written permission. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. UNIX is a registered trademark licensed through X/Open Company, Ltd. 0110Oracle Tuning Pack 11g delivers maximum benefits when used with the following Oracle products:RELATED PRODUCTS • Oracle Diagnostics Pack • Oracle Configuration Management Pack • Oracle Provisioning Pack • Oracle Database Change Management Pack • Oracle Real Application Testing Option。
★Oracle数据库巡检操作内涵
★Oracle数据库巡检操作内涵Oracle数据库巡检操作内涵吴汉耿资历荣誉履历曾任医保办公室主任、副主任、信息科科长、副科长、后勤科副科长等。
现为医保办公室副主任，具备高级岗位胜任力，取得中国医院协会医院医疗保险管理专业委员会合中国国家人事人才培训网联合签发的《从业人员培训合格证书》。
荣誉汕头市无偿献血先进个人，广东省无偿献血奉献奖铜奖获得者，国家无偿献血奉献奖铜奖获得者；广东省卫生信息化卓越青年工程师；广东省医学信息优秀管理者。
学术情况论文发表论文16篇，其中获得二等奖3篇，三等奖2篇，成果奖1篇，汕头科协三等奖1篇学者库编号PCNI:0000001 00834855X著作《思考的魅力》、《儒衢缵绪》、《中西结合·方剂条例》和《刺腧条辨》著作权《中西结合·方剂条例》登记号：国作登字-2020-A-00009537，《刺腧条辨》登记号：国作登字-2022-A-10116949,获中国版权保护中心认证，受《中华人民共和国著作权法》保护。
学术兼职汕头市汕头市计算机学会理事广东省医院协会信息专委会委员并潮汕分委会常务委员广东省广东省医院协会医院品管圈管理专业委员会委员广东省医院协会医院医疗保险管理专业委员会委员广东省卫生经济学会信息专委会常务委员广东省中医药学会会员、广东省中西医结合学会会员国家级中国中西医结合学会会员中国医药新闻信息协会智慧医院与后勤安全信息分会理事《中华现代医院管理杂志》常务编委一、Oracle数据库巡检概述目录二、Oracle数据库体系架构介绍三、数据库性能优化概述四、案例经验分享数据库系统的重要性数据是血液，数据库系统是心脏应用程序是大脑网络是血管、神经硬件是肌肉和骨骼数据库巡检概述启动、关闭数据库查看告警日志文件查看OS日志OS文件系统表空间管理检查备份与容灾性能监控启动数据库Linux/AIX: #su–oracle$exportORACLE_SID=ora11g$lsnrctlstart$sqlplus‘/assysdba’SQL>star tupWindows:-控制面板\系统和安全\管理工具?服务?OracleOraDb11g_home1TNSListener?启动OracleServiceORA11G?启动启动数据库（续）Windows 注册表：HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY_OraDb11g_home1注意各个参数以及数值的名称格式之间的关系启动数据库（续）如果ORA_ORA11G_AUTOSTART=falsecmd.exe:sqlplus/assysdbaSQL> startup关闭数据库Linux/AIX: #su–oracle$exportORACLE_SID=ora11g$lsnrctlstop$sqlplus‘/assysdba’SQL>shutdownimmediate Windows:-控制面板\系统和安全\管理工具?服务?OracleOraDb11g_home1TNSListener?停止OracleServiceORA11G?停止关闭数据库（续）cmd.exe:sqlplus/assysdbaSQL>shutdownimmediate如果不停止后台服务，内存是不会释放的。