oracle資料庫巡檢（二）全面檢查

實際工作中，不管是接手管理一個新的資料庫還是定期對資料進行檢查，我們都需要一個全面系統的工作，以下是我自己的實際工作操作並結合網上一些前輩對oracle巡檢的經驗總結的一篇文章，希望可以給到新人以參考並得到老鳥的補充和建議。

 

Ps：文中的系統平臺為linux，除過系統平臺的檢查外，oracle資料庫的檢查同樣適用windows平臺。

 

 

一、系統配置
----------------------------------------------------------
1、檢視主機名
[root@wtdb2 ~]# uname -n
wtdb2
2、檢視執行的硬體平臺型別
[root@wtdb2 ~]# uname -im
x86_64 x86_64
3、檢視cpu型別
[root@wtdb2 ~]# uname -p
x86_64
4、檢視cpu型號及引數資訊
[root@wtdb2 ~]# more /proc/cpuinfo
processor : 0
vendor_id : AuthenticAMD
cpu family : 16
model  : 2
model name : Quad-Core AMD Opteron(tm) Processor 8356
stepping : 3
cpu MHz  : 2300.084
cache size : 512 KB
5、檢視記憶體資訊
[root@wtdb2 ~]# free
             total       used       free     shared    buffers     cached
Mem:      32907676   13428340   19479336          0     587384    8346640
-/+ buffers/cache:    4494316   28413360
Swap:     26869752          0   26869752
6、檢視作業系統版本
[root@wtdb2 ~]# lsb_release -d
Description: Red Hat Enterprise Linux AS release 4 (Nahant Update 6)
或
[root@wtdb2 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux AS release 4 (Nahant Update 6)
7、檢視作業系統核心資訊
[root@wtdb2 ~]# uname -sr
Linux 2.6.9-67.ELlargesmp
8、檢視伺服器資訊
[root@wtdb2 ~]# /usr/sbin/dmidecode |grep "Product Name"
  Product Name: ProLiant DL785 G5

二、內容引數
----------------------------------------------------------------------
1、共享記憶體等
[root@wtdb2 ~]# cat /etc/sysctl.conf |grep -v "#" |grep -v "^$"
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
kernel.shmall = 2097152
kernel.shmmax = 21474836480
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
2、oracle使用者限制
[root@wtdb2 ~]# su - oracle
[oracle@wtdb2 ~]$ ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
file size               (blocks, -f) unlimited
pending signals                 (-i) 1024
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 65536
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 16384
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited   

三、磁碟的空間（非ASM環境）
----------------------------------------------------------------------------------
[root@wtdb2 ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                       25G   14G   11G  57% /
/dev/mapper/VolGroup00-LogVol01
                       81G  8.7G   69G  12% /u01
/dev/mapper/mpath47    50G  3.0G   44G   7% /u02/arch
/dev/cciss/c0d0p1      99M   15M   80M  16% /boot
none                   16G     0   16G   0% /dev/shm
20.20.20.2:/u02/bak   591G  286G  276G  51% /u02/bak   

四、資料庫配置
-------------------------------------------------------------------------------------
1、檢視RDBMS版本
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

2、檢視安裝選項
SQL> select * from v$option;

PARAMETER     VALUE
---------------------------------------- ----------
Partitioning     TRUE
Objects      TRUE
Real Application Clusters   TRUE

3、已被使用的產品選項
SQL> select comp_id, comp_name, version,status from dba_registry;

COMP_ID          COMP_NAME                VERSION          STATUS
---------------------------------------------------------------------- ----
SDO          Spatial                     10.2.0.4.0         VALID
ORDIM        Oracle interMedia             10.2.0.4.0         VALID
AMD          OLAP Catalog                  10.2.0.4.0         VALID
EM          Oracle Enterprise Manager       10.2.0.4.0         VALID
XDB          Oracle XML Database           10.2.0.4.0         VALID
CONTEXT      Oracle Text                  10.2.0.4.0         VALID
EXF          Oracle Expression Filter        10.2.0.4.0         VALID
RUL          Oracle Rules Manager           10.2.0.4.0         VALID
OWM          Oracle Workspace Manager        10.2.0.4.3         VALID
ODM          Oracle Data Mining             10.2.0.4.0         VALID
CATALOG      Oracle Database Catalog Views  10.2.0.4.0         VALID
4、資料庫引數
引數檔案
SQL> show parameter spfile;
SQL> select NAME,VALUE,ISDEFAULT as "Default",ISSYS_MODIFIABLE as "Dynamic" from v$system_parameter order by name;
資料庫非預設引數
SQL> select name , value from v$parameter where isdefault='FALSE';

NAME      VALUE
---------------------------------------- ----------------------------------------
processes          3000
sessions          3500
sga_max_size      4294967296
large_pool_size    218103808
spfile           +DATA01/zjcsc/spfilezjcsc.ora
sga_target        4294967296
control_files      +DATA01/zjcsc/controlfile/current.260.72
5、記憶體管理配置
a、檢視sga
SQL> show sga

Total System Global Area 4294967296 bytes
Fixed Size      2286080 bytes
Variable Size   2107317760 bytes
Database Buffers  2181038080 bytes
Redo Buffers      4325376 bytes

SQL> show parameter sga

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
lock_sga        boolean  FALSE
pre_page_sga        boolean  FALSE
sga_max_size        big integer 4G
sga_target        big integer 4G

b、檢視pga
SQL> show parameter pga

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target       big integer 3203M

五、檔案和日誌
----------------------------------------------------------------------------------
1、檢視跟蹤檔案，待補充
2、檢查alert.log
SQL> show parameter dump

NAME                     TYPE  VALUE
------------------------------------ ----------- ------------------------------
background_core_dump       string  partial
background_dump_dest       string  /u01/app/oracle/admin/zjcsc/bdump
core_dump_dest            string  /u01/app/oracle/admin/zjcsc/cdump
max_dump_file_size         string  UNLIMITED
shadow_core_dump           string  partial
user_dump_dest            string  /u01/app/oracle/admin/zjcsc/udump
SQL> host

[oracle@wtdb2 ~]$ cd /u01/app/oracle/admin/zjcsc/bdump/
[oracle@wtdb2 bdump]$ more alert_zjcsc2.log
3、檢查控制檔案
記錄資料庫的物理機構及同步資訊,建議同時存在2份以上的控制檔案，並儲存在不同的磁碟上。
SQL> show parameter control_files;
或者
SQL> select status,name,is_recovery_dest_file,(block_size*file_size_blks)/1024/1024 "Mbytes" from v$controlfile;
4、檢查審計檔案
SQL> show parameter audit_file_dest

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest        string  /u01/app/oracle/admin/zjcsc/adump
如果沒有設定，預設審計檔案在$ORACLE_HOME/rdbms/audit

5、redo log 檔案
建議配置多redo日誌組（2個以上的日誌組），對redo日誌進行映象儲存，配置2個及其以上的redo日誌映象。
connect as sysdba
SQL> select group#,thread#,bytes,members,archived,status from v$log;
SQL> select * from v$logfile order by 1;
SQL>
select l.group#, lf.member as Logfile,l.status,l.bytes/1024/1024 as "Size/MB"
from v$log l,v$logfile lf
where l.group#=lf.group#
order by group#;

    GROUP# LOGFILE           STATUS    Size/MB
---------- -------------------------------------------------- ---------------- ----------
  1 +DATA01/zjcsc/onlinelog/group_1.261.729380855      INACTIVE         50
  2 +DATA01/zjcsc/onlinelog/group_2.262.729380855      INACTIVE         50
  3 +DATA01/zjcsc/onlinelog/group_3.265.729380935      INACTIVE         50
  4 +DATA01/zjcsc/onlinelog/group_4.266.729380935      INACTIVE         50
  5 +DATA01/zjcsc/onlinelog/group_5.315.729393001      CURRENT        100
  6 +DATA01/zjcsc/onlinelog/group_6.316.729393021      ACTIVE        100
  7 +DATA01/zjcsc/onlinelog/group_7.317.729393041      INACTIVE        100
  8 +DATA01/zjcsc/onlinelog/group_8.318.729393053      INACTIVE        100
  9 +DATA01/zjcsc/onlinelog/group_9.319.729393091      INACTIVE        100
 10 +DATA01/zjcsc/onlinelog/group_10.320.729393109     CURRENT        100
                                    
6、archived log 檔案
如果空間允許，建議歸檔日誌儲存2份及其以上映象。
connect as sysdba
SQL> archive log list;
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        /u02/arch
Oldest online log sequence     31135
Next log sequence to archive   31139
Current log sequence        31139

select * from v$archive_dest;
select * from v$archive;
select * from v$archived_log;

6、資料檔案
a、檢視資料檔案（所有資料檔案總和）大小
SQL> select count(*) "count",sum(bytes)/1024/1024 "Mbyters" from v$datafile;   

     count    Mbyters
---------- ----------
 75     708672
b、檢視資料檔案位置及大小
SQL> select tablespace_name,file_id,bytes,autoextensible,status,file_name from dba_data_files;

TABLESPACE_NAME     FILE_ID      BYTES      AUT  STATUS     FILE_NAME
------------------------------ ---------- ---------- --- --------- --------------------------------------------------
USERS                 4        1845493760 YES  AVAILABLE  +DATA01/zjcsc/datafile/users.259.729380787
SYSAUX               3        5368709120 YES  AVAILABLE  +DATA01/zjcsc/datafile/sysaux.257.729380787
UNDOTBS1              2        1.0737E+10 YES  AVAILABLE  +DATA01/zjcsc/datafile/undotbs1.258.729380787
SYSTEM               1        2147483648 YES  AVAILABLE  +DATA01/zjcsc/datafile/system.256.729380787
UNDOTBS2              5        1.0737E+10 YES  AVAILABLE  +DATA01/zjcsc/datafile/undotbs2.264.729380887
c、檢視資料檔案是否自動擴充套件，預設系統表空間的是自動擴充套件的。
SQL> select file_id, tablespace_name, bytes, maxbytes, maxblocks, increment_by, file_name from dba_data_files where autoextensible = 'YES';

   FILE_ID TABLESPACE_NAME         BYTES   MAXBYTES  MAXBLOCKS INCREMENT_BY FILE_NAME
---------- ------------------------------ ---------- ---------- ---------- ------------ --------------------------------------------------
  4 USERS     1845493760 3.4360E+10    4194302     160 +DATA01/zjcsc/datafile/users.259.729380787
  3 SYSAUX     5368709120 3.4360E+10    4194302   12800 +DATA01/zjcsc/datafile/sysaux.257.729380787
  2 UNDOTBS1     1.0737E+10 3.4360E+10    4194302   12800 +DATA01/zjcsc/datafile/undotbs1.258.729380787
  1 SYSTEM     2147483648 3.4360E+10    4194302   12800 +DATA01/zjcsc/datafile/system.256.729380787
  5 UNDOTBS2     1.0737E+10 3.4360E+10    4194302   12800 +DATA01/zjcsc/datafile/undotbs2.264.729380887
 48 BIZMONITOR      209715200 3.4360E+10    4194302    1280 +DATA01/ts_ssvc_bizmonitor.dbf
 49 WLANINDEX      209715200 3.4360E+10    4194302    1280 +DATA01/ts_ssvc_wlanindex.dbf

六、表空間
--------------------------------------------------------------------------------------------------------------------
1、檢視錶空間使用情況，可查詢dba_tablespace; dba_free_space; dba_data_files;

select * from (
Select  a.tablespace_name,
       round(a.bytes/1024/1024,3) total_bytes,
  round(b.bytes/1024/1024,3) free_bytes,
  round(a.bytes/1024/1024 - b.bytes/1024/1024,3) use_bytes,
  round((1 - b.bytes/a.bytes)*100,3) || '%' use
from         (select tablespace_name,
                sum(bytes) bytes
           from dba_data_files
          group by tablespace_name) a,
        (select tablespace_name,
                sum(bytes) bytes
           from dba_free_space
          group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
union all
select         c.tablespace_name,
               round(c.bytes/1024/1024,3) total_bytes,
               round( (c.bytes-d.bytes_used)/1024/1024,3) free_bytes,
               round(d.bytes_used/1024/1024,3) use_bytes,
               round(d.bytes_used*100/c.bytes,3) || '%' use
from
(select tablespace_name,sum(bytes) bytes
from dba_temp_files group by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool group by tablespace_name) d
where c.tablespace_name = d.tablespace_name
)
order by tablespace_name;

2、表空間可用性檢查（online or offline）
SQL> select tablespace_name,status from dba_tablespaces;

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

4、檢視錶空間管理型別（資料字典管理or本地管理，9i之後預設的管理方式為本地管理方式）
SQL> select tablespace_name, extent_management from dba_tablespaces;
5、檢視臨時表空間
a、檢查當前永久表空間和臨時表空間
SQL> select tablespace_name, contents  from dba_tablespaces;
b、檢查是否存在永久表空間型別的臨時表空間（10G之後應該不會有這種情況）
SQL> select u.username, t.tablespace_name
from dba_users u, dba_tablespaces t
where u.temporary_tablespace = t.tablespace_name
and t.contents <> 'TEMPORARY';
c、檢查臨時表空間大小
SQL> select tablespace_name, sum(bytes)/1024/1024 mb
     from dba_temp_files
     group by tablespace_name;
d、檢視臨時表空間使用的高水位線（同一時間最大的使用量）
SQL> select tablespace_name, sum(bytes_cached)/1024/1024 mb
     from v$temp_extent_pool
     group by tablespace_name;
e、檢視臨時表空間當前使用情況
待定。。。
6、表空間碎片問題
待定。。。

 

七、資料庫物件
-------------------------------------------------------------------------------------------------------------------

1、檢查無效的資料庫物件
無效資料物件，應在系統不繁忙的時候，進行手工刪除，防止在系統壓力較大的時候，手工編譯或者自動編譯，否則可能會導致的資料庫死鎖。
SQL> SELECT owner,object_type,object_name,status,LAST_DDL_TIME  FROM dba_objects WHERE status like 'INVALID';
2、檢視在系統表空間（system,sysaux）內的非系統使用者（sys，system）物件。
SQL> select owner, segment_name, segment_type from dba_segments where tablespace_name = 'SYSTEM' and owner not in ('SYS','SYSTEM');

OWNER    SEGMENT_NAME        SEGMENT_TYPE
---------- ---------------------------------------- ------------------
ZJCSC517   LOGIN_DATE_CZH_7       INDEX
ZJCSC517   LOGIN_ID_CZH_7         INDEX
ZJCSC517   LOGIN_TYPE_CZH_7       INDEX
3、檢查資料庫連線
SQL> SELECT * FROM DBA_DB_LINKS;
4、檢查索引
索引需要維護。對於表的刪除或者新增操作都會間接地對index進行相應的操作。過時的index結構會產生碎片，此時index需要被重新建立。
belevel是B-tree索引形式的一部分，與oracle為搜尋某些記錄而減少index搜尋的次數相關聯。在一些情況下，BLEVEL需要單獨的磁碟命中。
如果blevel>4，那麼建議重建index
SQL> select * from dba_indexes where blevel>4;

八、安全管理
------------------------------------------------------------------------------------------------------------------------
database / administrator
檢查sys/system 不要使用預設密碼
檢查被授予DBA許可權的使用者，收回不必要的
select grantee,granted_role from dba_role_privs where granted_role='DBA'
檢查被授予SYSDBA許可權的使用者
select * from v$pwfile_users;
 
九、監聽器的設定
------------------------------------------------------------------------------------------------------------------------
1、位於伺服器端的監聽程式時單獨的程式。它從客戶端接收連線請求，並管理這些對服務端的請求。
   cd $ORACLE_HOME/network/admin
   more listener.ora

2、SQLNET.ORA設定
   包含了客戶端和伺服器對SQL*NET配置的設定資訊。
   cd $ORACLE_HOME/network/admin
   more sqlnet.log
3、TNSNAMES.ORA
   包含了與連線描述符相匹配的網路服務名。連線描述符包括監聽程式的地址以及connect_data
   cd $ORACLE_HOME/network/admin
   more tnsnames.ora
 
十、資料庫效能 
------------------------------------------------------------------------------------------------------------------------  
   statspack或者awr報告
 
十一、資料庫備份和恢復概況
------------------------------------------------------------------------------------------------------------------------
    檢查備份恢復策略
    建議：根據不同的資料庫失敗情況制定相應的恢復策略
          全庫恢復
          表空間恢復
          資料檔案恢復
          表恢復
          制定的恢復策略進行恢復測試。