Oracle常用监控脚本

1.日常管理
1.1 rac启动与关闭
要想启动或关闭crs服务必须首先切换到root用户，如下
su - root
启动crs 服务：
启动crs
#$crs_home/crs/bin/crsctl start crs
查看crs状态
#$crs_home/crs/bin/crsctl check crs
关闭crs
#$crs_home/crs/bin/crsctl stop crs
查看crs内部各资源状态
#$crs_home/crs/bin/crs_stat –t
启动数据库服务
# srvctl start database -d instancename #instancename为数据库名

1.2检查altersid.log
这个日志文件位于参数background_dump_dest指定的目录，可能通过以下命令来查看。
sql> show parameter background_dump_dest
检查altersid.log
oracle 出错信息可通过$grep ora- altersid.log查找
1.3环境确认
数据库实例是否正常工作
sql > select status from v$instance;
数据库监听器是否正常工作
$ lsnrctl status
是否存在故障表空间
select tablespace_name,status from dba_tablespace;
控制文件、日志文件是否正常
select * from v$controlfile;
select * from v$log;
select * from v$logfile;
性能监测
每天按业务峰值情况，对数据库性能数据进行定时采集
每天检查数据库的主要性能指标
每天检查最消耗资源的sql语句变化情况。
每天检查是否有足够的资源
检查所有表空间的剩余情况
识别出一些异常的增长
检查cpu、内存、网络等是否异常
1.4文件备份
控制文件备份
alter system backup controlfile to trace;
初始参数备份
create pfile=$oracel_home/dbs/pfile20151212.ora’ from spfile;
其它
tnsnames.ora，listener.ora 使用cp。
2.常用sql
2.1查看表空间物理文件的名称及大小
select tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) filesize
from dba_data_files
order by tablespace_name;
2.2查询表空间使用情况
select a.tablespace_name "表空间名称",
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100, 2) "占用率(%)",
round(a.bytes_alloc / 1024 / 1024, 2) "容量(m)",
round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) "空闲(m)",
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) "使用(m)",
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') "采样时间"
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible, 'yes', f.maxbytes, 'no', f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name, sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by 2 desc;
2.3查询表空间的碎片程度
select tablespace_name, count(tablespace_name)
from dba_free_space
group by tablespace_name
having count(tablespace_name) > 10;

alter tablespace hs_user_data coalesce;
alter table name deallocate unused;
2.4碎片程度
select tablespace_name, count(tablespace_name)
from dba_free_space
group by tablespace_name
having count(tablespace_name) > 10;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_name, block_id, bytes, blocks, segment_name
from dba_free_space
union all
select tablespace_name, block_id, bytes, blocks, segment_name
from dba_extents;
select * from ts_blocks_v;
select tablespace_name, sum(bytes), max(bytes), count(block_id)
from dba_free_space
group by tablespace_name;
查看碎片程度高的表
select segment_name table_name, count(*) extents
from dba_segments
where owner not in ('sys', 'system')
group by segment_name
having count(*) = (select max(count(*))
from dba_segments
group by segment_name);

2.5查看回滚段名称及大小
select segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) initialextent,
(next_extent / 1024) nextextent,
max_extents,
v.curext curextent
from dba_rollback_segs r, v$rollstat v
where r.segment_id = v.usn(+)
order by segment_name;

2.6查看控制文件
select name from v$controlfile;
2.7查看日志文件
select member from v$logfile;
2.8查看表空间的使用情况
select sum(bytes) / (1024 * 1024) as free_space, tablespace_name
from dba_free_space
group by tablespace_name;

select a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% used",
(c.bytes * 100) / a.bytes "% free"
from sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
where a.tablespace_name = b.tablespace_name
and a.tablespace_name = c.tablespace_name;

2.9查看数据库对象
select owner, object_type, status, count(*) count#
from all_objects
group by owner, object_type, status;
2.10查看数据库的版本
select version
from product_component_version
where substr(product, 1, 6) = 'oracle';
2.11查看oracle字符集
select * from sys.props$ where name = 'nls_characterset';
2.12在某个用户下找所有的索引
select user_indexes.table_name,
user_indexes.index_name,
uniqueness,
column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type,
user_indexes.table_name,
user_indexes.index_name,
column_position;
2.13表、索引的存储情况检查
select segment_name, sum(bytes), count(*) ext_quan
from dba_extents
where tablespace_name = '&tablespace_name'
and segment_type = 'table'
group by tablespace_name, segment_name;
select segment_name, count(*)
from dba_extents
where segment_type = 'index'
and owner = '&owner'
group by segment_name;

2.14查看数据库的创建日期和归档方式
select created, log_mode, log_mode from v$database;

2.15显示所有数据库对象的类别和大小
select type,
count(name) num_instances,
sum(source_size) source_size,
sum(parsed_size) parsed_size,
sum(code_size) code_size,
sum(error_size) error_size,
sum(source_size) + sum(parsed_size) + sum(code_size) +
sum(error_size) size_required
from dba_object_size
group by type
order by 1;

2.16设置rac为归档模式
步骤：
1.以sysdba身份登陆2个节点，执行
alter system set cluster_database=false scope =spfile sid=’*’;
设置归档路径
alter system set log_archive_start=true scope=spfile;
2.2个节点
shutdown immediate
3.在一个节点上执行
startup mount
alter database archivelog;
shutdown immediate;
alter database open;
alter system set cluster_database=true scope =spfile sid=’*’;
shutdown immediate
4、分别启动2个节点，修改完毕

2.17awr报告
$sqlplus / as sysdba
生成断点快照
sql> exec dbms_workload_repository.create_snapshot();
生成报告
sql> @ /rdbms/admin/awrrpt.sql

3.常用监控sql
常用性能相关sql，监控数据库性能的sql语句。
3.1监控事务的等待
select event,
sum(decode(wait_time, 0, 0, 1)) "prev",
sum(decode(wait_time, 0, 1, 0)) "curr",
count(*) "totol"
from v$session_wait
group by event
order by 4;
3.2查看一些等待信息：
select sid, event
from v$session_wait
where event not like 'sql%'
and event not like '%ipc%';

查看是否存在下面等常见的等待事件：
buffer busy waits，
free buffer waits，
db file sequential read,
db file scattered read,
enqueue,latch free,
log file parallel write,
log file sync
3.3查看等待(wait)情况
select v$waitstat.class,
v$waitstat.count count,
sum(v$sysstat.value) sum_value
from v$waitstat, v$sysstat
where v$sysstat.name in ('db block gets', 'consistent gets')
group by v$waitstat.class, v$waitstat.count;　　
3.4回滚段查看
select rownum,
sys.dba_rollback_segs.segment_name name,
v$rollstat.extents extents,
v$rollstat.rssize size_in_bytes,
v$rollstat.xacts xacts,
v$rollstat.gets gets,
v$rollstat.waits waits,
v$rollstat.writes writes,
sys.dba_rollback_segs.status status
from v$rollstat, sys.dba_rollback_segs, v$rollname
where v$rollname.name(+) = sys.dba_rollback_segs.segment_name
and v$rollstat.usn(+) = v$rollname.usn
order by rownum;
3.5回滚段的争用情况
select name, waits, gets, waits / gets "ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
3.6监控表空间的 i/o 比例
select df.tablespace_name name,
df.file_name "file",
f.phyrds pyr,
f.phyblkrd pbr,
f.phywrts pyw,
f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
3.7监控文件系统的 i/o 比例
select substr(a.file#, 1, 2) "#",
substr(a.name, 1, 30) "name",
a.status,
a.bytes,
b.phyrds,
b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
3.8监控 sga 的命中率
select a.value + b.value "logical_reads",
c.value "phys_reads",
round(100 * ((a.value + b.value) - c.value) / (a.value + b.value)) "buffer hit ratio"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 -- physical read total multi block requests
and b.statistic# = 39 -- physical read total bytes
and c.statistic# = 40; -- physical write total io requests
3.9监控 sga 中字典缓冲区的命中率
select parameter,
gets,
getmisses,
getmisses / (gets + getmisses) * 100 "miss ratio",
(1 - (sum(getmisses) / (sum(gets) + sum(getmisses)))) * 100 "hit ratio"
from v$rowcache
where gets + getmisses <> 0
group by parameter, gets, getmisses;
3.10监控 sga *享缓存区的命中率，应该小于1%
select sum(pins) "total pins",
sum(reloads) "total reloads",
sum(reloads) / sum(pins) libcache
from v$librarycache;
select sum(pinhits - reloads) / sum(pins) * 100 "hit radio",
sum(reloads) / sum(pins) "reload percent"
from v$librarycache;
3.11临控 sga 中重做日志缓存区的命中率，应该小于1%
select name,
gets,
misses,
immediate_gets,
immediate_misses,
decode(gets, 0, 0, misses / gets * 100) ratio1,
decode(immediate_gets + immediate_misses,
0,
0,
immediate_misses / (immediate_gets + immediate_misses) * 100) ratio2
from v$latch
where name in ('redo allocation', 'redo copy');
3.12监控内存和硬盘的排序比率，最好使它小于 0.10，增加 sort_area_size
select name, value
from v$sysstat
where name in ('sorts (memory)', 'sorts (disk)');
3.13监控当前数据库谁在运行什么sql语句
select osuser, username, sql_text
from v$session a, v$sqltext b
where a.sql_address = b.address
order by address, piece;
3.14监控字典缓冲区
select sum(pins) "executions",
sum(reloads) "cache misses while executing",
(sum(pins - reloads)) / sum(pins) "lib cache"
from v$librarycache;

select sum(gets) "dictionary gets",
sum(getmisses) "dictionary cache get misses",
(sum(gets - getmisses - usage - fixed)) / sum(gets) "row cache"
from v$rowcache;
“lib cache“与“row cache”越接近1.00超好，不要低于0.90。否则需要调大sga的空间。
3.15查看lock
select s.osuser,
l.sid,
s.serial#,
s.username,
s.terminal,
decode(l.type,
'tm',
'tm - dml enqueue',
'tx',
'tx - trans enqueue',
'ul',
'ul - user',
l.type || ' - other type') locktype,
substr(t.name, 1, 10) object,
u.name owner,
l.id1,
l.id2,
decode(l.lmode,
1,
'no lock',
2,
'row share',
3,
'row exclusive',
4,
'share',
5,
'shr row excl',
6,
'exclusive',
null) lmode,
decode(l.request,
1,
'no lock',
2,
'row share',
3,
'row excl',
4,
'share',
5,
'shr row excl',
6,
'exclusive',
null) request
from v$lock l, v$session s, sys.user$ u, sys.obj$ t
where l.sid = s.sid
and s.type != 'background'
and t.obj# = l.id1
and u.user# = t.owner#;
3.16捕捉运行很久的sql
select username,
sid,
opname,
round(sofar * 100 / totalwork, 0) || '%' as progress,
time_remaining,
sql_text
from v$session_longops, v$sql
where time_remaining <> 0
and sql_address = address
and sql_hash_value = hash_value;　
3.17查看数据表的参数信息
select partition_name,
table_name,
high_value,
high_value_length,
tablespace_name,
pct_free,
pct_used,
ini_trans,
max_trans,
initial_extent,
next_extent,
min_extent,
max_extent,
pct_increase,
freelists,
freelist_groups,
logging,
buffer_pool,
num_rows,
blocks,
empty_blocks,
avg_space,
chain_cnt,
avg_row_len,
sample_size,
last_analyzed
from sys.dba_tab_partitions
where table_owner = 'hs_his' and table_name = 'hisbanktrade'
--where table_name = :tname and table_owner = :towner
order by partition_position,partition_name;　　
3.18查看还没提交的事务
select * from v$locked_object;
select * from v$transaction;
3.19查找object为哪些进程所用
select p.spid,
s.sid,
s.serial# serial_num,
s.username user_name,
a.type object_type,
s.osuser os_user_name,
a.owner,
a.object object_name,
decode(sign(48 - command),
1,
to_char(command),
'action code #' || to_char(command)) action,
p.program oracle_process,
s.terminal terminal,
s.program program,
s.status session_status
from v$session s, v$access a, v$process p
where s.paddr = p.addr
and s.type = 'user'
and a.sid = s.sid
and a.object = 'fundreal'
order by s.username, s.osuser;
3.20查看catched object
select owner,
name,
db_link,
namespace,
type,
sharable_mem,
loads,
executions,
locks,
pins,
kept
from v$db_object_cache where owner like 'hs_%';
3.21查看v$sqlarea
select sql_text,
sharable_mem,
persistent_mem,
runtime_mem,
sorts,
version_count,
loaded_versions,
open_versions,
users_opening,
executions,
users_executing,
loads,
first_load_time,
invalidations,
parse_calls,
disk_reads,
buffer_gets,
rows_processed
from v$sqlarea;

3.22有关connection的相关信息
查看有哪些用户连接
select s.sid,
s.serial# serial_num,
s.osuser os_user_name,
decode(sign(48 - command),
1,
to_char(command),
'action code #' || to_char(command)) action,
p.program oracle_process,
status session_status,
s.terminal terminal,
s.program program,
s.username user_name,
s.fixed_table_sequence activity_meter
from v$session s, v$process p
where s.paddr = p.addr
and s.type = 'user' order by s.username, s.osuser;

2)根据v.sid查看对应连接的资源占用等情况
select n.name, v.value, n.class, n.statistic#
from v$statname n, v$sesstat v
where v.sid = &sid
and v.statistic# = n.statistic#
order by n.class, n.statistic#;

3)根据sid查看对应连接正在运行的sql
select /*+ push_subq */
command_type,
sql_text,
sharable_mem,
persistent_mem,
runtime_mem,
sorts,
version_count,
loaded_versions,
open_versions,
users_opening,
executions,
users_executing,
loads,
first_load_time,
invalidations,
parse_calls,
disk_reads,
buffer_gets,
rows_processed,
sysdate start_time,
sysdate finish_time,
'>' || address sql_address,
'n' status
from v$sqlarea
where address = (select sql_address from v$session where sid = &sid);

4.常见问题解决
4.1常用参数设置参考
参数 说明 参考设置
db_writer_processes /配置为cpu内核数的一半 /4
open_cursors /根据as数量及as连接数，配置为3000 ~ 5000 /5000
optimizer_mode /rule /rule
pga_aggregate_target /同样，也与根据as数量及as连接数相关，一般为1500 ~ 3000。一般1g ~ 3g /3g
processes /进程数，即可以对外提供服务的数据。同样，也与根据as数量及as连接数相关，一般为1500 ~ 3000 /2000
session_cached_cursors /300
sessions /设置processes后自动调整。 /2205
sga_max_size /内存的一半，如果存储使用的是裸设备，可以提高到60% /10g
sga_target /同上；若设置为小于等sga_max_size，可动态修改；否则则需要先设置sga_max_size /10g
shared_pool_size /不超过800m，一般300m到500m就足够了 /800m
注意：基于linux平安的rac不可设sga太大，否则可能会有问题。
sql> sqlplus / as sysdba
当前参数值查看方法如下(open_cursors)：
sql> show parameter open_cursors;
参数设置方法如下：
sql> alter system set open_cursors=4000; --非初始参数
sql> alter system set db_writer_processes=2 scope=spfile; --初始参数，设置后需要重启数据库
注：1、以上参考设置在数据库主机为4cpu双核，16g内存时的配置
2、标红的为初始参数，修改后需要重启数据库。
4.2杀死僵死连接
有时候由于客户端的异常退出，会出现一些连接的进程一直在运行，从而不能释放资源，影响系统的性能。可用以下方法实现把僵死的连接清除。
查询数据库死锁
select distinct *
from (select t2.username, t2.sid, t2.serial#, t2.logon_time
from v$locked_object t1, v$session t2
where t1.session_id = t2.sid)
order by logon_time;
查询出来的结果就是有死锁的session了，
alter system kill session 'sid,serial#';
一般情况可以解决数据库存在的死锁了，或通过session id 查到对应的操作系统进程，在unix中杀掉操作系统的进程。
select a.username, c.spid as os_process_id, c.pid as oracle_process_id
from v$session a, v$process c
where c.addr = a.paddr
and a.sid = m
and a.serial# = n;
采用kill （unix/linux）
kill -9 os_process_id
4.3统计信息失效导致执行计划走全表扫瞄
基于schema的统计信息收集
begin
dbms_stats.gather_schema_stats(ownname => 'tab_name',
estimate_percent => 20,
method_opt => 'for all indexed columns size 10',
cascade => true,
degree => 7);
end;

基于表的统计信息收集
analyze table hs_secu.entrust compute statistics;