利用DBMS_FILE_TRANSFER包实现远程备份

Oracle的rman一直没有提供备份到其他服务器上的功能，不过从10g开始，可以利用DBMS_FILE_TRANSFER包来实现这个功能。

 

 

由于DBMS_FILE_TRANSFER包可以实现PUT_FILE和GET_FILE的功能，因此可以在源数据库通过PUT_FILE的方式进行备份，也可以在远端站点通过GET_FILE的方式进行备份，不过考虑到使用DBMS_FILE_TRANSFER需要保证数据库处于打开的状态，因此需要将表空间处于备份状态，或者将表空间或数据库处于只读状态。显然后面一种方法对系统可用性的影响更大，而采用前一种需要执行ALTER TABLESPACE命令，因此还是选择在源数据库使用DBMS_FILE_TRANSFER.PUT_FILE的方式更方便一点。

如果只是在远端保存备份，那么没有太多的限制，只是要求源数据库和目标服务器上数据库版本都在10g以上，且数据库都处于打开状态。另外需要当前用户配置了远端数据库的数据库链，且数据库链用户可以访问备份目录的DIRECTORY，且拥有写权限，而当前用户拥有DBMS_FILE_TRANSFER包的执行权限，且拥有数据文件所在目录的DIRECTORY的访问权限。

首先在目标服务器的所在数据库做好准备工作：

[oracle@yans3 backup]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Fri May 8 15:38:18 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> create directory d_test10g as '/data/backup/test10g';

Directory created.

SQL> grant read, write on directory d_test10g to test;

Grant succeeded.

SQL> host ls -l /data/backup/test10g
total 0

下面在源数据库检查控制文件、数据文件的位置，并创建DIRECTORY和DATABASE LINK：

[oracle@bjtest ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on 星期六 5月 9 00:30:28 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> set pages 100 lines 120
SQL> select file_name from dba_data_files;

FILE_NAME
-------------------------------------------------------------------
/data/oradata/test10g/system01.dbf
/data/oradata/test10g/undotbs01.dbf
/data/oradata/test10g/sysaux01.dbf
/data/oradata/test10g/users01.dbf

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /data/oradata/test10g/control0
                                                 1.ctl, /data/oradata/test10g/c
                                                 ontrol02.ctl, /data/oradata/te
                                                 st10g/control03.ctl
SQL> create directory d_test10g as '/data/oradata/test10g';

Directory created.

SQL> create database link kaifa     
  2  connect to test
  3  identified by test
  4  using '172.25.13.227/kaifa';

Database link created.

下面可以将表空间置于备份状态，并开始备份：

SQL> alter tablespace system begin backup;

Tablespace altered.

SQL> alter tablespace sysaux begin backup;

Tablespace altered.

SQL> alter tablespace undotbs1 begin backup;

Tablespace altered.

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> exec dbms_file_transfer.put_file('D_TEST10G', 'system01.dbf', 'D_TEST10G', 'system01.dbf', 'KAIFA')

PL/SQL procedure successfully completed.

SQL> exec dbms_file_transfer.put_file('D_TEST10G', 'undotbs01.dbf', 'D_TEST10G', 'undotbs01.dbf', 'KAIFA')

PL/SQL procedure successfully completed.

SQL> exec dbms_file_transfer.put_file('D_TEST10G', 'sysaux01.dbf', 'D_TEST10G', 'sysaux01.dbf', 'KAIFA')

PL/SQL procedure successfully completed.

SQL> exec dbms_file_transfer.put_file('D_TEST10G', 'users01.dbf', 'D_TEST10G', 'users01.dbf', 'KAIFA')

PL/SQL procedure successfully completed.

SQL> alter tablespace system end backup;

Tablespace altered.

SQL> alter tablespace sysaux end backup;

Tablespace altered.

SQL> alter tablespace undotbs1 end backup;

Tablespace altered.

SQL> alter tablespace users end backup;

Tablespace altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database backup controlfile to '/data/oradata/test10g/controlfile.bak';

Database altered.

SQL> exec dbms_file_transfer.put_file('D_TEST10G', 'controlfile.bak', 'D_TEST10G', 'control01.ctl', 'KAIFA')

PL/SQL procedure successfully completed.

SQL> select name from v$archived_log; 

NAME
--------------------------------------------------------------------------
/data/oradata/test10g/archivelog/1_10_686362609.dbf

SQL> create directory d_arch as '/data/oradata/test10g/archivelog';

Directory created.

SQL> exec dbms_file_transfer.put_file('D_ARCH', '1_10_686362609.dbf', 'D_TEST10G', '1_10_686362609.dbf', 'KAIFA')

PL/SQL procedure successfully completed.

至此，所有数据文件、控制文件和归档日志的文件的备份全部完成。利用类似的方法还可以备份SPFILE，不过DBMS_FILE_TRANSFER不支持文本格式的pfile的备份。

SQL> select global_name from global_name;

GLOBAL_NAME
-----------------------------------------------------------------
KAIFA

SQL> host ls -l /data/backup/test10g
total 753244
-rw-r-----  1 oracle oinstall   8442368 May  8 16:31 1_10_686362609.dbf
-rw-r-----  1 oracle oinstall   7061504 May  8 16:30 control01.ctl
-rw-r-----  1 oracle oinstall 125837312 May  8 16:19 sysaux01.dbf
-rw-r-----  1 oracle oinstall 314580992 May  8 16:17 system01.dbf
-rw-r-----  1 oracle oinstall 209723392 May  8 16:18 undotbs01.dbf
-rw-r-----  1 oracle oinstall 104865792 May  8 16:21 users01.dbf

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-594565/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-594565/