oracle asm和文件系统,Oracle工具之--ASM与文件系统及跨网络传输文件
Oracle工具之--ASM與文件系統(tǒng)及跨網(wǎng)絡(luò)傳輸文件
Oracle?DBMS_FILE_TRANSFER可以實(shí)現(xiàn)文件系統(tǒng)和ASM磁盤(pán)組之間實(shí)現(xiàn)文件傳輸及ASM磁盤(pán)組之間跨網(wǎng)絡(luò)的傳輸。
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：
文件系統(tǒng)與ASM磁盤(pán)組之間文件傳送：
1、查看datafile存儲(chǔ)信息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存儲(chǔ))
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磁盤(pán)組文件到文件系統(tǒng)：
SQL> exec dbms_file_transfer.copy_file('ASM_DIR','users.259.852292709','FS_DIR','users01.dbf');
PL/SQL procedure successfully completed.
參數(shù)信息：
1、源文件目錄
2、源文件名
3、目標(biāo)文件目錄
4、目標(biāo)文件
16:04:36 SYS@ prod1>alter tablespace users end backup;
Tablespace altered.
4、驗(yàn)證文件傳送情況
[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磁盤(pán)組
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、驗(yàn)證文件傳送
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：
跨網(wǎng)絡(luò)從ASM磁盤(pán)組傳輸文件到ASM磁盤(pán)組
案例環(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.
驗(yàn)證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.
參數(shù)信息：
1、源文件目錄
2、源文件
3、目標(biāo)文件目錄
4、目標(biāo)文件
5、db-link
4、驗(yàn)證文件傳輸
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.
參數(shù)信息：
1、源文件目錄
2、源文件
3、db-link
4、目標(biāo)文件目錄
5、目標(biāo)文件
7、在node2驗(yàn)證文件
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
文件下載成功！
故障案例：
文件傳輸出現(xiàn)以下錯(cuò)誤：
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沒(méi)有做授權(quán)！
16:36:24 SYS@ test1asm>grant read ,write on directory test_asm to public;
Grant succeeded.
總結(jié)
以上是生活随笔為你收集整理的oracle asm和文件系统,Oracle工具之--ASM与文件系统及跨网络传输文件的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： oracle 安装及使用方法,Oracl
- 下一篇： oracle 按月累计求和,SQL Cu