ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 利用DBMS_FILE_TRANSFER包实现远程备份

利用DBMS_FILE_TRANSFER包实现远程备份

原创 Linux操作系统 作者:yangtingkun 时间:2009-05-08 21:04:15 0 删除 编辑

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

 

 

由于DBMS_FILE_TRANSFER包可以实现PUT_FILEGET_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

下面在源数据库检查控制文件、数据文件的位置,并创建DIRECTORYDATABASE 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10455866