共计 18284 个字符,预计需要花费 46 分钟才能阅读完成。
Oracle 数据文件头中的 scn 要与控制文件中的 scn 一致,数据库才可以 open,在 open 过程中我们可以通过 bbed 来修改某个数据文件头的 scn,来欺骗 oracle,来 open 库。
1、环境如下
使用 Oracle 11gR2 进行测试,具体版本为 11.2.0.4
SYS@linuxidc SQL>select file#,name,checkpoint_change#,checkpoint_time from v$datafile;
FILE# NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME
———- —————————————- —————— ——————-
1 /dbdata/oradata/linuxidc/system01.dbf 1233066 2016-11-10 11:23:44
2 /dbdata/oradata/linuxidc/sysaux01.dbf 1233066 2016-11-10 11:23:44
3 /dbdata/oradata/linuxidc/undotbs01.dbf 1233066 2016-11-10 11:23:44
4 /dbdata/oradata/linuxidc/users01.dbf 1233066 2016-11-10 11:23:44
5 /dbdata/oradata/linuxidc/moe01.dbf 1233066 2016-11-10 11:23:44
2、模拟某个数据文件在问题
shutdown immediate 数据库,然后拷贝 moe01.dbf 这个数据文件,然后打开数据库,做日志切换,然后 shutdown immediate,再把之前的拷贝替换现在的 moe01.dbf 数据文件
SYS@linuxidc SQL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@tstdb-25-220 linuxidc]$ cp moe01.dbf moe01.dbf.bak
[oracle@tstdb-25-220 linuxidc]$ ll
总用量 3872076
-rw-r—–. 1 oracle oinstall 9748480 11 月 10 09:48 control01.ctl
-rw-r—–. 1 oracle oinstall 9748480 11 月 10 09:48 control02.ctl
-rw-r—–. 1 oracle oinstall 1073750016 11 月 10 09:48 moe01.dbf
-rw-r—–. 1 oracle oinstall 1073750016 11 月 10 11:23 moe01.dbf.bak
-rw-r—–. 1 oracle oinstall 52429312 11 月 9 13:13 redo01.log
-rw-r—–. 1 oracle oinstall 52429312 11 月 9 22:00 redo02.log
-rw-r—–. 1 oracle oinstall 52429312 11 月 10 09:48 redo03.log
-rw-r—–. 1 oracle oinstall 587210752 11 月 10 09:48 sysaux01.dbf
-rw-r—–. 1 oracle oinstall 807411712 11 月 10 09:48 system01.dbf
-rw-r—–. 1 oracle oinstall 30416896 11 月 9 22:00 temp01.dbf
-rw-r—–. 1 oracle oinstall 131080192 11 月 10 09:48 undotbs01.dbf
-rw-r—–. 1 oracle oinstall 111419392 11 月 10 09:48 users01.dbf
SYS@linuxidc SQL>startup
ORACLE instance started.
Total System Global Area 2455228416 bytes
Fixed Size 2255712 bytes
Variable Size 620758176 bytes
Database Buffers 1811939328 bytes
Redo Buffers 20275200 bytes
Database mounted.
Database opened.
SYS@linuxidc SQL>alter system switch logfile;
System altered.
SYS@linuxidc SQL>alter system switch logfile;
System altered.
SYS@linuxidc SQL>alter system switch logfile;
System altered.
SYS@linuxidc SQL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@tstdb-25-220 linuxidc]$ cp moe01.dbf moe01.dbf.bak.f
[oracle@tstdb-25-220 linuxidc]$ cp moe01.dbf.bak moe01.dbf
启动数据库报错
SYS@linuxidc SQL>startup
ORACLE instance started.
Total System Global Area 2455228416 bytes
Fixed Size 2255712 bytes
Variable Size 620758176 bytes
Database Buffers 1811939328 bytes
Redo Buffers 20275200 bytes
Database mounted.
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: ‘/dbdata/oradata/linuxidc/moe01.dbf’
SYS@linuxidc SQL>recover datafile 5;
ORA-00279: change 1233063 generated at 11/10/2016 09:48:02 needed for thread 1
ORA-00289: suggestion : /apps/oracle/11.2.0/db_1/dbs/arch1_24_925478204.dbf
ORA-00280: change 1233063 for thread 1 is in sequence #24
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log ‘/apps/oracle/11.2.0/db_1/dbs/arch1_24_925478204.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log ‘/apps/oracle/11.2.0/db_1/dbs/arch1_24_925478204.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
3、场景模拟出来了,下面是修复过程
使用 BBED,将文件头的 SCN 等关键信息修改到与控制文件 control file 相匹配即可
SYS@linuxidc SQL>select file#, CHECKPOINT_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE#
———- ——————
1 1233692
2 1233692
3 1233692
4 1233692
5 1233692
SYS@linuxidc SQL>select CHECKPOINT_CHANGE# from v$database;
CHECKPOINT_CHANGE#
——————
1233692
SYS@linuxidc SQL>select file#, recover, fuzzy, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# REC FUZ CHECKPOINT_CHANGE#
———- — — ——————
1 NO NO 1233692
2 NO NO 1233692
3 NO NO 1233692
4 NO NO 1233692
5 YES NO 1233063
控制文件中 datafile5 的 scn 是:1233692,而数据文件头中的 scn 是:1233063
使用 bbed 将 datafile5 的数据文件头对应的 SCN 修改为与其他文件相同,我们先看一下 users01.dbf 这个文件的文件头
BBED> set filename ‘/dbdata/oradata/linuxidc/users01.dbf’
FILENAME /dbdata/oradata/linuxidc/users01.dbf
BBED> set block 1
BLOCK# 1
BBED> map
File: /dbdata/oradata/linuxidc/users01.dbf (0)
Block: 1 Dba:0x00000000
————————————————————
Data File Header
struct kcvfh, 860 bytes @0
ub4 tailchk @8188
因为我们要修改数据文件头,因此我们需要关注四个偏移量 offset 点,分别为 484、492、140 和 148
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 484) – SCN of last change to the datafile.
(2)kcvcptim (at offset 492) -Time of the last change to the datafile.
(3)kcvfhcpc (at offset 140) – Checkpoint count.
(4)kcvfhccc (at offset 148) – Unknown, but is always 1 less than thecheckpoint point count.
Oracle 有 4 个属性来判断 datafile 是否和其他的 datafile 一致,如果都一致,可以正常操作,如果不一致,那么会报 ORA-01113 错误
下面我们看下这几个 offset
BBED> set offset 484
OFFSET 484
BBED> dump
File: /dbdata/oradata/linuxidc/users01.dbf (0)
Block: 1 Offsets: 484 to 995 Dba:0x00000000
————————————————————————
1cd31200 00000000 bd384937 01000000 1b000000 30010000 100051f2 02000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
0d000d00 0d000100 00000000 00000000 00000000 02000001 03000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> set offset 492
OFFSET 492
BBED> dump
File: /dbdata/oradata/linuxidc/users01.dbf (0)
Block: 1 Offsets: 492 to 1003 Dba:0x00000000
————————————————————————
bd384937 01000000 1b000000 30010000 100051f2 02000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 0d000d00 0d000100
00000000 00000000 00000000 02000001 03000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> set offset 140
OFFSET 140
BBED> dump
File: /dbdata/oradata/linuxidc/users01.dbf (0)
Block: 1 Offsets: 140 to 651 Dba:0x00000000
————————————————————————
78000000 14ff4737 77000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
04000000 05005553 45525300 00000000 00000000 00000000 00000000 00000000
00000000 04000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 7ac92131 01000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 1cd31200 00000000
bd384937 01000000 1b000000 30010000 100051f2 02000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 0d000d00 0d000100
<32 bytes per line>
BBED> set offset 148
OFFSET 148
BBED> dump
File: /dbdata/oradata/linuxidc/users01.dbf (0)
Block: 1 Offsets: 148 to 659 Dba:0x00000000
————————————————————————
77000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 04000000 05005553
45525300 00000000 00000000 00000000 00000000 00000000 00000000 04000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 7ac92131 01000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 1cd31200 00000000 bd384937 01000000
1b000000 30010000 100051f2 02000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 0d000d00 0d000100 00000000 00000000
<32 bytes per line>
其中,位于 484 和 488 偏移量的是数据文件对应的 SCN 编号。在 Oracle 内部,SCN 是使用 wrap*4*1024*1024*1024+base 来进行标示的。通常我们看到的数据库 wrap 都是 0。位于 492 偏移量的是最后一次检查点对应的时间信息。位于 140 和 148 偏移量的是检查点次数。这些信息都是会由于时间推动和检查点动作引起变化,我们严格情况下,需要保证文件头块的信息和控制文件信息一致。
另外一点,由于 Linux 是 Little 字节系统,要关注写入时候的格式问题。最简单的方式是 dump 一下偏移量,看看是怎么保存的。
1234567 BBED> dump
File: /dbdata/oradata/linuxidc/users01.dbf (0)
Block: 1 Offsets: 484 to 995 Dba:0x00000000
————————————————————————
1cd31200 00000000 bd384937 01000000 1b000000 30010000 100051f2 02000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
然后我们修改 moe01.dbf 的文件头(修改这几个 offset,我这里只修改了 484、492 就可以 open 库了)
BBED> set filename ‘/dbdata/oradata/linuxidc/moe01.dbf’
FILENAME /dbdata/oradata/linuxidc/moe01.dbf
BBED> set block 1
BLOCK# 1
BBED> set mode edit
MODE Edit
BBED> set offset 484
OFFSET 484
BBED> dump
File: /dbdata/oradata/linuxidc/moe01.dbf (0)
Block: 1 Offsets: 484 to 995 Dba:0x00000000
————————————————————————
a7d01200 00000000 52204937 01000000 18000000 4d8d0000 100051f2 02000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
0d000d00 0d000100 00000000 00000000 00000000 02004001 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 1cd31200 484
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /dbdata/oradata/linuxidc/moe01.dbf (0)
Block: 1 Offsets: 484 to 995 Dba:0x00000000
————————————————————————
1cd31200 00000000 52204937 01000000 18000000 4d8d0000 100051f2 02000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
0d000d00 0d000100 00000000 00000000 00000000 02004001 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x bd384937 492
BBED-00209: invalid number (bd384937)
BBED> m /x bd38 492
File: /dbdata/oradata/linuxidc/moe01.dbf (0)
Block: 1 Offsets: 492 to 1003 Dba:0x00000000
————————————————————————
bd384937 01000000 18000000 4d8d0000 100051f2 02000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 0d000d00 0d000100
00000000 00000000 00000000 02004001 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 4937 494
File: /dbdata/oradata/linuxidc/moe01.dbf (0)
Block: 1 Offsets: 494 to 1005 Dba:0x00000000
————————————————————————
49370100 00001800 00004d8d 00001000 51f20200 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000d00 0d000d00 01000000
00000000 00000000 00000200 40010000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 1:
current = 0x1683, required = 0x1683
BBED> verify
DBVERIFY – Verification starting
FILE = /dbdata/oradata/linuxidc/moe01.dbf
BLOCK = 1
DBVERIFY – Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
这时我们再次查看各个数据文件头的 scn,发现 scn 一致了
123456789 SYS@linuxidc SQL>select file#, recover, fuzzy, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# REC FUZ CHECKPOINT_CHANGE#
———- — — ——————
1 NO NO 1233692
2 NO NO 1233692
3 NO NO 1233692
4 NO NO 1233692
5 YES NO 1233692
试着 open 库
SYS@linuxidc SQL>alter database open
2 ;
alter database open
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: ‘/dbdata/oradata/linuxidc/moe01.dbf’
SYS@linuxidc SQL>recover datafile 5;
Media recovery complete.
SYS@linuxidc SQL>alter database open;
Database altered.
看一下 alert 文件
ALTER DATABASE RECOVER datafile 5
Media Recovery Start
Serial Media Recovery started
Media Recovery Complete (linuxidc)
Completed: ALTER DATABASE RECOVER datafile 5
Thu Nov 10 13:47:24 2016
alter database open
Thu Nov 10 13:47:25 2016
Thread 1 opened at log sequence 27
Current log# 3 seq# 27 mem# 0: /dbdata/oradata/linuxidc/redo03.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Nov 10 13:47:25 2016
SMON: enabling cache recovery
[20910] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:3710149144 end:3710149224 diff:80 (0 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Thu Nov 10 13:47:25 2016
QMNC started with pid=20, OS id=21409
Completed: alter database open
在本次恢复过程中,recover 是可行的,原因是该库并没做过 resetlog,如果 datafile 是在 resetlog 之前就已经 offline 的数据文件,那 recover 是不可行的。
本文永久更新链接地址:http://www.linuxidc.com/Linux/2016-11/137012.htm