dbms_file_transfer使用简介

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

a 从一个asm diskgroup传输到另外一个asm diskgroup

b 从一个asm diskgroup传输到本地磁盘文件

c 从本地磁盘文件传输到asm

d 从一个计算机传输到另外一台计算机

分别有三种方法：

a copy_file 文件从一个目录拷贝到另外一个目录，只能本地传输

b get_file 把远程的文件拷贝到本地的目录中

c put_file 把本地文件拷贝到远程的目录中

1、创建一个测试表空间

SQL> create tablespace kel;

Tablespace created.

SQL> select file_name from dba_data_files where tablespace_name='KEL';

FILE_NAME

--------------------------------------------------------------------------------

+RECOVER/omf/datafile/kel.272.852423821

2、创建测试表

SQL> create table a (id int,name char(10)) tablespace kel;

Table created.

3、插入测试数据

SQL> insert into a values (1,'aa');

1 row created.

4、用analyze进行分析

SQL> analyze table a compute statistics;--对表进行分析

Table analyzed.

SQL> select blocks from user_tables where table_name='A';--数据表占用5个块

    BLOCKS

----------

	 5

SQL> select dbms_rowid.rowid_relative_fno(rowid) r_fno,--这个表的数据存放在数据文件5的12块上

  2  dbms_rowid.rowid_block_number(rowid) b_no,id

  3  from a order by 1,2;

     R_FNO	 B_NO	      ID

---------- ---------- ----------

	 5	   12	       1

	 5	   12	       1

	 5	   12	       1

	 5	   12	       1

	 5	   12	       1

	 5	   12	       1

	 5	   12	       1

	 5	   12	       1

	 5	   12	       1

	 5	   12	       1

	 5	   12	       1

     R_FNO	 B_NO	      ID

---------- ---------- ----------

	 5	   12	       1

	 5	   12	       1

	 5	   12	       1

	 5	   12	       1

	 5	   12	       1

16 rows selected.

5、查看数据文件5的位置

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

NAME

--------------------------------------------------------------------------------

+RECOVER/omf/datafile/kel.272.852423821

6、创建两个目录

SQL> create directory sd as '+recover/omf/datafile';--创建的时候，目录是必须存在的，否则会出现错误

Directory created.

SQL> create directory dd as '/home/oracle/backuptest';--删除目录的语法是drop directory name

Directory created.

7、将数据文件offline

SQL> alter database datafile 5 offline;

Database altered.

8、把文件从ASM拷贝到本地文件系统中

SQL> begin

  2  dbms_file_transfer.copy_file('sd','KEL.272.852423821','dd','kel.dbf');

  3  end;

  4  /

PL/SQL procedure successfully completed.

9、确认拷贝成功

[oracle@kel backuptest]$ ls -l kel.dbf

-rw-r----- 1 oracle oinstall 104865792 Jul  9 00:40 kel.dbf

10、对文件进行检查

[oracle@kel backuptest]$ dbv file=kel.dbf 

DBVERIFY: Release 10.2.0.1.0 - Production on Wed Jul 9 00:42:10 2014

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

DBVERIFY - Verification starting : FILE = kel.dbf

DBVERIFY - Verification complete

Total Pages Examined         : 12800

Total Pages Processed (Data) : 5

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 11

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 12784

Total Pages Marked Corrupt   : 0  ---表示文件没有坏块

Total Pages Influx           : 0

Highest block SCN            : 587508 (0.587508)

11、使用bbed破坏数据文件

查看bbed的两个参数文件：

[oracle@kel backuptest]$ cat bbed.par --自己编辑生成的参数文件

blocksize=8192

listfile=list

mode=edit

[oracle@kel backuptest]$ cat list

5 /home/oracle/backuptest/kel.dbf 104865792  --表示的含义为文件号、文件名、文件大小

[oracle@kel backuptest]$ bbed parfile=bbed.par  --BBED的密码为blockedit

Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Wed Jul 9 00:46:52 2014

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

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

BBED> set dba 5,12 --注意5,12的由来，注意上面分析的结果

	DBA            	0x0140000c (20971532 5,12)

BBED> find /c a

 File: /home/oracle/backuptest/kel.dbf (5)

 Block: 12               Offsets: 7923 to 8191           Dba:0x0140000c

------------------------------------------------------------------------

 61612020 20202020 20202c01 0202c102 0a616120 20202020 2020202c 010202c1

 020a6161 20202020 20202020 2c010202 c1020a61 61202020 20202020 202c0102

 02c1020a 61612020 20202020 20202c01 0202c102 0a616120 20202020 2020202c

 010202c1 020a6161 20202020 20202020 2c010202 c1020a61 61202020 20202020

 202c0102 02c1020a 61612020 20202020 20202c01 0202c102 0a616120 20202020

 2020202c 010202c1 020a6161 20202020 20202020 2c010202 c1020a61 61202020

 20202020 202c0102 02c1020a 61612020 20202020 20202c01 0202c102 0a616120

 20202020 2020202c 010202c1 020a6161 20202020 20202020 2c010202 c1020a61

 61202020 20202020 200106f4 f6 

 <32 bytes per line>

BBED> dump /v dba 5,12 offset 2170 count 16

 File: /home/oracle/backuptest/kel.dbf (5)

 Block: 12      Offsets: 2170 to 2185  Dba:0x0140000c

-------------------------------------------------------

 00000000 00000000 00000000 00000000 l ................

 <16 bytes per line>

BBED> modify 100 dba 5,12

 File: /home/oracle/backuptest/kel.dbf (5)

 Block: 12               Offsets: 2170 to 2185           Dba:0x0140000c

------------------------------------------------------------------------

 64000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> exit

12、再次检查数据文件

[oracle@kel backuptest]$ dbv file=kel.dbf 

DBVERIFY: Release 10.2.0.1.0 - Production on Wed Jul 9 00:49:44 2014

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

DBVERIFY - Verification starting : FILE = kel.dbf

Page 12 is marked corrupt

Corrupt block relative dba: 0x0140000c (file 5, block 12)

Bad check value found during dbv:

Data in bad block:

 type: 6 format: 2 rdba: 0x0140000c

 last change scn: 0x0000.0008f6f4 seq: 0x1 flg: 0x06

 spare1: 0x0 spare2: 0x0 spare3: 0x0

 consistency value in tail: 0xf6f40601

 check value in block header: 0xaa87

 computed block checksum: 0x64

DBVERIFY - Verification complete

Total Pages Examined         : 12800

Total Pages Processed (Data) : 4

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 11

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 12784

Total Pages Marked Corrupt   : 1   --数据文件出现坏块

Total Pages Influx           : 0

Highest block SCN            : 587507 (0.587507)

13、把文件拷贝回ASM

SQL> begin

  2  dbms_file_transfer.copy_file('dd','kel.dbf','sd','kel.dbf');

  3  end;

  4  /

PL/SQL procedure successfully completed.

14、对文件进行更名

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

+RECOVER/omf/datafile/system.256.852236093

+RECOVER/omf/datafile/undotbs1.258.852236095

+RECOVER/omf/datafile/sysaux.257.852236095

+RECOVER/omf/datafile/users.259.852236095

+RECOVER/omf/datafile/kel.272.852423821

SQL> alter tablespace kel rename datafile '+RECOVER/omf/datafile/kel.272.852423821'

  2  to '+recover/omf/datafile/kel.dbf';

Tablespace altered.

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

+RECOVER/omf/datafile/system.256.852236093

+RECOVER/omf/datafile/undotbs1.258.852236095

+RECOVER/omf/datafile/sysaux.257.852236095

+RECOVER/omf/datafile/users.259.852236095

+RECOVER/omf/datafile/kel.dbf

15、将数据文件online

SQL> alter database datafile 5 online;

alter database datafile 5 online

*

ERROR at line 1:

ORA-01113: file 5 needs media recovery

ORA-01110: data file 5: '+RECOVER/omf/datafile/kel.dbf'

16 、恢复坏块

[oracle@kel backuptest]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jul 9 00:57:03 2014

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

connected to target database: OMF (DBID=680034237)

RMAN> recover datafile 5;

Starting recover at 09-JUL-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=148 devtype=DISK

starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished recover at 09-JUL-14

17、把数据文件online

SQL> alter database datafile 5 online;

Database altered.

秒客网

dbms_file_transfer使用简介

相关文章