这个案例跟前两个案例对比：
用不完全恢复找回被误删除表空间（一） http://blog.itpub.net/12238525/viewspace-1211078/
用不完全恢复找回被误删除表空间（二） http://blog.itpub.net/12238525/viewspace-1211151/

创建表空间NEWTS之前，有数据库的备份（包含所有的数据文件和控制文件）。然后，创建了NEWTS表空间。之后， NEWTS表空间被误删除。
现在想用不完全恢复恢复找回NEWTS表空间。(注：NEWTS表空间没有备份这是跟用不完全恢复找回被误删除表空间（二）不同地方）

点击(此处)折叠或打开

sys@BJDB>select tablespace_name from dba_tablespaces; -- 备份前，没有NEWTS表空间
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMPTS1
TS_ASSM
SMALL_UNDO
sys@BJDB>@/home/oracle/scripts/hot_backup.sql -- 备份脚本备份所以的数据文件和控制文件（注意这时没有NETS表空间）

创建NEWTS 表空间

点击(此处)折叠或打开

sys@BJDB>create tablespace newts datafile \'/oradata/BJDB/newts01.dbf\' size 50M; -- 创建NEWTS表空间
Tablespace created.
sys@BJDB>create table scott.tab_newts -- NEWTS表空间下创建一个测试表
2 tablespace newts
3 as select * from scott.dept;

该表空间被误操作 drop tablespace

点击(此处)折叠或打开

sys@BJDB>select current_scn from v$database;
CURRENT_SCN
-----------
8489906
sys@BJDB>drop tablespace newts including contents and datafiles;
Tablespace dropped.

处理：
1，先把实例启动到 nomount状态。
2，转储备份的控制文件，然后加载数据库

点击(此处)折叠或打开

[oracle@Redhat55 hot]$ cp /u02/backup/BJDB/hot/control.backup /oradata/BJDB/control01.ctl -- 转储备份的控制文件
[oracle@Redhat55 hot]$ cp /u02/backup/BJDB/hot/control.backup /oradata/BJDB/control02.ctl
sys@BJDB>alter database mount;
Database altered.
sys@BJDB>select name from v$tablespace; -- 未识别 NEWTS表空间，这是因为备份Controlfile时，还没有NEWTS表空间
NAME
----------------------------------------------------------
SYSTEM
SYSAUX
TS_ASSM
SMALL_UNDO
TEMPTS1

3，转储所有的数据文件，开始做不完全恢复

点击(此处)折叠或打开

[oracle@Redhat55 hot]$ cp /u02/backup/BJDB/hot/*.dbf /oradata/BJDB/ -- 没有 NEWTS表空间的备份
sys@BJDB>recover database until change 8489906 using backup controlfile;
ORA-00279: change 8488811 generated at 07/06/2014 23:22:37 needed for thread 1
ORA-00289: suggestion : /u02/backup/BJDB/disk1/1_2_852246497.dbf
ORA-00280: change 8488811 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 3: \'/oradata/BJDB/newts01.dbf\' -- 恢复的过程中报错找不到数据文件 /oradata/BJDB/newts01.dbf
ORA-01112: media recovery not started
sys@BJDB>select name from v$tablespace;
NAME
----------------------------------------------------------
SYSTEM
SYSAUX
NEWTS
TS_ASSM
SMALL_UNDO
TEMPTS1
6 rows selected.
sys@BJDB>select name from v$datafile;
NAME
----------------------------------------------------------
/oradata/BJDB/system01.dbf
/oradata/BJDB/sysaux01.dbf
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00003 -- 正确的数据文件应该是 /oradata/BJDB/newts01.dbf
/oradata/BJDB/ts_assm01.dbf
/oradata/BJDB/small_undo01.dbf

4，由于 /oradata/BJDB/newts01.dbf 没有做备份，我们需要create一下该数据文件

点击(此处)折叠或打开

sys@BJDB>alter database create datafile \'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00003\'
as \'/oradata/BJDB/newts01.dbf\'; -- 重新添加数据文件 /oradata/BJDB/newts01.dbf\
Database altered.
sys@BJDB>select name from v$datafile;
NAME
----------------------------------------------------------
/oradata/BJDB/system01.dbf
/oradata/BJDB/sysaux01.dbf
/oradata/BJDB/newts01.dbf -- 文件名被该回来了
/oradata/BJDB/ts_assm01.dbf
/oradata/BJDB/small_undo01.dbf

6，再重新做不完全恢复

点击(此处)折叠或打开

sys@BJDB>recover database until change 8489906 using backup controlfile;
ORA-00279: change 8488811 generated at 07/06/2014 23:22:37 needed for thread 1
ORA-00289: suggestion : /u02/backup/BJDB/disk1/1_2_852246497.dbf
ORA-00280: change 8488811 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 8489905 generated at 07/06/2014 23:28:54 needed for thread 1
ORA-00289: suggestion : /u02/backup/BJDB/disk1/1_3_852246497.dbf
ORA-00280: change 8489905 for thread 1 is in sequence #3
ORA-00278: log file \'/u02/backup/BJDB/disk1/1_2_852246497.dbf\' no longer needed for this recovery
Log applied.
Media recovery complete.

7，用resetlogs 方式打开数据库

点击(此处)折叠或打开

sys@BJDB>alter database open resetlogs;
Database altered.

8，验证恢复效果

点击(此处)折叠或打开

sys@BJDB>select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMPTS1
TS_ASSM
SMALL_UNDO
NEWTS
6 rows selected.
sys@BJDB>select file_name from dba_data_files;
FILE_NAME
-------------------------------------------------
/oradata/BJDB/small_undo01.dbf
/oradata/BJDB/ts_assm01.dbf
/oradata/BJDB/newts01.dbf
/oradata/BJDB/sysaux01.dbf
/oradata/BJDB/system01.dbf
sys@BJDB>select * from scott.tab_newts; -- NEWTS下的表被找回
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

ITPub博客

用不完全恢复找回被误删除表空间（三）

最新文章