误drop tablespace后使用flashback database闪回异常处理

有朋友夜间打电话请求技术支持,数据库表空间被删除，然后使用flashback database 无法正常恢复。通过分析alert日志发现，创建表空间(xifenfei 别名),发现已经存在,就删除了该表空间(其实是业务核心表空间,误删除了,是否是连接错了数据库？) Sat Jul 05 17:10

有朋友夜间打电话请求技术支持,数据库表空间被删除，然后使用flashback database 无法正常恢复。通过分析alert日志发现，创建表空间(xifenfei 别名),发现已经存在,就删除了该表空间(其实是业务核心表空间,误删除了,是否是连接错了数据库？)

Sat Jul 05 17:10:06 2014create tablespace XIFENFEI datafile 'D:\Oracle\oradata\orcl\HANDBB.DBF' size 50M autoextend on next 50M maxsize 1536M extent management localSat Jul 05 17:10:06 2014ORA-1543 signalled during: create tablespace XIFENFEI datafile 'D:\Oracle\oradata\orcl\HANDBB.DBF' size 50M autoextend on next 50M maxsize 1536M extent management local...Sat Jul 05 17:10:59 2014drop tablespace XIFENFEISat Jul 05 17:10:59 2014ORA-1549 signalled during: drop tablespace XIFENFEI...Sat Jul 05 17:11:05 2014drop tablespace XIFENFEIORA-1549 signalled during: drop tablespace XIFENFEI...Sat Jul 05 17:11:24 2014drop tablespace XIFENFEI including contentsSat Jul 05 17:11:36 2014Thread 1 advanced to log sequence 186895 (LGWR switch)  Current log# 1 seq# 186895 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\REDO01.LOGSat Jul 05 17:11:36 2014ARC3: Warning.  Log sequence in archive filename wrappedto fix length as indicated by %S in LOG_ARCHIVE_FORMAT.Old log archive with same name might be overwritten.Sat Jul 05 17:11:43 2014LNS: Standby redo logfile selected for thread 1 sequence 186895 for destination LOG_ARCHIVE_DEST_4Sat Jul 05 17:11:49 2014LNS: Standby redo logfile selected for thread 1 sequence 186895 for destination LOG_ARCHIVE_DEST_2Sat Jul 05 17:12:09 2014Starting control autobackupControl autobackup written to DISK device	handle 'D:\FULLBACK\C-1342406147-20140705-00'Completed: drop tablespace XIFENFEI including contents

通过这里可以发现删除表空间时间点为2014年7月5日17:12:09

闪回数据库到删除之前时间点

Sat Jul 05 18:16:54 2014Database mounted in Exclusive ModeCompleted: ALTER DATABASE   MOUNTSat Jul 05 18:19:23 2014FLASHBACK DATA<b>本文来源gao@dai!ma.com搞$代^码!网7</b>BASE TO TIMESTAMP TO_TIMESTAMP('2014-07-05 17:09:00','YYYY-MM-DD HH24:MI:SS')Sat Jul 05 18:19:25 2014Flashback Restore StartSat Jul 05 18:20:52 2014--闪回时的控制文件中无表空间XIFENFEI信息(因为已经被删除)，--但是由于闪回的system 数据字典里面有相关文件信息,因此数据库在控制文件里面创建相关文件信息Flashback: created tablespace #6: 'XIFENFEI' in the controlfile.Flashback: created OFFLINE file 'UNNAMED00012' for tablespace #6 in the controlfile.Filename was:'D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\XIFENFEI4.DBF' when dropped.File will have to be restored from a backup and recovered.Flashback: created OFFLINE file 'UNNAMED00010' for tablespace #6 in the controlfile.Filename was:'D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\XIFENFEI3.DBF' when dropped.File will have to be restored from a backup and recovered.Flashback: created OFFLINE file 'UNNAMED00008' for tablespace #6 in the controlfile.Filename was:'D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\XIFENFEI2.DBF' when dropped.File will have to be restored from a backup and recovered.Flashback: created OFFLINE file 'UNNAMED00005' for tablespace #6 in the controlfile.Filename was:'D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\XIFENFEI.DBF' when dropped.File will have to be restored from a backup and recovered.Flashback Restore CompleteFlashback Media Recovery Start parallel recovery started with 15 processesFlashback Media Recovery Log D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ARC\ARC86891_0766797318.001Flashback Media Recovery Log D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ARC\ARC86892_0766797318.001Sat Jul 05 18:21:40 2014Flashback Media Recovery Log D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ARC\ARC86893_0766797318.001Sat Jul 05 18:21:47 2014WARNING: inbound connection timed out (ORA-3136)Sat Jul 05 18:22:11 2014Recovery of Online Redo Log: Thread 1 Group 3 Seq 186894 Reading mem 0  Mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\REDO03.LOGSat Jul 05 18:22:39 2014Incomplete Recovery applied until change 9078991241Flashback Media Recovery CompleteORA-38795 signalled during: FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2014-07-05 17:09:00','YYYY-MM-DD HH24:MI:SS')...Sat Jul 05 18:30:11 2014ALTER DATABASE OPEN RESETLOGSSat Jul 05 18:30:11 2014ORA-1245 signalled during: ALTER DATABASE OPEN RESETLOGS...--重命名相关UNNAMExxxxx文件名到硬盘上被删除表空间文件Sat Jul 05 18:39:31 2014alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005' to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI.DBF'Sat Jul 05 18:39:31 2014Completed: alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005' to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI.DBF'Sat Jul 05 18:39:47 2014alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008' to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI2.DBF'Sat Jul 05 18:39:47 2014Completed: alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008' to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI2.DBF'Sat Jul 05 18:39:59 2014alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00010' to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI3.DBF'Sat Jul 05 18:39:59 2014Completed: alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00010' to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI3.DBF'Sat Jul 05 18:40:12 2014alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00012' to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI4.DBF'Sat Jul 05 18:40:12 2014Completed: alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00012' to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI4.DBF'Sat Jul 05 18:41:25 2014ALTER DATABASE OPEN RESETLOGSSat Jul 05 18:41:25 2014ORA-1245 signalled during: ALTER DATABASE OPEN RESETLOGS...

到这里,可以看出来,因为数据库整体已经闪回,但是被drop 表空间的四个数据文件未被正常闪回,因此该四个文件的scn可能异常,通过数据库恢复检查脚本(Oracle Database Recovery Check)检查结果如下

这里很明显控制文件中的scn信息混乱不做过多参考,数据文件头信息看到只有ts# 6中的四个文件(就是被删除的表空间文件)scn过大,其他文件scn都处于正常状态(处于干净状态),到这里很明显,数据库闪回成功,但是被drop tablespace的数据文件未被闪回,因此该故障可以通过bbed修改四个文件头信息和其他文件相同即可使得数据库恢复正常
温馨提示:数据库操作需要慎重,备份重于一切

• 记录一次ORA-600 3004 恢复过程和处理思路
• 数据文件的三个创建SCN一点点探讨
• 记录一次ORA-00600[kdxlin:psno out of range]/ORA-00600[3020]/ORA-00600[4000]/ORA-00600[4193]的数据库恢复
• 使用flashback database找回被误删除表空间
• 记录一次ORA-00316 ORA-00312 redo异常恢复
• ORA-00600[kcrf_resilver_log_1]异常恢复
• 又一起存储故障导致ORA-00333 ORA-00312恢复
• Oracle安全警示录:加错裸设备导致redo异常

原文地址：误drop tablespace后使用flashback database闪回异常处理, 感谢原作者分享。

喜欢 (0)赏

[搞代码]

分享 (0)