恢复操作时,常常因为有其他客户端或进程占用某个数据库连接,导致该数据库恢复失败。
解决办法:在恢复前,强制中断所有的其他数据库连接,才能保证恢复数据正常。
1、SQL Server 数据库
1)创建一个存储过程 close_sqlserver_connect.sql,删除其他连接功能
create PROCEDURE p_closedblink
@dbname varchar(20)
AS
begin
declare @spid varchar(20)
declare #spid cursor for
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
open #spid
fetch next from #spid into @spid
while @@fetch_status=0
begin
exec('kill '+@spid)
fetch next from #spid into @spid
end
close #spid
deallocate #spid
end
2)在临时数据库tempdb中创建存储过程,删除数据库nemgr的客户端连接。
osql -S"127.0.0.1" -U%1 -P%2 -d"tempdb" -Q"DROP PROCEDURE p_closedblink" >>%global_log_file%
osql -"127.0.0.1" -U%1 -P%2 -d"tempdb" -i"%backup_path%\bin\close_sqlserver_connect.sql" >>%global_log_file%
osql -S"127.0.0.1" -U%1 -P%2 -d"tempdb" -Q"exec p_closedblink nemgr" >>%global_log_file%
osql -S"127.0.0.1" -U%1 -P%2 -d"tempdb" -Q"DROP PROCEDURE p_closedblink" >>%global_log_file%
2、Oracle 数据库
1) 查询会话ID SQL
String SQL_QUERY_SESSIONID =
"select SID, SERIAL# from v$session where username is not null and STATUS like 'INACTIVE'";
2) 执行删除会话 SQL
String SQL_INSERT_KILLSESSION = "alter system kill session ";
3) Java 实现代码
Connection conn = null;
try
{
conn = getOracleConnect();
final ResultSet rs = execQuery(conn, SQL_QUERY_SESSIONID);
final List<DataResult> lstData = new ArrayList<DataResult>();
while (rs.next())
{
final DataResult data = new DataResult();
data.setSid(rs.getInt("SID");
data.setSerial(rs.getInt("SERIAL#");
lstData.add(data);
}
for (final DataResult data : lstData)
{
final StringBuffer sb = new StringBuffer(SQL_INSERT_KILLSESSION);
sb.append('\'')
.append(Integer.toString(data.getSid()))
.append(',')
.append(Integer.toString(data.getSerial()))
.append('\'');
final int temp = execInsert(conn, sb.toString());
result &= (temp == 0) ? true : false;
}
}