注册

DBMS_SCHEDULER.CREATE_JOB的定时备份问题

lzq 2025/01/06 290 1

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:DM8
【操作系统】:KylinV10
【CPU】:
【问题描述】:定时备份时没有按周备份,而是按天在备份
作业:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'WEEKLY_FULL_BACKUP_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN full_backup; END;',
start_date => '2024-12-30 03:00:00.000000 +08:00',
repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN; BYHOUR=3; BYMINUTE=0; BYSECOND=0',
enabled => false,
auto_drop => true
);
END;

下面是备份
CREATE OR REPLACE PROCEDURE full_backup
AS
-- 变量说明部分
db_backup_path VARCHAR2(512);
ts_backup_path VARCHAR2(512);
tab_backup_dir VARCHAR2(512);
timestamp_str VARCHAR2(100) := TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS');
BEGIN
-- 执行体

-- 数据库全量备份
db_backup_path := '/dmdata/dmbak/db_bak_' || timestamp_str;
EXECUTE IMMEDIATE 'BACKUP DATABASE FULL BACKUPSET ''' || db_backup_path || '''';
DBMS_OUTPUT.PUT_LINE('已完成数据库全量备份到: ' || db_backup_path);

-- 表空间全量备份
ts_backup_path := '/dmdata/dmbak/ts_bak_' || timestamp_str;
EXECUTE IMMEDIATE 'BACKUP TABLESPACE TABLE BACKUPSET ''' || ts_backup_path || '''';
DBMS_OUTPUT.PUT_LINE('已完成表空间 TABLE 全量备份到: ' || ts_backup_path);

-- 单个表备份
FOR rec IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER='TABLE ') LOOP
tab_backup_dir := '/dmdata/dmbak/tab_bak_' || timestamp_str || '/' || rec.TABLE_NAME || '/';
EXECUTE IMMEDIATE 'BACKUP TABLE TABLE .' || rec.TABLE_NAME || ' BACKUPSET ''' || tab_backup_dir || '''';
DBMS_OUTPUT.PUT_LINE('已备份表: ' || rec.TABLE_NAME || ' 到目录: ' || tab_backup_dir);
END LOOP;

EXCEPTION
WHEN OTHERS THEN
-- 异常处理部分
DBMS_OUTPUT.PUT_LINE('全量备份过程中出现错误: ' || SQLERRM);
END full_backup;

回答 0
暂无回答
扫一扫
联系客服