用户管理热备份还原drop掉的表空间(不完全恢复的情况)

场景描述:
时间点12:00, 全库备份
12:07， 创建表空间tt
12:11， 在表空间tt上建表tab_a并插入数据
12:14， 成人用品店 www.xfqiao.com误删表空间tt
有全部的日志文件

问题，能否在没有tt备份的前提下恢复表tb_a数据？利用rman是否可行？

12:00:42 SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 65
Next log sequence to archive 67
Current log sequence 67
12:00:53 SQL>
接下来全库备份(用户管理热备份),过程省略...
看下当前表空间情况
12:07:49 SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE
IDX
EYGLE
TESTING_LMT_ASSM
TESTING_LMT_MSSM
LDB
11 rows selected.
添加一个表空间用来测试
12:07:55 SQL> create tablespace tt datafile 'D:\oracle\oradata\orcl\tt01.dbf' size 10m;
Tablespace created.
12:09:50 SQL> create table tab_a(id int ,name varchar2(10)) tablespace tt;
Table created.
12:11:30 SQL> insert into tab_a values(1,'jack');
1 row created.
12:11:40 SQL> commit;
Commit complete.
12:11:44 SQL> select *from tab_a;
ID NAME
---------- ----------
1 jack
删除表空间
12:14:19 SQL> drop tablespace tt including contents and datafiles;
Tablespace dropped.
表空间tt已不存在
12:24:29 SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE
IDX
EYGLE
TESTING_LMT_ASSM
TESTING_LMT_MSSM
LDB
11 rows selected.
二、恢复
使用之前的备份来恢复表空间tt
1)关闭数据库
12:29:53 SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
2)把之前备份的控制文件和所有数据文件还原
3)启动到mount
12:32:09 SQL> startup mount;
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1248768 bytes
Variable Size 83886592 bytes
Database Buffers 222298112 bytes
Redo Buffers 7139328 bytes
Database mounted.
查看scn
SQL> select file#,checkpoint_change# from v$datafile; --控制文件读取


FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 8140465
2 8140487
3 8140500
4 8140526
5 8140580
6 8140600
7 8140631
8 8140644
9 8140657
10 8140600
11 8140670


11 rows selected.
SQL> select file#,checkpoint_change# from v$datafile_header; --数据文件读取

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 8140465
2 8140487
3 8140500
4 8140526
5 8140580
6 8140600
7 8140631
8 8140644
9 8140657
10 8140600
11 8140670


11 rows selected.
数据库要打开，这两个SCN号一定要相同，但两个SCN号相同数据库不一定能打开
4)使用备份的控制文件恢复
SQL> recover database using backup controlfile;
ORA-00279: change 8140465 generated at 04/19/2014 12:02:56 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_04_19\O1_MF_1_
67_%U_.ARC
ORA-00280: change 8140465 for thread 1 is in sequence #67
Specify log: {=suggested | filename | AUTO | CANCEL}
D:\oracle\oradata\orcl\redo03.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 12: 'D:\ORACLE\ORADATA\ORCL\TT01.DBF'

ORA-01112: media recovery not started

SQL> select * from v$recover_file;


FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ------------
1 ONLINE ONLINE 8140780 19-APR-14
2 ONLINE ONLINE 8140780 19-APR-14
3 ONLINE ONLINE 8140780 19-APR-14
4 ONLINE ONLINE 8140780 19-APR-14
5 ONLINE ONLINE 8140780 19-APR-14
6 ONLINE ONLINE 8140780 19-APR-14
7 ONLINE ONLINE 8140780 19-APR-14
8 ONLINE ONLINE 8140780 19-APR-14
9 ONLINE ONLINE 8140780 19-APR-14
10 ONLINE ONLINE 8140780 19-APR-14
11 ONLINE ONLINE 8140780 19-APR-14


FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ------------
12 ONLINE ONLINE FILE MISSING 0


12 rows selected.
5)创建drop掉表空间的数据文件
SQL> alter database create datafile 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00012' as 'D:\ORACLE\ORADATA\ORCL\TT01.DBF
Database altered.
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ------------
1 ONLINE ONLINE 8140780 19-APR-14
2 ONLINE ONLINE 8140780 19-APR-14
3 ONLINE ONLINE 8140780 19-APR-14
4 ONLINE ONLINE 8140780 19-APR-14
5 ONLINE ONLINE 8140780 19-APR-14
6 ONLINE ONLINE 8140780 19-APR-14
7 ONLINE ONLINE 8140780 19-APR-14
8 ONLINE ONLINE 8140780 19-APR-14
9 ONLINE ONLINE 8140780 19-APR-14
10 ONLINE ONLINE 8140780 19-APR-14
11 ONLINE ONLINE 8140780 19-APR-14


FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ------------
12 ONLINE ONLINE 8140778 19-APR-14
12 rows selected.

SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 8140780
2 8140780
3 8140780
4 8140780
5 8140780
6 8140780
7 8140780
8 8140780
9 8140780
10 8140780
11 8140780


FILE# CHECKPOINT_CHANGE#
---------- ------------------
12 8140778


12 rows selected.


SQL> select file#,checkpoint_change# from v$datafile_header;


FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 8140780
2 8140780
3 8140780
4 8140780
5 8140780
6 8140780
7 8140780
8 8140780
9 8140780
10 8140780
11 8140780


FILE# CHECKPOINT_CHANGE#
---------- ------------------
12 8140778


12 rows selected.
6)恢复到drop掉的前一时间点
SQL> recover database using backup controlfile until time '2014-4-19 12:13:00'; --能不能直接用recover database until time '';
ORA-00279: change 8140778 generated at 04/19/2014 12:09:49 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_04_19\O1_MF_1_67_%U_.ARC
ORA-00280: change 8140778 for thread 1 is in sequence #67
Specify log: {=suggested | filename | AUTO | CANCEL}
D:\oracle\oradata\orcl\redo03.log
Log applied.
Media recovery complete.

SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 8140989
2 8140989
3 8140989
4 8140989
5 8140989
6 8140989
7 8140989
8 8140989
9 8140989
10 8140989
11 8140989
FILE# CHECKPOINT_CHANGE#
---------- ------------------
12 8140989


12 rows selected.


SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 8140989
2 8140989
3 8140989
4 8140989
5 8140989
6 8140989
7 8140989
8 8140989
9 8140989
10 8140989
11 8140989


FILE# CHECKPOINT_CHANGE#
---------- ------------------
12 8140989


12 rows selected.
7)打开数据库
SQL> alter database open resetlogs;
Database altered.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE
IDX
EYGLE
TESTING_LMT_ASSM
TESTING_LMT_MSSM
LDB


TABLESPACE_NAME
------------------------------
TT
12 rows selected.
8)查看恢复的表
SQL> select * from tab_a;

ID NAME
---------- ----------------------------------------------
1 jack

总结：
1.控制文件和数据文件要同时恢复，如果数据文件没有恢复,控制文件以数据文件的SCN为准,又恢复到被删除掉表空间的那个状态。