oracle job定时备份,使用JOB定时备份数据库

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

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

首先需要保证RMAN已PIPE方式一直在后台运行：

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