对于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等功能
E-Mail:dba@xifenfei.com