|
|
检测CPU情况，1，5，15分钟的负载
[iyunv@rhel5 ~]# uptime
16:38:35 up 4 min, 1 user, load average: 0.30, 0.76, 0.38
检测内状况
[iyunv@rhel5 ~]# free -m
total used free shared buffers cached
Mem: 2026 368 1657 0 53 262
-/+ buffers/cache: 52 1973
Swap: 2031 0 2031
检测硬盘状况
[iyunv@rhel5 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 47G 8.1G 36G 19% /
/dev/sda1 99M 12M 83M 12% /boot
tmpfs 1014M 0 1014M 0% /dev/shm
通过vmstat命令检测系统
[iyunv@rhel5 ~]# vmstat 5 5
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 0 0 1697688 55196 268560 0 0 195 12 1021 63 1 4 94 1 0
0 0 0 1697688 55196 268560 0 0 0 0 1003 44 0 1 99 0 0
0 0 0 1697688 55196 268560 0 0 0 6 1008 48 0 1 99 0 0
0 0 0 1697688 55196 268560 0 0 0 0 1003 43 0 1 99 0 0
1 0 0 1697688 55196 268560 0 0 0 0 1009 48 0 1 99 0 0
Procs
r: The number of processes waiting for run time.
--展示了正在执行和等待CPU资源的任务个数。当这个值超过了CPU数目，就会出现CPU瓶颈了
b: The number of processes in uninterruptible sleep.
Memory
swpd: the amount of virtual memory used.
free: the amount of idle memory.
buff: the amount of memory used as buffers. -- 一般对块设备的读写才需要缓冲。
cache: the amount of memory used as cache. -- 一般作为文件系统的cache。
inact: the amount of inactive memory. (-a option)
active: the amount of active memory. (-a option)
Swap
si: Amount of memory swapped in from disk (/s).
so: Amount of memory swapped to disk (/s).
IO
bi: Blocks received from a block device (blocks/s).
bo: Blocks sent to a block device (blocks/s).
System
in: The number of interrupts per second, including the clock. -- 每秒的中断数，包括时钟中断
cs: The number of context switches per second. -- 每秒的环境（上下文）转换次数。
-- 上面这2个值越大，会看到由内核消耗的CPU时间会越多
CPU
These are percentages of total CPU time.
us: Time spent running non-kernel code. (user time, including nice time)
sy: Time spent running kernel code. (system time)
id: Time spent idle. Prior to Linux 2.5.41, this includes IO-wait time.
wa: Time spent waiting for IO. Prior to Linux 2.5.41, included in idle.
-- wa 的值高时，说明IO等待比较严重，这可能是由于磁盘大量作随机访问造成，也有可能是磁盘的带宽出现瓶颈(块操作)。
st: Time stolen from a virtual machine. Prior to Linux 2.6.11, unknown
通过iostat命令检测系统
[oracle@rhel5 trace]$ iostat
Linux 2.6.18-194.el5 (rhel5) 03/13/2013
avg-cpu: %user %nice %system %iowait %steal %idle
2.62 0.05 11.10 6.35 0.00 79.88
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 19.14 1729.82 92.71 604433 32394
sda1 0.23 5.95 0.03 2080 10
sda2 0.10 4.21 0.00 1471 0
sda3 18.75 1718.46 92.68 600466 32384
tps
Indicate the number of transfers per second that were issued to the device. A
transfer is an I/O request to the device. Multiple logical requests can be com-
bined into a single I/O request to the device. A transfer is of indeterminate
size.
检测Oracle死锁
[oracle@rhel5 trace]$ cat alert_orcl.log | grep ORA-00600
ORA-00600: internal error code, arguments: [ksl_invalid_latch], [kslges], [0x1437EDC], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [ksl_invalid_latch], [kslges], [0x1437EDC], [], [], [], [], [], [], [], [], []
Oracle实例状态
SYS@ orcl> select instance_name, version, status, database_status from v$instance;
INSTANCE_NAME VERSION STATUS DATABASE_STATUS
---------------- ----------------- ------------ -----------------
orcl 11.2.0.1.0 OPEN ACTIVE
Oracle数据库状态
SYS@ orcl> select name, log_mode, open_mode, flashback_on from v$database;
NAME LOG_MODE OPEN_MODE FLASHBACK_ON
--------- ------------ -------------------- ------------------
ORCL ARCHIVELOG READ WRITE NO
检查数据库进程
[oracle@rhel5 trace]$ ps -ef | grep ora_ | grep -v grep | wc -l
28
SYS@ orcl> show parameter processes
processes integer 200
检查数据库的会话数
SYS@ orcl> show parameter sessions;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sessions integer 322
SYS@ orcl> select count(*) from v$session;
COUNT(*)
----------
27
一个稳定运行的数据库里，会话数量应保持平稳，如果出现会话数量大幅增加或大幅减少，就意味着可能出现了问题，需要进一步查找原因。（需要与日常稳定数值对比）
SYS@ orcl> select count(*) from v$session where lockwait is not null;
COUNT(*)
----------
0
检查数据文件状态
SYS@ orcl> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- ------------------------------ --- ---------- --------------
/oradata/orcl/control01.ctl NO 16384 594
/oradata/orcl/control02.ctl NO 16384 594
STATUE为INVALED为异常，NULL为正常
检查日志文件
SYS@ orcl> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
3 ONLINE /oradata/orcl/redo03.log NO
2 ONLINE /oradata/orcl/redo02.log NO
1 ONLINE /oradata/orcl/redo01.log NO
重做日志文件是oracle数据库的重要组成部分，通过检查重做日志文件可以及时发现重做日志文件出现的损坏等问题。STATUS为NULL，TYPE为ONLINE为正常
检查表空间
TABLESPACE_NAME CONTENTS STATUS
------------------------------ --------- ---------
SYSTEM PERMANENT ONLINE
SYSAUX PERMANENT ONLINE
UNDOTBS1 UNDO ONLINE
TEMP TEMPORARY ONLINE
USERS PERMANENT ONLINE
检查数据文件
SYS@ orcl> select name, status from v$datafile;
NAME STATUS
---------------------------------------- -------
/oradata/orcl/system01.dbf SYSTEM
/oradata/orcl/sysaux01.dbf ONLINE
/oradata/orcl/undotbs01.dbf ONLINE
/oradata/orcl/users01.dbf ONLINE
STATUS有可能是OFFLINE，RECOVER等状态。
检查回滚段
SYS@ orcl> select segment_name, status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU10_4131489474$ ONLINE
_SYSSMU9_1735643689$ ONLINE
_SYSSMU8_3901294357$ ONLINE
_SYSSMU7_3517345427$ ONLINE
_SYSSMU6_2897970769$ ONLINE
_SYSSMU5_538557934$ ONLINE
_SYSSMU4_1003442803$ ONLINE
_SYSSMU3_1204390606$ ONLINE
_SYSSMU2_967517682$ ONLINE
_SYSSMU1_592353410$ ONLINE
检查数据库的无效对象
SYS@ orcl> col object_name for a20
SYS@ orcl> col owner for a15
SYS@ orcl> select owner, object_name, object_type, status from dba_objects where status != 'VALID' and owner != 'SYS' and owner != 'SYSTEM';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
--------------- -------------------- ------------------- -------
SCOTT USERLIST PROCEDURE INVALID
可以用 Oracle 提供的工具：dbms_utility.compile_schema(schema varchar2， compile_all boolean default TRUE)来编译某个 Schema 下的所有 PROCEDURE、FUNCTION、PACKAGE 和TRIGGER.比如执行 dbms_utility.compile_schema（'Unmi'）。 $ORACLE_HOME/rdbms/admin/utlirp.sql这个可以全部编译所有函数和package
检查系统资源限制
SYS@ orcl> select * from v$resource_limit;
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
------------------------------ ------------------- --------------- -------------------- -----------
processes 29 34 500 500
sessions 35 43 772 772
enqueue_locks 15 29 8960 8960
enqueue_resources 14 37 3616 UNLIMITED
ges_procs 0 0 0 0
ges_ress 0 0 0 UNLIMITED
ges_locks 0 0 0 UNLIMITED
ges_cache_ress 0 0 0 UNLIMITED
ges_reg_msgs 0 0 0 UNLIMITED
ges_big_msgs 0 0 0 UNLIMITED
ges_rsv_msgs 0 0 0 0
gcs_resources 0 0 0 0
gcs_shadows 0 0 0 0
dml_locks 0 0 3396 UNLIMITED
temporary_table_locks 0 0 UNLIMITED UNLIMITED
transactions 0 0 849 UNLIMITED
branches 0 0 849 UNLIMITED
cmtcallbk 0 1 849 UNLIMITED
max_rollback_segments 11 11 849 65535
sort_segment_locks 0 1 UNLIMITED UNLIMITED
k2q_locks 0 0 1544 UNLIMITED
max_shared_servers 1 1 UNLIMITED UNLIMITED
parallel_max_servers 0 0 10 3600
检查数据文件的autoextensible
SYS@ orcl> select tablespace_name, file_id, ONLINE_STATUS, autoextensible from dba_data_files union all select tablespace_name, file_id, status, autoextensible from dba_temp_files;
TABLESPACE_NAME FILE_ID ONLINE_ AUT
------------------------------ ---------- ------- ---
USERS 4 ONLINE YES
UNDOTBS1 3 ONLINE YES
SYSAUX 2 ONLINE YES
SYSTEM 1 SYSTEM YES
TEMP 1 ONLINE YES
检查Oracle表空间使用情况
/* OracleOEM */
SELECT d.status "Status",
d.tablespace_name "Name",
d.contents "Type",
d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.900') "Size (M)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') || '/' ||
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99999999.999') "Used (M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status "Status",
d.tablespace_name "Name",
d.contents "Type",
d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.900') "Size (M)",
TO_CHAR(NVL(t.bytes, 0) / 1024 / 1024, '99999999.999') || '/' ||
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99999999.999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes
from v$temp_extent_pool
group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY'
/
Status Name Type Extent Man Size (M) Used (M) Used %
--------- --------------- --------- ---------- --------------- --------------------------- -------
ONLINE SYSAUX PERMANENT LOCAL 550.000 511.688/ 550.000 93.03
ONLINE UNDOTBS1 UNDO LOCAL 75.000 42.500/ 75.000 56.67
ONLINE USERS PERMANENT LOCAL 5.000 1.438/ 5.000 28.75
ONLINE SYSTEM PERMANENT LOCAL 680.000 677.000/ 680.000 99.56
ONLINE TEMP TEMPORARY LOCAL 29.000 28.000/ 29.000 96.55
Oracle数据库的数据是存放在表空间里，如果表空间剩余空间不足，数据库无法继续写入数据，数据库将报错。因此，及时检查表空间使用情况，确保表空间剩余空间维持在20%以上，对数据库的正常稳定运行来说具有重要意义。
检查Oracle扩展异常对象
SYS@ orcl> select segment_name, segment_type, tablespace_name, (extents/max_extents)*100 percent from dba_segments where max_extents !=0 and (extents/max_extents)*100 >=90 order by percent;
no rows selected
数据库中每个segment是由extent组成，而每个segment所能容纳的extent数量是有限制的，dba_segments中的max_extents列就是每个segment所能容纳的最大extent数量。如果segment中的extent数达到了这个数量的限制，则segment将无法继续扩展，数据库将报错。因此，通过检查每个segment中的extent数量，可以及时发现数据库中扩展异常的对象，以便于采取进一步参数，避免出现segment无法扩展的问题出现。（无返回值，状态正常）。
检查Oracle系统表空间
select distinct(owner) from dba_tables where tablespace_name = 'SYSTEM' and owner != 'SYS' and owner != 'SYSTEM'
union all
select distinct(owner) from dba_indexes where tablespace_name = 'SYSTEM' and owner != 'SYS' and owner != 'SYSTEM'
OWNER
---------------
OUTLN
MDSYS
OUTLN
OLAPSYS
ORDDATA
racle系统表空间一般是用于存放sys和system用户数据的，通常其它用户的数据是不能存放在系统表空间中，通过检查这项内容，可以发现有哪些非sys和system用户的数据被存放在系统表空间里，以防止其存储空间被过度占用而引起数据库问题。（此类用户属于内部用户，状态正常）
除了上述基本检测外，还需要检测每日的RMAN备份情况（全备或者增量备份），逻辑备份情况（EXPDP或EXP），并生成statspack与AWR报告，对数据库进行具体
|
|
QQ群⑧：
窥视卡
雷达卡
发表于 2013-3-15 08:53:25
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡