[20150408]只读表空间以及数据库恢复4.txt
参考链接:
http://blog.itpub.net/267265/viewspace-1544583/
http://blog.itpub.net/267265/viewspace-1548059/
http://blog.itpub.net/267265/viewspace-1548967/
--上午做了测试,通过新建控制文件的方式来恢复,实际上更常用的方式使用bbed,修改数据文件块1,保持与控制文件的记录一致.
--通过对比来看看需要修改那些地方.
1.建立测试环境:
SCOTT@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
CREATE TABLESPACE MSSM DATAFILE
'/mnt/ramdisk/test/mssm01.dbf' SIZE 16M AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
SYS@test> ALTER TABLESPACE MSSM READ ONLY;
Tablespace altered.
$ cp /mnt/ramdisk/test/mssm01.dbf /mnt/ramdisk/backup/mssm01.dbf_good1
SYS@test> ALTER TABLESPACE MSSM READ write ;
Tablespace altered.
SYS@test> ALTER TABLESPACE MSSM READ ONLY;
Tablespace altered.
$ cp /mnt/ramdisk/test/mssm01.dbf /mnt/ramdisk/backup/mssm01.dbf_good2
2.现在对比mssm01.dbf_good1,mssm01.dbf_good2,看看存在那些差异.
$ xxd -c 16 mssm01.dbf_good1 > /tmp/aa1.txt
$ xxd -c 16 mssm01.dbf_good2 > /tmp/aa2.txt
$ diff -Nur /tmp/aa1.txt /tmp/aa2.txt
--- /tmp/aa1.txt 2015-04-08 16:29:28.246493983 +0800
+++ /tmp/aa2.txt 2015-04-08 16:29:35.422493775 +0800
@@ -511,15 +511,15 @@
0001fe0: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0001ff0: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0002000: 0ba2 0000 0100 8001 0000 0000 0000 0104 ................
-0002010: b326 0000 0000 0000 0003 200a 38c0 f180 .&........ .8礼.
-0002020: 5445 5354 0000 0000 18d3 c333 0008 0000 TEST.....用3....
+0002010: 6dc2 0000 0000 0000 0003 200a 38c0 f180 m?....... .8礼.
~~~~
+0002020: 5445 5354 0000 0000 93d3 c333 0008 0000 TEST.....用3....
~~~~~~~~~
0002030: 0020 0000 0600 0300 0000 0000 0000 0000 . ..............
0002040: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0002050: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0002060: 0000 0000 e089 19cb 0200 0000 4501 1834 ....?.?...E..4
0002070: f713 c533 c0cc 2a00 0000 0000 0000 0000 ..?捞*.........
-0002080: 0000 0000 0000 0000 0000 0000 2700 0000 ............'...
-0002090: 0000 0000 2600 0000 0000 0000 0000 0000 ....&...........
+0002080: 0000 0000 0000 0000 0000 0000 2a00 0000 ............*...
~~~~
+0002090: 0000 0000 2900 0000 0000 0000 0000 0000 ....)...........
~~~~
00020a0: 0000 0000 0000 0000 0000 0000 0000 0000 ................
00020b0: 0000 0000 0000 0000 0000 0000 0000 0000 ................
00020c0: 0000 0000 0000 0000 0000 0000 0000 0000 ................
@@ -540,8 +540,8 @@
00021b0: 0000 0000 0000 0000 0000 0000 0000 0000 ................
00021c0: 0000 0000 0000 0000 0000 0000 0000 0000 ................
00021d0: 0000 0000 0000 0000 0000 0000 0000 0000 ................
-00021e0: 0000 0000 5afb 43f4 0200 0000 7e4c 3e34 ....Z鸆?...~L>4
-00021f0: 0100 0000 6500 0000 6500 0000 1000 0000 ....e...e.......
+00021e0: 0000 0000 e304 44f4 0200 0000 8857 3e34 ....?D?....W>4
~~~~~~~~~ ~~~~~~~~~
+00021f0: 0100 0000 6900 0000 7600 0000 1000 0000 ....i...v.......
~~~~ ~~~~
0002200: 0200 0000 0000 0000 0000 0000 0000 0000 ................
0002210: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0002220: 0000 0000 0000 0000 0000 0000 0000 0000 ................
--0x2000 = 8192 ,也就是第1块存在差异. ~表示不同之处.
3.通过bbed观察,是那些信息.
BBED> set dba 6,1
DBA 0x01800001 (25165825 6,1)
BBED> map /v
File: /mnt/ramdisk/test/mssm01.dbf (6)
Block: 1 Dba:0x01800001
------------------------------------------------------------
Data File Header
struct kcvfh, 676 bytes @0
struct kcvfhbfh, 20 bytes @0
struct kcvfhhdr, 76 bytes @20
ub4 kcvfhrdb @96
struct kcvfhcrs, 8 bytes @100
ub4 kcvfhcrt @108
ub4 kcvfhrlc @112
struct kcvfhrls, 8 bytes @116
ub4 kcvfhbti @124
struct kcvfhbsc, 8 bytes @128
ub2 kcvfhbth @136
ub2 kcvfhsta @138
struct kcvfhckp, 36 bytes @484
ub4 kcvfhcpc @140
ub4 kcvfhrts @144
ub4 kcvfhccc @148
struct kcvfhbcp, 36 bytes @152
ub4 kcvfhbhz @312
struct kcvfhxcd, 16 bytes @316
word kcvfhtsn @332
ub2 kcvfhtln @336
text kcvfhtnm[30] @338
ub4 kcvfhrfn @368
struct kcvfhrfs, 8 bytes @372
ub4 kcvfhrft @380
struct kcvfhafs, 8 bytes @384
ub4 kcvfhbbc @392
ub4 kcvfhncb @396
ub4 kcvfhmcb @400
ub4 kcvfhlcb @404
ub4 kcvfhbcs @408
ub2 kcvfhofb @412
ub2 kcvfhnfb @414
ub4 kcvfhprc @416
struct kcvfhprs, 8 bytes @420
struct kcvfhprfs, 8 bytes @428
ub4 kcvfhtrt @444
ub4 tailchk @8188
-0002010: b326 0000 0000 0000 0003 200a 38c0 f180 .&........ .8礼.
-0002020: 5445 5354 0000 0000 18d3 c333 0008 0000 TEST.....用3....
+0002010: 6dc2 0000 0000 0000 0003 200a 38c0 f180 m?....... .8礼.
~~~~
+0002020: 5445 5354 0000 0000 93d3 c333 0008 0000 TEST.....用3....
~~~~~~~~~
BBED> p kcvfhbfh
struct kcvfhbfh, 20 bytes @0
ub1 type_kcbh @0 0x0b
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x01800001
ub4 bas_kcbh @8 0x00000000
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0xc26d
ub2 spare3_kcbh @18 0x0000
--0x10 偏移在16字节.对应的是kcvfhbfh.chkval_kcbh,好像这个检查和.
BBED> p kcvfh.kcvfhhdr.kccfhcsq
ub4 kccfhcsq @40 0x33c3d393
-0002080: 0000 0000 0000 0000 0000 0000 2700 0000 ............'...
-0002090: 0000 0000 2600 0000 0000 0000 0000 0000 ....&...........
+0002080: 0000 0000 0000 0000 0000 0000 2a00 0000 ............*...
~~~~
+0002090: 0000 0000 2900 0000 0000 0000 0000 0000 ....)...........
~~~~
-- 0x80 = 128,128+12=140
BBED> p kcvfh.kcvfhcpc
ub4 kcvfhcpc @140 0x0000002a
BBED> p kcvfh.kcvfhccc
ub4 kcvfhccc @148 0x00000029
--这个应该就是CHECKPOINT_COUNT. kcvfh.kcvfhccc比kcvfh.kcvfhcpc小1.
--0x2a = 42
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 12688032679 5 2804928 ONLINE 868465079 YES
2 12688032679 600647 2804928 ONLINE 868465079 YES
3 12688032679 6678 2804928 ONLINE 868465079 YES
4 12688032679 10685 2804928 ONLINE 868465081 YES
5 12688032679 625439 2804928 ONLINE 868465079 YES
6 12688032995 11997383136 2804928 ONLINE 42 NO
6 rows selected.
-00021e0: 0000 0000 5afb 43f4 0200 0000 7e4c 3e34 ....Z鸆?...~L>4
-00021f0: 0100 0000 6500 0000 6500 0000 1000 0000 ....e...e.......
+00021e0: 0000 0000 e304 44f4 0200 0000 8857 3e34 ....?D?....W>4
~~~~~~~~~ ~~~~~~~~~
+00021f0: 0100 0000 6900 0000 7600 0000 1000 0000 ....i...v.......
~~~~ ~~~~
-- 0x1e0 = 480 480+4= 484
BBED> p kcvfh.kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0xf44404e3
ub2 kscnwrp @488 0x0002
ub4 kcvcptim @492 0x343e5788
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000069
ub4 kcrbabno @504 0x00000076
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
-- 对应的就是scn kcvfh.kcvfhckp.kscnbas .
kcvfh.kcvfhckp.kcvcptim 应该是Time of the last change to the datafile.
参考我以前写的:
http://blog.itpub.net/267265/viewspace-746222/
摘抄一段:
The file header is stored in the first block cf the data file. We can use bbed tc examine the blcck and show the block
map. The header blocks contain a single data structure — kcvfh. Oracle considers four attributes of this data structure
when determining if a data file is sync with the other data files of the database:
kscnbas (at offset 484) -- SCN of last change to the datafile.
kcvcptim (at offset 492) -- Time of the last change to the datafile.
kcvfhcpc (at offset 140) -- Checkpoint count.
kcvfhccc (at offset 148) -- Unknown, but is always l less than the checkpoint point count.
The first two attributes are stored in the kcvfhckp sub-structure. The second two are attributes in their own right. We
can use the print command to display them all for the file that requires recovery:
-------根据以上提示,修改这4处成以下数值,应该就ok了.自己再复习一下.
BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas @484 0xf44404e3
BBED> p kcvfh.kcvfhckp.kcvcptim
ub4 kcvcptim @492 0x343e5788
BBED> p kcvfh.kcvfhcpc
ub4 kcvfhcpc @140 0x0000002a
BBED> p kcvfh.kcvfhccc
ub4 kcvfhccc @148 0x00000029
====================================
这些数值的确定可以通过通知文件确定.
SYS@test> alter session set events 'immediate trace name controlf level 3';
Session altered.
DATA FILE #6:
(name #9) /mnt/ramdisk/test/mssm01.dbf
creation size=0 block size=8192 status=0x86 head=9 tail=9 dup=1
tablespace 7, index=7 krfil=6 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:42 scn: 0x0002.f44404e3 04/08/2015 16:27:52
Stop scn: 0x0002.f44404e3 04/08/2015 16:27:52
Creation Checkpointed at scn: 0x0002.cb1989e0 03/10/2015 14:33:41
thread:0 rba:(0x0.0.0)
--看数据文件是这样.
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 12688065304 5 2804928 ONLINE 868465081 NO
2 12688065304 600647 2804928 ONLINE 868465081 NO
3 12688065304 6678 2804928 ONLINE 868465081 NO
4 12688065304 10685 2804928 ONLINE 868465083 NO
5 12688065304 625439 2804928 ONLINE 868465081 NO
6 12688030554 11997383136 2804928 ONLINE 39 NO
6 rows selected.
--也就是修改scn=12688030554 成 2*power(2,32)+0xf44404e3=12688032995.
-- CHECKPOINT_COUNT=39,修改为42.
-- 修改kcvfh.kcvfhckp.kcvcpscn.kscnbas:
BBED> set dba 6,1
DBA 0x01800001 (25165825 6,1)
BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas @484 0xf443fb5a
BBED> modify /x e304
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /mnt/ramdisk/test/mssm01.dbf (6)
Block: 1 Offsets: 484 to 491 Dba:0x01800001
------------------------------------------------------------------------------------------------------------------------------------------------
e30443f4 02000000
BBED> set offset +2
OFFSET 486
BBED> modify /x 44f4
File: /mnt/ramdisk/test/mssm01.dbf (6)
Block: 1 Offsets: 486 to 493 Dba:0x01800001
------------------------------------------------------------------------------------------------------------------------------------------------
44f40200 00007e4c
BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas @484 0xf44404e3
--修改 kcvfh.kcvfhcpc.
BBED> p kcvfh.kcvfhcpc
ub4 kcvfhcpc @140 0x00000027
BBED> modify /x 2a00
File: /mnt/ramdisk/test/mssm01.dbf (6)
Block: 1 Offsets: 140 to 147 Dba:0x01800001
------------------------------------------------------------------------------------------------------------------------------------------------
2a000000 00000000
BBED> p kcvfh.kcvfhcpc
ub4 kcvfhcpc @140 0x0000002a
--修改kcvfh.kcvfhccc.
BBED> p kcvfh.kcvfhccc
ub4 kcvfhccc @148 0x00000026
BBED> modify /x 2900
File: /mnt/ramdisk/test/mssm01.dbf (6)
Block: 1 Offsets: 148 to 155 Dba:0x01800001
------------------------------------------------------------------------------------------------------------------------------------------------
29000000 00000000
BBED> p kcvfh.kcvfhccc
ub4 kcvfhccc @148 0x00000029
--先不修改kcvfh.kcvfhckp.kcvcptim看看.
BBED> sum apply
Check value for File 6, Block 1:
current = 0xd90f, required = 0xd90f
SYS@test> alter database open ;
Database altered.
SYS@test> select * from scott.deptx;
DEPTNO DNAME LOC
------------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
--OK!现在可以访问了.
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile;
FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# STATUS
------------ ------------------ --------------------- ------------ --------------- -------------- -------
1 12688065305 0 0 0 SYSTEM
2 12688065305 0 0 0 ONLINE
3 12688065305 0 0 0 ONLINE
4 12688065305 0 0 0 ONLINE
5 12688065305 0 0 0 ONLINE
6 12688032995 0 12688032995 12688030554 12688032981 ONLINE
6 rows selected.
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 12688065305 5 2804928 ONLINE 868465082 YES
2 12688065305 600647 2804928 ONLINE 868465082 YES
3 12688065305 6678 2804928 ONLINE 868465082 YES
4 12688065305 10685 2804928 ONLINE 868465084 YES
5 12688065305 625439 2804928 ONLINE 868465082 YES
6 12688032995 11997383136 2804928 ONLINE 42 NO