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 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：
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 10
14 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.dbf
Elapsed: 00:00:00.1516: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.
