表空间在没有备份的情况下被DROP后的恢复

ALLSTARS_ORACLE

过程：全备->建立表空间->在此表空间上进行业务->此表空间被 DROP->恢复此表空间
[oracle@redhat55 ~]$ 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>
[oracle@redhat55 ~]$ 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
[oracle@redhat55 ~]$ 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.
[oracle@redhat55 ~]$ 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之前的点，表空间会被归档日志重新创建。

ALLSTARS_ORACLE

我认为在不完全恢复过程中，控制文件的确定和使用是非常关键的，如果控制文件没有用好，恢复经常失败。


我认为是你的控制文件的原因，控制文件的使用在恢复过程中非常关键的