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

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

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

Oracle DBMS_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_transferPROCEDURE COPY_FILE

Argument Name                  Type                    In/Out Default?

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

SOURCE_DIRECTORY_OBJECT        VARCHAR2                IN

SOURCE_FILE_NAME               VARCHAR2                IN

DESTINATION_DIRECTORY_OBJECT   VARCHAR2                IN

DESTINATION_FILE_NAME          VARCHAR2                IN

PROCEDURE GET_FILE

Argument Name                  Type                    In/Out Default?

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

SOURCE_DIRECTORY_OBJECT        VARCHAR2                IN

SOURCE_FILE_NAME               VARCHAR2                IN

SOURCE_DATABASE                VARCHAR2                IN

DESTINATION_DIRECTORY_OBJECT   VARCHAR2                IN

DESTINATION_FILE_NAME          VARCHAR2                IN

PROCEDURE PUT_FILE

Argument Name                  Type                    In/Out Default?

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

SOURCE_DIRECTORY_OBJECT        VARCHAR2                IN

SOURCE_FILE_NAME               VARCHAR2                IN

DESTINATION_DIRECTORY_OBJECT   VARCHAR2                IN

DESTINATION_FILE_NAME          VARCHAR2                IN

DESTINATION_DATABASE           VARCHAR2                IN

案例1：

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

1、查看datafile存储信息SQL> select name from v$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.852293259

6 rows selected.

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：