醬油DBA奉獻ORACLE資料庫監控健康指令碼（一）
作為一個甲方醬油DBA，編寫一個ORACLE資料庫監控健康指令碼獻給醬油的所有DBA！！！（目前版本為ver1,日後會有更新）
指令碼用途：一次性收集ORACLE資料庫的基本資訊，排查出資料庫上一些基礎性的問題所在。希望對廣大醬油DBA有所幫助。
指令碼收集資訊展示：
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
指令碼用途：一次性收集ORACLE資料庫的基本資訊，排查出資料庫上一些基礎性的問題所在。希望對廣大醬油DBA有所幫助。
指令碼收集資訊展示：
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-1165919/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 醬油DBA奉獻expdp,impdp多使用者遷移資料（二）
- 資料庫監控指令碼（一）資料庫指令碼
- Oracle DBA常用監控指令碼Oracle指令碼
- DBA監控指令碼指令碼
- 資料庫監控指令碼資料庫指令碼
- 監控資料庫指令碼資料庫指令碼
- 8個DBA最常用的監控Oracle資料庫的常用shell指令碼Oracle資料庫指令碼
- 8個DBA最常用的監控Oracle資料庫的常用shell指令碼--Oracle資料庫指令碼
- 監控Oracle資料庫的常用shell指令碼Oracle資料庫指令碼
- oracle DBA 常用監控指令碼1(轉)Oracle指令碼
- 8個DBA最常用的監控Oracle資料庫的常用shell指令碼--轉Oracle資料庫指令碼
- [zt]資料庫監控指令碼資料庫指令碼
- 資料庫監控指令碼（二）資料庫指令碼
- 資料庫監控指令碼（三）資料庫指令碼
- [轉]監控Oracle資料庫的常用shell指令碼Oracle資料庫指令碼
- 監控Oracle資料庫效能的指令碼段整理Oracle資料庫指令碼
- 監控Oracle資料庫的常用shell指令碼(轉)Oracle資料庫指令碼
- 資料庫效能SQL監控指令碼資料庫SQL指令碼
- 幾個重要的指令碼來監控Oracle資料庫指令碼Oracle資料庫
- 【轉載】監控Oracle資料庫的常用shell指令碼Oracle資料庫指令碼
- oracle 資料庫效能健康檢查指令碼Oracle資料庫指令碼
- 監控DB2資料庫指令碼DB2資料庫指令碼
- (轉)oracle 資料庫效能健康檢查指令碼Oracle資料庫指令碼
- oracle 監控指令碼Oracle指令碼
- oracle 資料庫效能健康檢查指令碼[轉帖]Oracle資料庫指令碼
- 監控Oracle資料庫方法Oracle資料庫
- zabbix監控oracle資料庫Oracle資料庫
- 一個簡單資料庫健康檢查指令碼資料庫指令碼
- 2 Day DBA-管理方案物件-監控和優化資料庫-積極的資料庫監控物件優化資料庫
- 一套較全面的oracle資料庫監控管理指令碼Oracle資料庫指令碼
- mysql 的一個監控指令碼，監控heartbeatMySql指令碼
- 監控oracle表空間指令碼Oracle指令碼
- oracle會話監控shell指令碼Oracle會話指令碼
- oracle空間使用監控指令碼Oracle指令碼
- 資料庫健康檢查 sqlplus 指令碼資料庫SQL指令碼
- 監控指令碼指令碼
- Oracle資料庫的監控內容Oracle資料庫
- Oracle 資料庫監控SQL語句Oracle資料庫SQL