/// <summary>
/// 获取每个会话的字符串
/// </summary>
/// <param name="scId"></param>
/// <returns></returns>
static string GetSessionConnStr(long scId)
{
StringBuilder sbConn = new StringBuilder();
using (var db = new DbMonitorEntities())
{
var sc = db.SessionConnection.Find(scId);
if (sc != null)
{
if (sc.SCDBType == "ORACLE")
{
sbConn.AppendFormat("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));",
sc.SCHostName, sc.SCPort, sc.SCServiceName.ToUpper());
sbConn.AppendFormat("Persist Security Info=True;User ID={0};Password={1};",
sc.SCUser, sc.SCPassword);
if (sc.SCRole.ToUpper() == "SYSDBA")
{
sbConn.Append("DBA Privilege=SYSDBA;");
}
}
else if (sc.SCDBType == "DM")
{
sbConn.AppendFormat("Server={0}:{1};User Id={2};PWD={3}",
sc.SCHostName, sc.SCPort, sc.SCUser, sc.SCPassword);
}
}
}
return(sbConn.ToString());
}
static void GrabDmData(MonitorManagement mm)
{
DateTime dtBeg;
DateTime dtEnd;
//从上次最大时间开始
dtBeg = DateTime.Parse(mm.MMLastTime);
dtEnd = DateTime.Now;
//采集SQL
StringBuilder sbSql = new StringBuilder();
sbSql.Append("SELECT a.USERNAME, ");
sbSql.Append("a.SCHNAME, ");
sbSql.Append("a.OBJNAME, ");
sbSql.Append("a.OPERATION, ");
sbSql.Append("a.SQL_TEXT, ");
sbSql.Append(" TO_CHAR(a.OPTIME,'yyyy-mm-dd HH24:MI:SS') TIMESTAMP, ");
sbSql.Append("s.SUBTYPE$ OBJTYPE ");
sbSql.Append("FROM SYSAUDITOR.V$AUDITRECORDS a ");
sbSql.Append("LEFT JOIN sysobjects s ");
sbSql.Append("ON a.SCHID = s.SCHID ");
sbSql.Append("WHERE SUCC_FLAG = 'Y' ");
sbSql.AppendFormat("AND a.OPTIME >= to_date('{0}', 'yyyy-mm-dd HH24:MI:SS') ", dtBeg.ToString("yyyy-MM-dd HH:mm:ss"));
sbSql.AppendFormat("AND a.OPTIME < to_date('{0}', 'yyyy-mm-dd HH24:MI:SS') ", dtEnd.ToString("yyyy-MM-dd HH:mm:ss"));
DataTable dt = null;
string connStr = GetSessionConnStr(mm.SCID.Value);
using (DmDAL dal = new DmDAL(connStr))
{
dt = dal.ExecuteQuery(sbSql.ToString());
}
var grabTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
using (var ctx = new DbMonitorEntities())
{
foreach (DataRow dr in dt.Rows)
{
ChangeLog log = new ChangeLog();
log.SCID = mm.SCID;
log.CLChangeEvent = dr["OPERATION"].ToString();
log.CLContent = "";
log.CLObjectName = dr["OBJNAME"].ToString();
log.CLSchema = dr["SCHNAME"].ToString();
log.CLObjectType = dr["OBJTYPE"].ToString();
log.CLSQL_Text = dr["SQL_TEXT"].ToString();
log.CLOperator = dr["USERNAME"].ToString();
log.CLChangeTime = dr["TIMESTAMP"].ToString();
log.CLGrabTime = grabTime;
ctx.ChangeLog.Add(log);
}
var editMM = ctx.MonitorManagement.Find(mm.ID);
editMM.MMLastTime = dtEnd.ToString("yyyy-MM-dd HH:mm:ss");
ctx.SaveChanges();
Console.WriteLine("id:{0} grab {1} items", mm.ID, dt.Rows.Count);
}
}
public bool IsUserLocked(string username)
{
using (var context = new DbMonitorEntities())
{
var user = (from u in context.User
where u.ULoginName == username
select u).FirstOrDefault();
if (user != null && user.UIsLock == 1)
{
return(true);
}
}
return(false);
}
public void ExecuteImport(long id)
{
using (var ctx = new DbMonitorEntities())
{
var me = ctx.MirrorExport.Find(id);
var dic = ctx.Dictionary.Where(d => d.DTypeCode == "DmExport" && d.DEnable == 1).ToList();
//导入到本地
var home = dic.Where(d => d.DCode == "dm_home").First().DName;
var backup_dir = dic.Where(d => d.DCode == "backup_dir").FirstOrDefault().DName;
var local_user = dic.Where(d => d.DCode == "user").FirstOrDefault().DName;
var local_pwd = dic.Where(d => d.DCode == "pwd").FirstOrDefault().DName;
var imp_log = me.MELogFile.Replace(".log", "_imp.log");
using (var dal = new DmDAL(connStr))
{
var sql = string.Format("SELECT COUNT(1) FROM DBA_USERS WHERE USERNAME='******'", me.MESchemas);
var cnt = Convert.ToInt32(dal.ExecuteScalar(sql));
if (cnt == 1)
{
sql = string.Format("DROP USER {0} CASCADE", me.MESchemas);
dal.ExecuteNonQuery(sql);
}
sql = string.Format("create user {0} identified by {0} default tablespace BACKUP", me.MESchemas);
dal.ExecuteNonQuery(sql);
}
StringBuilder sbExp = new StringBuilder();
sbExp.AppendFormat("%DM_HOME%\\bin\\dimp USERID={0}/{1} FILE={2} LOG={3} DIRECTORY={4}",
local_user, local_pwd, me.MEFileName, imp_log, backup_dir);
me.MEImportStatus = "开始导入";
me.MEImportTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
ctx.SaveChanges();
bool bImportOK = false;
bImportOK = ImportTask(home, backup_dir, imp_log, sbExp.ToString());
if (bImportOK)
{
me.MEImportStatus = "导入成功";
me.EditingTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
ctx.SaveChanges();
}
else
{
me.MEImportStatus = "导入失败";
me.EditingTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
ctx.SaveChanges();
}
}
}
public bool IsUserExisted(string username)
{
using (var context = new DbMonitorEntities())
{
var userCount = (from u in context.User
where u.ULoginName == username
select u).Count();
//用户存在
if (userCount > 0)
{
return(true);
}
}
return(false);
}
public bool Authenticate(string username, string password)
{
using (var context = new DbMonitorEntities())
{
var user = (from u in context.User
where u.ULoginName == username && u.UPassword == password
select u).FirstOrDefault();
//用户存在
if (user != null)
{
return(true);
}
}
return(false);
}
public static void Start()
{
using (var ctx = new DbMonitorEntities())
{
//加载数据
var dmDDL = ctx.Dictionary.Where(d => d.DTypeCode == "DmDDL" && d.DEnable == 1).ToList();
foreach (var d in dmDDL)
{
_dicDmDDL.Add(d.DCode, d.DName);
}
}
_timer = new Timer();
_timer.Interval = 1000;
_timer.Elapsed += _timer_Elapsed;
_timer.Start();
}
private static void _timer_Elapsed(object sender, ElapsedEventArgs e)
{
List <MonitorManagement> mms = null;
using (var ctx = new DbMonitorEntities())
{
mms = ctx.MonitorManagement.ToList();
foreach (var mm in mms)
{
var sc = ctx.SessionConnection.Find(mm.SCID);
if (sc != null && mm.MMOpen == 1)
{
ExecuteGrab(mm, sc.SCDBType);
}
}
}
}
static void GrabOracleData(MonitorManagement mm)
{
//采集SQL
StringBuilder sbSql = new StringBuilder();
sbSql.Append("SELECT INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS,DATABASE_STATUS FROM V$INSTANCE");
DataTable dt = null;
string connStr = DataGraber.GetSessionConnStr(mm.SCID.Value);
using (OracleDAL dal = new OracleDAL(connStr))
{
dt = dal.ExecuteQuery(sbSql.ToString());
}
var grabTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
using (var ctx = new DbMonitorEntities())
{
//把上一次数据设成历史数据
var ds = ctx.DatabaseStatus.Where(s => s.REALTIME == 1);
foreach (var s in ds)
{
s.REALTIME = 0;
s.EditingTime = grabTime;
}
ctx.SaveChanges();
foreach (DataRow dr in dt.Rows)
{
DatabaseStatus status = ctx.DatabaseStatus.Create();
status.SCID = mm.SCID;
status.INSTANCE_NAME = dr["INSTANCE_NAME"].ToString();
status.HOST_NAME = dr["HOST_NAME"].ToString();
status.VERSION = dr["VERSION"].ToString();
status.STARTUP_TIME = dr["STARTUP_TIME"].ToString();
status.STATUS = dr["STATUS"].ToString();
status.DATABASE_STATUS = dr["DATABASE_STATUS"].ToString();
status.REALTIME = 1;
status.CreationTime = grabTime;
ctx.DatabaseStatus.Add(status);
}
ctx.SaveChanges();
Console.WriteLine("id:{0} oracle-status-grab {1} items", mm.ID, dt.Rows.Count);
}
}
private static void T_Elapsed(object sender, ElapsedEventArgs e)
{
var s = DateTime.Now.ToShortTimeString();
List <MonitorManagement> mms = null;
using (var ctx = new DbMonitorEntities())
{
mms = ctx.MonitorManagement.ToList();
foreach (var mm in mms)
{
var sc = ctx.SessionConnection.Find(mm.SCID);
if (sc != null && mm.MMOpen == 1)
{
ExecuteGrab(mm, sc.SCDBType);
}
}
}
//Console.WriteLine(s);
}
public void ExecuteExport(long id)
{
try
{
MirrorExport me = null;
List <Dictionary> dic = null;
SessionConnection sc = null;
using (var ctx = new DbMonitorEntities())
{
me = ctx.MirrorExport.Find(id);
dic = ctx.Dictionary.Where(d => d.DTypeCode == "DmExport" && d.DEnable == 1).ToList();
sc = ctx.SessionConnection.Find(me.SCID);
me.MEStatus = "开始导出";
me.EditingTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
ctx.SaveChanges();
/*
* List<string> cmds = new List<string>();
* cmds.Add(string.Format("set DM_HOME={0}", dic.Where(d => d.DCode == "dm_home").First().DName));
* cmds.Add(@"set PATH=%path%;%DM_HOME%\bin");
*
* var backup_dir = dic.Where(d => d.DCode == "backup_dir").FirstOrDefault().DName;
*
* //使用StringBuilder注意参数之间的空格
* StringBuilder sbExp = new StringBuilder();
* sbExp.AppendFormat("%DM_HOME%\\bin\\dexp USERID={0}/{1}@{2}:{3} ",
* me.MEUser, me.MEPassword, sc.SCHostName, sc.SCPort);
* sbExp.AppendFormat("file={0} log={1} directory={2} schemas={3}",
* me.MEFileName, me.MELogFile, backup_dir, me.MESchemas);
* cmds.Add(sbExp.ToString());
* CmdHelper.Execute(cmds.ToArray());
*
* var logfile = Path.Combine(backup_dir, me.MELogFile);
* bool bExportOK = false;
* if (File.Exists(logfile))
* {
* string text = File.ReadAllText(logfile, EncodingType.GetType(logfile));
* Console.WriteLine(text);
*
* string pattern = @"共导出[\s\d]+个SCHEMA";
* if (Regex.IsMatch(text, pattern))
* {
* bExportOK = true;
* }
* }
* if (bExportOK)
* {
* me.MEStatus = "导出成功";
* me.EditingTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
* ctx.SaveChanges();
* }
* else
* {
* me.MEStatus = "导出失败";
* me.EditingTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
* ctx.SaveChanges();
* }
*
* //导入到本地
* var local_user = dic.Where(d => d.DCode == "user").FirstOrDefault().DName;
* var local_pwd = dic.Where(d => d.DCode == "pwd").FirstOrDefault().DName;
*/
var home = dic.Where(d => d.DCode == "dm_home").First().DName;
var backup_dir = dic.Where(d => d.DCode == "backup_dir").FirstOrDefault().DName;
var exp_log1 = me.MELogFile.Replace(".log", "_exp1.log");
//使用StringBuilder注意参数之间的空格
StringBuilder sbExp = new StringBuilder();
sbExp.AppendFormat("%DM_HOME%\\bin\\dexp USERID={0}/{1}@{2}:{3} ",
me.MEUser, me.MEPassword, sc.SCHostName, sc.SCPort);
sbExp.AppendFormat("file={0} log={1} directory={2} schemas={3}",
me.MEFileName, exp_log1, backup_dir, me.MESchemas);
bool bExportOK = ExportTask(home, backup_dir, exp_log1, sbExp.ToString());
if (bExportOK)
{
me.MEStatus = "远程导出成功";
me.EditingTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
ctx.SaveChanges();
//导入到本地
/* var local_user = dic.Where(d => d.DCode == "user").FirstOrDefault().DName;
* var local_pwd = dic.Where(d => d.DCode == "pwd").FirstOrDefault().DName;
* var imp_log = me.MELogFile.Replace(".log", "_exp2.log");
* using (var dal = new DmDAL(connStr))
* {
* var sql = string.Format("SELECT COUNT(1) FROM DBA_USERS WHERE USERNAME='******'", me.MESchemas);
* var cnt = Convert.ToInt32(dal.ExecuteScalar(sql));
* if(cnt == 1)
* {
* sql = string.Format("DROP USER {0} CASCADE", me.MESchemas);
* dal.ExecuteNonQuery(sql);
*
* }
* sql = string.Format("create user {0} identified by {0} default tablespace BACKUP", me.MESchemas);
* dal.ExecuteNonQuery(sql);
* }
* sbExp.Clear();
* sbExp.AppendFormat("%DM_HOME%\\bin\\dimp USERID={0}/{1} FILE={2} LOG={3} DIRECTORY={4}",
* local_user, local_pwd, me.MEFileName, imp_log, backup_dir);
*
* me.MEImportStatus = "开始自动导入";
* me.EditingTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
* ctx.SaveChanges();
*
* bool bImportOK = false;
* bImportOK = ImportTask(home, backup_dir, imp_log, sbExp.ToString());
* if (bImportOK)
* {
* me.MEImportStatus = "自动导入成功";
* me.EditingTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
* ctx.SaveChanges();
* }
* else
* {
* me.MEImportStatus = "自动导入失败";
* me.EditingTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
* ctx.SaveChanges();
* }*/
}
else
{
me.MEStatus = "远程导出失败";
me.EditingTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
ctx.SaveChanges();
}
}
}
catch (Exception ex)
{
LogHelper.WriteError(ex, string.Format("导出达梦数据库，会话ID：{0}", id));
}
}
static void GrabOracleData(MonitorManagement mm)
{
DateTime dtBeg;
DateTime dtEnd;
//首次采集
//if (string.IsNullOrWhiteSpace(mm.MMLastTime))
//{
// //从现在开始
// dtBeg = DateTime.Now;
//}
//else
//{
// //从上次最大时间开始
// dtBeg = DateTime.Parse(mm.MMLastTime);
//}
////采集范围
//dtEnd = dtBeg.AddMinutes(mm.MMTimeRange.Value);
//if(dtEnd > DateTime.Now)
//{
// dtEnd = DateTime.Now;
//}
//从上次最大时间开始
dtBeg = DateTime.Parse(mm.MMLastTime);
dtEnd = DateTime.Now;
//采集SQL
StringBuilder sbSql = new StringBuilder();
sbSql.Append("SELECT t.DB_USER,t.OBJECT_SCHEMA,t.OBJECT_NAME,t.STATEMENT_TYPE,t.SQL_TEXT,to_char(t.EXTENDED_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') TIMESTAMP,o.OBJECT_TYPE ");
sbSql.Append("FROM DBA_COMMON_AUDIT_TRAIL t ");
sbSql.Append("LEFT JOIN dba_objects o ");
sbSql.Append("ON t.OBJECT_SCHEMA = o.OWNER AND t.OBJECT_NAME = o.OBJECT_NAME ");
sbSql.Append("WHERE t.OBJECT_NAME is not null AND object_type is not null and returncode = 0 ");
sbSql.AppendFormat("and t.STATEMENT_TYPE not in('SELECT') ");
sbSql.AppendFormat("and t.EXTENDED_TIMESTAMP >= to_date('{0}','yyyy-mm-dd hh24:mi:ss') ", dtBeg.ToString("yyyy-MM-dd HH:mm:ss"));
sbSql.AppendFormat("and t.EXTENDED_TIMESTAMP < to_date('{0}','yyyy-mm-dd hh24:mi:ss') ", dtEnd.ToString("yyyy-MM-dd HH:mm:ss"));
DataTable dt = null;
string connStr = GetSessionConnStr(mm.SCID.Value);
using (OracleDAL dal = new OracleDAL(connStr))
{
dt = dal.ExecuteQuery(sbSql.ToString());
}
var grabTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
using (var ctx = new DbMonitorEntities())
{
foreach (DataRow dr in dt.Rows)
{
ChangeLog log = new ChangeLog();
log.SCID = mm.SCID;
log.CLChangeEvent = dr["STATEMENT_TYPE"].ToString();
log.CLContent = "";
log.CLObjectName = dr["OBJECT_NAME"].ToString();
log.CLSchema = dr["OBJECT_SCHEMA"].ToString();
log.CLObjectType = dr["OBJECT_TYPE"].ToString();
log.CLSQL_Text = dr["SQL_TEXT"].ToString();
log.CLOperator = dr["DB_USER"].ToString();
log.CLChangeTime = dr["TIMESTAMP"].ToString();
log.CLGrabTime = grabTime;
ctx.ChangeLog.Add(log);
}
var editMM = ctx.MonitorManagement.Find(mm.ID);
editMM.MMLastTime = dtEnd.ToString("yyyy-MM-dd HH:mm:ss");
ctx.SaveChanges();
Console.WriteLine("id:{0} grab {1} items", mm.ID, dt.Rows.Count);
}
}
public void ExecuteExport(long id)
{
using (var ctx = new DbMonitorEntities())
{
var me = ctx.MirrorExport.Find(id);
var dic = ctx.Dictionary.Where(d => d.DTypeCode == "OracleExport" && d.DEnable == 1).ToList();
var sc = ctx.SessionConnection.Find(me.SCID);
me.MEStatus = "开始导出";
me.EditingTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
ctx.SaveChanges();
string dblink = string.Format("dblink{0}", id),
user = me.MEUser,
pwd = me.MEPassword,
host = sc.SCHostName,
service = sc.SCServiceName;
long port = sc.SCPort.Value;
using (var dal = new OracleDAL(connStr))
{
try
{
StringBuilder sbSql = new StringBuilder();
//存在就先删除
var cnt = Convert.ToInt32(dal.ExecuteScalar(string.Format("select count(1) from dba_db_links where db_link='{0}'", dblink.ToUpper())));
if (cnt > 0)
{
dal.ExecuteNonQuery(string.Format("drop public database link {0}", dblink.ToUpper()));
}
//再添加
sbSql.AppendFormat("create public database link {0} connect to {1} identified by {2} using",
dblink, user, pwd);
sbSql.AppendFormat("'(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = {0})(PORT = {1})))(CONNECT_DATA =(SERVICE_NAME = {2})))'",
host, port, service);
dal.ExecuteNonQuery(sbSql.ToString());
}
catch (Exception ex)
{
me.MEStatus = "创建dblink失败";
me.EditingTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
ctx.SaveChanges();
LogHelper.WriteError(ex, string.Format("导出Oracle数据库时创建dblink失败，会话ID：{0}", id));
return;
}
}
me.MEStatus = "创建dblink成功";
me.EditingTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
ctx.SaveChanges();
List <string> cmds = new List <string>();
//变量设置是必须的
string directory = dic.Where(d => d.DCode == "directory").FirstOrDefault().DName,
oracle_base = dic.Where(d => d.DCode == "oracle_base").FirstOrDefault().DName,
oracle_home = dic.Where(d => d.DCode == "oracle_home").FirstOrDefault().DName,
oracle_sid = dic.Where(d => d.DCode == "oracle_sid").FirstOrDefault().DName,
user_local = dic.Where(d => d.DCode == "user").FirstOrDefault().DName,
pwd_local = dic.Where(d => d.DCode == "pwd").FirstOrDefault().DName;
cmds.Add(string.Format("set ORACLE_BASE={0}", oracle_base));
cmds.Add(string.Format("set ORACLE_HOME=%ORACLE_BASE%{0}", oracle_home));
cmds.Add(string.Format("set ORACLE_SID={0}", oracle_sid));
cmds.Add(@"set PATH=%path%;%ORACLE_HOME%\bin");
string file = me.MEFileName, log = me.MELogFile, schemas = me.MESchemas, timestamp = me.MEExportTime;
//使用StringBuilder注意参数之间的空格
StringBuilder sbExp = new StringBuilder();
sbExp.AppendFormat("%ORACLE_HOME%\\bin\\expdp {0}/{1} directory={2} dumpfile={3} logfile={4} network_link={5} schemas={6} ",
user_local, pwd_local, directory, file, log, dblink, schemas);
sbExp.AppendFormat("flashback_time=\\\"to_timestamp('{0}','yyyy-mm-dd hh24:mi:ss')\\\"", timestamp);
cmds.Add(sbExp.ToString());
CmdHelper.Execute(cmds.ToArray());
var backup_dir = dic.Where(d => d.DCode == "backup_dir").FirstOrDefault().DName;
var logfile = Path.Combine(backup_dir, log);
if (File.Exists(logfile))
{
string text = File.ReadAllText(logfile, EncodingType.GetType(logfile));
Console.WriteLine(text);
string pattern = @"作业[\s\S]+完成";
if (Regex.IsMatch(text, pattern))
{
me.MEStatus = "导出成功";
me.EditingTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
ctx.SaveChanges();
}
else
{
me.MEStatus = "导出失败";
me.EditingTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
ctx.SaveChanges();
}
}
else
{
me.MEStatus = "导出失败";
me.EditingTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
ctx.SaveChanges();
}
}
}
public void ExecuteImport(long id)
{
using (var ctx = new DbMonitorEntities())
{
var me = ctx.MirrorExport.Find(id);
var dic = ctx.Dictionary.Where(d => d.DTypeCode == "OracleExport" && d.DEnable == 1).ToList();
var sc = ctx.SessionConnection.Find(me.SCID);
me.MEImportStatus = "开始导入";
me.EditingTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
me.MEImportTime = me.EditingTime;
ctx.SaveChanges();
List <string> cmds = new List <string>();
//变量设置是必须的
string directory = dic.Where(d => d.DCode == "directory").FirstOrDefault().DName,
oracle_base = dic.Where(d => d.DCode == "oracle_base").FirstOrDefault().DName,
oracle_home = dic.Where(d => d.DCode == "oracle_home").FirstOrDefault().DName,
oracle_sid = dic.Where(d => d.DCode == "oracle_sid").FirstOrDefault().DName,
user_local = dic.Where(d => d.DCode == "user").FirstOrDefault().DName,
pwd_local = dic.Where(d => d.DCode == "pwd").FirstOrDefault().DName;
cmds.Add(string.Format("set ORACLE_BASE={0}", oracle_base));
cmds.Add(string.Format("set ORACLE_HOME=%ORACLE_BASE%{0}", oracle_home));
cmds.Add(string.Format("set ORACLE_SID={0}", oracle_sid));
cmds.Add(@"set PATH=%path%;%ORACLE_HOME%\bin");
string file = me.MEFileName, implog = me.MELogFile.Replace(".log", "") + "_imp.log";
//使用StringBuilder注意参数之间的空格
StringBuilder sbExp = new StringBuilder();
sbExp.AppendFormat("%ORACLE_HOME%\\bin\\impdp {0}/{1} directory={2} dumpfile={3} logfile={4}",
user_local, pwd_local, directory, file, implog);
cmds.Add(sbExp.ToString());
CmdHelper.Execute(cmds.ToArray());
var backup_dir = dic.Where(d => d.DCode == "backup_dir").FirstOrDefault().DName;
var logfile = Path.Combine(backup_dir, implog);
me.MEImportLogFile = implog;
bool bOK = false;
if (File.Exists(logfile))
{
string text = File.ReadAllText(logfile, EncodingType.GetType(logfile));
string pattern = @"作业[\s\S]+完成";
if (Regex.IsMatch(text, pattern))
{
bOK = true;
}
}
if (bOK)
{
me.MEImportStatus = "导入成功";
me.EditingTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
ctx.SaveChanges();
}
else
{
me.MEImportStatus = "导入失败";
me.EditingTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
ctx.SaveChanges();
}
}
}
static void GrabDmData(MonitorManagement mm)
{
DateTime dtBeg;
DateTime dtEnd;
//从上次最大时间开始
dtBeg = DateTime.Parse(mm.MMLastTime);
dtEnd = DateTime.Now;
//采集SQL
StringBuilder sbSql = new StringBuilder();
sbSql.Append("SELECT a.USERNAME, ");
sbSql.Append("a.SCHNAME, ");
sbSql.Append("a.OBJNAME, ");
sbSql.Append("a.OPERATION, ");
sbSql.Append("a.SQL_TEXT, ");
sbSql.Append(" TO_CHAR(a.OPTIME,'yyyy-mm-dd HH24:MI:SS') TIMESTAMP, ");
sbSql.Append("'' AS OBJTYPE ");
sbSql.Append("FROM SYSAUDITOR.V$AUDITRECORDS a ");
//sbSql.Append("LEFT JOIN sysobjects s ");
//sbSql.Append("ON a.SCHID = s.SCHID ");
//sbSql.Append("WHERE SUCC_FLAG = 'Y' ");
sbSql.Append("WHERE OPERATION NOT IN ('SELECT') ");
sbSql.AppendFormat("AND a.OPTIME >= to_date('{0}', 'yyyy-mm-dd HH24:MI:SS') ", dtBeg.ToString("yyyy-MM-dd HH:mm:ss"));
sbSql.AppendFormat("AND a.OPTIME < to_date('{0}', 'yyyy-mm-dd HH24:MI:SS') ", dtEnd.ToString("yyyy-MM-dd HH:mm:ss"));
DataTable dt = null;
string connStr = GetSessionConnStr(mm.SCID.Value);
using (DmDAL dal = new DmDAL(connStr))
{
dt = dal.ExecuteQuery(sbSql.ToString());
}
var grabTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
bool bAdd;
if (dt.Rows.Count > 0)
{
using (var ctx = new DbMonitorEntities())
{
foreach (DataRow dr in dt.Rows)
{
bAdd = true;
ChangeLog log = new ChangeLog();
log.SCID = mm.SCID;
log.CLChangeEvent = dr["OPERATION"].ToString();
log.CLContent = "";
log.CLObjectName = dr["OBJNAME"].ToString();
log.CLSchema = dr["SCHNAME"].ToString();
log.CLObjectType = dr["OBJTYPE"].ToString();
log.CLSQL_Text = dr["SQL_TEXT"].ToString();
log.CLOperator = dr["USERNAME"].ToString();
log.CLChangeTime = dr["TIMESTAMP"].ToString();
log.CLGrabTime = grabTime;
var sql_Upper = log.CLSQL_Text.ToUpper();
if (_dicDmDDL.ContainsKey(log.CLChangeEvent))
{
var objName = GetObjectNameFormDDL(sql_Upper, log.CLChangeEvent);
if (log.CLChangeEvent.Contains("CREATE"))
{
log.CLOldData = "";
log.CLNewData = objName;
}
else if (log.CLChangeEvent.Contains("ALTER"))
{
var pos = sql_Upper.IndexOf(objName);
log.CLNewData = sql_Upper.Substring(pos + objName.Length);
}
else if (log.CLChangeEvent.Contains("DROP"))
{
log.CLOldData = objName;
log.CLNewData = "";
}
log.CLChangeType = _dicDmDDL[log.CLChangeEvent];
switch (log.CLChangeEvent)
{
case "CREATE TABLE":
case "ALTER TABLE":
case "DROP TABLE":
case "TRUNCATE TABLE":
log.CLObjectType = "UTAB";
break;
case "CREATE VIEW":
case "DROP VIEW":
log.CLObjectType = "VIEW";
break;
case "CREATE PROCEDURE":
case "DROP PROCEDURE":
log.CLObjectType = "PROC";
break;
case "CREATE TRIGGER":
case "DROP TRIGGER":
log.CLObjectType = "TRIG";
break;
case "CREATE INDEX":
case "DROP INDEX":
log.CLObjectType = "INDEX";
break;
}
}
//数据操纵
else if (log.CLChangeEvent == "INSERT")
{
var pos = sql_Upper.IndexOf("VALUES") + 6;
var lastPos = sql_Upper.LastIndexOf(")");
log.CLOldData = "";
log.CLNewData = sql_Upper.Substring(pos, lastPos - pos);
log.CLChangeType = "插入数据";
log.CLObjectType = "UTAB";
}
else if (log.CLChangeEvent == "UPDATE")
{
var pos = sql_Upper.IndexOf("SET");
log.CLOldData = "";
log.CLNewData = sql_Upper.Substring(pos);
log.CLChangeType = "更新数据";
log.CLObjectType = "UTAB";
}
else if (log.CLChangeEvent == "DELETE")
{
var pos = sql_Upper.IndexOf(log.CLObjectName);
log.CLOldData = "";
log.CLNewData = sql_Upper.Substring(pos + log.CLObjectName.Length);
log.CLChangeType = "删除数据";
log.CLObjectType = "UTAB";
}
else
{
bAdd = false;
}
if (bAdd)
{
ctx.ChangeLog.Add(log);
}
//有数据才更新数据库
var editMM = ctx.MonitorManagement.Find(mm.ID);
editMM.MMLastTime = dtEnd.ToString("yyyy-MM-dd HH:mm:ss");
ctx.SaveChanges();
}
}
Console.WriteLine("id:{0} grab {1} items", mm.ID, dt.Rows.Count);
}
}
static void GrabOracleData(MonitorManagement mm)
{
DateTime dtBeg;
DateTime dtEnd;
//从上次最大时间开始
dtBeg = DateTime.Parse(mm.MMLastTime);
dtEnd = DateTime.Now;
//采集SQL
/*StringBuilder sbSql = new StringBuilder();
* sbSql.Append("SELECT t.DB_USER,t.OBJECT_SCHEMA,t.OBJECT_NAME,t.STATEMENT_TYPE,t.SQL_TEXT,to_char(t.EXTENDED_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') TIMESTAMP,o.OBJECT_TYPE ");
* sbSql.Append("FROM DBA_COMMON_AUDIT_TRAIL t ");
* sbSql.Append("LEFT JOIN dba_objects o ");
* sbSql.Append("ON t.OBJECT_SCHEMA = o.OWNER AND t.OBJECT_NAME = o.OBJECT_NAME ");
* sbSql.Append("WHERE t.OBJECT_NAME is not null AND object_type is not null and returncode = 0 ");
* sbSql.AppendFormat("and t.STATEMENT_TYPE not in('SELECT') ");
* sbSql.AppendFormat("and t.EXTENDED_TIMESTAMP >= to_date('{0}','yyyy-mm-dd hh24:mi:ss') ", dtBeg.ToString("yyyy-MM-dd HH:mm:ss"));
* sbSql.AppendFormat("and t.EXTENDED_TIMESTAMP < to_date('{0}','yyyy-mm-dd hh24:mi:ss') ", dtEnd.ToString("yyyy-MM-dd HH:mm:ss"));
*/
DataTable dt = null;
string connStr = GetSessionConnStr(mm.SCID.Value);
using (OracleDAL dal = new OracleDAL(connStr))
{
//dt = dal.ExecuteQuery(sbSql.ToString());
dt = dal.ExecuteProcedureQuery("DB_MONITOR.P_GetChangeLog",
new OracleParameter("begtime", OracleDbType.Varchar2, dtBeg.ToString("yyyy-MM-dd HH:mm:ss"), ParameterDirection.Input),
new OracleParameter("endtime", OracleDbType.Varchar2, dtEnd.ToString("yyyy-MM-dd HH:mm:ss"), ParameterDirection.Input),
new OracleParameter("out_data", OracleDbType.RefCursor, ParameterDirection.Output)
);
}
var grabTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
using (var ctx = new DbMonitorEntities())
{
foreach (DataRow dr in dt.Rows)
{
ChangeLog log = new ChangeLog();
log.SCID = mm.SCID;
log.CLChangeEvent = dr["STATEMENT_TYPE"].ToString();
log.CLContent = "";
log.CLObjectName = dr["OBJECT_NAME"].ToString();
log.CLSchema = dr["OBJECT_SCHEMA"].ToString();
log.CLObjectType = dr["OBJECT_TYPE"].ToString();
log.CLSQL_Text = dr["SQL_TEXT"].ToString();
log.CLOperator = dr["DB_USER"].ToString();
log.CLChangeTime = dr["OP_TIME"].ToString();
log.CLGrabTime = grabTime;
log.CLOldData = dr["OLD_DATA"].ToString();
log.CLNewData = dr["NEW_DATA"].ToString();
log.CLChangeType = dr["CHANGE_TYPE"].ToString();
ctx.ChangeLog.Add(log);
var editMM = ctx.MonitorManagement.Find(mm.ID);
editMM.MMLastTime = dtEnd.ToString("yyyy-MM-dd HH:mm:ss");
ctx.SaveChanges();
}
Console.WriteLine("id:{0} grab {1} items", mm.ID, dt.Rows.Count);
}
}