Oracle手工不完全恢复（二）：使用备份控制文件

最新推荐文章于 2021-04-07 16:53:57 发布
最新推荐文章于 2021-04-07 16:53:57 发布
阅读量153 收藏
点赞数


相关连接：
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

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

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


--查看控制文件和数据文件头所记录的SCN，发现test01.dbf数据文件头没有记录
 SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile;

     FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ --------------------------------------------------
         1 1981768 /u01/app/oracle/oradata/OraDB11g/system01.dbf
         2 1981768 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
         3 1981768 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
         4 1981768 /u01/app/oracle/oradata/OraDB11g/users01.dbf
         5 1981768 /u01/app/oracle/oradata/OraDB11g/example01.dbf
         6 1981768 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
         7 1981768 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
         8 1981768 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
         9 1981768 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
        10 1986000 /u01/app/oracle/oradata/OraDB11g/test01.dbf

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

     FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ --------------------------------------------------
         1 1913765 /u01/app/oracle/oradata/OraDB11g/system01.dbf
         2 1913765 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
         3 1913765 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
         4 1913765 /u01/app/oracle/oradata/OraDB11g/users01.dbf
         5 1913765 /u01/app/oracle/oradata/OraDB11g/example01.dbf
         6 1913765 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
         7 1913765 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
         8 1913765 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
         9 1913765 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
        10 0


可以看出：
①    file10在控制文件里记录是test01.dbf,而与之对应的数据文件10是不存在的，
②    备份的数据备份的SCN比控制文件SCN还老。


--查看需要恢复的数据文件
SYS@seiang11g>select * from v$recover_file;

     FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
         1 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
         2 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
         3 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
         4 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
         5 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
         6 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
         7 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
         8 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
         9 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
        10 ONLINE ONLINE FILE NOT FOUND 0

    
--尝试做完全恢复，提示使用备份的控制文件来恢复
SYS@seiang11g>recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


--使用备份的控制文件来做恢复，出现报错
SYS@seiang11g>recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 10: '/u01/app/oracle/oradata/OraDB11g/test01.dbf'
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/u01/app/oracle/oradata/OraDB11g/test01.dbf'

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


--新建一个数据文件
SYS@seiang11g>alter database create datafile '/u01/app/oracle/oradata/OraDB11g/test01.dbf';
Database altered.


--再次查看控制文件和数据文件头中做记录的SCN
SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile;

     FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ --------------------------------------------------
         1 1981768 /u01/app/oracle/oradata/OraDB11g/system01.dbf
         2 1981768 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
         3 1981768 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
         4 1981768 /u01/app/oracle/oradata/OraDB11g/users01.dbf
         5 1981768 /u01/app/oracle/oradata/OraDB11g/example01.dbf
         6 1981768 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
         7 1981768 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
         8 1981768 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
         9 1981768 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
        10 1986000 /u01/app/oracle/oradata/OraDB11g/test01.dbf

10 rows selected.

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

     FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ --------------------------------------------------
         1 1913766 /u01/app/oracle/oradata/OraDB11g/system01.dbf
         2 1913766 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
         3 1913766 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
         4 1913766 /u01/app/oracle/oradata/OraDB11g/users01.dbf
         5 1913766 /u01/app/oracle/oradata/OraDB11g/example01.dbf
         6 1913766 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
         7 1913766 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
         8 1913766 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
         9 1913766 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
        10 1985999 /u01/app/oracle/oradata/OraDB11g/test01.dbf


--再次使用备份的控制文件来做恢复
SYS@seiang11g>recover database using backup controlfile;
ORA-00279: change 1913766 generated at 08/02/2017 11:12:22 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_1.log
ORA-00280: change 1913766 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto (因为需要的日志已经归档，所以选择auto)
ORA-00279: change 1914386 generated at 08/02/2017 14:48:59 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_2.log
ORA-00280: change 1914386 for thread 1 is in sequence #2
ORA-00278: log file '/u01/app/oracle/arch/arch_1_950971495_1.log' no longer needed for this recovery


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


ORA-00279: change 1936446 generated at 08/02/2017 15:39:52 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_2.log
ORA-00280: change 1936446 for thread 1 is in sequence #2
ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_1.log' no longer needed for this recovery


ORA-00279: change 1937042 generated at 08/02/2017 15:49:42 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_3.log
ORA-00280: change 1937042 for thread 1 is in sequence #3
ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_2.log' no longer needed for this recovery


ORA-00279: change 1937100 generated at 08/02/2017 15:51:34 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_4.log
ORA-00280: change 1937100 for thread 1 is in sequence #4
ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_3.log' no longer needed for this recovery


ORA-00279: change 1937111 generated at 08/02/2017 16:23:53 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_1.log
ORA-00280: change 1937111 for thread 1 is in sequence #1


ORA-00279: change 1955524 generated at 08/02/2017 22:00:32 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_2.log
ORA-00280: change 1955524 for thread 1 is in sequence #2
ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_1.log' no longer needed for this recovery


ORA-00279: change 1981768 generated at 08/03/2017 08:19:54 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_3.log
ORA-00280: change 1981768 for thread 1 is in sequence #3
ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_2.log' no longer needed for this recovery


ORA-00279: change 1986580 generated at 08/03/2017 10:00:19 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_4.log
ORA-00280: change 1986580 for thread 1 is in sequence #4
ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_3.log' no longer needed for this recovery


ORA-00308: cannot open archived log '/u01/app/oracle/arch/arch_1_950977433_4.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

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

SYS@seiang11g>recover database using backup controlfile;
ORA-00279: change 1986580 generated at 08/03/2017 10:00:19 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_4.log
ORA-00280: change 1986580 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/OraDB11g/redo01.log （当前日志文件）
Log applied.
Media recovery complete.
SYS@seiang11g>


--恢复完成，使用resetlogs打开数据库
SYS@seiang11g>alter database open resetlogs;
Database altered.

--查看控制文件和数据文件头记录的SCN一致
SYS@seiang11g>select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1 1986883
         2 1986883
         3 1986883
         4 1986883
         5 1986883
         6 1986883
         7 1986883
         8 1986883
         9 1986883
        10 1986883

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

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1 1986883
         2 1986883
         3 1986883
         4 1986883
         5 1986883
         6 1986883
         7 1986883
         8 1986883
         9 1986883
        10 1986883

--确认test4表中的数据全部恢复成功
SYS@seiang11g>select * from seiang.test4;

        ID NAME
---------- --------------------------------------------------
      1001 wjq
      1002 seiang
      1003 wjqgood
      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


作者：SEian.G（苦练七十二变，笑对八十一难）

ITPUB：http://blog.itpub.net/31015730/

51CTO：http://seiang.blog.51cto.com/


来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31015730/viewspace-2143113/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31015730/viewspace-2143113/

couyifu7209
关注
使用backup as copy 备份部分表空间并做不完全恢复
范小波
09-13 1966
本文是测试在不停dataguard情况下, 从dataguard 用backup as copy 方法导出部分表空间， 并基于这些表空间, 恢复出一个源库的子集。, 然后做不完全恢复, 恢复到一个指定的时间点。 （ 重点, 在dataguard 不停机情况下, 不影像dataguard的正常使用） （另外, backup as copy tablesapce， 不会在control
oracle恢复数据库文件,oracle数据库恢复系列之控制文件恢复
weixin_31626765的博客
04-04 1233
一、不同场景控制文件损坏时的恢复方法场景恢复方法恢复条件其中一个控制文件损坏1.1拷贝冗余的控制文件1、具有多路冗余控制文件镜像2、其它冗余控制文件没有损坏1.2修改control_files参数去除损坏文件同上、但不推荐该方法进行恢复所有的控制文件损坏有备份2.1通过rman备份控制文件进行完全恢复1、通过rman备份了控制文件2、备份了控制文件之后有连续的归档和redo文件2.2通过rman备...
手工转件Oracle数据库控制文件的策略
01-15
控制文件对于Oralce数据库的作用，就好像微软操作系统中注册表的作用一样。控制文件是一个比较小的二进制文件，记录着数据库的结构信息。如果数据库控制文件发生孙华的话，则Oracle将无法正常启动。通常情况下，在创建数据库时会自动创建控制文件;并且当数据库的结构发生变化时，也会自动修改自动文件的内容。
Oracle用户手工备份控制文件，trace和映像副本
demonson的专栏
09-26 1086
用户备份控制文件的方式包括：  1.创建为映像副本，写入到指定名称的文件： SQL> ALTER DATABASE BACKUP CONTROLFILE TO   '/u01/backup/controlfile.bak';   Database altered.   2.生成重新创建控制文件的脚本，写入到跟踪文件： SQL> ALTER DATABASE BACKUP CO
备份用户到d盘oracle,Oracle用户手工备份控制文件，trace和映像副本
weixin_39868663的博客
04-07 164
用户备份控制文件的方式包括： 1.创建为映像副本，写入到指定名称的文件：SQL> ALTER DATABASE BACKUP CONTROLFILE TO'/u01/backup/controlfile.bak';Database altered.2.生成重新创建控制文件的脚本，写入到跟踪文件：SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRA...
oracle手工备份恢复,Oracle 10g控制文件备份到文件与手工恢复
weixin_30539317的博客
04-04 120
SQL> !cat /data/oracle/admin/asp/udump/sdk_ora_31331.trc/data/oracle/admin/asp/udump/sdk_ora_31331.trcOracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OL...
Oracle11g备份恢复笔记文档
07-22
- **手工备份与恢复**：即用户管理的备份（UMAN），通过操作系统命令完成数据文件的备份与恢复，之后使用归档日志进行恢复。 - **自动备份与恢复**：利用 RMAN（Recovery Manager）实现自动化备份和恢复流程，支持...
Oracle 11g 备份与恢复最佳手册
10-27
- **完全恢复**: 使用完整备份或部分备份，将数据文件恢复到故障发生前的最后一个提交点，确保数据无丢失。 - **不完全恢复**: 使用完整备份和日志将数据库恢复到过去的某个时间点（或SCN），可能会导致数据丢失。 ...
oracle数据库备份与恢复方案.doc
最新发布
11-17
Oracle 数据库备份与恢复方案中使用的备份工具是 Oracle RMAN（Recovery Manager），它是一种用于备份、还原和恢复数据库的 Oracle 工具。RMAN 只能用于 ORACLE8 或更高的版本中。它能够备份整个数据库或数据库部件...
oracle数据库手工管理的备份和恢复笔记.doc
06-03
Oracle数据库的手工管理和备份恢复是数据库管理员日常工作中至关重要的任务，确保数据的安全性和可恢复性。本文档主要探讨了Oracle数据库的备份类型、恢复策略以及一些相关知识点。 首先，备份可分为物理备份和逻辑...
基于cancel的不完全恢复
cuidai9315的博客
02-19 132
场景：联机日志文件损坏或丢失，此处再增加一个数据文件也丢失的情况 步骤： 1. 查询当前表中数据，如下有初始6行记录 2. 插入一条记录变为7行 ...
所有除参数文件以外的文件都丢失，但是只有数据文件的 RMAN 备份的恢复
cqu60618的博客
03-26 163
昨天做一个实验，结果把数据库搞坏了，当试图进行恢复时居然报了RMAN-06026错误。 回想一下，原来在尝试恢复中使用了_allow_resetlogs_corruption参数，resetlogs之后，Oracle使用当前的控...
mysql 丢失控制文件恢复_Oracle数据库丢失控制文件的恢复四则
weixin_32207033的博客
02-07 484
下文介绍了Oracle数据库中丢失控制文件的几种处理方法。丢失单个控制文件报错信息：2013-05-08 03:00:29.678000 +08:00Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_5204.trc:ORA-00210: cannot open the specifiedcontrol fileORA...
Oracle数据库丢失控制文件的恢复四则
热门推荐
renfengjun
01-26 1万+
下文介绍了Oracle数据库中丢失控制文件的几种处理方法。
ORA-01207 old control file完全解决方案
wolf
03-20 7668
这个错误是oracle db最常见的错误了，引起的原因很多，但是最主要的一个原因是数据库服务器突然掉电，然后重启启动数据库报错。 究其这个问题产生的原因，其实就是控制文件中记录的db信息太老，导致数据库在启动检测时出现不一致。 控制文件中记录了整个数据库的全部信息，具体包括数据文件的，日志文件等等。 那么为什么会出现控制文件记录的数据库信息太老呢，原因很简单：根据oracle db运行原理，
一个oracle数据库的恢复测试
新路的专栏
05-16 768
<br />一个oracle数据库的恢复测试<br />1,关闭数据库完全备份<br />SQL> shutdown immediate<br />Database closed.<br />Database dismounted.<br />ORACLE instance shut down.<br /> <br />$ cp *.dbf backup1<br />$cp control01.ctl backup1<br />2,创建表并插入数据<br />SQL> startup<br />ORACLE
oracle使用catalog手动注册日志进行前滚
Perry
12-01 3252
1.手动注册归档日志 RMAN> catalog start with '/archivelog/'; searching for all files that match the pattern /archivelog/ List of Files Unknown to the Database =====================================
一看就会一做就废系列：说说 RECOVER UNTIL CANCEL
weixin_30266829的博客
06-13 933
这里是：一看就会，一做就废系列 数据库演示版本为 19.3 （12.2.0.3） 该系列涉及恢复过程中使用的5个语句： 1. recover database 2. recover database until cancel 3. recover database using backup controlfile 4. recover database until cancel...
oracle11误删了控制文件,Oracle 11g 表空间的误删除, 当前的控制文件不识别该表空间...
weixin_42493325的博客
04-05 163
--做3次日志切换--查看归档日志信息,注意resetlogs前后,incarnation的变化sys@TESTDB11>selectsequence#,namefromv$archived_log;SEQUENCE# NAME---------- ------------------------------93/archive1/1_93_813665348.dbf93/archive2/...
Oracle RMAN备份详解：自动备份与恢复控制文件
传统的手工备份和恢复方式存在很多缺点，比如会产生大量重做日志，需要手动记录备份信息，如备份文件位置、时间、数据文件位置等。而RMAN则解决了这些问题，它不仅可以备份数据文件、归档日志、控制文件和spfile，还...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值