操作流程：

1、 創(chuàng)建表空間、用戶等，注意根據(jù)數(shù)據(jù)量大小，預(yù)估多add幾個(gè)datafile文件，否則會(huì)出現(xiàn)空間不足的情況

2、 備份文件恢復(fù)到數(shù)據(jù)庫(kù)中

3、 驗(yàn)證（掛測(cè)試業(yè)務(wù)或?qū)⑦€原后數(shù)據(jù)內(nèi)容與原庫(kù)做對(duì)比）

4、 重置表空間，為下一次恢復(fù)測(cè)試準(zhǔn)備

一、創(chuàng)建表空間、用戶等

/*第1步：創(chuàng)建臨時(shí)表空間 */

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步：創(chuàng)建數(shù)據(jù)表空間*/

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文件（根據(jù)實(shí)際數(shù)據(jù)量大小添加,一個(gè)為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步：創(chuàng)建用戶并指定表空間 */

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

/*第4步：給用戶授予權(quán)限 *

SQL>grant connect,resource,dba to dbuser ;

二、備份文件恢復(fù)到數(shù)據(jù)庫(kù)中

將備份數(shù)據(jù)庫(kù)文件里的數(shù)據(jù)導(dǎo)入指定的數(shù)據(jù)庫(kù)SampleDB 中,假設(shè)SampleDB 已存在該表,則不再導(dǎo)入;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

#導(dǎo)入部分表：

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

三、驗(yàn)證：對(duì)比數(shù)據(jù)

完全還原后，隨機(jī)抽取某表數(shù)據(jù)與原DB系統(tǒng)中表數(shù)據(jù)做對(duì)比

或還原部分表數(shù)據(jù)與DB系統(tǒng)中表數(shù)據(jù)做對(duì)比

四、重置表空間

#cat reset_tablespace.sh

#!/bin/bash

DB_CONN_STR='/ as sysdba'

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

echo "Usage: $0 TABLESPACE_NAME ADDFILENUM"

echo "TABLESPACE_NAME:要重置的表空間名稱，即已存在的表空間，重置會(huì)刪除表空間，請(qǐng)謹(jǐn)慎操作！"

echo "ADDFILENUM:新建表空間時(shí)指定需要附加的datafile文件數(shù)量，若只有一個(gè)datafile，此處填寫(xiě)0,若附加一個(gè)（即共2個(gè)），此處寫(xiě)1"

#刪除表空間，

if [ $# -ne 2 ]; then

echo "Usage: $0 TABLESPACE_NAME ADDFILENUM "

exit 1

fi

read -p $'此腳本會(huì)刪除指定名稱的表空間（若存在），后重建此表空間（不涉及臨時(shí)表空間）。\x0a 確定要執(zhí)行？ y:確認(rèn)，其他：退出 :' 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

#創(chuàng)建表空間

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}個(gè)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修復(fù)'SYSAUX';

select tablespace_name from dba_tablespaces ;

PROMPT

EOF

exit

oracle修復(fù)}

owner=$(getuser)

echo oracle修復(fù) "$owner"