Python 监控 Data Guard 脚本
之前弄过监控Data Guard 状态的SQL脚本。 这个Python 监控也就是在Python里调用那个SQL。 并将返回结果发送到相关的邮箱。
一. SQL 语句
保存为checkdgstatus.sql：
conn sys/pwd@orcl_pd as sysdba
column dest_name format a30
column destination format a20
column MEMBER format a45
column destination format a20
column TABLESPACE_NAME format a10
column FREE_RATE format a10
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set wrap off;
prompt **************************** 实 例 状 态 ************************************;
select instance_name,version,status,database_status from v$instance;
prompt **************************** 数据库状态 *************************************;
select name,log_mode,open_mode from v$database;
prompt **************************** 控制文件状态 ***********************************;
column name format a50
select status,name from v$controlfile;
prompt **************************** 日志文件状态 ***********************************;
select GROUP#,status,type,member from v$logfile;
prompt***************************** 归档目的地状态 *********************************;
select dest_name ,status,database_mode,destination from v$archive_dest_status where dest_id in ('1','2');
set heading off;
prompt ************数据库已连续运行天数*******************************************
select round(a.atime-b.startup_time)||' days ' from(select sysdate atime from dual) a,v$instance b;
set heading on;
prompt***************************** 会 话 数 *************************************;
select sessions_current,sessions_highwater from v$license;
prompt**************************** active sessions count **************************;
select count(*) "Active session count" from v$session where status='ACTIVE';
prompt**************************** total sessions count **************************;
select count(*) "Total session count" from v$session;
prompt**************************** top 30 big objects name ************************;
column OWNER format a10
column SEGMENT_NAME format a35
column SEGMENT_TYPE format a15
column SIZES format a10
SELECT * FROM
(
select OWNER, SEGMENT_NAME, SEGMENT_TYPE, round(BYTES / 1024 / 1024 / 1024,3)||'G' AS SIZES
from dba_segments
ORDER BY BYTES DESC)
WHERE ROWNUM<=30
;
prompt***************************** WANGGOUuser data size ****************************;
select sum(bytes)/1024/1024/1024||'G' "User Data Size" from dba_segments where owner='WANGOU';
prompt***************************** SUP data size ************************************;
select sum(bytes)/1024/1024/1024||'G' "User Data Size" from dba_segments where owner='SUP';
prompt***************************** DB size ******************************************;
select sum(bytes)/1024/1024/1024||'G' "DB Size" from dba_segments;
prompt***************************** total tablespace size ****************************;
select sum(bytes)/1024/1024/1024||'G' "Total Tablespace Size" from dba_data_files;
prompt***************************** last day archive log count ***********************;
select sequence#, completion_time from v$archived_log where completion_time>= trunc(sysdate-1) and completion_time<= trunc(sysdate) and dest_id=1;
prompt********************** 表空间监控********************;
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS "SUM_BLOCKS(K)",
SPACE - NVL (FREE_SPACE, 0) "USED_SPACE(M)",
ROUND( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --如果有临时表空间
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
NVL (FREE_SPACE, 0) "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1;
prompt **************************** 表空间OFFLINE(显示为空正常) ********************;
select tablespace_name ,status from dba_tablespaces where status='OFFLINE';
prompt **************************** SEQUENCE同步数 *********************************;
select max(sequence#)from v$log_history;
CONN sys/pwd@orcl_st as sysdba;
prompt **************************** 备库SEQUENCE同步数 *****************************;
select max(sequence#)from v$log_history;
prompt **************************** 备库日志未应用(显示为空正常) *******************;
select sequence#,applied from v$archived_log where applied='NO';
prompt **************************** 备库日志应用(显示最近十个日志) *****************;
select * from(select sequence#,applied from v$archived_log order by sequence# desc) where rownum<=10;
set time on
disconnect
exit
二. Python 脚本
checkdataguard.py
#!/usr/bin/python
#coding=gbk
import os
import pickle
import sys
import smtplib
def get_dg_data():
pipe = os.popen('/u01/app/oracle/product/10.2.0/db_1/bin/sqlplus /nolog @checkdbstatus.sql')
data = pipe.read().strip()
# print data
return data
def save(obj,fname='dgstatus.txt'):
# save to file
fp=open(fname, 'w')
fp.write(obj)
fp.close()
FROM_USER='tianlesoftware@vip.qq.com'
SMTP_SERVER='192.168.1.100'
EMAIL_USER='tianlesoftware'
EMAIL_PASSWD='pwd'
TO_USERS=['tianlesoftware@vip.qq.com',' tianlesoftware@vip.qq.com']
def mysendmail(fromaddr,toaddrs,subject,body):
server=smtplib.SMTP(SMTP_SERVER)
server.login(EMAIL_USER,EMAIL_PASSWD)
# sendmail 的touser 支持元组，我这里用了个for循环做了个判断。 不判断也行。
for toaddr in toaddrs:
print toaddr
msg = 'From: %s/nTo: %s/nSubject: %s/n/n%s/n' % (fromaddr, toaddr, subject, body)
server.sendmail(fromaddr,toaddr,msg)
server.quit()
body=get_dg_data()
save(body)
subject='192.168.88.199/100 Data Guard Status'
mysendmail(FROM_USER,TO_USERS,subject,body)
三. 将Python 添加到crontab
[oracle@qs-wg-db1 scripts]$ crontab -l
00 9,11,17 * * * export ORACLE_HOME='/u01/app/oracle/product/10.2.0/db_1' && cd /u02/scripts && /u01/scripts/checkdg.py >/u02/scripts/checkdg.log 2>&1 &
在这个地方浪费了不少时间，Python 脚本写很快， 到时这个crontab。 因为直接运行Python 脚本没有问题。 添加到crontab里后就会报错：
[oracle@qs-wg-db1 scripts]$ cat checkdg.log
Error 6 initializing SQL*Plus
Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
后来没办法，把环境变量也加到crontab 里了。总算搞定了。环境变量是小，带来的问题却不小。
Linux Crontab 定时任务 命令详解
http://blog.csdn.net/tianlesoftware/archive/2010/02/21/5315039.aspx
------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群：62697716(满); DBA2 群：62697977(满)
DBA3 群：62697850 DBA 超级群：63306533;
聊天 群：40132017
--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请
