One of the Oracle tools-- ASM and file system and transferring files across networks

One of the Oracle tools-- ASM and file system and transferring files across networks

Oracle DBMS_FILE_TRANSFER can realize the file transfer between the file system and ASM disk groups and the cross-network transfer between ASM disk groups.

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 INPROCEDURE 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 INPROCEDURE 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

Case 1:

File transfer between the file system and the ASM disk group:

1. View datafile storage information

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.8522932596 rows selected.

2. Set up the transfer directory

Establish Oracle directory (ASM storage)

SQL > create directory asm_dir as'+ DG1/prod/datafile'

Directory created.

Set up Oracle directory (file system)

[root@node2 ~] # mkdir / u01/bak

[root@node2 ~] # chown oracle:dba / u01/bak

SQL > create directory fs_dir as'/ u01Compact bak'

Directory created.

3. Hot standby for tablespace

16:04:26 SYS@ prod1 > alter tablespace users begin backup

Tablespace altered.

Back up the ASM diskgroup files to the file system:

SQL > exec dbms_file_transfer.copy_file ('ASM_DIR','users.259.852292709','FS_DIR','users01.dbf')

PL/SQL procedure successfully completed.

Parameter information:

1. Source file directory

2. Source file name

3. Target file directory

4. Target file

16:04:36 SYS@ prod1 > alter tablespace users end backup

Tablespace altered.

4. Verify file transfer

[oracle@node2 ~] $ls-lh / u01/bak

Total 5.1M

-rw-r- 1 oracle asmadmin 5.1m Jul 7 17:18 users01.dbf

File transfer successful!

5. Transfer files from the file system to the ASM disk group

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. Verify file transfer

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

File transfer successful!

Case 2:

Transfer files from ASM disk groups to ASM disk groups across the network

Case environment:

NODE1:

Operating system: Linux EL5

Oracle: Oracle 10gR2

NODE2:

Operating system: Linux EL5

Oracle: Oracle 11gR2

1. Configure database link

NODE1:

TNSNAMES.ORA:

TEST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.8.239) (PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = test1asm)

)

)

Configure 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.

Verify 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 1014 rows selected.

2. Create a file transfer directory (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.dbfElapsed: 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. Upload files from node1 to 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.

Parameter information:

1. Source file directory

2. Source file

3. Target file directory

4. Target file

5 、 db-link

4. Verify file transfer

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

File transfer successful!

5. Configure 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. Download files from node1 to 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.

Parameter information:

1. Source file directory

2. Source file

3 、 db-link

4. Target file directory

5. Target file

7. Verify the file in 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

File downloaded successfully!

Failure case:

The following error occurred in the file transfer:

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

Solution:

Directory didn't authorize it!

16:36:24 SYS@ test1asm > grant read, write on directory test_asm to public

Grant succeeded.