BBED 修改文件头 解决 ORA-01190

  • yewushang

    2014-02-11 21:03:15
  • Oracle
  • 原创
问题：ORA-01190: control file or data file 6 is from before the last RESETLOGS

1.mount状态下

点击(此处)折叠或打开

  1. alter database backup controlfile to trace.


2.找到udump  ls -lrt最后一个是最新的

3.关闭数据库 启动到nomount


点击(此处)折叠或打开

  1. SQL> CREATE CONTROLFILE REUSE DATABASE \"YWS\" RESETLOGS NOARCHIVELOG
  2. 2 MAXLOGFILES 16
  3. 3 MAXLOGMEMBERS 3
  4. 4 MAXDATAFILES 100
  5. 5 MAXINSTANCES 8
  6. 6 MAXLOGHISTORY 292
  7. 7 LOGFILE
  8. 8 GROUP 1 '/u01/oracle/oradata/yws/redo01.log' SIZE 50M,
  9. 9 GROUP 2 '/u01/oracle/oradata/yws/redo02.log' SIZE 50M,
  10. 10 GROUP 3 '/u01/oracle/oradata/yws/redo03.log' SIZE 50M
  11. 11 -- STANDBY LOGFILE
  12. 12 DATAFILE
  13. 13 '/u01/oracle/oradata/yws/system01.dbf',
  14. 14 '/u01/oracle/oradata/yws/undotbs01.dbf',
  15. 15 '/u01/oracle/oradata/yws/sysaux01.dbf',
  16. 16 '/u01/oracle/oradata/yws/users01.dbf',
  17. 17 '/u01/oracle/oradata/yws/yws.dbf'
  18. 18 CHARACTER SET WE8ISO8859P1
  19. 19 ;
  21. Control file created.
  23. SQL> alter database open;
  24. alter database open
  25. *
  26. ERROR at line 1:
  27. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
  30. SQL> alter database open resetlogs;
  32. Database altered.

4.原来备份的数据文件 拿回来覆盖现在的数据文件

点击(此处)折叠或打开

  1. [oracle@lk oradata]$ mv yws.dbf yws
  2. [oracle@lk yws]$ pwd
  3. /u01/oracle/oradata/yws

5.开库报错

点击(此处)折叠或打开

  1. SQL> startup
  2. ORACLE instance started.

  4. Total System Global Area 243269632 bytes
  5. Fixed Size 1218748 bytes
  6. Variable Size 83887940 bytes
  7. Database Buffers 155189248 bytes
  8. Redo Buffers 2973696 bytes
  9. Database mounted.
  10. ORA-01190: control file or data file 6 is from before the last RESETLOGS
  11. ORA-01110: data file 6: '/u01/oracle/oradata/yws/yws.dbf'


各数据文件头中resetlogs的信息如下：

点击(此处)折叠或打开

  1. Fhrls：resetlogs scn值
  2. Fhrlc：resetlogs count值
  3. fhrlc_i：resetlogs count 转换成10进制的值


  6. reset logs count and scn: The counter with the SCN is called the Reset Log Stamp,
  7. and is a unique identification. The counter is in fact a timestamp





点击(此处)折叠或打开

  1. SQL> select resetlogs_change#, to_char(resetlogs_time,'mm/dd/yyyy hh24:mi:ss') time from v$database;

  3. RESETLOGS_CHANGE# TIME
  4. ----------------- -------------------
  5.            501518 12/22/2013 17:51:25

  7. SQL> select hxfil,fhrls change#,fhrlc_i,fhrlc time from x$kcvfh;

  9.      HXFIL CHANGE# FHRLC_I TIME
  10. ---------- ---------------- ---------- --------------------
  11.          1 501518 834861085 12/22/2013 17:51:25
  12.          2 501518 834861085 12/22/2013 17:51:25
  13.          3 501518 834861085 12/22/2013 17:51:25
  14.          4 501518 834861085 12/22/2013 17:51:25
  15.          6 446075 829960901 10/28/2013 00:41:41

6.bbed修改文件头

点击(此处)折叠或打开

  1. BBED> modify /x 993ac3 dba 6,1 offset 112
  2. Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
  3.  File: /u01/oracle/oradata/yws/yws.dbf (6)
  4.  Block: 1 Offsets: 112 to 623 Dba:0x01800001
  5. ------------------------------------------------------------------------
  6.  993ac331 7bce0600 00000000 00000000 00000000 00000000 00000000 07000000
  7.  00000000 06000000 00000000 00000000 00000000 00000000 00000000 00000000
  8.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  9.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  10.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  11.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  12.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 07000000
  13.  03005957 53000000 00000000 00000000 00000000 00000000 00000000 00000000
  14.  06000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  15.  00000000 00000000 00000000 00000000 74ef8421 01000000 00000000 00000000
  16.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  17.  00000000 00000000 00000000 00000000 00000000 72a50700 00000000 d0f3c231
  18.  01000a00 34000000 867e0000 1000d1bf 02000000 00000000 00000000 00000000
  19.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  20.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  21.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

  23.  <32 bytes per line>

  25. BBED> dump dba 6,1 offset 112
  26.  File: /u01/oracle/oradata/yws/yws.dbf (6)
  27.  Block: 1 Offsets: 112 to 623 Dba:0x01800001
  28. ------------------------------------------------------------------------
  29.  993ac331 7bce0600 00000000 00000000 00000000 00000000 00000000 07000000
  30.  00000000 06000000 00000000 00000000 00000000 00000000 00000000 00000000
  31.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  32.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  33.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  34.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  35.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 07000000
  36.  03005957 53000000 00000000 00000000 00000000 00000000 00000000 00000000
  37.  06000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  38.  00000000 00000000 00000000 00000000 74ef8421 01000000 00000000 00000000
  39.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  40.  00000000 00000000 00000000 00000000 00000000 72a50700 00000000 d0f3c231
  41.  01000a00 34000000 867e0000 1000d1bf 02000000 00000000 00000000 00000000
  42.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  43.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  44.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

  46.  <32 bytes per line>




  51. BBED> dump dba 1,1 offset 116
  52.  File: /u01/oracle/oradata/yws/system01.dbf (1)
  53.  Block: 1 Offsets: 116 to 627 Dba:0x00400001
  54. ------------------------------------------------------------------------
  55.  fab40700 00000000 00000000 00000000 00000000 00000420 6a000000 6b48c331
  56.  67000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  57.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  58.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  59.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  60.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  61.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 06005359
  62.  5354454d 00000000 00000000 00000000 00000000 00000000 00000000 01000000
  63.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  64.  00000000 00000000 0a000a00 c5327831 7bce0600 00000000 00000000 00000000
  65.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  66.  00000000 00000000 00000000 00000000 fbb40700 0000b4bf af3ac331 0100bd0c
  67.  01000000 02000000 10000000 02000000 00000000 00000000 00000000 00000000
  68.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  69.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  70.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

  72.  <32 bytes per line>





  78. BBED> modify /x fab407 dba 6,1 offset 116
  79.  File: /u01/oracle/oradata/yws/yws.dbf (6)
  80.  Block: 1 Offsets: 116 to 627 Dba:0x01800001
  81. ------------------------------------------------------------------------
  82.  fab40700 00000000 00000000 00000000 00000000 00000000 07000000 00000000
  83.  06000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  84.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  85.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  86.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  87.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  88.  00000000 00000000 00000000 00000000 00000000 00000000 07000000 03005957
  89.  53000000 00000000 00000000 00000000 00000000 00000000 00000000 06000000
  90.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  91.  00000000 00000000 00000000 74ef8421 01000000 00000000 00000000 00000000
  92.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  93.  00000000 00000000 00000000 00000000 72a50700 00000000 d0f3c231 01000a00
  94.  34000000 867e0000 1000d1bf 02000000 00000000 00000000 00000000 00000000
  95.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  96.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  97.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

  99.  <32 bytes per line>

  101. BBED> sum apply;
  102. Check value for File 6, Block 1:
  103. current = 0x0748, required = 0x0748

7.重建控制文件

点击(此处)折叠或打开

  1. SQL> alter database backup controlfile to trace;

  3. Database altered.

  5. SQL> shutdown immediate;
  6. ORA-01109: database not open

  8. SQL> CREATE CONTROLFILE REUSE DATABASE \"YWS\" RESETLOGS NOARCHIVELOG
  9.   2 MAXLOGFILES 16
  10.   3 MAXLOGMEMBERS 3
  11.   4 MAXDATAFILES 100
  12.   5 MAXINSTANCES 8
  13.   6 MAXLOGHISTORY 292
  14.   7 LOGFILE
  15.   8 GROUP 1 '/u01/oracle/oradata/yws/redo01.log' SIZE 50M,
  16.   9 GROUP 2 '/u01/oracle/oradata/yws/redo02.log' SIZE 50M,
  17.  10 GROUP 3 '/u01/oracle/oradata/yws/redo03.log' SIZE 50M
  18.  11 -- STANDBY LOGFILE
  19.  12 DATAFILE
  20.  13 '/u01/oracle/oradata/yws/system01.dbf',
  21.  14 '/u01/oracle/oradata/yws/undotbs01.dbf',
  22.  15 '/u01/oracle/oradata/yws/sysaux01.dbf',
  23.  16 '/u01/oracle/oradata/yws/users01.dbf',
  24.  17 '/u01/oracle/oradata/yws/yws.dbf'
  25.  18 CHARACTER SET WE8ISO8859P1
  26.  19 ;

  28. Control file created.

8.查询


SQL> select hxfil,fhrls change#,fhrlc_i,fhrlc time from x$kcvfh;


     HXFIL CHANGE#             FHRLC_I TIME
---------- ---------------- ---------- --------------------
         1 505082            834878105 12/22/2013 22:35:05
         2 505082            834878105 12/22/2013 22:35:05
         3 505082            834878105 12/22/2013 22:35:05
         4 505082            834878105 12/22/2013 22:35:05
         6 505082            834878105 12/22/2013 22:35:05

点击(此处)折叠或打开

  1. Errors in file /u01/oracle/admin/yws/udump/yws_ora_13792.trc:
  2. ORA-00600: internal error code, arguments: [2662], [0], [505089], [0], [505116], [4194313], [], []
  3. Sun Dec 22 23:50:14 2013
  4. SMON: enabling cache recovery
  5. Sun Dec 22 23:50:16 2013
  6. Errors in file /u01/oracle/admin/yws/udump/yws_ora_13792.trc:
  7. ORA-00600: internal error code, arguments: [2662], [0], [505089], [0], [505116], [4194313], [], []
  8. Sun Dec 22 23:50:16 2013
  9. Error 600 happened during db open, shutting down database
  10. USER: terminating instance due to error 600
  11. Instance terminated by USER, pid = 13792
  12. ORA-1092 signalled during: alter database open resetlogs...



8.开库


点击(此处)折叠或打开

  1. SQL> conn / as sysdba
  2. Connected to an idle instance.
  3. SQL> startup
  4. ORACLE instance started.

  6. Total System Global Area 243269632 bytes
  7. Fixed Size 1218748 bytes
  8. Variable Size 83887940 bytes
  9. Database Buffers 155189248 bytes
  10. Redo Buffers 2973696 bytes
  11. Database mounted.
  12. ORA-01113: file 1 needs media recovery
  13. ORA-01110: data file 1: '/u01/oracle/oradata/yws/system01.dbf'


  16. SQL> recover datafile 1;
  17. Media recovery complete.
  18. SQL> alter database open;
  19. alter database open
  20. *
  21. ERROR at line 1:
  22. ORA-01113: file 2 needs media recovery
  23. ORA-01110: data file 2: '/u01/oracle/oradata/yws/undotbs01.dbf'


  26. SQL> recover database;
  27. Media recovery complete.
  28. SQL> alter database open;

  30. Database altered.

  32. SQL>



  36. 成功open。
请使用浏览器的分享功能分享到微信等