溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
Oracle工具之--ASM與文件系統(tǒng)及跨網絡傳輸文件
Oracle DBMS_FILE_TRANSFER可以實現文件系統(tǒng)和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：
文件系統(tǒng)與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（文件系統(tǒng)）
[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磁盤組文件到文件系統(tǒng)：
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、從文件系統(tǒng)傳送文件到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磁盤組
案例環(huán)境：
NODE1：
操作系統(tǒng)： Linux EL5
Oracle： Oracle 10gR2
NODE2：
操作系統(tǒng)： 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、創(chuàng)建文件傳輸目錄（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@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.
免責聲明：本站發(fā)布的內容（圖片、視頻和文字）以原創(chuàng)、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
