oracle数据库fliegroup14,dbms_diskgroup拷贝block/datafile

对于asm而言,如果我们要操作里面的数据文件,虽然从11.2开始有asmcmd的cp可能进行拷贝,但是如果想对数据文件中的某个block进行拷贝出来asm到文件系统(或者拷贝某个文件系统的block到asm中),还是比较麻烦的事情(请见:bbed修改ASM中数据)。其实oracle官方提供了dbms_diskgroup这个包,可以通过sqlplus直接操作asm里面的block/datafile,非常方便,这里简单列举几个例子:

dbms_diskgroup获取asm中文件属性

SQL> declare

2 v_filename varchar2(4000);

3 v_filetype number;

4 v_filesize number;

5 v_lbks number;

6 v_typename varchar2(4000);

7 begin

8 dbms_output.enable(5000);

9 v_filename := '&file_name';

10 dbms_diskgroup.getfileattr(v_filename,v_filetype,v_filesize,v_lbks);

11 select decode(v_filetype,1,'Control File',2,'Data File',3,'Online Log File',4,'Archive Log',5,'Trace File',6,'Temporary File',

12 7,'Not Used',8,'Not Used',9,'Backup Piece',10,'Incremental Backup Piece',11,'Archive Backup Piece',12,'Data File Copy',

13 13,'Spfile',14,'Disaster Recovery Configuration',15,'Storage Manager Disk',16,'Change Tracking File',17,'Flashback Log File',

14 18,'DataPump Dump File',19,'Cross Platform Converted File',20,'Autobackup',21,'Any OS file',22,'Block Dump File',

15 23,'CSS Voting File',24,'CRS') into v_typename from dual;

16 dbms_output.put_line('File: '||v_filename); dbms_output.new_line;

17 dbms_output.put_line('Type: '||v_filetype||' '||v_typename); dbms_output.new_line;

18 dbms_output.put_line('Size (Logical Block Size): '||v_filesize); dbms_output.new_line;

19 dbms_output.put_line('Logical Block Size: '||v_lbks); dbms_output.new_line;

20 end;

21 /

Enter value for file_name: +DATA/xifenfei/datafile/system.256.878224279

old 9: v_filename := '&file_name';

new 9: v_filename := '+DATA/xifenfei/datafile/system.256.878224279';

File: +DATA/xifenfei/datafile/system.256.878224279

Type: 12 Data File Copy

Size (Logical Block Size): 94720

Logical Block Size: 8192

PL/SQL procedure successfully completed.

创建测试表t_xifenfei

SQL> create table t_xifenfei tablespace users

2 as select 'www.xifenfei.com' xifenfei from dual;

Table created.

SQL> select rowid,xifenfei from t_xifenfei;

ROWID XIFENFEI

------------------ ----------------

AAAVU3AAEAAAACrAAA www.xifenfei.com

SQL> select

2 dbms_rowid.rowid_relative_fno(rowid) rel_fno,

3 dbms_rowid.rowid_block_number(rowid )block_no

4 from t_xifenfei;

REL_FNO BLOCK_NO

---------- ----------

4 171

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select name from v$datafile where file#=4;

NAME

--------------------------------------------------------------------------------

+DATA/xifenfei/datafile/users.259.878224279

dbms_diskgroup拷贝asm datafile 4 block 171

SQL> declare

2 v_AsmFilename varchar2(4000);

3 v_FsFilename varchar2(4000);

4 v_offstart number;

5 v_numblks number;

6 v_filetype number;

7 v_filesize number;

8 v_lbks number;

9 v_typename varchar2(4000);

10 v_pblksize number;

11 v_handle number;

12 begin

13 dbms_output.enable(500000);

14 v_AsmFilename := '&ASM_File_Name';

15 v_offstart := '&block_to_extract';

16 v_numblks := '&number_of_blocks_to_extract';

17 v_FsFilename := '&FileSystem_File_Name';

18 dbms_diskgroup.getfileattr(v_AsmFilename,v_filetype,v_filesize,v_lbks);

19 dbms_diskgroup.open(v_AsmFilename,'r',v_filetype,v_lbks,v_handle,v_pblksize,v_filesize);

20 dbms_diskgroup.close(v_handle);

21 select decode(v_filetype,1,'Control File',2,'Data File',3,'Online Log File',4,'Archive Log',5,'Trace File',6,'Temporary File',

22 7,'Not Used',8,'Not Used',9,'Backup Piece',10,'Incremental Backup Piece',11,'Archive Backup Piece',12,'Data File Copy',

23 13,'Spfile',14,'Disaster Recovery Configuration',15,'Storage Manager Disk',16,'Change Tracking File',17,'Flashback Log File',

24 18,'DataPump Dump File',19,'Cross Platform Converted File',20,'Autobackup',21,'Any OS file',22,'Block Dump File',

25 23,'CSS Voting File',24,'CRS') into v_typename from dual;

26 dbms_output.put_line('File: '||v_AsmFilename); dbms_output.new_line;

27 dbms_output.put_line('Type: '||v_filetype||' '||v_typename); dbms_output.new_line;

28 dbms_output.put_line('Size (in logical blocks): '||v_filesize); dbms_output.new_line;

29 dbms_output.put_line('Logical Block Size: '||v_lbks); dbms_output.new_line;

30 dbms_output.put_line('Physical Block Size: '||v_pblksize); dbms_output.new_line;

31 dbms_diskgroup.patchfile(v_AsmFilename,v_filetype,v_lbks,v_offstart,0,v_numblks,v_FsFilename,v_filetype,1,1);

32 end;

33 /

Enter value for asm_file_name: +DATA/xifenfei/datafile/users.259.878224279

old 14: v_AsmFilename := '&ASM_File_Name';

new 14: v_AsmFilename := '+DATA/xifenfei/datafile/users.259.878224279';

Enter value for block_to_extract: 171

old 15: v_offstart := '&block_to_extract';

new 15: v_offstart := '171';

Enter value for number_of_blocks_to_extract: 1

old 16: v_numblks := '&number_of_blocks_to_extract';

new 16: v_numblks := '1';

Enter value for filesystem_file_name: /tmp/xifenfei.dbf

old 17: v_FsFilename := '&FileSystem_File_Name';

new 17: v_FsFilename := '/tmp/xifenfei.dbf';

File: +DATA/xifenfei/datafile/users.259.878224279

Type: 12 Data File Copy

Size (in logical blocks): 640

Logical Block Size: 8192

Physical Block Size: 512

PL/SQL procedure successfully completed.

[grid@xifenfei ~]$ ls -l /tmp/xifenfei.dbf

-rw-r----- 1 grid oinstall 16384 Apr 28 15:55 /tmp/xifenfei.dbf

这里注意拷贝出来的block size 为8192,由于默认写了block 0信息，因此这里显示大小为2*block size=16384

bbed修改拷贝出来block内容

SQL> select dump('xifenfei',16) from dual;

DUMP('XIFENFEI',16)

-------------------------------------

Typ=96 Len=8: 78,69,66,65,6e,66,65,69

SQL> select dump('XIFENFEI',16) from dual;

DUMP('XIFENFEI',16)

-------------------------------------

Typ=96 Len=8: 58,49,46,45,4e,46,45,49

[oracle@xifenfei tmp]$ bbed filename='/tmp/xifenfei.dbf' blocksize=8192

Password:

BBED: Release 2.0.0.0.0 - Limited Production on Tue Apr 28 16:24:35 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

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

BBED> show

FILE# 0

BLOCK# 1

OFFSET 0

DBA 0x00000000 (0 0,1)

FILENAME /tmp/xifenfei.dbf

BIFILE bifile.bbd

LISTFILE

BLOCKSIZE 8192

MODE Browse

EDIT Unrecoverable

IBASE Dec

OBASE Dec

WIDTH 80

COUNT 512

LOGFILE log.bbd

SPOOL No

BBED> map

File: /tmp/xifenfei.dbf (0)

Block: 1 Dba:0x00000000

------------------------------------------------------------

KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes @0

struct ktbbh, 96 bytes @20

struct kdbh, 14 bytes @124

struct kdbt[1], 4 bytes @138

sb2 kdbr[1] @142

ub1 freespace[8024] @144

ub1 rowdata[20] @8168

ub4 tailchk @8188

BBED> p *kdbr[0]

rowdata[0]

----------

ub1 rowdata[0] @8168 0x2c

BBED> d /v offset 8168

File: /tmp/xifenfei.dbf (0)

Block: 1 Offsets: 8168 to 8191 Dba:0x00000000

-------------------------------------------------------

2c000110 7777772e 78696665 6e666569 l ,...www.xifenfei

2e636f6d 020624bc l .com..$.

<16 bytes per line>

BBED> r /x c

BBED-00200: invalid keyword (r)

BBED> x /rc

rowdata[0] @8168

----------

flag@8168: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8169: 0x00

cols@8170: 1

col 0[16] @8171: www.xifenfei.com

BBED> set mode edit

MODE Edit

BBED> set offset 8171

OFFSET 8171

BBED> set count 32

COUNT 32

BBED> d

File: /tmp/xifenfei.dbf (0)

Block: 1 Offsets: 8171 to 8191 Dba:0x00000000

------------------------------------------------------------------------

10777777 2e786966 656e6665 692e636f 6d020624 bc

<32 bytes per line>

BBED>

BBED> set offset +5

OFFSET 8176

BBED> d

File: /tmp/xifenfei.dbf (0)

Block: 1 Offsets: 8176 to 8191 Dba:0x00000000

------------------------------------------------------------------------

78696665 6e666569 2e636f6d 020624bc

<32 bytes per line>

BBED> m /x 58494645

File: /tmp/xifenfei.dbf (0)

Block: 1 Offsets: 8176 to 8191 Dba:0x00000000

------------------------------------------------------------------------

58494645 6e666569 2e636f6d 020624bc

<32 bytes per line>

BBED> set offset +4

OFFSET 8180

BBED> d

File: /tmp/xifenfei.dbf (0)

Block: 1 Offsets: 8180 to 8191 Dba:0x00000000

------------------------------------------------------------------------

6e666569 2e636f6d 020624bc

<32 bytes per line>

BBED> m /x 4e464549

File: /tmp/xifenfei.dbf (0)

Block: 1 Offsets: 8180 to 8191 Dba:0x00000000

------------------------------------------------------------------------

4e464549 2e636f6d 020624bc

<32 bytes per line>

BBED> d /v offset 8168

File: /tmp/xifenfei.dbf (0)

Block: 1 Offsets: 8168 to 8191 Dba:0x00000000

-------------------------------------------------------

2c000110 7777772e 58494645 4e464549 l ,...www.XIFENFEI

2e636f6d 020624bc l .com..$.

<16 bytes per line>

BBED> x /rc

rowdata[0] @8168

----------

flag@8168: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8169: 0x00

cols@8170: 1

col 0[16] @8171: www.XIFENFEI.com

BBED> sum apply

Check value for File 0, Block 1:

current = 0x3060, required = 0x3060

这里通过bbed把拷贝出来的datafile 4 block 171中的www.xifenfei.com修改为www.XIFENFEI.com

dbms_diskgroup拷贝os block to asm datafile 4 block 171

SQL> declare

2 v_FsFileName varchar2(4000);

3 v_AsmFileName varchar2(4000);

4 v_FsFileType number;

5 v_AsmFileType number;

6 v_offstart number;

7 v_filesize number;

8 v_lbks number;

9 v_typename varchar2(4000);

10 v_handle number;

11 error number;

12 txt varchar2(4000);

13 begin

14 dbms_output.enable(500000);

15 v_FsFileName := '&file_with_patched_block';

16 v_AsmFileName := '&file_to_patch_in_ASM';

17 v_offstart := '&block_to_patch';

18 dbms_diskgroup.getfileattr(v_AsmFileName,v_AsmFileType,v_filesize,v_lbks);

19 select decode(v_AsmFileType,1,'Control File',2,'Data File',3,'Online Log File',4,'Archive Log',5,'Trace File',6,'Temporary File',

20 7,'Not Used',8,'Not Used',9,'Backup Piece',10,'Incremental Backup Piece',11,'Archive Backup Piece',12,'Data File Copy',

21 13,'Spfile',14,'Disaster Recovery Configuration',15,'Storage Manager Disk',16,'Change Tracking File',

22 17,'Flashback Log File',

23 18,'DataPump Dump File',19,'Cross Platform Converted File',20,'Autobackup',21,'Any OS file',22,'Block Dump File',

24 23,'CSS Voting File',24,'CRS') into v_typename from dual;

25 dbms_output.put_line('File: '||v_AsmFileName); dbms_output.new_line;

26 dbms_output.put_line('Type: '||v_AsmFileType||' '||v_typename); dbms_output.new_line;

27 dbms_output.put_line('Size: '||v_filesize); dbms_output.new_line;

28 dbms_output.put_line('Logical Block Size: '||v_lbks); dbms_output.new_line;

29 dbms_diskgroup.patchfile(v_FsFileName,12,v_lbks,1,0,1,v_AsmFileName,v_AsmFileType,v_offstart,0);

30 end;

31 /

Enter value for file_with_patched_block: /tmp/xifenfei.dbf

old 15: v_FsFileName := '&file_with_patched_block';

new 15: v_FsFileName := '/tmp/xifenfei.dbf';

Enter value for file_to_patch_in_asm: +DATA/xifenfei/datafile/users.259.878224279

old 16: v_AsmFileName := '&file_to_patch_in_ASM';

new 16: v_AsmFileName := '+DATA/xifenfei/datafile/users.259.878224279';

Enter value for block_to_patch: 171

old 17: v_offstart := '&block_to_patch';

new 17: v_offstart := '171';

File: +DATA/xifenfei/datafile/users.259.878224279

Type: 12 Data File Copy

Size: 640

Logical Block Size: 8192

PL/SQL procedure successfully completed.

验证修改block是否正确

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 952020992 bytes

Fixed Size 2258960 bytes

Variable Size 306186224 bytes

Database Buffers 637534208 bytes

Redo Buffers 6041600 bytes

Database mounted.

Database opened.

SQL> select rowid,xifenfei from t_xifenfei;

ROWID XIFENFEI

------------------ ----------------

AAAVU3AAEAAAACrAAA www.XIFENFEI.com

dbms_diskgroup拷贝asm datafile to os

SQL> declare

2 v_AsmFileName varchar2(4000);

3 v_FsFileName varchar2(4000);

4 v_filetype number;

5 v_filesize number;

6 v_lbks number;

7 v_typename varchar2(4000);

8 v_pblksize number;

9 v_handle number;

10 begin

11 dbms_output.enable(500000);

12 v_AsmFileName := '&ASM_file_name';

13 v_FsFileName := '&FileSystem_file_name';

14 dbms_diskgroup.getfileattr(v_AsmFileName,v_filetype,v_filesize,v_lbks);

15 dbms_diskgroup.open(v_AsmFileName,'r',v_filetype,v_lbks,v_handle,v_pblksize,v_filesize);

16 dbms_diskgroup.close(v_handle);

17 select decode(v_filetype,1,'Control File',2,'Data File',3,'Online Log File',4,'Archive Log',5,'Trace File',6,'Temporary File',

18 7,'Not Used',8,'Not Used',9,'Backup Piece',10,'Incremental Backup Piece',11,'Archive Backup Piece',12,'Data File Copy',

19 13,'Spfile',14,'Disaster Recovery Configuration',15,'Storage Manager Disk',16,'Change Tracking File',17,'Flashback Log File',

20 18,'DataPump Dump File',19,'Cross Platform Converted File',20,'Autobackup',21,'Any OS file',22,'Block Dump File',

21 23,'CSS Voting File',24,'CRS') into v_typename from dual;

22 dbms_output.put_line('File: '||v_AsmFileName); dbms_output.new_line;

23 dbms_output.put_line('Type: '||v_filetype||' '||v_typename); dbms_output.new_line;

24 dbms_output.put_line('Size (in logical blocks): '||v_filesize); dbms_output.new_line;

25 dbms_output.put_line('Logical Block Size: '||v_lbks); dbms_output.new_line;

26 dbms_output.put_line('Physical Block Size: '||v_pblksize); dbms_output.new_line;

27 dbms_diskgroup.patchfile(v_AsmFileName,v_filetype,v_lbks,1,0,v_filesize,v_FsFileName,2,1,1);

28 end;

29 /

Enter value for asm_file_name: +DATA/xifenfei/datafile/users.259.878224279

old 12: v_AsmFileName := '&ASM_file_name';

new 12: v_AsmFileName := '+DATA/xifenfei/datafile/users.259.878224279';

Enter value for filesystem_file_name: /tmp/users01.dbf

old 13: v_FsFileName := '&FileSystem_file_name';

new 13: v_FsFileName := '/tmp/users01.dbf';

File: +DATA/xifenfei/datafile/users.259.878224279

Type: 12 Data File Copy

Size (in logical blocks): 640

Logical Block Size: 8192

Physical Block Size: 512

PL/SQL procedure successfully completed.

[grid@xifenfei ~]$ ls -l /tmp/users01.dbf

-rw-r----- 1 grid oinstall 5251072 Apr 28 16:39 /tmp/users01.dbf

通过上述几个简单例子说明:dbms_diskgroup可以看asm file的属性,可以拷贝asm中的datafile中的某个block到os,也可以从os拷贝到asm,可以从asm中直接拷贝文件文件到os等功能