oracle 10g操作和维护手册

时间:2022-01-12 17:46:41

1.   
检查数据库基本状况...

1.1.    
检查Oracle实例状态...

1.2.    
检查Oracle服务进程...

1.3.    
检查Oracle监听状态...

2.   
检查系统和oracle日志文件...

2.1.    
检查操作系统日志文件...

2.2.    
检查oracle日志文件...

2.3.    
检查Oracle核心转储文件夹...

2.4.    
检查Root用户和Oracle用户的email.

3.   
检查Oracle对象状态...

3.1.    
检查Oracle控制文件状态...

3.2.    
检查Oracle在线日志状态...

3.3.    
检查Oracle表空间的状态...

3.4.    
检查Oracle全部数据文件状态...

3.5.    
检查无效对象...

3.6.    
检查全部回滚段状态...

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

4.1.    
检查Oracle初始化文件里相关參数值...

4.2.    
检查数据库连接情况...

4.3.    
检查系统磁盘空间...

4.4.    
检查表空间使用情况...

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

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

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

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

5.1.    
检查数据库备份日志信息...

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

5.3.    
检查oracle用户的email.

6.   
检查Oracle数据库性能...

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

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

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

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

6.5.    
检查执行非常久的SQL.

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

6.7.    
检查碎片程度高的表...

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

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

6.10.     
检查死锁及处理...

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

6.12.     
查看是否有僵死进程...

6.13.     
检查行链接/迁移...

6.14.     
定期做统计分析...

6.15.     
检查缓冲区命中率...

6.16.     
检查共享池命中率...

6.17.     
检查排序区...

6.18.     
检查日志缓冲区...

7.   
检查数据库安全性...

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

7.2.    
检查用户改动password...

8.   
其它检查...

8.1.    
检查当前crontab任务是否正常...

8.2.    
Oracle Job是否有失败...

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

8.4.    
检查失效的索引...

8.5.    
检查不起作用的约束...

8.6.    
检查无效的trigger.

巡检内容

1.    
检查数据库基本状况

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

1.1.   
检查Oracle
实例状态

SQL> select instance_name,host_name,startup_time,status,database_status from v$instance;

INSTANCE_NAME  HOST_NAME  STARTUP_TIME  STATUS    DATABASE_STATUS

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

CKDB             AS14          2009-5-7 9:3      OPEN        ACTIVE

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

SQL> select name,log_mode,open_mode from v$database;

NAME      LOG_MODE     OPEN_MODE

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

CKDB      ARCHIVELOG   READ WRITE

当中“LOG_MODE”表示Oracle当前的归档方式。“ARCHIVELOG”表示数据库执行在归档模式下。“NOARCHIVELOG”表示数据库执行在非归档模式下。在我们的系统中数据库必须执行在归档方式下。

1.2.   
检查Oracle
服务进程

$ps -ef|grep ora_|grep -v grep&&ps -ef|grep ora_|grep -v grep|wc –l

oracle    2960     1  0 May07 ?        00:01:02 ora_pmon_CKDB

oracle    2962     1  0 May07 ?        00:00:22 ora_psp0_CKDB

oracle    2964     1  0 May07 ?        00:00:00 ora_mman_CKDB

oracle    2966     1  0 May07 ?        00:03:20 ora_dbw0_CKDB

oracle    2968     1  0 May07 ?        00:04:29 ora_lgwr_CKDB

oracle    2970     1  0 May07 ?

00:10:31 ora_ckpt_CKDB

oracle    2972     1  0 May07 ?

00:03:45 ora_smon_CKDB

oracle    2974     1  0 May07 ?        00:00:00 ora_reco_CKDB

oracle    2976     1  0 May07 ?

00:01:24 ora_cjq0_CKDB

oracle    2978     1  0 May07 ?        00:06:17 ora_mmon_CKDB

oracle    2980     1  0 May07 ?        00:07:26 ora_mmnl_CKDB

oracle    2982     1  0 May07 ?        00:00:00 ora_d000_CKDB

oracle    2984     1  0 May07 ?

00:00:00 ora_s000_CKDB

oracle    2994     1  0 May07 ?        00:00:28 ora_arc0_CKDB

oracle    2996     1  0 May07 ?        00:00:29 ora_arc1_CKDB

oracle    3000     1  0 May07 ?        00:00:00 ora_qmnc_CKDB

oracle    3625     1  0 May07 ?        00:01:40 ora_q000_CKDB

oracle   31594     1  0 Jul20 ?        00:00:00 ora_q003_CKDB

oracle   23802     1  0 05:09 ?        00:00:33 ora_j000_CKDB

19

在检查Oracle的进程命令输出后,输出显示至少应包含下面一些进程:

. Oracle写数据文件的进程,输出显示为:“ora_dbw0_CKDB”


. Oracle写日志文件的进程,输出显示为:“ora_lgwr_ CKDB”


. Oracle监听实例状态的进程。输出显示为:“ora_smon_ CKDB”


. Oracle监听client连接进程状态的进程,输出显示为:“ora_pmon_ CKDB”


. Oracle进行归档的进程,输出显示为:“ora_arc0_ CKDB”


. Oracle进行检查点的进程,输出显示为:“ora_ckpt_ CKDB”


. Oracle进行恢复的进程,输出显示为:“ora_reco_ CKDB”

1.3.   
检查Oracle
监听状态

/home/oracle>lsnrctl status

LSNRCTL for Linux: Version 10.2.0.2.0 - Production on 23-JUL-2009 14:11:53

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 10.2.0.2.0 - Production

Start Date                07-MAY-2009 09:35:52

Uptime                    77 days 4 hr. 36 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /data/oracle/product/10.2.0/network/admin/listener.ora

Listener Log File         /data/oracle/product/10.2.0/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AS14)(PORT=1521)))

Services Summary...

Service "CKDB" has 1 instance(s).

Instance "CKDB", status READY, has 1 handler(s) for this service...

Service "CKDBXDB" has 1 instance(s).

Instance "CKDB", status READY, has 1 handler(s) for this service...

Service "CKDB_XPT" has 1 instance(s).

Instance "CKDB", status READY, has 1 handler(s) for this service...

The command completed successfully

“Services Summary”项表示Oracle的监听进程正在监听哪些数据库实例,输出显示中至少应该有“CKDB”这一项。

检查监听进程是否存在:

[oracle@AS14 ~]$  ps -ef|grep lsn|grep -v grep

oracle    2954     1  0 May07 ?        00:01:17 /data/oracle/product/10.2.0/bin/tnslsnr LISTENER –inherit

2.    
检查系统和oracle
日志文件

在本节主要检查相关的日志文件,包括:检查操作系统的日志文件,检查Oracle日志文件,检查Oracle核心转储文件夹,检查Root用户和Oracle用户的email。总共四个部分。

2.1.   
检查操作系统日志文件

# cat /var/log/messages |grep failed

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

2.2.   
检查oracle
日志文件

[oracle@AS14 ~]$ cat /data/oracle/admin/CKDB/bdump/alert_CKDB.log |grep ora-

[oracle@AS14 ~]$ cat /data/oracle/admin/CKDB/bdump/alert_CKDB.log |grep err

[oracle@AS14 ~]$ cat /data/oracle/admin/CKDB/bdump/alert_CKDB.log |grep fail

Oracle在执行过程中,会在警告日志文件(alert_SID.log)中记录数据库的一些执行情况:数据库的启动、关闭,启动时的非缺省參数;数据库的重做日志切换情况。记录每次切换的时间,及假设由于检查点(checkpoint)操作没有执行完毕造成不能切换,会记录不能切换的原因;对数据库进行的某些操作,如创建或删除表空间、添加数据文件。数据库发生的错误,如表空间不够、出现坏块、数据库内部错误(ORA-600)等。定期检查日志文件,依据日志中发现的问题及时进行处理:

问题

处理

启动參数不正确

检查初始化參数文件

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

假设常常发生这种情况,能够考虑添加重做日志文件组。想办法提高检查点或归档操作的效率。

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

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

出现坏块

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

表空间不够

添加数据文件到对应的表空间

出现ORA-600

依据日志文件的内容查看对应的TRC文件,假设是Oracle的bug,要及时打上对应的补丁

Listener日志:$ORACLE_HOME/network/log

2.3.   
检查Oracle
核心转储文件夹

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

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

假设上面命令的结果每天都在增长。则说明Oracle进程常常发生核心转储。这说明某些用户进程或者数据库后台进程因为无法处理的原因而异常退出。

频繁的核心转储特别是数据库后台进程的核心转储会导致数据库异常终止。

2.4.   
检查Root
用户和Oracle用户的email

#tail –n 200 /var/mail/root

#tail –n 200 /var/mail/oracle

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

3.    
检查Oracle
对象状态

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

3.1.   
检查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”应该为空。状态为空表示控制文件状态正常。

3.2.   
检查Oracle
在线日志状态

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”显示为空表示正常。

3.3.   
检查Oracle
表空间的状态

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。

3.4.   
检查Oracle
全部数据文件状态

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”。

3.5.   
检查无效对象

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

3.6.   
检查全部回滚段状态

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”。

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

在本节主要检查Oracle相关资源的使用情况,包括:检查Oracle初始化文件里相关的參数值,检查数据库连接情况。检查系统磁盘空间,检查Oracle各个表空间使用情况,检查一些扩展异常的对象,检查system表空间内的内容。检查对象的下一扩展与表空间的最大扩展值,总共七个部分。

4.1.   
检查Oracle
初始化文件里相关參数值

SQL> select resource_name,max_utilization,initial_allocation,

limit_value from v$resource_limit;

RESOURCE_NAME        MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE

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

processes                        162        500             500

sessions                         168        555             555

enqueue_locks                    136       6930            6930

enqueue_resources                111       2660       UNLIMITED

ges_procs                          0          0               0

ges_ress                           0          0       UNLIMITED

ges_locks                          0          0       UNLIMITED

ges_cache_ress                     0          0       UNLIMITED

ges_reg_msgs                       0          0       UNLIMITED

ges_big_msgs                       0          0       UNLIMITED

ges_rsv_msgs                       0          0               0

gcs_resources                      0          0               0

gcs_shadows                        0          0               0

dml_locks                         76       2440       UNLIMITED

temporary_table_locks              26  UNLIMITED       UNLIMITED

transactions                      13        610       UNLIMITED

branches                           0        610       UNLIMITED

cmtcallbk                          3        610       UNLIMITED

sort_segment_locks                 5  UNLIMITED       UNLIMITED

max_rollback_segments              11        610           65535

RESOURCE_NAME        MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE

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

max_shared_servers                 1  UNLIMITED       UNLIMITED

parallel_max_servers              16         80            3600

22 rows selected

若LIMIT_VALU-MAX_UTILIZATION<=5,则表明与RESOURCE_NAME相关的Oracle初始化參数须要调整。

能够通过改动Oracle初始化參数文件$ORACLE_BASE/admin/CKDB/pfile/initORCL.ora来改动。

4.2.   
检查数据库连接情况

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

SQL> select count(*) from v$session;

COUNT(*)

----------

29

select sid,serial#,username,program,machine,status from v$session;

SID    SERIAL#     USERNAME       PROGRAM        MACHINE      STATUS

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

1   3                       oracle@xz15saledb (PMON)     xz15saledb   ACTIVE

2   3                       oracle@xz15saledb (DBW0)     xz15saledb   ACTIVE

3   3                       oracle@xz15saledb (DBW1)     xz15saledb   ACTIVE

4   3                       oracle@xz15saledb (LGWR)     xz15saledb   ACTIVE

5   3                       oracle@xz15saledb (CKPT)     xz15saledb   ACTIVE

6   3                       oracle@xz15saledb (SMON)     xz15saledb   ACTIVE

7   3                       oracle@xz15saledb (RECO)     xz15saledb   ACTIVE

8    1                       oracle@xz15saledb (CJQ0)    xz15saledb   ACTIVE

9    3                       oracle@xz15saledb (ARC0)    xz15saledb   ACTIVE

10    3                       oracle@xz15saledb (ARC1)    xz15saledb   ACTIVE

11  11319    ZK   AccPrtInv_svr@xz15tuxedo2 (TNS V1-V3)   xz15tuxedo2  INACTIVE

13  48876    ZG           upload@xz15saleap (TNS V1-V3)   xz15saleap   INACTIVE

17  20405    ZK    AccCreateRpt@xz15tuxedo1 (TNS V1-V3)   xz15tuxedo1  INACTIVE

20  12895    ZK       OweScanSvr@xz15billdb (TNS V1-V3)   xz15billdb   INACTIVE

当中:SID 
会话(session)的ID号。

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

USERNAME  建立该会话的username;

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

STATUS  当前这个会话的状态,ACTIVE表示会话正在运行某些任务。INACTIVE表示当前会话没有运行不论什么操作;

假设建立了过多的连接。会消耗数据库的资源。同一时候,对一些“挂死”的连接可能须要手工进行清理。

假设DBA要手工断开某个会话,则运行:(一般不建议使用这样的方式去杀掉数据库的连接,这样有时候session不会断开。

easy引起死连接。建议通过sid查到操作系统的spid,使用ps
–ef|grep spidno的方式确认spid不是ORACLE的后台进程。使用操作系统的kill -9命令杀掉连接)

alter system kill session 'SID,SERIAL#';

注意:上例中SID为1到10(USERNAME列为空)的会话,是Oracle的后台进程。不要对这些会话进行不论什么操作。

4.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

4.4.   
检查表空间使用情况

SQL> select f.tablespace_name,a.total,f.free,round((f.free/a.total)*100) "% Free"

from

(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,

(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f

WHERE a.tablespace_name = f.tablespace_name(+)

order by "% Free";

TABLESPACE_NAME                     TOTAL       FREE     % Free

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

OPERATION_DATA                       1800        547         30

WAPWEB_DATA                           100         36         36

OPERATION_INDEX                       500        186         37

SYSTEM                               1024        515         50

SYSAUX                               1024        534         52

SALE8_TEMP                            100         62         62

SJ1                                   500        348         70

PERFSTAT                              500        356         71

……….

HOME_DATA                             100         77         77

SYS_INDEX                             100        100        100

VIDEO_INDEX                           100        100        100

VIDEO_DATA                            100        100        100

BLOG_DATA                             100        100        100

39 rows selected

假设空暇率%Free小于10%以上(包括10%),则注意要添加数据文件来扩展表空间而不要是用数据文件的自己主动扩展功能。请不要对表空间添加过多的数据文件,添加数据文件的原则是每一个数据文件大小为2G或者4G,自己主动扩展的最大限制在8G。

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

sql>select Segment_Name, Segment_Type, TableSpace_Name,

(Extents/Max_extents)*100 Percent

From sys.DBA_Segments

Where Max_Extents != 0 and (Extents/Max_extents)*100>=95

order By Percent;

no rows selected

假设有记录返回。则这些对象的扩展已经快达到它定义时的最大扩展值。对于这些对象要改动它的存储结构參数。

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

select distinct(owner) from dba_tables

where tablespace_name='SYSTEM' and

owner!='SYS' and owner!='SYSTEM'

union

select distinct(owner) from dba_indexes

where tablespace_name='SYSTEM' and

owner!='SYS' and owner!='SYSTEM';

no rows selected

假设记录返回,则表明system表空间内存在一些非system和sys用户的对象。

应该进一步检查这些对象是否与我们应用相关。假设相关请把这些对象移到非System表空间。同一时候应该检查这些对象属主的缺省表空间值。

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

sql>select a.table_name, a.next_extent, a.tablespace_name

from all_tables a,

(select tablespace_name, max(bytes) as big_chunk

from dba_free_space

group by tablespace_name ) f

where f.tablespace_name = a.tablespace_name

and a.next_extent > f.big_chunk

union

select a.index_name, a.next_extent, a.tablespace_name

from all_indexes a,

(select tablespace_name, max(bytes) as big_chunk

from dba_free_space

group by tablespace_name ) f

where f.tablespace_name = a.tablespace_name

and a.next_extent > f.big_chunk;

no rows selected

假设有记录返回,则表明这些对象的下一个扩展大于该对象所属表空间的最大扩展值。需调整对应表空间的存储參数。

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

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

5.1.   
检查数据库备份日志信息

如果:备份的暂时文件夹为/backup/hotbakup,我们须要检查2009年7月22日的备份结果,则用以下的命令来检查:

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

备份脚本的日志文件为hotbackup-月份-日期-年份.log。在备份的暂时文件夹以下。假设文件里存在“ERROR:”,则表明备份没有成功,存在问题须要检查。

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

#ls –lt /backup/hotbackup

backup卷是备份的暂时文件夹。查看输出结果中文件的日期,都应当是在当天凌晨由热备份脚本产生的。假设时间不正确则表明热备份脚本没运行成功。

5.3.   
检查oracle
用户的email

#tail –n 300 /var/mail/oracle

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

6.    
检查Oracle
数据库性能

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

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

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等等待事件时,须要对其进行分析,可能存在问题的语句。

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

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

WHERE ROWNUM<=5 desc;

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

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 ;

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

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

6.5.   
检查执行非常久的SQL

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;

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

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%';

6.7.   
检查碎片程度高的表

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

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

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;

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

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

6.10.
检查死锁及处理

查询眼下锁对象信息:

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

6.11.
检查数据库cpu
、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:       
       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的时候就表明系统在超负荷运转了,并将此值记录到巡检表,视为异常。

6.12.
查看是否有僵死进程

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

有些僵尸进程有堵塞其它业务的正常执行。定期杀掉僵尸进程。

6.13.
检查行链接/
迁移

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

6.14.
定期做统计分析

对于採用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);

6.15.
检查缓冲区命中率

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。

6.16.
检查共享池命中率

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

SUM(PINHITS)/SUM(PINS)*100

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

99.5294474716798

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

6.17.
检查排序区

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)。

6.18.
检查日志缓冲区

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。

7.    
检查数据库安全性

在本节主要检查Oracle数据库的安全性。包括:检查系统安全信息,定期改动password。总共两个部分。

7.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 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  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)视为系统正常,出现错误提示。应作出系统告警通知。

7.2.   
检查用户改动password

在数据库系统上往往存在非常多的用户,如:第三方数据库监控系统,初始安装数据库时的演示用户。管理员用户等等,这些用户的password往往是写定的。被非常多人知道。会被别实用心的人利用来攻击系统甚至进行改动数据。

须要改动password的用户包含:

数据库管理员用户SYS,SYSTEM;其它用户。

登陆系统后,提示符下输入cat /etc/passwd。在列出来的用户中查看是否存在已经不再使用的或是陌生的帐号。若存在。则记录为异常。

改动password方法:

Sql>alter user USER_NAME identified by PASSWORD;

8.    
其它检查

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

8.1.   
检查当前crontab
任务是否正常

[oracle@AS14 ~]$ crontab -l

8.2.   
Oracle Job
是否有失败

Sql>select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user='CAIKE';

如有问题建议重建job,如:

exec sys.dbms_job.remove(1);

commit;

exec sys.dbms_job.isubmit(1,'REFRESH_ALL_SNAPSHOT;',SYSDATE+1/1440,'SYSDATE+4/1440');

commit;

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

SQL> select

2  A.tablespace_name,(1-(A.total)/B.total)*100 used_percent

3  from (select tablespace_name,sum(bytes) total

4  from dba_free_space group by tablespace_name) A,

5  (select tablespace_name,sum(bytes) total

6  from dba_data_files group by tablespace_name) B

7  where A.tablespace_name=B.tablespace_name;

TABLESPACE_NAME                USED_PERCENT

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

HOME_INDEX                              1.5

BLOG_DATA                             0.375

VIDEO_DATA                             0.25

VIDEO_INDEX                            0.25

SYS_DATA                                9.5

SYS_INDEX                            0.4375

CURRENCY_INDEX                           13

UNDOTBS1                       2.3055555555

SYSAUX                         47.875976562

依据本周每天的检查情况找到空间扩展非常快的数据库对象,并採取对应的措施:

--- 删除历史数据

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

--- 扩表空间

alter tablespace <tablespace_name> add datafile ‘<file>’ size <size>  autoextend off;

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

运行SQL语句:

alter database backup controlfile to '/home/backup/control.bak';

或:

alter database backup controlfile to trace;

这样,会在USER_DUMP_DEST(初始化參数文件里指定)文件夹下生成创建控制文件的SQL命令。

8.4.   
检查失效的索引

Sql>select index_name,table_name,tablespace_name,status From dba_indexes Where owner='CTAIS2' And status<>'VALID';

注:分区表上的索引status为N/A是正常的,如有失效索引则对该索引做rebuild,如:

Sql>alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME;

8.5.   
检查不起作用的约束

SELECT owner, constraint_name, table_name, constraint_type, status

FROM dba_constraints

WHERE status ='DISABLE' and constraint_type='P';

如有失效约束则启用,如:

Sql>alter Table TABLE_NAME Enable Constraints CONSTRAINT_NAME;

8.6.   
检查无效的trigger

SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';

如有失效触发器则启用,如:

Sql>alter Trigger TRIGGER_NAME Enable;

版权声明:本文博客原创文章。博客,未经同意,不得转载。