操作流程：

1、 创建表空间、用户等，注意根据数据量大小，预估多add几个datafile文件，否则会出现空间不足的情况

2、 备份文件恢复到数据库中

3、 验证（挂测试业务或将还原后数据内容与原库做对比）

4、 重置表空间，为下一次恢复测试准备

一、创建表空间、用户等

/*第1步：创建临时表空间 */

SQL>create temporary tablespace dbuser_temp tempfile '/home/oracle/app/oracle/oradata/orcl/ dbuser_temp.dbf' size 50m autoextend on next 50m maxsize unlimited extent management local;

/*第2步：创建数据表空间*/

SQL>create tablespace dbuser logging datafile '/home/oracle/app/oracle/oradata/orcl/dbuser.dbf' size 50m autoextend on next 50m maxsize unlimited extent management local;

/*添加datafile文件（根据实际数据量大小添加,一个为32G，具体看DB_BLOCK_SIZE配置：2K = 8G、8K = 32G、16K = 64G、32K = 128G）*/

SQL>alter tablespace dbuser logging add datafile '/home/oracle/app/oracle/oradata/orcl/dbuser1.dbf' size 50m autoextend on next 50m maxsize unlimited extent management local;

SQL>alter tablespace dbuser logging add datafile '/home/oracle/app/oracle/oradata/orcl/dbuser2.dbf' size 50m autoextend on next 50m maxsize unlimited extent management local;

………………….

/*第3步：创建用户并指定表空间 */

SQL>create user dbuser identified by dbuser0769test default tablespace dbuser temporary tablespace dbuser_temp;

/*第4步：给用户授予权限 *

SQL>grant connect,resource,dba to dbuser ;

二、备份文件恢复到数据库中

将备份数据库文件里的数据导入指定的数据库SampleDB 中,假设SampleDB 已存在该表,则不再导入;imp dbuser/dbuser0769test@orcl file=/data/sampleDB.dmp full=y ignore=y#imp system/systempwd@orcl file=/home/oracle/sampledb.dmp fromuser=dbuser1 touser=dbuser2

#导入部分表：

imp dbuser/dbuser0769test@orcl file=/data/sampleDB.dmp ignore=y tables=（table1）

三、验证：对比数据

完全还原后，随机抽取某表数据与原DB系统中表数据做对比

或还原部分表数据与DB系统中表数据做对比

四、重置表空间

#cat reset_tablespace.sh

#!/bin/bash

DB_CONN_STR='/ as sysdba'

echo "#####################################################################"

echo "Usage: $0 TABLESPACE_NAME ADDFILENUM"

echo "TABLESPACE_NAME:要重置的表空间名称，即已存在的表空间，重置会删除表空间，请谨慎操作！"

echo "ADDFILENUM:新建表空间时指定需要附加的datafile文件数量，若只有一个datafile，此处填写0,若附加一个（即共2个），此处写1"

#删除表空间，

if [ $# -ne 2 ]; then

echo "Usage: $0 TABLESPACE_NAME ADDFILENUM "

exit 1

fi

read -p $'此脚本会删除指定名称的表空间（若存在），后重建此表空间（不涉及临时表空间）。\x0a 确定要执行？ y:确认，其他：退出 :' par

if [ "$par" != "y" ];then

exit 1

fi

ORACLE_BASE=/home/oracle/app/oracle

ORACLE_HOME=${ORACLE_BASE}/product/11.2.0/db_1

ORACLE_SID=orcl

ora_data=${ORACLE_BASE}/oradata

tablespace_name=$(echo $1 | tr '[a-z]' '[A-Z]')

datafile=$(echo $2)

echo $datafile

function gettablespace(){

sqlplus -s $DB_CONN_STR <

REM ------------------------------------------------------------

set pagesize 20

set feedback off

set verify off

set head on

alter session set nls_date_format='HH:MI:SS DD-MON-YY';

col host_name for a15

col instance_name format a11

col version format a15

col status format a8

col RAC for a5

col log_mode format a10

col platform_name format a20

set lines 150

PROMPT

--PROMPT---- Instance general information ----------

select tablespace_name from dba_tablespaces where tablespace_name='${tablespace_name}';

PROMPT

EOF

exit

}

getts=$(gettablespace |grep ${tablespace_name})

echo $getts

#删除表空间，

if [ "${getts}" = "${tablespace_name}" ]; then

wind_var=$(

sqlplus -s ${DB_CONN_STR} <

set heading off

drop tablespace ${tablespace_name} including contents and datafiles;

EXIT;

EOF

)

echo -e "\e[1;32m ${wind_var} \e[0m" #Direct display returns results

else

echo -e "\e[1;31m --------------------------------------- \e[0m"

echo -e "\e[1;31m The tablespace ${tablespace_name} not exits! \e[0m"

echo -e "\e[1;31m --------------------------------------- \e[0m"

exit 1

fi

#创建表空间

if [ $? -eq 0 ];then

wind_var1=$(

sqlplus -s ${DB_CONN_STR} <

set heading off

create tablespace ${tablespace_name} logging datafile '${ora_data}/${ORACLE_SID}/${tablespace_name}.dbf' size 50m autoextend on next 50m maxsize unlimited extent management local;

EXIT;

EOF

)

if [ $? -eq 0 ]; then

echo -e "\e[1;32m ${wind_var1} \e[0m" #Direct display returns results

#添加datafile文件，

if [ $datafile -ne 0 ];then

for i in `seq ${datafile}`

do

wind_var2=$(

sqlplus -s ${DB_CONN_STR} <

set heading off

alter tablespace ${tablespace_name} add datafile '${ora_data}/${ORACLE_SID}/${tablespace_name}${i}.dbf' size 50m autoextend on next 50m maxsize unlimited ;

EXIT;

EOF

)

done

fi

if [ $? -eq 0 ]; then

echo -e "\e[1;32m ${wind_var2} \e[0m" #Direct display returns results

echo -e "\e[1;32m 已附加${datafile}个datafile文件。 \e[0m" #Direct display returns results

exit 1

else

echo "add datafile fail."

fi

else

echo "create tablespace fail."

fi

fi

查询用户名及表空间

catcheck_oracle_user.sh

###########/exp/backup/export.sh

#!/bin/sh

#function:using data pump backup database

#####ORACLE PARATMETER ####################

export ORACLE_SID=orcl #$SID 为oracle sid

export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/db_1

export PATH=.:$ORACLE_HOME/bin:$PATH:.

export NLS_LANG="Simplified Chinese_china.AL32UTF8"

ACTION_TIME=`date +%Y_%m_%d`

DB_CONN_STR='/ as sysdba'

function getuser(){

sqlplus -s $DB_CONN_STR <

REM ------------------------------------------------------------------------

set pagesize 20

set feedback off

set verify off

set head on

alter session set nls_date_format='HH:MI:SS DD-MON-YY';

col host_name for a15

col instance_name format a11

col version format a15

col status format a8

col RAC for a5

col log_mode format a10

col platform_name format a20

set lines 150

PROMPT

--PROMPT--------------- Instance general information ------------------

select username,default_tablespace from dba_users where account_status='OPEN'AND DEFAULT_TABLESPACE !='SYSTEM'AND DEFAULT_TABLESPACE !=oracle修复'SYSAUX';

select tablespace_name from dba_tablespaces ;

PROMPT

EOF

exit

oracle修复}

owner=$(getuser)

echo oracle修复 "$owner"