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

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_transfer

PROCEDURE 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                INPROCEDURE 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                INPROCEDURE 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.8522932596 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：

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

 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------      7369 SMITH      CLERK           7902 17-DEC-80        800                    20      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30      7566 JONES      MANAGER         7839 02-APR-81       2975                    20      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20      7839 KING       PRESIDENT            17-NOV-81       5000                    10      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20      7900 JAMES      CLERK           7698 03-DEC-81        950                    30      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20      7934 MILLER     CLERK           7782 23-JAN-82       1300                    1014 rows selected.

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

16:23:59 SYS@ test1asm>select name from v$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.