EXT

import os
import sys
from subprocess import Popen, PIPE
import commands
import re
import pprint
import math
import getopt

blockSize = 8192
fileMaxSize = blockSize * 2 ** 22 / 2** 30

def execSQL(sql):
proc = Popen(["sqlplus", "-S", "/", "as", "sysdba"],
stdout=PIPE, stdin=PIPE, stderr=PIPE)
formatSQL = """
set feedback off;
set verify off;
set echo off heading off underline off;
set pages 50000
"""
proc.stdin.write(formatSQL)
proc.stdin.write(sql)
(out, err) = proc.communicate()
if proc.returncode != 0:
print err
sys.exit(proc.returncode)
elif out.find('ORA') >= 0 or out.find('SP2-') >= 0:
print out
sys.exit("execute sql meet error")
else:
return out


def dataType():
"""get the db's storage type
Arguments:
oraSid {char} -- the ORACLE_SID in env
Returns:
char -- the function will retuan two value: 'ASM' or 'FILE'
"""
sql = "select /*+rule*/distinct substr(name,1,instr(name,'/',1,1)-1) from v$datafile;"
try:
resultTmp = execSQL(sql)
except Exception as e:
raise "db is not normal, please check db first!"
if resultTmp.strip() == "":
datatype = 'FILE'
else:
datatype = 'ASM'
return datatype


def getASMInfo():
sql1 = "select /*+rule*/distinct substr(name,1,instr(name,'/',1,1)-1) from v$datafile;"
resultName = execSQL(sql1).strip()[1:]
sql2 = "select NAME,round(FREE_MB/1024,2) free_g, round(TOTAL_MB/1024,2) total_g, round((TOTAL_MB-FREE_MB)/TOTAL_MB,2) use_pct from v$asm_diskgroup where NAME like upper('%%%s%%');" % (resultName)
asmInfo = {}
resultTmp = execSQL(sql2).split()
asmInfo['name'] = "+" + resultTmp[0]
asmInfo['size'] = resultTmp[2]
asmInfo['free'] = resultTmp[1]
asmInfo['used_pct'] = resultTmp[3]
return asmInfo


def toGB(size):
if re.match(r'(\d+\.?\d*)T', size):
return round(float(re.match(r'(\d+\.?\d*)T', size).group(1)) * 1000 * 1000 / 1024,2)
elif re.match(r'(\d+\.?\d*)M', size):
return round(float(re.match(r'(\d+\.?\d*)M', size).group(1)) / 1024,2)
elif re.match(r'(\d+\.?\d*)K', size):
return round(float(re.match(r'(\d+\.?\d*)K', size).group(1)) / 1024 / 1024,2)
elif re.match(r'(\d+\.?\d*)G', size):
return round(float(re.match(r'(\d+\.?\d*)G', size).group(1)) * 1000 / 1024,2)


def getFileUse(volume):
""" Returns:
dev total use free used_pct mount
"""
fileInfoRaw = commands.getoutput("df -h %s | grep data" % volume).split()
titles = ['dev', 'total', 'use', 'free', 'used_pct', 'mount']
fileInfo = dict(zip(titles, fileInfoRaw))
fileInfo['total'] = toGB(fileInfo['total'])
fileInfo['use'] = toGB(fileInfo['use'])
fileInfo['free'] = toGB(fileInfo['free'])
# fileInfo['can_add'] = math.floor((fileInfo['total'] - fileInfo['max_use']) / fileMaxSize) if (
# fileInfo['total'] - fileInfo['max_use'] > 0) else 0
return fileInfo


def getFileMaxUse(splitnum):
sql = """
select /*+rule*/substr(d.FILE_NAME, 1, instr(d.FILE_NAME, '/', 1, '{splitnum}') - 1) as volume,
round(sum(d.bytes)/1024/1024/1024,2) g_size,
round(sum(decode(d.MAXBYTES, 0, d.bytes, d.MAXBYTES)) / 1024 / 1024 / 1024,2) g_extend_size,
sum(decode(d.autoextensible,'YES',1,0)) autoex_files
from dba_data_files d
group by substr(d.FILE_NAME, 1, instr(d.FILE_NAME, '/', 1, '{splitnum}') - 1)
order by 3;
"""
sql = sql.format(splitnum = splitnum)
titles = ['use','max_use','autoex_files']
fieldnum = 4
resultTmp = execSQL(sql).split()
volumeInfo = {}
for i in range(0,int(math.ceil(len(resultTmp) / fieldnum))):
volume = resultTmp[i * 4]
volumeInfo[volume] = dict(zip(titles,[resultTmp[i * 4 +1],resultTmp[i * 4 +2],resultTmp[i * 4 +3]]))
volumeInfo[volume]['use'] = float(volumeInfo[volume]['use'])
volumeInfo[volume]['max_use'] = float(volumeInfo[volume]['max_use'])
volumeInfo[volume]['autoex_files'] = float(volumeInfo[volume]['autoex_files'])
return volumeInfo


def getAllFileInfo():
sql = "select /*+rule*/distinct substr(name,1,instr(name,'/',-1,1)-1) from v$datafile;"
volumes = execSQL(sql).strip().split('\n')
volumeInfos = {}
volumeset = set()
for i in volumes:
volumeset.add(re.match(r'(/.*?/data\d*)',i).group(1))
splitnum = len(list(volumeset)[0].split('/'))
fileMaxUse = getFileMaxUse(splitnum)
for volume in volumeset:
volumeInfos[volume] = getFileUse(volume)
volumeInfos[volume]['max_use'] = fileMaxUse[volume]['max_use']
volumeInfos[volume]['autoex_files'] = fileMaxUse[volume]['autoex_files']
formatCanAdd = lambda x:x if x>0 else 0
volumeInfos[volume]['can_add'] = formatCanAdd(math.ceil((volumeInfos[volume]['total'] - volumeInfos[volume]['max_use'] - 500) / fileMaxSize))
return volumeInfos


def getMaxTbsNum(tbsname):
""" need match type
/paic/orcl2/data/oradata/orcl2/users5
/paic/orcl2/data/oradata/orcl2/users2.dbf
/paic/orcl2/data/oradata/orcl2/users01.dbf
/paic/orcl2/data/oradata/orcl2/users_01.dbf
/paic/orcl2/data/oradata/orcl2/users_1_2.dbf
"""
text = """/paic/orcl2/data/oradata/orcl2/users5
/paic/orcl2/data/oradata/orcl2/users2.dbf
/paic/orcl2/data/oradata/orcl2/users01.dbf
/paic/orcl2/data/oradata/orcl2/users_01.dbf
/paic/orcl2/data/oradata/orcl2/users_1_2.dbf
/paic/orcl2/data/oradata/orcl2/users20.dbf"""
sql = "select /*+rule*/file_name from dba_data_files d where d.tablespace_name = upper('%s');" % (
tbsname)
try:
text = execSQL(sql)
except Exception as e:
print "no find datafile in tablespace %s" % tbsname
return 1
pattern = re.compile(r'/.*/%s_?(\d+)_?.*' % tbsname, re.I | re.M)
numlist = pattern.findall(text)
numlist.sort(key=int)
return int(numlist[-1]) if len(numlist) > 0 else 1


def getTbsInfo(tbsname,warn_pct=85,to_pct=80):
sql = """
SELECT round(b.maxbytes / 1024 / 1024 / 1024, 2) "maxbyes_GB",
round((total - FREE) / 1024 / 1024 / 1024, 2) "use_GB",
round((total - FREE) / total, 2) * 100 "use_pct",
round((total - FREE) / b.maxbytes, 2) * 100 "maxuse_pct",
num
FROM
(SELECT tablespace_name,
SUM(bytes) FREE
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
sum(CASE autoextensible WHEN 'YES' THEN maxbytes ELSE bytes END) maxbytes,
SUM(bytes) total,
count(file_id) num
FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = upper('{tbsname}');
"""
sql2 = "select t.BIGFILE from dba_tablespaces t where t.TABLESPACE_NAME = '{tbsname}';"
sql = sql.format(tbsname=tbsname)
sql2 = sql2.format(tbsname=tbsname)
titles = ['maxbyes_GB', 'use_GB', 'use_pct', 'maxuse_pct', 'num']
tbsinfo = dict(zip(titles, map(float, (execSQL(sql).split()))))
tbsinfo['isBigFile'] = execSQL(sql2).strip()
# tbsinfo ={'use_GB': 900, 'maxuse_pct': 93.75, 'num': 30, 'maxbyes_GB': 960, 'use_pct': 98}
if len(tbsinfo) != 0:
tbsinfo['need_add'] = math.ceil(
(tbsinfo['use_GB'] / to_pct - tbsinfo['maxbyes_GB']) / fileMaxSize) if tbsinfo['maxuse_pct'] > warn_pct else 0
else:
exit("no find tbs %s" % tbsname)
return tbsinfo


def genAddFileSql(tbsname, begin, num, volume):
oraSid = os.environ.get('ORACLE_SID').lower()
if(re.match(r'\D+\d+', tbsname)):
sql = "alter tablespace {tbsname} add datafile '{volume}/oradata/{oraSid}/{tbsname}_{fileNo}.dbf' size 100m autoextend on next 100m;"
else:
sql = "alter tablespace {tbsname} add datafile '{volume}/oradata/{oraSid}/{tbsname}{fileNo}.dbf' size 100m autoextend on next 100m;"
sqlList = []
current = int(begin)
end = begin + num
for i in range(0, int(num)):
if current >= end:
break
sqlList.append(sql.format(oraSid=oraSid,tbsname=tbsname,volume=volume, fileNo=current))
current += 1
return sqlList


def addFileSql(tbsname,limit, warn_pct=84, to_pct=0.8):
tbsinfo = getTbsInfo(tbsname, warn_pct, to_pct)
need_add = tbsinfo['need_add'] if tbsinfo['need_add'] < limit else limit
if tbsinfo['num'] >= 1023:
print("the number of %s's datafile reache 1024, can't add datafile" % tbsname)
elif tbsinfo['num'] + need_add >= 1023:
print("the number of %s's datafile will reache 1024, please notice!!" % tbsname)
need_add = (need_add if (need_add + tbsinfo['num']) <= 1023 else (1023 - tbsinfo['num']))
begin = getMaxTbsNum(tbsname) + 1
volumeInfos = getAllFileInfo()
sqlList = []
for i in volumeInfos.keys():
if volumeInfos[i]['can_add'] > 0 and (need_add - volumeInfos[i]['can_add']) >= 0:
sqlList.extend(genAddFileSql(tbsname,begin,volumeInfos[i]['can_add'],volumeInfos[i]['mount']))
begin = (begin + volumeInfos[i]['can_add'])
need_add = need_add - volumeInfos[i]['can_add']
elif volumeInfos[i]['can_add'] > 0 and (need_add - volumeInfos[i]['can_add']) < 0 and need_add > 0:
sqlList.extend(genAddFileSql(tbsname,begin,need_add,volumeInfos[i]['mount']))
need_add = 0
if need_add > 0:
print "the left of need_add for %s is %d" % (tbsname, need_add)
print "there is no enough space volume to add datafile, please request for extend volume!!"
pprint.pprint(volumeInfos)
return '\n'.join(sqlList)



def genAddAsmSql(tbsname, num, asm):
sql = """
begin
for i in 1..{num} loop
execute immediate 'alter tablespace {tbsname} add datafile ''{asm}'' size 100m autoextend on next 100m';
end loop;
end;
/
"""
sql = sql.format(tbsname=tbsname,num=num,asm=asm)
return sql

def addAsmSql(tbsname,limit, warn_pct=84,to_pct=0.8):
tbsinfo = getTbsInfo(tbsname,warn_pct,to_pct)
need_add = tbsinfo['need_add'] if tbsinfo['need_add'] < limit else limit
if tbsinfo['num'] >= 1023:
print("the number of %s's datafile reache 1024, can't add datafile" % tbsname)
elif tbsinfo['num'] + need_add >= 1023:
print("the number of %s's datafile will reache 1024, please notice!!" % tbsname)
need_add = (need_add if (need_add + tbsinfo['num']) <= 1023 else (1023 - tbsinfo['num']))
asmInfo = getASMInfo()
num = int(tbsinfo['need_add']) if int(tbsinfo['need_add']) < limit else limit
if float(asmInfo['used_pct']) > 0.9:
print "%s used_pct is %s%%, please request extend ASM!!" % (asmInfo['name'],asmInfo['used_pct'])

return genAddAsmSql(tbsname,num,asmInfo['name'])

def isDbFiles():
sql1 = "select /*+rule*/ count(1) from dba_data_files;"
sql2 = "select value from v$parameter where name = 'db_files';"
num1 = int(execSQL(sql1).strip())
num2 = int(execSQL(sql2).strip())
if (num1 < num2 and (num2-num1)/num2 >0.9):
print "the limit of db_files will reach, please contact DA!!"
limit = num2 - num1 - 50 if (num2 - num1 - 50) > 0 else 0
return limit

def main(argv):
print argv
datatype = dataType()
warn_pct = 84
to_pct = 0.8
if len(argv) == 1:
tbsname = argv[0]
elif len(argv) == 2:
tbsname = argv[0]
warn_pct = int(argv[1]) if argv[1] > 80 else 80
elif len(argv) == 3:
tbsname = argv[0]
warn_pct = int(argv[1]) if argv[1] > 80 else 80
to_pct = float(argv[2]) if argv[2] * 100 > warn_pct else warn_pct
else:
tbsname = None
if tbsname == None:
print("please give me argv which is tbsname")
tbsname = raw_input("tbsname: ")

tbsinfo = getTbsInfo(tbsname,warn_pct,to_pct)
if tbsname == None:
exit("no find tbs %s" %(tbsname))
if tbsinfo['num'] >= 1023:
pprint.pprint(tbsinfo)
exit("the number of %s's datafile is reached 1024, can't add datafile" % tbsname)
if tbsinfo['isBigFile'] == 'YES':
pprint.pprint(tbsinfo)
exit("%s is bigfile tablespace, cant't add datafile!!" % tbsname)


limit = isDbFiles()
global blockSize
blockSize= int(execSQL("select value from v$parameter p where p.NAME like '%%db_block_size%%';"))
global fileMaxSize
fileMaxSize = blockSize * 2 ** 22 / 2** 30

print "%s info: " % tbsname
pprint.pprint(tbsinfo)
if datatype == 'ASM':
print "ASM info: "
pprint.pprint(getASMInfo())
print "add datafile commands:"
print addAsmSql(tbsname,limit,warn_pct)

elif datatype == 'FILE':
print "add datafile commands:"
print addFileSql(tbsname,limit,warn_pct)

if __name__ == '__main__':
main(sys.argv[1:])