[20160329]bbed修复offline的数据文件.txt
--测试数据库，不小心将一个数据文件offline了，archivelog也删除了(主要磁盘空间紧张，做了一次整理)。
--自己测试修复看看，顺便做一个记录。
1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ----------------
1 13227498080 2016-03-29 11:44:35 7 13227286650 ONLINE 999 NO /mnt/ramdisk/book/system01.dbf SYSTEM
2 13227498080 2016-03-29 11:44:35 1834 13227286650 ONLINE 995 NO /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13227498080 2016-03-29 11:44:35 923328 13227286650 ONLINE 915 NO /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13227498080 2016-03-29 11:44:35 16143 13227286650 ONLINE 999 NO /mnt/ramdisk/book/users01.dbf USERS
5 13227498080 2016-03-29 11:44:35 952916 13227286650 ONLINE 912 NO /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13227498080 2016-03-29 11:44:35 1314508 13227286650 ONLINE 928 NO /mnt/ramdisk/book/sugar01.dbf SUGAR
7 13227287392 2016-03-25 17:13:07 13227207527 13227286650 OFFLINE 20 YES /mnt/ramdisk/book/tea01.dbf TEA
7 rows selected.
--我估计CHECKPOINT_CHANGE# 修改为13227498080 就ok了。测试看看。安全起见做一个备份：
$ cp tea01.dbf_ORG tea01.dbf
SYS@book> @ &r/10to16 13227287392
10 to 16 HEX REVERSE16
-------------- -----------------------------------
0000314686360 0x60636814-03000000
SYS@book> @ &r/10to16 13227498080
10 to 16 HEX REVERSE16
-------------- -----------------------------------
00003146b9a60 0x609a6b14-03000000
SCOTT@book> @ &r/bbvi 7 1
BVI_COMMAND
--------------------------------------------------
bvi -b 8192 -s 8192 /mnt/ramdisk/book/tea01.dbf
$ cp tea01.dbf tea01.dbf_ORG
2.使用bvi打开，修改0x60636814=>0x609a6b14
BBED> set dba 7,1
DBA 0x01c00001 (29360129 7,1)
BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas @484 0x14686360
BBED> set dba 1,1
DBA 0x00400001 (4194305 1,1)
BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas @484 0x146b9a60
--换1种修改模式算是学习。使用bbed的assign命令。
BBED> assign file 7 block 1 offset 484= file 1 block 1 offset 484;
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub1 pad @484 0x60
BBED> set dba 1,1
DBA 0x00400001 (4194305 1,1)
BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas @484 0x146b9a60
BBED> set dba 7,1
DBA 0x01c00001 (29360129 7,1)
BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas @484 0x14686360
--仔细检查发现没改，仅仅修改最后1个字节，实际上这里正好一样(看不出来)。
BBED> help assign
ASSIGN[/x|d|u|o] <target spec>=<source spec>
<target spec> : [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
<source spec> : [ value | <target spec options> ]
BBED> assign dba 7,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas = dba 1,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas;
ub4 kscnbas @484 0x146b9a60
BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas dba 1,1
ub4 kscnbas @484 0x146b9a60
BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas dba 7,1
ub4 kscnbas @484 0x146b9a60
--ok现在一样了。
BBED> sum apply dba 7,1
Check value for File 7, Block 1:
current = 0xdedb, required = 0xdedb
BBED> verify dba 7,1
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/tea01.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
3.重启到mount状态：
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ------------------------------- ----------------
1 13227498080 2016-03-29 11:44:35 7 13227286650 ONLINE 999 NO /mnt/ramdisk/book/system01.dbf SYSTEM
2 13227498080 2016-03-29 11:44:35 1834 13227286650 ONLINE 995 NO /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13227498080 2016-03-29 11:44:35 923328 13227286650 ONLINE 915 NO /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13227498080 2016-03-29 11:44:35 16143 13227286650 ONLINE 999 NO /mnt/ramdisk/book/users01.dbf USERS
5 13227498080 2016-03-29 11:44:35 952916 13227286650 ONLINE 912 NO /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13227498080 2016-03-29 11:44:35 1314508 13227286650 ONLINE 928 NO /mnt/ramdisk/book/sugar01.dbf SUGAR
7 13227498080 2016-03-25 17:13:07 13227207527 13227286650 OFFLINE 20 YES /mnt/ramdisk/book/tea01.dbf TEA
7 rows selected.
--现在一致了，recover看看。
SYS@book> recover datafile 7;
Media recovery complete.
alter database open read only ;
SYS@book> select owner,segment_name from dba_segments where tablespace_name='TEA';
OWNER SEGMENT_NAME
------ --------------------
SCOTT EMPX
SYS@book> select * from scott.empx ;
select * from scott.empx
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'
SYS@book> alter database datafile 7 online ;
Database altered.
SYS@book> select * from scott.empx where rownum<=1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------ ---------- --------- ------------ ------------------- ------------ ------------ ------------
7369 YYYY CLERK 7902 1980-12-17 00:00:00 800 20