ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用JOB定时备份数据库

使用JOB定时备份数据库

原创 Linux操作系统 作者:yangtingkun 时间:2007-06-04 00:00:00 0 删除 编辑

Oracle的备份一般都是在操作系统上完成,因此定时备份Oracle的功能一般都是由操作系统功能完成,比如crontab。但是OraclePIPE接口使得在Oracle数据库中通过JOB来备份Oracle变得可能。

这篇文章给出一个简单的例子,说明如何在JOB中定期备份数据库。


首先需要保证RMANPIPE方式一直在后台运行:

[oracle@member member]$ rman pipe PJOB target / timeout = 360000 &
[1] 22735
[oracle@member member]$
Recovery Manager: Release 9.2.0.4.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.


[oracle@member member]$

设置timeout参数的值大于备份JOB的周期。

下面构建一个日志表和备份使用的过程:

SQL> CREATE TABLE BACKUP_LOG (END_DATE DATE, OUTPUT_LOG VARCHAR2(4000));

Table created.

SQL> CREATE OR REPLACE PROCEDURE P_BACKUP_SQL AS
2 V_INPUT VARCHAR2(32767);
3 V_OUTPUT VARCHAR2(32767);
4 V_OUT NUMBER;
5 BEGIN
6 V_INPUT := 'RUN
7 {
8 ALLOCATE CHANNEL C1 DEVICE TYPE DISK FORMAT ''/data/backup/member/%U'';
9 ALLOCATE CHANNEL C2 DEVICE TYPE DISK FORMAT ''/data/backup/member/%U'';
10 ALLOCATE CHANNEL C3 DEVICE TYPE DISK FORMAT ''/data/backup/member/%U'';
11 BACKUP DATABASE;
12 }';
13 DBMS_PIPE.PACK_MESSAGE(V_INPUT);
14 V_OUT := DBMS_PIPE.SEND_MESSAGE('ORA$RMAN_PJOB_IN');
15 COMMIT;
16
17 V_OUT := 0;
18 WHILE (V_OUT = 0) LOOP
19 V_OUT := DBMS_PIPE.RECEIVE_MESSAGE('ORA$RMAN_PJOB_OUT', 3600);
20 IF V_OUT = 0 THEN
21 DBMS_PIPE.UNPACK_MESSAGE(V_OUTPUT);
22 INSERT INTO BACKUP_LOG (END_DATE, OUTPUT_LOG) VALUES (SYSDATE, SUBSTRB(V_OUTPUT, 1, 4000));
23 END IF;
24 END LOOP;
25 COMMIT;
26 END;
27 /

Procedure created.

下面就可以通过job来定时备份了:

SQL> DECLARE
2 V_JOB NUMBER;
3 BEGIN
4 DBMS_JOB.SUBMIT(V_JOB, 'P_BACKUP_SQL;', TRUNC(SYSDATE) + 17.25/24, 'TRUNC(SYSDATE) + 41/24');
5 COMMIT;
6 END;
7 /

PL/SQL procedure successfully completed.

等待备份结束后,检查备份结果:

SQL> SELECT * FROM BACKUP_LOG;

END_DATE OUTPUT_LOG
------------------- --------------------------------------------------------------------------------
2007-06-04 17:40:09 channel C3: finished piece 1 at 04-6
-07
2007-06-04 17:40:09 piece handle=/data/backup/member/07ijeqcm_1_1 comment=NONE
2007-06-04 17:15:01 using target database controlfile instead of recovery catalog
2007-06-04 17:40:09 channel C3: backup set complete, elapsed time: 00:25:07
2007-06-04 17:40:12 channel C3: starting full datafile backupset
2007-06-04 17:40:12 channel C3: specifying datafile(s) in backupset
2007-06-04 17:40:12 including current SPFILE in backupset
2007-06-04 17:40:13 including current controlfile in backupset
2007-06-04 17:40:13 input datafile fno=00001 name=/data/oradata/member/system01.dbf
2007-06-04 17:40:13 input datafile fno=00003 name=/data/oradata/member/cwmlite01.dbf
2007-06-04 17:40:13 input datafile fno=00009 name=/data/oradata/member/users01.dbf
2007-06-04 17:40:13 input datafile fno=00004 name=/data/oradata/member/drsys01.dbf
2007-06-04 17:40:13 channel C3: starting piece 1 at 04-6
-07
2007-06-04 17:40:38 channel C2: finished piece 1 at 04-6
-07
2007-06-04 17:40:38 piece handle=/data/backup/member/06ijeqcm_1_1 comment=NONE
2007-06-04 17:40:38 channel C2: backup set complete, elapsed time: 00:25:36
2007-06-04 17:40:41 channel C2: starting full datafile backupset
2007-06-04 17:40:41 channel C2: specifying datafile(s) in backupset
2007-06-04 17:40:41 input datafile fno=00005 name=/data/oradata/member/example01.dbf
2007-06-04 17:40:41 input datafile fno=00010 name=/data/oradata/member/xdb01.dbf
2007-06-04 17:40:41 input datafile fno=00006 name=/data/oradata/member/indx01.dbf
2007-06-04 17:40:41 input datafile fno=00008 name=/data/oradata/member/tools01.dbf
2007-06-04 17:40:41 channel C2: starting piece 1 at 04-6
-07
2007-06-04 17:51:37 channel C2: finished piece 1 at 04-6
-07
2007-06-04 17:51:37 piece handle=/data/backup/member/09ijersp_1_1 comment=NONE
2007-06-04 17:51:37 channel C2: backup set complete, elapsed time: 00:10:55
2007-06-04 17:51:37 channel C2: starting full datafile backupset
2007-06-04 17:51:38 channel C2: specifying datafile(s) in backupset
2007-06-04 17:51:38 input datafile fno=00018 name=/data/oradata/member/info.dbf
2007-06-04 17:51:38 channel C2: starting piece 1 at 04-6
-07
2007-06-04 17:52:03 channel C3: finished piece 1 at 04-6
-07
2007-06-04 17:52:03 piece handle=/data/backup/member/08ijerrs_1_1 comment=NONE
2007-06-04 17:52:03 channel C3: backup set complete, elapsed time: 00:11:51
2007-06-04 17:53:07 channel C2: finished piece 1 at 04-6
-07
2007-06-04 17:53:07 piece handle=/data/backup/member/0aijesh8_1_1 comment=NONE
2007-06-04 17:53:07 channel C2: backup set complete, elapsed time: 00:01:31
2007-06-04 18:44:45 channel C1: finished piece 1 at 04-6
-07
2007-06-04 18:44:45 piece handle=/data/backup/member/05ijeqcl_1_1 comment=NONE
2007-06-04 18:44:45 channel C1: backup set complete, elapsed time: 01:29:44
2007-06-04 18:44:45 Finished backup at 04-6
-07
2007-06-04 18:44:45 released channel: C1
2007-06-04 18:44:45 released channel: C2
2007-06-04 18:44:45 released channel: C3
2007-06-04 18:44:45 RMAN-00572: waiting for dbms_pipe input

44 rows selected.

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

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

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10487726