您的位置：首页 > 博客中心 > 数据库 >

oracle 10g运维手册

时间：2022-03-10 17:49

巡检内容

在本节中主要对数据库的基本状况进行检查，其中包含：检查Oracle实例状态，检查Oracle服务进程，检查Oracle监听进程，共三个部分。

实例状态

问题

处理

启动参数不对

检查初始化参数文件

因为检查点操作或归档操作没有完成造成重做日志不能切换

如果经常发生这样的情况，可以考虑增加重做日志文件组；想办法提高检查点或归档操作的效率；

有人未经授权删除了表空间

检查数据库的安全问题，是否密码太简单；如有必要，撤消某些用户的系统权限

出现坏块

检查是否是硬件问题(如磁盘本生有坏块)，如果不是，检查是那个数据库对象出现了坏块，对这个对象进行重建

表空间不够

增加数据文件到相应的表空间

出现ORA-600

根据日志文件的内容查看相应的TRC文件，如果是Oracle的bug，要及时打上相应的补丁

Listener日志：$ORACLE_HOME/network/log

核心转储目录

$ls $ORACLE_BASE/admin/CKDB/cdump/*.trc|wc -l

$ls $ORACLE_BASE/admin/CKDB/udump/*.trc|wc –l

如果上面命令的结果每天都在增长，则说明Oracle进程经常发生核心转储。这说明某些用户进程或者数据库后台进程由于无法处理的原因而异常退出。频繁的核心转储特别是数据库后台进程的核心转储会导致数据库异常终止。

用户和Oracle用户的email

#tail –n 200 /var/mail/root

#tail –n 200 /var/mail/oracle

查看有无与Oracle用户相关的出错信息。

对象状态

在本节主要检查相关Oracle对象的状态，包含：检查Oracle控制文件状态，检查Oracle在线日志状态，检查Oracle表空间的状态，检查Oracle所有数据文件状态，检查Oracle所有表、索引、存储过程、触发器、包等对象的状态，检查Oracle所有回滚段的状态，总共六个部分。

控制文件状态

SQL> select status,name from v$controlfile;

STATUS NAME

------- --------------------------------------------------------------------------------

/data/oradata/CKDB/control01.ctl

/data/oradata/CKDB/control02.ctl

/data/oradata/CKDB/control03.ctl

输出结果应该有3条以上（包含3条）的记录，“STATUS”应该为空。状态为空表示控制文件状态正常。

在线日志状态

SQL> select group#,status,type,member from v$logfile;

GROUP# STATUS TYPE MEMBER

---------- ------- ------- -----------

3 ONLINE /data/oradata/CKDB/redo03.log

2 ONLINE /data/oradata/CKDB/redo02.log

1 ONLINE /data/oradata/CKDB/redo01.log

4 ONLINE /data/oradata/CKDB/redo04.log

5 ONLINE /data/oradata/CKDB/redo05.log

6 ONLINE /data/oradata/CKDB/redo06.log

6 rows selected

输出结果应该有3条以上（包含3条）记录，“STATUS”应该为非“INVALID”，非“DELETED”。注：“STATUS”显示为空表示正常。

表空间的状态

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

------------------------------ ---------

SYSTEM ONLINE

UNDOTBS1 ONLINE

SYSAUX ONLINE

TEMP ONLINE

USERS ONLINE

SJ1 ONLINE

ADM_INDEX ONLINE

HOME_DATA ONLINE

HOME_INDEX ONLINE

PHOTO_DATA ONLINE

PHOTO_INDEX ONLINE

。。。。。。。

输出结果中STATUS应该都为ONLINE。

所有数据文件状态

SQL> select name,status from v$datafile;

NAME STATUS

-------------------------------------------------- -------

/data/oradata/CKDB/system01.dbf SYSTEM

/data/oradata/CKDB/undotbs01.dbf ONLINE

/data/oradata/CKDB/sysaux01.dbf ONLINE

/data/oradata/CKDB/users01.dbf ONLINE

/data/oradata/CKDB/sj.dbf ONLINE

/data/oradata/CKDB/HOME_DATA1.dbf ONLINE

/data/oradata/CKDB/HOME_INDEX1.dbf ONLINE

/data/oradata/CKDB/PHOTO_DATA1.dbf ONLINE

/data/oradata/CKDB/PHOTO_INDEX1.dbf ONLINE

/data/oradata/CKDB/BLOG_DATA1.dbf ONLINE

/data/oradata/CKDB/BLOG_INDEX1.dbf ONLINE

/data/oradata/CKDB/AUDIO_DATA1.dbf ONLINE

/data/oradata/CKDB/AUDIO_INDEX1.dbf ONLINE

/data/oradata/CKDB/VIDEO_DATA1.dbf ONLINE

/data/oradata/CKDB/VIDEO_INDEX1.dbf ONLINE

/data/oradata/CKDB/SYS_DATA1.dbf ONLINE

/data/oradata/CKDB/SYS_INDEX1.dbf ONLINE

/data/oradata/CKDB/ADM_DATA1.dbf ONLINE

/data/oradata/CKDB/ADM_INDEX1.dbf ONLINE

/data/oradata/CKDB/perfstat.dbf ONLINE

输出结果中“STATUS”应该都为“ONLINE”。或者：

SQL> select file_name,status from dba_data_files;

FILE_NAME STATUS

--------------------------------------------- ---------

/data/oradata/CKDB/users01.dbf AVAILABLE

/data/oradata/CKDB/sysaux01.dbf AVAILABLE

/data/oradata/CKDB/undotbs01.dbf AVAILABLE

/data/oradata/CKDB/system01.dbf AVAILABLE

/data/oradata/CKDB/sj.dbf AVAILABLE

/data/oradata/CKDB/perfstat.dbf AVAILABLE

/data/oradata/CKDB/HOME_DATA1.dbf AVAILABLE

/data/oradata/CKDB/HOME_INDEX1.dbf AVAILABLE

/data/oradata/CKDB/PHOTO_DATA1.dbf AVAILABLE

输出结果中“STATUS”应该都为“AVAILABLE”。

sql>select owner,object_name,object_type from dba_objects where status!=‘VALID‘ and owner!=‘SYS‘ and owner!=‘SYSTEM‘;

no rows selected

如果有记录返回，则说明存在无效对象。若这些对象与应用相关，那么需要重新编译生成这个对象，或者：

SELECT owner, object_name, object_type FROM dba_objects WHERE status= ‘INVALID‘;

SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME STATUS

------------------------------ ----------------

SYSTEM ONLINE

_SYSSMU1$ ONLINE

_SYSSMU2$ ONLINE

_SYSSMU3$ ONLINE

_SYSSMU4$ ONLINE

_SYSSMU5$ ONLINE

_SYSSMU6$ ONLINE

_SYSSMU7$ ONLINE

_SYSSMU8$ ONLINE

_SYSSMU9$ ONLINE

_SYSSMU10$ ONLINE

11 rows selected

输出结果中所有回滚段的“STATUS”应该为“ONLINE”。

数据库备份结果

在本节主要检查Oracle数据库备份结果，包含：检查数据库备份日志信息，检查backup卷中文件产生的时间，检查oracle用户的email，总共三个部分。

假设：备份的临时目录为/backup/hotbakup，我们需要检查2009年7月22日的备份结果，则用下面的命令来检查：

#cat /backup/hotbackup/hotbackup-09-7-22.log|grep –i error

备份脚本的日志文件为hotbackup-月份-日期-年份.log，在备份的临时目录下面。如果文件中存在“ERROR:”，则表明备份没有成功，存在问题需要检查。

卷中文件产生的时间

#ls –lt /backup/hotbackup

backup卷是备份的临时目录，查看输出结果中文件的日期，都应当是在当天凌晨由热备份脚本产生的。如果时间不对则表明热备份脚本没执行成功。

用户的email

#tail –n 300 /var/mail/oracle

热备份脚本是通过Oracle用户的cron去执行的。cron执行完后操作系统就会发一条Email通知Oracle用户任务已经完成。查看Oracle email中今天凌晨部分有无ORA-，Error，Failed等出错信息，如果有则表明备份不正常。

数据库性能

在本节主要检查Oracle数据库性能情况，包含：检查数据库的等待事件，检查死锁及处理，检查cpu、I/O、内存性能，查看是否有僵死进程，检查行链接/迁移，定期做统计分析，检查缓冲区命中率，检查共享池命中率，检查排序区，检查日志缓冲区，总共十个部分。

set pages 80

set lines 120

col event for a40

select sid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like ‘SQL%‘ and event not like ‘rdbms%‘;

如果数据库长时间持续出现大量像latch free，enqueue，buffer busy waits，db file sequential read，db file scattered read等等待事件时，需要对其进行分析，可能存在问题的语句。

最高的SQL语句的获取

SQL>SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)

WHERE ROWNUM<=5 desc;

SELECT * FROM (SELECT PARSING_USER_ID

EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,

SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC)

WHERE ROWNUM<10 ;

个系统等待事件的获取

SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE ‘SQL%‘ ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;

COLUMN USERNAME FORMAT A12

COLUMN OPNAME FORMAT A16

COLUMN PROGRESS FORMAT A8

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;

最高的进程

SET LINE 240

SET VERIFY OFF

COLUMN SID FORMAT 999

COLUMN PID FORMAT 999

COLUMN S_# FORMAT 999

COLUMN USERNAME FORMAT A9 HEADING "ORA USER"

COLUMN PROGRAM FORMAT A29

COLUMN SQL FORMAT A60

COLUMN OSNAME FORMAT A9 HEADING "OS USER"

SELECT P.PID PID,S.SID SID,P.SPID SPID,S.USERNAME USERNAME,S.OSUSER OSNAME,P.SERIAL# S_#,P.TERMINAL,P.PROGRAM PROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT, 1, 80)) SQLFROM V$PROCESS P, V$SESSION S,V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS (+) AND P.SPID LIKE ‘%&1%‘;

SQL> 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);

比例

SQL>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;

比例

SQL>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#;

查询目前锁对象信息：

col sid for 999999

col username for a10

col schemaname for a10

col osuser for a16

col machine for a16

col terminal for a20

col owner for a10

col object_name for a30

col object_type for a10

select sid,serial#,username,SCHEMANAME,osuser,MACHINE,

terminal,PROGRAM,owner,object_name,object_type,o.object_id

from dba_objects o,v$locked_object l,v$session s

where o.object_id=l.object_id and s.sid=l.session_id;

oracle级kill掉该session：

alter system kill session ‘&sid,&serial#‘;

操作系统级kill掉session：

#>kill -9 pid

、I/O、内存性能

记录数据库的cpu使用、IO、内存等使用情况，使用vmstat,iostat,sar,top等命令进行信息收集并检查这些信息，判断资源使用情况。

1. CPU使用情况：

[root@sale8 ~]# top

top - 10:29:35 up 73 days, 19:54, 1 user, load average: 0.37, 0.38, 0.29

Tasks: 353 total, 2 running, 351 sleeping, 0 stopped, 0 zombie

Cpu(s): 1.2% us, 0.1% sy, 0.0% ni, 98.8% id, 0.0% wa, 0.0% hi, 0.0% si

Mem: 16404472k total, 12887428k used, 3517044k free, 60796k buffers

Swap: 8385920k total, 665576k used, 7720344k free, 10358384k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

30495 oracle 15 0 8329m 866m 861m R 10 5.4 7:53.90 oracle

32501 oracle 15 0 8328m 1.7g 1.7g S 2 10.6 1:58.38 oracle

32503 oracle 15 0 8329m 1.6g 1.6g S 2 10.2 2:06.62 oracle

。。。。。。。

注意上面的蓝色字体部分，此部分内容表示系统剩余的cpu，当其平均值下降至10%以下的时视为CPU使用率异常，需记录下该数值，并将状态记为异常。

2. 内存使用情况：

# free -m

total used free shared buffers cached

Mem: 2026 1958 67 0 76 1556

-/+ buffers/cache: 326 1700

Swap: 5992 92 5900

如上所示，蓝色部分表示系统总内存，红色部分表示系统使用的内存，黄色部分表示系统剩余内存，当剩余内存低于总内存的10%时视为异常。

3. 系统I/O情况：

# iostat -k 1 3

Linux 2.6.9-22.ELsmp (AS14) 07/29/2009

avg-cpu: %user %nice %sys %iowait %idle

0.16 0.00 0.05 0.36 99.43

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

sda 3.33 13.16 50.25 94483478 360665804

avg-cpu: %user %nice %sys %iowait %idle

0.00 0.00 0.00 0.00 100.00

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

sda 0.00 0.00 0.00 0 0

如上所示，蓝色字体部分表示磁盘读写情况，红色字体部分为cpu IO等待情况。

4. 系统负载情况：

#uptime

12:08:37 up 162 days, 23:33, 15 users, load average: 0.01, 0.15, 0.10

如上所示，蓝体字部分表示系统负载，后面的3个数值如果有高于2.5的时候就表明系统在超负荷运转了，并将此值记录到巡检表，视为异常。

select spid from v$process where addr not in (select paddr from v$session);

有些僵尸进程有阻塞其他业务的正常运行，定期杀掉僵尸进程。

迁移

Sql>select table_name,num_rows,chain_cnt From dba_tables Where owner=‘CTAIS2‘ And chain_cnt<>0;

注：含有long raw列的表有行链接是正常的,找到迁移行保存到chained_rows表中,如没有该表执行../rdbms/admin/utlchain.sql
Sql>analyze table tablename list chained rows;
可通过表chained_rows中table_name,head_rowid看出哪些行是迁移行
如:Sql>create table aa as select a.* from sb_zsxx a,chained_rows b where a.rowid=b.head_rowid and b.table_name =‘SB_ZSXX‘;
sql>delete from sb_zsxx where rowid in (select head_rowid from chained_rows where table_name = ‘SB_ZSXX‘);
sql>insert into sb_zsxx select * from chained_row where table_name = ‘SB_ZSXX‘;

对于采用Oracle Cost-Based-Optimizer的系统，需要定期对数据对象的统计信息进行采集更新，使优化器可以根据准备的信息作出正确的explain plan。在以下情况更需要进行统计信息的更新：
1、应用发生变化
2、大规模数据迁移、历史数据迁出、其他数据的导入等
3、数据量发生变化
查看表或索引的统计信息是否需更新，如：
Sql>Select table_name,num_rows,last_analyzed From user_tables where table_name =‘DJ_NSRXX‘
sql>select count(*) from DJ_NSRXX如num_rows和count(*)
如果行数相差很多,则该表需要更新统计信息，建议一周做一次统计信息收集，如：
Sql>exec sys.dbms_stats.gather_schema_stats(ownname=>‘CTAIS2‘,cascade => TRUE,degree => 4);

SQL> SELECT a.VALUE + b.VALUE logical_reads,

c.VALUE phys_reads,

round(100*(1-c.value/(a.value+b.value)),4) hit_ratio

FROM v$sysstat a,v$sysstat b,v$sysstat c

WHERE a.NAME=‘db block gets‘

AND b.NAME=‘consistent gets‘

AND c.NAME=‘physical reads‘ ;

LOGICAL_READS PHYS_READS HIT_RATIO

------------- ---------- ----------

1273645705 71191430 94.4104

如果命中率低于90% 则需加大数据库参数db_cache_size。

SQL> select sum(pinhits)/sum(pins)*100 from v$librarycache;

SUM(PINHITS)/SUM(PINS)*100

--------------------------

99.5294474716798

如低于95%，则需要调整应用程序使用绑定变量，或者调整数据库参数shared pool的大小。

SQL> select name,value from v$sysstat where name like ‘%sort%‘;

NAME VALUE

---------------------------------------------------------------- ----------

sorts (memory) 6135534

sorts (disk) 8

sorts (rows) 2264742084

如果disk/(memoty+row)的比例过高，则需要调整sort_area_size(workarea_size_policy=false)或pga_aggregate_target(workarea_size_policy=true)。

SQL> select name,value from v$sysstat where name in (‘redo entries‘,‘redo buffer allocation retries‘);

NAME VALUE

---------------------------------------------------------------- ----------

redo entries 27663705

redo buffer allocation retries 880

如果redo buffer allocation retries/redo entries 超过1% ，则需要增大log_buffer。

在本节主要检查Oracle数据库的安全性，包含：检查系统安全信息，定期修改密码，总共两个部分。

系统安全日志文件的目录在/var/log 下，主要检查登录成功或失败的用户日志信息。

检查登录成功的日志：

[root@rac2 ~]# grep -i accepted /var/log/secure

Jan 8 08:44:43 rac2 sshd[29559]: Accepted password for root from ::ffff:10.10.10.6 port 1119 ssh2……

检查登录失败的日志：

[root@rac2 ~]# grep -i inval /var/log/secure &&grep -i failed /var/log/secure

Jan 9 10:30:44 rac2 sshd[3071]: Invalid user ydbuser from ::ffff:192.168.3.5

Jan 9 10:30:56 rac2 sshd[3071]: Failed password for invalid user ydbuser from ::ffff:192.168.3.5 port 36005 ssh2

Jan 10 22:44:38 rac2 sshd[21611]: Failed password for root from ::ffff:10.10.10.6 port 1723 ssh2

在出现的日志信息中没有错误(Invalid、refused)提示，如果没有(Invalid、refused)视为系统正常，出现错误提示，应作出系统告警通知。

在数据库系统上往往存在很多的用户，如：第三方数据库监控系统，初始安装数据库时的演示用户，管理员用户等等，这些用户的密码往往是写定的，被很多人知道，会被别有用心的人利用来攻击系统甚至进行修改数据。需要修改密码的用户包括：
数据库管理员用户SYS，SYSTEM；其他用户。
登陆系统后，提示符下输入cat /etc/passwd，在列出来的用户中查看是否存在已经不再使用的或是陌生的帐号。若存在，则记录为异常。

修改密码方法：

Sql>alter user USER_NAME identified by PASSWORD;

在本节主要检查当前crontab任务是否正常，检查Oracle Job是否有失败等共六个部分。