联系:手机/微信(+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会创建表空间,但是数据文件需要人工干预