联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
使用 dul 挖数据文件初试
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
最近测试了下dul,整体感觉和odu差不多
1.配置init.dul
[oracle@xifenfei dul]$ more init.dul osd_big_endian_flag=false osd_dba_file_bits=10 osd_c_struct_alignment=32 osd_file_leader_size=1 osd_word_size = 32 dc_columns=2000000 dc_tables=10000 dc_objects=1000000 dc_users=400 dc_segments=100000 Buffer=10485760 control_file = control.txt db_block_size=8192 export_mode=true --false表示是sqlloader,true表示imp compatible=10
2.配置控制文件
[oracle@xifenfei dul]$ more control.txt
0 1 /u01/oracle/oradata/XFF/system01.dbf
1 2 /u01/oracle/oradata/XFF/undotbs01.dbf
2 3 /u01/oracle/oradata/XFF/sysaux01.dbf
4 4 /u01/oracle/oradata/XFF/users01.dbf
6 5 /u01/oracle/oradata/XFF/datfttuser.dbf
--sql语句
select ts#,rfile#,name from v$datafile;
3.启动dul
[oracle@xifenfei dul]$ ./dul Data UnLoader: 10.2.0.5.13 - Internal Only - on Sun Jun 10 06:39:47 2012 with 64-bit io functions Copyright (c) 1994 2012 Bernard van Duijnen All rights reserved. Strictly Oracle Internal Use Only Found db_id = 3426707456 Found db_name = XFF
4.加载初始化数据字典
DUL> BOOTSTRAP;
Probing file = 1, block = 377
. unloading table BOOTSTRAP$
DUL: Warning: block number is non zero but marked deferred trying to process it anyhow
57 rows unloaded
DUL: Warning: Dictionary cache DC_BOOTSTRAP is empty
Reading BOOTSTRAP.dat 57 entries loaded
Parsing Bootstrap$ contents
Generating dict.ddl for version 10
OBJ$: segobjno 18, file 1 block 121
TAB$: segobjno 2, tabno 1, file 1 block 25
COL$: segobjno 2, tabno 5, file 1 block 25
USER$: segobjno 10, tabno 1, file 1 block 89
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table OBJ$ 50930 rows unloaded
. unloading table TAB$ 1593 rows unloaded
. unloading table COL$ 55163 rows unloaded
. unloading table USER$ 61 rows unloaded
Reading USER.dat 61 entries loaded
Reading OBJ.dat 50930 entries loaded and sorted 50930 entries
Reading TAB.dat 1593 entries loaded
Reading COL.dat 55163 entries loaded and sorted 55163 entries
Reading BOOTSTRAP.dat 57 entries loaded
DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 10
OBJ$: segobjno 18, file 1 block 121
TAB$: segobjno 2, tabno 1, file 1 block 25
COL$: segobjno 2, tabno 5, file 1 block 25
USER$: segobjno 10, tabno 1, file 1 block 89
TABPART$: segobjno 266, file 1 block 2121
INDPART$: segobjno 271, file 1 block 2161
TABCOMPART$: segobjno 288, file 1 block 2297
INDCOMPART$: segobjno 293, file 1 block 2345
TABSUBPART$: segobjno 278, file 1 block 2217
INDSUBPART$: segobjno 283, file 1 block 2257
IND$: segobjno 2, tabno 3, file 1 block 25
ICOL$: segobjno 2, tabno 4, file 1 block 25
LOB$: segobjno 2, tabno 6, file 1 block 25
COLTYPE$: segobjno 2, tabno 7, file 1 block 25
TYPE$: segobjno 181, tabno 1, file 1 block 1297
COLLECTION$: segobjno 181, tabno 2, file 1 block 1297
ATTRIBUTE$: segobjno 181, tabno 3, file 1 block 1297
LOBFRAG$: segobjno 299, file 1 block 2393
LOBCOMPPART$: segobjno 302, file 1 block 2425
UNDO$: segobjno 15, file 1 block 105
TS$: segobjno 6, tabno 2, file 1 block 57
PROPS$: segobjno 96, file 1 block 721
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
50930 rows unloaded
. unloading table TAB$
DUL: Warning: Recreating file "TAB.ctl"
1593 rows unloaded
. unloading table COL$
DUL: Warning: Recreating file "COL.ctl"
55163 rows unloaded
. unloading table USER$
DUL: Warning: Recreating file "USER.ctl"
61 rows unloaded
. unloading table TABPART$ 90 rows unloaded
. unloading table INDPART$ 99 rows unloaded
. unloading table TABCOMPART$ 0 rows unloaded
. unloading table INDCOMPART$ 0 rows unloaded
. unloading table TABSUBPART$ 0 rows unloaded
. unloading table INDSUBPART$ 0 rows unloaded
. unloading table IND$ 2251 rows unloaded
. unloading table ICOL$ 3669 rows unloaded
. unloading table LOB$ 537 rows unloaded
. unloading table COLTYPE$ 1702 rows unloaded
. unloading table TYPE$ 1886 rows unloaded
. unloading table COLLECTION$ 552 rows unloaded
. unloading table ATTRIBUTE$ 7051 rows unloaded
. unloading table LOBFRAG$ 1 row unloaded
. unloading table LOBCOMPPART$ 0 rows unloaded
. unloading table UNDO$ 21 rows unloaded
. unloading table TS$ 7 rows unloaded
. unloading table PROPS$ 27 rows unloaded
Reading USER.dat 61 entries loaded
Reading OBJ.dat 50930 entries loaded and sorted 50930 entries
Reading TAB.dat 1593 entries loaded
Reading COL.dat 55163 entries loaded and sorted 55163 entries
Reading TABPART.dat 90 entries loaded and sorted 90 entries
Reading TABCOMPART.dat 0 entries loaded and sorted 0 entries
Reading TABSUBPART.dat 0 entries loaded and sorted 0 entries
Reading INDPART.dat 99 entries loaded and sorted 99 entries
Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries
Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries
Reading IND.dat 2251 entries loaded
Reading LOB.dat 537 entries loaded
Reading ICOL.dat 3669 entries loaded
Reading COLTYPE.dat 1702 entries loaded
Reading TYPE.dat 1886 entries loaded
Reading ATTRIBUTE.dat 7051 entries loaded
Reading COLLECTION.dat 552 entries loaded
Reading BOOTSTRAP.dat 57 entries loaded
Reading LOBFRAG.dat 1 entries loaded and sorted 1 entries
Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries
Reading UNDO.dat 21 entries loaded
Reading TS.dat 7 entries loaded
Reading PROPS.dat 27 entries loaded
Database character set is ZHS16GBK
Database national character set is AL16UTF16
5.导出某种表
DUL> desc chf.t_xifenfei;
Table CHF.T_XIFENFEI
obj#= 52189, dataobj#= 52189, ts#= 4, file#= 4, block#=123
tab#= 0, segcols= 2, clucols= 0
Column information:
icol# 01 segcol# 01 ID len 22 type 2 NUMBER(0,-127)
icol# 02 segcol# 02 NAME len 100 type 1 VARCHAR2 cs 852(ZHS16GBK)
DUL> UNLOAD TABLE chf.t_xifenfei;
. unloading table T_XIFENFEI 2 rows unloaded
6.验证导出dmp文件
[oracle@xifenfei dul]$ strings CHF_T_XIFENFEI.dmp
EXPORT:V07.00.07
UBernard's DUL
RTABLES
1024
Direct UnLoader(C) in EXPort mode
TABLE "T_XIFENFEI"
CREATE TABLE "T_XIFENFEI"("ID" NUMBER,"NAME" VARCHAR2(100))
INSERT INTO "T_XIFENFEI" ("ID", "NAME") VALUES (:1, :2)
www.xifenfei.com
WWW.XIFENEI.COM
EXIT
使用bbed让rac中的sysaux数据文件online
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：使用bbed让rac中的sysaux数据文件online
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
一个朋友的11g rac库的sysaux表空间因某种原因缺少历史归档,导致无法正常online,是的数据库的很多功能受限.通过实现展示恢复过程.
模拟环境
SQL> select name,file#,status from v$datafile; NAME FILE# STATUS ---------------------------------------------------- ---------- ------- +XIFENFEI/xff/datafile/system.256.776961315 1 SYSTEM +XIFENFEI/xff/datafile/sysaux.257.776961315 2 ONLINE +XIFENFEI/xff/datafile/undotbs1.258.776961317 3 ONLINE +XIFENFEI/xff/datafile/user_dd.dbf 4 ONLINE +XIFENFEI/xff/datafile/undotbs2.264.776961693 5 ONLINE +XIFENFEI/asm/datafile/xifenfei01.dbf.268.781967893 6 ONLINE 6 rows selected. SQL> alter database datafile 2 offline; Database altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 14 Next log sequence to archive 15 Current log sequence 15 SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 19 Next log sequence to archive 19 Current log sequence 20 --删除部分归档日志 [grid@rac1 ~]$ asmcmd ASMCMD> ls DATA/ XIFENFEI/ ASMCMD> cd data ASMCMD> ls XFF/ rac-cluster/ ASMCMD> cd xff ASMCMD> ls ARCHIVELOG/ CONTROLFILE/ ONLINELOG/ ASMCMD> cd archivelog ASMCMD> ls 2012_03_03/ 2012_04_13/ 2012_04_30/ 2012_05_01/ 2012_05_24/ 2012_06_12/ ASMCMD> cd 2012_06_12 ASMCMD> ls thread_1_seq_15.280.785752747 thread_1_seq_16.281.785752845 thread_1_seq_17.282.785752929 thread_1_seq_18.283.785753043 thread_1_seq_19.284.785753115 ASMCMD> rm thread_1_seq_16.281.785752845 ASMCMD> rm thread_1_seq_15.280.785752747
尝试online 数据文件
SQL> alter database datafile 2 online;
alter database datafile 2 online
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '+XIFENFEI/xff/datafile/sysaux.257.776961315'
SQL> recover datafile 2;
ORA-00279: change 1155352 generated at 06/12/2012 08:20:10 needed for thread 1
ORA-00289: suggestion :
+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747
ORA-00280: change 1155352 for thread 1 is in sequence #15
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747'
ORA-17503: ksfdopn:2 Failed to open file
+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747
ORA-15012: ASM file
'+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747' does not exist
ORA-00308: cannot open archived log
'+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747'
ORA-17503: ksfdopn:2 Failed to open file
+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747
ORA-15012: ASM file
'+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747' does not exist
准备bbed修改数据文件
现在datafile 2不能恢复,我们需要修改的就是该datafile header 相关的scn等信息,另外拷贝一个数据文件出来做修改时候参考
RMAN> copy datafile 2 to '/tmp/auxsys.dbf_rman'; Starting backup at 2012-06-12 08:59:07 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 instance=XFF1 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=+XIFENFEI/xff/datafile/sysaux.257.776961315 output file name=/tmp/auxsys.dbf_rman tag=TAG20120612T090029 RECID=1 STAMP=785754322 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:50 Finished backup at 2012-06-12 09:05:36 RMAN> copy datafile 4 to '/tmp/user.dbf_rman'; Starting backup at 2012-06-12 09:09:28 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=+XIFENFEI/xff/datafile/user_dd.dbf output file name=/tmp/user.dbf_rman tag=TAG20120612T090932 RECID=2 STAMP=785754582 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 Finished backup at 2012-06-12 09:09:48
bbed修改datafile header
[oracle@rac1 tmp]$ bbed password=blockedit listfile=/tmp/o_bbed mode=edit
BBED: Release 2.0.0.0.0 - Limited Production on Tue Jun 12 09:37:30 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /tmp/auxsys.dbf_rman 0
2 /tmp/user.dbf_rman 0
BBED> set file 2 block 1
FILE# 2
BLOCK# 1
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x0011a787
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x2ed5a9cd
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000014
ub4 kcrbabno @504 0x000000c5
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
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000086
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000085
BBED> set file 1 block 1
FILE# 1
BLOCK# 1
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x0011a118
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x2ed59e3a
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x0000000f
ub4 kcrbabno @504 0x0000c4ed
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
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000079
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000078
/*
确定需要修改项kscnbas/kcvcptim/kcvfhcpc/kcvfhccc的相关信息
*/
BBED> set count 16
COUNT 16
BBED> d file 2 block 1 offset 484
File: /tmp/user.dbf_rman (2)
Block: 1 Offsets: 484 to 499 Dba:0x00800001
------------------------------------------------------------------------
87a71100 00001000 cda9d52e 01000000
<32 bytes per line>
BBED> m /x 87a71100 file 1 block 1 offset 484
BBED-00209: invalid number (87a71100)
BBED> m /x 87a7 file 1 block 1 offset 484
File: /tmp/auxsys.dbf_rman (1)
Block: 1 Offsets: 484 to 499 Dba:0x00400001
------------------------------------------------------------------------
87a71100 00000000 3a9ed52e 01000000
<32 bytes per line>
BBED> d file 2 block 1 offset 492
File: /tmp/user.dbf_rman (2)
Block: 1 Offsets: 492 to 507 Dba:0x00800001
------------------------------------------------------------------------
cda9d52e 01000000 14000000 c5000000
<32 bytes per line>
BBED> m /x cda9d52e file 1 block 1 offset 492
BBED-00209: invalid number (cda9d52e)
BBED> d file 1 block 1 offset 492
File: /tmp/auxsys.dbf_rman (1)
Block: 1 Offsets: 492 to 507 Dba:0x00400001
------------------------------------------------------------------------
3a9ed52e 01000000 0f000000 edc40000
<32 bytes per line>
BBED> m /x cda9 file 1 block 1 offset 492
File: /tmp/auxsys.dbf_rman (1)
Block: 1 Offsets: 492 to 507 Dba:0x00400001
------------------------------------------------------------------------
cda9d52e 01000000 0f000000 edc40000
<32 bytes per line>
BBED> d file 1 block 1 offset 140
File: /tmp/auxsys.dbf_rman (1)
Block: 1 Offsets: 140 to 155 Dba:0x00400001
------------------------------------------------------------------------
79000000 2970bc2e 78000000 00000000
<32 bytes per line>
BBED> d file 2 block 1 offset 140
File: /tmp/user.dbf_rman (2)
Block: 1 Offsets: 140 to 155 Dba:0x00800001
------------------------------------------------------------------------
86000000 2970bc2e 85000000 00000000
<32 bytes per line>
BBED> m /x 86000000 file 1 block 1 offset 140
BBED-00209: invalid number (86000000)
BBED> m /x 8600 file 1 block 1 offset 140
File: /tmp/auxsys.dbf_rman (1)
Block: 1 Offsets: 140 to 155 Dba:0x00400001
------------------------------------------------------------------------
86000000 2970bc2e 78000000 00000000
<32 bytes per line>
BBED> d file 2 block 1 offset 148
File: /tmp/user.dbf_rman (2)
Block: 1 Offsets: 148 to 163 Dba:0x00800001
------------------------------------------------------------------------
85000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 8500 file 1 block 1 offset 148
File: /tmp/auxsys.dbf_rman (1)
Block: 1 Offsets: 148 to 163 Dba:0x00400001
------------------------------------------------------------------------
85000000 00000000 00000000 00000000
<32 bytes per line>
BBED> set file 1 block 1
FILE# 1
BLOCK# 1
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x0011a787
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x2ed5a9cd
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x0000000f
ub4 kcrbabno @504 0x0000c4ed
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
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000086
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000085
BBED> sum apply
Check value for File 1, Block 1:
current = 0x48c4, required = 0x48c4
使用修改后数据文件尝试online
SQL> alter database rename file '+XIFENFEI/xff/datafile/sysaux.257.776961315' to '/tmp/auxsys.dbf_rman'; Database altered. SQL> recover database datafile 2 ; ORA-00274: illegal recovery option DATAFILE SQL> recover database datafile 2; ORA-00274: illegal recovery option DATAFILE SQL> recover datafile 2; ORA-00283: recovery session canceled due to errors ORA-01122: database file 2 failed verification check ORA-01110: data file 2: '/tmp/auxsys.dbf_rman' ORA-01207: file is more recent than control file - old control file
尝试重建控制文件
SQL> alter database backup controlfile to trace as '/tmp/xifenfei.ctl'; Database altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP NOMOUNT ORACLE instance started. Total System Global Area 535662592 bytes Fixed Size 1346140 bytes Variable Size 411043236 bytes Database Buffers 117440512 bytes Redo Buffers 5832704 bytes SQL> @xifenfei_ctl CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS ARCHIVELOG * ERROR at line 1: ORA-01503: CREATE CONTROLFILE failed ORA-12720: operation requires database is in EXCLUSIVE mode --在rac中重建控制文件需要设置cluster_database=FALSE SQL> alter system set cluster_database=FALSE scope=spfile; System altered. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> STARTUP NOMOUNT ORACLE instance started. Total System Global Area 535662592 bytes Fixed Size 1346140 bytes Variable Size 411043236 bytes Database Buffers 117440512 bytes Redo Buffers 5832704 bytes SQL> @xifenfei_ctl Control file created.
online数据文件
重建控制文件恢复数据库之后 datafile 2自动online成功,省去了手工处理麻烦,如果没有自动online,请手工处理
SQL> recover database; Media recovery complete. SQL> alter database open; Database altered. SQL> col name for a52 SQL> select name,file#,status from v$datafile; NAME FILE# STATUS ---------------------------------------------------- ---------- ------- +XIFENFEI/xff/datafile/system.256.776961315 1 SYSTEM /tmp/auxsys.dbf_rman 2 ONLINE +XIFENFEI/xff/datafile/undotbs1.258.776961317 3 ONLINE +XIFENFEI/xff/datafile/user_dd.dbf 4 ONLINE +XIFENFEI/xff/datafile/undotbs2.264.776961693 5 ONLINE +XIFENFEI/asm/datafile/xifenfei01.dbf.268.781967893 6 ONLINE 6 rows selected.
文件系统中的datafile 2 恢复到asm中
SQL> alter database datafile 2 offline; Database altered. RMAN> copy datafile 2 to '+XIFENFEI'; Starting backup at 2012-06-12 10:55:42 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=21 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/tmp/auxsys.dbf_rman output file name=+XIFENFEI/xff/datafile/sysaux.257.785761227 tag=TAG20120612T105800 RECID=1 STAMP=785762097 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:16:24 Finished backup at 2012-06-12 11:15:05 RMAN> switch datafile 2 to copy; datafile 2 switched to datafile copy "+XIFENFEI/xff/datafile/sysaux.257.785761227" RMAN> recover datafile 2; Starting recover at 2012-06-12 11:30:32 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:01:30 Finished recover at 2012-06-12 11:34:11 RMAN> sql 'alter database datafile 2 online'; sql statement: alter database datafile 2 online
验证和收尾工作
SQL> select name,file#,status from v$datafile; NAME FILE# STATUS ---------------------------------------------------- ---------- ------- +XIFENFEI/xff/datafile/system.256.776961315 1 SYSTEM +XIFENFEI/xff/datafile/sysaux.257.785761227 2 ONLINE +XIFENFEI/xff/datafile/undotbs1.258.776961317 3 ONLINE +XIFENFEI/xff/datafile/user_dd.dbf 4 ONLINE +XIFENFEI/xff/datafile/undotbs2.264.776961693 5 ONLINE +XIFENFEI/asm/datafile/xifenfei01.dbf.268.781967893 6 ONLINE SQL> alter system set cluster_database=true scope=spfile; System altered. --然后重启节点
使用bbed修复损坏datafile header
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
相关信息和准备工作
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production CORE 9.2.0.3.0 Production TNS for Linux: Version 9.2.0.4.0 - Production NLSRTL Version 9.2.0.4.0 - Production SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "www.xifenfei.com" from dual; www.xifenfei.com ------------------- 2012-05-29 19:39:48
启动数据块异常
SQL> startup ORACLE instance started. Total System Global Area 353441008 bytes Fixed Size 451824 bytes Variable Size 184549376 bytes Database Buffers 167772160 bytes Redo Buffers 667648 bytes Database mounted. ORA-01110: data file 11: '/u01/oracle/oradata/xifenfei/bbed01.dbf' ORA-01115: IO error reading block from file 11 (block # 1) ORA-27072: skgfdisp: I/O error
bbed检测datafile header
[oracle@xifenfei ~]$ bbed password=blockedit blocksize=8192 listfile=/home/oracle/bbed.file mode=edit
BBED: Release 2.0.0.0.0 - Limited Production on Sat May 26 05:29:37 2012
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /home/oracle/users01.dbf 0
2 /home/oracle/system01.dbf.head 0
3 /home/oracle/data11.ora 0
4 /u01/oracle/oradata/xifenfei/system01.dbf 0
5 /u01/oracle/oradata/xifenfei/users01.dbf 0
6 /home/oracle/data11.ora.10 0
11 /u01/oracle/oradata/xifenfei/bbed01.dbf 0
12 /u01/oracle/oradata/xifenfei/bbed02.dbf 0
BBED> set file 11
FILE# 11
BBED> set block 1
BLOCK# 1
BBED> map
File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
Block: 1 Dba:0x02c00001
------------------------------------------------------------
BBED-00400: invalid blocktype (00)
BBED> d
File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
Block: 1 Offsets: 0 to 511 Dba:0x02c00001
------------------------------------------------------------------------
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
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>
--header 记录全部为0，证明数据文件header坏掉
拷贝数据块
为了方便,拷贝同一个表空间的数据块
BBED> set file 12
FILE# 12
BBED> set block 1
BLOCK# 1
BBED> d count 16
File: /u01/oracle/oradata/xifenfei/bbed02.dbf (12)
Block: 1 Offsets: 0 to 15 Dba:0x03000001
------------------------------------------------------------------------
0b020000 01000003 00000000 00000104
<32 bytes per line>
BBED> copy dba 0x03000001 to dba 0x02c00001
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
Block: 1 Offsets: 0 to 15 Dba:0x02c00001
------------------------------------------------------------------------
0b020000 01000003 00000000 00000104
<32 bytes per line>
BBED> show
FILE# 11
BLOCK# 1
OFFSET 0
DBA 0x02c00001 (46137345 11,1)
FILENAME /u01/oracle/oradata/xifenfei/bbed01.dbf
BIFILE bifile.bbd
LISTFILE /home/oracle/bbed.file
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 16
LOGFILE log.bbd
SPOOL No
BBED> map
File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
Block: 1 Dba:0x02c00001
------------------------------------------------------------
Data File Header
struct kcvfh, 360 bytes @0
ub4 tailchk @8188
修改数据块内容
BBED> p kcvfh
struct kcvfh, 360 bytes @0
struct kcvfhbfh, 20 bytes @0
ub1 type_kcbh @0 0x0b
ub1 frmt_kcbh @1 0x02
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x03000001
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 0xb10a
ub2 spare3_kcbh @18 0x0000
struct kcvfhhdr, 76 bytes @20
ub4 kccfhswv @20 0x09200000
ub4 kccfhcvn @24 0x08000000
ub4 kccfhdbi @28 0x5314b4cd
text kccfhdbn[0] @32 X
text kccfhdbn[1] @33 I
text kccfhdbn[2] @34 F
text kccfhdbn[3] @35 E
text kccfhdbn[4] @36 N
text kccfhdbn[5] @37 F
text kccfhdbn[6] @38 E
text kccfhdbn[7] @39 I
ub4 kccfhcsq @40 0x000001d8
ub4 kccfhfsz @44 0x00001400
s_blkz kccfhbsz @48 0x00
ub2 kccfhfno @52 0x000c
ub2 kccfhtyp @54 0x0003
ub4 kccfhacid @56 0x00000000
ub4 kccfhcks @60 0x00000000
text kccfhtag[0] @64
text kccfhtag[1] @65
text kccfhtag[2] @66
text kccfhtag[3] @67
text kccfhtag[4] @68
text kccfhtag[5] @69
text kccfhtag[6] @70
text kccfhtag[7] @71
text kccfhtag[8] @72
text kccfhtag[9] @73
text kccfhtag[10] @74
text kccfhtag[11] @75
text kccfhtag[12] @76
text kccfhtag[13] @77
text kccfhtag[14] @78
text kccfhtag[15] @79
text kccfhtag[16] @80
text kccfhtag[17] @81
text kccfhtag[18] @82
text kccfhtag[19] @83
text kccfhtag[20] @84
text kccfhtag[21] @85
text kccfhtag[22] @86
text kccfhtag[23] @87
text kccfhtag[24] @88
text kccfhtag[25] @89
text kccfhtag[26] @90
text kccfhtag[27] @91
text kccfhtag[28] @92
text kccfhtag[29] @93
text kccfhtag[30] @94
text kccfhtag[31] @95
ub4 kcvfhrdb @96 0x00000000
struct kcvfhcrs, 8 bytes @100
ub4 kscnbas @100 0xc00a3405
ub2 kscnwrp @104 0x0b2c
ub4 kcvfhcrt @108 0x2ebeb8c3
ub4 kcvfhrlc @112 0x2e51408f
struct kcvfhrls, 8 bytes @116
ub4 kscnbas @116 0x0002ab78
ub2 kscnwrp @120 0x0000
ub4 kcvfhbti @124 0x00000000
struct kcvfhbsc, 8 bytes @128
ub4 kscnbas @128 0x00000000
ub2 kscnwrp @132 0x0000
ub2 kcvfhbth @136 0x0000
ub2 kcvfhsta @138 0x0000 (NONE)
struct kcvfhckp, 36 bytes @140
struct kcvcpscn, 8 bytes @140
ub4 kscnbas @140 0xc00b6467
ub2 kscnwrp @144 0x0b2c
ub4 kcvcptim @148 0x2ebf0c07
ub2 kcvcpthr @152 0x0001
union u, 12 bytes @156
struct kcvcprba, 12 bytes @156
ub4 kcrbaseq @156 0x00000015
ub4 kcrbabno @160 0x0000429a
ub2 kcrbabof @164 0x0010
struct kcvcptr, 12 bytes @156
struct kcrtrscn, 8 bytes @156
ub4 kscnbas @156 0x00000015
ub2 kscnwrp @160 0x429a
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
ub4 kcvfhcpc @176 0x0000000d
ub4 kcvfhrts @180 0x2ebeea4f
ub4 kcvfhccc @184 0x0000000c
struct kcvfhbcp, 36 bytes @188
struct kcvcpscn, 8 bytes @188
ub4 kscnbas @188 0x00000000
ub2 kscnwrp @192 0x0000
ub4 kcvcptim @196 0x00000000
ub2 kcvcpthr @200 0x0000
union u, 12 bytes @204
struct kcvcprba, 12 bytes @204
ub4 kcrbaseq @204 0x00000000
ub4 kcrbabno @208 0x00000000
ub2 kcrbabof @212 0x0000
struct kcvcptr, 12 bytes @204
struct kcrtrscn, 8 bytes @204
ub4 kscnbas @204 0x00000000
ub2 kscnwrp @208 0x0000
ub4 kcrtrtim @212 0x00000000
ub1 kcvcpetb[0] @216 0x00
ub1 kcvcpetb[1] @217 0x00
ub1 kcvcpetb[2] @218 0x00
ub1 kcvcpetb[3] @219 0x00
ub1 kcvcpetb[4] @220 0x00
ub1 kcvcpetb[5] @221 0x00
ub1 kcvcpetb[6] @222 0x00
ub1 kcvcpetb[7] @223 0x00
ub4 kcvfhbhz @224 0x00000000
struct kcvfhxcd, 16 bytes @228
ub4 space_kcvmxcd[0] @228 0x00000000
ub4 space_kcvmxcd[1] @232 0x00000000
ub4 space_kcvmxcd[2] @236 0x00000000
ub4 space_kcvmxcd[3] @240 0x00000000
word kcvfhtsn @244 12
ub2 kcvfhtln @248 0x0004
text kcvfhtnm[0] @250 B
text kcvfhtnm[1] @251 B
text kcvfhtnm[2] @252 E
text kcvfhtnm[3] @253 D
text kcvfhtnm[4] @254
text kcvfhtnm[5] @255
text kcvfhtnm[6] @256
text kcvfhtnm[7] @257
text kcvfhtnm[8] @258
text kcvfhtnm[9] @259
text kcvfhtnm[10] @260
text kcvfhtnm[11] @261
text kcvfhtnm[12] @262
text kcvfhtnm[13] @263
text kcvfhtnm[14] @264
text kcvfhtnm[15] @265
text kcvfhtnm[16] @266
text kcvfhtnm[17] @267
text kcvfhtnm[18] @268
text kcvfhtnm[19] @269
text kcvfhtnm[20] @270
text kcvfhtnm[21] @271
text kcvfhtnm[22] @272
text kcvfhtnm[23] @273
text kcvfhtnm[24] @274
text kcvfhtnm[25] @275
text kcvfhtnm[26] @276
text kcvfhtnm[27] @277
text kcvfhtnm[28] @278
text kcvfhtnm[29] @279
ub4 kcvfhrfn @280 0x0000000c
struct kcvfhrfs, 8 bytes @284
ub4 kscnbas @284 0x00000000
ub2 kscnwrp @288 0x0000
ub4 kcvfhrft @292 0x2ebee9f9
struct kcvfhafs, 8 bytes @296
ub4 kscnbas @296 0x00000000
ub2 kscnwrp @300 0x0000
ub4 kcvfhbbc @304 0x00000000
ub4 kcvfhncb @308 0x00000000
ub4 kcvfhmcb @312 0x00000000
ub4 kcvfhlcb @316 0x00000000
ub4 kcvfhbcs @320 0x00000000
ub2 kcvfhofb @324 0x0000
ub2 kcvfhnfb @326 0x0000
ub4 kcvfhprc @328 0x00000000
struct kcvfhprs, 8 bytes @332
ub4 kscnbas @332 0x00000000
ub2 kscnwrp @336 0x0000
struct kcvfhprfs, 8 bytes @340
ub4 kscnbas @340 0x00000000
ub2 kscnwrp @344 0x0000
ub4 kcvfhtrt @356 0x00000000
/*需要修改内容
ub4 rdba_kcbh @4 0x03000001
ub4 kccfhfsz @44 0x00001400
ub2 kccfhfno @52 0x000c
struct kcvfhcrs, 8 bytes @100
ub4 kscnbas @100 0xc00a3405
ub2 kscnwrp @104 0x0b2c
ub4 kcvfhrfn @280 0x0000000c
/
/*修改值(通过错误提示结合file$表)
rdba_kcbh 02c00001
kccfhfsz 00000500
kccfhfno 000b
kscnbas c00a32b8
kcvfhrfn 0000000b
/
BBED> set offset 4
OFFSET 4
BBED> m /x 0100c002
File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
Block: 1 Offsets: 4 to 19 Dba:0x02c00001
------------------------------------------------------------------------
0100c002 00000000 00000104 0ab10000
<32 bytes per line>
BBED> set offset 44
OFFSET 44
BBED> m /x 00050000
File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
Block: 1 Offsets: 44 to 59 Dba:0x02c00001
------------------------------------------------------------------------
00050000 00200000 0c000300 00000000
<32 bytes per line>
BBED> set offset 52
OFFSET 52
BBED> m /x
BBED-00203: incomplete/malformed command
BBED> m /x 0b00
File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
Block: 1 Offsets: 52 to 67 Dba:0x02c00001
------------------------------------------------------------------------
0b000300 00000000 00000000 00000000
<32 bytes per line>
BBED> set offset 100
OFFSET 100
BBED> m /x b8320ac0
File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
Block: 1 Offsets: 100 to 115 Dba:0x02c00001
------------------------------------------------------------------------
b8320ac0 2c0b0000 c3b8be2e 8f40512e
<32 bytes per line>
BBED> set offset 280
OFFSET 280
BBED> m /x 0b000000
File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
Block: 1 Offsets: 280 to 295 Dba:0x02c00001
------------------------------------------------------------------------
0b000000 00000000 00000000 f9e9be2e
<32 bytes per line>
BBED> sum apply
Check value for File 11, Block 1:
current = 0xa777, required = 0xa777
重建控制文件open数据库
SQL> alter database backup controlfile to trace as '/tmp/t_xifenfie.ctl'; Database altered. SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup nomount ORACLE instance started. Total System Global Area 353441008 bytes Fixed Size 451824 bytes Variable Size 184549376 bytes Database Buffers 167772160 bytes Redo Buffers 667648 bytes SQL> CREATE CONTROLFILE REUSE DATABASE "XIFENFEI" NORESETLOGS ARCHIVELOG 2 MAXLOGFILES 50 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 1 6 MAXLOGHISTORY 226 7 LOGFILE 8 GROUP 1 '/u01/oracle/oradata/xifenfei/redo01.log' SIZE 100M, 9 GROUP 2 '/u01/oracle/oradata/xifenfei/redo02.log' SIZE 100M, 10 GROUP 3 '/u01/oracle/oradata/xifenfei/redo03.log' SIZE 100M 11 DATAFILE 12 '/u01/oracle/oradata/xifenfei/system01.dbf', 13 '/u01/oracle/oradata/xifenfei/undotbs01.dbf', 14 '/u01/oracle/oradata/xifenfei/cwmlite01.dbf', 15 '/u01/oracle/oradata/xifenfei/drsys01.dbf', 16 '/u01/oracle/oradata/xifenfei/example01.dbf', 17 '/u01/oracle/oradata/xifenfei/indx01.dbf', 18 '/u01/oracle/oradata/xifenfei/odm01.dbf', 19 '/u01/oracle/oradata/xifenfei/tools01.dbf', 20 '/u01/oracle/oradata/xifenfei/users01.dbf', 21 '/u01/oracle/oradata/xifenfei/xdb01.dbf', 22 '/u01/oracle/oradata/xifenfei/bbed01.dbf', 23 '/u01/oracle/oradata/xifenfei/bbed02.dbf' 24 CHARACTER SET ZHS16GBK 25 ; Control file created. SQL> recover database ; ORA-00283: recovery session canceled due to errors ORA-00264: no recovery required SQL> alter database open; Database altered.
至此通过拷贝相同表空间的datafile header修复损坏的datafile header
bbed 找回被删除数据
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：bbed 找回被删除数据
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
创建模拟表数据
SQL> create table t_xifenfei(id number,name varchar2(10)); Table created. SQL> insert into t_xifenfei values(1,'xifenfei'); 1 row created. SQL> insert into t_xifenfei values(2,'XIFENFEI'); 1 row created. SQL> commit; Commit complete.
dump数据块
SQL> alter system flush BUFFER_CACHE; System altered. SQL> select rowid,id,name, 2 dbms_rowid.rowid_relative_fno(rowid)rel_fno, 3 dbms_rowid.rowid_block_number(rowid)blockno, 4 dbms_rowid.rowid_row_number(rowid) rowno 5 from chf.t_xifenfei; ROWID ID NAME REL_FNO BLOCKNO ROWNO ------------------ ---------- ---------- ---------- ---------- ---------- AAASdmAAEAAAACvAAA 1 xifenfei 4 175 0 AAASdmAAEAAAACvAAB 2 XIFENFEI 4 175 1 SQL> alter system dump datafile 4 block 175; System altered.
dump文件内容
block_row_dump: tab 0, row 0, @0x1f89 tl: 15 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 02 col 1: [ 8] 78 69 66 65 6e 66 65 69 tab 0, row 1, @0x1f7a tl: 15 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 03 col 1: [ 8] 58 49 46 45 4e 46 45 49 end_of_block_dump 2012-05-01 05:09:29.287714 : kjbmbassert [0xaf.4] End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175
删除表数据
SQL> delete from t_xifenfei; 2 rows deleted. SQL> commit; Commit complete. SQL> alter system flush BUFFER_CACHE; System altered. SQL> alter system dump datafile 4 block 175; System altered.
dump文件内容
block_row_dump: tab 0, row 0, @0x1f89 tl: 2 fb: --HDFL-- lb: 0x2 tab 0, row 1, @0x1f7a tl: 2 fb: --HDFL-- lb: 0x2 end_of_block_dump 2012-05-01 05:13:35.214357 : kjbmbassert [0xaf.4] End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175
通过对比这两次的dump文件发现
1.数据内容被删除,并不是真正删除,而是给其增加了一个标识位(fd:---D----) 2.fb:--H-FL--(head of row piece+first data piece+last data piece ) 其有8个选项每个选项的值分别对应bitmask即32+8+4=44 or 0x2c 3.如果一个row被delete了,那么row flag就会更新,bitmask里的deleted被设置为16. 此时row flag为：32+16+8+4 = 60 or 0x3c. 4.如果我们要找回来被删除的数据,只需要把3c改为2c即可
关闭数据库
SQL> select * from chf.t_xifenfei; no rows selected SQL> select name from v$datafile where file#=4; NAME ------------------------------------------------ /tmp/user01.dbf SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.
bbed修改数据
BBED> set filename '/tmp/user01.dbf'
FILENAME /tmp/user01.dbf
BBED> set block 175
BLOCK# 175
BBED> set blocksize 8192
BLOCKSIZE 8192
BBED> set mode edit
MODE Edit
BBED> map
File: /tmp/user01.dbf (0)
Block: 175 Dba:0x00000000
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @100
struct kdbt[1], 4 bytes @114
sb2 kdbr[2] @118
ub1 freespace[8036] @122
ub1 rowdata[30] @8158
ub4 tailchk @8188
BBED> p *kdbr[0]
rowdata[15]
-----------
ub1 rowdata[15] @8173 0x3c
BBED> p *kdbr[1]
rowdata[0]
----------
ub1 rowdata[0] @8158 0x3c
BBED> m /x 2c offset 8158
File: /tmp/user01.dbf (0)
Block: 175 Offsets: 8158 to 8191 Dba:0x00000000
------------------------------------------------------------------------
2c630202 c1030858 4946454e 46454932 630202c1 02087869 66656e66 65690106
b47e
<32 bytes per line>
BBED> m /x 2c offset 8173
File: /tmp/user01.dbf (0)
Block: 175 Offsets: 8173 to 8191 Dba:0x00000000
------------------------------------------------------------------------
2c630202 c1020878 6966656e 66656901 06b47e
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 175:
current = 0x4d13, required = 0x4d13
启动数据库验证
SQL> startup
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1346140 bytes
Variable Size 411043236 bytes
Database Buffers 117440512 bytes
Redo Buffers 5832704 bytes
Database mounted.
Database opened.
SQL> select * from chf.t_xifenfei;
ID NAME
---------- ----------
1 xifenfei
2 XIFENFEI
bbed修改ASM中数据
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：bbed修改ASM中数据
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
本篇文章演示了如何从ASM中通过dd拷贝出某种表的记录,然后通过bbed修改相关记录,再拷贝到库中.说的简单点就是通过dd拷贝出最少的需要对象数据块,然后通过bbed绕过数据库级别对相关记录进行修改
模拟测试数据
--ORACLE数据库中执行
SQL> create tablespace xifenfei datafile '+xifenfei' size 30M autoextend on maxsize 10G;
Tablespace created.
SQL> create table t_xifenfei (id number,name varchar2(10)) tablespace xifenfei;
Table created.
SQL> insert into t_xifenfei values(1,'xifenfei');
1 row created.
SQL> insert into t_xifenfei values(2,'XFF');
1 row created.
SQL> SELECT * FROM T_XIFENFEI;
ID NAME
---------- ----------
1 xifenfei
2 XFF
SQL> commit;
Commit complete.
SQL> select EXTENT_ID, BLOCK_ID, BLOCKS, FILE_ID from dba_extents
2 where SEGMENT_NAME='T_XIFENFEI' and OWNER='SYS';
EXTENT_ID BLOCK_ID BLOCKS FILE_ID
---------- ---------- ---------- ----------
0 128 8 6
SQL> select name from v$datafile where file#=6;
NAME
----------------------------------------------------
+XIFENFEI/xff/datafile/xifenfei.268.781905429
SQL> select GROUP_NUMBER from V$ASM_DISKGROUP where NAME like '%XIFENFEI%';
GROUP_NUMBER
------------
2
在ASM用户中查询相关数据
--ASM中执行
SQL> SELECT disk_kffxp, au_kffxp, xnum_kffxp
2 FROM x$kffxp
3 WHERE GROUP_KFFXP=2
4 AND NUMBER_KFFXP=268;
DISK_KFFXP AU_KFFXP XNUM_KFFXP
---------- ---------- ----------
0 681 0
1 1092 1
1 1093 2
0 682 3
1 1094 4
1 1095 5
0 683 6
1 1096 7
0 684 8
1 1097 9
1 1098 10
DISK_KFFXP AU_KFFXP XNUM_KFFXP
---------- ---------- ----------
0 685 11
1 1099 12
0 686 13
1 1100 14
1 1101 15
0 687 16
1 1102 17
1 1103 18
0 688 19
1 1104 20
0 689 21
DISK_KFFXP AU_KFFXP XNUM_KFFXP
---------- ---------- ----------
1 1105 22
1 1106 23
0 690 24
1 1107 25
0 691 26
1 1108 27
1 1109 28
0 692 29
1 1110 30
31 rows selected.
--数据文件6的AU分配情况
SQL> select 128*8/1024 from dual;
128*8/1024
----------
1
SQL> select 8*8/1024 from dual;
8*8/1024
----------
.0625
--可以得出该表T_XIFENFEI的数据分布在第二块AU中(DISK_KFFXP=1/AU_KFFXP=1092/XNUM_KFFXP=1)
SQL> select name, path from v$asm_disk where group_number=2
2 and disk_number=1;
NAME PATH
------------------------------ --------------------------
XIFENFEI_0001 /dev/oracleasm/disks/VOL4
找出对应磁盘或者分区
[grid@rac1 ~]$ /etc/init.d/oracleasm querydisk -d VOL4 Disk "VOL4" is a valid ASM disk on device [8,18] [grid@rac1 ~]$ cat /proc/partitions |grep "8 18" 8 18 3879697 sdb2
因为这里的block_id=128,刚好是下一个AU的起点,所以dd操作的起点是第二个AU(DISK_KFFXP=1/AU_KFFXP=1092),而终点是8*8=64K(第二个AU中offset 64KB)
执行dd导出表数据
of=/dev/sdb2 sb=1K skip=1092*1024=1118208 count=64 [root@rac1 ~]# dd if=/dev/sdb2 bs=1k count=64 skip=1118208|strings 64+0 records in 64+0 records out 65536 bytes (66 kB) copied, 0.000656471 seconds, 99.8 MB/s XFF, xifenfei [root@rac1 ~]# dd if=/dev/sdb2 bs=1k count=64 skip=1118208 of=/tmp/t_xifenfe.tab 64+0 records in 64+0 records out 65536 bytes (66 kB) copied, 0.00226337 seconds, 29.0 MB/s [root@rac1 ~]# chown oracle.oinstall /tmp/t_xifenfe.tab [root@rac1 ~]# ll /tmp/t_xifenfe.tab -rw-r--r-- 1 oracle oinstall 65536 Apr 29 21:54 /tmp/t_xifenfe.tab
bbed 修改数据内容
[oracle@rac1 ~]$ bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Sun Apr 29 22:43:56 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set filename '/tmp/t_xifenfe.tab'
FILENAME /tmp/t_xifenfe.tab
BBED> set block 4
BLOCK# 4
BBED> set mode edit
MODE Edit
BBED> set blocksize 8192
BLOCKSIZE 8192
BBED> map
File: /tmp/t_xifenfe.tab (0)
Block: 4 Dba:0x00000000
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @100
struct kdbt[1], 4 bytes @114
sb2 kdbr[2] @118
ub1 freespace[8041] @122
ub1 rowdata[25] @8163
ub4 tailchk @8188
BBED> p kdbr
sb2 kdbr[0] @118 8073
sb2 kdbr[1] @120 8063
BBED> find /c XFF
File: /tmp/t_xifenfe.tab (0)
Block: 4 Offsets: 8170 to 8191 Dba:0x00000000
------------------------------------------------------------------------
5846462c 010202c1 02087869 66656e66 65690106 ba33
<32 bytes per line>
BBED> dump /v
File: /tmp/t_xifenfe.tab (0)
Block: 4 Offsets: 8170 to 8191 Dba:0x00000000
-------------------------------------------------------
5846462c 010202c1 02087869 66656e66 l XFF,......xifenf
65690106 ba33 l ei...3
<16 bytes per line>
BBED> m /c xff
File: /tmp/t_xifenfe.tab (0)
Block: 4 Offsets: 8170 to 8191 Dba:0x00000000
------------------------------------------------------------------------
7866662c 010202c1 02087869 66656e66 65690106 ba33
<32 bytes per line>
BBED> dump /v
File: /tmp/t_xifenfe.tab (0)
Block: 4 Offsets: 8170 to 8191 Dba:0x00000000
-------------------------------------------------------
7866662c 010202c1 02087869 66656e66 l xff,......xifenf
65690106 ba33 l ei...3
<16 bytes per line>
BBED> find /c xifenfei
File: /tmp/t_xifenfe.tab (0)
Block: 4 Offsets: 8180 to 8191 Dba:0x00000000
------------------------------------------------------------------------
78696665 6e666569 0106ba33
<32 bytes per line>
BBED> dump /v
File: /tmp/t_xifenfe.tab (0)
Block: 4 Offsets: 8180 to 8191 Dba:0x00000000
-------------------------------------------------------
78696665 6e666569 0106ba33 l xifenfei...3
<16 bytes per line>
BBED> m /c XIFENFEI
File: /tmp/t_xifenfe.tab (0)
Block: 4 Offsets: 8180 to 8191 Dba:0x00000000
------------------------------------------------------------------------
58494645 4e464549 0106ba33
<32 bytes per line>
BBED> dump /v
File: /tmp/t_xifenfe.tab (0)
Block: 4 Offsets: 8180 to 8191 Dba:0x00000000
-------------------------------------------------------
58494645 4e464549 0106ba33 l XIFENFEI...3
<16 bytes per line>
BBED> sum
Check value for File 0, Block 4:
current = 0xd332, required = 0xf332
BBED> sum apply
Check value for File 0, Block 4:
current = 0xf332, required = 0xf332
BBED> set offset 8073
OFFSET 8073
BBED> dump /v
File: /tmp/t_xifenfe.tab (0)
Block: 4 Offsets: 8073 to 8191 Dba:0x00000000
-------------------------------------------------------
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00002c01 0202c103 l ..........,.....
03786666 2c010202 c1020858 4946454e l .xff,......XIFEN
46454901 06ba33 l FEI...3
<16 bytes per line>
BBED> exit
dd导入修改后数据验证
--会话1关闭数据库
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Apr 29 22:48:51 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--会话2导入bbed修改后数据
[root@rac1 ~]# dd of=/dev/sdb2 bs=1k count=64 seek=1118208 if=/tmp/t_xifenfe.tab
64+0 records in
64+0 records out
65536 bytes (66 kB) copied, 0.0014908 seconds, 44.0 MB/s
--会话1启动数据库库查询
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Apr 29 22:51:00 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1346140 bytes
Variable Size 411043236 bytes
Database Buffers 117440512 bytes
Redo Buffers 5832704 bytes
Database mounted.
Database opened.
SQL> select * from t_xifenfei;
ID NAME
---------- ----------
1 XIFENFEI
2 xff
手工修复ASM DISK HEADER 异常
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
今天有网友对asm中的磁盘做了fdisk操作,导致asm disk异常,通过手工修复ASM DISK HEADER 解决该问题,这里通过实验重现,提醒大家操作asm中的硬盘分区需要慎重,平时对ASM DISK HEADER 做好备份
初始化信息
SQL> select * from v$version;
BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> show parameter instance_name;
NAME TYPE VALUE
------------------------------------ ---------- ----------------
instance_name string +ASM1
SQL> select group_number,DISK_NUMBER,PATH,HEADER_STATUS from v$asm_disk ;
GROUP_NUMBER DISK_NUMBER PATH HEADER_STATUS
------------ ----------- ------------------------------ ------------------------
1 1 /dev/oracleasm/disks/VOL2 MEMBER
1 0 /dev/oracleasm/disks/VOL1 MEMBER
2 1 /dev/oracleasm/disks/VOL4 MEMBER
2 0 /dev/oracleasm/disks/VOL3 MEMBER
[grid@rac1 ~]$ /etc/init.d/oracleasm querydisk -d VOL3
Disk "VOL3" is a valid ASM disk on device [8,17]
[grid@rac1 ~]$ ll /dev |grep 8,|grep 17
brw-r----- 1 root disk 8, 17 Apr 17 11:37 sdb1
[grid@rac1 ~]$ /etc/init.d/oracleasm querydisk -d VOL4
Disk "VOL4" is a valid ASM disk on device [8,18]
[grid@rac1 ~]$ ll /dev |grep 8,|grep 18
brw-r----- 1 root disk 8, 18 Apr 17 11:37 sdb2
备份ASM DISK HEADER
[root@rac1 backup_asmheader]# dd if=/dev/sdb1 of=vol3header.dd bs=4096 count=1 1+0 records in 1+0 records out 4096 bytes (4.1 kB) copied, 0.000143581 seconds, 28.5 MB/s [root@rac1 backup_asmheader]# dd if=/dev/sdb2 of=vol4header.dd bs=4096 count=1 1+0 records in 1+0 records out 4096 bytes (4.1 kB) copied, 0.000147727 seconds, 27.7 MB/s
破坏ASM DISK HEADER
[root@rac1 ~]# dd if=/dev/zero of=/dev/sdb1 bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 4.4421e-05 seconds, 92.2 MB/s
[grid@rac1 ~]$ kfed read /dev/oracleasm/disks/VOL3
kfbh.endian: 0 ; 0x000: 0x00
kfbh.hard: 0 ; 0x001: 0x00
kfbh.type: 0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt: 0 ; 0x003: 0x00
kfbh.block.blk: 0 ; 0x004: blk=0
kfbh.block.obj: 0 ; 0x008: file=0
kfbh.check: 0 ; 0x00c: 0x00000000
kfbh.fcn.base: 0 ; 0x010: 0x00000000
kfbh.fcn.wrap: 0 ; 0x014: 0x00000000
kfbh.spare1: 0 ; 0x018: 0x00000000
kfbh.spare2: 0 ; 0x01c: 0x00000000
B4C83200 00000000 00000000 00000000 00000000 [................]
Repeat 255 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]
SQL> select group_number,DISK_NUMBER,PATH,HEADER_STATUS from v$asm_disk ;
GROUP_NUMBER DISK_NUMBER PATH HEADER_STATUS
------------ ----------- ------------------------------ ------------------------
1 1 /dev/oracleasm/disks/VOL2 MEMBER
1 0 /dev/oracleasm/disks/VOL1 MEMBER
2 1 /dev/oracleasm/disks/VOL4 MEMBER
2 0 /dev/oracleasm/disks/VOL3 CANDIDATE
remount diskgroup
SQL> alter diskgroup xifenfei dismount; Diskgroup altered. SQL> alter diskgroup xifenfei mount; alter diskgroup xifenfei mount * ERROR at line 1: ORA-15032: not all alterations performed ORA-15017: diskgroup "XIFENFEI" cannot be mounted ORA-15063: ASM discovered an insufficient number of disks for diskgroup "XIFENFEI"
查看同一DISKGROUP中其他磁盘kfed
[grid@rac1 ~]$ kfed read /dev/oracleasm/disks/VOL4 kfbh.endian: 1 ; 0x000: 0x01 kfbh.hard: 130 ; 0x001: 0x82 kfbh.type: 1 ; 0x002: KFBTYP_DISKHEAD kfbh.datfmt: 1 ; 0x003: 0x01 kfbh.block.blk: 0 ; 0x004: blk=0 kfbh.block.obj: 2147483649 ; 0x008: disk=1 kfbh.check: 349717291 ; 0x00c: 0x14d8432b kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 kfdhdb.driver.provstr: ORCLDISKVOL4 ; 0x000: length=12 kfdhdb.driver.reserved[0]: 877416278 ; 0x008: 0x344c4f56 kfdhdb.driver.reserved[1]: 0 ; 0x00c: 0x00000000 kfdhdb.driver.reserved[2]: 0 ; 0x010: 0x00000000 kfdhdb.driver.reserved[3]: 0 ; 0x014: 0x00000000 kfdhdb.driver.reserved[4]: 0 ; 0x018: 0x00000000 kfdhdb.driver.reserved[5]: 0 ; 0x01c: 0x00000000 kfdhdb.compat: 186646528 ; 0x020: 0x0b200000 kfdhdb.dsknum: 1 ; 0x024: 0x0001 kfdhdb.grptyp: 1 ; 0x026: KFDGTP_EXTERNAL kfdhdb.hdrsts: 3 ; 0x027: KFDHDR_MEMBER kfdhdb.dskname: XIFENFEI_0001 ; 0x028: length=13 kfdhdb.grpname: XIFENFEI ; 0x048: length=8 kfdhdb.fgname: XIFENFEI_0001 ; 0x068: length=13 kfdhdb.capname: ; 0x088: length=0 kfdhdb.crestmp.hi: 32967790 ; 0x0a8: HOUR=0xe DAYS=0x3 MNTH=0x3 YEAR=0x7dc kfdhdb.crestmp.lo: 2015933440 ; 0x0ac: USEC=0x0 MSEC=0x22d SECS=0x2 MINS=0x1e kfdhdb.mntstmp.hi: 32969260 ; 0x0b0: HOUR=0xc DAYS=0x11 MNTH=0x4 YEAR=0x7dc kfdhdb.mntstmp.lo: 3109835776 ; 0x0b4: USEC=0x0 MSEC=0x315 SECS=0x15 MINS=0x2e kfdhdb.secsize: 512 ; 0x0b8: 0x0200 kfdhdb.blksize: 4096 ; 0x0ba: 0x1000 kfdhdb.ausize: 1048576 ; 0x0bc: 0x00100000 kfdhdb.mfact: 113792 ; 0x0c0: 0x0001bc80 kfdhdb.dsksize: 3788 ; 0x0c4: 0x00000ecc kfdhdb.pmcnt: 2 ; 0x0c8: 0x00000002 kfdhdb.fstlocn: 1 ; 0x0cc: 0x00000001 kfdhdb.altlocn: 2 ; 0x0d0: 0x00000002 kfdhdb.f1b1locn: 0 ; 0x0d4: 0x00000000 kfdhdb.redomirrors[0]: 0 ; 0x0d8: 0x0000 kfdhdb.redomirrors[1]: 0 ; 0x0da: 0x0000 kfdhdb.redomirrors[2]: 0 ; 0x0dc: 0x0000 kfdhdb.redomirrors[3]: 0 ; 0x0de: 0x0000 kfdhdb.dbcompat: 168820736 ; 0x0e0: 0x0a100000 kfdhdb.grpstmp.hi: 32967790 ; 0x0e4: HOUR=0xe DAYS=0x3 MNTH=0x3 YEAR=0x7dc kfdhdb.grpstmp.lo: 2015746048 ; 0x0e8: USEC=0x0 MSEC=0x176 SECS=0x2 MINS=0x1e kfdhdb.vfstart: 0 ; 0x0ec: 0x00000000 kfdhdb.vfend: 0 ; 0x0f0: 0x00000000 kfdhdb.spfile: 0 ; 0x0f4: 0x00000000 kfdhdb.spfflg: 0 ; 0x0f8: 0x00000000 kfdhdb.ub4spare[0]: 0 ; 0x0fc: 0x00000000 kfdhdb.ub4spare[1]: 0 ; 0x100: 0x00000000 kfdhdb.ub4spare[2]: 0 ; 0x104: 0x00000000 kfdhdb.ub4spare[3]: 0 ; 0x108: 0x00000000 kfdhdb.ub4spare[4]: 0 ; 0x10c: 0x00000000 kfdhdb.ub4spare[5]: 0 ; 0x110: 0x00000000 kfdhdb.ub4spare[6]: 0 ; 0x114: 0x00000000 kfdhdb.ub4spare[7]: 0 ; 0x118: 0x00000000 kfdhdb.ub4spare[8]: 0 ; 0x11c: 0x00000000 kfdhdb.ub4spare[9]: 0 ; 0x120: 0x00000000 kfdhdb.ub4spare[10]: 0 ; 0x124: 0x00000000 kfdhdb.ub4spare[11]: 0 ; 0x128: 0x00000000 kfdhdb.ub4spare[12]: 0 ; 0x12c: 0x00000000 kfdhdb.ub4spare[13]: 0 ; 0x130: 0x00000000 kfdhdb.ub4spare[14]: 0 ; 0x134: 0x00000000 kfdhdb.ub4spare[15]: 0 ; 0x138: 0x00000000 kfdhdb.ub4spare[16]: 0 ; 0x13c: 0x00000000 kfdhdb.ub4spare[17]: 0 ; 0x140: 0x00000000 kfdhdb.ub4spare[18]: 0 ; 0x144: 0x00000000 kfdhdb.ub4spare[19]: 0 ; 0x148: 0x00000000 kfdhdb.ub4spare[20]: 0 ; 0x14c: 0x00000000 kfdhdb.ub4spare[21]: 0 ; 0x150: 0x00000000 kfdhdb.ub4spare[22]: 0 ; 0x154: 0x00000000 kfdhdb.ub4spare[23]: 0 ; 0x158: 0x00000000 kfdhdb.ub4spare[24]: 0 ; 0x15c: 0x00000000 kfdhdb.ub4spare[25]: 0 ; 0x160: 0x00000000 kfdhdb.ub4spare[26]: 0 ; 0x164: 0x00000000 kfdhdb.ub4spare[27]: 0 ; 0x168: 0x00000000 kfdhdb.ub4spare[28]: 0 ; 0x16c: 0x00000000 kfdhdb.ub4spare[29]: 0 ; 0x170: 0x00000000 kfdhdb.ub4spare[30]: 0 ; 0x174: 0x00000000 kfdhdb.ub4spare[31]: 0 ; 0x178: 0x00000000 kfdhdb.ub4spare[32]: 0 ; 0x17c: 0x00000000 kfdhdb.ub4spare[33]: 0 ; 0x180: 0x00000000 kfdhdb.ub4spare[34]: 0 ; 0x184: 0x00000000 kfdhdb.ub4spare[35]: 0 ; 0x188: 0x00000000 kfdhdb.ub4spare[36]: 0 ; 0x18c: 0x00000000 kfdhdb.ub4spare[37]: 0 ; 0x190: 0x00000000 kfdhdb.ub4spare[38]: 0 ; 0x194: 0x00000000 kfdhdb.ub4spare[39]: 0 ; 0x198: 0x00000000 kfdhdb.ub4spare[40]: 0 ; 0x19c: 0x00000000 kfdhdb.ub4spare[41]: 0 ; 0x1a0: 0x00000000 kfdhdb.ub4spare[42]: 0 ; 0x1a4: 0x00000000 kfdhdb.ub4spare[43]: 0 ; 0x1a8: 0x00000000 kfdhdb.ub4spare[44]: 0 ; 0x1ac: 0x00000000 kfdhdb.ub4spare[45]: 0 ; 0x1b0: 0x00000000 kfdhdb.ub4spare[46]: 0 ; 0x1b4: 0x00000000 kfdhdb.ub4spare[47]: 0 ; 0x1b8: 0x00000000 kfdhdb.ub4spare[48]: 0 ; 0x1bc: 0x00000000 kfdhdb.ub4spare[49]: 0 ; 0x1c0: 0x00000000 kfdhdb.ub4spare[50]: 0 ; 0x1c4: 0x00000000 kfdhdb.ub4spare[51]: 0 ; 0x1c8: 0x00000000 kfdhdb.ub4spare[52]: 0 ; 0x1cc: 0x00000000 kfdhdb.ub4spare[53]: 0 ; 0x1d0: 0x00000000 kfdhdb.acdb.aba.seq: 0 ; 0x1d4: 0x00000000 kfdhdb.acdb.aba.blk: 0 ; 0x1d8: 0x00000000 kfdhdb.acdb.ents: 0 ; 0x1dc: 0x0000 kfdhdb.acdb.ub2spare: 0 ; 0x1de: 0x0000
通过VOL4 kfed修改出VOL3 kfed
[grid@rac1 ~]$ cat vol3.txt kfbh.endian: 1 ; 0x000: 0x01 kfbh.hard: 130 ; 0x001: 0x82 kfbh.type: 1 ; 0x002: KFBTYP_DISKHEAD kfbh.datfmt: 1 ; 0x003: 0x01 kfbh.block.blk: 0 ; 0x004: blk=0 *kfbh.block.obj: 2147483648 ; 0x008: disk=0 kfbh.check: 332940500 ; 0x00c: 0x13d844d4 kfbh.fcn.base: 0 ; 0x010: 0x00000000 kfbh.fcn.wrap: 0 ; 0x014: 0x00000000 kfbh.spare1: 0 ; 0x018: 0x00000000 kfbh.spare2: 0 ; 0x01c: 0x00000000 *kfdhdb.driver.provstr: ORCLDISKVOL3 ; 0x000: length=12 *kfdhdb.driver.reserved[0]: 860639062 ; 0x008: 0x334c4f56 kfdhdb.driver.reserved[1]: 0 ; 0x00c: 0x00000000 kfdhdb.driver.reserved[2]: 0 ; 0x010: 0x00000000 kfdhdb.driver.reserved[3]: 0 ; 0x014: 0x00000000 kfdhdb.driver.reserved[4]: 0 ; 0x018: 0x00000000 kfdhdb.driver.reserved[5]: 0 ; 0x01c: 0x00000000 kfdhdb.compat: 186646528 ; 0x020: 0x0b200000 *kfdhdb.dsknum: 0 ; 0x024: 0x0000 kfdhdb.grptyp: 1 ; 0x026: KFDGTP_EXTERNAL kfdhdb.hdrsts: 3 ; 0x027: KFDHDR_MEMBER *kfdhdb.dskname: XIFENFEI_0000 ; 0x028: length=13 kfdhdb.grpname: XIFENFEI ; 0x048: length=8 *kfdhdb.fgname: XIFENFEI_0000 ; 0x068: length=13 kfdhdb.capname: ; 0x088: length=0 kfdhdb.crestmp.hi: 32967790 ; 0x0a8: HOUR=0xe DAYS=0x3 MNTH=0x3 YEAR=0x7dc kfdhdb.crestmp.lo: 2015933440 ; 0x0ac: USEC=0x0 MSEC=0x22d SECS=0x2 MINS=0x1e kfdhdb.mntstmp.hi: 32969260 ; 0x0b0: HOUR=0xc DAYS=0x11 MNTH=0x4 YEAR=0x7dc kfdhdb.mntstmp.lo: 3109835776 ; 0x0b4: USEC=0x0 MSEC=0x315 SECS=0x15 MINS=0x2e kfdhdb.secsize: 512 ; 0x0b8: 0x0200 kfdhdb.blksize: 4096 ; 0x0ba: 0x1000 kfdhdb.ausize: 1048576 ; 0x0bc: 0x00100000 kfdhdb.mfact: 113792 ; 0x0c0: 0x0001bc80 *kfdhdb.dsksize: 2353 ; 0x0c4: 0x00000931 kfdhdb.pmcnt: 2 ; 0x0c8: 0x00000002 kfdhdb.fstlocn: 1 ; 0x0cc: 0x00000001 kfdhdb.altlocn: 2 ; 0x0d0: 0x00000002 *kfdhdb.f1b1locn: 2 ; 0x0d4: 0x00000002 kfdhdb.redomirrors[0]: 0 ; 0x0d8: 0x0000 kfdhdb.redomirrors[1]: 0 ; 0x0da: 0x0000 kfdhdb.redomirrors[2]: 0 ; 0x0dc: 0x0000 kfdhdb.redomirrors[3]: 0 ; 0x0de: 0x0000 kfdhdb.dbcompat: 168820736 ; 0x0e0: 0x0a100000 kfdhdb.grpstmp.hi: 32967790 ; 0x0e4: HOUR=0xe DAYS=0x3 MNTH=0x3 YEAR=0x7dc kfdhdb.grpstmp.lo: 2015746048 ; 0x0e8: USEC=0x0 MSEC=0x176 SECS=0x2 MINS=0x1e kfdhdb.vfstart: 0 ; 0x0ec: 0x00000000
后续处理
--导入kfed [grid@rac1 ~]$ kfed merge /dev/oracleasm/disks/VOL3 text=vol3.txt --MOUNT diskgroup SQL> alter diskgroup xifenfei mount; Diskgroup altered.
如果您遇到此类情况,无法解决请联系我们，提供专业ORACLE数据库恢复技术支持
Phone:17813235971 Q Q:107644445
E-Mail:dba@xifenfei.com
bbed解决ORA-01190
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
当我们使用resetlogs方式打开数据库后，发现有数据文件处于offline状态，这个时候很可能就是悲剧降临的时候，很有可能这个文件文件在resetlogs之前就处于offline状态，然后你resetlogs之后，这个文件使用常规方法很难再online，会出现ORA-01190或者ORA-01189之类的错误。
一、模拟offline文件然后resetlogs操作
1.设置datafile 5数据文件offline
2.rman备份数据库
3.关闭原数据库，删除数据文件/当前日志和部分归档日志
4.执行不完全恢复，resetlogs打开数据库(如下面操作)
[oracle@xifenfei ora11g]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 15 07:36:59 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> recover database until cancel;
ORA-00279: change 868870 generated at 03/15/2012 03:32:11 needed for thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/archivelog/ora11g/1_29_777766629.dbf
ORA-00280: change 868870 for thread 1 is in sequence #29
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;
FILE# ONLINE_STATUS TO_CHAR(CHANGE#,'999999999
---------- -------------- --------------------------
5 OFFLINE 868810
SQL> alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01190: control file or data file 5 is from before the last RESETLOGS
ORA-01110: data file 5: '/u01/oracle/oradata/ora11g/xifenfei01.dbf'
SQL> select file#,to_char(checkpoint_change#,'999999999999'),
2 to_char(last_change#,'999999999999') from v$datafile;
FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(LAST_CHANGE#,'9999
---------- -------------------------- --------------------------
1 868874
2 868874
3 868874
4 868874
5 868810 868874
--可以看到offline的数据文件，没有因为resetlogs操作而改变
--CHECKPOINT_CHANGE#和RESETLOGS_CHANGE#信息
SQL> select file#,to_char(checkpoint_change#,'999999999999'),
2 to_char(RESETLOGS_CHANGE#,'999999999999')
3 from v$datafile_header;
FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(RESETLOGS_CHANGE#,
---------- -------------------------- --------------------------
1 868874 868871
2 868874 868871
3 868874 868871
4 868874 868871
5 868810 787897
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
二、bbed修改相关项
下面两项与resetlogs相关
kcvfhrlc表示 reset logs count
kcvfhrls表示 resetlogs scn
下面四项与数据库文件scn相关
kscnbas (at offset 140) – SCN of last change to the datafile.
kcvcptim (at offset 148) - Time of the last change to the datafile.
kcvfhcpc (at offset 176) – Checkpoint count.
kcvfhccc (at offset 184) – Unknown, but is always 1 less than thecheckpoint point count.
BBED> set filename '/u01/oracle/oradata/ora11g/system01.dbf'
FILENAME /u01/oracle/oradata/ora11g/system01.dbf
BBED> p kcvfhrlc
ub4 kcvfhrlc @112 0x2e5eed37
BBED> p kcvfhrls
struct kcvfhrls, 8 bytes @116
ub4 kscnbas @116 0x000d4207
ub2 kscnwrp @120 0x0000
BBED> p kcvcpscn
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x000d4495
ub2 kscnwrp @488 0x0000
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000078
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000077
BBED> set filename '/u01/oracle/oradata/ora11g/xifenfei01.dbf'
FILENAME /u01/oracle/oradata/ora11g/xifenfei01.dbf
BBED> p kcvfhrlc
ub4 kcvfhrlc @112 0x2e5bc6e5
BBED> set mode edit
MODE Edit
BBED> m /x 37ed5e2e
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
BBED> p kcvfhrlc
ub4 kcvfhrlc @112 0x2e5eed37
BBED> p kcvfhrls
struct kcvfhrls, 8 bytes @116
ub4 kscnbas @116 0x000c05b9
ub2 kscnwrp @120 0x0000
BBED> m /x 07420d00
BBED> p kcvfhrls
struct kcvfhrls, 8 bytes @116
ub4 kscnbas @116 0x000d4207
ub2 kscnwrp @120 0x0000
BBED> p kcvcpscn
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x000d41ca
ub2 kscnwrp @488 0x0000
BBED> set offset 484
OFFSET 484
BBED> m /x 95440d00
BBED-00209: invalid number (95440d00)
BBED> m /x 9544
BBED> set offset +2
OFFSET 486
BBED> m /x 0d00
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000003
BBED> m /x 78000000
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000078
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000002
BBED> m /x 77000000
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000077
BBED> sum
Check value for File 0, Block 1:
current = 0xe079, required = 0x5940
BBED> sum apply
Check value for File 0, Block 1:
current = 0x5940, required = 0x5940
三、数据文件online
[oracle@xifenfei ora11g]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 15 07:48:48 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 368263168 bytes
Fixed Size 1345016 bytes
Variable Size 301992456 bytes
Database Buffers 58720256 bytes
Redo Buffers 6205440 bytes
Database mounted.
Database opened.
SQL> select file#,to_char(checkpoint_change#,'999999999999'),
2 to_char(RESETLOGS_CHANGE#,'999999999999')
3 from v$datafile_header;
FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(RESETLOGS_CHANGE#,
---------- -------------------------- --------------------------
1 869528 868871
2 869528 868871
3 869528 868871
4 869528 868871
5 869525 868871
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.
找回ASM中数据文件
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：找回ASM中数据文件
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
很多时候由于asm不能正常启动，导致数据丢失。下面提供两种方法找回asm中的数据文件
一.使用AMDU工具
AMDU是Oracle 11g里自带的一个免费的工具，用于分析ASM磁盘组的元数据以及从不能mount的磁盘组中往外抽取数据文件“NOTE:553639.1 Placeholder for AMDU binaries and using with ASM 10g”明确指出：AMDU也可用于10g，并提供了可用于10g的AMDU的各个操作系统的版本供大家下载。
1.设置LD_LIBRARY_PATH
rac1-> export LD_LIBRARY_PATH=/tmp/amdu
2.查看asm磁盘信息
rac1-> ./amdu -diskstring '/dev/raw/raw*'
amdu_2012_03_05_03_17_19/
rac1-> cd amdu_2012_03_05_03_17_19/
rac1-> ls
report.txt
rac1-> more report.txt
-*-amdu-*-
******************************* AMDU Settings ********************************
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: rac1
Release: 2.6.9-89.0.0.0.1.ELhugemem
Version: #1 SMP Tue May 19 04:38:38 EDT 2009
Machine: i686
amdu run: 05-MAR-12 03:17:19
Endianess: 1
--------------------------------- Operations ---------------------------------
------------------------------- Disk Selection -------------------------------
-diskstring '/dev/raw/raw*'
------------------------------ Reading Control -------------------------------
------------------------------- Output Control -------------------------------
********************************* DISCOVERY **********************************
----------------------------- DISK REPORT N0001 ------------------------------
Disk Path: /dev/raw/raw23
Unique Disk ID:
Disk Label:
Physical Sector Size: 512 bytes
Disk Size: 156 megabytes
** NOT A VALID ASM DISK HEADER. BAD VALUE IN FIELD blksize_kfdhdb **
----------------------------- DISK REPORT N0002 ------------------------------
Disk Path: /dev/raw/raw11
Unique Disk ID:
Disk Label:
Physical Sector Size: 512 bytes
Disk Size: 156 megabytes
** NOT A VALID ASM DISK HEADER. BAD VALUE IN FIELD blksize_kfdhdb **
----------------------------- DISK REPORT N0003 ------------------------------
Disk Path: /dev/raw/raw32
Unique Disk ID:
Disk Label:
Physical Sector Size: 512 bytes
Disk Size: 156 megabytes
** NOT A VALID ASM DISK HEADER. BAD VALUE IN FIELD blksize_kfdhdb **
----------------------------- DISK REPORT N0004 ------------------------------
Disk Path: /dev/raw/raw31
Unique Disk ID:
Disk Label:
Physical Sector Size: 512 bytes
Disk Size: 156 megabytes
** NOT A VALID ASM DISK HEADER. BAD VALUE IN FIELD blksize_kfdhdb **
----------------------------- DISK REPORT N0005 ------------------------------
Disk Path: /dev/raw/raw12
Unique Disk ID:
Disk Label:
Physical Sector Size: 512 bytes
Disk Size: 156 megabytes
** NOT A VALID ASM DISK HEADER. BAD VALUE IN FIELD blksize_kfdhdb **
----------------------------- DISK REPORT N0006 ------------------------------
Disk Path: /dev/raw/raw2
Unique Disk ID:
Disk Label:
Physical Sector Size: 512 bytes
Disk Size: 1976 megabytes
Group Name: DATA
Disk Name: DATA_0001
Failure Group Name: DATA_0001
Disk Number: 1
Header Status: 3
Disk Creation Time: 2012/03/01 22:32:39.289000
Last Mount Time: 2012/03/05 02:10:02.771000
Compatibility Version: 0x0a100000
Disk Sector Size: 512 bytes
Disk size in AUs: 1976 AUs
Group Redundancy: 1
Metadata Block Size: 4096 bytes
AU Size: 1048576 bytes
Stride: 113792 AUs
Group Creation Time: 2012/03/01 22:32:39.221000
File 1 Block 1 location: AU 0
----------------------------- DISK REPORT N0007 ------------------------------
Disk Path: /dev/raw/raw1
Unique Disk ID:
Disk Label:
Physical Sector Size: 512 bytes
Disk Size: 3137 megabytes
Group Name: DATA
Disk Name: DATA_0000
Failure Group Name: DATA_0000
Disk Number: 0
Header Status: 3
Disk Creation Time: 2012/03/01 22:32:39.289000
Last Mount Time: 2012/03/05 02:10:02.771000
Compatibility Version: 0x0a100000
Disk Sector Size: 512 bytes
Disk size in AUs: 3137 AUs
Group Redundancy: 1
Metadata Block Size: 4096 bytes
AU Size: 1048576 bytes
Stride: 113792 AUs
Group Creation Time: 2012/03/01 22:32:39.221000
File 1 Block 1 location: AU 2
******************************* END OF REPORT ********************************
从这里可以得到信息如下：
1)只有/dev/raw/raw1和raw2是有效的asm磁盘
2)磁盘组只有DATA
3.找出数据文件信息
rac1-> ../amdu -diskstring '/dev/raw/raw*' -dump 'DATA' amdu_2012_03_05_03_19_38/ rac1-> cd amdu_2012_03_05_03_19_38/ rac1-> ls DATA_0001.img DATA.map report.txt rac1-> strings DATA_0001.img |grep DATAFILE DATAFILE DATAFILE DATAFILE DEVDB/DATAFILE/SYSTEM.256.776817753 DEVDB/DATAFILE/SYSAUX.257.776817753 DEVDB/DATAFILE/UNDOTBS1.258.776817753 DEVDB/DATAFILE/USERS.259.776817753 DEVDB/DATAFILE/UNDOTBS2.267.776817909
从这里可以得出数据文件的file号(第一位数字)
4.恢复数据文件
rac1-> ../../amdu -diskstring '/dev/raw/raw*' -extract 'DATA.259' amdu_2012_03_05_03_27_21/ rac1-> cd amdu_2012_03_05_03_27_21/ rac1-> ls -l total 5148 -rw-r--r-- 1 oracle oinstall 5251072 Mar 5 03:27 DATA_259.f -rw-r--r-- 1 oracle oinstall 6468 Mar 5 03:27 report.txt rac1-> dbv file=DATA_259.f DBVERIFY: Release 10.2.0.1.0 - Production on Mon Mar 5 03:28:00 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. DBVERIFY - Verification starting : FILE = DATA_259.f DBVERIFY - Verification complete Total Pages Examined : 640 Total Pages Processed (Data) : 15 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 2 Total Pages Failing (Index): 0 Total Pages Processed (Other): 26 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 597 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Highest block SCN : 442362 (0.442362)
二.使用aulasm工具
1.配置ASM使用磁盘
[root@rac1 oracle]# more disk /dev/sdb1 /dev/sdb2
2.使用aulasm
[root@rac1 oracle]# ./aulasm_linux32_trial.bin
AUL : AnySQL UnLoader(MyDUL) for Oracle ASM, release 2.0.0
(C) Copyright Lou Fangxin 2011-2012 (AnySQL.net), all rights reserved.
ASM> help
SET -- set options (BLOCK_SIZE, AU_SIZE, F1B1_DISK, F1B1_AU)
OPEN -- open data file
ADD -- add disk with customized disk num (disk, path)
LIST -- list opened disks information (to)
LSAU -- list file of given AU (disk, au, count)
FILE -- list all file entries in ASM (file, [disk, au, block]).
ALIAS -- list all alias entries in ASM (file, [disk, au, block]).
EXTENT -- list or adjust variable extent (level, au, count).
FMAP -- list file extent (file, [disk, au, block], to)
DUMP -- dump file extent (file, [disk, au, block], offset, to)
COPY -- copy file to os (file, [disk, au, block], to)
QUIT/EXIT -- exit the program.
ASM> open disk
2012-03-05 03:32:48
2012-03-05 03:32:48
ASM> list
2012-03-05 03:32:49
Total Disks = 2, ausize=1048576, blksize=4096
disk size block disk name disk group disk path
===== ======== ===== ================ ============ ========================
0 3137 4096 DATA_0000 DATA /dev/sdb1
1 1976 4096 DATA_0001 DATA /dev/sdb2
f1b1disk = 0, f1b1au = 2, score=256, compat=0x0a100000
file=(1,0,2,1), disk=(2,0,2,2), alias=(6,0,2,6)
2012-03-05 03:32:49
ASM> alias
2012-03-05 03:33:15
file inc parent fstblk flag refer alias
======== ============ ====== ====== ==== ====== ==========================
-1 -1 0 0 4 1 DEVDB
-1 -1 0 1 4 2 DATAFILE
-1 -1 0 1 4 3 CONTROLFILE
-1 -1 0 1 4 4 ONLINELOG
-1 -1 0 1 4 5 TEMPFILE
-1 -1 0 1 4 6 PARAMETERFILE
-1 -1 0 1 4 7 ARCHIVELOG
256 776817753 1 2 18 -1 SYSTEM
257 776817753 1 2 18 -1 SYSAUX
258 776817753 1 2 18 -1 UNDOTBS1
259 776817753 1 2 18 -1 USERS
267 776817909 1 2 18 -1 UNDOTBS2
261 776817829 1 3 18 -1 Current
260 776817829 1 3 18 -1 Current
262 776817833 1 4 18 -1 group_1
263 776817835 1 4 18 -1 group_1
264 776817837 1 4 18 -1 group_2
265 776817841 1 4 18 -1 group_2
268 776818011 1 4 18 -1 group_3
269 776818013 1 4 18 -1 group_3
270 776818015 1 4 18 -1 group_4
271 776818017 1 4 18 -1 group_4
266 776817859 1 5 18 -1 TEMP
272 776818021 1 6 18 -1 spfile
-1 -1 1 7 4 8 2012_03_01
-1 -1 1 7 4 9 2012_03_03
-1 -1 1 7 4 10 2012_03_05
273 776818747 7 8 18 -1 thread_1_seq_2
274 776819571 7 8 18 -1 thread_1_seq_3
275 776819721 7 8 18 -1 thread_2_seq_1
276 776820331 7 8 18 -1 thread_1_seq_4
277 776992321 7 9 18 -1 thread_1_seq_5
278 776992321 7 9 18 -1 thread_2_seq_2
279 777089437 7 10 18 -1 thread_1_seq_6
2012-03-05 03:33:15
ASM> fmap file 259
2012-03-05 03:34:49
fid=259, disk=0, au=56, block=3
siz=5, ecnt=6, eeof=6, flags=17, type=12, alias=109,-1
extid disk auid cnt flg chk
========== ===== ========== ==== === ===
1 0 428 1 0 135
2 1 408 1 0 178
3 0 429 1 0 134
4 1 409 1 0 179
5 0 430 1 0 133
6 1 410 1 0 176
2012-03-05 03:34:49
ASM> copy file 259 to user_259
2012-03-05 03:35:01
2012-03-05 03:35:01
[root@rac1 oracle]# ll
total 6252
-rwxr-xr-x 1 root root 84619 Mar 6 2012 aulasm_linux32_trial.bin
-rw-r--r-- 1 root root 20 Mar 5 02:52 disk
-rw-r--r-- 1 root root 6291456 Mar 5 03:35 user_259
[root@rac1 oracle]# su - oracle
rac1-> dbv file=user_259
DBVERIFY: Release 10.2.0.1.0 - Production on Mon Mar 5 03:35:27 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = user_259
DBVERIFY - Verification complete
Total Pages Examined : 640
Total Pages Processed (Data) : 15
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 2
Total Pages Failing (Index): 0
Total Pages Processed (Other): 26
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 597
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 442362 (0.442362)
通过dbv验证，两个数据文件(文件大小有出入，可能在对待未格式化数据块上，两者处理有出入)可以基本上确定一致，证明两种方法都可以从异常的ASM中获取数据文件
三.总结与比较
1.AMDU完全免费，aulasm超过16块硬盘需要收费
2.AMDU操作相对于aulasm麻烦一点，但是整体还是可以接受
3.获取到asm中的数据文件后恢复数据内容或者open库，那都是容易的事情
如果因为磁盘头损坏太多或者其他原因,以上方法都不能实现抽取asm磁盘组中数据文件,参考:asm disk header 彻底损坏恢复
odu恢复truncate和drop表
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
使用odu恢复被drop表过程
http://www.xifenfei.com/wp-content/uploads/2012/02/odu_drop.pdf
使用odu恢复被truncate表过程
http://www.xifenfei.com/wp-content/uploads/2012/02/odu_truncate.pdf