本篇文章演示了如何从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
