oracle dg 监控工具,oracle--DG监控脚本

1 conn sys/123456@oracle01 as sysdba

2 column dest_name format a30

3 column destination format a20

4 column MEMBER format a45

5 column destination format a20

6 column TABLESPACE_NAME format a10

7 column FREE_RATE format a10

8 alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

9 set wrap off;

10 prompt **************************** 实 例 状 态 ************************************;

11 select instance_name,version,status,database_status from v$instance;

12 prompt **************************** 数 据 库 状 态 *************************************;

13 select name,log_mode,open_mode from v$database;

14 prompt **************************** 控 制 文 件 状 态 ***********************************;

15 column name format a50

16 select status,name from v$controlfile;

17 prompt **************************** 日 志 文 件 状 态 ***********************************;

18 select GROUP#,status,type,member from v$logfile;

19 prompt***************************** 归 档 目 的 地 状 态 *********************************;

20 select dest_name ,status,database_mode,destination from v$archive_dest_status where

21 dest_id in ('1','2');

22 set heading off;

23 prompt ************ 数 据 库 已 连 续 运 行 天 数*******************************************

24 select round(a.atime-b.startup_time)||' days ' from(select sysdate atime from dual)

25 a,v$instance b;

26 set heading on;

27 prompt***************************** 会 话 数 *************************************;

28 select sessions_current,sessions_highwater from v$license;

29 prompt**************************** active sessions count **************************;

30 select count(*) "Active session count" from v$session where status='ACTIVE';

31 prompt**************************** total sessions count **************************•;

32 select count(*) "Total session count" from v$session;

33 prompt**************************** top 30 big objects name ************************;

34 column OWNER format a10

35 column SEGMENT_NAME format a35

36 column SEGMENT_TYPE format a15

37 column SIZES format a10

38 SELECT * FROM

39 (

40 select OWNER, SEGMENT_NAME, SEGMENT_TYPE, round(BYTES / 1024 /

41 1024 / 1024,3)||'G' AS SIZES

42 from dba_segments

43 ORDER BY BYTES DESC)

44 WHERE ROWNUM<=30

45 ;

46 prompt***************************** WANGGOUuser data size ****************************;

47 select sum(bytes)/1024/1024/1024||'G' "User Data Size" from dba_segments where

48 owner='WANGOU';

49

50 prompt***************************** SUP data size ************************************;

51 select sum(bytes)/1024/1024/1024||'G' "User Data Size" from dba_segments where

52 owner='SUP';

53

54 prompt***************************** DB size ******************************************;

55 select sum(bytes)/1024/1024/1024||'G' "DB Size" from dba_segments;

56

57 prompt***************************** total tablespace size ****************************;

58 select sum(bytes)/1024/1024/1024||'G' "Total Tablespace Size" from

59 dba_data_files;

60

61 prompt***************************** last day archive log count ***********************;

62 select sequence#, completion_time from v$archived_log where completion_time>=

63 trunc(sysdate-1) and completion_time<= trunc(sysdate) and dest_id=1;

64

65 prompt********************** 表空间监控********************;

66 SELECT D.TABLESPACE_NAME,

67 SPACE "SUM_SPACE(M)",

68 BLOCKS "SUM_BLOCKS(K)",

69 SPACE - NVL (FREE_SPACE, 0) "USED_SPACE(M)",

70 ROUND( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2)

71 "USED_RATE(%)",

72 FREE_SPACE "FREE_SPACE(M)"

73 FROM ( SELECT TABLESPACE_NAME,

74 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,

75 SUM (BLOCKS) BLOCKS

76 FROM DBA_DATA_FILES

77 GROUP BY TABLESPACE_NAME) D,

78 ( SELECT TABLESPACE_NAME,

79 ROUND (SUM (BYTES) / (1024 * 1024), 2)

80 FREE_SPACE

81 FROM DBA_FREE_SPACE

82 GROUP BY TABLESPACE_NAME) F

83 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

84 UNION ALL --如果有

85 SELECT D.TABLESPACE_NAME,

86 SPACE "SUM_SPACE(M)",

87 BLOCKS SUM_BLOCKS,

88 USED_SPACE "USED_SPACE(M)",

89 ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2)

90 "USED_RATE(%)",

91 NVL (FREE_SPACE, 0) "FREE_SPACE(M)"

92 FROM ( SELECT TABLESPACE_NAME,

93 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,

94 SUM (BLOCKS) BLOCKS

95 FROM DBA_TEMP_FILES

96 GROUP BY TABLESPACE_NAME) D,

97 ( SELECT TABLESPACE_NAME,

98 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2)

99 USED_SPACE,

100 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2)

101 FREE_SPACE

102 FROM V$TEMP_SPACE_HEADER

103 GROUP BY TABLESPACE_NAME) F

104 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

105 ORDER BY 1;

106 prompt **************************** 表 空 间OFFLINE(显 示 为 空 正 常) ********************;

107 select tablespace_name ,status from dba_tablespaces where status='OFFLINE';

108 prompt **************************** SEQUENCE 同步数 *********************************;

109 select max(sequence#)from v$log_history;

110 conn sys/123456@oracle01 as sysdba

111 prompt **************************** 备库SEQUENCE 同步数 *****************************;

112 select max(sequence#)from v$log_history;

113 prompt **************************** 备库日志未应用(显 示 为 空 正 常) *******************;

114 select sequence#,applied from v$archived_log where applied='yes';

115 prompt **************************** 备库日志应用(显示最近十个日志) *****************;

116 select * from(select sequence#,applied from v$archived_log order by sequence# desc)

117 where rownum<=10;

118 set time on

119 disconnect

120 exit

标签：status,prompt,name,format,column,DG,监控,oracle,select

来源： https://www.cnblogs.com/kingle-study/p/10997563.html