转载自http://ms.itpub.net/redirect.php?fid=2&tid=1229988&goto=nextnewset
通过flashback database恢复被删除的表空间（测试案例）
网友问题：《FLASHBACK DATABASE可以恢复删除的TABLESPACE吗？》http://www.itpub.net/thread-1042099-1-1.html
重新发贴，贴测试案例过程：
$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Aug 21 09:31:32 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TESTDB (DBID=2437320461)
RMAN> list backup;
using target database control file instead of recovery catalog
RMAN> exit
Recovery Manager complete.
-----以上表明该数据库并没有rman备份---------
$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 21 09:34:05 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> set time on
09:34:13 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SP2-0640: Not connected
09:34:32 SQL> conn /as sysdba
Connected.
09:34:37 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
09:34:41 SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
TEST1
6 rows selected.
09:34:55 SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/testdb/system01.dbf
/oracle/oradata/testdb/undotbs01.dbf
/oracle/oradata/testdb/sysaux01.dbf
/oracle/oradata/testdb/users01.dbf
/oracle/oradata/testdb/s.dbf
/oracle/oradata/testdb/test1.dbf
6 rows selected.
09:35:05 SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
09:35:12 SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
09:35:23 SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
2948639 2008-08-21 09:20:19
09:35:39 SQL> drop tablespace test1; 此处删除表空间test1
Tablespace dropped.
09:35:50 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
09:36:33 SQL> startup mount
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 2020192 bytes
Variable Size 109055136 bytes
Database Buffers 155189248 bytes
Redo Buffers 2170880 bytes
Database mounted.
09:36:55 SQL> FLASHBACK DATABASE TO timestamp(to_date('2008-08-21 09:35:23','yyyy-mm-dd hh24:mi:ss'));FLASHBACK DATABASE TO timestamp(to_date('2008-08-21 09:35:23','yyyy-mm-dd hh24:mi:ss'))
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error
below
ORA-01245: offline file 6 will be lost if RESETLOGS is done
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/oracle/product/10.2.0/dbs/UNNAMED00006'
09:37:13 SQL> alter database create datafile 6 as '/oracle/oradata/testdb/test1.dbf'; 此处也可以用alter database rename file '/oracle/product/10.2.0/dbs/UNNAMED00006' to '/oracle/oradata/testdb/test1.dbf';
替代
Database altered.
09:37:36 SQL> alter database datafile 6 online; 关键步骤1
Database altered.
09:37:56 SQL> recover database until time '2008-08-21 09:35:23';关键步骤2
ORA-00279: change 2948175 generated at 08/21/2008 09:00:26 needed for thread 1
ORA-00289: suggestion :
/oracle/recovery/TESTDB/archivelog/2008_08_21/o1_mf_1_34_%u_.arc
ORA-00280: change 2948175 for thread 1 is in sequence #34
09:38:22 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
09:38:37 SQL> alter database open read only;
Database altered.
09:39:00 SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
TEST1
6 rows selected.
09:39:08 SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/testdb/system01.dbf
/oracle/oradata/testdb/undotbs01.dbf
/oracle/oradata/testdb/sysaux01.dbf
/oracle/oradata/testdb/users01.dbf
/oracle/oradata/testdb/s.dbf
/oracle/oradata/testdb/test1.dbf
6 rows selected.
09:40:09 SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
09:40:27 SQL> startup
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 2020192 bytes
Variable Size 109055136 bytes
Database Buffers 155189248 bytes
Redo Buffers 2170880 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
09:40:41 SQL> alter database open resetlogs;
Database altered.
09:41:08 SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
TEST1
6 rows selected.
09:41:17 SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/testdb/system01.dbf
/oracle/oradata/testdb/undotbs01.dbf
/oracle/oradata/testdb/sysaux01.dbf
/oracle/oradata/testdb/users01.dbf
/oracle/oradata/testdb/s.dbf
/oracle/oradata/testdb/test1.dbf
6 rows selected.
09:41:24 SQL>
---成功恢复表空间test1---