系统上线及后期运维中都免不了给数据库文件备份,windows下的每天凌晨自动备份写成bat文件。
基本sql语句
–1、创建表空间SQL
create tablespace xxtest
logging
datafile 'D:\xxdb_tablespace\xxtest.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;
–2、创建用户SQL
create user ceshi
identified by ceshi
default tablespace xxtest
temporary tablespace TEMP;
–3、给用户赋予权限
grant connect,create any job,unlimited tablespace,resource, dba ,exp_full_database,imp_full_database to ceshi;
–4、导入语句
imp zhengshib/zhengshib@orcl full=y file=D:\zhengshi_20131009.dmp ignore=y
imp zhengshi/zhengshi@orcl full=y file=D:\backup\DMP\zhengshib_20131127_092450.DMP ignore=y
–5、导出语句
exp zhengshib/zhengshib@orcl file=D:\zhengshib_20131205.dmp owner=zhengshib
exp grcsp_uat/grcsp_uat@oracle file=D:\grcsp_uat165.dmp owner=grcsp_uat
–6、远程导入\导出
imp xxtest/xxtest@10.105.182.100:1521/orcl file=D:\20130731xx_xtt.dmp full=y;
exp zhengshi/@10.105.182.100:1521/orcl file=d:\xxtest_150_20130804.dmp owner=xxtest
imp zhengshi/zhengshi@10.105.182.100:1521/orcl file=D:\backup\DMP\zhengshib_20131127_092450.DMP full=y;
bat写法实例
icdb-backup.bat:
@echo off
REM ###########################################################
REM # Windows Server 2003下Oracle数据库自动备份批处理脚本 add by xm 20160401
REM ###########################################################
REM 取当前系统时间,可能因操作系统不同而取值不一样
set CURDATE=%date:~0,4%%date:~5,2%%date:~8,2%
set CURTIME=%time:~0,2%
REM 小时数如果小于10,则在前面补0
if "%CURTIME%"==" 0" set CURTIME=00
if "%CURTIME%"==" 1" set CURTIME=01
if "%CURTIME%"==" 2" set CURTIME=02
if "%CURTIME%"==" 3" set CURTIME=03
if "%CURTIME%"==" 4" set CURTIME=04
if "%CURTIME%"==" 5" set CURTIME=05
if "%CURTIME%"==" 6" set CURTIME=06
if "%CURTIME%"==" 7" set CURTIME=07
if "%CURTIME%"==" 8" set CURTIME=08
if "%CURTIME%"==" 9" set CURTIME=09
set CURTIME=%CURTIME%%time:~3,2%%time:~6,2%
REM 设置所有者、用户名和密码
set OWNER=xx
set USER=xx
set PASSWORD=xx
set PREFIX=orcl
REM 创建备份用目录,目录结构为backup/YYYYMMDD/
REM if not exist "%CURDATE%" mkdir %CURDATE%
set CURDIR=d:\backup\DMP\
set FILENAME=%CURDIR%\%OWNER%_%CURDATE%_%CURTIME%.DMP
set EXPLOG=%CURDIR%\%OWNER%_%CURDATE%_%CURTIME%_log.log
REM 调用ORACLE的exp命令导出用户数据
set ORACLE_HOME=D:\oracle\product\10.2.0\db_1
exp %USER%/%PASSWORD%@%PREFIX% file=%FILENAME% owner=%OWNER%
exit