DM8共享集群数据库备份的设置

DM8集群备份设置指南

最新推荐文章于 2025-12-04 15:25:15 发布

原创最新推荐文章于 2025-12-04 15:25:15 发布 · 275 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

达梦数据库（DM8）专栏收录该内容

8 篇文章

订阅专栏

背景：

近期项目使用了DM8DSC集群，需要有设置完整备份及恢复方案。(近期文档可能会有点错乱)

备份方案：周全备，日增量，定期删除过期的备份文件，定期收集统计信息。

操作：

本次操作均在客户端进行（SQlark百灵上操作）

连接数据库，在SQL窗口或命令行窗口执行(按需调整备份时间及目录)，注意：commit，即可：

SP_INIT_JOB_SYS(0);
SP_INIT_JOB_SYS(1);
--定时每周六运行，进行全量备份
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', 'JOB_FULL_BAK_TIMELY', 6, '01000000/dmbak/DAMENG', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('JOB_FULL_BAK_TIMELY', 'JOB_FULL_BAK_TIMELY', 1, 2, 1, 64, 0, '00:30:00', NULL, '2025-11-07 12:06:08', 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', 'JOB_INCREMENT_BAK_TIMELY_NIGHT', 6, '11000000/dmbak/DAMENG', 1, 3, 0, 0, NULL, 0);
call SP_ADD_JOB_STEP('JOB_INCREMENT_BAK_TIMELY_NIGHT', 'JOB_FULL_BAK', 6, '01000000/dmbak/DAMENG', 1, 1, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('JOB_INCREMENT_BAK_TIMELY_NIGHT', 'JOB_INCREMENT_BAK_TIMELY_NIGHT', 1, 2, 1, 63, 0, '01:00:00', NULL, '2025-11-07 12:06:08', 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', 'JOB_DEL_BAK_TIMELY', 0, 'SF_BAKSET_BACKUP_DIR_ADD(''DISK'',''/dmbak/DAMENG'');
CALL SP_DB_BAKSET_REMOVE_BATCH(''DISK'',SYSDATE - 15);', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('JOB_DEL_BAK_TIMELY', 'JOB_DEL_BAK_TIMELY', 1, 1, 1, 0, 0, '00:10:10', NULL, '2025-11-07 12:06:08', NULL, '');
call SP_JOB_CONFIG_COMMIT('JOB_DEL_BAK_TIMELY');

--call SP_DROP_JOB('statistics');
call SP_CREATE_JOB('statistics',1,0,'',0,0,'',0,'每周六凌晨2点开始收集所有列统计信息');
call SP_JOB_CONFIG_START('statistics');
call SP_ADD_JOB_STEP('statistics', 'statistics1', 0, 'begin
    for rs in (select ''sf_set_SESSION_para_value(''''HAGR_HASH_SIZE'''',
                      (select cast( case when max(table_rowcount(owner,table_name))<=(select max_value from v$dm_ini where para_Name=''''HAGR_HASH_SIZE'''') and max(table_rowcount(owner,table_name))>=( select min_value from v$dm_ini where para_Name=''''HAGR_HASH_SIZE'''') then max(table_rowcount(owner,table_name))
                                         when max(table_rowcount(owner,table_name))<( select min_value from v$dm_ini where para_Name=''''HAGR_HASH_SIZE'''') then (select min_value from v$dm_ini where para_Name=''''HAGR_HASH_SIZE'''')
                                         else (select max_value from v$dm_ini where para_Name=''''HAGR_HASH_SIZE'''')
                                    end as bigint)
                      from dba_tables
                      where owner=''''''||NAME||''''''));'' sql1,
                      ''DBMS_STATS.GATHER_SCHEMA_STATS(''''''||NAME||'''''',100,TRUE,''''FOR ALL COLUMNS SIZE AUTO'''');'' sql2
              from SYS.SYSOBJECTS
              where TYPE$=''SCH'')
    loop
        execute immediate rs.sql1;
        execute immediate rs.sql2;
    end loop;
end;', 0, 0, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('statistics', 'statistics1', 1, 2, 1, 64, 0, '02:00:00', NULL, '2025-11-07 12:06:08', NULL, '');
call SP_JOB_CONFIG_COMMIT('statistics');

查看备份信息及手动调用备份

或者：

--手动调用备份
SELECT * FROM SYSJOB.SYSJOBS;
SP_DBMS_JOB_RUN(ID);