当前位置：Gxlcms > mysql > 误droptablespace后使用flashbackdatabase闪回异常处理

误droptablespace后使用flashbackdatabase闪回异常处理

时间：2021-07-01 10:21:17 帮助过：7人阅读

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

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

  1. Sat Jul 05 17:10:06 2014
  2. create tablespace XIFENFEI datafile 'D:\Oracle\oradata\orcl\HANDBB.DBF' 
  3. size 50M autoextend on next 50M maxsize 1536M extent management local
  4. Sat Jul 05 17:10:06 2014
  5. ORA-1543 signalled during: create tablespace XIFENFEI datafile 'D:\Oracle\oradata\orcl\HANDBB.DBF' 
  6. size 50M autoextend on next 50M maxsize 1536M extent management local
  7. ...
  8. Sat Jul 05 17:10:59 2014
  9. drop tablespace XIFENFEI
  10. Sat Jul 05 17:10:59 2014
  11. ORA-1549 signalled during: drop tablespace XIFENFEI
  12. ...
  13. Sat Jul 05 17:11:05 2014
  14. drop tablespace XIFENFEI
  15. ORA-1549 signalled during: drop tablespace XIFENFEI
  16. ...
  17. Sat Jul 05 17:11:24 2014
  18. drop tablespace XIFENFEI including contents
  19. Sat Jul 05 17:11:36 2014
  20. Thread 1 advanced to log sequence 186895 (LGWR switch)
  21.   Current log# 1 seq# 186895 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\REDO01.LOG
  22. Sat Jul 05 17:11:36 2014
  23. ARC3: Warning.  Log sequence in archive filename wrapped
  24. to fix length as indicated by %S in LOG_ARCHIVE_FORMAT.
  25. Old log archive with same name might be overwritten.
  26. Sat Jul 05 17:11:43 2014
  27. LNS: Standby redo logfile selected for thread 1 sequence 186895 for destination LOG_ARCHIVE_DEST_4
  28. Sat Jul 05 17:11:49 2014
  29. LNS: Standby redo logfile selected for thread 1 sequence 186895 for destination LOG_ARCHIVE_DEST_2
  30. Sat Jul 05 17:12:09 2014
  31. Starting control autobackup
  32. Control autobackup written to DISK device
  33. handle 'D:\FULLBACK\C-1342406147-20140705-00'
  34. Completed: drop tablespace XIFENFEI including contents

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

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

  1. Sat Jul 05 18:16:54 2014
  2. Database mounted in Exclusive Mode
  3. Completed: ALTER DATABASE   MOUNT
  4. Sat Jul 05 18:19:23 2014
  5. FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2014-07-05 17:09:00','YYYY-MM-DD HH24:MI:SS')
  6. Sat Jul 05 18:19:25 2014
  7. Flashback Restore Start
  8. Sat Jul 05 18:20:52 2014
  9. --闪回时的控制文件中无表空间XIFENFEI信息(因为已经被删除)，
  10. --但是由于闪回的system 数据字典里面有相关文件信息,因此数据库在控制文件里面创建相关文件信息
  11. Flashback: created tablespace #6: 'XIFENFEI' in the controlfile.
  12. Flashback: created OFFLINE file 'UNNAMED00012' for tablespace #6 in the controlfile.
  13. Filename was:
  14. 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\XIFENFEI4.DBF' when dropped.
  15. File will have to be restored from a backup and recovered.
  16. Flashback: created OFFLINE file 'UNNAMED00010' for tablespace #6 in the controlfile.
  17. Filename was:
  18. 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\XIFENFEI3.DBF' when dropped.
  19. File will have to be restored from a backup and recovered.
  20. Flashback: created OFFLINE file 'UNNAMED00008' for tablespace #6 in the controlfile.
  21. Filename was:
  22. 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\XIFENFEI2.DBF' when dropped.
  23. File will have to be restored from a backup and recovered.
  24. Flashback: created OFFLINE file 'UNNAMED00005' for tablespace #6 in the controlfile.
  25. Filename was:
  26. 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\XIFENFEI.DBF' when dropped.
  27. File will have to be restored from a backup and recovered.
  28. Flashback Restore Complete
  29. Flashback Media Recovery Start
  30.  parallel recovery started with 15 processes
  31. Flashback Media Recovery Log D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ARC\ARC86891_0766797318.001
  32. Flashback Media Recovery Log D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ARC\ARC86892_0766797318.001
  33. Sat Jul 05 18:21:40 2014
  34. Flashback Media Recovery Log D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ARC\ARC86893_0766797318.001
  35. Sat Jul 05 18:21:47 2014
  36. WARNING: inbound connection timed out (ORA-3136)
  37. Sat Jul 05 18:22:11 2014
  38. Recovery of Online Redo Log: Thread 1 Group 3 Seq 186894 Reading mem 0
  39.   Mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\REDO03.LOG
  40. Sat Jul 05 18:22:39 2014
  41. Incomplete Recovery applied until change 9078991241
  42. Flashback Media Recovery Complete
  43. ORA-38795 signalled during: FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2014-07-05 17:09:00','YYYY-MM-DD HH24:MI:SS')...
  44. Sat Jul 05 18:30:11 2014
  45. ALTER DATABASE OPEN RESETLOGS
  46. Sat Jul 05 18:30:11 2014
  47. ORA-1245 signalled during: ALTER DATABASE OPEN RESETLOGS...
  48. --重命名相关UNNAMExxxxx文件名到硬盘上被删除表空间文件
  49. Sat Jul 05 18:39:31 2014
  50. alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005' 
  51. to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI.DBF'
  52. Sat Jul 05 18:39:31 2014
  53. Completed: alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005' 
  54. to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI.DBF'
  55. Sat Jul 05 18:39:47 2014
  56. alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008' 
  57. to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI2.DBF'
  58. Sat Jul 05 18:39:47 2014
  59. Completed: alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008' 
  60. to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI2.DBF'
  61. Sat Jul 05 18:39:59 2014
  62. alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00010' 
  63. to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI3.DBF'
  64. Sat Jul 05 18:39:59 2014
  65. Completed: alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00010' 
  66. to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI3.DBF'
  67. Sat Jul 05 18:40:12 2014
  68. alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00012' 
  69. to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI4.DBF'
  70. Sat Jul 05 18:40:12 2014
  71. Completed: alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00012' 
  72. to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI4.DBF'
  73. Sat Jul 05 18:41:25 2014
  74. ALTER DATABASE OPEN RESETLOGS
  75. Sat Jul 05 18:41:25 2014
  76. ORA-1245 signalled during: ALTER DATABASE OPEN RESETLOGS...

到这里,可以看出来,因为数据库整体已经闪回,但是被drop 表空间的四个数据文件未被正常闪回,因此该四个文件的scn可能异常,通过数据库恢复检查脚本(Oracle Database Recovery Check)检查结果如下
datafile_scn
datafile_header_scn
这里很明显控制文件中的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闪回异常处理, 感谢原作者分享。

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！