为了减少监控或同级
多套数据库存储容量或者备份检查的工作量,可以考虑使用一个专门做
监控服务器,
通过配置一些需要到的配置表,然后以sh脚本或者存储过程形式固化采集多套库的存储容量情况或者 备份情况的
方式,形成简易的采集流程,大大简化了重复而繁琐的数据库管理工作 。
配置方法:
1、监控服务器;
2、监控服务器通往各个被监控服务器dblink的配置表,(如下 suxing.conf11g_dblink_tab或者suxing.CONF_BACKUPCHECK_DBLINK_TAB )
----由于两个配置表的功能有所差异,所以分开两个配置表;
3、专门作监控的用户;
4、脚本或者存储过程(如下 多个服务器 容量统计脚本和多个服务器备份检查结果采集脚本 );
5、存放采集数据的表(如下 suxing.space_gather11G_tab或者 suxing.db_backup_check_alltab )。
#####多个服务器 容量统计脚本:
declare
v_sql varchar2(4000);
begin
for rec in (select * from suxing.conf11g_dblink_tab) loop
v_sql :='insert into suxing.space_gather11G_tab
select * from (
with
I as (select instance_name from v$instance@'||rec.db_link||'),
A as (select round(sum(bytes)/1024/1024/1024,2) aa from dba_data_files@'||rec.db_link||'),
B as (select round(sum(bytes)/1024/1024/1024,2) bb from dba_free_space@'||rec.db_link||'),
C as (select round(sum(BYTES / 1024 / 1024 / 1024), 2) cc
from v$datafile@'||rec.db_link||' a, v$tablespace@'||rec.db_link||' b
where a.TS# = b.TS#
and a.CREATION_TIME <
to_date(''2017-07-15 10:00:00'', ''YYYY-MM-DD HH24:MI:SS'')),
D as (select round(sum(BYTES / 1024 / 1024 / 1024), 2) dd
from v$datafile@'||rec.db_link||' a, v$tablespace@'||rec.db_link||' b
where a.TS# = b.TS#
and a.CREATION_TIME <
to_date(''2017-04-15 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'')),
E as (select round(sum(BYTES / 1024 / 1024 / 1024), 2) ee
from v\$datafile@'||rec.db_link||' a, v\$tablespace@'||rec.db_link||' b
where a.TS# = b.TS#
and a.CREATION_TIME <
to_date(''2017-06-15 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'')),
G as (select GROUP_NUMBER,NAME,TOTAL_MB/1024 totalGB,FREE_MB/1024 freeGB
from v$asm_diskgroup@'||rec.db_link||' where name like ''DATA%'')
select I.instance_name,A.aa Total_G,A.aa-B.bb Used_G,B.bb Free_G,G.totalGB,G.freeGB,(C.cc - D.dd)/3 AVRG3,C.cc - E.ee,sysdate,to_char(sysdate,''yymmdd'')
from I,A,B,C,D,E,G
)';
--dbms_output.put_line(v_sql);
execute immediate v_sql;
commit;
end loop;
end;
##### 多个服务器 备份检查结果采集脚本:
declare
v_sql varchar2(4000);
begin
for rec in (select * from suxing.CONF_BACKUPCHECK_DBLINK_TAB) loop
v_sql :='insert into suxing.db_backup_check_alltab
select * from (
with
I as (select instance_name from v$instance@'||rec.db_link||'),
BC as (
select start_time, t.INPUT_TYPE, status
from v$rman_backup_job_details@'||rec.db_link||' t
where t.start_time in (select max(start_time)
from v$rman_backup_job_details@'||rec.db_link||')
and t.INPUT_TYPE = ''ARCHIVELOG''
group by t.start_time, t.INPUT_TYPE, t.STATUS
union all
select start_time, t.INPUT_TYPE, status
from v$rman_backup_job_details@'||rec.db_link||' t
where t.start_time in (select max(start_time)
from v$rman_backup_job_details@'||rec.db_link||' t
where t.INPUT_TYPE = ''DB INCR'')
group by t.start_time, t.INPUT_TYPE, t.STATUS)
select I.instance_name,BC.*,to_char(sysdate,''mmddhh24'') from I,BC)';
--dbms_output.put_line(v_sql);
execute immediate v_sql;
commit;
end loop;
end;
注:这些脚本当中,with子句的作用非常关键。
通过配置一些需要到的配置表,然后以sh脚本或者存储过程形式固化采集多套库的存储容量情况或者 备份情况的
方式,形成简易的采集流程,大大简化了重复而繁琐的数据库管理工作 。
配置方法:
1、监控服务器;
2、监控服务器通往各个被监控服务器dblink的配置表,(如下 suxing.conf11g_dblink_tab或者suxing.CONF_BACKUPCHECK_DBLINK_TAB )
----由于两个配置表的功能有所差异,所以分开两个配置表;
3、专门作监控的用户;
4、脚本或者存储过程(如下 多个服务器 容量统计脚本和多个服务器备份检查结果采集脚本 );
5、存放采集数据的表(如下 suxing.space_gather11G_tab或者 suxing.db_backup_check_alltab )。
#####多个服务器 容量统计脚本:
declare
v_sql varchar2(4000);
begin
for rec in (select * from suxing.conf11g_dblink_tab) loop
v_sql :='insert into suxing.space_gather11G_tab
select * from (
with
I as (select instance_name from v$instance@'||rec.db_link||'),
A as (select round(sum(bytes)/1024/1024/1024,2) aa from dba_data_files@'||rec.db_link||'),
B as (select round(sum(bytes)/1024/1024/1024,2) bb from dba_free_space@'||rec.db_link||'),
C as (select round(sum(BYTES / 1024 / 1024 / 1024), 2) cc
from v$datafile@'||rec.db_link||' a, v$tablespace@'||rec.db_link||' b
where a.TS# = b.TS#
and a.CREATION_TIME <
to_date(''2017-07-15 10:00:00'', ''YYYY-MM-DD HH24:MI:SS'')),
D as (select round(sum(BYTES / 1024 / 1024 / 1024), 2) dd
from v$datafile@'||rec.db_link||' a, v$tablespace@'||rec.db_link||' b
where a.TS# = b.TS#
and a.CREATION_TIME <
to_date(''2017-04-15 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'')),
E as (select round(sum(BYTES / 1024 / 1024 / 1024), 2) ee
from v\$datafile@'||rec.db_link||' a, v\$tablespace@'||rec.db_link||' b
where a.TS# = b.TS#
and a.CREATION_TIME <
to_date(''2017-06-15 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'')),
G as (select GROUP_NUMBER,NAME,TOTAL_MB/1024 totalGB,FREE_MB/1024 freeGB
from v$asm_diskgroup@'||rec.db_link||' where name like ''DATA%'')
select I.instance_name,A.aa Total_G,A.aa-B.bb Used_G,B.bb Free_G,G.totalGB,G.freeGB,(C.cc - D.dd)/3 AVRG3,C.cc - E.ee,sysdate,to_char(sysdate,''yymmdd'')
from I,A,B,C,D,E,G
)';
--dbms_output.put_line(v_sql);
execute immediate v_sql;
commit;
end loop;
end;
##### 多个服务器 备份检查结果采集脚本:
declare
v_sql varchar2(4000);
begin
for rec in (select * from suxing.CONF_BACKUPCHECK_DBLINK_TAB) loop
v_sql :='insert into suxing.db_backup_check_alltab
select * from (
with
I as (select instance_name from v$instance@'||rec.db_link||'),
BC as (
select start_time, t.INPUT_TYPE, status
from v$rman_backup_job_details@'||rec.db_link||' t
where t.start_time in (select max(start_time)
from v$rman_backup_job_details@'||rec.db_link||')
and t.INPUT_TYPE = ''ARCHIVELOG''
group by t.start_time, t.INPUT_TYPE, t.STATUS
union all
select start_time, t.INPUT_TYPE, status
from v$rman_backup_job_details@'||rec.db_link||' t
where t.start_time in (select max(start_time)
from v$rman_backup_job_details@'||rec.db_link||' t
where t.INPUT_TYPE = ''DB INCR'')
group by t.start_time, t.INPUT_TYPE, t.STATUS)
select I.instance_name,BC.*,to_char(sysdate,''mmddhh24'') from I,BC)';
--dbms_output.put_line(v_sql);
execute immediate v_sql;
commit;
end loop;
end;
注:这些脚本当中,with子句的作用非常关键。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31392094/viewspace-2142760/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31392094/viewspace-2142760/