设置定时备份和归档删除策略
DM7
备份删除
步骤
CALL SF_BAK_LST_INIT();
CALL SF_BAK_LST_SET_N_PATH(1);
CALL SF_BAK_LST_SET_PATH(0,‘D:\’);
CALL SF_BAK_LST_COLLECT_ALL();
SELECT SF_BAK_LST_COLLECT_ALL();
删除14天前的备份
CALL SP_BATCH_DEL_BAK(‘GJ_AB’, ‘’, 1, sysdate-30,-1);
删除归档
1 根据时间删除
2 根据lsn删除
DM8
备份删除
1 添加和删除备份目录
2 手动删除备份集
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Yje8KZWS-1598861518821)(image-20200724105138195.png)]
SELECT SF_BAKSET_REMOVE(‘DISK’,’/home/dm_bak/db_bak_for_remove’,1); 级联删除
3 删除指定时间之前的备份集
BACKUP DATABASE FULL BACKUPSET ‘/home/dm_bak/db_bak_for_batch_del’;
SF_BAKSET_BACKUP_DIR_ADD(‘DISK’,’/home/dm_bak’);
call SP_DB_BAKSET_REMOVE_BATCH(‘DISK’,SYSDATE-30);
日志归档删除
与DM7一样
通过作业配置备份和删除策略
作业环境的创建和删除
SP_INIT_JOB_SYS(1); – 1创建
SP_INIT_JOB_SYS(0); – 删除
作业,作业步骤,作业调度的删除
作业
SP_DROP_JOB (JOB_NAME VARCHAR(128))
SP_DROP_JOB(‘TEST’);
作业步骤
SP_DROP_JOB_STEP(JOB_NAME VARCHAR(128), STEP_NAME VARCHAR(128))
作业调度
SP_DROP_JOB_SCHEDULE (
JOB_NAME VARCHAR(128),
SCHEDULE_NAME VARCHAR(128)
)
SP_DROP_JOB_SCHEDULE(‘TEST’, ‘SCHEDULE3’);
查看和清除作业日志历史
创建的每一个作业信息都存储在作业表 SYSJOBS 中。通过查看表 SYSJOBS，可以看到
所有已经创建的作业。
清除作业历史
SP_JOB_CLEAR_HISTORIES (
JOB_NAME VARCHAR(128)
)
SP_JOB_CLEAR_HISTORIES (‘TEST’); 清除迄今为止作业 TEST 的所有日志记录
基于备份集备份和删除(适用于dm7和dm8)
每周6的01:30全量备份
call SP_CREATE_JOB(‘JOB_FULL_BAK_TIMELY’,1,0,’’,0,0,’’,0,‘定时全量备份’);
call SP_JOB_CONFIG_START(‘JOB_FULL_BAK_TIMELY’);
call SP_ADD_JOB_STEP(‘JOB_FULL_BAK_TIMELY’, ‘STEP_FULL_BAK’, 6, ‘01000000G:\dm7\data\DMTEST1\bak’, 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE(‘JOB_FULL_BAK_TIMELY’, ‘SCHEDULE_FULL_BAK’, 1, 2, 1, 64, 0, ‘01:30:00’, NULL, ‘2019-10-29 21:00:57’, NULL, ‘’);
call SP_JOB_CONFIG_COMMIT(‘JOB_FULL_BAK_TIMELY’);
除周6每天02:30的增量备份
call SP_CREATE_JOB(‘JOB_INCREMENT_BAK_TIMELY_NIGHT’,1,0,’’,0,0,’’,0,‘定 时增量备份’);
call SP_JOB_CONFIG_START(‘JOB_INCREMENT_BAK_TIMELY_NIGHT’);
call SP_ADD_JOB_STEP(‘JOB_INCREMENT_BAK_TIMELY_NIGHT’, ‘STEP_INCREMENT_BAK’, 6, ‘11000000G:\dm7\data\DMTEST1\bak|G:\dm7\data\DMTEST1\bak’, 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE(‘JOB_INCREMENT_BAK_TIMELY_NIGHT’, ‘SCHEDULE_INCREMENT_BAK’, 1, 2, 1, 63, 0, ‘02:30:00’, NULL, ‘2020-07-30 10:33:44’, NULL, ‘’);
call SP_JOB_CONFIG_COMMIT(‘JOB_INCREMENT_BAK_TIMELY_NIGHT’);
删除30天前归档
call SP_CREATE_JOB(‘JOB_DEL_ARCH_TIMELY’,1,0,’’,0,0,’’,0,‘定时删除备份’);
call SP_JOB_CONFIG_START(‘JOB_DEL_ARCH_TIMELY’);
call SP_ADD_JOB_STEP(‘JOB_DEL_ARCH_TIMELY’, ‘STEP_DEL_ARCH’, 0, ‘SF_ARCHIVELOG_DELETE_BEFORE_TIME(SYSDATE – 30);’, 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE(‘JOB_DEL_ARCH_TIMELY’, ‘SCHEDULE_DEL_ARCH’, 1, 2, 1, 1, 0, ‘00:05:56’, NULL, ‘2019-10-29 21:00:57’, NULL, ‘’);
call SP_JOB_CONFIG_COMMIT(‘JOB_DEL_ARCH_TIMELY’);
删除30天前备份(全量和增量，库级别）
call SP_CREATE_JOB(‘JOB_DEL_BAK_TIMELY’,1,0,’’,0,0,’’,0,‘定时删除备份’);
call SP_JOB_CONFIG_START(‘JOB_DEL_BAK_TIMELY’);
call SP_ADD_JOB_STEP(‘JOB_DEL_BAK_TIMELY’, ‘STEP_DEL_BAK’, 0, ‘SP_DB_BAKSET_REMOVE_BATCH(’‘DISK’’, sysdate-30);’, 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE(‘JOB_DEL_BAK_TIMELY’, ‘SCHEDULE_DEL_BAK’, 1, 2, 1, 33, 0, ‘11:22:50’, NULL, ‘2019-10-29 21:00:57’, NULL, ‘’);
call SP_JOB_CONFIG_COMMIT(‘JOB_DEL_BAK_TIMELY’);
基于备份片备份和删除(适用于dm7)
–定时每周六运行，进行全量备份
call SP_CREATE_JOB(‘JOB_FULL_BAK_TIMELY’,1,0,’’,0,0,’’,0,‘定时全量备份’);
call SP_JOB_CONFIG_START(‘JOB_FULL_BAK_TIMELY’);
call SP_ADD_JOB_STEP(‘JOB_FULL_BAK_TIMELY’, ‘STEP_FULL_BAK’, 5，‘01000/dmdata/XUNWEN_OCR_AB/bak’, 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE(‘JOB_FULL_BAK_TIMELY’, ‘SCHEDULE_FULL_BAK’, 1, 2, 1, 64, 0, ‘00:05:56’, NULL, ‘2019-10-29 21:00:57’, NULL, ‘’);
call SP_JOB_CONFIG_COMMIT(‘JOB_FULL_BAK_TIMELY’);
–定时每天运行，进行增量备份(晚上,周六除外)
call SP_CREATE_JOB(‘JOB_INCREMENT_BAK_TIMELY_NIGHT’,1,0,’’,0,0,’’,0,’ 定时增量备份’);
call SP_JOB_CONFIG_START(‘JOB_INCREMENT_BAK_TIMELY_NIGHT’);
call SP_ADD_JOB_STEP(‘JOB_INCREMENT_BAK_TIMELY_NIGHT’, ‘STEP_INCREMENT_BAK’, 5, ‘11000/dmdata/XUNWEN_OCR_AB/bak|/dmdata/XUNWEN_OCR_AB/bak’, 1, 2, 0,0, NULL, 0);
call SP_ADD_JOB_SCHEDULE(‘JOB_INCREMENT_BAK_TIMELY_NIGHT’,‘SCHEDULE_INCREMENT_BAK’, 1, 2, 1, 63, 0, ‘01:05:56’, NULL, ‘2019-10-20 21:00:57’, NULL, ‘’);
call SP_JOB_CONFIG_COMMIT(‘JOB_INCREMENT_BAK_TIMELY_NIGHT’);
–定时每周日运行，删除前 30 天的备份，包括全量和增量
call SP_CREATE_JOB(‘JOB_DEL_BAK_TIMELY’,1,0,’’,0,0,’’,0,‘定时删除备份’);
call SP_JOB_CONFIG_START(‘JOB_DEL_BAK_TIMELY’);
call SP_ADD_JOB_STEP(‘JOB_DEL_BAK_TIMELY’, ‘STEP_DEL_BAK’, 0, ‘SP_BATCH_DEL_BAK(’‘XUNWEN_OCR’’, ‘’’’, 1, sysdate-30, -1);’, 1, 2, 0,0, NULL, 0);
call SP_ADD_JOB_SCHEDULE(‘JOB_DEL_BAK_TIMELY’, ‘SCHEDULE_DEL_BAK’, 1, 2, 1, 1, 0, ‘00:05:56’, NULL, ‘2019-10-29 21:00:57’, NULL, ‘’);
call SP_JOB_CONFIG_COMMIT(‘JOB_DEL_BAK_TIMELY’);
–定时每周日运行，删除前 30 天的归档日志
call SP_CREATE_JOB(‘JOB_DEL_ARCH_TIMELY’,1,0,’’,0,0,’’,0,‘定时删除备份’);
call SP_JOB_CONFIG_START(‘JOB_DEL_ARCH_TIMELY’);
call SP_ADD_JOB_STEP(‘JOB_DEL_ARCH_TIMELY’, ‘STEP_DEL_ARCH’, 0,‘SF_ARCHIVELOG_DELETE_BEFORE_TIME(SYSDATE – 30);’, 1, 2, 0, 0, NULL,0);
call SP_ADD_JOB_SCHEDULE(‘JOB_DEL_ARCH_TIMELY’,‘SCHEDULE_DEL_ARCH’, 1, 2, 1, 1, 0, ‘00:05:56’, NULL, ‘2019-10-29 21:00:57’, NULL, ‘’);
0,‘SF_ARCHIVELOG_DELETE_BEFORE_TIME(SYSDATE – 30);’, 1, 2, 0, 0, NULL,0);
call SP_ADD_JOB_SCHEDULE(‘JOB_DEL_ARCH_TIMELY’,‘SCHEDULE_DEL_ARCH’, 1, 2, 1, 1, 0, ‘00:05:56’, NULL, ‘2019-10-29 21:00:57’, NULL, ‘’);
call SP_JOB_CONFIG_COMMIT(‘JOB_DEL_ARCH_TIMELY’);