ORACLE使用JOB定時備份資料庫

來源:互聯網
上載者:User

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

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.