失眠网 > DM8:达梦数据库定时自动全库备份与删除备份

DM8:达梦数据库定时自动全库备份与删除备份

时间：2018-11-26 13:08:05

环境介绍1 备份条件2 测试手动备份是否成功2.1 报错 -7182.2 错误解决方法方法1：在数据库执行SQL方法2：重启数据库实例服务，再继续备份 3 创建代理环境4 配置作业4.1 新建定时自动备份作业：4.2 配置删除历史备份作业： 5 备份查询相关视图5.1 V$BACKUPSET查询结果：5.2 V$BACKUPSET_DBINFO 查询结果5.3 V$BACKUPSET_DBF 查询结果5.4 V$BACKUPSET_ARCH 查询结果5.5 V$BACKUPSET_BKP 查询结果5.6 V$BACKUPSET_SEARCH_DIRS 查询结果5.7 V$BACKUP_HISTORY 查询结果 6 使用备份文件还原数据库7 达梦数据库学习使用列表

环境介绍

备份很重要，建议备份的文件和数据库分开磁盘放置，避免放在一起时磁盘损坏导致数据库和备份一起丢失；请根据数据库占用磁盘空间规划备份空间，备份保留周期；可选择全库备份或全库备份+增量备份；备份时建议勾选压缩选项，默认压缩级别 1，节省备份磁盘空间；

1 备份条件

备份前的准备工作：备份之前需要先开归档！！！（归档配置点我）

2 测试手动备份是否成功

在库备份右键，新建备份

2.1 报错 -718

报错啦：

错误号：-718

错误消息：收集到的归档日志不连续

2.2 错误解决方法

方法1：在数据库执行SQL

CHECKPOINT(100);

方法2：重启数据库实例服务，再继续备份

backup database full to "DB_DAMENG5240_FULL__12_08_21_20_27" backupset 'DB_DAMENG5240_FULL__12_08_21_20_27';

3 创建代理环境

创建代理环境：SP_INIT_JOB_SYS(1);–与图形化界面效果一样

删除代理环境：SP_INIT_JOB_SYS(0); --谨慎操作

4 配置作业

4.1 新建定时自动备份作业：

定时自动备份数据库选择步骤类型

配置好的作业步骤

定时自动备份数据库配置作业调度

调度配置完成后如图

通过一系列的配置，最终的执行脚本

call SP_CREATE_JOB('db_bak',1,0,'',0,0,'',0,'');call SP_JOB_CONFIG_START('db_bak');call SP_ADD_JOB_STEP('db_bak', 'db_bak', 6, '00000000', 0, 0, 0, 0, NULL, 0);call SP_ADD_JOB_SCHEDULE('db_bak', 'db_bak', 1, 1, 1, 0, 0, '01:36:44', NULL, '-12-08 21:36:44', NULL, '');call SP_JOB_CONFIG_COMMIT('db_bak');

4.2 配置删除历史备份作业：

将 sql 放到作业里即可，末尾的 -8 意思是删除8天之前的历史备份，具体保存多久的历史备份，根据磁盘剩余空间大小，与项目现场的要求配置

CALL SP_DB_BAKSET_REMOVE_BATCH('DISK', NOW()-8);

定时自动删除历史备份--配置步骤类型与脚本

定时自动删除历史备份--配置调度策略

定时自动删除历史备份通过一系列配置，最终得到以下脚本

call SP_CREATE_JOB('del_dbbak',1,0,'',0,0,'',0,'');call SP_JOB_CONFIG_START('del_dbbak');call SP_ADD_JOB_STEP('del_dbbak', 'del_dbbak', 0, 'CALL SP_DB_BAKSET_REMOVE_BATCH(''DISK'', NOW()-8);', 0, 0, 0, 0, NULL, 0);call SP_ADD_JOB_SCHEDULE('del_dbbak', 'del_dbbak', 1, 1, 1, 0, 0, '22:04:40', NULL, '-12-08 22:04:40', NULL, '');call SP_JOB_CONFIG_COMMIT('del_dbbak');

定时自动备份数据库完成了

5 备份查询相关视图

备份管理相关动态视图总结如下

列举部分视图查询结果

5.1 V$BACKUPSET查询结果：

SQL> SELECT * FROM V$BACKUPSET;行号DEVICE_TYPE BACKUP_ID PARENT_ID BACKUP_NAME BACKUP_PATH TYPE LEVEL RANGE#---------- ----------- ----------- ----------- ----------- ---------------------------- ----------- ----------- -----------OBJECT_NAME OBJECT_ID BASE_NAME BACKUP_TIMEDESC# ENCRYPT_TYPE COMPRESS_LEVEL WITHOUT_LOG----------- ----------- --------- -------------------------- ----- ------------ -------------- -----------USE_PWRPKG_SIZE BEGIN_LSN END_LSN BKP_NUMDBF_NUMPARALLEL_NUM BAKSET_TYPE----------- ----------- -------------------- -------------------- ----------- ----------- ------------ -----------MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE MIN_DCT_VER DDL_CLONE----------- -------------------- ------------ ----------- ----------- -----------1DISK 170370612 -1DBBAK_73 /home/dmdba/dmdata/bak/DB_73 0 0 1dsc -1-03-20 10:14:07.327960 0 0 00 33554432 6703189 6704494 2 5 1 00 0134283521 0 4 0已用时间: 416.830(毫秒). 执行号:1565612.SQL>

5.2 V$BACKUPSET_DBINFO 查询结果

SQL> SELECT * FROM V$BACKUPSET_DBINFO;行号DEVICE_TYPE BACKUP_ID BACKUP_NAME BACKUP_PATH EXTENT_SIZE PAGE_SIZE LOG_PAGE_SIZE CASE_SENSITVE---------- ----------- ----------- ----------- ---------------------------- ----------- ----------- ------------- -------------DB_MAGIC PMNT_MAGIC UNICODE_FLAG DB_VERSION GLOBAL_VERSION ENABLE_POLICY ARCH_FLAG DSC_NODE PAGE_CHECK----------- ----------- ------------ ----------- -------------- ------------- ----------- ----------- -----------RLOG_ENCRYPT EX_CIPHER_NAME EX_CIPHER_ID EX_HASH_NAME EX_HASH_ID LENGTH_IN_CHAR USE_NEW_HASH BLANK_PAD_MODE------------ -------------- ------------ ------------ ----------- -------------- ------------ --------------SRC_DB_MAGIC------------1DISK 170370612 DBBAK_73 /home/dmdba/dmdata/bak/DB_73 3232768 512 0619384466 1082198939 1 458764V8 0 1 2 00 00 0 1 0619384466已用时间: 247.530(毫秒). 执行号:1565613.SQL>

5.3 V$BACKUPSET_DBF 查询结果

SQL> SELECT * FROM V$BACKUPSET_DBF;行号DEVICE_TYPE BACKUP_ID BACKUPNAME BACKUPPATH FILE_SEQ TS_ID FILE_IDTS_STATE---------- ----------- ----------- ---------- ---------------------------- ----------- ----------- ----------- -----------TS_NAME FILE_NAME MIRROR_PATH FILE_LEN MAX_LIMIT_SIZE AUTO_EXTEND NEXT_SIZE------- --------------------------- ----------- -------------------- -------------- ----------- -----------START_BKP_SEQ START_BKP_OFF END_BKP_SEQ END_BKP_OFF------------- -------------------- ----------- --------------------1DISK 170370612 DBBAK_73 /home/dmdba/dmdata/bak/DB_73 1 0 0 0SYSTEM +DMDATA/data/dsc/system.dbf 161480704 0 1 0NULLNULL NULL NULL行号DEVICE_TYPE BACKUP_ID BACKUPNAME BACKUPPATH FILE_SEQ TS_ID FILE_IDTS_STATE---------- ----------- ----------- ---------- ---------------------------- ----------- ----------- ----------- -----------TS_NAME FILE_NAME MIRROR_PATH FILE_LEN MAX_LIMIT_SIZE AUTO_EXTEND NEXT_SIZE------- --------------------------- ----------- -------------------- -------------- ----------- -----------START_BKP_SEQ START_BKP_OFF END_BKP_SEQ END_BKP_OFF------------- -------------------- ----------- --------------------2DISK 170370612 DBBAK_73 /home/dmdba/dmdata/bak/DB_73 2 1 0 0ROLL +DMDATA/data/dsc/roll.dbf249561088 0 1 0NULLNULL NULL NULL行号DEVICE_TYPE BACKUP_ID BACKUPNAME BACKUPPATH FILE_SEQ TS_ID FILE_IDTS_STATE---------- ----------- ----------- ---------- ---------------------------- ----------- ----------- ----------- -----------TS_NAME FILE_NAME MIRROR_PATH FILE_LEN MAX_LIMIT_SIZE AUTO_EXTEND NEXT_SIZE------- --------------------------- ----------- -------------------- -------------- ----------- -----------START_BKP_SEQ START_BKP_OFF END_BKP_SEQ END_BKP_OFF------------- -------------------- ----------- --------------------3DISK 170370612 DBBAK_73 /home/dmdba/dmdata/bak/DB_73 3 4 0 0MAIN +DMDATA/data/dsc/main.dbf134217728 0 1 0NULLNULL NULL NULL已用时间: 284.245(毫秒). 执行号:1565614.SQL>

5.4 V$BACKUPSET_ARCH 查询结果

SQL> SELECT * FROM V$BACKUPSET_ARCH;行号DEVICE_TYPE BACKUP_ID BACKUPNAME BACKUPPATH FILE_SEQ FILE_NAME FILE_LEN---------- ----------- ----------- ---------- ---------------------------- ----------- --------- --------------------DSC_SEQNO BEGIN_SEQNOBEGIN_LSN END_SEQNO END_LSN----------- -------------------- -------------------- -------------------- --------------------CREATE_TIME CLOSE_TIME START_BKP_SEQ START_BKP_OFF END_BKP_SEQ END_BKP_OFF----------------------- ----------------------- ------------- -------------------- ----------- --------------------1DISK 170370612 DBBAK_73 /home/dmdba/dmdata/bak/DB_73 4 1628160 1142892 6703189 1143109 67044940-00-00 00:00:00.000000 0-00-00 00:00:00.000000 NULLNULL NULL NULL行号DEVICE_TYPE BACKUP_ID BACKUPNAME BACKUPPATH FILE_SEQ FILE_NAME FILE_LEN---------- ----------- ----------- ---------- ---------------------------- ----------- --------- --------------------DSC_SEQNO BEGIN_SEQNOBEGIN_LSN END_SEQNO END_LSN----------- -------------------- -------------------- -------------------- --------------------CREATE_TIME CLOSE_TIME START_BKP_SEQ START_BKP_OFF END_BKP_SEQ END_BKP_OFF----------------------- ----------------------- ------------- -------------------- ----------- --------------------2DISK 170370612 DBBAK_73 /home/dmdba/dmdata/bak/DB_73 5 291841 4464756704248 44651467044930-00-00 00:00:00.000000 0-00-00 00:00:00.000000 NULLNULL NULL NULL已用时间: 225.422(毫秒). 执行号:1565615.SQL>

5.5 V$BACKUPSET_BKP 查询结果

SQL> SELECT * FROM V$BACKUPSET_BKP;行号DEVICE_TYPE BACKUP_ID BACKUPNAME BACKUPPATH BKP_NTHFILE_NAME BKP_LEN---------- ----------- ----------- ---------- ---------------------------- ----------- ----------- --------------------1DISK 170370612 DBBAK_73 /home/dmdba/dmdata/bak/DB_73 0 DB_73.bak 1662740482DISK 170370612 DBBAK_73 /home/dmdba/dmdata/bak/DB_73 1 DB_73_1.bak 197120已用时间: 219.501(毫秒). 执行号:1565616.SQL>

5.6 V$BACKUPSET_SEARCH_DIRS 查询结果

SQL> SELECT * FROM V$BACKUPSET_SEARCH_DIRS;行号DIR---------- ----------------------1/home/dmdba/dmdata/bak已用时间: 0.215(毫秒). 执行号:1565617.SQL>

5.7 V$BACKUP_HISTORY 查询结果

SQL> SELECT * FROM V$BACKUP_HISTORY;行号PATHSTART_TIME END_TIME READ_SIZE---------- ---------------------------- -------------------------- -------------------------- --------------------WRITE_SIZE AVG_READ MIN_READ MAX_READ AVG_WRITE MIN_WRITE MAX_WRITE-------------------- -------- -------- -------- --------- --------- ---------1/home/dmdba/dmdata/bak/DB_73 -03-20 10:13:57.000000 -03-20 10:14:07.000000 295698432166456832 29.75 0 117.07 16.750 62.99已用时间: 23.909(毫秒). 执行号:1565618.SQL>