oracle 10gr2 中 clob 字段原理浅析

最新推荐文章于 2023-03-31 12:53:03 发布

cmoaai42662

最新推荐文章于 2023-03-31 12:53:03 发布

阅读量165

点赞数

前段时间生产库的数据文件出了点问题，导致部分数据丢失，其中包括log segment，当时想通过常规手段将丢失的log数据清除掉，但由于文件offline了，所以没办法了。
以下是通过bbed在测试库上所作的更改，目的就是想研究下log字段原理以及非常规手段清除数据的办法，该测试仅限于测试库。
在9i/10g中连接生成bbed:
cd $ ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
以上生成的bbed可执行文件在$ORACLE_HOME/rdbms/lib目录，可以复制到其他位置或者其他同Oracle版本的机器上运行。也可通过以下命令将bbed生成到$ORACLE_HOME/bin目录
make -f ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed
在11g中生成bbed，需要先从10g中复制如下文件到相应目录，然后再执行上述连接命令:
$ORACLE_HOME/rdbms/lib/ssbbded.o
$ORACLE_HOME/rdbms/lib/sbbdpt.o
$ORACLE_HOME/rdbms/mesg/bbedus.msb
BBED密码为blockedit

以下是测试过程：
1、创建表空间
create tablespace ts_cui datafile '/dbdata/oradata/ora10/dbsm/ts_cui_01.dbf' size 1m;
alter tablespace ts_cui add datafile '/dbdata/oradata/ora10/dbsm/ts_cui_02.dbf' size 1m;
2、建表
CREATE TABLE scott.CUI
   ( C1 NUMBER,
C2 CLOB
   )
TABLESPACE users
LOB (C2) STORE AS (
TABLESPACE TS_CUI enable STORAGE IN ROW CHUNK 32768 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;

表建在users表空间上，log segment存储在ts_cui表空间上，使用系统建表是默认属性 enable storage in row

3\ 测试数据
然后随便插入一些数据：
insert into cui(c1,c2) values(3,'2222222222222222222222');
commit;
INSERT INTO scott.cui VALUES (6,LPAD ('X',97584,'X'));
   INSERT INTO scott.cui VALUES (7,empty_clob());
commit;
需要ts_cui下的两个文件都要存储上数据，这样便于对比数据；

可以通过查看以下sql，来核实是否数据已经存在于2个文件中：
select bytes,file_id,block_id,relative_fno,segment_name,segment_type,tablespace_name
from dba_extents t where tablespace_name='TS_CUI';

---option-------------
造好测试数据之后，可以先将文件中的数据dump出来，以便对照修改之后的dump文件；
方法： alter system dump datafile 4 block xxxx;
具体dump哪块，下边有写
------------------------------------------------------------------------------------------------------------

4、删除文件
SQL>
SQL> alter database datafile '/dbdata/oradata/ora10/dbsm/ts_cui_02.dbf' offline drop;

Database altered.

5、查询数据， select rownum,a.* from scott.cui a ; 会看到存在 value error的log列，如下

上图中的第二列等于 5 的那列此时应该为 value error，这是我用bbed改过之后的效果，之前的图没存，先说明下；

6、此时就可以用bbed来修改数据了
[ora10@sdw0 dbsm]$ bbed parfile=bbed.par
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Wed Jan 23 12:27:14 2013

Copyright (c) 1982, 2007, Oracle. All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED>

bbed.par 文件内容：
[root@sdw0 dbsm]# cat bbed.par
blocksize=32768
listfile=filelist.txt
mode=edit

filelist.txt 文件内容：
[root@sdw0 dbsm]# cat filelist.txt
4 /dbdata/oradata/ora10/dbsm/users01.dbf 98336768

说明：blocksize 建库块大小；98336768 users01.dbf 文件大小

BBED> info
File# Name                                                        Size(blks)
----- ----                                                        ----------
     4 /dbdata/oradata/ora10/dbsm/users01.dbf                            3001

BBED> help all
SET DBA [ dba | file#, block# ]
SET FILENAME 'filename'
SET FILE file#
SET BLOCK [+/-]block#
SET OFFSET [ [+/-]byte offset | symbol | *symbol ]
SET BLOCKSIZE bytes
SET LIST[FILE] 'filename'
SET WIDTH character_count
SET COUNT bytes_to_display
SET IBASE [ HEX | OCT | DEC ]
SET OBASE [ HEX | OCT | DEC ]
SET MODE [ BROWSE | EDIT ]
SET SPOOL [ Y | N ]
SHOW [ | ALL ]
INFO
MAP[/v] [ DBA | FILENAME | FILE | BLOCK ]
DUMP[/v] [ DBA | FILENAME | FILE | BLOCK | OFFSET | COUNT ]
PRINT[/x|d|u|o|c] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
EXAMINE[/Nuf] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
:
N - a number which specifies a repeat count.
u - a letter which specifies a unit size:
b - b1, ub1 (byte)
h - b2, ub2 (half-word)
w - b4, ub4(word)
r - Oracle table/index row
f - a letter which specifies a display format:
x - hexadecimal
d - decimal
u - unsigned decimal
o - octal
c - character (native)
n - Oracle number
t - Oracle date
i - Oracle rowid
FIND[/x|d|u|o|c] numeric/character string [ TOP | CURR ]
COPY [ DBA | FILE | FILENAME | BLOCK ] TO [ DBA | FILE | FILENAME | BLOCK ]
MODIFY[/x|d|u|o|c] numeric/character string
      [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
ASSIGN[/x|d|u|o] =
: [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
: [ value | ]
SUM [ DBA | FILE | FILENAME | BLOCK ] [ APPLY ]
PUSH [ DBA | FILE | FILENAME | BLOCK | OFFSET ]
POP [ALL]
REVERT [ DBA | FILE | FILENAME | BLOCK ]
UNDO
HELP [ | ALL ]
VERIFY [ DBA | FILE | FILENAME | BLOCK ]
CORRUPT [ DBA | FILE | FILENAME | BLOCK ]

以上是bbed支持的所有命令，具体如何使用不再说明，可以百度；

7、找到损坏数据对应的block
SELECT DBMS_ROWID.rowid_relative_fno(ROWID) FNO,DBMS_ROWID.rowid_block_number(ROWID) BLOCKID, DBMS_ROWID.rowid_row_number(ROWID) ROWNUMA   FROM scott.CUI;

8、通过bbed修改数据
修改之前的dump信息如下：
改之前：
tab 0, row 71, @0x16ea
tl: 47 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 06
col  1: [40]
00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 22 cb 0d 00 14 05 00 00
00 00 00 1f 40 00 00 00 00 00 02 06 80 00 09

BBED> dump /v offset 5995 count 1
File: /dbdata/oradata/ora10/dbsm/users01.dbf (4)
Block: 2798    Offsets: 5995 to 5995  Dba:0x01000aee
-------------------------------------------------------
05                                  l .

<16 bytes per line>

BBED> modify /x 09 offset 5995
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /dbdata/oradata/ora10/dbsm/users01.dbf (4)
Block: 2798             Offsets: 5995 to 5995           Dba:0x01000aee
------------------------------------------------------------------------
09

<32 bytes per line>

BBED> dump /v offset 6001 count 2
File: /dbdata/oradata/ora10/dbsm/users01.dbf (4)
Block: 2798    Offsets: 6001 to 6002  Dba:0x01000aee
-------------------------------------------------------
1f40                                l .@

<16 bytes per line>

BBED> modify /x 0004 offset 6001
File: /dbdata/oradata/ora10/dbsm/users01.dbf (4)
Block: 2798             Offsets: 6001 to 6002           Dba:0x01000aee
------------------------------------------------------------------------
0004

<32 bytes per line>

BBED> dump /v offset 6008 count 1
File: /dbdata/oradata/ora10/dbsm/users01.dbf (4)
Block: 2798    Offsets: 6008 to 6008  Dba:0x01000aee
-------------------------------------------------------
02                                  l .

<16 bytes per line>

BBED> modify /x 01 offset 6008
File: /dbdata/oradata/ora10/dbsm/users01.dbf (4)
Block: 2798             Offsets: 6008 to 6008           Dba:0x01000aee
------------------------------------------------------------------------
01

<32 bytes per line>

BBED>

BBED> modify /x 004300 offset 6009
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /dbdata/oradata/ora10/dbsm/users01.dbf (4)
Block: 2798             Offsets: 6009 to 6012           Dba:0x01000aee
------------------------------------------------------------------------
00430021
BBED> modify /x 49 offset 6012
File: /dbdata/oradata/ora10/dbsm/users01.dbf (4)
Block: 2798             Offsets: 6012 to 6012           Dba:0x01000aee
------------------------------------------------------------------------
49

<32 bytes per line>

BBED> dump /v offset 6009 count 4
File: /dbdata/oradata/ora10/dbsm/users01.dbf (4)
Block: 2798    Offsets: 6009 to 6012  Dba:0x01000aee
-------------------------------------------------------
00430049                            l .C.I

<16 bytes per line>

BBED> sum apply
Check value for File 4, Block 2798:
current = 0x3779, required = 0x3779

BBED> exit

改后：
tab 0, row 71, @0x16ea
tl: 47 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 06
col 1: [40]
00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 22 cb 0d 00 14 09 00 00
00 00 00 00 04 00 00 00 00 00 01 00 43 00 49

00 43 对应ascii C
00 49 对应ascii I

SQL> startup force
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2085360 bytes
Variable Size            1191185936 bytes
Database Buffers          922746880 bytes
Redo Buffers               31465472 bytes
Database mounted.
Database opened.
SQL>

9、重启数据库后，可以看到数据已经可以访问了，内容就是通过bbed修改的内容 CI

下面说明一下修改的目的，对照以下图片：

0054  lob head 的最大长度( Lob数据的结构，头部最大为８４字节，最小为３６字节)
0001　vsn
02 0c 80 00 flag
00 02 数据的单位长度为２个字节。
00 00 00 01 00 00 00 22 cb 0d   lob_id
00 14 body length
05 00 flags
00 00 00 00 chunk 数量，具体含义见下图
1f 40 lob length
00 00 00 00 00 02 version(01 表示数据在行内存放，02　表示数据存储在log段中)
其中 00 14 05 00 00 00 00 00 1f 40 00 00 00 00 00 02 也可以称为 inodeid
06 80 00 09 data/first chunk address/rdba(dba of lob chunks)
如果数据在行内存放，那么就是data内容，如果行外存放，取而代之的是rdba

06 80 00 09 转化为10机制： 109051913
   select dbms_utility.data_block_address_file(109051913) file#,dbms_utility.data_block_address_block(109051913) block# from dual;
1    26    9

可见这个log的数据存储在26文件block9上，如有兴趣可以dump出来，

以上是数据存储在行内和行外（很少数据量）的情况，以下是数据很多时的情况，见图示：