mysql数据库as表恢复_使用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 中

指定日志: {=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会创建表空间，但是数据文件需要人工干预