分类目录归档：数据库

对于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等功能