DM8 物理备份还原实操
数据库不同状态下支持的备份还原操作如下表：
| 状态 | 备份粒度 | 全量备份 | 增量备份 | 还原 | 恢复 | |
|---|---|---|---|---|---|---|
| 联机 | 库 | ✅ | ✅ | 恢复一致性 | 更新 DB_MAGIC | |
| 表空间 | ✅ | ✅ | ||||
| 表 | ✅ | ✅ | ||||
| 归档 | ✅ | |||||
| 脱机 | 库 | ✅ | ✅ | ✅ | ✅ | ✅ |
| 表空间 | ✅ | ✅ | ✅ | |||
| 表 | ||||||
| 归档 | ✅ | ✅ |
1 联机备份还原实操
1.1 数据库备份
-
查看备份路径
SELECT PARA_NAME, PARA_VALUE FROM V$DM_INI WHERE PARA_NAME in ('SYSTEM_PATH','BAK_PATH'); 11:33:39 SYSDBA@DTS:5238 SQL> SELECT PARA_NAME, PARA_VALUE FROM V$DM_INI WHERE PARA_NAME in ('SYSTEM_PATH','BAK_PATH');2 3 PARA_NAME PARA_VALUE ----------- ------------------ SYSTEM_PATH /db/dmdata/DTS BAK_PATH /db/dmdata/DTS/bak -
执行全量备份及增量
BACKUP DATABASE FULL TO DB_FULL_SC_0806 BACKUPSET '/db/dmbak/DTS/DB_FULL_SC' BACKUPINFO '数据库全量备份实操测试' PARALLEL 2; BACKUP DATABASE INCREMENT WITH BACKUPDIR '/db/dmbak/DTS/DB_FULL_SC' TO DB_INCRE_SC_0806 BACKUPSET '/db/dmbak/DTS/DB_INCRE_SC' BACKUPINFO '数据库增量备份实操测试' PARALLEL 2; 14:21:38 SYSDBA@DTS:5238 SQL> BACKUP DATABASE FULL TO DB_FULL_SC_0806 BACKUPSET '/db/dmbak/DTS/DB_FULL_SC' BACKUPINFO '数据库全量备份实操测试' PARALLEL 2; 操作已执行 已用时间: 00:00:06.814. 执行号:6415. SQL> BACKUP DATABASE INCREMENT WITH BACKUPDIR '/db/dmbak/DTS/DB_FULL_SC' TO DB_INCRE_SC_0806 BACKUPSET '/db/dmbak/DTS/DB_INCRE_SC' BACKUPINFO '数据库增量备份实操测试' PARALLEL 2; 操作已执行 已用时间: 00:00:06.710. 执行号:6422. 14:43:10 SYSDBA@DTS:5238 SQL> -
查看数据库备份集
select SF_BAKSET_BACKUP_DIR_ADD('DISK','/db/dmbak/DTS/DB_FULL_SC'); select SF_BAKSET_BACKUP_DIR_ADD('DISK','/db/dmbak/DTS/DB_INCRE_SC'); select OBJECT_NAME，BACKUP_NAME,BACKUP_PATH,TYPE,RANGE# from V$BACKUPSET where RANGE# =1 ; 14:44:18 SYSDBA@DTS:5238 SQL> select OBJECT_NAME，BACKUP_NAME,BACKUP_PATH,TYPE,RANGE# from V$BACKUPSET where RANGE# =1 ; OBJECT_NAME BACKUP_NAME BACKUP_PATH TYPE RANGE# ----------- ------------------------------ --------------------------------------- ----------- ----------- DTS DB_FULL_SC_0806 /db/dmbak/DTS/DB_FULL_SC 0 1 DTS DB_FULL_SC_0806 /db/dmbak/DTS/DB_FULL_SC/DB_FULL_SC_1 0 1 DTS DB_FULL_SC_0806 /db/dmbak/DTS/DB_FULL_SC/DB_FULL_SC_0 0 1 DTS DB_INCRE_SC_0806 /db/dmbak/DTS/DB_INCRE_SC 1 1 DTS DB_INCRE_SC_0806 /db/dmbak/DTS/DB_INCRE_SC/DB_INCRE_SC_1 1 1 DTS DB_INCRE_SC_0806 /db/dmbak/DTS/DB_INCRE_SC/DB_INCRE_SC_0 1 1 DTS DB_FULL_20250806_111654_135920 /db/dmdata/DTS/bak/db_full_bak_02 0 1 -
查看备份目录
14:25:18 SYSDBA@DTS:5238 SQL> HOST ls -al /db/dmbak/DTS/DB_FULL_SC 总用量 336 drwxr-xr-x 4 dmdba dinstall 4096 8月 6 14:22 . drwxr-xr-x 5 dmdba dinstall 4096 8月 6 14:22 .. drwxr-xr-x 2 dmdba dinstall 4096 8月 6 14:22 DB_FULL_SC_0 drwxr-xr-x 2 dmdba dinstall 4096 8月 6 14:22 DB_FULL_SC_1 -rw-r--r-- 1 dmdba dinstall 13312 8月 6 14:22 DB_FULL_SC.bak -rw-r--r-- 1 dmdba dinstall 307712 8月 6 14:22 DB_FULL_SC.meta 14:44:23 SYSDBA@DTS:5238 SQL> HOST ls -al /db/dmbak/DTS/DB_INCRE_SC 总用量 332 drwxr-xr-x 4 dmdba dinstall 4096 8月 6 14:43 . drwxr-xr-x 6 dmdba dinstall 4096 8月 6 14:43 .. drwxr-xr-x 2 dmdba dinstall 4096 8月 6 14:43 DB_INCRE_SC_0 drwxr-xr-x 2 dmdba dinstall 4096 8月 6 14:43 DB_INCRE_SC_1 -rw-r--r-- 1 dmdba dinstall 13312 8月 6 14:43 DB_INCRE_SC.bak -rw-r--r-- 1 dmdba dinstall 303616 8月 6 14:43 DB_INCRE_SC.meta
1.2 表空间备份
-
执行表空间全量以及增量备份
--全量 BACKUP TABLESPACE TEST_TBS FULL TO TBS_TEST_TBS_FULL_SC_0806 BACKUPSET '/db/dmbak/DTS/TBS_TEST_TBS_FULL_SC_0806' BACKUPINFO '表空间备份实操测试'; --增量 BACKUP TABLESPACE TEST_TBS INCREMENT WITH BACKUPDIR '/db/dmbak/DTS/TBS_TEST_TBS_FULL_SC_0806' TO TBS_TEST_TBS_INCRE_SC_0806 BACKUPSET '/db/dmbak/DTS/TBS_TEST_TBS_INCRE_SC_0806' BACKUPINFO '表空间备份实操测试'; 14:53:57 SYSDBA@DTS:5238 SQL> BACKUP TABLESPACE TEST_TBS FULL TO TBS_TEST_TBS_FULL_SC_0806 BACKUPSET '/db/dmbak/DTS/TBS_TEST_TBS_FULL_SC_0806' BACKUPINFO '表空间备份实操测试'; 操作已执行 已用时间: 00:00:05.038. 执行号:6618. 14:55:18 SYSDBA@DTS:5238 SQL> BACKUP TABLESPACE TEST_TBS INCREMENT WITH BACKUPDIR '/db/dmbak/DTS/TBS_TEST_TBS_FULL_SC_0806' TO TBS_TEST_TBS_INCRE_SC_0806 BACKUPSET '/db/dmbak/DTS/TBS_TEST_TBS_INCRE_SC_0806' BACKUPINFO '表空间备份实操测试'; 操作已执行 已用时间: 00:00:09.051. 执行号:6619. -
查看表空间备份集
select SF_BAKSET_BACKUP_DIR_ADD('DISK','/db/dmbak/DTS/TBS_TEST_TBS_FULL_SC_0806'); select SF_BAKSET_BACKUP_DIR_ADD('DISK','/db/dmbak/DTS/TBS_TEST_TBS_INCRE_SC_0806'); select OBJECT_NAME，BACKUP_NAME,BACKUP_PATH,TYPE,RANGE# from V$BACKUPSET where RANGE# =2; 15:01:41 SYSDBA@DTS:5238 SQL> select OBJECT_NAME，BACKUP_NAME,BACKUP_PATH,TYPE,RANGE# from V$BACKUPSET where RANGE# =2; OBJECT_NAME BACKUP_NAME BACKUP_PATH TYPE RANGE# ----------- -------------------------- ---------------------------------------- ----------- ----------- TEST_TBS TBS_TEST_TBS_FULL_SC_0806 /db/dmbak/DTS/TBS_TEST_TBS_FULL_SC_0806 0 2 TEST_TBS TBS_TEST_TBS_INCRE_SC_0806 /db/dmbak/DTS/TBS_TEST_TBS_INCRE_SC_0806 1 2 -
查看备份目录
15:02:25 SYSDBA@DTS:5238 SQL> HOST ls -al /db/dmbak/DTS 总用量 28 drwxr-xr-x 7 dmdba dinstall 4096 8月 6 14:55 . drwxr-xr-x 6 dmdba dinstall 4096 8月 6 12:00 .. drwxr-xr-x 4 dmdba dinstall 4096 8月 6 14:22 DB_FULL_SC drwxr-xr-x 4 dmdba dinstall 4096 8月 6 14:43 DB_INCRE_SC drwxr-xr-x 2 dmdba dinstall 4096 7月 30 14:45 sqllog drwxr-xr-x 2 dmdba dinstall 4096 8月 6 14:54 TBS_TEST_TBS_FULL_SC_0806 drwxr-xr-x 2 dmdba dinstall 4096 8月 6 14:55 TBS_TEST_TBS_INCRE_SC_0806
1.3 表备份
-
执行表备份
BACKUP TABLE DTSTEST.TEST_TABLE TO TB_TEST_TABLE_SC_0806 BACKUPSET '/db/dmbak/DTS/TB_TEST_TABLE_SC_0806' BACKUPINFO '表备份实操测试'; 15:06:34 SYSDBA@DTS:5238 SQL> BACKUP TABLE DTSTEST.TEST_TABLE TO TB_TEST_TABLE_SC_0806 BACKUPSET '/db/dmbak/DTS/TB_TEST_TABLE_SC_0806' BACKUPINFO '表备份实操测试'; 操作已执行 已用时间: 00:00:01.054. 执行号:6632. -
查看表备份集
select SF_BAKSET_BACKUP_DIR_ADD('DISK','/db/dmbak/DTS/TB_TEST_TABLE_SC_0806'); select OBJECT_NAME，BACKUP_NAME,BACKUP_PATH,TYPE,RANGE# from V$BACKUPSET where RANGE# =3; 15:07:16 SYSDBA@DTS:5238 SQL> OBJECT_NAME BACKUP_NAME BACKUP_PATH TYPE RANGE# ------------------ ------------------------------- ----------------------------------- ----------- ----------- DTSTEST.TEST_TABLE TB_TEST_TABLE_SC_0806 /db/dmbak/DTS/TB_TEST_TABLE_SC_0806 2 3 SYSDBA.TAB_01 TAB_FULL_20250805_180850_887501 /db/dmdata/DTS/bak/tab_bak_01 2 3 -
查看备份目录
15:07:19 SYSDBA@DTS:5238 SQL> HOST ls -al /db/dmbak/DTS 总用量 32 drwxr-xr-x 8 dmdba dinstall 4096 8月 6 15:06 . drwxr-xr-x 6 dmdba dinstall 4096 8月 6 12:00 .. drwxr-xr-x 4 dmdba dinstall 4096 8月 6 14:22 DB_FULL_SC drwxr-xr-x 4 dmdba dinstall 4096 8月 6 14:43 DB_INCRE_SC drwxr-xr-x 2 dmdba dinstall 4096 7月 30 14:45 sqllog drwxr-xr-x 2 dmdba dinstall 4096 8月 6 14:54 TBS_TEST_TBS_FULL_SC_0806 drwxr-xr-x 2 dmdba dinstall 4096 8月 6 14:55 TBS_TEST_TBS_INCRE_SC_0806 drwxr-xr-x 2 dmdba dinstall 4096 8月 6 15:06 TB_TEST_TABLE_SC_0806
1.4 表还原
-
清空表
16:22:50 SYSDBA@DTS:5238 SQL> truncate table DTSTEST.TEST_TABLE; 操作已执行 已用时间: 14.777(毫秒). 执行号:6672. 16:22:53 SYSDBA@DTS:5238 SQL> select count(*) from DTSTEST.TEST_TABLE; COUNT(*) -------------------- 0 已用时间: 0.516(毫秒). 执行号:6673. -
表还原
RESTORE TABLE DTSTEST.TEST_TABLE FROM BACKUPSET '/db/dmbak/DTS/TB_TEST_TABLE_SC_0806'; --若表中含有索引约束需要先还原表结构再还原表数据 RESTORE TABLE DTSTEST.TEST_TABLE STRUCT WITHOUT CONSTRAINT FROM BACKUPSET '/db/dmbak/DTS/TB_TEST_TABLE_SC_0806'; RESTORE TABLE DTSTEST.TEST_TABLE WITHOUT CONSTRAINT FROM BACKUPSET '/db/dmbak/DTS/TB_TEST_TABLE_SC_0806'; -
查看数据
16:23:25 SYSDBA@DTS:5238 SQL> select count(*) from DTSTEST.TEST_TABLE; COUNT(*) -------------------- 10 已用时间: 1.757(毫秒). 执行号:6676. -
问题处理
-
报错
[-8327]:还原表中存在二级索引或冗余约束. -
原因分析：目标数据库中已存在该表的二级索引或约束，导致还原失败。
-
解决方案：如果直接将备份表作为还原表，由于备份表上存在约束，因此需要首先使用 RESTORE TABLE…STRUCT WITHOUT CONSTRAINT…语句还原备份表的表结构，去除表中约束后才能以不重建约束的方式还原表数据
-
1.5 归档备份
-
执行归档备份
--全量 BACKUP ARCHIVELOG ALL TO ARCH_ALL_SC_0806 BACKUPSET '/db/dmbak/DTS/ARCH_ALL_SC_0806' BACKUPINFO '归档全量备份实操测试'; --范围 BACKUP ARCHIVELOG FROM TIME '2025-08-06 10:00:00' TO ARCH_FW_SC_0806 BACKUPSET '/db/dmbak/DTS/ARCH_FW_SC_0806' BACKUPINFO '归档范围备份实操测试'; -
查看归档备份集
select SF_BAKSET_BACKUP_DIR_ADD('DISK','/db/dmbak/DTS/ARCH_ALL_SC_0806'); select SF_BAKSET_BACKUP_DIR_ADD('DISK','/db/dmbak/DTS/ARCH_FW_SC_0806'); select OBJECT_NAME，BACKUP_NAME,BACKUP_PATH,TYPE,RANGE# from V$BACKUPSET where RANGE# =4; 15:42:45 SYSDBA@DTS:5238 SQL> OBJECT_NAME BACKUP_NAME BACKUP_PATH TYPE RANGE# ----------- ---------------- ------------------------------ ----------- ----------- DTS ARCH_ALL_SC_0806 /db/dmbak/DTS/ARCH_ALL_SC_0806 3 4 DTS ARCH_FW_SC_0806 /db/dmbak/DTS/ARCH_FW_SC_0806 3 4 -
查看备份目录
15:43:43 SYSDBA@DTS:5238 SQL> HOST ls -al /db/dmbak/DTS 总用量 40 drwxr-xr-x 10 dmdba dinstall 4096 8月 6 15:42 . drwxr-xr-x 6 dmdba dinstall 4096 8月 6 12:00 .. drwxr-xr-x 2 dmdba dinstall 4096 8月 6 15:24 ARCH_ALL_SC_0806 drwxr-xr-x 2 dmdba dinstall 4096 8月 6 15:42 ARCH_FW_SC_0806 drwxr-xr-x 4 dmdba dinstall 4096 8月 6 14:22 DB_FULL_SC drwxr-xr-x 4 dmdba dinstall 4096 8月 6 14:43 DB_INCRE_SC drwxr-xr-x 2 dmdba dinstall 4096 7月 30 14:45 sqllog drwxr-xr-x 2 dmdba dinstall 4096 8月 6 14:54 TBS_TEST_TBS_FULL_SC_0806 drwxr-xr-x 2 dmdba dinstall 4096 8月 6 14:55 TBS_TEST_TBS_INCRE_SC_0806 drwxr-xr-x 2 dmdba dinstall 4096 8月 6 15:06 TB_TEST_TABLE_SC_0806
1.6 备份策略配置
全备：周六 23 点,备份保留 30 天，删除过期备份
–增备：周日-周五 23 点
备份路径：/db/dmbak
--备份位置是/db/dmbak，根据实际情况修改脚本路径
sp_init_job_sys(1);
--全备：周六23点,备份保留30天，删除过期备份
call SP_CREATE_JOB('DM_FULL_DEL_BAK',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('DM_FULL_DEL_BAK');
call SP_ADD_JOB_STEP('DM_FULL_DEL_BAK', 'FULL_BAK', 6, '01000000/db/dmbak', 3, 1, 0, 0, NULL, 0);
call SP_ADD_JOB_STEP('DM_FULL_DEL_BAK', 'DEL_BAK', 0, 'SF_BAKSET_BACKUP_DIR_ADD(''DISK'',''/db/dmbak'');
call SP_DB_BAKSET_REMOVE_BATCH(''DISK'',SYSDATE-30);', 1, 1, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('DM_FULL_DEL_BAK', 'FULL2', 1, 2, 1, 64, 0, '23:00:00', NULL, '2025-08-06 00:00:00', NULL, '');
call SP_JOB_CONFIG_COMMIT('DM_FULL_DEL_BAK');
--增备：周日-周五 23点
call SP_CREATE_JOB('DM_INC_BAK',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('DM_INC_BAK');
call SP_ADD_JOB_STEP('DM_INC_BAK', 'INC1', 6, '11000000/db/dmbak|/db/dmbak', 1, 3, 0, 0, NULL, 0);
call SP_ADD_JOB_STEP('DM_INC_BAK', 'FULL1', 6, '01000000/db/dmbak', 1, 1, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('DM_INC_BAK', 'INC2', 1, 2, 1, 63, 0, '23:00:00', NULL, '2025-08-06 00:00:00', NULL, '');
call SP_JOB_CONFIG_COMMIT('DM_INC_BAK');
select job,next_sec,what from SYSJOB.DBA_JOBS;
call SP_DBMS_JOB_RUN(1754555680);--测试全量备份作业
call SP_DBMS_JOB_RUN(1754555681);--测试增量备份作业
进去到备份目录，确认备份是否生成成功/db/dmbak
[dmdba@dm_dbtest2:/home/dmdba]$ ll /db/dmbak
总用量 24
drwxr-xr-x 2 dmdba dinstall 4096 7月 30 15:38 DAMENG
drwxr-xr-x 2 dmdba dinstall 4096 8月 7 17:04 DB_DTS_FULL_2025_08_07_17_04_37
drwxr-xr-x 2 dmdba dinstall 4096 8月 7 17:04 DB_DTS_INCREMENT_2025_08_07_17_04_43
查看作业日志，确认执行情况:
select jobid,
exec_id,
name,
stepname,
start_time,
end_time,
errcode,
errinfo
from sysjob.sysstephistories2
order by start_time desc;
--删除作业
CALL SP_DROP_JOB('DM_FULL_DEL_BAK');
CALL SP_DROP_JOB('DM_INC_BAK');
2 备份还原实操
一般是使用联机的备份集进行恢复
2.1 数据库备份还原
2.1.1 数据库备份还原实操
2.1.1.1 全量备份还原
全量备份准备
-
备份还原相关参数查看
SELECT NAME,VALUE,DESCRIPTION FROM SYS."V$PARAMETER" A WHERE A.NAME IN('BAK_PATH','BAK_USE_AP'); 17:57:17 SYSDBA@DTS:5238 SQL> SELECT NAME,VALUE,DESCRIPTION FROM SYS."V$PARAMETER" A WHERE A.NAME IN('BAK_PATH','BAK_USE_AP'); NAME VALUE DESCRIPTION ---------- ------------------ ----------------------------------------------------------------------------------- BAK_PATH /db/dmdata/DTS/bak backup file path BAK_USE_AP 1 backup use assistant plus-in, 0:use sub process; 1:use AP; 2:not use AP. default 1. -
查看 AP 服务状态
[dmdba@dm_dbtest2:/db/dm/log]$ ps -ef|grep dmap dmdba 1505 1 0 8月04 ? 00:00:16 /db/dm/bin/dmap dmap_ini=/db/dm/bin/dmap.ini dmdba 32217 10621 0 17:58 pts/0 00:00:00 grep --color=auto dmap -
备份数据库
此处使用联机备份
--使用联机备份 BACKUP DATABASE FULL TO DB_FULL_BFHY_0807 BACKUPSET '/db/dmbak/DTS/DB_FULL_BFHY_0807' BACKUPINFO '数据库备份还原测试'; --使用脱机备份 BACKUP DATABASE '/db/dmdata/DTS/dm.ini' FULL TO DB_FULL_BFHY_0807 BACKUPSET '/db/dmbak/DTS/DB_FULL_BFHY_0807' BACKUPINFO '数据库备份还原测试'; -
模拟数据库损坏
注： 生产环境禁止以下操作
-
删除 TEST_TBS 表空间，并重启数据库
[dmdba@dm_dbtest2:/db/dm]$ cd /db/dmdata/DTS/ [dmdba@dm_dbtest2:/db/dmdata/DTS]$ rm -f /db/dmdata/DTS/TEST_TBS.DBF [dmdba@dm_dbtest2:/db/dmdata/DTS]$ exit 登出 [root@dm_dbtest2:/root]# systemctl restart DmServiceDTSTEST.service [root@dm_dbtest2:/root]# systemctl status DmServiceDTSTEST.service ● DmServiceDTSTEST.service - DM Instance Service(DmServiceDTSTEST). Loaded: loaded (/usr/lib/systemd/system/DmServiceDTSTEST.service; enabled; vendor preset: disabled) Active: active (running) since 四 2025-08-07 18:01:55 CST; 9s ago Process: 708 ExecStop=/db/dm/bin/DmServiceDTSTEST stop (code=exited, status=0/SUCCESS) Process: 793 ExecStart=/db/dm/bin/DmServiceDTSTEST start (code=exited, status=0/SUCCESS) Main PID: 828 (dmserver) CGroup: /system.slice/DmServiceDTSTEST.service └─828 /db/dm/bin/dmserver path=/db/dmdata/DTS/dm.ini -noconsole 8月 07 18:01:40 dm_dbtest2 systemd[1]: Starting DM Instance Service(DmServiceDTSTEST).... 8月 07 18:01:55 dm_dbtest2 DmServiceDTSTEST[793]: [38B blob data] 8月 07 18:01:55 dm_dbtest2 systemd[1]: Started DM Instance Service(DmServiceDTSTEST).. -
查看数据库日志
可以看到日志显示，由于/db/dmdata/DTS/TEST_TBS.DBF 数据文件不存在，被抢走 offline 了
/db/dmdata/DTS/TEST_TBS.DBF not exist,force ts[5, TEST_TBS] to be off-line User data files missed, Force to startup MOUNT -
重启后数据库处于 MOUNT 状态，手动切换到 OPEN
ALTER DATABASE OPEN; -
查看表数据
select * from DTSTEST.TEST_TABLE; :11:37 SYSDBA@DTS:5238 SQL> select * from DTSTEST.TEST_TABLE; * 第 1 行, 第 3 列[:]附近出现错误[-2007]:语法分析出错. 已用时间: 0.225(毫秒). 执行号:0. 18:11:50 SYSDBA@DTS:5238 SQL> select * from DTSTEST.TEST_TABLE; [-3433]:表空间处于脱机状态. 已用时间: 1.092(毫秒). 执行号:0. -
查看表空间状态
SELECT NAME,STATUS$ FROM V$TABLESPACE; 18:12:36 SYSDBA@DTS:5238 SQL> SELECT NAME,STATUS$ FROM V$TABLESPACE; NAME STATUS$ -------- ----------- SYSTEM 0 ROLL 0 TEMP 0 MAIN 0 TEST_TBS 1 -
停止数据库
systemctl stop DmServiceDTSTEST.service
-
全量还原恢复
-
校验备份
CHECK BACKUPSET '/db/dmbak/DTS/DB_FULL_BFHY_0807'; RMAN> CHECK BACKUPSET '/db/dmbak/DTS/DB_FULL_BFHY_0807'; CHECK BACKUPSET '/db/dmbak/DTS/DB_FULL_BFHY_0807'; [Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00] check backupset successfully. time used: 214.741(ms) -
还原数据库
RESTORE DATABASE '/db/dmdata/DTS/dm.ini' FROM BACKUPSET '/db/dmbak/DTS/DB_FULL_BFHY_0807'; RMAN> RESTORE DATABASE '/db/dmdata/DTS/dm.ini' FROM BACKUPSET '/db/dmbak/DTS/DB_FULL_BFHY_0807'; RESTORE DATABASE '/db/dmdata/DTS/dm.ini' FROM BACKUPSET '/db/dmbak/DTS/DB_FULL_BFHY_0807'; file dm.key not found, use default license! [Percent:100.00%][Speed:0.00M/s][Cost:00:00:03][Remaining:00:00:00] restore successfully. time used: 00:00:03.346 -
恢复数据库
RECOVER DATABASE '/db/dmdata/DTS/dm.ini' FROM BACKUPSET '/db/dmbak/DTS/DB_FULL_BFHY_0807'; RMAN> RECOVER DATABASE '/db/dmdata/DTS/dm.ini' FROM BACKUPSET '/db/dmbak/DTS/DB_FULL_BFHY_0807'; RECOVER DATABASE '/db/dmdata/DTS/dm.ini' FROM BACKUPSET '/db/dmbak/DTS/DB_FULL_BFHY_0807'; [Percent:100.00%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00] recover successfully! time used: 00:00:03.226 -
更新数据库魔术
RECOVER DATABASE '/db/dmdata/DTS/dm.ini' update db_magic; RMAN> RECOVER DATABASE '/db/dmdata/DTS/dm.ini' update db_magic; RECOVER DATABASE '/db/dmdata/DTS/dm.ini' update db_magic; recover successfully! time used: 00:00:01.224 -
启动数据库
[root@dm_dbtest2:/db/dm/log]# systemctl start DmServiceDTSTEST.service-
查看表空间状态
-
SELECT NAME,STATUS$ FROM V$TABLESPACE; 18:25:17 SYSDBA@DTS:5238 SQL> SELECT NAME,STATUS$ FROM V$TABLESPACE; NAME STATUS$ -------- ----------- SYSTEM 0 ROLL 0 TEMP 0 MAIN 0 TEST_TBS 0 -
查看表数据
select * from DTSTEST.TEST_TABLE; 18:25:18 SYSDBA@DTS:5238 SQL> select * from DTSTEST.TEST_TABLE; ID NAME AGE CREATE_TIME STATUS DESCRIPTION ----------- --------- ----------- -------------------------- ------ -------------- 1 张三 25 2025-08-06 16:08:53.610726 A 测试数据1 2 李四 30 2025-08-06 16:08:53.610736 A 测试数据2 3 王五 28 2025-08-06 16:08:53.610737 A 测试数据3 4 赵六 35 2025-08-06 16:08:53.610738 A 测试数据4 5 钱七 40 2025-08-06 16:08:53.610739 A 测试数据5 6 孙八 22 2025-08-06 16:08:53.610740 A 测试数据6 7 周九 29 2025-08-06 16:08:53.610741 A 测试数据7 8 吴十 31 2025-08-06 16:08:53.610742 A 测试数据8 9 郑十一 27 2025-08-06 16:08:53.610743 A 测试数据9 10 王十二 33 2025-08-06 16:08:53.610745 A 测试数据10 10 rows got 已用时间: 2.403(毫秒). 执行号:606.恢复完成
-
2.1.1.2 增量备份还原
增量备份准备
-
添加增量数据
09:24:55 SYSDBA@DTS:5238 SQL> insert into DTSTEST.TEST_TABLE(ID,NAME) values (11,'dhf'); 影响行数 1 已用时间: 2.197(毫秒). 执行号:5706. 09:25:17 SYSDBA@DTS:5238 SQL> commit; 操作已执行 已用时间: 5.381(毫秒). 执行号:5707. 09:28:34 SYSDBA@DTS:5238 SQL> select * from DTSTEST.TEST_TABLE where ID = 11; ID NAME AGE CREATE_TIME STATUS DESCRIPTION ----------- ---- ----------- -------------------------- ------ ----------- 11 dhf NULL 2025-08-08 09:25:17.031590 A NULL -
执行增量备份
BACKUP DATABASE INCREMENT WITH BACKUPDIR '/db/dmbak/DTS/DB_FULL_BFHY_0807' TO DB_INC_BFHY_0807 BACKUPSET '/db/dmbak/DTS/DB_INC_BFHY_0807' BACKUPINFO '数据库增量备份还原实操测试'; BACKUP DATABASE INCREMENT WITH BACKUPDIR '/db/dmbak/DTS/DB_FULL_BFHY_0807' TO DB_INC_BFHY_0807 BACKUPSET '/db/dmbak/DTS/DB_INC_BFHY_0807' BACKUPINFO '数据库增量备份还原实操测试'; 操作已执行 已用时间: 00:00:05.025. 执行号:5708. -
删除测试表
drop table DTSTEST.TEST_TABLE; 09:30:28 SYSDBA@DTS:5238 SQL> drop table DTSTEST.TEST_TABLE; 操作已执行 已用时间: 28.267(毫秒). 执行号:5710. -
停止数据库服务
[root@dm_dbtest2:/root]# systemctl stop DmServiceDTSTEST.service [root@dm_dbtest2:/root]# systemctl status DmServiceDTSTEST.service ● DmServiceDTSTEST.service - DM Instance Service(DmServiceDTSTEST). Loaded: loaded (/usr/lib/systemd/system/DmServiceDTSTEST.service; enabled; vendor preset: disabled) Active: inactive (dead) since 五 2025-08-08 09:31:44 CST; 44s ago Process: 29928 ExecStop=/db/dm/bin/DmServiceDTSTEST stop (code=exited, status=0/SUCCESS) Process: 8971 ExecStart=/db/dm/bin/DmServiceDTSTEST start (code=exited, status=0/SUCCESS) Main PID: 8992 (code=exited, status=0/SUCCESS)
增量还原恢复
-
校验备份
CHECK BACKUPSET '/db/dmbak/DTS/DB_INC_BFHY_0807'; RMAN> CHECK BACKUPSET '/db/dmbak/DTS/DB_INC_BFHY_0807'; CHECK BACKUPSET '/db/dmbak/DTS/DB_INC_BFHY_0807'; [Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00] check backupset successfully. time used: 192.139(ms) -
还原数据库
RESTORE DATABASE '/db/dmdata/DTS/dm.ini' FROM BACKUPSET '/db/dmbak/DTS/DB_INC_BFHY_0807' WITH BACKUPDIR '/db/dmbak/DTS/DB_FULL_BFHY_0807' ; RMAN> RESTORE DATABASE '/db/dmdata/DTS/dm.ini' FROM BACKUPSET '/db/dmbak/DTS/DB_INC_BFHY_0807' WITH BACKUPDIR '/db/dmbak/DTS/DB_FULL_BFHY_0807' ; RESTORE DATABASE '/db/dmdata/DTS/dm.ini' FROM BACKUPSET '/db/dmbak/DTS/DB_INC_BFHY_0807' WITH BACKUPDIR '/db/dmbak/DTS/DB_FULL_BFHY_0807'; file dm.key not found, use default license! [Percent:100.00%][Speed:0.00M/s][Cost:00:00:03][Remaining:00:00:00] restore successfully. time used: 00:00:03.345 -
恢复数据库
RECOVER DATABASE '/db/dmdata/DTS/dm.ini' FROM BACKUPSET '/db/dmbak/DTS/DB_INC_BFHY_0807'; RMAN> RECOVER DATABASE '/db/dmdata/DTS/dm.ini' FROM BACKUPSET '/db/dmbak/DTS/DB_INC_BFHY_0807'; RECOVER DATABASE '/db/dmdata/DTS/dm.ini' FROM BACKUPSET '/db/dmbak/DTS/DB_INC_BFHY_0807'; [Percent:100.00%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00] recover successfully! time used: 00:00:03.224 -
更新 DB_MEGIC
RECOVER DATABASE '/db/dmdata/DTS/dm.ini' update db_magic; RMAN> RECOVER DATABASE '/db/dmdata/DTS/dm.ini' update db_magic; RECOVER DATABASE '/db/dmdata/DTS/dm.ini' update db_magic; recover successfully! time used: 00:00:01.229 -
启动数据库
[root@dm_dbtest2:/root]# systemctl start DmServiceDTSTEST.service [root@dm_dbtest2:/root]# systemctl status DmServiceDTSTEST.service ● DmServiceDTSTEST.service - DM Instance Service(DmServiceDTSTEST). Loaded: loaded (/usr/lib/systemd/system/DmServiceDTSTEST.service; enabled; vendor preset: disabled) Active: active (running) since 五 2025-08-08 09:43:34 CST; 1min 5s ago Process: 29928 ExecStop=/db/dm/bin/DmServiceDTSTEST stop (code=exited, status=0/SUCCESS) Process: 1425 ExecStart=/db/dm/bin/DmServiceDTSTEST start (code=exited, status=0/SUCCESS) Main PID: 1450 (dmserver) CGroup: /system.slice/DmServiceDTSTEST.service └─1450 /db/dm/bin/dmserver path=/db/dmdata/DTS/dm.ini -noconsole 8月 08 09:43:19 dm_dbtest2 systemd[1]: Starting DM Instance Service(DmServiceDTSTEST).... 8月 08 09:43:34 dm_dbtest2 DmServiceDTSTEST[1425]: [38B blob data] 8月 08 09:43:34 dm_dbtest2 systemd[1]: Started DM Instance Service(DmServiceDTSTEST).. -
数据验证
select * from DTSTEST.TEST_TABLE where ID = 11; 09:51:18 SYSDBA@DTS:5238 SQL> select * from DTSTEST.TEST_TABLE where ID = 11 2 ; ID NAME AGE CREATE_TIME STATUS DESCRIPTION ----------- ---- ----------- -------------------------- ------ ----------- 11 dhf NULL 2025-08-08 09:25:17.031590 A NULL
2.1.1.3 基于时间点/LSN 还原恢复
备份准备
-
创建测试数据
CREATE TABLE DTSTEST.TMLSN_HF(id int,NAME VARCHAR(10),CREATE_TIME TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP); insert into DTSTEST.TMLSN_HF(id,name) values (1,'dhf'); insert into DTSTEST.TMLSN_HF(id,name) values (2,'cjy'); insert into DTSTEST.TMLSN_HF(id,name) values (3,'csy'); commit; 15:30:35 SYSDBA@DTS:5238 SQL> select * from DTSTEST.TMLSN_HF; ID NAME CREATE_TIME ----------- ---- -------------------------- 1 dhf 2025-08-08 15:30:34.996923 2 cjy 2025-08-08 15:30:34.997518 3 csy 2025-08-08 15:30:34.998084 -
全量备份数据库
BACKUP DATABASE FULL TO DB_FULL_BFHY_0808 BACKUPSET '/db/dmbak/DTS/DB_FULL_BFHY_0808' BACKUPINFO '基于时间点和LSN还原测试'; 15:31:42 SYSDBA@DTS:5238 SQL> BACKUP DATABASE FULL TO DB_FULL_BFHY_0808 BACKUPSET '/db/dmbak/DTS/DB_FULL_BFHY_0808' BACKUPINFO '基于时间点和LSN还原测试'; 操作已执行 已用时间: 00:00:05.297. 执行号:616. -
插入增量数据
插入数据并查看对应的 LSN 号
insert into DTSTEST.TMLSN_HF(id,name) values (4,'tjy'); SELECT CUR_LSN FROM V$RLOG; commit; update DTSTEST.TMLSN_HF set NAME = 'xxx' where id = 4; SELECT CUR_LSN FROM V$RLOG; commit; 15:35:05 SYSDBA@DTS:5238 SQL> insert into DTSTEST.TMLSN_HF(id,name) values (4,'tjy'); 影响行数 1 已用时间: 1.846(毫秒). 执行号:617. 15:35:11 SYSDBA@DTS:5238 SQL> commit; 操作已执行 已用时间: 4.607(毫秒). 执行号:619. 15:35:07 SYSDBA@DTS:5238 SQL> SELECT CUR_LSN FROM V$RLOG; CUR_LSN -------------------- 59581 已用时间: 1.924(毫秒). 执行号:618. 15:35:19 SYSDBA@DTS:5238 SQL> update DTSTEST.TMLSN_HF set NAME = 'xxx' where id = 4; 影响行数 1 已用时间: 2.120(毫秒). 执行号:620. 15:35:23 SYSDBA@DTS:5238 SQL> commit; 15:35:26 SYSDBA@DTS:5238 SQL> select * from DTSTEST.TMLSN_HF; ID NAME CREATE_TIME ----------- ---- -------------------------- 1 dhf 2025-08-08 15:30:34.996923 2 cjy 2025-08-08 15:30:34.997518 3 csy 2025-08-08 15:30:34.998084 4 xxx 2025-08-08 15:35:07.489898 -
停止数据库服务
[root@dm_dbtest2:/db/dm/log]# systemctl stop DmServiceDTSTEST.service
基于 LSN 恢复
-
备份校验
RMAN> CHECK BACKUPSET '/db/dmbak/DTS/DB_FULL_BFHY_0808'; CHECK BACKUPSET '/db/dmbak/DTS/DB_FULL_BFHY_0808'; [Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00] check backupset successfully. time used: 206.308(ms) -
还原数据库
RMAN> RESTORE DATABASE '/db/dmdata/DTS/dm.ini' FROM BACKUPSET '/db/dmbak/DTS/DB_FULL_BFHY_0808'; RESTORE DATABASE '/db/dmdata/DTS/dm.ini' FROM BACKUPSET '/db/dmbak/DTS/DB_FULL_BFHY_0808'; [Percent:100.00%][Speed:0.00M/s][Cost:00:00:03][Remaining:00:00:00] restore successfully. time used: 00:00:03.232 -
恢复数据库
恢复数据库至未修改第 4 条数据前，LSN 号为 59581
RECOVER DATABASE '/db/dmdata/DTS/dm.ini' WITH ARCHIVEDIR '/db/dmarch/DTSTEST' UNTIL LSN 59581; RMAN> RECOVER DATABASE '/db/dmdata/DTS/dm.ini' WITH ARCHIVEDIR '/db/dmarch/DTSTEST' UNTIL LSN 59581; RECOVER DATABASE '/db/dmdata/DTS/dm.ini' WITH ARCHIVEDIR '/db/dmarch/DTSTEST' UNTIL LSN 59581; [Percent:70.59%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00] recover successfully! time used: 00:00:01.226 -
更新 DB_MEGIC 值
RECOVER DATABASE '/db/dmdata/DTS/dm.ini' update db_magic; -
启动数据库
[root@dm_dbtest2:/db/dm/log]# systemctl start DmServiceDTSTEST.service -
验证数据
恢复成功，数据为插入第四条数据后未修改值
15:58:01 SYSDBA@DTS:5238 SQL> select * from DTSTEST.TMLSN_HF; ID NAME CREATE_TIME ----------- ---- -------------------------- 1 dhf 2025-08-08 15:30:34.996923 2 cjy 2025-08-08 15:30:34.997518 3 csy 2025-08-08 15:30:34.998084 4 tjy 2025-08-08 15:35:07.489898
基于时间点还原
-
停止数据库
[root@dm_dbtest2:/db/dm/log]# systemctl stop DmServiceDTSTEST.service -
备份校验
RMAN> CHECK BACKUPSET '/db/dmbak/DTS/DB_FULL_BFHY_0808'; CHECK BACKUPSET '/db/dmbak/DTS/DB_FULL_BFHY_0808'; [Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00] check backupset successfully. time used: 206.308(ms) -
还原数据库
RMAN> RESTORE DATABASE '/db/dmdata/DTS/dm.ini' FROM BACKUPSET '/db/dmbak/DTS/DB_FULL_BFHY_0808'; RESTORE DATABASE '/db/dmdata/DTS/dm.ini' FROM BACKUPSET '/db/dmbak/DTS/DB_FULL_BFHY_0808'; [Percent:100.00%][Speed:0.00M/s][Cost:00:00:03][Remaining:00:00:00] restore successfully. time used: 00:00:03.232 -
恢复数据库
恢复数据库至修改第 4 条数据后，时间为 15:35:07
RECOVER DATABASE '/db/dmdata/DTS/dm.ini' WITH ARCHIVEDIR '/db/dmarch/DTSTEST' UNTIL TIME'2025-08-08 15:35:19'; RMAN> RECOVER DATABASE '/db/dmdata/DTS/dm.ini' WITH ARCHIVEDIR '/db/dmarch/DTSTEST' UNTIL TIME'2025-08-08 15:35:19'; RECOVER DATABASE '/db/dmdata/DTS/dm.ini' WITH ARCHIVEDIR '/db/dmarch/DTSTEST' UNTIL TIME '2025-08-08 15:35:19'; [Percent:100.00%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00] recover successfully! time used: 00:00:01.230 -
更新 DB_MEGIC 值
RECOVER DATABASE '/db/dmdata/DTS/dm.ini' update db_magic; -
启动数据库
[root@dm_dbtest2:/db/dm/log]# systemctl start DmServiceDTSTEST.service -
验证数据
恢复成功，数据为插入第四条数据后未修改值
16:12:01 SYSDBA@DTS:5238 SQL> select * from DTSTEST.TMLSN_HF; ID NAME CREATE_TIME ----------- ---- -------------------------- 1 dhf 2025-08-08 15:30:34.996923 2 cjy 2025-08-08 15:30:34.997518 3 csy 2025-08-08 15:30:34.998084 4 tjy 2025-08-08 15:35:07.489898
2.1.2 表空间备份还原实操
2.1.2.1 备份准备
-
备份表空间
11:23:37 SYSDBA@DTS:5238 SQL> create table DTSTEST.tbs_bfhy(id int,name varchar(10)); insert into DTSTEST.tbs_bfhy(ID,NAME) values (1,'lsl');操作已执行 已用时间: 9.512(毫秒). 执行号:605. 11:23:37 SYSDBA@DTS:5238 SQL> 影响行数 1 已用时间: 1.068(毫秒). 执行号:606. 11:23:40 SYSDBA@DTS:5238 SQL> commit; 操作已执行 已用时间: 4.206(毫秒). 执行号:607. 11:23:42 SYSDBA@DTS:5238 SQL> checkpoint(100); DMSQL 过程已成功完成 已用时间: 39.907(毫秒). 执行号:608. 11:24:13 SYSDBA@DTS:5238 SQL> BACKUP TABLESPACE TEST_TBS FULL TO TBS_TEST_TBS_FULL_BFHY_0807 BACKUPSET '/db/dmbak/DTS/TBS_TEST_TBS_FULL_BFHY_0807_01' BACKUPINFO '表空间全量备份还原实操测试'; 操作已执行 已用时间: 00:00:05.057. 执行号:609. -
删除表空间文件并重启数据库
[root@dm_dbtest2:/db/dm/log]# rm -rf /db/dmdata/DTS/TEST_TBS.DBF [root@dm_dbtest2:/db/dm/log]# systemctl restart DmServiceDTSTEST.service -
重启后数据库处于 MOUNT 状态，手动切换到 OPEN
ALTER DATABASE OPEN; -
查看数据
SYSDBA@DTS:5238 SQL> select * from DTSTEST.tbs_bfhy; select * from DTSTEST.tbs_bfhy; [-3433]:表空间处于脱机状态. 已用时间: 2.453(毫秒). 执行号:0. -
停止数据库
[root@dm_dbtest2:/root]# systemctl stop DmServiceDTSTEST.service
2.1.2.2 还原恢复
-
校验备份
CHECK BACKUPSET '/db/dmbak/DTS/TBS_TEST_TBS_FULL_BFHY_0807_01'; -
全量还原表空间
RESTORE DATABASE '/db/dmdata/DTS/dm.ini' TABLESPACE TEST_TBS FROM BACKUPSET '/db/dmbak/DTS/TBS_TEST_TBS_FULL_BFHY_0807_01'; RMAN> RESTORE DATABASE '/db/dmdata/DTS/dm.ini' TABLESPACE TEST_TBS FROM BACKUPSET '/db/dmbak/DTS/TBS_TEST_TBS_FULL_BFHY_0807_01'; RESTORE DATABASE '/db/dmdata/DTS/dm.ini' TABLESPACE TEST_TBS FROM BACKUPSET '/db/dmbak/DTS/TBS_TEST_TBS_FULL_BFHY_0807_01'; file dm.key not found, use default license! [Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00] restore successfully. time used: 00:00:02.490 -
恢复表空间
RECOVER DATABASE '/db/dmdata/DTS/dm.ini' TABLESPACE; RMAN> RECOVER DATABASE '/db/dmdata/DTS/dm.ini' TABLESPACE TEST_TBS; RECOVER DATABASE '/db/dmdata/DTS/dm.ini' TABLESPACE TEST_TBS UNTIL; dres_backupset_recover_ts begin [Percent:100.00%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00] dres_backupset_recover_ts end recover successfully. time used: 00:00:01.228 -
启动数据库
[root@dm_dbtest2:/root]# systemctl start DmServiceDTSTEST.service -
数据校验
select * from DTSTEST.tbs_bfhy; SYSDBA@DTS:5238 SQL> select * from DTSTEST.tbs_bfhy 2 ; ID NAME ----------- ---- 1 lsl
3. 桌面端工具使用
manager 和 console 工具都在安装目录的 tool 子目录下，manager 可以进行联机备份操作为 1 联机备份还原实操的客户端操作，console 工具主要进行脱机备份换韵操作
3.1 manager
manager 工具联机备份
示例：
新建数据库备份
选择备份路径，默认为 BAK_PATH 路径下
名称、路径和备份方式选择完成后，点击完确定就开始备份，备份完成
3.2 console
console 工具脱机备份恢复
./console
示例：
关闭数据库服务
[root@dm_dbtest2:/db/dm/log]# systemctl stop DmServiceDTSTEST.service
配置备份路径
配置路径后选择获取备份
选择一个备份集，点击还原，配置数据库还原，选择 INI 文件路径，点击确定即可
进行恢复
DB_MAGIC 更新
扫一扫
1312
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
