oracle数据库日常维护（4）

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

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

1.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来修改。

1.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  建立该会话的用户名；

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的后台进程，不要对这些会话进行任何操作。

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

1.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。

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

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

1.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表空间，同时应该检查这些对象属主的缺省表空间值。

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

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