批量監控

--------------------------------------------------------
-- File created - Monday-September-26-2016
--------------------------------------------------------
--------------------------------------------------------
-- DDL for Table A
--------------------------------------------------------

CREATE TABLE "HPEJK"."A"
( "TABLESPACE_NAME" VARCHAR2(30 BYTE),
"BYTES" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Table ASM_INFO
--------------------------------------------------------

CREATE TABLE "HPEJK"."ASM_INFO"
( "NAME" VARCHAR2(30 BYTE),
"TOTAL_G" NUMBER,
"FREE_G" NUMBER,
"IP" VARCHAR2(20 BYTE),
"NOASM" VARCHAR2(20 BYTE),
"TIME" DATE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Table ASM_TEMP
--------------------------------------------------------

CREATE TABLE "HPEJK"."ASM_TEMP"
( "NAME" VARCHAR2(30 BYTE),
"TOTAL_G" NUMBER,
"FREE_G" NUMBER,
"IP" VARCHAR2(20 BYTE),
"NOASM" VARCHAR2(20 BYTE),
"TIME" DATE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Table BAK_INFO
--------------------------------------------------------

CREATE TABLE "HPEJK"."BAK_INFO"
( "SESSION_RECID" NUMBER,
"START_TIME" DATE,
"END_TIME" DATE,
"INPUT_TYPE" VARCHAR2(13 BYTE),
"STATUS" VARCHAR2(23 BYTE),
"IP" VARCHAR2(20 BYTE),
"NOBAK" VARCHAR2(20 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Table BAK_TEMP
--------------------------------------------------------

CREATE TABLE "HPEJK"."BAK_TEMP"
( "SESSION_RECID" NUMBER,
"START_TIME" DATE,
"END_TIME" DATE,
"INPUT_TYPE" VARCHAR2(13 BYTE),
"STATUS" VARCHAR2(23 BYTE),
"IP" VARCHAR2(20 BYTE),
"NOBAK" VARCHAR2(20 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Table D
--------------------------------------------------------

CREATE TABLE "HPEJK"."D"
( "TABLESPACE_NAME" VARCHAR2(30 BYTE),
"STATUS" VARCHAR2(9 BYTE),
"EXTENT_MANAGEMENT" VARCHAR2(10 BYTE),
"CONTENTS" VARCHAR2(9 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Table DBCHECK
--------------------------------------------------------

CREATE TABLE "HPEJK"."DBCHECK"
( "IP" VARCHAR2(600 BYTE),
"TIME" DATE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Table F
--------------------------------------------------------

CREATE TABLE "HPEJK"."F"
( "TABLESPACE_NAME" VARCHAR2(30 BYTE),
"BYTES" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Table HP_INFO
--------------------------------------------------------

CREATE TABLE "HPEJK"."HP_INFO"
( "IP" VARCHAR2(200 BYTE),
"DBLINKNAME" VARCHAR2(200 BYTE),
"OWNER" VARCHAR2(20 BYTE),
"CMDBID" VARCHAR2(20 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Table TS_INFO
--------------------------------------------------------

CREATE TABLE "HPEJK"."TS_INFO"
( "STATUS" VARCHAR2(9 BYTE),
"NAME" VARCHAR2(30 BYTE),
"TS_SIZE" NUMBER,
"FREE" NUMBER,
"USED" NUMBER,
"TIME" DATE,
"IP" VARCHAR2(20 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Table TS_TEMP
--------------------------------------------------------

CREATE TABLE "HPEJK"."TS_TEMP"
( "STATUS" VARCHAR2(9 BYTE),
"NAME" VARCHAR2(30 BYTE),
"TS_SIZE" NUMBER,
"FREE" NUMBER,
"USED" NUMBER,
"TIME" DATE,
"IP" VARCHAR2(20 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Index A_TABLESPACE
--------------------------------------------------------

CREATE INDEX "HPEJK"."A_TABLESPACE" ON "HPEJK"."A" ("TABLESPACE_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Index D_TABNAME
--------------------------------------------------------

CREATE INDEX "HPEJK"."D_TABNAME" ON "HPEJK"."D" ("TABLESPACE_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Index D_MAN
--------------------------------------------------------

CREATE INDEX "HPEJK"."D_MAN" ON "HPEJK"."D" ("EXTENT_MANAGEMENT")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Index D_CON
--------------------------------------------------------

CREATE INDEX "HPEJK"."D_CON" ON "HPEJK"."D" ("CONTENTS")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Index F_TABLESPACE
--------------------------------------------------------

CREATE INDEX "HPEJK"."F_TABLESPACE" ON "HPEJK"."F" ("TABLESPACE_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
-- Constraints for Table D
--------------------------------------------------------

ALTER TABLE "HPEJK"."D" MODIFY ("TABLESPACE_NAME" NOT NULL ENABLE);
--------------------------------------------------------
-- Constraints for Table TS_INFO
--------------------------------------------------------

ALTER TABLE "HPEJK"."TS_INFO" MODIFY ("NAME" NOT NULL ENABLE);
--------------------------------------------------------
-- Constraints for Table TS_TEMP
--------------------------------------------------------

ALTER TABLE "HPEJK"."TS_TEMP" MODIFY ("NAME" NOT NULL ENABLE);

=====================
=====================
--------------------------------------------------------
-- File created - Monday-September-26-2016
--------------------------------------------------------
--------------------------------------------------------
-- DDL for Procedure ASM
--------------------------------------------------------
set define off;

CREATE OR REPLACE PROCEDURE "HPEJK"."ASM" AS
v_host_ip varchar2(200);
v_host_name varchar2(200);
v_db_link_name hp_info.dblinkname%type;
--v_tb_name HP_INFO.asm%type;
v_sql varchar2(512);
v_count number;
cursor link IS select * from hp_info;
BEGIN
for linkname in link loop
v_db_link_name := linkname.dblinkname;
v_host_ip :=linkname.ip;
EXECUTE IMMEDIATE 'select count(*) from V$ASM_DISKgroup@'||v_db_link_name into v_count;
----- dbms_output.put_line(v_count);
if v_count>0 then
v_sql := 'insert into asm_temp(name,total_g,free_g) select NAME,round(TOTAL_MB/1024,3),round(FREE_MB/1024,3) from V$ASM_DISKGROUP@' ||v_db_link_name;
--- dbms_output.put_line('have asm');
--dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE 'truncate table ASM_temp';
EXECUTE IMMEDIATE v_sql;
update ASM_temp set ip=linkname.ip;
update asm_temp set time=sysdate;
insert into ASM_info select * from ASM_temp;
commit;
else
--dbms_output.put_line(v_count);
-- dbms_output.put_line('no asm');
EXECUTE IMMEDIATE 'truncate table ASM_temp';
insert into asm_temp(IP,NOASM) VALUES(linkname.ip,'NO ASM');
update asm_temp set time=sysdate;
insert into ASM_info select * from ASM_temp;
COMMIT;
end if;
end loop;
END asm;

/
--------------------------------------------------------
-- DDL for Procedure BAK
--------------------------------------------------------
set define off;

CREATE OR REPLACE PROCEDURE "HPEJK"."BAK" AS
v_host_ip varchar2(200);
v_host_name varchar2(200);
v_db_link_name hp_info.dblinkname%type;
--v_tb_name HP_INFO.asm%type;
v_sql varchar2(512);
v_count number;
cursor link IS select * from hp_info;
BEGIN
for linkname in link loop
v_db_link_name := linkname.dblinkname;
v_host_ip :=linkname.ip;
EXECUTE IMMEDIATE 'select count(*) from V$RMAN_BACKUP_JOB_DETAILS@'||v_db_link_name into v_count;
-- dbms_output.put_line(v_count);
if v_count>0 then
v_sql := 'insert into bak_temp(SESSION_RECID,START_TIME,END_TIME,INPUT_TYPE,status ) select SESSION_RECID,START_TIME,END_TIME,INPUT_TYPE,status from V$RMAN_BACKUP_JOB_DETAILS@'||v_db_link_name||' where START_TIME>sysdate-2';
EXECUTE IMMEDIATE 'truncate table BAK_temp';
EXECUTE IMMEDIATE v_sql;
update BAK_temp set ip=linkname.ip;
COMMIT;
insert into BAK_info select * from BAK_temp;
commit;
else
--- dbms_output.put_line(v_count);
--- dbms_output.put_line('no BAK');
EXECUTE IMMEDIATE 'truncate table BAK_temp';
insert into BAK_temp(IP,NOBAK) VALUES(linkname.ip,'NO BAK');
update BAK_temp set ip=linkname.ip;
COMMIT;
insert into BAK_info select * from BAK_temp;
COMMIT;
end if;
end loop;
END bak;

/
--------------------------------------------------------
-- DDL for Procedure HPECHECK
--------------------------------------------------------
set define off;

CREATE OR REPLACE PROCEDURE "HPEJK"."HPECHECK" AS
BEGIN
tsinfo();
bak();
asm();
END HPECHECK;

/
--------------------------------------------------------
-- DDL for Procedure TEST
--------------------------------------------------------
set define off;

CREATE OR REPLACE PROCEDURE "HPEJK"."TEST" AS
v_host_ip varchar2(200);
v_host_name varchar2(200);
v_db_link_name hp_info.dblinkname%type;
--v_tb_name HP_INFO.asm%type;
v_sql varchar2(512);
v_status varchar2(512);
v_count number;
cursor link IS select * from hp_info;
BEGIN
for linkname in link loop
v_db_link_name := linkname.dblinkname;
v_host_ip :=linkname.ip;
EXECUTE IMMEDIATE 'select status from v$instance@'||v_db_link_name into v_status;
-- dbms_output.put_line(v_status);
if v_status='OPEN' then
dbms_output.put_line(v_host_ip||' OPEN');
else
dbms_output.put_line(v_host_ip||' NOT OPEN');
dbms_output.put_line('not ok');
end if;
end loop;
EXCEPTION
WHEN OTHERS THEN
---DBMS_OUTPUT.PUT_LINE(v_host_ip||'---'||SQLERRM);
insert into dbcheck values(v_host_ip,sysdate);
commit;
END TEST;

/
--------------------------------------------------------
-- DDL for Procedure TSINFO
--------------------------------------------------------
set define off;

CREATE OR REPLACE PROCEDURE "HPEJK"."TSINFO" AS
v_host_ip varchar2(200);
v_host_name varchar2(200);
v_db_link_name hp_info.dblinkname%type;
f_sql varchar2(512);
a_sql varchar2(512);
d_sql varchar2(512);
cursor link IS select * from hp_info;
BEGIN
for linkname in link loop
v_db_link_name := linkname.dblinkname;
v_host_ip :=linkname.ip;
EXECUTE IMMEDIATE 'truncate table f';
EXECUTE IMMEDIATE 'truncate table a';
EXECUTE IMMEDIATE 'truncate table d';
f_sql :='insert into f select tablespace_name, sum(bytes) bytes from dba_free_space@'||v_db_link_name|| ' group by tablespace_name';
a_sql :='insert into a select tablespace_name, sum(bytes) bytes from dba_data_files@'||v_db_link_name|| ' group by tablespace_name';
d_sql :='insert into d select tablespace_name,status,extent_management,contents from sys.dba_tablespaces@'||v_db_link_name;
dbms_output.put_line(f_sql);
dbms_output.put_line(a_sql);
dbms_output.put_line(d_sql);
EXECUTE IMMEDIATE f_sql;
EXECUTE IMMEDIATE a_sql;
EXECUTE IMMEDIATE d_sql;
commit;
EXECUTE IMMEDIATE 'truncate table ts_temp';
EXECUTE IMMEDIATE 'insert into ts_temp(status,NAME,TS_SIZE,FREE,USED,TIME) select * from tsjk';
update ts_temp set ip=linkname.ip;
commit;
insert into ts_info select * from ts_temp;
commit;
end loop;
END TSINFO;

/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20747382/viewspace-2130419/，如需轉載，請註明出處，否則將追究法律責任。

相關文章