首页 > 代码库 > Oracle bbed 实用示例-----File Header Reset
Oracle bbed 实用示例-----File Header Reset
一.查看当前环境
1.1 当前控制文件中的SCN号
[oracle@ora10 ~]$ sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on Sat Jan 17 10:11:34 2015Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn /as sysdbaConnected.SQL> col name format a65SQL> select file#,to_char(checkpoint_change#,‘999999999999‘) from v$datafile; FILE# TO_CHAR(CHECK---------- ------------- 1 490652 2 490652 3 490652 4 490652 5 490652SQL>
1.2 当前数据文件的状态
SQL> select file#,status,name from v$datafile; FILE# STATUS NAME---------- ------- ----------------------------------------------------------------- 1 SYSTEM /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf 2 ONLINE /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_bc7ldql4_.dbf 3 ONLINE /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_bc7lf4bd_.dbf 4 ONLINE /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_bc7lflno_.dbf 5 ONLINE /u01/app/oracle/oradata/ORCL/datafile/test_01.dbfSQL>
二.模拟故障场景
2.1 将datafile 5 offline
SQL> alter database datafile 5 offline;Database altered.SQL> select file#,status,name from v$datafile; FILE# STATUS NAME---------- ------- ----------------------------------------------------------------- 1 SYSTEM /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf 2 ONLINE /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_bc7ldql4_.db f 3 ONLINE /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_bc7lf4bd_.dbf 4 ONLINE /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_bc7lflno_.dbf 5 RECOVER /u01/app/oracle/oradata/ORCL/datafile/test_01.dbf ---文件5的状态已经被改变SQL>
2.2 更改文件的SCN号
--做日志切换，多执行几次SQL> alter system switch logfile;.........SQL> select file#,checkpoint_change# from v$datafile; FILE# CHECKPOINT_CHANGE#---------- ------------------ 1 491337 2 491337 3 491337 4 491337 5 490652 --SCN号未变SQL> ---查看需要恢复datafile 的SCN:SQL> select file#,online_status,change# from v$recover_file; FILE# ONLINE_ CHANGE#---------- ------- ---------- 5 OFFLINE 490652SQL>
2.3 将datafile 5 online
SQL> alter database datafile 5 online;alter database datafile 5 online*ERROR at line 1:ORA-01113: file 5 needs media recoveryORA-01110: data file 5: ‘/u01/app/oracle/oradata/ORCL/datafile/test_01.dbf‘SQL>
三.利用bbed修改文件5的scn号
3.1 再利用bbed前，将数据库关闭
SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL>
3.2 利用bbed查看文件1和文件5的scn号
----查看文件的scn号之前，先了解文件中的scn号的4个属性值
Oracle根据4个属性来判断datafile 是否和其他的datafile 一致，如果都一致，可以正常操作，如果不一致，那么会报ORA-01113错误。
- 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 1 less than thecheckpoint point count
这四个属性值存在File header中，即文件的第一个数据块中
---查看文件1的4个SCN属性值BBED> set file 1 FILE# 1BBED> set block 1 BLOCK# 1BBED> map /v File: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf (1) Block: 1 Dba:0x00400001------------------------------------------------------------ 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 BBED> print kcvfhckpstruct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x00077fd0 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x33cf444d ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000010 ub4 kcrbabno @504 0x000000f3 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 0x00BBED> print kcvfhcpcub4 kcvfhcpc @140 0x0000004eBBED> print kcvfhcccub4 kcvfhccc @148 0x0000004dBBED>注意：print打印出来的值和在文件中存储的值可能不是完全一致。在intel 的little endian是低位先存储，即顺序与我们看到的是相反的.利用dump出数据实际存储值：BBED> dump /v dba 1,1 offset 484 count 32 File: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf (1) Block: 1 Offsets: 484 to 515 Dba:0x00400001------------------------------------------------------- d07f0700 00000000 4d44cf33 01000a00 l ......MD.... 10000000 f3000000 1000ffbf 02000000 l ............ <16 bytes per line>BBED> dump /v dba 1,1 offset 140 count 32 File: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf (1) Block: 1 Offsets: 140 to 171 Dba:0x00400001------------------------------------------------------- 4e000000 1ecfce33 4d000000 00000000 l N....衔3M....... 00000000 00000000 00000000 00000000 l ................ <16 bytes per line>BBED> ub4 kscnbas @484 0x00077fd0 -->d07f0700 这四个值才是我们要写入文件5中的ub4 kcvcptim @492 0x33cf444d -->4d44cf33ub4 kcvfhcpc @140 0x0000004e -->4e000000ub4 kcvfhccc @148 0x0000004d -->4d000000
3.3 修改文件5的4个SCN值
----修改kscnbas
BBED> modify /x d07f0700 dba 5,1 offset 484BBED-00209: invalid number (d07f0700)--报无法修改，看看两个文件的484至487的4个字节值的异同：BBED> dump /v dba 1,1 offset 484 count 32 File: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf (1) Block: 1 Offsets: 484 to 515 Dba:0x00400001------------------------------------------------------- d07f0700 00000000 4d44cf33 01000a00 l ......MD.... 10000000 f3000000 1000ffbf 02000000 l ............ <16 bytes per line>BBED>BBED> dump /v dba 5,1 offset 484 count 32 File: /u01/app/oracle/oradata/ORCL/datafile/test_01.dbf (5) Block: 1 Offsets: 484 to 515 Dba:0x01400001------------------------------------------------------- 9c7c0700 0000870a 1d3fcf33 01000000 l .|.......?.... 09000000 02000000 1000ffbf 02000000 l .............. <16 bytes per line>BBED>--观察文件1和文件5的前四个字节的异同，只修改484，485前两字节BBED> modify /x d07f dba 5,1 offset 484Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y File: /u01/app/oracle/oradata/ORCL/datafile/test_01.dbf (5) Block: 1 Offsets: 484 to 515 Dba:0x01400001------------------------------------------------------------------------ d07f0700 0000870a 1d3fcf33 01000000 09000000 02000000 1000ffbf 02000000 <32 bytes per line>BBED>
----修改kcvcptim
----观察文件1和文件5的前四个字节的异同BBED> dump /v dba 1,1 offset 492 count 4 File: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf (1) Block: 1 Offsets: 492 to 495 Dba:0x00400001------------------------------------------------------- 4d44cf33 l MD <16 bytes per line>BBED> dump /v dba 5,1 offset 492 count 4 File: /u01/app/oracle/oradata/ORCL/datafile/test_01.dbf (5) Block: 1 Offsets: 492 to 495 Dba:0x01400001------------------------------------------------------- 1d3fcf33 l .? <16 bytes per line>BBED>同理，只需修改492，493两字节的值BBED> modify /x 4d44 dba 5,1 offset 492 File: /u01/app/oracle/oradata/ORCL/datafile/test_01.dbf (5) Block: 1 Offsets: 492 to 495 Dba:0x01400001------------------------------------------------------------------------ 4d44cf33 <32 bytes per line>BBED>
----修改kcvfhcpc
----观察前四个字节的异同BBED> dump /v dba 1,1 offset 140 count 4 File: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf (1) Block: 1 Offsets: 140 to 143 Dba:0x00400001------------------------------------------------------- 4e000000 l N... <16 bytes per line>BBED> dump /v dba 5,1 offset 140 count 4 File: /u01/app/oracle/oradata/ORCL/datafile/test_01.dbf (5) Block: 1 Offsets: 140 to 143 Dba:0x01400001------------------------------------------------------- 49000000 l I... <16 bytes per line>BBED> --只需修改前两个字节BBED> modify /x 4e00 dba 5,1 offset 140 File: /u01/app/oracle/oradata/ORCL/datafile/test_01.dbf (5) Block: 1 Offsets: 140 to 143 Dba:0x01400001------------------------------------------------------------------------ 4e000000 <32 bytes per line>BBED>
----修改kcvfhccc
----观察前四个字节的异同BBED> dump /v dba 1,1 offset 148 count 4 File: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf (1) Block: 1 Offsets: 148 to 151 Dba:0x00400001------------------------------------------------------- 4d000000 l M... <16 bytes per line>BBED> dump /v dba 5,1 offset 148 count 4 File: /u01/app/oracle/oradata/ORCL/datafile/test_01.dbf (5) Block: 1 Offsets: 148 to 151 Dba:0x01400001------------------------------------------------------- 48000000 l H... <16 bytes per line>BBED>----只需修改前两个字节BBED> modify /x 4d00 dba 5,1 offset 148 File: /u01/app/oracle/oradata/ORCL/datafile/test_01.dbf (5) Block: 1 Offsets: 148 to 151 Dba:0x01400001------------------------------------------------------------------------ 4d000000 <32 bytes per line>BBED>
3.4 应用修改
BBED> sum applyCheck value for File 5, Block 1:current = 0xf719, required = 0xf719BBED>四.创建控制文件
虽然我们修改了datafile header里的几个值，但是v$datafile里的scn并没有改变，因为v$datafile里的scn是从控制文件里读取的。 而BBED 不能修改控制文件，所以，还需要重建控制文件。
4.1 用带*._allow_resetlogs_corruption=TRUE的pfile启库到nomount状态
SQL> startup nomount pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.oraORACLE instance started.Total System Global Area 285212672 bytesFixed Size 1218992 bytesVariable Size 92276304 bytesDatabase Buffers 188743680 bytesRedo Buffers 2973696 bytesSQL> 注：如果没有此参数后期open resetlogs 时会报：SQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01194: file 5 needs more recovery to be consistentORA-01110: data file 5: ‘/u01/app/oracle/oradata/ORCL/datafile/test_01.dbf‘4.2 创建控制文件
SQL> CREATE CONTROLFILE REUSE DATABASE "orcl" NORESETLOGS ARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292 7 LOGFILE 8 GROUP 1 ‘/u01/app/oracle/oradata/ORCL/onlinelog/redo01.log‘ SIZE 50M, 9 GROUP 2 ‘/u01/app/oracle/oradata/ORCL/onlinelog/redo02.log‘ SIZE 50M, 10 GROUP 3 ‘/u01/app/oracle/oradata/ORCL/onlinelog/redo03.log‘ SIZE 50M 11 DATAFILE 12 ‘/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_bc7lf4bd_.dbf‘, 13 ‘/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf‘, 14 ‘/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_bc7ldql4_.dbf‘, 15 ‘/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_bc7lflno_.dbf‘, 16 ‘/u01/app/oracle/oradata/ORCL/datafile/test_01.dbf‘ 17 CHARACTER SET AL32UTF8 18 ;CREATE CONTROLFILE REUSE DATABASE "orcl" NORESETLOGS ARCHIVELOG*ERROR at line 1:ORA-01503: CREATE CONTROLFILE failedORA-01229: data file 5 is inconsistent with logsORA-01110: data file 5: ‘/u01/app/oracle/oradata/ORCL/datafile/test_01.dbf‘SQL>因为ORA-01229: data file 5 is inconsistent with logs，索引不能用NORESETLOGS创建控制文件，只能用RESETLOGSSQL> CREATE CONTROLFILE REUSE DATABASE "orcl" RESETLOGS ARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292 7 LOGFILE 8 GROUP 1 ‘/u01/app/oracle/oradata/ORCL/onlinelog/redo01.log‘ SIZE 50M, 9 GROUP 2 ‘/u01/app/oracle/oradata/ORCL/onlinelog/redo02.log‘ SIZE 50M, 10 GROUP 3 ‘/u01/app/oracle/oradata/ORCL/onlinelog/redo03.log‘ SIZE 50M 11 DATAFILE 12 ‘/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_bc7lf4bd_.dbf‘, 13 ‘/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf‘, 14 ‘/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_bc7ldql4_.dbf‘, 15 ‘/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_bc7lflno_.dbf‘, 16 ‘/u01/app/oracle/oradata/ORCL/datafile/test_01.dbf‘ 17 CHARACTER SET AL32UTF8 18 ;Control file created.SQL>4.3 恢复数据库
SQL> recover database using backup controlfile until cancel;ORA-00279: change 491472 generated at 01/17/2015 10:24:13 needed for thread 1ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2015_01_17/o1_mf_1_9_%u_.arcORA-00280: change 491472 for thread 1 is in sequence #9Specify log: {<RET>=suggested | filename | AUTO | CANCEL}auto ORA-00328: archived log ends at change 491322, need later change 491472ORA-00334: archived log: ‘/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2015_01_17/o1_mf_1_9_bcnzchrb_.arc‘ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 5 needs more recovery to be consistentORA-01110: data file 5: ‘/u01/app/oracle/oradata/ORCL/datafile/test_01.dbf‘SQL> alter database open resetlogs; --Database altered.SQL>
至此，数据库打开，不过并不是完全恢复
Oracle bbed 实用示例-----File Header Reset
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。