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_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@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.