温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

Oracle如何使用备份控制文件

发布时间:2021-11-10 09:49:44 来源:亿速云 阅读:162 作者:小新 栏目:关系型数据库

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




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

实验环境:

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

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

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


  3.        TS# NAME                                               INC BIG FLA ENC

  4. ---------- -------------------------------------------------- --- --- --- ---

  5.          0 SYSTEM                                             YES NO  YES

  6.          1 SYSAUX                                             YES NO  YES

  7.          2 UNDOTBS1                                           YES NO  YES

  8.          4 USERS                                              YES NO  YES

  9.          3 TEMP                                               NO  NO  YES

  10.          6 EXAMPLE                                            YES NO  YES

  11.          7 RMAN_CATALOG                                       YES NO  YES

  12.          8 SEIANG                                             YES NO  YES

  13.          9 WJQ                                                YES NO  YES

  14.         10 WJQBEST                                            YES NO  YES


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

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


  17.     GROUP# SEQUENCE# STATUS

  18. ---------- ---------- ----------------

  19.          1          1 INACTIVE

  20.          2          2 INACTIVE

  21.          3          3 CURRENT



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

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

  24. Tablespace created.



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

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

  27. Database altered.


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

  29. SYS@seiang11g>show parameter control


  30. NAME TYPE VALUE

  31. ------------------------------------ ----------- ------------------------------

  32. control_file_record_keep_time integer 14

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

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

  35.                                                  cle/fast_recovery_area/OraDB11

  36.                                                  g/control02.ctl

  37. control_management_pack_access       string      DIAGNOSTIC+TUNING



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

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

  40. Table created.



  41. --在test4表中插入两条数据,并提交

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

  43. 1 row created.


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

  45. 1 row created.


  46. SYS@seiang11g>commit;

  47. Commit complete.


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

  49. SYS@seiang11g>alter system switch logfile;

  50. System altered.


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

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


  53.     GROUP# SEQUENCE# STATUS

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

  55.          1          4 CURRENT

  56.          2          2 INACTIVE

  57.          3          3 ACTIVE


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

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

  60. 1 row created.


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

  62. 1 row created.


  63. SYS@seiang11g>commit;

  64. Commit complete.


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

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


  67.         ID NAME

  68. ---------- --------------------------------------------------

  69.       1001 wjq

  70.       1002 seiang

  71.       1003 wjqgood

  72.       1004 wjqbest



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

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

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


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



  77. --数据库已经宕机,无法访问

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

  79. select * from seiang.test4

  80. *

  81. ERROR at line 1:

  82. ORA-03135: connection lost contact

  83. Process ID: 17679

  84. 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.      FILE# CHECKPOINT_CHANGE# NAME

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

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

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

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

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

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

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

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

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

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

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


  16. SYS@seiang11g>

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


  18.      FILE# CHECKPOINT_CHANGE# NAME

  19. ---------- ------------------ --------------------------------------------------

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

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

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

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

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

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

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

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

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

  29.         10                  0



  30. 可以看出:

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

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



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

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


  35.      FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME

  36. ---------- ------- ------- ----------------------------------------------------------------- ---------- ---------

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

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

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

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

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

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

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

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

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

  46.         10 ONLINE  ONLINE  FILE NOT FOUND                                                             0


  47.     

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

  49. SYS@seiang11g>recover database;

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

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



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

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

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

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

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

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


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



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

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

  61. Database altered.



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

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


  64.      FILE# CHECKPOINT_CHANGE# NAME

  65. ---------- ------------------ --------------------------------------------------

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

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

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

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

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

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

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

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

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

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


  76. 10 rows selected.


  77. SYS@seiang11g>

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


  79.      FILE# CHECKPOINT_CHANGE# NAME

  80. ---------- ------------------ --------------------------------------------------

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

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

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

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

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

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

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

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

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

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



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

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

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

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

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


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

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

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

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

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

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



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

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

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



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

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

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

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



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

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

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

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



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

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

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

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



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

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

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



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

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

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

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



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

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

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

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



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

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

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

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



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

  133. ORA-27037: unable to obtain file status

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

  135. Additional information: 3


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


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

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

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

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



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

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

  143. Log applied.

  144. Media recovery complete.

  145. SYS@seiang11g>



  146. --恢复完成,使用resetlogs打开数据库

  147. SYS@seiang11g>alter database open resetlogs;

  148. Database altered.


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

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


  151.      FILE# CHECKPOINT_CHANGE#

  152. ---------- ------------------

  153.          1            1986883

  154.          2            1986883

  155.          3            1986883

  156.          4            1986883

  157.          5            1986883

  158.          6            1986883

  159.          7            1986883

  160.          8            1986883

  161.          9            1986883

  162.         10            1986883


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


  164.      FILE# CHECKPOINT_CHANGE#

  165. ---------- ------------------

  166.          1            1986883

  167.          2            1986883

  168.          3            1986883

  169.          4            1986883

  170.          5            1986883

  171.          6            1986883

  172.          7            1986883

  173.          8            1986883

  174.          9            1986883

  175.         10            1986883


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

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


  178.         ID NAME

  179. ---------- --------------------------------------------------

  180.       1001 wjq

  181.       1002 seiang

  182.       1003 wjqgood

  183.       1004 wjqbest



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

 

实验环境:

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


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

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


  3.        TS# NAME                                               INC BIG FLA ENC

  4. ---------- -------------------------------------------------- --- --- --- ---

  5.          0 SYSTEM                                             YES NO  YES

  6.          1 SYSAUX                                             YES NO  YES

  7.          2 UNDOTBS1                                           YES NO  YES

  8.          4 USERS                                              YES NO  YES

  9.          3 TEMP                                               NO  NO  YES

  10.          6 EXAMPLE                                            YES NO  YES

  11.          7 RMAN_CATALOG                                       YES NO  YES

  12.          8 SEIANG                                             YES NO  YES

  13.          9 WJQ                                                YES NO  YES

  14.         10 WJQBEST                                            YES NO  YES



  15. --备份控制文件

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

  17. Database altered.



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

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

  20. Tablespace created.



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

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

  23. Table created.


  24. --在test4表中插入两条数据,并提交

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

  26. 1 row created.


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

  28. 1 row created.


  29. SYS@seiang11g>commit;

  30. Commit complete.


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


  32.        AGE ADDRESS

  33. ---------- ----------

  34.         23 beijing

  35.         25 shanghai



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

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


  38.     GROUP# SEQUENCE# STATUS

  39. ---------- ---------- ----------------

  40.          1          1 CURRENT

  41.          2          0 UNUSED

  42.          3          0 UNUSED



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

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


  45. SYS@seiang11g>shutdown abort

  46. ORACLE instance shut down.


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

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


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


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

  1. SYS@seiang11g>startup

  2. ORACLE instance started.


  3. Total System Global Area 1252663296 bytes

  4. Fixed Size 2252824 bytes

  5. Variable Size 788533224 bytes

  6. Database Buffers          452984832 bytes

  7. Redo Buffers                8892416 bytes

  8. Database mounted.

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



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

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


  12.      FILE# CHECKPOINT_CHANGE# NAME

  13. ---------- ------------------ --------------------------------------------------

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

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

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

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

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

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

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

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

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



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


  24.      FILE# CHECKPOINT_CHANGE# NAME

  25. ---------- ------------------ --------------------------------------------------

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

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

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

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

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

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

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

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

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



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

  36. SYS@seiang11g>recover database;

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

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



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

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

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

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

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



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

  45. auto    (该日志已归档,所以选择auto)

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

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

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

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



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

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

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



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

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

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

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



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

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

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

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



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

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

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

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



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

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

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



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

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

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

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



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

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

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

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



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

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

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

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



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

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

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



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

  84. ORA-27037: unable to obtain file status

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

  86. Additional information: 3


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



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

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

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

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



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

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

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

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

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

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


  98. ORA-01112: media recovery not started



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

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

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

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

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

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

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

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


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

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


  109.      FILE# CHECKPOINT_CHANGE# NAME

  110. ---------- ------------------ --------------------------------------------------

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

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

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

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

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

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

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

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

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

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

  121.                               D00010



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


  123.      FILE# CHECKPOINT_CHANGE# NAME

  124. ---------- ------------------ --------------------------------------------------

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

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

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

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

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

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

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

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

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

  134.         10                  0


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

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

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

  138. Database altered.


  139. (当前的日志文件)

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


  141.      FILE# CHECKPOINT_CHANGE# NAME

  142. ---------- ------------------ --------------------------------------------------

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

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

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

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

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

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

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

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

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

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



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

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


  155.      FILE# CHECKPOINT_CHANGE# NAME

  156. ---------- ------------------ --------------------------------------------------

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

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

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

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

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

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

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

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

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

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



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

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

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

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

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



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

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

  174. Log applied.

  175. Media recovery complete.



  176. --恢复完成后,使用resetlogs打开数据库

  177. SYS@seiang11g>alter database open resetlogs;

  178. Database altered.



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

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


  181.      FILE# CHECKPOINT_CHANGE# NAME

  182. ---------- ------------------ --------------------------------------------------

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

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

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

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

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

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

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

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

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

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



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


  194.      FILE# CHECKPOINT_CHANGE# NAME

  195. ---------- ------------------ --------------------------------------------------

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

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

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

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

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

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

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

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

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

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



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

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


  208.        AGE ADDRESS

  209. ---------- ----------

  210.         23 beijing

  211.         25 shanghai

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

向AI问一下细节

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

AI