联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
SQL> startup
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/opt/oracle/oradata/xifenfei/xff01.dbf'
提示数据文件2需要恢复，首选是recover datafile 2;，如果失败，可以考虑bbed修改scn的办法
SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;
FILE# TO_CHAR(CHECK
---------- -------------
1 107374278108
2 107374278108
3 107374278108
4 107374278108
5 107374278108
6 107374278108
7 107374278108
8 107374278108
9 107374278108
10 107374278108
11 107374278108
11 rows selected.
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;
FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
2 ONLINE 107374277136
[oracle@localhost tmp]$ bbed parfile=/tmp/parfile.cnf
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Mon Aug 22 06:05:34 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /opt/oracle/oradata/xifenfei/system01.dbf 1280
2 /opt/oracle/oradata/xifenfei/xff01.dbf 1280
BBED> set dba 1,1
DBA 0x00400001 (4194305 1,1)
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @140
struct kcvcpscn, 8 bytes @140
* ub4 kscnbas @140 0x000175dc
ub2 kscnwrp @144 0x0019
* ub4 kcvcptim @148 0x2d49fbbb
ub2 kcvcpthr @152 0x0001
union u, 12 bytes @156
struct kcvcprba, 12 bytes @156
ub4 kcrbaseq @156 0x00000005
ub4 kcrbabno @160 0x00008e05
ub2 kcrbabof @164 0x0010
struct kcvcptr, 12 bytes @156
struct kcrtrscn, 8 bytes @156
ub4 kscnbas @156 0x00000005
ub2 kscnwrp @160 0x8e05
ub4 kcrtrtim @164 0x09110010
ub1 kcvcpetb[0] @168 0x02
ub1 kcvcpetb[1] @169 0x00
ub1 kcvcpetb[2] @170 0x00
ub1 kcvcpetb[3] @171 0x00
ub1 kcvcpetb[4] @172 0x00
ub1 kcvcpetb[5] @173 0x00
ub1 kcvcpetb[6] @174 0x00
ub1 kcvcpetb[7] @175 0x00
BBED> p kcvfhcpc
*ub4 kcvfhcpc @176 0x0000007a
BBED> p kcvfhccc
*ub4 kcvfhccc @184 0x00000079
星号表示使用bbed修改datafile header scn需要考虑的地方
SQL> select to_char(to_number('19000175dc','xxxxxxxxxxxx'),'999999999999') from dual;
TO_CHAR(TO_NU
-------------
107374278108
证实system01.dbf的scn为107374278108和v$datafile查询到的一致
BBED> set dba 2,1
DBA 0x00800001 (8388609 2,1)
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @140
struct kcvcpscn, 8 bytes @140
ub4 kscnbas @140 0x00017210
ub2 kscnwrp @144 0x0019
ub4 kcvcptim @148 0x2d49fa27
ub2 kcvcpthr @152 0x0001
union u, 12 bytes @156
struct kcvcprba, 12 bytes @156
ub4 kcrbaseq @156 0x00000005
ub4 kcrbabno @160 0x00006f18
ub2 kcrbabof @164 0x0010
struct kcvcptr, 12 bytes @156
struct kcrtrscn, 8 bytes @156
ub4 kscnbas @156 0x00000005
ub2 kscnwrp @160 0x6f18
ub4 kcrtrtim @164 0x09110010
ub1 kcvcpetb[0] @168 0x02
ub1 kcvcpetb[1] @169 0x00
ub1 kcvcpetb[2] @170 0x00
ub1 kcvcpetb[3] @171 0x00
ub1 kcvcpetb[4] @172 0x00
ub1 kcvcpetb[5] @173 0x00
ub1 kcvcpetb[6] @174 0x00
ub1 kcvcpetb[7] @175 0x00
BBED> p kcvfhcpc
ub4 kcvfhcpc @176 0x00000034
BBED> p kcvfhccc
ub4 kcvfhccc @184 0x00000033
SQL> select to_char(to_number('1900017210','xxxxxxxxxxxx'),'999999999999') from dual;
TO_CHAR(TO_NU
-------------
107374277136
和v$recover_file视图中查询出来一致
BBED> set dba 2,1 offset 140
DBA 0x00800001 (8388609 2,1)
OFFSET 140
BBED> show
FILE# 2
BLOCK# 1
OFFSET 140
DBA 0x00800001 (8388609 2,1)
FILENAME /opt/oracle/oradata/xifenfei/xff01.dbf
BIFILE bifile.bbd
LISTFILE /tmp/list
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 32
LOGFILE log.bbd
SPOOL No
BBED> m /x dc750100
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 1 Offsets: 140 to 171 Dba:0x00800001
------------------------------------------------------------------------
dc750100 19000000 27fa492d 01000000 05000000 186f0000 10001109 02000000
<32 bytes per line>
BBED> m /x bbfb492d offset 158
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 1 Offsets: 158 to 189 Dba:0x00800001
------------------------------------------------------------------------
bbfb492d 00001000 11090200 00000000 00003400 00004cc0 492d3300 00000000
<32 bytes per line>
BBED> m /x 7a000000 offset 176
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 1 Offsets: 176 to 207 Dba:0x00800001
------------------------------------------------------------------------
7a000000 4cc0492d 33000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 79000000 offset 184
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 1 Offsets: 184 to 215 Dba:0x00800001
------------------------------------------------------------------------
79000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
注意：p打印出来的值和m修改的值可能不是完全一致（和cpu的计算类型有关，常用的intel cpu中是倒序存储），可以选择先dump正确的相关值，然后m修改
如：p打印出来的值为：80084d1f时，m修改时要为： 1f4d0880
BBED> sum apply dba 2,1
Check value for File 2, Block 1:
current = 0x7ece, required = 0x7ece
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01122: database file 2 failed verification check
ORA-01110: data file 2: '/opt/oracle/oradata/xifenfei/xff01.dbf'
ORA-01207: file is more recent than controlfile - old controlfile
提示控制文件太老，需要重建控制文件
SQL> shutdown abort
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "XIFENFEI" NORESETLOGS ARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/opt/oracle/oradata/xifenfei/redo01.log' SIZE 100M,
GROUP 2 '/opt/oracle/oradata/xifenfei/redo02.log' SIZE 100M,
GROUP 3 '/opt/oracle/oradata/xifenfei/redo03.log' SIZE 100M
DATAFILE
'/opt/oracle/oradata/xifenfei/system01.dbf',
'/opt/oracle/oradata/xifenfei/xff01.dbf',
'/opt/oracle/oradata/xifenfei/cwmlite01.dbf',
'/opt/oracle/oradata/xifenfei/drsys01.dbf',
'/opt/oracle/oradata/xifenfei/example01.dbf',
'/opt/oracle/oradata/xifenfei/indx01.dbf',
'/opt/oracle/oradata/xifenfei/odm01.dbf',
'/opt/oracle/oradata/xifenfei/tools01.dbf',
'/opt/oracle/oradata/xifenfei/users01.dbf',
'/opt/oracle/oradata/xifenfei/xdb01.dbf',
'/opt/oracle/oradata/xifenfei/UNDOTBS01.dbf'
CHARACTER SET ZHS16GBK
;
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
Control file created.
SQL> alter database open;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/xifenfei/temp01.dbf'
2 SIZE 32505856 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
重建控制文件后，需要添加临时文件
补充说明：
1、datafile 的file header 存储在第一个block里
2、Oracle considers four attributes of this data structure when determining if a datafile is sync with the other data files of the database:（不同oracle版本offset可能不同）
（1）kscnbas (at offset 140) – SCN of last change to the datafile.
（2）kcvcptim (at offset 148) -Time of the last change to the datafile.
（3）kcvfhcpc (at offset 176) – Checkpoint count.
（4）kcvfhccc (at offset 184) – Unknown, but is always 1 less than thecheckpoint point count.
Oracle有4个属性来判断datafile 是否和其他的datafile 一致，如果都一致，可以正常操作，如果不一致，那么会报ORA-01113错误
使用Oracle Recovery Tools一键式解决给问题
加我QQ
