Oracle日常巡检
一、操作系统
1.查看IP地址: ifconfig –a
2.查看文件系统使用情况 df –k
参考:文件系统使用率大于85%需要注意,提醒客户是否需要扩文件系统或者删除不需要的文件释放空间。
3.查看CPU、内存使用情况: vmstat 2 3
4.查看CPU使用情况 sar -u 2 3
5.查看IO使用情况 Iostat 2 3
参考:hdisk繁忙大于85%需要关注。
二、数据库层面:
1.查看数据库版本 select * from v$version;
2.查看数据库实例
select instance_number,instance_name from v$instance;
3.临时表空间数据文件情况: col file_name format a30
select file_name,tablespace_name,bytes/1024/1024 MB,autoextensible from dba_temp_files;
select name,log_mode,open_mode from v$database; archive log list
select tablespace_name,sum(bytes/1024/1024) from dba_data_files group by tablespace_name;
3.临时表空间使用情况:
select se.username,se.sid,se.serial#,su.extents,su.blocks*to_number(rtrim(p.value)) as space,tablespace,segtype,sql_text
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by se.username,se.sid;
4.表空间剩余大小(小余15%需要关注).
select e.tablespace_name "TABLESPACE",round(e.TOTAL,0) "TOTAL(MB)",round(f.FREE,0) "FREE(MB)",round(f.FREE/e.TOTAL*100,0) "FREEPER"
from (select tablespace_name,sum(bytes)/1024/1024 "TOTAL" from dba_data_files group by tablespace_name) e,(select tablespace_name,sum(bytes)/1024/1024 "FREE" from dba_free_space group by tablespace_name) f
where e.tablespace_name=f.tablespace_name order by "FREEPER";
5.查看SGA/PGA参数: show parameter sga_; show parameter pga_; show parameter processes;
show parameter dump_dest
注:background_dump_dest后台日志.
6.查看数据库用户默认表空间:(个人用户不要放在系统表空间):
select username,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users;
7.查看redolog group信息: set linesize 300
col member format a60 select * from v$logfile; select * from v$log;
三、查看数据库后台日志
1、通过show parameter dump_dest查看background_dump_dest后台日志路径 然后登陆到该路径,使用以下命令查看后台日志: 1) SUN系统:
$tail -200 *.log|more 查看最新200行日志。 2)、aix系统
$tail –n 200 *.log|more 查看最新200行日志。 注:
查看日志是否有warning和error报错,如果有则需要关注。
2、查看监听日志
$ cd $ORACLE_HOME/network/log tail -200 listener.log|more
select * from v$version;
select instance_number,instance_name from v$instance; col file_name format a30
select file_name,tablespace_name,bytes/1024/1024 MB,autoextensible from dba_temp_files; select name,log_mode,open_mode from v$database; archive log list
select tablespace_name,sum(bytes/1024/1024) from dba_data_files group by tablespace_name; select se.username,se.sid,se.serial#,su.extents,su.blocks*to_number(rtrim(p.value)) as space,tablespace,segtype,sql_text
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by se.username,se.sid;
select e.tablespace_name "TABLESPACE",round(e.TOTAL,0) "TOTAL(MB)",round(f.FREE,0) "FREE(MB)",round(f.FREE/e.TOTAL*100,0) "FREEPER"
from (select tablespace_name,sum(bytes)/1024/1024 "TOTAL" from dba_data_files group by tablespace_name) e,(select tablespace_name,sum(bytes)/1024/1024 "FREE" from dba_free_space group by tablespace_name) f
where e.tablespace_name=f.tablespace_name order by "FREEPER"; show parameter sga_; show parameter pga_; show parameter processes;
select username,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users; set linesize 300
col member format a60 select * from v$logfile; select * from v$log;