Oracle工具之--ASM与文件系统及跨网络传输文件

OracleDBMS_FILE_TRANSFER可以实现文件系统和ASM磁盘组之间实现文件传输及ASM磁盘组之间跨网络的传输。

DBMS_FILE_TRANSFER：

The DBMS_FILE_TRANSFER package provides procedures to copy a binary file within a database or to transfer a binary file between databases.

[oracle@node1 ~]$sqlplus / as sysdba

SQL> desc dbms_file_transfer

PROCEDURECOPY_FILEArgumentNameTypeIn/OutDefault?-------------------------------------------------------------------SOURCE_DIRECTORY_OBJECTVARCHAR2INSOURCE_FILE_NAMEVARCHAR2INDESTINATION_DIRECTORY_OBJECTVARCHAR2INDESTINATION_FILE_NAMEVARCHAR2INPROCEDUREGET_FILEArgumentNameTypeIn/OutDefault?-------------------------------------------------------------------SOURCE_DIRECTORY_OBJECTVARCHAR2INSOURCE_FILE_NAMEVARCHAR2INSOURCE_DATABASEVARCHAR2INDESTINATION_DIRECTORY_OBJECTVARCHAR2INDESTINATION_FILE_NAMEVARCHAR2INPROCEDUREPUT_FILEArgumentNameTypeIn/OutDefault?-------------------------------------------------------------------SOURCE_DIRECTORY_OBJECTVARCHAR2INSOURCE_FILE_NAMEVARCHAR2INDESTINATION_DIRECTORY_OBJECTVARCHAR2INDESTINATION_FILE_NAMEVARCHAR2INDESTINATION_DATABASEVARCHAR2IN

案例1：

文件系统与ASM磁盘组之间文件传送：

1、查看datafile存储信息

SQL>selectnamefromv$datafile;NAME--------------------------------------------------------------------------------+DG1/prod/datafile/system.256.852292703+DG1/prod/datafile/sysaux.257.852292707+DG1/prod/datafile/undotbs1.258.852292707+DG1/prod/datafile/users.259.852292709+DG1/prod/datafile/example.264.852292891+DG1/prod/datafile/undotbs2.265.8522932596rowsselected.

2、建立传输目录

建立Oracle directory（ASM存储）

SQL> create directory asm_dir as '+DG1/prod/datafile';

Directory created.

建立Oracle directory（文件系统）

[root@node2 ~]# mkdir /u01/bak

[root@node2 ~]# chown oracle:dba /u01/bak

SQL> create directory fs_dir as '/u01/bak';

Directory created.

3、表空间做热备

16:04:26 SYS@ prod1>alter tablespace users begin backup;

Tablespace altered.

备份ASM磁盘组文件到文件系统：

SQL> exec dbms_file_transfer.copy_file('ASM_DIR','users.259.852292709','FS_DIR','users01.dbf');

PL/SQL procedure successfully completed.

参数信息：

1、源文件目录

2、源文件名

3、目标文件目录

4、目标文件

16:04:36 SYS@ prod1>alter tablespace users end backup;

Tablespace altered.

4、验证文件传送情况

[oracle@node2 ~]$ ls -lh /u01/bak

total 5.1M

-rw-r----- 1 oracle asmadmin 5.1M Jul 7 17:18 users01.dbf

文件传送成功！

5、从文件系统传送文件到ASM磁盘组

16:07:51 SYS@ prod2>exec dbms_file_transfer.copy_file('FS_DIR','users01.dbf' ,'ASM_DIR','TEST01.DBF');

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.25

6、验证文件传送

ASMCMD>cd +dg1/prod/datafile

ASMCMD> ls

COPY_FILE.272.852394075

EXAMPLE.264.852292891

SYSAUX.257.852292707

SYSTEM.256.852292703

TBS1.269.852376681

TEST01.DBF

UNDOTBS1.258.852292707

UNDOTBS2.265.852293259

USERS.259.852387481

文件传送成功！

案例2：

跨网络从ASM磁盘组传输文件到ASM磁盘组

案例环境：

NODE1：

操作系统：Linux EL5

Oracle： Oracle 10gR2

NODE2：

操作系统： Linux EL5

Oracle： Oracle 11gR2

1、配置database link

NODE1：

TNSNAMES.ORA:

TEST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.239)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = test1asm)

)

)

配置db-link：

16:25:21 SYS@ prod2>create public database link

16:25:35 2 test connect to scott identified by tiger using 'TEST';

Database link created.

验证db-link：

16:26:01 SYS@ prod2>select * from emp@test;

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO------------------------------------------------------------------------------7369SMITHCLERK790217-DEC-80800207499ALLENSALESMAN769820-FEB-811600300307521WARDSALESMAN769822-FEB-811250500307566JONESMANAGER783902-APR-812975207654MARTINSALESMAN769828-SEP-8112501400307698BLAKEMANAGER783901-MAY-812850307782CLARKMANAGER783909-JUN-812450107788SCOTTANALYST756619-APR-873000207839KINGPRESIDENT17-NOV-815000107844TURNERSALESMAN769808-SEP-8115000307876ADAMSCLERK778823-MAY-871100207900JAMESCLERK769803-DEC-81950307902FORDANALYST756603-DEC-813000207934MILLERCLERK778223-JAN-8213001014rowsselected.

2、创建文件传输目录（node2）

16:23:59SYS@test1asm>selectnamefromv$datafile;NAME-----------------------------------------------------------------------------------------+DG1/test1asm/datafile/system01.dbf+DG1/test1asm/datafile/undotbs01.dbf+DG1/test1asm/datafile/sysaux01.dbf+DG1/test1asm/datafile/users01.dbfElapsed:00:00:00.15

16:24:08 SYS@ test1asm>create directory test_asm as '+dg1/test1asm/datafile';

Directory created.

Elapsed: 00:00:00.11

16:36:24 SYS@ test1asm>grant read ,write on directory test_asm to public;

Grant succeeded.

3、从node1上传文件到node2

16:40:14 SYS@ prod2>exec dbms_file_transfer.put_file('ASM_DIR','TEST01.DBF','test_asm','TEST001.DBF','TEST');

PL/SQL procedure successfully completed.

参数信息：

1、源文件目录

2、源文件

3、目标文件目录

4、目标文件

5、db-link

4、验证文件传输

ASMCMD> cd datafile

ASMCMD> ls

FILE_TRANSFER.266.852395843

SYSAUX.259.848848797

SYSTEM.258.848848793

TEST001.DBF

UNDOTBS1.260.848848799

USERS.261.848848801

sysaux01.dbf

system01.dbf

undotbs01.dbf

users01.dbf

文件传输成功！

5、配置NODE2 DATABASE LINK

TNSNAMES.ORA:

PROD2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.24)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = TAF)

)

)

[oracle@rh65 admin]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 8 16:45:01 2014

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

16:45:02 SYS@ test1asm>create public database link prod

16:45:15 2 connect to scott identified by tiger using 'prod2';

Database link created.

16:49:28 SYS@ prod2>grant read,write on directory asm_dir to public;

Grant succeeded.

6、从node1下载文件到node2

16:50:14 SYS@ prod2>exec dbms_file_transfer.get_file('ASM_DIR','TEST01.DBF','prod','test_asm','TEST002.DBF');

PL/SQL procedure successfully completed.

参数信息：

1、源文件目录

2、源文件

3、db-link

4、目标文件目录

5、目标文件

7、在node2验证文件

ASMCMD> ls

FILE_TRANSFER.266.852395843

SYSAUX.259.848848797

SYSTEM.258.848848793

TEST002.DBF

UNDOTBS1.260.848848799

USERS.261.848848801

sysaux01.dbf

system01.dbf

undotbs01.dbf

users01.dbf

文件下载成功！

故障案例：

文件传输出现以下错误：

16:34:06 SYS@ prod2>exec dbms_file_transfer.put_file('ASM_DIR','TEST01.DBF','TEST_ASM','TEST001.DBF','TEST');

BEGIN dbms_file_transfer.put_file('ASM_DIR','TEST01.DBF','TEST_ASM','TEST001.DBF','TEST'); END;

*

ERROR at line 1:

ORA-06564: object TEST_ASM does not exist

ORA-02063: preceding line from TEST

ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 60

ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 168

ORA-06512: at line 1

Elapsed: 00:00:04.71

解决方法：

Directory没有做授权！

16:36:24 SYS@ test1asm>grant read ,write on directory test_asm to public;

Grant succeeded.