Oracle数据库巡检(二)
检测
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_351734542