作为一个甲方酱油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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26442936/viewspace-1165919/