oracle环境备份,oracle正式环境备份恢复到测试环境的一个方便方案

1、从生产库上导出标准格式(username_YYYYMMDD.dmp)的备份

edw_exp_date.sh

DATE=`date +%Y%m%d`;export DATE

exp edw/edw file=/ODS-DATA/DW_DMP/edw_$DATE.dmp log=/ODS-DATA/DW_DMP/edw_exp_$DATE.log OWNER=edw

2、用ftp或U盘将备份传到测试库

根据环境不同，采用合适的方式

3、重建测试环境用户

create_edw.sh

sqlplus bdw/bdw<

drop user EDW cascade;

create user EDW

identified by EDW

default tablespace BDW

temporary tablespace TEMP

profile DEFAULT;

grant connect to EDW;

grant dba to EDW;

grant resource to EDW;

grant create any table to EDW;

grant delete any table to EDW;

grant drop any table to EDW;

grant insert any table to EDW;

grant select any table to EDW;

grant unlimited tablespace to EDW;

grant update any table to EDW;

exit

!

4、向测试库导入生产备份的脚本

edw_imp_to_edw.sh

echo "please input date for imp: /c"

read DATE

imp edw/edw file=/ODS-DATA/DW_DMP/edw_$DATE.dmp log=/ODS-DATA/DW_DMP/edw_imp_$DATE.log fromuser=EDW touser=edw

5、另一种可变方式

生成usernameYYYYMMDD用户，将备分导入，脚本如下：

edw_date_imp.sh

echo "please input date for imp: /c"

read DATE

imp edw$DATE/edw file=/ODS-DATA/DW_DMP/edw_$DATE.dmp log=/ODS-DATA/DW_DMP/edw_imp_$DATE.log fromuser=EDW touser=edw$DATE

6、根据实际需要，灵活处置。