行业资讯

Oracle如何使用备份控制文件

发布时间：2021-11-10 09:49:44 作者：小新
来源：亿速云 阅读：164

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




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

实验环境：

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

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

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


  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


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

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


  20.     GROUP# SEQUENCE# STATUS

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

  22.          1          1 INACTIVE

  23.          2          2 INACTIVE

  24.          3          3 CURRENT



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

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

  29. Tablespace created.



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

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

  34. Database altered.


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

  37. SYS@seiang11g>show parameter control


  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



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

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

  51. Table created.



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

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

  56. 1 row created.


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

  59. 1 row created.


  61. SYS@seiang11g>commit;

  62. Commit complete.


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

  65. SYS@seiang11g>alter system switch logfile;

  66. System altered.


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

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


  71.     GROUP# SEQUENCE# STATUS

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

  73.          1          4 CURRENT

  74.          2          2 INACTIVE

  75.          3          3 ACTIVE


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

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

  79. 1 row created.


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

  82. 1 row created.


  84. SYS@seiang11g>commit;

  85. Commit complete.


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

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


  90.         ID NAME

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

  92.       1001 wjq

  93.       1002 seiang

  94.       1003 wjqgood

  95.       1004 wjqbest



  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


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



  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;


  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


  18. SYS@seiang11g>

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


  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



  35. 可以看出：

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

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



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

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


  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


  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



  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'


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



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

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

  75. Database altered.



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

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


  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


  94. 10 rows selected.


  96. SYS@seiang11g>

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


  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



  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


  119. Specify log: {<RET>=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



  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



  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



  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



  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



  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



  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



  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



  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



  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


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


  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



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

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

  188. Log applied.

  189. Media recovery complete.

  190. SYS@seiang11g>



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

  194. SYS@seiang11g>alter database open resetlogs;

  195. Database altered.


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

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


  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


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


  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


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

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


  231.         ID NAME

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

  233.       1001 wjq

  234.       1002 seiang

  235.       1003 wjqgood

  236.       1004 wjqbest



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

 

实验环境：

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


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

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


  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



  18. --备份控制文件

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

  20. Database altered.



  23. -创建表空间comsys该表空间记录在当前的日志redo01.log中

  24. SYS@seiang11g>create tablespace comsys datafile '/u01/app/oracle/oradata/OraDB11g/comsys01.dbf' size 5M;

  25. Tablespace created.



  28. --在seiang用户下创建一张表test4,隶属于comsys表空间

  29. SYS@seiang11g>create table seiang.test4(age number,address varchar2(10)) tablespace comsys;

  30. Table created.


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

  33. SYS@seiang11g>insert into seiang.test4 values(23,'beijing');

  34. 1 row created.


  36. SYS@seiang11g>insert into seiang.test4 values(25,'shanghai');

  37. 1 row created.


  39. SYS@seiang11g>commit;

  40. Commit complete.


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


  44.        AGE ADDRESS

  45. ---------- ----------

  46.         23 beijing

  47.         25 shanghai



  50. --查看当前日志的序列号为1

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


  53.     GROUP# SEQUENCE# STATUS

  54. ---------- ---------- ----------------

  55.          1          1 CURRENT

  56.          2          0 UNUSED

  57.          3          0 UNUSED



  60. --模拟comsys01.dbf数据文件丢失或损坏，控制文件损坏

  61. SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/comsys01.dbf


  63. SYS@seiang11g>shutdown abort

  64. ORACLE instance shut down.


  66. --从备份的文件中还原控制文件和数据文件

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


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


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

  1. SYS@seiang11g>startup

  2. ORACLE instance started.


  4. Total System Global Area 1252663296 bytes

  5. Fixed Size 2252824 bytes

  6. Variable Size 788533224 bytes

  7. Database Buffers          452984832 bytes

  8. Redo Buffers                8892416 bytes

  9. Database mounted.

  10. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open



  13. --查看控制文件和数据文件头，发现并没有comsys表空间的相关记录

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


  16.      FILE# CHECKPOINT_CHANGE# NAME

  17. ---------- ------------------ --------------------------------------------------

  18.          1            1986883 /u01/app/oracle/oradata/OraDB11g/system01.dbf

  19.          2            1986883 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

  20.          3            1986883 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

  21.          4            1986883 /u01/app/oracle/oradata/OraDB11g/users01.dbf

  22.          5            1986883 /u01/app/oracle/oradata/OraDB11g/example01.dbf

  23.          6            1986883 /u01/app/oracle/oradata/OraDB11g/rman01.dbf

  24.          7            1986883 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

  25.          8            1986883 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf

  26.          9            1986883 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf



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


  31.      FILE# CHECKPOINT_CHANGE# NAME

  32. ---------- ------------------ --------------------------------------------------

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

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

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

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

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

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

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

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

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



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

  45. SYS@seiang11g>recover database;

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

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



  50. --使用备份的控制文件做恢复

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

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

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

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



  57. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

  58. auto    （该日志已归档，所以选择auto）

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

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

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

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



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

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

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



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

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

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

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



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

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

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

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



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

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

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

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



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

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

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



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

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

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

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



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

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

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

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



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

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

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

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



  111. ORA-00279: change 1986880 generated at 08/03/2017 10:33:47 needed for thread 1

  112. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_951042827_1.log

  113. ORA-00280: change 1986880 for thread 1 is in sequence #1



  116. ORA-00308: cannot open archived log '/u01/app/oracle/arch/arch_1_951042827_1.log'

  117. ORA-27037: unable to obtain file status

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

  119. Additional information: 3


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



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

  125. ORA-00279: change 1986880 generated at 08/03/2017 10:33:47 needed for thread 1

  126. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_951042827_1.log

  127. ORA-00280: change 1986880 for thread 1 is in sequence #1



  130. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

  131. /u01/app/oracle/oradata/OraDB11g/redo01.log (当前的日志文件)

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

  133. ORA-01244: unnamed datafile(s) added to control file by media recovery

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

  135. (从当前的日志文件中，我们发现了关于comsys表空间的相关记录)


  137. ORA-01112: media recovery not started



  140. 当再次使用备份的控制文件做恢复时，出现如下的错误提示

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

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

  143. ORA-01111: name for data file 10 is unknown - rename to correct file

  144. ORA-01110: data file 10: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00010'

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

  146. ORA-01111: name for data file 10 is unknown - rename to correct file

  147. ORA-01110: data file 10: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00010'


  149. --查看控制文件和数据文件头，有了关于comsys表空间的相关记录

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


  152.      FILE# CHECKPOINT_CHANGE# NAME

  153. ---------- ------------------ --------------------------------------------------

  154.          1            1986883 /u01/app/oracle/oradata/OraDB11g/system01.dbf

  155.          2            1986883 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

  156.          3            1986883 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

  157.          4            1986883 /u01/app/oracle/oradata/OraDB11g/users01.dbf

  158.          5            1986883 /u01/app/oracle/oradata/OraDB11g/example01.dbf

  159.          6            1986883 /u01/app/oracle/oradata/OraDB11g/rman01.dbf

  160.          7            1986883 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

  161.          8            1986883 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf

  162.          9            1986883 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf

  163.         10            1988334 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAME

  164.                               D00010



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


  169.      FILE# CHECKPOINT_CHANGE# NAME

  170. ---------- ------------------ --------------------------------------------------

  171.          1            1988336 /u01/app/oracle/oradata/OraDB11g/system01.dbf

  172.          2            1988336 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

  173.          3            1988336 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

  174.          4            1988336 /u01/app/oracle/oradata/OraDB11g/users01.dbf

  175.          5            1988336 /u01/app/oracle/oradata/OraDB11g/example01.dbf

  176.          6            1988336 /u01/app/oracle/oradata/OraDB11g/rman01.dbf

  177.          7            1988336 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

  178.          8            1988336 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf

  179.          9            1988336 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf

  180.         10                  0


  182. --创建数据文件，并对控制文件中记录未知的数据文件重命名

  183. SYS@seiang11g>alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00010'

  184.   2 as '/u01/app/oracle/oradata/OraDB11g/comsys01.dbf';

  185. Database altered.


  187. (当前的日志文件)

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


  190.      FILE# CHECKPOINT_CHANGE# NAME

  191. ---------- ------------------ --------------------------------------------------

  192.          1            1986883 /u01/app/oracle/oradata/OraDB11g/system01.dbf

  193.          2            1986883 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

  194.          3            1986883 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

  195.          4            1986883 /u01/app/oracle/oradata/OraDB11g/users01.dbf

  196.          5            1986883 /u01/app/oracle/oradata/OraDB11g/example01.dbf

  197.          6            1986883 /u01/app/oracle/oradata/OraDB11g/rman01.dbf

  198.          7            1986883 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

  199.          8            1986883 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf

  200.          9            1986883 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf

  201.         10            1988334 /u01/app/oracle/oradata/OraDB11g/comsys01.dbf



  204. --再次查看控制文件和数据文件头

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


  207.      FILE# CHECKPOINT_CHANGE# NAME

  208. ---------- ------------------ --------------------------------------------------

  209.          1            1988336 /u01/app/oracle/oradata/OraDB11g/system01.dbf

  210.          2            1988336 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

  211.          3            1988336 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

  212.          4            1988336 /u01/app/oracle/oradata/OraDB11g/users01.dbf

  213.          5            1988336 /u01/app/oracle/oradata/OraDB11g/example01.dbf

  214.          6            1988336 /u01/app/oracle/oradata/OraDB11g/rman01.dbf

  215.          7            1988336 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

  216.          8            1988336 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf

  217.          9            1988336 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf

  218.         10            1988334 /u01/app/oracle/oradata/OraDB11g/comsys01.dbf



  221. --再次使用备份的控制文件和当前日志做恢复

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

  223. ORA-00279: change 1988334 generated at 08/03/2017 10:53:39 needed for thread 1

  224. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_951042827_1.log

  225. ORA-00280: change 1988334 for thread 1 is in sequence #1



  228. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

  229. /u01/app/oracle/oradata/OraDB11g/redo01.log

  230. Log applied.

  231. Media recovery complete.



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

  235. SYS@seiang11g>alter database open resetlogs;

  236. Database altered.



  239. --查看控制文件和数据文件头SCN一致

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


  242.      FILE# CHECKPOINT_CHANGE# NAME

  243. ---------- ------------------ --------------------------------------------------

  244.          1            1989738 /u01/app/oracle/oradata/OraDB11g/system01.dbf

  245.          2            1989738 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

  246.          3            1989738 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

  247.          4            1989738 /u01/app/oracle/oradata/OraDB11g/users01.dbf

  248.          5            1989738 /u01/app/oracle/oradata/OraDB11g/example01.dbf

  249.          6            1989738 /u01/app/oracle/oradata/OraDB11g/rman01.dbf

  250.          7            1989738 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

  251.          8            1989738 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf

  252.          9            1989738 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf

  253.         10            1989738 /u01/app/oracle/oradata/OraDB11g/comsys01.dbf



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


  258.      FILE# CHECKPOINT_CHANGE# NAME

  259. ---------- ------------------ --------------------------------------------------

  260.          1            1989738 /u01/app/oracle/oradata/OraDB11g/system01.dbf

  261.          2            1989738 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

  262.          3            1989738 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

  263.          4            1989738 /u01/app/oracle/oradata/OraDB11g/users01.dbf

  264.          5            1989738 /u01/app/oracle/oradata/OraDB11g/example01.dbf

  265.          6            1989738 /u01/app/oracle/oradata/OraDB11g/rman01.dbf

  266.          7            1989738 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

  267.          8            1989738 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf

  268.          9            1989738 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf

  269.         10            1989738 /u01/app/oracle/oradata/OraDB11g/comsys01.dbf



  272. --查看已恢复test4表中的数据记录

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


  275.        AGE ADDRESS

  276. ---------- ----------

  277.         23 beijing

  278.         25 shanghai

以上是“Oracle如何使用备份控制文件”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！

推荐阅读：
  1. Oracle控制文件移动
  2. Oracle RMAN备份之控制文件备份

免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。

oracle

上一篇：使用rownum分页排序前后几次查询数据不一样的示例分析

下一篇：Django中的unittest应用是什么

相关阅读

您好，登录后才能下订单哦！

密码登录
登录注册
获取短信验证码
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》
产品服务
地区划分
帮助支持
关于我们
行业资讯-文章归档 问答-问答归档
广州亿速云计算有限公司

7*24小时在线电话：400-100-2938

7*24小时在线QQ：800811969

Copyright © Yisu Cloud Ltd. All Rights Reserved. 2018 版权所有
粤ICP备17096448号-1 粤公网安备 44010402001142号