操作流程：

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"