BBED 修改文件头 解决 ORA-01190

问题：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.
   
3. 
   
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进制的值
   
4. 
   
5. 
   
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
   
8. 
   

点击(此处)折叠或打开

1. SQL> select resetlogs_change#, to_char(resetlogs_time,'mm/dd/yyyy hh24:mi:ss') time from v$database;
   
2. 
   
3. RESETLOGS_CHANGE# TIME
   
4. ----------------- -------------------
   
5.            501518 12/22/2013 17:51:25
   
6. 
   
7. SQL> select hxfil,fhrls change#,fhrlc_i,fhrlc time from x$kcvfh;
   
8. 
   
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
    
22. 
    
23.  <32 bytes per line>
    
24. 
    
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
    
45. 
    
46.  <32 bytes per line>
    
47. 
    
48. 
    
49. 
    
50. 
    
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
    
71. 
    
72.  <32 bytes per line>
    
73. 
    
74. 
    
75. 
    
76. 
    
77. 
    
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
    
98. 
    
99.  <32 bytes per line>
    
100. 
     
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;
   
2. 
   
3. Database altered.
   
4. 
   
5. SQL> shutdown immediate;
   
6. ORA-01109: database not open
   
7. 
   
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 ;
    
27. 
    
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.
   
5. 
   
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'
    
14. 
    
15. 
    
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'
    
24. 
    
25. 
    
26. SQL> recover database;
    
27. Media recovery complete.
    
28. SQL> alter database open;
    
29. 
    
30. Database altered.
    
31. 
    
32. SQL>
    
33. 
    
34. 
    
35. 
    
36. 成功open。

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

转载于:http://blog.itpub.net/29477587/viewspace-1079790/