理解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 方式测试增量传输表空间XTTS

使用 dbms_file_transfer 方式测试增量传输表空间XTTS  dbms_file_transfer :   For large number of files, using dbm...

dbms_file_transfer使用简介

dbms_file_transfer这个包可以在两个位置传输文件,分别可以有

DBMS_FILE_TRANSFER用法取asm下文件

###192.168.8.115的asm里下的日志文件取到192.168.8.116所在的asm或者os下### --在116上创建database link create public databa...

DBMS_FILE_TRANSFER文件拷贝命令介绍

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

使用DBMS_FILE_TRANSFER迁移ASM中的表空间和数据文件

这是一个存储过程包,是除了RMAN之外又一个用于执行迁移任务的工具包,可以完成如下的迁移:ASM->ASM、ASM->OS Flie、OS File->ASM、OS File->OS File。  ...
  • gtlions
  • gtlions
  • 2013年02月26日 06:24
  • 820

file_transfer

  • 2015年08月30日 11:14
  • 70KB
  • 下载

Cordova – file-transfer, unzip and present adventures

For a hybrid project I have a very simple requirement: download zip file, unzip it, serve content on...
  • n7800
  • n7800
  • 2015年03月04日 12:44
  • 838

File Transfer1.2j汉化版(文件传送)

  • 2017年01月19日 18:43
  • 262KB
  • 下载

java udp file transfer client

  • 2014年03月03日 18:30
  • 1KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:理解dbms_file_transfer
举报原因:
原因补充:

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