oracle 手工切快照,ORACLE DBA实践之五十九：oracle快照监控脚本2（转自CNOUG）

监控以下大类：

监控脚本1

1、数据库实例信息

2、调度和作业信息

3、存储

监控脚本24、undo段

5、备份

6、闪回技术

7、性能

8、自动工作量仓库(AWR)

9、会话

10、安全

11、对象

12、OLAP

13、数据泵

14、网络

15、复制

每一大类下又分了很多小项，如存储大类下就分了表空间，数据文件，数据库增长率等，执行该基本后会自动产生一个html文件，放在执行sqlplus时所在的目录下，在xp系统上默认是x:\document

and settings\用户名。用了觉得好的就顶一下！在pl/sql

dev下运行，生产的html报告格式可能会有问题，请直接在sql*plus下执行

--

+----------------------------------------------------------------------------+

--

| - UNDO SEGMENT CONTENTION

- |

--

+----------------------------------------------------------------------------+

prompt

name="undo_segment_contention">

prompt

face="Arial,Helvetica,Geneva,sans-serif"

color="#336699">UNDO

Segment

Contention

---

align="left" width="460">

prompt UNDO statistics from

V$ROLLSTAT - (ordered by

waits)

CLEAR COLUMNS BREAKS COMPUTES

COLUMN

roll_name HEADING 'UNDO Segment Name' ENTMAP off

COLUMN

gets FORMAT 999,999,999 HEADING

'Gets' ENTMAP off

COLUMN

waits FORMAT 999,999,999 HEADING

'Waits' ENTMAP off

COLUMN immediate_misses FORMAT

999,999,999 HEADING 'Immediate

Misses' ENTMAP off

COLUMN

hit_ratio HEADING 'Hit

Ratio' ENTMAP off

BREAK ON report

COMPUTE SUM label '

color="#990000">Total:

'

OF gets waits ON report

SELECT

'

color="#336699">'

|| b.name ||

'

' roll_name

,

gets gets

,

waits waits

, '

align="right">' || TO_CHAR(ROUND(((gets -

waits)*100)/gets, 2)) || '%

'

hit_ratio

FROM

sys.v_$rollstat a

, sys.v_$rollname b

WHERE

a.USN =

b.USN

ORDER BY

waits

DESC;

prompt

prompt Wait

statistics

CLEAR COLUMNS BREAKS COMPUTES

COLUMN

class HEADING

'Class' COLUMN

ratio HEADING 'Wait

Ratio'

SELECT

'

color="#336699">'

|| w.class ||

'

' class

, '

align="right">' ||

TO_CHAR(ROUND(100*(w.count/SUM(s.value)),8)) ||

'%

' ratio

FROM

v$waitstat w

, v$sysstat s

WHERE

w.class IN ( 'system undo header'

, 'system undo block'

, 'undo header'

, 'undo block'

)

AND s.name IN ('db block gets', 'consistent

gets')

GROUP BY

w.class

, w.count;

prompt

[

class="noLink"

href="#top">Top

]

--

+============================================================================+

--

| |

--

| <<<<>>>> |

--

| |

--

+============================================================================+

prompt

prompt

size="+2" face="Arial,Helvetica,Geneva,sans-serif"

color="#663300">Backups

--

+----------------------------------------------------------------------------+

--

| - RMAN BACKUP JOBS

- |

--

+----------------------------------------------------------------------------+

prompt

name="rman_backup_jobs">

prompt

face="Arial,Helvetica,Geneva,sans-serif"

color="#336699">RMAN

Backup

Jobs

---

align="left" width="460">

prompt Last 10 RMAN backup

jobs

CLEAR COLUMNS BREAKS COMPUTES

COLUMN

backup_name FORMAT a130 HEADING 'Backup

Name' ENTMAP off

COLUMN

start_time FORMAT a75 HEADING 'Start

Time' ENTMAP off

COLUMN

elapsed_time FORMAT a75 HEADING 'Elapsed

Time' ENTMAP off

COLUMN

status HEADING

'Status' ENTMAP off

COLUMN

input_type HEADING 'Input

Type' ENTMAP off

COLUMN

output_device_type HEADING 'Output

Devices' ENTMAP off

COLUMN

input_size HEADING 'Input

Size' ENTMAP off

COLUMN

output_size HEADING 'Output

Size' ENTMAP off

COLUMN

output_rate_per_sec