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.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@rh55 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.
转载于:https://blog.51cto.com/tiany/1436029