理解dbms_file_transfer

新建一张测试数据表

SQL> create table a (id number,name char(2000));

Table created.
SQL> insert into a values (1,'abc');

1 row created.

SQL> insert into a values (2,'def');

1 row created.

SQL> insert into a values (3,'ade');

1 row created.

SQL> insert into a values (4,'asdf');

analyze分析

SQL> analyze table a compute statistics;

Table analyzed.
SQL> select blocks from user_tables where table_name='A';

    BLOCKS
----------
         5

查看文件和数据号
SQL> select dbms_rowid.rowid_relative_fno(rowid) r_fno,dbms_rowid.rowid_block_number(rowid)b_no,id from a order by 1,2;

     R_FNO       B_NO         ID
---------- ---------- ----------
         4        388          1
         4        388          2
         4        388          3
         4        389          4

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

NAME
--------------------------------------------------------------------------------
+DG1/orcl/datafile/users.262.779226311

SQL>
SQL> create directory users as '+DG1/orcl/datafile';

Directory created.
SQL> create directory dd as '/home/oracle';

Directory created.

SQL> alter database datafile 4 offline;

Database altered.

SQL> begin
  2     dbms_file_transfer.copy_file('users','users.262.779226311','dd','users.dbf');
  3  end;
  4  /

PL/SQL procedure successfully completed.

[oracle@Oracle ~]$ ls users.dbf
users.dbf
[oracle@Oracle ~]$

[oracle@Oracle ~]$ dbv file=/home/oracle/users.dbf

DBVERIFY: Release 10.2.0.1.0 - Production on Thu Mar 29 22:54:53 2012

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

DBVERIFY - Verification starting : FILE = /home/oracle/users.dbf

DBVERIFY - Verification complete

Total Pages Examined         : 640
Total Pages Processed (Data) : 48
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 63
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 136
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 393
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 592906 (0.592906)

这里用到bbed小工具

[oracle@Oracle lib]$ cat bbed.par
blocksize=8192
listfile=list
mode=edit
[oracle@Oracle lib]$ ./bbed parfile=bbed.par
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Fri Mar 30 10:44:52 2012

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

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

BBED>
BBED> set dba 4,389
        DBA             0x01000185 (16777605 4,389)
BBED> find /c 4
 File: /home/oracle/users.dbf (4)
 Block: 389              Offsets:  212 to  723           Dba:0x01000185
------------------------------------------------------------------------
 34144814 5c147014 84149814 ac14c014 d414e814 fc141015 24153815 4c156015
 74158815 9c15b015 c415d815 ec150016 14162816 3c165016 64167816 8c16a016
 b416c816 dc16f016 04171817 2c174017 54176817 7c179017 a417b817 cc17e017
 f4170818 1c183018 44185818 6c188018 9418a818 bc18d018 e418f818 0c192019
 34194819 5c197019 84199819 ac19c019 d419e819 fc19101a 241a381a 4c1a601a
 741a881a 9c1ab01a c41ad81a ec1a001b 141b281b 3c1b501b 641b781b 8c1ba01b
 b41bc81b dc1bf01b 041c181c 2c1c401c 541c681c 7c1c901c a41cb81c cc1ce01c
 f41c081d 1c1d301d 441d581d 6c1d801d 941da81d bc1dd01d e41df81d 0c1e201e
 341e481e 5c1e701e 841e981e ac1ec01e d41ee81e fc1e101f 241f381f 4c1f0000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 <32 bytes per line>

BBED> dump /v dba 4,389 offset 212 count 32
 File: /home/oracle/users.dbf (4)
 Block: 389     Offsets:  212 to  243  Dba:0x01000185
-------------------------------------------------------
 34144814 5c147014 84149814 ac14c014 l 4.H.\.p.....

                                                     d414e814 fc141015 24153815 4c156015 l ..$.8.L.`.

 <16 bytes per line>

BBED> modify 50 dba 4,389
 File: /home/oracle/users.dbf (4)
 Block: 389              Offsets:  212 to  243           Dba:0x01000185
------------------------------------------------------------------------
 32144814 5c147014 84149814 ac14c014 d414e814 fc141015 24153815 4c156015

 <32 bytes per line>

BBED> exit

[oracle@Oracle ~]$ dbv file=/home/oracle/users.dbf

DBVERIFY: Release 10.2.0.1.0 - Production on Fri Mar 30 10:57:54 2012

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

DBVERIFY - Verification starting : FILE = /home/oracle/users.dbf
Page 389 is marked corrupt
Corrupt block relative dba: 0x01000185 (file 4, block 389)
Bad check value found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x01000185
 last change scn: 0x0000.00090bb9 seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x0bb90602
 check value in block header: 0xbf4a
 computed block checksum: 0x50

 

DBVERIFY - Verification complete

Total Pages Examined         : 640
Total Pages Processed (Data) : 47
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 63
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 136
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 393
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 592906 (0.592906)

SQL> begin
  2     dbms_file_transfer.copy_file('dd','users.dbf','users','users.262.779226311');
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-19504: failed to create file "+DG1/orcl/datafile/users.262.779226311"
ORA-17502: ksfdcre:4 Failed to create file
+DG1/orcl/datafile/users.262.779226311
ORA-15046: ASM file name '+DG1/orcl/datafile/users.262.779226311' is not in
single-file creation form
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 84
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 193
ORA-06512: at line 2

SQL>
SQL> begin
  2     dbms_file_transfer.copy_file('dd','users.dbf','users','users.dbf');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

+DG1/orcl/datafile/users.262.779226311

10 rows selected.

SQL> alter tablespace users rename datafile '+DG1/orcl/datafile/users.262.779226311' to '+DG1/orcl/datafile/users.dbf';

Tablespace altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

+DG1/orcl/datafile/users.dbf

SQL> alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '+DG1/orcl/datafile/users.dbf'

[oracle@Oracle ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Mar 30 11:10:28 2012

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

connected to target database: ORCL (DBID=1306991326)

RMAN> recover datafile 4;

Starting recover at 30-MAR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:04

Finished recover at 30-MAR-12

RMAN>

SQL> alter database datafile 4 online;

Database altered.