Oracle如何使用备份控制文件

这篇文章主要为大家展示了“Oracle如何使用备份控制文件”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“Oracle如何使用备份控制文件”这篇文章吧。

创新互联公司主要从事成都网站制作、网站建设、外贸网站建设、网页设计、企业做网站、公司建网站等业务。立足成都服务习水,十余年网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:18980820575

示例一：冷备份所有数据文件--->新建表空间--->备份控制文件（日志文件完好）

实验环境：

当前的控制文件损坏，新创建的表空间损坏，冷备的数据文件中没有该数据文件的备份，但是控制文件和联机日志中有相关的记录；由于控制文件损坏，只能使用备份的控制文件来做恢复。

1. --查看数据库中已有的表空间
   

2. SYS@seiang11g>select * from v$tablespace;
   

3. 
   

4.        TS# NAME                                               INC BIG FLA ENC
   

5. ---------- -------------------------------------------------- --- --- --- ---
   

6.          0 SYSTEM                                             YES NO  YES
   

7.          1 SYSAUX                                             YES NO  YES
   

8.          2 UNDOTBS1                                           YES NO  YES
   

9.          4 USERS                                              YES NO  YES
   

10.          3 TEMP                                               NO  NO  YES
    

11.          6 EXAMPLE                                            YES NO  YES
    

12.          7 RMAN_CATALOG                                       YES NO  YES
    

13.          8 SEIANG                                             YES NO  YES
    

14.          9 WJQ                                                YES NO  YES
    

15.         10 WJQBEST                                            YES NO  YES
    

16. 
    

17. --查看当前日志的序列号为3
    

18. SYS@seiang11g>select group#,sequence#,status from v$log;
    

19. 
    

20.     GROUP# SEQUENCE# STATUS
    

21. ---------- ---------- ----------------
    

22.          1          1 INACTIVE
    

23.          2          2 INACTIVE
    

24.          3          3 CURRENT
    

25. 
    

26. 
    

27. --新创建一个表空间test
    

28. SYS@seiang11g>create tablespace test datafile '/u01/app/oracle/oradata/OraDB11g/test01.dbf' size 5M;
    

29. Tablespace created.
    

30. 
    

31. 
    

32. --表空间创建完成之后，备份控制文件
    

33. SYS@seiang11g>alter database backup controlfile to '/u01/app/oracle/oradata/OraDB11g/control.bak';
    

34. Database altered.
    

35. 
    

36. --查看数据库中控制文件的多元化路径
    

37. SYS@seiang11g>show parameter control
    

38. 
    

39. NAME TYPE VALUE
    

40. ------------------------------------ ----------- ------------------------------
    

41. control_file_record_keep_time integer 14
    

42. control_files                        string /u01/app/oracle/oradata/OraDB1
    

43.                                                  1g/control01.ctl, /u01/app/ora
    

44.                                                  cle/fast_recovery_area/OraDB11
    

45.                                                  g/control02.ctl
    

46. control_management_pack_access       string      DIAGNOSTIC+TUNING
    

47. 
    

48. 
    

49. --4在seiang用户下创建一张表test4,隶属于test表空间
    

50. SYS@seiang11g>create table seiang.test4(ID number,name varchar2(30)) tablespace test;
    

51. Table created.
    

52. 
    

53. 
    

54. --在test4表中插入两条数据，并提交
    

55. SYS@seiang11g>insert into seiang.test4 values(1001,'wjq');
    

56. 1 row created.
    

57. 
    

58. SYS@seiang11g>insert into seiang.test4 values(1002,'seiang');
    

59. 1 row created.
    

60. 
    

61. SYS@seiang11g>commit;
    

62. Commit complete.
    

63. 
    

64. --执行日志切换，刚插入的表中的记录信息已归档
    

65. SYS@seiang11g>alter system switch logfile;
    

66. System altered.
    

67. 
    

68. --查看当前的日志序列号为4
    

69. SYS@seiang11g>select group#,sequence#,status from v$log;
    

70. 
    

71.     GROUP# SEQUENCE# STATUS
    

72. ---------- ---------- ----------------
    

73.          1          4 CURRENT
    

74.          2          2 INACTIVE
    

75.          3          3 ACTIVE
    

76. 
    

77. --再在test4表中插入两条数据，但后两条插入的数据记录在当前日志文件1中
    

78. SYS@seiang11g>insert into seiang.test4 values(1003,'wjqgood');
    

79. 1 row created.
    

80. 
    

81. SYS@seiang11g>insert into seiang.test4 values(1004,'wjqbest');
    

82. 1 row created.
    

83. 
    

84. SYS@seiang11g>commit;
    

85. Commit complete.
    

86. 
    

87. --查看test4表中数据的内容
    

88. SYS@seiang11g>select * from seiang.test4;
    

89. 
    

90.         ID NAME
    

91. ---------- --------------------------------------------------
    

92.       1001 wjq
    

93.       1002 seiang
    

94.       1003 wjqgood
    

95.       1004 wjqbest
    

96. 
    

97. 
    

98. --模拟test表空间中数据文件损坏或丢失，以及控制文件损坏
    

99. SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/test01.dbf
    

100. SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/control01.ctl
     

101. 
     

102. SYS@seiang11g>host rm /u01/app/oracle/fast_recovery_area/OraDB11g/control02.ctl
     

103. 
     

104. 
     

105. --数据库已经宕机，无法访问
     

106. SYS@seiang11g>select * from seiang.test4;
     

107. select * from seiang.test4
     

108. *
     

109. ERROR at line 1:
     

110. ORA-03135: connection lost contact
     

111. Process ID: 17679
     

112. Session ID: 34 Serial number: 531

1. --还原所有的数据文件和控制文件，准备做不完全恢复

   SYS@seiang11g>host cp /u01/app/oracle/UMAN_Backup/*.dbf /u01/app/oracle/oradata/OraDB11g/

   
   

   SYS@seiang11g>host cp /u01/app/oracle/oradata/OraDB11g/control.bak /u01/app/oracle/oradata/OraDB11g/control01.ctl

   
   

   SYS@seiang11g>host cp /u01/app/oracle/oradata/OraDB11g/control.bak /u01/app/oracle/fast_recovery_area/OraDB11g/control02.ctl

   
   

2. --查看控制文件和数据文件头所记录的SCN，发现test01.dbf数据文件头没有记录
   

3. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile;
   

4. 
   

5.      FILE# CHECKPOINT_CHANGE# NAME
   

6. ---------- ------------------ --------------------------------------------------
   

7.          1            1981768 /u01/app/oracle/oradata/OraDB11g/system01.dbf
   

8.          2            1981768 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
   

9.          3            1981768 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
   

10.          4            1981768 /u01/app/oracle/oradata/OraDB11g/users01.dbf
    

11.          5            1981768 /u01/app/oracle/oradata/OraDB11g/example01.dbf
    

12.          6            1981768 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
    

13.          7            1981768 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
    

14.          8            1981768 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
    

15.          9            1981768 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
    

16.         10            1986000 /u01/app/oracle/oradata/OraDB11g/test01.dbf
    

17. 
    

18. SYS@seiang11g>
    

19. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile_header;
    

20. 
    

21.      FILE# CHECKPOINT_CHANGE# NAME
    

22. ---------- ------------------ --------------------------------------------------
    

23.          1            1913765 /u01/app/oracle/oradata/OraDB11g/system01.dbf
    

24.          2            1913765 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
    

25.          3            1913765 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
    

26.          4            1913765 /u01/app/oracle/oradata/OraDB11g/users01.dbf
    

27.          5            1913765 /u01/app/oracle/oradata/OraDB11g/example01.dbf
    

28.          6            1913765 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
    

29.          7            1913765 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
    

30.          8            1913765 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
    

31.          9            1913765 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
    

32.         10                  0
    

33. 
    

34. 
    

35. 可以看出：
    

36. ①    file10在控制文件里记录是test01.dbf,而与之对应的数据文件10是不存在的，
    

37. ②    备份的数据备份的SCN比控制文件SCN还老。
    

38. 
    

39. 
    

40. --查看需要恢复的数据文件
    

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

42. 
    

43.      FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
    

44. ---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
    

45.          1 ONLINE  ONLINE  UNKNOWN ERROR                                                        1913765 02-AUG-17
    

46.          2 ONLINE  ONLINE  UNKNOWN ERROR                                                        1913765 02-AUG-17
    

47.          3 ONLINE  ONLINE  UNKNOWN ERROR                                                        1913765 02-AUG-17
    

48.          4 ONLINE  ONLINE  UNKNOWN ERROR                                                        1913765 02-AUG-17
    

49.          5 ONLINE  ONLINE  UNKNOWN ERROR                                                        1913765 02-AUG-17
    

50.          6 ONLINE  ONLINE  UNKNOWN ERROR                                                        1913765 02-AUG-17
    

51.          7 ONLINE  ONLINE  UNKNOWN ERROR                                                        1913765 02-AUG-17
    

52.          8 ONLINE  ONLINE  UNKNOWN ERROR                                                        1913765 02-AUG-17
    

53.          9 ONLINE  ONLINE  UNKNOWN ERROR                                                        1913765 02-AUG-17
    

54.         10 ONLINE  ONLINE  FILE NOT FOUND                                                             0
    

55. 
    

56.     
    

57. --尝试做完全恢复，提示使用备份的控制文件来恢复
    

58. SYS@seiang11g>recover database;
    

59. ORA-00283: recovery session canceled due to errors
    

60. ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
    

61. 
    

62. 
    

63. --使用备份的控制文件来做恢复，出现报错
    

64. SYS@seiang11g>recover database using backup controlfile;
    

65. ORA-00283: recovery session canceled due to errors
    

66. ORA-01110: data file 10: '/u01/app/oracle/oradata/OraDB11g/test01.dbf'
    

67. ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
    

68. ORA-01110: data file 10: '/u01/app/oracle/oradata/OraDB11g/test01.dbf'
    

69. 
    

70. 此错是因为老备份里没有abcd表空间，但只要控制文件里记录了abcd就好办，方法是建一个datafile的空文件，而其中内容可由日志文件recover(前滚)时填补出来。
    

71. 
    

72. 
    

73. --新建一个数据文件
    

74. SYS@seiang11g>alter database create datafile '/u01/app/oracle/oradata/OraDB11g/test01.dbf';
    

75. Database altered.
    

76. 
    

77. 
    

78. --再次查看控制文件和数据文件头中做记录的SCN
    

79. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile;
    

80. 
    

81.      FILE# CHECKPOINT_CHANGE# NAME
    

82. ---------- ------------------ --------------------------------------------------
    

83.          1            1981768 /u01/app/oracle/oradata/OraDB11g/system01.dbf
    

84.          2            1981768 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
    

85.          3            1981768 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
    

86.          4            1981768 /u01/app/oracle/oradata/OraDB11g/users01.dbf
    

87.          5            1981768 /u01/app/oracle/oradata/OraDB11g/example01.dbf
    

88.          6            1981768 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
    

89.          7            1981768 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
    

90.          8            1981768 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
    

91.          9            1981768 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
    

92.         10            1986000 /u01/app/oracle/oradata/OraDB11g/test01.dbf
    

93. 
    

94. 10 rows selected.
    

95. 
    

96. SYS@seiang11g>
    

97. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile_header;
    

98. 
    

99.      FILE# CHECKPOINT_CHANGE# NAME
    

100. ---------- ------------------ --------------------------------------------------
     

101.          1            1913766 /u01/app/oracle/oradata/OraDB11g/system01.dbf
     

102.          2            1913766 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
     

103.          3            1913766 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
     

104.          4            1913766 /u01/app/oracle/oradata/OraDB11g/users01.dbf
     

105.          5            1913766 /u01/app/oracle/oradata/OraDB11g/example01.dbf
     

106.          6            1913766 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
     

107.          7            1913766 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
     

108.          8            1913766 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
     

109.          9            1913766 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
     

110.         10            1985999 /u01/app/oracle/oradata/OraDB11g/test01.dbf
     

111. 
     

112. 
     

113. --再次使用备份的控制文件来做恢复
     

114. SYS@seiang11g>recover database using backup controlfile;
     

115. ORA-00279: change 1913766 generated at 08/02/2017 11:12:22 needed for thread 1
     

116. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_1.log
     

117. ORA-00280: change 1913766 for thread 1 is in sequence #1
     

118. 
     

119. Specify log: {=suggested | filename | AUTO | CANCEL}
     

120. auto (因为需要的日志已经归档，所以选择auto)
     

121. ORA-00279: change 1914386 generated at 08/02/2017 14:48:59 needed for thread 1
     

122. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_2.log
     

123. ORA-00280: change 1914386 for thread 1 is in sequence #2
     

124. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950971495_1.log' no longer needed for this recovery
     

125. 
     

126. 
     

127. ORA-00279: change 1914402 generated at 08/02/2017 14:59:56 needed for thread 1
     

128. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_1.log
     

129. ORA-00280: change 1914402 for thread 1 is in sequence #1
     

130. 
     

131. 
     

132. ORA-00279: change 1936446 generated at 08/02/2017 15:39:52 needed for thread 1
     

133. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_2.log
     

134. ORA-00280: change 1936446 for thread 1 is in sequence #2
     

135. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_1.log' no longer needed for this recovery
     

136. 
     

137. 
     

138. ORA-00279: change 1937042 generated at 08/02/2017 15:49:42 needed for thread 1
     

139. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_3.log
     

140. ORA-00280: change 1937042 for thread 1 is in sequence #3
     

141. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_2.log' no longer needed for this recovery
     

142. 
     

143. 
     

144. ORA-00279: change 1937100 generated at 08/02/2017 15:51:34 needed for thread 1
     

145. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_4.log
     

146. ORA-00280: change 1937100 for thread 1 is in sequence #4
     

147. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_3.log' no longer needed for this recovery
     

148. 
     

149. 
     

150. ORA-00279: change 1937111 generated at 08/02/2017 16:23:53 needed for thread 1
     

151. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_1.log
     

152. ORA-00280: change 1937111 for thread 1 is in sequence #1
     

153. 
     

154. 
     

155. ORA-00279: change 1955524 generated at 08/02/2017 22:00:32 needed for thread 1
     

156. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_2.log
     

157. ORA-00280: change 1955524 for thread 1 is in sequence #2
     

158. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_1.log' no longer needed for this recovery
     

159. 
     

160. 
     

161. ORA-00279: change 1981768 generated at 08/03/2017 08:19:54 needed for thread 1
     

162. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_3.log
     

163. ORA-00280: change 1981768 for thread 1 is in sequence #3
     

164. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_2.log' no longer needed for this recovery
     

165. 
     

166. 
     

167. ORA-00279: change 1986580 generated at 08/03/2017 10:00:19 needed for thread 1
     

168. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_4.log
     

169. ORA-00280: change 1986580 for thread 1 is in sequence #4
     

170. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_3.log' no longer needed for this recovery
     

171. 
     

172. 
     

173. ORA-00308: cannot open archived log '/u01/app/oracle/arch/arch_1_950977433_4.log'
     

174. ORA-27037: unable to obtain file status
     

175. Linux-x86_64 Error: 2: No such file or directory
     

176. Additional information: 3
     

177. 
     

178. 出现此错误，因为当前的当前的日志文件尚未归档，所以出现错误，所以接下来使用当前的日志文件来做恢复
     

179. 
     

180. SYS@seiang11g>recover database using backup controlfile;
     

181. ORA-00279: change 1986580 generated at 08/03/2017 10:00:19 needed for thread 1
     

182. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_4.log
     

183. ORA-00280: change 1986580 for thread 1 is in sequence #4
     

184. 
     

185. 
     

186. Specify log: {=suggested | filename | AUTO | CANCEL}
     

187. /u01/app/oracle/oradata/OraDB11g/redo01.log （当前日志文件）
     

188. Log applied.
     

189. Media recovery complete.
     

190. SYS@seiang11g>
     

191. 
     

192. 
     

193. --恢复完成，使用resetlogs打开数据库
     

194. SYS@seiang11g>alter database open resetlogs;
     

195. Database altered.
     

196. 
     

197. --查看控制文件和数据文件头记录的SCN一致
     

198. SYS@seiang11g>select file#,checkpoint_change# from v$datafile;
     

199. 
     

200.      FILE# CHECKPOINT_CHANGE#
     

201. ---------- ------------------
     

202.          1            1986883
     

203.          2            1986883
     

204.          3            1986883
     

205.          4            1986883
     

206.          5            1986883
     

207.          6            1986883
     

208.          7            1986883
     

209.          8            1986883
     

210.          9            1986883
     

211.         10            1986883
     

212. 
     

213. SYS@seiang11g>select file#,checkpoint_change# from v$datafile_header;
     

214. 
     

215.      FILE# CHECKPOINT_CHANGE#
     

216. ---------- ------------------
     

217.          1            1986883
     

218.          2            1986883
     

219.          3            1986883
     

220.          4            1986883
     

221.          5            1986883
     

222.          6            1986883
     

223.          7            1986883
     

224.          8            1986883
     

225.          9            1986883
     

226.         10            1986883
     

227. 
     

228. --确认test4表中的数据全部恢复成功
     

229. SYS@seiang11g>select * from seiang.test4;
     

230. 
     

231.         ID NAME
     

232. ---------- --------------------------------------------------
     

233.       1001 wjq
     

234.       1002 seiang
     

235.       1003 wjqgood
     

236.       1004 wjqbest

示例二：冷备份所有数据文件--->备份控制文件--->新建表空间（日志文件完好）

实验环境：

当前的控制文件损坏，新创建的表空间损坏，冷备的数据文件中没有该数据文件的备份，控制文件中也没有该表空间的记录，但是联机日志中有相关的记录；由于控制文件损坏，只能使用备份的控制文件来做恢复。