表空间在没有备份的情况下被DROP后的恢复
过程：全备->建立表空间->在此表空间上进行业务->此表空间被 DROP->恢复此表空间
$ rman target/
Recovery Manager:Release 10.2.0.1.0 - Production on 星期二 4月 30 23:23:19 2013
Copyright (c) 1982,2005, Oracle. All rights reserved.
connected to targetdatabase: ORCL (DBID=1341748519)
RMAN> list backup;
using targetdatabase control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time CompletionTime
------- ---- ------------ ----------- ------------ -------------------
1 Full 30.83M DISK 00:01:00 2013-04-30 23:21:46
BP Key: 1 Status: AVAILABLE Compressed: YES Tag: TAG20130430T232045
Piece Name:/disk1/backup/orcl/02o8dnae_1_1.bak
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -----------------------
2 Full 573612 2013-04-3023:20:46 /u01/app/oracle/oradata/orcl/undotbs01.dbf
3 Full 573612 2013-04-3023:20:46 /u01/app/oracle/oradata/orcl/sysaux01.dbf
5 Full 573612 2013-04-3023:20:46 /u01/app/oracle/oradata/orcl/example01.dbf
BS Key Type LV Size Device Type Elapsed Time CompletionTime
------- ---- ------------ ----------- ------------ -------------------
2 Full 80.84M DISK 00:01:19 2013-04-30 23:22:05
BP Key: 2 Status: AVAILABLE Compressed: YES Tag: TAG20130430T232045
Piece Name:/disk1/backup/orcl/01o8dnae_1_1.bak
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -----------------------
1 Full 573611 2013-04-3023:20:46 /u01/app/oracle/oradata/orcl/system01.dbf
4 Full 573611 2013-04-3023:20:46 /u01/app/oracle/oradata/orcl/users01.dbf
BS Key Type LV Size Device Type Elapsed Time CompletionTime
------- ---- ------------ ----------- ------------ -------------------
3 Full 6.80M DISK 00:00:00 2013-04-30 23:22:07
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20130430T232207
Piece Name: /disk1/backup/orcl/c-1341748519-20130430-00.clt
Control File Included: Ckp SCN: 573653 Ckp time: 2013-04-30 23:22:07
SPFILE Included: Modification time:2013-04-30 23:17:26
RMAN>
$ sqlplus /as sysdba
SQL*Plus: Release10.2.0.1.0 - Production on 星期二 4月 30 23:24:35 2013
Copyright (c) 1982,2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10gEnterprise Edition Release 10.2.0.1.0 - 64bit Production
With thePartitioning, OLAP and Data Mining options
SQL>
SQL> create tablespace tbsdatafile '/u01/app/oracle/oradata/orcl/tbs.dbf' size 5m;
Tablespace created.
SQL> create table t (id int)tablespace tbs;
Table created.
SQL> insert into t values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
ID
----------
1
SQL> select file_name fromdba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/tbs.dbf
6 rows selected.
SQL> select current_scn fromv$database;
CURRENT_SCN
-----------
573950 // 恢复时恢复到这个SCN号
SQL> alter system archive logcurrent;
System altered.
SQL> alter system archive logcurrent;
System altered.
SQL> alter system archive logcurrent;
System altered.
SQL> alter system archive logcurrent;
System altered.
SQL>
SQL> drop tablespace tbsINCLUDING CONTENTS and datafiles;
Tablespace dropped.
SQL> select file_namefrom dba_data_files; // 表空间对应的数据文件已经没有了
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
SQL>
SQL> shutdown abort;
ORACLE instance shutdown.
SQL> startup nomount;
ORACLE instancestarted.
Total System GlobalArea 1224736768 bytes
Fixed Size 2020384 bytes
Variable Size 318770144 bytes
DatabaseBuffers 889192448 bytes
Redo Buffers 14753792 bytes
SQL>
SQL> exit
Disconnected fromOracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With thePartitioning, OLAP and Data Mining options
$ rman target/
Recovery Manager:Release 10.2.0.1.0 - Production on 星期二 4月 30 23:29:31 2013
Copyright (c) 1982,2005, Oracle. All rights reserved.
connected to targetdatabase: orcl (not mounted)
RMAN> restore controlfilefrom '/disk1/backup/orcl/c-1341748519-20130430-00.clt';
Starting restore at2013-04-30 23:29:54
using targetdatabase control file instead of recovery catalog
allocated channel:ORA_DISK_1
channel ORA_DISK_1:sid=156 devtype=DISK
channel ORA_DISK_1:restoring control file
channel ORA_DISK_1:restore complete, elapsed time: 00:00:02
outputfilename=/u01/app/oracle/oradata/orcl/control01.ctl
outputfilename=/u01/app/oracle/oradata/orcl/control02.ctl
outputfilename=/u01/app/oracle/oradata/orcl/control03.ctl
Finished restore at2013-04-30 23:29:57
RMAN> shutdown abort;
Oracle instance shutdown
RMAN> startup mount;
connected to targetdatabase (not started)
Oracle instancestarted
database mounted
Total System GlobalArea 1224736768 bytes
Fixed Size 2020384 bytes
Variable Size 318770144 bytes
DatabaseBuffers 889192448 bytes
Redo Buffers 14753792 bytes
RMAN> restore database;
Starting restore at2013-04-30 23:30:30
Starting implicitcrosscheck backup at 2013-04-30 23:30:30
allocated channel:ORA_DISK_1
channel ORA_DISK_1:sid=157 devtype=DISK
allocated channel:ORA_DISK_2
channel ORA_DISK_2:sid=155 devtype=DISK
Crosschecked 2objects
Finished implicitcrosscheck backup at 2013-04-30 23:30:33
Starting implicitcrosscheck copy at 2013-04-30 23:30:33
using channelORA_DISK_1
using channelORA_DISK_2
Finished implicitcrosscheck copy at 2013-04-30 23:30:33
searching for allfiles in the recovery area
cataloging files...
no files cataloged
using channelORA_DISK_1
using channelORA_DISK_2
channel ORA_DISK_1:starting datafile backupset restore
channel ORA_DISK_1:specifying datafile(s) to restore from backup set
restoring datafile00002 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
restoring datafile00003 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
restoring datafile00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1:reading from backup piece /disk1/backup/orcl/02o8dnae_1_1.bak
channel ORA_DISK_2:starting datafile backupset restore
channel ORA_DISK_2:specifying datafile(s) to restore from backup set
restoring datafile00001 to /u01/app/oracle/oradata/orcl/system01.dbf
restoring datafile00004 to /u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_2:reading from backup piece /disk1/backup/orcl/01o8dnae_1_1.bak
channel ORA_DISK_1:restored backup piece 1
piecehandle=/disk1/backup/orcl/02o8dnae_1_1.bak tag=TAG20130430T232045
channel ORA_DISK_1:restore complete, elapsed time: 00:00:46
channel ORA_DISK_2:restored backup piece 1
piecehandle=/disk1/backup/orcl/01o8dnae_1_1.bak tag=TAG20130430T232045
channel ORA_DISK_2:restore complete, elapsed time: 00:01:11
Finished restore at2013-04-30 23:31:45
RMAN>
RMAN> run{
2> set until scn 573950;
3> recover database;
4> }
executing command:SET until clause
Starting recover at2013-04-30 23:33:37
using channelORA_DISK_1
using channelORA_DISK_2
starting mediarecovery
archive logfilename=/disk1/backup/orcl/1_3_813967785.arc thread=1 sequence=3
creating datafilefno=6 name=/u01/app/oracle/oradata/orcl/tbs.dbf
archive logfilename=/disk1/backup/orcl/1_3_813967785.arc thread=1 sequence=3
media recoverycomplete, elapsed time: 00:00:03
Finished recover at2013-04-30 23:33:43
RMAN> sql'alter database openresetlogs';
sql statement: alterdatabase open resetlogs
RMAN> exit
Recovery Managercomplete.
$ sqlplus /as sysdba
SQL*Plus: Release10.2.0.1.0 - Production on 星期二 4月 30 23:34:03 2013
Copyright (c) 1982,2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10gEnterprise Edition Release 10.2.0.1.0 - 64bit Production
With thePartitioning, OLAP and Data Mining options
SQL> select file_name fromdba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/tbs.dbf // 表空间已经恢复
6 rows selected.
SQL> select * from t;
ID
----------
1
SQL>
总结：表空间在没有备份的情况下被DROP，此时若想恢复被DROP的表空间，必须使用DROP之前的备份及控制文件，然后利用归档日志再将数据库恢复到DROP之前的点，表空间会被归档日志重新创建。
我认为在不完全恢复过程中，控制文件的确定和使用是非常关键的，如果控制文件没有用好，恢复经常失败。
我认为是你的控制文件的原因，控制文件的使用在恢复过程中非常关键的
页:
[1]