使用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.