003.日常维护手册？

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_T STATUS       DATABASE_STATUS
-------------- -------------------- --------- ------------ -----------------
orcl           company-vpnserver    07-OCT-21 OPEN         ACTIVE

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

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

NAME      LOG_MODE     OPEN_MODE
--------- ------------ --------------------
ORCL      NOARCHIVELOG READ WRITE

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

1.2.检查Oracle服务进程

[oracle@company-vpnserver ~]$ ps -ef|grep ora_|grep -v grep&&ps -ef|grep ora_|grep -v grep|wc -l
oracle    12340      1  0 Oct08 ?        00:00:47 ora_pmon_orcl
oracle    12342      1  0 Oct08 ?        00:00:55 ora_psp0_orcl
oracle    12344      1  1 Oct08 ?        01:52:25 ora_vktm_orcl
oracle    12349      1  0 Oct08 ?        00:00:09 ora_gen0_orcl
oracle    12351      1  0 Oct08 ?        00:00:16 ora_diag_orcl
oracle    12353      1  0 Oct08 ?        00:00:15 ora_dbrm_orcl
oracle    12355      1  0 Oct08 ?        00:02:48 ora_dia0_orcl
oracle    12357      1  0 Oct08 ?        00:00:09 ora_mman_orcl
oracle    12359      1  0 Oct08 ?        00:00:21 ora_dbw0_orcl
oracle    12361      1  0 Oct08 ?        00:00:14 ora_lgwr_orcl
oracle    12363      1  0 Oct08 ?        00:01:09 ora_ckpt_orcl
oracle    12365      1  0 Oct08 ?        00:00:16 ora_smon_orcl
oracle    12367      1  0 Oct08 ?        00:00:03 ora_reco_orcl
oracle    12369      1  0 Oct08 ?        00:01:47 ora_mmon_orcl
oracle    12371      1  0 Oct08 ?        00:01:26 ora_mmnl_orcl
oracle    12373      1  0 Oct08 ?        00:00:04 ora_d000_orcl
oracle    12375      1  0 Oct08 ?        00:00:03 ora_s000_orcl
oracle    12388      1  0 Oct08 ?        00:00:04 ora_qmnc_orcl
oracle    12404      1  0 Oct08 ?        00:00:50 ora_cjq0_orcl
oracle    12414      1  0 Oct08 ?        00:00:05 ora_q000_orcl
oracle    12416      1  0 Oct08 ?        00:00:03 ora_q001_orcl
oracle    12599      1  0 Oct08 ?        00:00:13 ora_smco_orcl
oracle    48381      1  0 14:32 ?        00:00:00 ora_w000_orcl
23

在检查Oracle的进程命令输出后，输出显示至少应包括以下一些进程：

Oracle写数据文件的进程，输出显示为：“ora_dbw0_CKDB”
Oracle写日志文件的进程，输出显示为：“ora_lgwr_ CKDB”
Oracle监听实例状态的进程，输出显示为：“ora_smon_ CKDB”
Oracle监听客户端连接进程状态的进程，输出显示为：“ora_pmon_ CKDB”
Oracle进行归档的进程，输出显示为：“ora_arc0_ CKDB”
Oracle进行检查点的进程，输出显示为：“ora_ckpt_ CKDB”
Oracle进行恢复的进程，输出显示为：“ora_reco_ CKDB”

1.3.检查Oracle监听状态

[oracle@company-vpnserver ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 12-OCT-2021 14:52:23
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                07-OCT-2021 23:10:20
Uptime                    4 days 3 hr. 42 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /oracle/app/oracle/diag/tnslsnr/company-vpnserver/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=company-vpnserver)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

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

检查监听进程是否存在：

[oracle@company-vpnserver ~]$ ps -ef|grep lsn|grep -v grep
oracle    11432      1  0 Oct08 ?        00:00:15 /oracle/app/oracle/product/11.2.0/db_1/bin/tnslsnr LISTENER -inherit

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

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

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

# cat /var/log/messages |grep failed

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

2.2.检查oracle日志文件

$cat /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log  |grep ORA-
$cat /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log  |grep err
$cat /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log  |grep fail

Oracle在运行过程中，会在警告日志文件(alert_SID.log)中记录数据库的一些运行情况：

.数据库的启动、关闭，启动时的非缺省参数；

.数据库的重做日志切换情况，记录每次切换的时间，及如果因为检查点(checkpoint)操作没有执行完成造成不能切换，会记录不能切换的原因；

.对数据库进行的某些操作，如创建或删除表空间、增加数据文件；

.数据库发生的错误，如表空间不够、出现坏块、数据库内部错误(ORA－600)等。

定期检查日志文件，根据日志中发现的问题及时进行处理：

问题	处理
启动参数不对	检查初始化参数文件
因为检查点操作或归档操作没有完成造成重做日志不能切换	如果经常发生这样的情况，可以考虑增加重做日志文件组；想办法提高检查点或归档操作的效率；
有人未经授权删除了表空间	检查数据库的安全问题，是否密码太简单；如有必要，撤消某些用户的系统权限
出现坏块	检查是否是硬件问题(如磁盘本生有坏块)，如果不是，检查是那个数据库对象出现了坏块，对这个对象进行重建
表空间不够	增加数据文件到相应的表空间
出现ORA-600	根据日志文件的内容查看相应的TRC文件，如果是Oracle的bug，要及时打上相应的补丁

2.3.检查Oracle核心转储目录

$ ls /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/*.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
------- --------------------------------------------------------------------------------
        /oracle/app/oracle/oradata/orcl/control01.ctl
        /oracle/app/oracle/fast_recovery_area/orcl/control02.ctl

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

3.2.检查Oracle在线日志状态

SQL> select group#,status,type,member from v$logfile;
    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ------------------------------------------------------------
         3         ONLINE  /oracle/app/oracle/oradata/orcl/redo03.log
         2         ONLINE  /oracle/app/oracle/oradata/orcl/redo02.log
         1         ONLINE  /oracle/app/oracle/oradata/orcl/redo01.log

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

3.3.检查Oracle表空间的状态

SQL> select tablespace_name,status from dba_tablespaces;


TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE

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

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

SQL> select name,status from v$datafile;
NAME                                             STATUS
------------------------------------------------ -------
/oracle/app/oracle/oradata/orcl/system01.dbf     SYSTEM
/oracle/app/oracle/oradata/orcl/sysaux01.dbf     ONLINE
/oracle/app/oracle/oradata/orcl/undotbs01.dbf    ONLINE
/oracle/app/oracle/oradata/orcl/users01.dbf      ONLINE

除系统表空间外，输出结果中“STATUS”应该都为“ONLINE”。或者：

SQL> select file_name,status from dba_data_files;
FILE_NAME                                          STATUS
-------------------------------------------------- ---------
/oracle/app/oracle/oradata/orcl/users01.dbf        AVAILABLE
/oracle/app/oracle/oradata/orcl/undotbs01.dbf      AVAILABLE
/oracle/app/oracle/oradata/orcl/sysaux01.dbf       AVAILABLE
/oracle/app/oracle/oradata/orcl/system01.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
_SYSSMU10_1197734989$          ONLINE
_SYSSMU9_1650507775$           ONLINE
_SYSSMU8_517538920$            ONLINE
_SYSSMU7_2070203016$           ONLINE
_SYSSMU6_1263032392$           ONLINE
_SYSSMU5_898567397$            ONLINE
_SYSSMU4_1254879796$           ONLINE
_SYSSMU3_1723003836$           ONLINE
_SYSSMU2_2996391332$           ONLINE
_SYSSMU1_3724004606$           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_ALLOCATION   LIMIT_VALUE
------------------------------ --------------- -------------------- --------------------
processes                                   40        150                  150
sessions                                    53        248                  248
enqueue_locks                               35       3160                 3160
enqueue_resources                           46       1308            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  UNLIMITED            UNLIMITED
gcs_shadows                                  0  UNLIMITED            UNLIMITED
smartio_overhead_memory                  71704          0            UNLIMITED
smartio_buffer_memory                        0          0            UNLIMITED
smartio_metadata_memory                      0          0            UNLIMITED
smartio_sessions                             1          0            UNLIMITED
dml_locks                                   24       1088            UNLIMITED
temporary_table_locks                       37  UNLIMITED            UNLIMITED
transactions                                 4        272            UNLIMITED
branches                                     0        272            UNLIMITED
cmtcallbk                                    4        272            UNLIMITED
max_rollback_segments                       11        272                65535
sort_segment_locks                           7  UNLIMITED            UNLIMITED
k2q_locks                                    0        496            UNLIMITED
max_shared_servers                           1  UNLIMITED            UNLIMITED
parallel_max_servers                         0         80                 3600

27 rows selected.

若LIMIT_VALUE-MAX_UTILIZATION<=5，则表明与RESOURCE_NAME相关的Oracle初始化参数需要调整。可以通过修改Oracle初始化参数文件$ORACLE_HOME/dbs/initORCL.ora来修改或者通过以下方式来更改

创建参数文件,集群环境切记不能直接**create pfile from spfile;**,这样修改的参数会导致节点间的参数值不一致而出现问题。

create pfile='/home/oracle/pfile.ora' from spfile

vi 修改/home/oracle/pfile.ora要改的参数
重新创建spfile文件

create spfile from pfile='/home/oracle/pfile.ora'

4.2.检查数据库连接情况

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

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

  COUNT(*)
----------
        23

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

       SID    SERIAL# USERNAME     PROGRAM                                          MACHINE              STATUS
---------- ---------- ------------ ------------------------------------------------ -------------------- --------
         1          1              oracle@company-vpnserver (PMON)                  company-vpnserver    ACTIVE
         2          1              oracle@company-vpnserver (VKTM)                  company-vpnserver    ACTIVE
         3          1              oracle@company-vpnserver (DIAG)                  company-vpnserver    ACTIVE
         4          1              oracle@company-vpnserver (DIA0)                  company-vpnserver    ACTIVE
         5          1              oracle@company-vpnserver (DBW0)                  company-vpnserver    ACTIVE
         6          1              oracle@company-vpnserver (CKPT)                  company-vpnserver    ACTIVE
         7          1              oracle@company-vpnserver (RECO)                  company-vpnserver    ACTIVE
         8          1              oracle@company-vpnserver (MMNL)                  company-vpnserver    ACTIVE
         9          3              oracle@company-vpnserver (QMNC)                  company-vpnserver    ACTIVE
        11          3              oracle@company-vpnserver (CJQ0)                  company-vpnserver    ACTIVE
        17        953 SYS          sqlplus@company-vpnserver (TNS V1-V3)            company-vpnserver    ACTIVE
        22       1299 DBCHECK      SQL Developer                                    DESKTOP-VI1TH5A      INACTIVE
       125         11              oracle@company-vpnserver (Q000)                  company-vpnserver    ACTIVE
       126          1              oracle@company-vpnserver (PSP0)                  company-vpnserver    ACTIVE
       127          1              oracle@company-vpnserver (GEN0)                  company-vpnserver    ACTIVE
       128          1              oracle@company-vpnserver (DBRM)                  company-vpnserver    ACTIVE
       129          1              oracle@company-vpnserver (MMAN)                  company-vpnserver    ACTIVE
       130          1              oracle@company-vpnserver (LGWR)                  company-vpnserver    ACTIVE
       131          1              oracle@company-vpnserver (SMON)                  company-vpnserver    ACTIVE
       132          1              oracle@company-vpnserver (MMON)                  company-vpnserver    ACTIVE
       136          9              oracle@company-vpnserver (Q001)                  company-vpnserver    ACTIVE
       139          5              oracle@company-vpnserver (SMCO)                  company-vpnserver    ACTIVE
       144       1503              oracle@company-vpnserver (W000)                  company-vpnserver    ACTIVE

23 rows selected.

其中：

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

4.3.检查系统磁盘空间

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

[oracle@company-vpnserver ~]$ df  -h
Filesystem             Size  Used Avail Use% Mounted on
/dev/mapper/rhel-root   95G   14G   82G  15% /
devtmpfs               2.0G     0  2.0G   0% /dev
tmpfs                  2.0G  876M  1.1G  45% /dev/shm
tmpfs                  2.0G  9.0M  2.0G   1% /run
tmpfs                  2.0G     0  2.0G   0% /sys/fs/cgroup
/dev/sda1             1014M  133M  882M  14% /boot
/dev/sda2              4.0G   41M  4.0G   1% /home
tmpfs                  396M     0  396M   0% /run/user/0
/dev/sr0               4.2G  4.2G     0 100% /media/cdrom
tmpfs                  396M     0  396M   0% /run/user/1001

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
------------------------------ ---------- ---------- ----------
SYSTEM                                750          6          1
SYSAUX                                600         32          5
USERS                               56.25         12         21
UNDOTBS1                               75         62         83

如果空闲率%Free小于10%以上（包含10%），则注意要增加数据文件来扩展表空间而不要使用数据文件的自动扩展功能。为了方便运维管理，生产环境建议数据文件关闭自动扩展功能，也不能一下子添加过多的数据文件。

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表空间内的内容

SQL>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，我们需要检查2021年9月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等出错信息，如果有则表明备份不正常。

5.4.sql查询备份情况

SQL>
set timing on
set time on
set lines 168 pages 100
set COLSEP '|'
col hour for 99.9
col command_id for a21
col status for a16
col input_type for a12
col inputmb for a12
col outputmb for a12
col permb for a12

select
   ELAPSED_SECONDS/3600 hour
   , to_char(start_time,'mm/dd hh24:mi:ss') start_time
   , to_char(END_TIME,'mm/dd hh24:mi:ss') end_time
   , status
   , input_type
   , output_device_type devtype
   , input_bytes_display inputmb
   , output_bytes_display outputmb
   , output_bytes_per_sec_display permb
 from v$rman_backup_job_details
where start_time >= trunc(sysdate - nvl('$1',2))
 order by start_time DESC
/   

no rows selected

生产环境的重要系统、核心系统都是要求做备份的。备份高于一切。

6.检查Oracle数据库性能

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

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

set COLSEP '|'
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语句的获取

SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS DESC) WHERE ROWNUM<=5 ;

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

set COLSEP '|'
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 COLSEP '|'
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)) SQL FROM 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.检查碎片程度高的表

 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 比例

set lines 199
col file for a80
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 比例

SELECT SUBSTR(A.FILE#,1,2) "#", SUBSTR(A.NAME,1,60) "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　－９　pid

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

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

CPU使用情况：

[oracle@company-vpnserver ~]$ top
top - 16:58:17 up 4 days,  7:04,  3 users,  load average: 0.01, 0.04, 0.05
Tasks: 210 total,   1 running, 209 sleeping,   0 stopped,   0 zombie
%Cpu(s):  2.9 us,  5.7 sy,  0.0 ni, 91.4 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :  4045668 total,   285196 free,   680324 used,  3080148 buff/cache
KiB Swap:   524284 total,   502268 free,    22016 used.  2162988 avail Mem 


   PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                               
 56018 oracle    20   0  162164   2372   1548 R   6.2  0.1   0:00.02 top                                                                                                    1 root      20   0  125460   3232   2112 S   0.0  0.1   1:28.79 systemd                                                           
     2 root      20   0       0      0      0 S   0.0  0.0   0:00.07 kthreadd                                                              
     3 root      20   0       0      0      0 S   0.0  0.0   0:03.08 ksoftirqd/0

内存使用情况：

[oracle@company-vpnserver ~]$ free  -h
              total        used        free      shared  buff/cache   available
Mem:           3.9G        666M        275M        879M        2.9G        2.1G
Swap:          511M         21M        490M

系统I/O情况

[oracle@company-vpnserver ~]$ iostat -k 1 3
Linux 3.10.0-957.el7.x86_64 (company-vpnserver)         10/12/2021      _x86_64_        (2 CPU)


avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.59    0.00    0.57    0.60    0.00   98.24


Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sda               1.96        20.60        63.40    7648693   23537497
scd0              0.00         0.22         0.00      82516          0
dm-0              2.00        19.89        63.38    7384142   23527590

如上所示，kB_read/s、kB_wrtn/s部分表示磁盘读写情况，avg-cpu部分为cpu IO等待情况。

系统负载情况：

[oracle@company-vpnserver ~]$ uptime
 17:03:27 up 4 days,  7:09,  3 users,  load average: 0.05, 0.05, 0.05

如上所示，load average部分表示系统负载，后面的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表中,如没有该表执行

Sql>@/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.检查缓冲区命中率

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

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

6.16.检查共享池命中率

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

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

6.17.检查排序区

select name,value from v$sysstat where name like '%sort%';

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

6.18.检查日志缓冲区

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

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

7.检查数据库安全性

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

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

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

检查登录成功的日志：

[root@company-vpnserver ~]# grep -i accepted /var/log/secure
Oct 10 11:18:51 company-vpnserver sshd[63136]: Accepted password for appuser from 192.168.3.40 port 63729 ssh2
...

检查登录失败的日志：

[root@company-vpnserver ~]# grep -i failure /var/log/secure

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

7.2.检查用户修改密码

在数据库系统上往往存在很多的用户，如：第三方数据库监控系统，初始安装数据库时的演示用户，管理员用户等等，这些用户的密码往往是写定的，被很多人知道，会被别有用心的人利用来攻击系统甚至进行修改数据。

需要修改密码的用户包括：数据库管理员用户SYS，SYSTEM；其他用户。登陆系统后，提示符下输入cat /etc/passwd，在列出来的用户中查看是否存在已经不再使用的或是陌生的帐号。若存在，则记录为异常。

修改密码方法：alter user USER_NAME identified by PASSWORD;

8.其他检查

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

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

[oracle@company-vpnserver ~]$ crontab -l
00 */1 * * * /usr/bin/sh /home/oracle/dba/dbcheck/dbcheck.sh

8.2.Oracle Job是否有失败

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.监控数据量的增长情况

select
A.tablespace_name,(1-(A.total)/B.total)*100 used_percent
from (select tablespace_name,sum(bytes) total
from dba_free_space group by tablespace_name) A,
(select tablespace_name,sum(bytes) total
from dba_data_files group by tablespace_name) B
where A.tablespace_name=B.tablespace_name;

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

--- 删除历史数据

移动规定数据库中至少保留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.检查失效的索引

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

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

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

如有失效约束则启用，如：

alter Table TABLE_NAME Enable Constraints CONSTRAINT_NAME;

8.6.检查无效的trigger

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

如有失效触发器则启用，如：

alter Trigger TRIGGER_NAME Enable;