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

引言：在数据库日常维护中，我们经常需要不定期或者定期从生产数据库备份数据恢复到开发或者测试服务器，一般的处理是编写好对应的脚本文件，然后手工或者自动调度执行。但是在调用脚本的过程中，我们经常会遇到一些特殊情况，比如有用户正在使用或者连接，这样就会导致脚本执行的错误，这个汇总的脚本包含了对这种情况的通用处理方法，本脚本包含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

 1 #!/bin/bash
 2 rm -f /opt/arm.dmp
 3 rm -f /opt/hrm.dmp
 4 rm -f /opt/cas.dmp
 5 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"
 6 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"
 7 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"
 8 
 9 scp /opt/arm.dmp root@192.168.201.12:/home/impscript/
10 scp /opt/hrm.dmp root@192.168.201.12:/home/impscript/
11 scp /opt/cas.dmp root@192.168.201.12:/home/impscript/

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

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

 1 create or replace procedure "SYS"."DROP3USER"
 2 as
 3 begin
 4 declare 
 5 cursor cur_user 
 6 is 
 7 SELECT SID,SERIAL# SERIAL FROM V$SESSION p WHERE USERNAME in ('ARM','CAS','HRM') and status='INACTIVE';
 8 row_user cur_user%rowtype;
 9 USID V$SESSION.SID%TYPE;
10 SERIAL V$SESSION.SERIAL#%TYPE;
11 begin
12 for row_user in cur_user loop
13 USID:=row_user.SID;
14 SERIAL:=row_user.SERIAL;
15 execute immediate 'ALTER SYSTEM KILL SESSION '''|| USID || ',' || SERIAL || '''';
16 end loop;
17 end;
18 
19 end;
20 /

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

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

 1 #!/bin/bash
 2 rm -f /home/impscript/cas.log
 3 rm -f /home/impscript/hrm.log
 4 rm -f /home/impscript/arm.log
 5 chmod -R 777 /home/impscript/cas.dmp
 6 chmod -R 777 /home/impscript/hrm.dmp
 7 chmod -R 777 /home/impscript/arm.dmp
 8 
 9 su - oracle -c "/u01/app/product/11.2/bin/sqlplus /nolog" <<EOF
10 connect / as sysdba
11 exec SYS.DROP3USER;
12 drop user ARM cascade;
13 drop user CAS cascade;
14 drop user HRM cascade;
15 drop tablespace BFGDATA including contents and datafiles;
16 create tablespace BFGDATA logging datafile '/u01/app/oradata/orcl/bfgdata01.dbf' size 200m autoextend on next 50m maxsize 20480m extent management local;
17 create user ARM identified by ARM default tablespace BFGDATA;
18 create user CAS identified by CAS default tablespace BFGDATA;
19 create user HRM identified by HRM default tablespace BFGDATA;
20 grant connect,resource,dba to ARM;
21 grant connect,resource,dba to CAS;
22 grant connect,resource,dba to HRM;
23 grant read,write on directory dump_dir to system;
24 exit
25 EOF
26 su - oracle -c "impdp system/orcl_2014@orcl DIRECTORY=dump_dir DUMPFILE=cas.dmp SCHEMAS=CAS logfile=cas.log PARALLEL=2"
27 su - oracle -c "impdp system/orcl_2014@orcl DIRECTORY=dump_dir DUMPFILE=hrm.dmp SCHEMAS=HRM logfile=hrm.log PARALLEL=2"
28 su - oracle -c "impdp system/orcl_2014@orcl DIRECTORY=dump_dir DUMPFILE=arm.dmp SCHEMAS=ARM logfile=arm.log PARALLEL=2"
29 rm -f /home/impscript/cas.dmp
30 rm -f /home/impscript/hrm.dmp
31 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

 1 ALTER proc [dbo].[DELDB]
 2 (
 3 @dbname SYSNAME
 4 )
 5 as
 6 begin
 7 
 8 
 9 --DECLARE @dbname SYSNAME 
10 --SET @dbname = 'databasename' --这个是要删除的数据库库名 
11 
12 DECLARE @s NVARCHAR(1000) 
13 DECLARE tb CURSOR LOCAL 
14 FOR 
15 SELECT s = 'kill ' + CAST(spid AS VARCHAR) 
16 FROM MASTER..sysprocesses 
17 WHERE dbid = DB_ID(@dbname) 
18 
19 OPEN tb 
20 FETCH NEXT FROM tb INTO @s 
21 WHILE @@fetch_status = 0 
22 BEGIN 
23 EXEC (@s) 
24 FETCH NEXT FROM tb INTO @s 
25 END 
26 CLOSE tb 
27 DEALLOCATE tb 
28 
29 --EXEC ('drop database [' + @dbname + ']') 
30 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';

(责任编辑：IT)

搜索

热门标签:

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