Oracle的備份一般都是在作業系統上完成,因此定時備份Oracle的功能一般都是由作業系統功能完成,比如crontab。但是Oracle的PIPE介面使得在Oracle資料庫中通過JOB來備份Oracle變得可能。
這篇文章給出一個簡單的例子,說明如何在JOB中定期備份資料庫。
首先需要保證RMAN已PIPE方式一直在後台運行:
[oracle@member member]$ rman pipe PJOB target / nocatalog
[oracle@member member]$
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
[oracle@member member]$
下面構建一個日誌表和備份使用的過程:
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