當前位置：首頁 > 运维知识 > windows >内容正文

windows

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

發布時間：2023/12/15 windows 24 豆豆

生活随笔收集整理的這篇文章主要介紹了 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_FILEArgument?Name??????????????????Type????????????????????In/Out?Default?------------------------------?-----------------------?------?--------SOURCE_DIRECTORY_OBJECT????????VARCHAR2????????????????INSOURCE_FILE_NAME???????????????VARCHAR2????????????????INDESTINATION_DIRECTORY_OBJECT???VARCHAR2????????????????INDESTINATION_FILE_NAME??????????VARCHAR2????????????????IN PROCEDURE?GET_FILEArgument?Name??????????????????Type????????????????????In/Out?Default?------------------------------?-----------------------?------?--------SOURCE_DIRECTORY_OBJECT????????VARCHAR2????????????????INSOURCE_FILE_NAME???????????????VARCHAR2????????????????INSOURCE_DATABASE????????????????VARCHAR2????????????????INDESTINATION_DIRECTORY_OBJECT???VARCHAR2????????????????INDESTINATION_FILE_NAME??????????VARCHAR2????????????????IN PROCEDURE?PUT_FILEArgument?Name??????????????????Type????????????????????In/Out?Default?------------------------------?-----------------------?------?--------SOURCE_DIRECTORY_OBJECT????????VARCHAR2????????????????INSOURCE_FILE_NAME???????????????VARCHAR2????????????????INDESTINATION_DIRECTORY_OBJECT???VARCHAR2????????????????INDESTINATION_FILE_NAME??????????VARCHAR2????????????????INDESTINATION_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????????????????????207499?ALLEN??????SALESMAN????????7698?20-FEB-81???????1600????????300?????????307521?WARD???????SALESMAN????????7698?22-FEB-81???????1250????????500?????????307566?JONES??????MANAGER?????????7839?02-APR-81???????2975????????????????????207654?MARTIN?????SALESMAN????????7698?28-SEP-81???????1250???????1400?????????307698?BLAKE??????MANAGER?????????7839?01-MAY-81???????2850????????????????????307782?CLARK??????MANAGER?????????7839?09-JUN-81???????2450????????????????????107788?SCOTT??????ANALYST?????????7566?19-APR-87???????3000????????????????????207839?KING???????PRESIDENT????????????17-NOV-81???????5000????????????????????107844?TURNER?????SALESMAN????????7698?08-SEP-81???????1500??????????0?????????307876?ADAMS??????CLERK???????????7788?23-MAY-87???????1100????????????????????207900?JAMES??????CLERK???????????7698?03-DEC-81????????950????????????????????307902?FORD???????ANALYST?????????7566?03-DEC-81???????3000????????????????????207934?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

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

總結

以上是生活随笔為你收集整理的Oracle工具之--ASM与文件系统及跨网络传输文件的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：华为花币怎么用
下一篇：华龙一号海南昌江核电 3 号机组内穹顶吊