理解dbms_file_transfer

原创 2012年03月30日 11:14:08

新建一张测试数据表

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.

 

dbms_file_transfer使用简介

dbms_file_transfer这个包可以在两个位置传输文件,分别可以有
  • kellyseeme
  • kellyseeme
  • 2014年07月09日 16:12
  • 880

利用DBMS_FILE_TRANSFER传输数据库文件

从Oracle 10g开始,Oracle提供了DBMS_FILE_TRANSFER这么一个程序包,可以方便地在本地数据库和远程数据库,ASM和文件系统间传输数据库文件。这样数据库文件的传输就方便了许多...
  • liqfyiyi
  • liqfyiyi
  • 2013年05月16日 09:17
  • 2410

使用 dbms_file_transfer 方式测试增量传输表空间XTTS

使用 dbms_file_transfer 方式测试增量传输表空间XTTS  dbms_file_transfer :   For large number of files, using dbm...
  • wll_1017
  • wll_1017
  • 2017年03月15日 18:05
  • 580

DBMS_FILE_TRANSFER文件拷贝命令介绍

DBMS_FILE_TRANSFER命令介绍 用途 可以用来传输文件,替代操作系统命令和数据库之间进行传输表空间操作。 在os system和ASM中的文件都可以使用此过程 实验 SQL> SQL>...
  • wangzizhzxy
  • wangzizhzxy
  • 2013年11月22日 11:16
  • 816

使用DBMS_FILE_TRANSFER包拷贝文件

Copying a File on a Local File System This section includes an example that uses the COPY_FILE proc...
  • lichangzai
  • lichangzai
  • 2013年02月18日 11:10
  • 671

DBMS_FILE_TRANSFER Package in Oracle Database 10g

 DBMS_FILE_TRANSFER Package in Oracle Database 10g from:http://www.oracle-base.com/articles/10g/File...
  • palm_civet
  • palm_civet
  • 2009年10月01日 21:20
  • 423

使用dbms_file_transfer传输ASM file出现ORA-15001

10.2.0.5 on oracle linus 5.8 使用dbms_file_transfer.copy_file出现ORA-15001错误,尝试直接在ASM diskgroup上直接创建tab...
  • loryliu
  • loryliu
  • 2015年10月02日 17:11
  • 295

DBMS_FILE_TRANSFER用法取asm下文件

###192.168.8.115的asm里下的日志文件取到192.168.8.116所在的asm或者os下### --在116上创建database link create public databa...
  • u013428681
  • u013428681
  • 2014年01月22日 18:16
  • 508

使用Oracle包DBMS_FILE_TRANSFER进行copy文件

参考文档: https://docs.oracle.com/database/122/ADMIN/managing-data-files-and-temp-files.htm#ADMIN11438 ...
  • xxzhaobb
  • xxzhaobb
  • 2017年11月08日 21:17
  • 25

MVC你是如何理解的?

最近学委们一直都在研究MVC,秉着好好学习,天天向上的心理,时不时的就去蹭会。说实话,原来也有看过MVC的一些相关资料,但是只是浅尝辄止。而且,因为先学了三层,所以再看MVC的时候,潜意识里的就认为它...
  • huo_yun
  • huo_yun
  • 2014年08月19日 21:17
  • 2649
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:理解dbms_file_transfer
举报原因:
原因补充:

(最多只允许输入30个字)