通過flashback database恢復被刪除的表空間

做過實驗,不過沒有記錄下過程,轉一下別人的過程吧![@more@]$ 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: {=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---

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/271283/viewspace-1009251/，如需轉載，請註明出處，否則將追究法律責任。