flashback database 恢復誤刪除的表空間。

轉載自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: {=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---