iis服务器助手广告广告

扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >Oracle手工不完全恢复（一）：使用当前控制文件
  • 409

0
分享到

Oracle手工不完全恢复（一）：使用当前控制文件

2024-04-02 19:04:59 409人浏览 独家记忆
摘要

实验环境 操作系统：Centos 7.1 数据库：oracle 11.2.0.4 目录 示例一：基于SCN或时间点的恢复----恢复过去某个时间误删除的表

实验环境

操作系统：Centos 7.1

数据库：oracle 11.2.0.4


目录

示例一：基于SCN或时间点的恢复----恢复过去某个时间误删除的表

示例二：当前日志组损坏，造成数据库崩溃

示例三：归档日志丢失或损坏



示例一：基于SCN或时间点的恢复----恢复过去某个时间误删除的表


环境：

（1）提前对数据库做一次全库冷备份；

（2）在seiang用户下有一张test1表隶属于seiang表空间；


定位错误操作发生的时间或SCN：logminer

恢复原理： 

1.冷备份的数据文件*.dbf比现在要旧，那么数据文件的scn肯定就比现在的小；  

2.使用日志挖掘方法，在日志中找出删表时的scn或时间戳；  

3.在数据库shutdown的状态下，用冷备份的数据文件*.dbf，覆盖现在的*.dbf文件；保证数据文件完整性；

4.启动数据库到mount下,恢复到日志挖掘的SCN或时间点（利用日志对数据文件重做一次）  

5.以resetlogs方式打开数据库，以前的日志就被覆盖了；


示例二：当前日志组损坏，造成数据库崩溃


环境：

（1）提前对数据库做一次全库冷备份；

（2）在seiang用户下有一张test1表隶属于seiang表空间；


示例三：归档日志丢失或损坏


环境：

（1）提前有一套完成的数据文件的冷备份；

（2）在seiang用户下有一张test1表隶属于seiang表空间；

  1. SYS@seiang11g>select owner,table_name,tablespace_name from dba_tables where owner='SEIANG';

  3. OWNER TABLE_NAME TABLESPACE_NAME
  4. ------------------------------ ------------------------------ ------------------------------
  5. SEIANG TEST1 SEIANG
  6. SEIANG TEST2 WJQ
  7. SEIANG TEST3 WJQBEST

  9. SYS@seiang11g>select * from seiang.test1;

  11.         ID NAME AGE
  12. ---------- ------------------------------ ----------
  13.          1 wjq 23
  14.          2 seiang 24
  15.          3 wjqdood 25
  16.          4 wjqbest 30

  18. --查看当前日志序号为2
  19. SYS@seiang11g>select group#,sequence#,status from v$log;

  21.     GROUP# SEQUENCE# STATUS
  22. ---------- ---------- ----------------
  23.          1 1 INACTIVE
  24.          2 2 CURRENT
  25.          3 0 UNUSED


  28. --修改数据并提交(sequence号2)
  29. SYS@seiang11g>update seiang.test1 set age=100 where id=1;
  30. 1 row updated.

  32. SYS@seiang11g>commit;
  33. Commit complete.

  35. SYS@seiang11g>alter system switch logfile;
  36. System altered.

  38. --修改数据并提交(sequence号3)
  39. SYS@seiang11g>update seiang.test1 set age=200 where id=1;
  40. 1 row updated.

  42. SYS@seiang11g>commit;
  43. Commit complete.

  45. SYS@seiang11g>alter system switch logfile;
  46. System altered.

  48. --修改数据并提交(sequence号4)
  49. SYS@seiang11g>update seiang.test1 set age=300 where id=1;
  50. 1 row updated.

  52. SYS@seiang11g>commit;
  53. Commit complete.

  55. SYS@seiang11g>alter system switch logfile;
  56. System altered.

  58. --修改数据并提交(sequence号5)
  59. SYS@seiang11g>update seiang.test1 set age=400 where id=1;
  60. 1 row updated.

  62. SYS@seiang11g>commit;
  63. Commit complete.

  65. SYS@seiang11g>alter system switch logfile;
  66. System altered.

  68. --修改数据并提交(sequence号6)
  69. SYS@seiang11g>update seiang.test1 set age=500 where id=1;
  70. 1 row updated.

  72. SYS@seiang11g>commit;
  73. Commit complete.

  75. SYS@seiang11g>alter system switch logfile;
  76. System altered.

  78. --修改数据并提交(sequence号7)
  79. SYS@seiang11g>update seiang.test1 set age=600 where id=1;
  80. 1 row updated.

  82. SYS@seiang11g>commit;
  83. Commit complete.

  85. SYS@seiang11g>alter system switch logfile;
  86. System altered.

  88. --当前联机日志序号为8
  89. SYS@seiang11g>select group#,sequence#,status from v$log;

  91.     GROUP# SEQUENCE# STATUS
  92. ---------- ---------- ----------------
  93.          1 7 ACTIVE
  94.          2 8 CURRENT
  95.          3 6 ACTIVE

  97. --查看相应的归档日志
  98. SYS@seiang11g>select name from v$arcHived_log;

  100. NAME
  101. ------------------------------------------------------
  102. /u01/app/oracle/arch/arch_1_950962051_1.log
  103. /u01/app/oracle/arch/arch_1_950962051_2.log
  104. /u01/app/oracle/arch/arch_1_950971495_1.log
  105. /u01/app/oracle/arch/arch_1_950971495_2.log
  106. /u01/app/oracle/arch/arch_1_950972396_1.log
  107. /u01/app/oracle/arch/arch_1_950972396_2.log
  108. /u01/app/oracle/arch/arch_1_950972396_3.log
  109. /u01/app/oracle/arch/arch_1_950972396_4.log
  110. /u01/app/oracle/arch/arch_1_950972396_5.log
  111. /u01/app/oracle/arch/arch_1_950972396_6.log
  112. /u01/app/oracle/arch/arch_1_950972396_7.log

  114. --关闭数据库
  115. SYS@seiang11g >shutdown immediate
  116. Database closed.
  117. Database dismounted.
  118. ORACLE instance shut down.

  120. --模拟数据文件seiang损坏
  121. SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/seiang01.dbf


  124. --重新打开数据库 ，由于数据文件的丢死，数据库无法open
  125. SYS@seiang11g>startup
  126. ORACLE instance started.

  128. Total System Global Area 1252663296 bytes
  129. Fixed Size 2252824 bytes
  130. Variable Size 788533224 bytes
  131. Database Buffers 452984832 bytes
  132. Redo Buffers 8892416 bytes
  133. Database mounted.
  134. ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
  135. ORA-01110: data file 7: '/u01/app/oracle/oradata/OraDB11g/seiang01.dbf'


  138. SYS@seiang11g>select * from v$recover_file;

  140.      FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
  141. ---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
  142.          7 ONLINE ONLINE FILE NOT FOUND 0

  144. --还原备份的数据文件
  145. SYS@seiang11g>host cp /u01/app/oracle/UMAN_Backup/seiang01.dbf /u01/app/oracle/oradata/OraDB11g/
  146.     
  147. SYS@seiang11g>select * from v$recover_file;

  149.      FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
  150. ---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
  151.          7 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17


  154. --查看一下归档日志的详细信息
  155. SYS@seiang11g>select sequence#, name, first_change#, next_change# from v$archived_log;

  157.  SEQUENCE# NAME FIRST_CHANGE# NEXT_CHANGE#
  158. ---------- -------------------------------------------------- ------------- ------------
  159.         1 /u01/app/oracle/arch/arch_1_950962051_1.log 1914744 1921854
  160.          2 /u01/app/oracle/arch/arch_1_950962051_2.log 1921854 1921883
  161.          1 /u01/app/oracle/arch/arch_1_950971495_1.log 1913766 1914386
  162.          2 /u01/app/oracle/arch/arch_1_950971495_2.log 1914386 1914401
  163.          1 /u01/app/oracle/arch/arch_1_950972396_1.log 1914402 1936446
  164.          2 /u01/app/oracle/arch/arch_1_950972396_2.log 1936446 1937042
  165.          3 /u01/app/oracle/arch/arch_1_950972396_3.log 1937042 1937100
  166.          4 /u01/app/oracle/arch/arch_1_950972396_4.log 1937100 1937110
  167.          5 /u01/app/oracle/arch/arch_1_950972396_5.log 1937110 1937123
  168.          6 /u01/app/oracle/arch/arch_1_950972396_6.log 1937123 1937139
  169.          7 /u01/app/oracle/arch/arch_1_950972396_7.log 1937139 1937148


  172. --模拟归档日志5丢失或者损坏
  173. SYS@seiang11g>host rm /u01/app/oracle/arch/arch_1_950972396_5.log

  175. [oracle@seiang11g OraDB11g]$ ll /u01/app/oracle/arch/arch_1_950972396_5.log
  176. ls: cannot access /u01/app/oracle/arch/arch_1_950972396_5.log: No such file or directory


  179. --执行恢复操作，当在需要归档日志5的时候出现错误
  180. SYS@seiang11g>recover datafile 7;
  181. ORA-00279: change 1913766 generated at 08/02/2017 11:12:22 needed for thread 1
  182. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_1.log
  183. ORA-00280: change 1913766 for thread 1 is in sequence #1

  185. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  186. auto
  187. ORA-00279: change 1914386 generated at 08/02/2017 14:48:59 needed for thread 1
  188. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_2.log
  189. ORA-00280: change 1914386 for thread 1 is in sequence #2

  191. ORA-00279: change 1914402 generated at 08/02/2017 14:59:56 needed for thread 1
  192. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_1.log
  193. ORA-00280: change 1914402 for thread 1 is in sequence #1

  195. ORA-00279: change 1936446 generated at 08/02/2017 15:39:52 needed for thread 1
  196. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_2.log
  197. ORA-00280: change 1936446 for thread 1 is in sequence #2

  199. ORA-00279: change 1937042 generated at 08/02/2017 15:49:42 needed for thread 1
  200. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_3.log
  201. ORA-00280: change 1937042 for thread 1 is in sequence #3

  203. ORA-00279: change 1937100 generated at 08/02/2017 15:51:34 needed for thread 1
  204. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_4.log
  205. ORA-00280: change 1937100 for thread 1 is in sequence #4

  207. ORA-00279: change 1937110 generated at 08/02/2017 15:51:54 needed for thread 1
  208. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_5.log
  209. ORA-00280: change 1937110 for thread 1 is in sequence #5

  211. ORA-00308: cannot open archived log '/u01/app/oracle/arch/arch_1_950972396_5.log'
  212. ORA-27037: unable to obtain file status
  213. linux-x86_64 Error: 2: No such file or directory
  214. Additional infORMation: 3
  215.     
  216. --查看数据文件头，发现检查点不一致
  217. SYS@seiang11g>select file#,name,checkpoint_change# from v$datafile_header;

  219.      FILE# NAME CHECKPOINT_CHANGE#
  220. ---------- -------------------------------------------------- ------------------
  221.          1 /u01/app/oracle/oradata/OraDB11g/system01.dbf 1937617
  222.          2 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf 1937617
  223.          3 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf 1937617
  224.          4 /u01/app/oracle/oradata/OraDB11g/users01.dbf 1937617
  225.          5 /u01/app/oracle/oradata/OraDB11g/example01.dbf 1937617
  226.          6 /u01/app/oracle/oradata/OraDB11g/rman01.dbf 1937617
  227.          7 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf 1937110
  228.          8 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf 1937617
  229.          9 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf 1937617
您可能感兴趣的文档:
点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: Oracle手工不完全恢复（一）：使用当前控制文件

本文链接: https://www.lsjlt.com/news/46010.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档
去做题
猜你喜欢
软考高级职称资格查询
iis服务器助手广告
软考职称历年真题下载
软考职称资料下载
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作