联系：手机/微信(+86 17813235971) QQ(107644445)
标题：使用flashback database找回被误删除表空间
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
我个人不怎么推荐使用flashback database功能，因为在生产库中，谁能够承受得起整个库的回退(也许特别极端的情况下可能需要使用到)，今天帮网友恢复了一个案例:删除表空间，然后尝试着flashback database功能把这个删除的表空间找回来，但是他在整个操作过程中思路比较混乱，最后导致数据库不能正常起来。因为网友有这个方面的需求，我做了一个flashback database 找回表空间操作的试验(官方建议:flashback database在不改变数据文件的情况下使用)
1、恢复过程
SQL> select * from v$version;
BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> create tablespace xff_test datafile 'E:\ORACLE\ORADATA\XFF\xff_test01.dbf'
size 10m autoextend on next 1m maxsize 20m;
表空间已创建。
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2011-11-12 21:05:07
SQL> create table chf.t_flashback tablespace xff_test
2 as
3 select * from dba_objects;
表已创建。
SQL> select count(*) from chf.t_flashback;
COUNT(*)
----------
73211
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2011-11-12 21:06:35
SQL> host dir E:\ORACLE\ORADATA\XFF\xff_test01.dbf
驱动器 E 中的卷没有标签。
卷的序列号是 38D0-2A35
E:\ORACLE\ORADATA\XFF 的目录
2011/11/12 21:04 10,493,952 XFF_TEST01.DBF
1 个文件 10,493,952 字节
0 个目录 14,644,822,016 可用字节
SQL> drop tablespace xff_test including contents and datafiles;
表空间已删除。
SQL> host dir E:\ORACLE\ORADATA\XFF\xff_test01.dbf
驱动器 E 中的卷没有标签。
卷的序列号是 38D0-2A35
E:\ORACLE\ORADATA\XFF 的目录
找不到文件
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 368263168 bytes
Fixed Size 1374668 bytes
Variable Size 276825652 bytes
Database Buffers 83886080 bytes
Redo Buffers 6176768 bytes
数据库装载完毕。
SQL> flashback database to timestamp
2 to_timestamp('2011-11-12 21:06:35','yyyy-mm-dd hh24:mi:ss');
闪回完成。
SQL> alter database open resetlogs;
数据库已更改。
SQL> select name from v$datafile;
NAME
-------------------------------------------------------------
E:\ORACLE\ORADATA\XFF\SYSTEM01.DBF
E:\ORACLE\ORADATA\XFF\SYSAUX01.DBF
E:\ORACLE\ORADATA\XFF\UNDOTBS01.DBF
E:\ORACLE\ORADATA\XFF\USERS01.DBF
E:\ORACLE\ORADATA\XFF\EXAMPLE01.DBF
E:\ORACLE\ORADATA\XFF\O_ORACLE.DBF
E:\ORACLE\ORADATA\XFF\XIFENFEI01.DBF
E:\ORACLE\ORADATA\XFF\P_TEST01.DBF
E:\ORACLE\ORADATA\XFF\SYS_MG01.DBF
E:\ORACLE\ORADATA\XFF\P101.DBF
E:\ORACLE\ORADATA\XFF\P201.DBF
NAME
---------------------------------------------------------------
E:\ORACLE\ORADATA\XFF\P301.DBF
E:\ORACLE\11_2_0\DATABASE\UNNAMED00013
E:\ORACLE\ORADATA\XFF\OGG01.DBF
E:\ORACLE\ORADATA\XFF\SPOT01.DBF
已选择15行。
SQL> desc chf.t_flashback;
ERROR:
ORA-04043: 对象 chf.t_flashback 不存在
SQL> COL ERROR FOR A20
SQL> SELECT FILE#,ONLINE_STATUS,ERROR FROM V$RECOVER_FILE;
FILE# ONLINE_ ERROR
---------- ------- --------------------
13 OFFLINE FILE NOT FOUND
SQL> SELECT NAME FROM V$DATAFILE WHERE FILE#=13;
NAME
-----------------------------------------------------------------
E:\ORACLE\11_2_0\DATABASE\UNNAMED00013
SQL> HOST DIR E:\ORACLE\11_2_0\DATABASE\UNNAMED00013
驱动器 E 中的卷没有标签。
卷的序列号是 38D0-2A35
E:\ORACLE\11_2_0\DATABASE 的目录
找不到文件
SQL> ALTER DATABASE CREATE DATAFILE 13
AS 'E:\ORACLE\ORADATA\XFF\xff_test01.dbf';
数据库已更改。
SQL> HOST DIR E:\ORACLE\ORADATA\XFF\xff_test01.dbf
驱动器 E 中的卷没有标签。
卷的序列号是 38D0-2A35
E:\ORACLE\ORADATA\XFF 的目录
2011/11/12 21:25 10,493,952 XFF_TEST01.DBF
1 个文件 10,493,952 字节
0 个目录 14,640,848,896 可用字节
SQL> recover datafile 13;
ORA-00279: 更改 10903431152368 (在 11/12/2011 21:04:40 生成) 对于线程 1
是必需的
ORA-00289: 建议: E:\ORACLE\ARCHIVELOG\ARC0000000241_0753489409.0001
ORA-00280: 更改 10903431152368 (用于线程 1) 在序列 #241 中
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
auto
已应用的日志。
完成介质恢复。
SQL> alter database datafile 13 online;
数据库已更改。
SQL> select count(*) from chf.t_flashback;
COUNT(*)
----------
73211
2、alert中关键信息
Sat Nov 12 21:12:30 2011
flashback database to timestamp to_timestamp('2011-11-12 21:06:35','yyyy-mm-dd hh24:mi:ss')
Flashback Restore Start
Flashback: created tablespace #18: 'XFF_TEST' in the controlfile.
Flashback: created OFFLINE file 'UNNAMED00013' for tablespace #18 in the controlfile.
Filename was:
'E:\ORACLE\ORADATA\XFF\XFF_TEST01.DBF' when dropped.
File will have to be restored from a backup and recovered.
Flashback: deleted datafile #13 in tablespace #18 from control file.
Flashback: dropped tablespace #18: 'XFF_TEST' from the control file.
Flashback Restore Complete
Flashback Media Recovery Start
started logmerger process
Parallel Media Recovery started with 2 slaves
Sat Nov 12 21:12:38 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 241 Reading mem 0
Mem# 0: E:\ORACLE\ORADATA\XFF\REDO01
Flashback recovery: Added file #13 to control file as OFFLINE and 'UNNAMED00013'
because it was dropped during the flashback interval
or it was added during flashback media recovery.
File was originally created as:
'E:\ORACLE\ORADATA\XFF\XFF_TEST01.DBF'
File will have to be restored from a backup or
recreated using ALTER DATABASE CREATE DATAFILE command,
and the file has to be onlined and recovered.
Incomplete Recovery applied until change 10903431152644 time 11/12/2011 21:05:11
Sat Nov 12 21:12:44 2011
Flashback Media Recovery Complete
Completed: flashback database to timestamp to_timestamp('2011-11-12 21:06:35','yyyy-mm-dd hh24:mi:ss')
--flashback database 操作过程
……
Sat Nov 12 21:25:29 2011
ALTER DATABASE CREATE DATAFILE 13 AS 'E:\ORACLE\ORADATA\XFF\xff_test01.dbf'
Completed: ALTER DATABASE CREATE DATAFILE 13 AS 'E:\ORACLE\ORADATA\XFF\xff_test01.dbf'
Sat Nov 12 21:25:59 2011
ALTER DATABASE RECOVER datafile 13
Media Recovery Start
Serial Media Recovery started
Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 10903431152644
ORA-279 signalled during: ALTER DATABASE RECOVER datafile 13 ...
Sat Nov 12 21:28:27 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log E:\ORACLE\ARCHIVELOG\ARC0000000241_0753489409.0001
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0: E:\ORACLE\ORADATA\XFF\REDO01
Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT
Sat Nov 12 21:29:04 2011
alter database datafile 13 online
Completed: alter database datafile 13 online
--恢复被删除数据文件过程,flashbackup database会创建表空间，但是数据文件需要人工干预