酱油DBA奉献ORACLE数据库监控健康脚本（一）

lovehewenyu · 发表于 2014-1-20 17:19

本帖最后由 lovehewenyu 于 2014-12-10 14:06 编辑

作为一个甲方酱油DBA，编写一个ORACLE数据库监控健康脚本献给酱油的所有DBA！！！（目前版本为ver1,日后会有更新）

脚本用途：一次性收集ORACLE数据库的基本信息，排查出数据库上一些基础性的问题所在。希望对广大酱油DBA有所帮助。

酱油DBA奉献ORACLE数据库监控健康脚本（一）
http://www.itpub.net/thread-1841912-1-1.html

酱油DBA奉献expdp,impdp多用户迁移数据（二）
http://www.itpub.net/thread-1868059-1-1.html

酱油DBA奉献 Oracle 11gR2 RAC on linux 集成脚本化安装（三）
http://www.itpub.net/thread-1900973-1-1.html

脚本收集信息展示：

NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                string
db_name                            string    doudou
db_unique_name                      string    doudou
global_names                      boolean    FALSE
instance_name                      string    doudou
lock_name_space                   string
log_file_name_convert             string
processor_group_name                string
service_names                      string    doudou
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
NAME    OPEN_MODE          HOSTNAME          IPADDR
--------- -------------------- -------------------- --------------------
DOUDOU READ WRITE          doudou             192.168.1.10

--1. Parameter file---------------------------------------------------------------------------------
NAME                                  VALUE
---------------------------------------- --------------------------------------------------------------------------------
lock_name_space
processes                               150
sessions                               247
timed_statistics                      TRUE
timed_os_statistics                   0
resource_limit                         FALSE
license_max_sessions                   0
license_sessions_warning                0
cpu_count                               1
instance_groups
event
NAME                                  VALUE
---------------------------------------- --------------------------------------------------------------------------------
sga_max_size                            591396864
use_large_pages                         TRUE
pre_page_sga                            FALSE
shared_memory_address                   0
hi_shared_memory_address                0
use_indirect_data_buffers             FALSE
lock_sga                               FALSE
processor_group_name
shared_pool_size                      0
large_pool_size                         0
java_pool_size                         0
NAME                                  VALUE
---------------------------------------- --------------------------------------------------------------------------------
streams_pool_size                      0
shared_pool_reserved_size             6501171
java_soft_sessionspace_limit          0
java_max_sessionspace_size             0
spfile                                  /oradata/product/11.2.0/dbhome_1/dbs/spfiledoudou.ora
instance_type                         RDBMS
nls_language                            AMERICAN
nls_territory                         AMERICA
nls_sort
nls_date_language
nls_date_format
此处省略2万字，嘿嘿

--2. Controlfiles-----------------------------------------------------------------------------------
STATUS  NAME
------- ----------------------------------------
      /oradata/oradata/doudou/control01.ctl
      /oradata/oradata/doudou/control02.ctl
--3. Redolog files----------------------------------------------------------------------------------
GROUP# THREAD# STATUS          MEMBER                                           logfile/mb
---------- ---------- ---------------- -------------------------------------------------- ----------
      3       1 INACTIVE       /oradata/oradata/doudou/redo03.log                      50
      2       1 CURRENT       /oradata/oradata/doudou/redo02.log                      50
      1       1 INACTIVE       /oradata/oradata/doudou/redo01.log                      50
--4. Archiving--------------------------------------------------------------------------------------
Database log mode             Archive Mode
Automatic archival          Enabled
Archive destination          /oradata/arch1
Oldest online log sequence    12
Next log sequence to archive 14
Current log sequence          14
ARC APPLIED NAME
--- --------- ------------------------------------------------------------
YES NO       /oradata/arch/1_11_836736339.dbf
YES NO       /oradata/arch1/1_12_836736339.dbf
YES NO       /oradata/arch1/1_13_836736339.dbf
COMPLETION       NUM
---------- ----------
2014/01/15       3
--5. Datafiles--------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME          USE/M MAXBYTES  MAXBLOCKS INCREMENT/M FILE_NAME
---------- -------------------- ---------- ---------- ---------- ----------- ----------------------------------------
      4 USERS                      5 3.4360E+10 4194302  .000152588 /oradata/oradata/doudou/users01.dbf
      3 UNDOTBS1                   90 3.4360E+10 4194302  .000610352 /oradata/oradata/doudou/undotbs01.dbf
      2 SYSAUX                   580 3.4360E+10 4194302  .001220703 /oradata/oradata/doudou/sysaux01.dbf
      1 SYSTEM                   710 3.4360E+10 4194302  .001220703 /oradata/oradata/doudou/system01.dbf
      5 DOUDOU                      50 1073741824    131072  9.5367E-07 /oradata/oradata/doudou/doudou.dbf
--6. Tablespaces------------------------------------------------------------------------------------
--6.1 SYSTEM Tablespace-----------------------------------------------------------------------------
OWNER                         SEGMENT_NAME                                                                   SEGMENT_TYPE
------------------------------ --------------------------------------------------------------------------------- ------------------
OUTLN                         OL$                                                                            TABLE
OUTLN                         OL$HINTS                                                                         TABLE
OUTLN                         OL$HNT_NUM                                                                      INDEX
OUTLN                         OL$NAME                                                                         INDEX
OUTLN                         OL$NODES                                                                         TABLE
OUTLN                         OL$NODE_OL_NAME                                                                INDEX
OUTLN                         OL$SIGNATURE                                                                   INDEX
OUTLN                         SYS_IL0000000451C00021$$                                                       LOBINDEX
OUTLN                         SYS_LOB0000000451C00021$$                                                       LOBSEGMENT
--6.2 SYSAUX Tablespace-----------------------------------------------------------------------------
SPACE_USAGE_KBYTES OCCUPANT_NAME                                                 OCCUPANT_DESC
------------------ ---------------------------------------------------------------- ---------------------------------------------
         161216 XDB                                                             XDB
         75712 SDO                                                             Oracle Spatial
         73088 EM                                                             Enterprise Manager Repository
         33984 XSOQHIST                                                       OLAP API History Tables
         33984 AO                                                             Analytical Workspace Object Table
         32448 SM/AWR                                                          Server Manageability - Automatic Workload Rep
                                                                                 ository

         13760 ORDIM/ORDDATA                                                 Oracle Multimedia ORDDATA Components
         12544 LOGMNR                                                          LogMiner
            9920 SM/OPTSTAT                                                    Server Manageability - Optimizer Statistics H
此处省略2千字

--6.3 Locally vs Dictionary Managed Tablespaces-----------------------------------------------------
TABLESPACE_NAME    EXTENT_MAN
-------------------- ----------
SYSTEM             LOCAL
SYSAUX             LOCAL
UNDOTBS1          LOCAL
TEMP                LOCAL
USERS             LOCAL
DOUDOU             LOCAL
DOUDOU_TMP          LOCAL
--6.4 Temporary Tablespace--------------------------------------------------------------------------
TABLESPACE_NAME    CONTENTS
-------------------- ---------
USERS             PERMANENT
SYSAUX             PERMANENT
DOUDOU             PERMANENT
SYSTEM             PERMANENT
DOUDOU_TMP          TEMPORARY
TEMP                TEMPORARY
UNDOTBS1          UNDO
--wrong temporary_tablespace type-------------------------------------------------------------------
--temporary_tablespace size-------------------------------------------------------------------------
TABLESPACE_NAME             MB
-------------------- ----------
TEMP                      29
DOUDOU_TMP                1024
--temporary_tablespace max used at one time---------------------------------------------------------
TABLESPACE_NAME             MB
-------------------- ----------
TEMP                      28
--temporary_tablespace give current usage-----------------------------------------------------------
TABLESPACE_NAME             MB
-------------------- ----------
TEMP                         0
--7. Objects----------------------------------------------------------------------------------------
--7.1 Number of Extents-----------------------------------------------------------------------------
--7.2 Next extent-----------------------------------------------------------------------------------
--7.3 Indexes---------------------------------------------------------------------------------------
--partition indexes---------------------------------------------------------------------------------
--8. AUTO vs MANUAL undo----------------------------------------------------------------------------
--8.1 AUTO UNDO-------------------------------------------------------------------------------------
NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
undo_management                   string    AUTO
undo_retention                      integer    900
undo_tablespace                   string    UNDOTBS1
TABLESPACE_NAME RETENTION
--------------- -----------
UNDOTBS1       NOGUARANTEE
--8.2 MANUAL UNDO-----------------------------------------------------------------------------------
SEGMENT_NAME TABLESPACE_NAME    WAITS SHRINKS    WRAPS STATUS
--------------- --------------- ---------- ---------- ---------- ---------------
SYSTEM       SYSTEM                0       0       0 ONLINE
_SYSSMU10_24902 UNDOTBS1                1       2       3 ONLINE
56178$

_SYSSMU1_313888 UNDOTBS1                0       0       0 ONLINE
5392$

_SYSSMU2_422823 UNDOTBS1                1       1       6 ONLINE
8222$

_SYSSMU3_221074 UNDOTBS1                0       0       8 ONLINE
SEGMENT_NAME TABLESPACE_NAME    WAITS SHRINKS    WRAPS STATUS
--------------- --------------- ---------- ---------- ---------- ---------------
2642$

_SYSSMU4_145531 UNDOTBS1                1       1       4 ONLINE
8006$

_SYSSMU5_378762 UNDOTBS1                0       0       0 ONLINE
2316$

_SYSSMU6_246024 UNDOTBS1                0       0       0 ONLINE
8069$

SEGMENT_NAME TABLESPACE_NAME    WAITS SHRINKS    WRAPS STATUS
--------------- --------------- ---------- ---------- ---------- ---------------
_SYSSMU7_192488 UNDOTBS1                0       0       0 ONLINE
3037$

_SYSSMU8_190928 UNDOTBS1                0       0       0 ONLINE
0886$

_SYSSMU9_359345 UNDOTBS1                0       0       0 ONLINE
0615$

--9. Memory Management------------------------------------------------------------------------------
NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address          integer    0
memory_max_target                   big integer 564M
memory_target                      big integer 564M
shared_memory_address             integer    0
--10. Logging | Tracing-----------------------------------------------------------------------------
--10.1 Alert File-----------------------------------------------------------------------------------
NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                   string    /oradata
NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                string    partial
background_dump_dest                string    /oradata/diag/rdbms/doudou/dou
                                             dou/trace
core_dump_dest                      string    /oradata/diag/rdbms/doudou/dou
                                             dou/cdump
max_dump_file_size                string    unlimited
shadow_core_dump                   string    partial
user_dump_dest                      string    /oradata/diag/rdbms/doudou/dou
                                             dou/trace
--10.2 Max_dump_file_size---------------------------------------------------------------------------
NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size                string    unlimited
--10.2 Max_dump_file_size---------------------------------------------------------------------------
--10.4 Audit files----------------------------------------------------------------------------------
NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                   string    /oradata/admin/doudou/adump
audit_sys_operations                boolean    FALSE
audit_syslog_level                string
audit_trail                         string    DB

lovehewenyu · 发表于 2014-1-20 17:22

希望每个DBA，都去编写自己的脚本。用自己的方式去管理数据库，我会编写更多的有利于“甲方酱油DBA”的脚本，期望更多的酱油DBA关注！

haoge0205 · 发表于 2014-1-20 17:32

支持...

lovehewenyu · 发表于 2014-1-21 14:52

亲爱的酱油DBA们，使用后，给点意见和回复。我会加快更新的脚步。

墨雪-guoyan · 发表于 2014-1-22 11:03

支持一下

芳林野草 · 发表于 2014-1-22 15:52

本帖最后由芳林野草于 2014-1-22 15:53 编辑

提两点建议：（1）文本格式的检查结果可读性不友好，建议尝试一下生成HTML格式的检查结果。
（2）内容较多排版混乱，建议将内容分组优化排版。

分享一个我之前写的检查脚本：
owl.sql (39.52 KB, 下载次数: 346)

2014-1-22 15:51 上传
点击文件名下载附件

写脚本的过程就是成长的过程，楼主加油！

sylvanzzy · 发表于 2014-1-22 17:39

建议在格式排版上下点功夫
看空间脚本可以使用一些格式进行处理,如
SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 255
SET FEEDBACK OFF

SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",
   Substr(df.file_name,1,40) "File Name",
   Round(df.bytes/1024/1024,2) "Size (M)",
   Round(e.used_bytes/1024/1024,2) "Used (M)",
   Round(f.free_bytes/1024/1024,2) "Free (M)",
   Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "% Used"
FROM DBA_DATA_FILES DF,
   (SELECT file_id,
            Sum(Decode(bytes,NULL,0,bytes)) used_bytes
      FROM dba_extents
      GROUP by file_id) E,
   (SELECT Max(bytes) free_bytes,
            file_id
      FROM dba_free_space
      GROUP BY file_id) f
WHERE  e.file_id (+) = df.file_id
AND df.file_id  = f.file_id (+)
ORDER BY df.tablespace_name,
      df.file_name;

PROMPT
SET FEEDBACK ON
SET PAGESIZE 18

lovehewenyu · 发表于 2014-1-22 21:10

sylvanzzy 发表于 2014-1-22 17:39
建议在格式排版上下点功夫
看空间脚本可以使用一些格式进行处理,如
SET SERVEROUTPUT ON

嗯，谢谢！以后会更完善的

lovehewenyu · 发表于 2014-1-22 21:12

芳林野草发表于 2014-1-22 15:52
提两点建议：（1）文本格式的检查结果可读性不友好，建议尝试一下生成HTML格式的检查结果。
（2）内容较多 ...

谢谢，这个正是我需要的呵呵

lovehewenyu · 发表于 2014-1-22 21:12

芳林野草发表于 2014-1-22 15:52
提两点建议：（1）文本格式的检查结果可读性不友好，建议尝试一下生成HTML格式的检查结果。
（2）内容较多 ...

早就先过搞 HTML，呵呵。这回好好研究一下

[体系架构] 酱油DBA奉献ORACLE数据库监控健康脚本（一）

浏览过的版块