Oracle数据库日常巡检有哪些

2025-03-12 技术教程

本篇内容介绍了“Oracle数据库日常巡检有哪些”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

1. 检查数据库基本状况

包含：检查Oracle实例状态，检查Oracle服务进程，检查Oracle监听进程，共三个部分。

1.1. 检查Oracle实例状态

selectinstance_name,host_name,startup_time,status,database_statusfromv$instance;

其中“STATUS”表示Oracle当前的实例状态，必须为“OPEN”；“DATABASE_STATUS”表示Oracle当前数据库的状态，必须为“ACTIVE”。

1.2. 检查Oracle在线日志状态

selectgroup#,status,type,memberfromv$logfile;

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

1.3. 检查Oracle表空间的状态

selecttablespace_name,statusfromdba_tablespaces;

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

1.4. 检查Oracle所有数据文件状态

selectname,statusfromv$datafile;

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

selectfile_name,statusfromdba_data_files;

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

1.5. 检查无效对象

selectowner,object_name,object_typefromdba_objectswherestatus!='VALID'andowner!='SYS'andowner!='SYSTEM';

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

SELECTowner,object_name,object_typeFROMdba_objectsWHEREstatus='INVALID';

1.6. 检查所有回滚段状态

selectsegment_name,statusfromdba_rollback_segs;

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

回到顶部

2. 检查Oracle相关资源的使用情况

包含：
a.检查Oracle初始化文件中相关的参数值
b.检查数据库连接情况，检查系统磁盘空间
c.检查Oracle各个表空间使用情况，检查一些扩展异常的对象，
d.检查system表空间内的内容，检查对象的下一扩展与表空间的最大扩展值，总共七个部分。

2.1. 检查Oracle初始化文件中相关参数值

selectresource_name,max_utilization,initial_allocation,limit_valuefromv$resource_limit;

若LIMIT_VALU-MAX_UTILIZATION<=5，则表明与RESOURCE_NAME相关的Oracle初始化参数需要调整。可以通过修改Oracle初始化参数文件$ORACLE_BASE/admin/CKDB/pfile/initORCL.ora来修改。

2.2. 检查数据库连接情况

查看当前会话连接数，是否属于正常范围。

selectcount(*)fromv$session;selectsid,serial#,username,program,machine,statusfromv$session;

其中：SID 会话(session)的ID号；

SERIAL# 会话的序列号，和SID一起用来唯一标识一个会话；

USERNAME 建立该会话的用户名；

PROGRAM 这个会话是用什么工具连接到数据库的；

STATUS 当前这个会话的状态，ACTIVE表示会话正在执行某些任务，INACTIVE表示当前会话没有执行任何操作；

如果建立了过多的连接，会消耗数据库的资源，同时，对一些“挂死”的连接可能需要手工进行清理。如果DBA要手工断开某个会话，则执行：

（一般不建议使用这种方式去杀掉数据库的连接，这样有时候session不会断开。容易引起死连接。建议通过sid查到操作系统的spid,使用ps –ef|grep spidno的方式确认spid不是ORACLE的后台进程。使用操作系统的kill -9命令杀掉连接）

alter system kill session 'SID,SERIAL#';

注意：上例中SID为1到10(USERNAME列为空)的会话，是Oracle的后台进程，不要对这些会话进行任何操作。

2.3. 检查系统磁盘空间

如果文件系统的剩余空间过小或增长较快，需对其进行确认并删除不用的文件以释放空间。

[oracle@AS14 ~]$ df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda5 9.7G 3.9G 5.4G 42% /

/dev/sda1 479M 16M 438M 4% /boot

/dev/sda2 49G 19G 28G 41% /data

none 1014M 0 1014M 0% /dev/shm

2.4. 检查表空间使用情况

selectf.tablespace_name,a.total,f.free,round((f.free/a.total)*100)"%Free"from(selecttablespace_name,sum(bytes/(1024*1024))totalfromdba_data_filesgroupbytablespace_name)a,(selecttablespace_name,round(sum(bytes/(1024*1024)))freefromdba_free_spacegroupbytablespace_name)fWHEREa.tablespace_name=f.tablespace_name(+)orderby"%Free";

如果空闲率%Free小于10%以上（包含10%），则注意要增加数据文件来扩展表空间而不要是用数据文件的自动扩展功能。请不要对表空间增加过多的数据文件，增加数据文件的原则是每个数据文件大小为2G或者4G，自动扩展的最大限制在8G。

2.5. 检查一些扩展异常的对象

selectSegment_Name,Segment_Type,TableSpace_Name,(Extents/Max_extents)*100PercentFromsys.DBA_SegmentsWhereMax_Extents!=0and(Extents/Max_extents)*100>=95orderByPercent;

如果有记录返回，则这些对象的扩展已经快达到它定义时的最大扩展值。对于这些对象要修改它的存储结构参数。

2.6. 检查system表空间内的内容

selectdistinct(owner)fromdba_tableswheretablespace_name='SYSTEM'andowner!='SYS'andowner!='SYSTEM'unionselectdistinct(owner)fromdba_indexeswheretablespace_name='SYSTEM'andowner!='SYS'andowner!='SYSTEM';

如果记录返回，则表明system表空间内存在一些非system和sys用户的对象。应该进一步检查这些对象是否与我们应用相关。如果相关请把这些对象移到非System表空间，同时应该检查这些对象属主的缺省表空间值。

2.7. 检查对象的下一扩展与表空间的最大扩展值

selecta.table_name,a.next_extent,a.tablespace_namefromall_tablesa,(selecttablespace_name,max(bytes)asbig_chunkfromdba_free_spacegroupbytablespace_name)fwheref.tablespace_name=a.tablespace_nameanda.next_extent>f.big_chunkunionselecta.index_name,a.next_extent,a.tablespace_namefromall_indexesa,(selecttablespace_name,max(bytes)asbig_chunkfromdba_free_spacegroupbytablespace_name)fwheref.tablespace_name=a.tablespace_nameanda.next_extent>f.big_chunk;

如果有记录返回，则表明这些对象的下一个扩展大于该对象所属表空间的最大扩展值，需调整相应表空间的存储参数。

回到顶部

3. 检查Oracle数据库备份结果

包含：
a.检查数据库备份日志信息;
b.检查backup卷中文件产生的时间;
c.检查oracle用户的email
3.1. 检查数据库备份日志信息

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

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

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

3.2. 检查backup卷中文件产生的时间

#ls –lt /backup/hotbackup

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

3.3. 检查oracle用户的email

#tail –n 300 /var/mail/oracle

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

回到顶部

4. 检查Oracle数据库性能

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

4.1. 检查数据库的等待事件

setpages80setlines120coleventfora40selectsid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAITfromv$session_waitwhereeventnotlike'SQL%'andeventnotlike'rdbms%';

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

4.2. Disk Read最高的SQL语句的获取

SELECTSQL_TEXTFROM(SELECT*FROMV$SQLAREAORDERBYDISK_READS)WHEREROWNUM<=5;

4.3. 查找前十条性能差的sql

SELECT*FROM(SELECTPARSING_USER_IDEXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,SQL_TEXTFROMV$SQLAREAORDERBYDISK_READSDESC)WHEREROWNUM<10;

4.4. 等待时间最多的5个系统等待事件的获取

SELECT*FROM(SELECT*FROMV$SYSTEM_EVENTWHEREEVENTNOTLIKE'SQL%'ORDERBYTOTAL_WAITSDESC)WHEREROWNUM<=5;

4.5. 检查运行很久的SQL

COLUMNUSERNAMEFORMATA12COLUMNOPNAMEFORMATA16COLUMNPROGRESSFORMATA8SELECTUSERNAME,SID,OPNAME,ROUND(SOFAR*100/TOTALWORK,0)||'%'ASPROGRESS,TIME_REMAINING,SQL_TEXTFROMV$SESSION_LONGOPS,V$SQLWHERETIME_REMAINING<>0ANDSQL_ADDRESS=ADDRESSANDSQL_HASH_VALUE=HASH_VALUE;

4.6. 检查消耗CPU最高的进程

SETLINE240SETVERIFYOFFCOLUMNSIDFORMAT999COLUMNPIDFORMAT999COLUMNS_#FORMAT999COLUMNUSERNAMEFORMATA9HEADING"ORAUSER"COLUMNPROGRAMFORMATA29COLUMNSQLFORMATA60COLUMNOSNAMEFORMATA9HEADING"OSUSER"SELECTP.PIDPID,S.SIDSID,P.SPIDSPID,S.USERNAMEUSERNAME,S.OSUSEROSNAME,P.SERIAL#S_#,P.TERMINAL,P.PROGRAMPROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT,1,80))SQLFROMV$PROCESSP,V$SESSIONS,V$SQLAREAAWHEREP.ADDR=S.PADDRANDS.SQL_ADDRESS=A.ADDRESS(+)ANDP.SPIDLIKE'%&1%';

4.7. 检查碎片程度高的表

SELECTsegment_nametable_name,COUNT(*)extentsFROMdba_segmentsWHEREownerNOTIN('SYS','SYSTEM')GROUPBYsegment_nameHAVINGCOUNT(*)=(SELECTMAX(COUNT(*))FROMdba_segmentsGROUPBYsegment_name);

4.8. 检查表空间的I/O比例

SELECTDF.TABLESPACE_NAMENAME,DF.FILE_NAME"FILE",F.PHYRDSPYR,F.PHYBLKRDPBR,F.PHYWRTSPYW,F.PHYBLKWRTPBWFROMV$FILESTATF,DBA_DATA_FILESDFWHEREF.FILE#=DF.FILE_IDORDERBYDF.TABLESPACE_NAME;

4.9. 检查文件系统的I/O比例

SELECTSUBSTR(A.FILE#,1,2)"#",SUBSTR(A.NAME,1,30)"NAME",A.STATUS,A.BYTES,B.PHYRDS,B.PHYWRTSFROMV$DATAFILEA,V$FILESTATBWHEREA.FILE#=B.FILE#;

4.10.检查死锁及处理

查询目前锁对象信息：

selectsid,serial#,username,SCHEMANAME,osuser,MACHINE,terminal,PROGRAM,owner,object_name,object_type,o.object_idfromdba_objectso,v$locked_objectl,v$sessionswhereo.object_id=l.object_idands.sid=l.session_id;

oracle级kill掉该session：

altersystemkillsession'&sid,&serial#';

操作系统级kill掉session：

#>kill -9 pid

回到顶部

5. 检查数据库cpu、I/O、内存性能

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

5.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使用率异常，需记录下该数值，并将状态记为异常。

5.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%时视为异常。

5.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等待情况。

5.4 系统负载情况：

#uptime

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

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

5.5.查看是否有僵死进程

selectspidfromv$processwhereaddrnotin(selectpaddrfromv$session);

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

5.6.检查行链接/迁移

selecttable_name,num_rows,chain_cntFromdba_tablesWhereowner='CTAIS2'Andchain_cnt<>0;

注：含有long raw列的表有行链接是正常的,找到迁移行保存到chained_rows表中,如没有该表执行../rdbms/admin/utlchain.sql

analyzetabletablenamelistchainedrows;

可通过表chained_rows中table_name,head_rowid看出哪些行是迁移行

createtableaaasselecta.*fromsb_zsxxa,chained_rowsbwherea.rowid=b.head_rowidandb.table_name='SB_ZSXX';deletefromsb_zsxxwhererowidin(selecthead_rowidfromchained_rowswheretable_name='SB_ZSXX');insertintosb_zsxxselect*fromchained_rowwheretable_name='SB_ZSXX';

5.7 定期做统计分析
对于采用Oracle Cost-Based-Optimizer的系统，需要定期对数据对象的统计信息进行采集更新，使优化器可以根据准备的信息作出正确的explain plan。在以下情况更需要进行统计信息的更新：
a. 应用发生变化
b. 大规模数据迁移、历史数据迁出、其他数据的导入等
c .数据量发生变化
查看表或索引的统计信息是否需更新，如：

Selecttable_name,num_rows,last_analyzedFromuser_tableswheretable_name='DJ_NSRXX'selectcount(*)fromDJ_NSRXX如num_rows和count(*)

如果行数相差很多,则该表需要更新统计信息，建议一周做一次统计信息收集，如：

execsys.dbms_stats.gather_schema_stats(ownname=>'CTAIS2',cascade=>TRUE,degree=>4);

5.8 检查缓冲区命中率

SELECTa.VALUE+b.VALUElogical_reads,c.VALUEphys_reads,round(100*(1-c.value/(a.value+b.value)),4)hit_ratioFROMv$sysstata,v$sysstatb,v$sysstatcWHEREa.NAME='dbblockgets'ANDb.NAME='consistentgets'ANDc.NAME='physicalreads';

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

5.9 检查共享池命中率

selectsum(pinhits)/sum(pins)*100fromv$librarycache;

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

5.10 检查排序区

selectname,valuefromv$sysstatwherenamelike'%sort%';

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

5.11 检查日志缓冲区

selectname,valuefromv$sysstatwherenamein('redoentries','redobufferallocationretries');

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

回到顶部

6. 检查数据库安全性

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

6.1. 检查系统安全日志信息

系统安全日志文件的目录在/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 ssh3……

检查登录失败的日志：

[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 ssh3

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

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

6.2. 检查用户修改密码

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

修改密码方法：

alteruserUSER_NAMEidentifiedbyPASSWORD;

回到顶部

7. 其他检查

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

7.1 检查当前crontab任务是否正常

[oracle@AS14 ~]$ crontab -l

7.2 Oracle Job是否有失败

selectjob,what,last_date,next_date,failures,brokenfromdba_jobsWhereschema_user='CAIKE';

如有问题建议重建job，如：

execsys.dbms_job.remove(1);commit;execsys.dbms_job.isubmit(1,'REFRESH_ALL_SNAPSHOT;',SYSDATE+1/1440,'SYSDATE+4/1440');commit;

7.3. 监控数据量的增长情况

selectA.tablespace_name,(1-(A.total)/B.total)*100used_percentfrom(selecttablespace_name,sum(bytes)totalfromdba_free_spacegroupbytablespace_name)A,(selecttablespace_name,sum(bytes)totalfromdba_data_filesgroupbytablespace_name)BwhereA.tablespace_name=B.tablespace_name;

根据本周每天的检查情况找到空间扩展很快的数据库对象,并采取相应的措施：

--删除历史数据
移动规定数据库中至少保留6个月的历史数据，所以以前的历史数据可以考虑备份然后进行清除以便释放其所占的资源空间。

---扩表空间

altertablespace<tablespace_name>adddatafile‘<file>’size<size>autoextendoff;

注意：在数据库结构发生变化时，如增加了表空间，增加了数据文件或重做日志文件这些操作，都会造成Oracle数据库控制文件的变化，DBA应及进行控制文件的备份，备份方法是：

执行SQL语句：

alterdatabasebackupcontrolfileto'/home/backup/control.bak';

或：

alterdatabasebackupcontrolfiletotrace;

这样，会在USER_DUMP_DEST(初始化参数文件中指定)目录下生成创建控制文件的SQL命令。

7.4. 检查失效的索引

selectindex_name,table_name,tablespace_name,statusFromdba_indexesWhereowner='CTAIS2'Andstatus<>'VALID';