数据库的每日构建脚本完整版

引言：在数据库日常维护中，我们经常需要不定期或者定期从生产数据库备份数据恢复到开发或者测试服务器，一般的处理是编写好对应的脚本文件，然后手工或者自动调度执行。但是在调用脚本的过程中，我们经常会遇到一些特殊情况，比如有用户正在使用或者连接，这样就会导致脚本执行的错误，这个汇总的脚本包含了对这种情况的通用处理方法，本脚本包含oracle和sqlserver的处理脚本，基本实现了现有常用数据库的每日构建工作，希望对一些刚接触DBA工作的人有一些指导建议，虽然网上有一些零星的脚本，但是还没有发现有这么仔细和全部的内容，因此有必要共享出来。

 

说明：

oracle和sql的双库备份恢复
oracle:192.168.200.17 的CAS HRM ARM用户恢复到192.168.201.12
sql:192.168.200.117的ekp2和bfclogin库恢复到192.168.201.14

 工作原理：

oracle的工作原理：通过expdp导出用户数据然后scp到开发服务器然后impdp导入数据，前提是脚本删除用户和库，重建后导入

sql工作原理：backup库后通过ftp传输到开发服务器，然后restore库后，处理孤立账户问题。

两者都处理了kill连接用户的问题。

 

一、oracle库恢复
1.主库192.168.200.17设置：设置为每天晚上2点执行
vi /home/oracle/sysback.sh

#!/bin/bash
rm -f /opt/arm.dmp
rm -f /opt/hrm.dmp
rm -f /opt/cas.dmp
su - oracle -c "/home/oracle/oracle/product/10.2.0/db_1/bin/expdp system/s777a888 DIRECTORY=dump_dir DUMPFILE=arm.dmp SCHEMAS=ARM PARALLEL=2"
su - oracle -c "/home/oracle/oracle/product/10.2.0/db_1/bin/expdp system/s777a888 DIRECTORY=dump_dir DUMPFILE=hrm.dmp SCHEMAS=HRM PARALLEL=2"
su - oracle -c "/home/oracle/oracle/product/10.2.0/db_1/bin/expdp system/s777a888 DIRECTORY=dump_dir DUMPFILE=cas.dmp SCHEMAS=CAS PARALLEL=2"

scp /opt/arm.dmp root@192.168.201.12:/home/impscript/
scp /opt/hrm.dmp root@192.168.201.12:/home/impscript/
scp /opt/cas.dmp root@192.168.201.12:/home/impscript/

 

2.从库192.168.201.12 设置：每天3点执行

(1)首先创建删除连接用户的存储过程

create or replace procedure "SYS"."DROP3USER"
as
begin
declare 
cursor cur_user 
is 
SELECT SID,SERIAL# SERIAL FROM V$SESSION p WHERE USERNAME in ('ARM','CAS','HRM') and status='INACTIVE';
row_user cur_user%rowtype;
USID V$SESSION.SID%TYPE;
SERIAL V$SESSION.SERIAL#%TYPE;
begin
for row_user in cur_user loop
USID:=row_user.SID;
SERIAL:=row_user.SERIAL;
execute immediate 'ALTER SYSTEM KILL SESSION '''|| USID || ',' || SERIAL || '''';
end loop;
end;

end;
/

 

 

GRANT EXECUTE ON "SYS"."DROP3USER" TO "DBA";

 

(2)创建导入脚本
vi /home/impscript/imp.sh

#!/bin/bash
rm -f /home/impscript/cas.log
rm -f /home/impscript/hrm.log
rm -f /home/impscript/arm.log
chmod -R 777 /home/impscript/cas.dmp
chmod -R 777 /home/impscript/hrm.dmp
chmod -R 777 /home/impscript/arm.dmp

su - oracle -c "/u01/app/product/11.2/bin/sqlplus /nolog" <<EOF
connect / as sysdba
exec SYS.DROP3USER;
drop user ARM cascade;
drop user CAS cascade;
drop user HRM cascade;
drop tablespace BFGDATA including contents and datafiles;
create tablespace BFGDATA logging datafile '/u01/app/oradata/orcl/bfgdata01.dbf' size 200m autoextend on next 50m maxsize 20480m extent management local;
create user ARM identified by ARM default tablespace BFGDATA;
create user CAS identified by CAS default tablespace BFGDATA;
create user HRM identified by HRM default tablespace BFGDATA;
grant connect,resource,dba to ARM;
grant connect,resource,dba to CAS;
grant connect,resource,dba to HRM;
grant read,write on directory dump_dir to system;
exit
EOF
su - oracle -c "impdp system/orcl_2014@orcl DIRECTORY=dump_dir DUMPFILE=cas.dmp SCHEMAS=CAS logfile=cas.log PARALLEL=2"
su - oracle -c "impdp system/orcl_2014@orcl DIRECTORY=dump_dir DUMPFILE=hrm.dmp SCHEMAS=HRM logfile=hrm.log PARALLEL=2"
su - oracle -c "impdp system/orcl_2014@orcl DIRECTORY=dump_dir DUMPFILE=arm.dmp SCHEMAS=ARM logfile=arm.log PARALLEL=2"
rm -f /home/impscript/cas.dmp
rm -f /home/impscript/hrm.dmp
rm -f /home/impscript/arm.dmp

 

二、sqlserver设置
1.主库192.168.200.117 设置：设置每天2点执行
master库下建立存储过程dbfortransfer

CREATE proc dbfortransfer
as
declare @sql varchar(2000),@bkfile varchar(200)
begin

set @bkfile='D:\DB_BAK\transfer\ekp2.bak'
set @sql='master..xp_cmdshell ''del "'+@bkfile+'"'',No_output'
exec(@sql)

set @bkfile='D:\DB_BAK\transfer\BFCLogin.bak'
set @sql='master..xp_cmdshell ''del "'+@bkfile+'"'',No_output'
exec(@sql)

BACKUP DATABASE EKP2 TO DISK = 'D:\DB_BAK\transfer\ekp2.bak';
BACKUP DATABASE BFCLogin TO DISK = 'D:\DB_BAK\transfer\BFCLogin.bak';


end

 

2.主库192.168.200.117 ftp传送文件
(1)D:\DB_BAK\transfer下建立文件ftp.txt

open 192.168.201.14
Anonymous

binary
cd /
put D:\DB_BAK\transfer\ekp2.bak
put D:\DB_BAK\transfer\BFCLogin.bak
bye

　　

(2)D:\DB_BAK\transfer下建立文件transfer.bat

@echo OFF
ftp -s:ftp.txt

(3)建立windows计划任务，每天3点执行D:\DB_BAK\transfer\transfer.bat

(4)从库192.168.201.14在master建立删除DB和kill session存储过程DELDB

ALTER proc [dbo].[DELDB]
(
@dbname SYSNAME
)
as
begin


--DECLARE @dbname SYSNAME 
--SET @dbname = 'databasename' --这个是要删除的数据库库名 

DECLARE @s NVARCHAR(1000) 
DECLARE tb CURSOR LOCAL 
FOR 
SELECT s = 'kill ' + CAST(spid AS VARCHAR) 
FROM MASTER..sysprocesses 
WHERE dbid = DB_ID(@dbname) 

OPEN tb 
FETCH NEXT FROM tb INTO @s 
WHILE @@fetch_status = 0 
BEGIN 
EXEC (@s) 
FETCH NEXT FROM tb INTO @s 
END 
CLOSE tb 
DEALLOCATE tb 

--EXEC ('drop database [' + @dbname + ']') 
end    

 

(5)从库192.168.201.14在master建立恢复数据库存储过程IMPDATA

ALTER proc [dbo].[IMPDATA]
as
set nocount on
begin
--prepare for del
exec master.dbo.DELDB 'EKP2';
exec master.dbo.DELDB 'BFCLogin';
RESTORE DATABASE BFCLogin 
FROM DISK = 'E:\datafile\BFCLogin.bak' 
with replace;

RESTORE DATABASE EKP2 
FROM DISK = 'E:\datafile\ekp2.bak' 
with replace;

end

　　

(6)从库192.168.201.14建立job:每天3:40执行

第一步：exec IMPDATA
第二步：在ekp2库上
exec sp_change_users_login 'AUTO_FIX','EKP2';
exec sp_change_users_login 'Update_ONE','EKP2','EKP2';
第三步：在BFCLogin库上
exec sp_change_users_login 'AUTO_FIX','bfcloginSSO';
exec sp_change_users_login 'Update_ONE','bfcloginSSO','bfcloginSSO';