达梦数据库系列—13. 数据库的备份和还原
目次1、配置归档
1.1联机配置归档
手动配置归档
2、联机数据库备份
完全备份
增量备份
3、联机备份管理
添加备份目次
删除备份目次
备份信息查看
监控备份
4、脱机DMRMAN备份数据库
dmrman的配置
完全备份
增量备份
归档备份
5、脱机备份管理
备份集查看
备份集校验
备份集删除
6、还原数据库
7、数据库恢复
从备份集恢复
从归档恢复
恢复数据库到指定lsn
更新 DB_MAGIC
8、增量备份还原恢复
1、配置归档
1.1联机配置归档
修改数据库为MOUNT状态，并开启归档模式
ALTER DATABASE MOUNT;
ALTER DATABASE ARCHIVELOG; 配置本地归档
ALTER DATABASE ADD ARCHIVELOG 'DEST = /dmdata/dameng/arch_dsc0, TYPE = local, FILE_SIZE = 1024,SPACE_LIMIT = 2048,ARCH_FLUSH_BUF_SIZE=16,HANG_FLAG=1'; 修改数据库为OPEN状态
ALTER DATABASE OPEN;
手动配置归档
[*]手动编辑 dmarch.ini 文件
dmarch.ini 文件内容如下：
ARCH_TYPE = LOCAL
ARCH_DEST = d:\dm_arch\arch
ARCH_FILE_SIZE = 1024
ARCH_SPACE_LIMIT = 2048
[*]编辑 dm.ini 文件，设置参数 ARCH_INI=1，生存；
[*]重启数据库实例，数据库已运行于归档模式。
2、联机数据库备份
完全备份
BACKUP DATABASE FULL BACKUPSET '/dm/backup/db_full_bak_01'; 参数 FULL 可以省略
BACKUP DATABASE BACKUPSET 'db_bak_01'; 备份路径为参数BAK_PATH的路径
BACKUP DATABASE BACKUPSET '/dm/backup/db_bak_3_01'; 指定备份集路径为/dm/backup/db_bak_3_01
BACKUP DATABASE TO WEEKLY_FULL_BAK BACKUPSET '/dm/backup/db_bak_3_02'; 指定备份名为WEEKLY_FULL_BAK
如果不设置备份名，体系会默认创建一个备份名：DB_库名_备份类型_备份时间
增量备份
BACKUP DATABASE INCREMENT WITH BACKUPDIR '/dm/backup' BACKUPSET '/dm/backup/db_increment_bak_02'; INCREMENT 参数不可省略，指定备份类型为增量备份。累积增量备份，还必要指定 CUMULATIVE 参数，否则缺省为差异增量备份。WITH BACKUPDIR 参数用来指定基备份集的搜索目次。
backup database increment BASE ON BACKUPSET '/dm/backup/dm_bak/db_full_bak_01' to ONLINEBAKINCR_01 backupset '/dm/backup/dm_bak/ONLINEBAK_01' ;
3、联机备份管理
DM 没有提供自动删除逾期备份的功能，删除备份必要手动实验。
添加备份目次
向 DISK 中添加备份目次/dm/backup
SELECT SF_BAKSET_BACKUP_DIR_ADD('DISK','/dm/backup'); 查看备份集信息，必要先添加备份目次，V$BACKUPSET_SEARCH_DIRS有目次信息，当前会话实验查询。
删除备份目次
删除指定备份目次（只删除视图V$BACKUPSET_SEARCH_DIRS记录，不删除文件）
SELECT SF_BAKSET_BACKUP_DIR_REMOVE('DISK','/dm/backup'); 清算全部备份目次，默认备份目次除外（只删除视图V$BACKUPSET_SEARCH_DIRS记录，不删除文件）
SELECT SF_BAKSET_BACKUP_DIR_REMOVE_ALL(); 校验 DISK 中的备份目次/dm/backup/db_bak_for_check 是否合法
SELECT SF_BAKSET_CHECK('DISK','/dm/backup/db_bak_for_check'); 删除指定设备类型和指定目次的备份集（删除整个目次，删除文件）
SELECT SF_BAKSET_REMOVE('DISK','/dm/backup/db_full_bak_01',1); 删除满足指定条件的所有备份集
必要先使用 SF_BAKSET_BACKUP_DIR_ADD 添加将要删除的备份集目次，否则只删除数据库的默认备份目次下的备份集。
SF_BAKSET_REMOVE_BATCH (device_type varchar,end_time datetime,range int,obj_name varchar(257)) device_type：设备类型，DISK 或 TAPE。若指定为 NULL，则忽略存储设备的区分。
end_time：删除备份集天生的结束时间，仅删除 end_time 之前的备份集，必须指定。
range：指定删除备份的级别。1 代表库级，2 代表表空间级，3 代表表级，4 代表归档备份。若指定为 NULL，则忽略备份集备份级别的区分。
obj_name：待删除备份集中备份对象的名称，若指定为 NULL，则忽略备份集中备份对象名称区分。
SELECT SF_BAKSET_REMOVE_BATCH ('DISK', now(), NULL, NULL); 删除满足指定条件的所有库级备份集
SELECT SF_BAKSET_REMOVE_BATCH_S ('DISK', now(), NULL, NULL); 删除满足指定条件的所有库级备份集，生存备份时间最新的 n 个库级完全备份集
SELECT SF_BAKSET_REMOVE_BATCH_N ('DISK', now(), NULL, NULL, 2); 删除指定时间之前的数据库备份集
CALL SP_DB_BAKSET_REMOVE_BATCH('DISK', NOW()); 删除指定表空间对象及指定时间之前的表空间备份集
CALL SP_TS_BAKSET_REMOVE_BATCH('DISK',NOW(),'MAIN'); 删除指定表对象及指定时间之前的表备份集
CALL SP_TAB_BAKSET_REMOVE_BATCH('DISK',NOW(),'SYSDBA','TAB_FOR_BATCH_DEL'); 删除指定时间之前的归档备份集
CALL SP_ARCH_BAKSET_REMOVE_BATCH('DISK', NOW());
BACKUP ARCHIVELOG BACKUPSET '/dm/backup/arch_bak_for_batch_del';
SELECT SF_BAKSET_BACKUP_DIR_ADD('DISK','/dm/backup');
CALL SP_ARCH_BAKSET_REMOVE_BATCH('DISK', NOW()); 备份信息查看
V$BACKUPSET
表现备份集的基本信息。
SELECT SF_BAKSET_BACKUP_DIR_ADD('DISK', '/dm/backup');
SELECT DEVICE_TYPE,BACKUP_PATH, TYPE, RANGE# FROM V$BACKUPSET;
V$BACKUPSET_DBINFO
表现备份集的数据库相关信息
SELECT SF_BAKSET_BACKUP_DIR_ADD('DISK', '/dm/backup');
SELECT BACKUP_PATH, PAGE_SIZE, EXTENT_SIZE, CASE_SENSITIVE FROM V$BACKUPSET_DBINFO WHERE BACKUP_PATH='/dm/backup/tab_bak_01'; V$BACKUPSET_DBF
表现备份集中数据文件的相关信息，表备份时无效
SELECT SF_BAKSET_BACKUP_DIR_ADD('DISK', '/dm/backup');
SELECT FILE_SEQ, TS_ID, FILE_ID, TS_NAME, FILE_NAME FROM V$BACKUPSET_DBF WHERE BACKUPPATH ='/dm/backup/ts_bak_for_dbf'; V$BACKUPSET_ARCH
表现备份集中归档文件的信息，且仅归档备份才会有数据。
SELECT SF_BAKSET_BACKUP_DIR_ADD('DISK', '/dm/backup');
SELECT BACKUPPATH, FILE_SEQ, BEGIN_LSN, END_LSN FROM V$BACKUPSET_ARCH
WHERE BACKUPPATH='/dm/backup/db_bak_for_arch'; V$BACKUPSET_BKP
表现备份集的备份片信息。
SELECT SF_BAKSET_BACKUP_DIR_ADD('DISK', '/dm/backup');
SELECT BACKUPPATH, BKP_NTH, FILE_NAME, BKP_LEN FROM V$BACKUPSET_BKP WHERE
BACKUPPATH='/dm/backup/db_bak_for_bkp'; V$BACKUPSET_SEARCH_DIRS
表现动态视图备份集搜索目次。BAK_PATH、SYSTEM_PATH和添加的备份目次
SELECT SF_BAKSET_BACKUP_DIR_REMOVE_ALL();
SELECT * FROM V$BACKUPSET_SEARCH_DIRS; V$BACKUPSET_TABLE
表现表备份集中备份表信息，仅表备份有效。
SELECT SF_BAKSET_BACKUP_DIR_ADD('DISK', '/dm/backup');
SELECT BACKUPNAME, TABLENAME FROM V$BACKUPSET_TABLE WHERE BACKUPPATH='/dm/backup/tab_bak_for_info'; V$BACKUPSET_SUBS
并行备份中天生的子备份集信息。
BACKUP DATABASE BACKUPSET '/dm/backup/db_bak_for_subs' PARALLEL 3;
SELECT SF_BAKSET_BACKUP_DIR_ADD('DISK', '/dm/backup');
SELECT BACKUPPATH, BKP_NUM, DBF_NUM FROM V$BACKUPSET_SUBS; 监控备份
V$BACKUP_MONITOR
当前备份使命实时监控信息。
BACKUP DATABASE BACKUPSET '/dm/backup/db_bak_for_monitor';
SELECT TOTAL_SIZE, PCNT, CUR_READ, CUR_WRITE FROM V$BACKUP_MONITOR; V$BACKUP_FILES
当前备份使命待备份数据文件列表。
V$BACKUP_HISTORY
最近 100 条备份监控信息。
SELECT READ_SIZE, WRITE_SIZE, AVG_READ, AVG_WRITE FROM V$BACKUP_HISTORY;
4、脱机DMRMAN备份数据库
dmrman的配置
表现 DMRMAN 配置项的当前值
RMAN>CONFIGURE; 配置存储介质类型：DISK 或 TAPE
RMAN>CONFIGURE DEFAULT DEVICE TYPE TAPE PARMS 'command'; 配置跟踪日记文件
RMAN>CONFIGURE DEFAULT TRACE FILE '/dm/backup/dm_bak/trace.log' TRACE LEVEL 2; 配置备份集搜索目次
RMAN>CONFIGURE DEFAULT BACKUPDIR;
RMAN>CONFIGURE DEFAULT BACKUPDIR '/dm/backup/dm_bak';
RMAN>CONFIGURE DEFAULT BACKUPDIR ADD '/home/dm_bak3';
RMAN>CONFIGURE DEFAULT BACKUPDIR DELETE '/home/dm_bak3'; 配置归档日记搜索目次
RMAN>CONFIGURE DEFAULT ARCHIVEDIR;
RMAN>CONFIGURE DEFAULT ARCHIVEDIR '/dm/backup/dm_arch1';
RMAN>CONFIGURE DEFAULT ARCHIVEDIR ADD '/home/dm_arch3';
RMAN>CONFIGURE DEFAULT ARCHIVEDIR DELETE '/home/dm_arch3';
完全备份
RMAN>BACKUP DATABASE '/dm/data/DMDB/dm.ini' FULL BACKUPSET '/dm/backup/dm_bak/db_full_bak_01';
增量备份
RMAN>BACKUP DATABASE '/dm/data/DMDB/dm.ini' INCREMENT WITH BACKUPDIR '/dm/backup/dm_bak' BACKUPSET '/dm/backup/dm_bak/db_increment_bak_02';
归档备份
RMAN>BACKUP ARCHIVE LOG ALL DATABASE '/opt/dmdbms/data/DAMENG/dm.ini' BACKUPSET '/home/dm_bak/arch_all_bak_01';
RMAN>BACKUP ARCHIVE LOG LSN BETWEEN 50000 AND 120000 DATABASE '/dm/data/DMDB/dm.ini' BACKUPSET '/dm/backup/dm_bak/arch_lsn_bak_02';
5、脱机备份管理
备份集查看
RMAN> show backupset '/dm/backup/dm_bak';
RMAN> SHOW BACKUPSETS WITH BACKUPDIR '/dm/backup/dm_bak/db_full_bak_01'; 查看指定目次下所有备份集信息。
RMAN>SHOW BACKUPSETS WITH BACKUPDIR '/dm/backup/dm_bak' USE DB_MAGIC 511306610; 查看部分备份集信息,DB、META、FILE、TABLESPACE、TABLE
RMAN> show backupset '/dm/backup/dm_bak/db_full_bak_01' info meta 备份集校验
RMAN>CHECK BACKUPSET '/dm/backup/dm_bak/db_full_bak_01'; 备份集删除
RMAN>REMOVE BACKUPSET '/dm/backup/dm_bak/db_full_bak_01';
RMAN>REMOVE BACKUPSET '/dm/backup/dm_bak/db_full_bak_01' DATABASE '/dm/data/DMDB/dm.ini'; 删除备份目次下的某一个数据库备份集
RMAN>REMOVE BACKUPSETS WITH BACKUPDIR '/dm/backup/dm_bak';
RMAN>REMOVE BACKUPSETS WITH BACKUPDIR '/dm/backup/dm_bak' UNTIL TIME '2024-06-25 13:38:00'; 会删除整个目次
6、还原数据库
表还原只能联机举行，数据库和表空间的还原只能脱机dmrman下举行。
备份数据库
SQL>BACKUP DATABASE BACKUPSET '/dm/backup/dm_bak/db_full_bak_for_restore'; 准备目标库
dminit path=/dm/data db_name=DAMENG_FOR_RESTORE auto_overwrite=1
校验备份
RMAN>CHECK BACKUPSET '/dm/backup/dm_bak/db_full_bak_for_restore'; 还原数据库
RMAN>RESTORE DATABASE '/dm/data/DAMENG_FOR_RESTORE/dm.ini' FROM BACKUPSET '/dm/backup/dm_bak/db_full_bak_for_restore'; 也可指定 REUSE DMINI 子句举行数据库还原，此时会将备份集中备份的 dm.ini 中除路径相关的 INI 参数外，均拷贝到当前 dm.ini 上。如下所示：
RMAN> RESTORE DATABASE '/opt/dmdbms/data/DAMENG_FOR_RESTORE/dm.ini' REUSE DMINI FROM BACKUPSET '/home/dm_bak/db_full_bak_for_restore'; RMAN> CHECK BACKUPSET '/dm/backup/dm_bak/db_full_bak_for_restore';
CHECK BACKUPSET '/dm/backup/dm_bak/db_full_bak_for_restore';
check backupset successfully.
time used: 184.800(ms)
RMAN> RESTORE DATABASE '/dm/data/DAMENG_FOR_RESTORE/dm.ini' FROM BACKUPSET '/dm/backup/dm_bak/db_full_bak_for_restore';
RESTORE DATABASE '/dm/data/DAMENG_FOR_RESTORE/dm.ini' FROM BACKUPSET '/dm/backup/dm_bak/db_full_bak_for_restore';
file dm.key not found, use default license!
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
restore successfully.
time used: 00:00:02.678
RMAN> RECOVER DATABASE '/dm/data/DAMENG_FOR_RESTORE/dm.ini' FROM BACKUPSET '/dm/backup/dm_bak/db_full_bak_for_restore';
RECOVER DATABASE '/dm/data/DAMENG_FOR_RESTORE/dm.ini' FROM BACKUPSET '/dm/backup/dm_bak/db_full_bak_for_restore';
Database mode = 2, oguid = 0
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP's cur_lsn, file_lsn
recover successfully!
time used: 00:00:02.713
RMAN> RECOVER DATABASE '/dm/data/DAMENG_FOR_RESTORE/dm.ini' UPDATE DB_MAGIC;
RECOVER DATABASE '/dm/data/DAMENG_FOR_RESTORE/dm.ini' UPDATE DB_MAGIC;
Database mode = 2, oguid = 0
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP's cur_lsn, file_lsn
recover successfully!
time used: 00:00:01.086 7、数据库恢复
从备份集恢复
RMAN>RECOVER DATABASE '/dm/data/DAMENG_FOR_RESTORE/dm.ini' FROM BACKUPSET '/dm/backup/dm_bak/db_full_bak_for_restore';
从归档恢复
1.联机数据库实验备份，产生备份集 B1；
BACKUP DATABASE FULL TO B1 BACKUPSET '/dm/backup/dm_bak/B1' DEVICE TYPE DISK BACKUPINFO 'DAMENG FULL BACKUP ONLINE' MAXPIECESIZE 2048; 2.向表中循环插入数据为例来操纵数据库，在插入数据的过程中，强行杀掉数据库服务器进程模拟故障；
DROP TABLE TAB_FOR_RECOVER; CREATE TABLE TAB_FOR_RECOVER(C1 INT);
BEGIN
FOR I IN 1..100000 LOOP
INSERT INTO TAB_FOR_RECOVER VALUES(I);
COMMIT;
END LOOP;
END;
/ 3.创建目标库 D2，即待还原的库；
dminit path=/dm/data db_name=DAMENG_FOR_RES 4.启动 DMRMAN 工具，修复源库 D1 归档文件；
RMAN>REPAIR ARCHIVELOG DATABASE '/dm/data/DMDB/dm.ini' 5.使用备份集 B1 及归档 将目标库 D2 恢复到最新状态。
RMAN>RESTORE DATABASE '/dm/data/DAMENG_FOR_RES/dm.ini' FROM BACKUPSET '/dm/backup/dm_bak/B1';
RMAN>RECOVER DATABASE '/dm/data/DAMENG_FOR_RES/dm.ini' WITH ARCHIVEDIR '/dm/data/DMDB/arch';
SQL> BACKUP DATABASE FULL TO B1 BACKUPSET '/dm/backup/dm_bak/B1' DEVICE TYPE DISK BACKUPINFO 'DAMENG FULL BACKUP ONLINE' MAXPIECESIZE 2048;
操作已执行
已用时间: 00:00:03.181. 执行号:2102.
SQL> CREATE TABLE TAB_FOR_RECOVER(C1 INT);
操作已执行
已用时间: 16.851(毫秒). 执行号:2103.
SQL> BEGIN
FOR I IN 1..100000 LOOP
INSERT INTO TAB_FOR_RECOVER VALUES(I);
COMMIT;
END LOOP;
END;
/2 3 4 5 6 7
连接丢失
SQL>
RMAN> RESTORE DATABASE '/dm/data/DAMENG_FOR_RES/dm.ini' FROM BACKUPSET '/dm/backup/dm_bak/B1';
RESTORE DATABASE '/dm/data/DAMENG_FOR_RES/dm.ini' FROM BACKUPSET '/dm/backup/dm_bak/B1';
file dm.key not found, use default license!
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
restore successfully.
time used: 00:00:02.577
RMAN> REPAIR ARCHIVELOG DATABASE '/dm/data/DMDB/dm.ini';
REPAIR ARCHIVELOG DATABASE '/dm/data/DMDB/dm.ini';
Database mode = 0, oguid = 0
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
Can not open ini file /dm/data/DMDB/dmmal.ini!
begin redo pwr log collect, last ckpt lsn: 488116 ...
redo pwr log collect finished
EP's cur_lsn, file_lsn
rafil_recv_if_necessary, arch file recv begin: arch_lsn: 574470, clsn: 574475, begin_seq: 86209, end_seq: 86209
arch file recv end: arch_lsn: 574471, clsn: 574475, begin_seq: 86209, end_seq: 86209
repair archive log successfully.
repair time used: 658.107(ms)
time used: 658.543(ms)
RMAN> RECOVER DATABASE '/dm/data/DAMENG_FOR_RES/dm.ini' WITH ARCHIVEDIR '/dm/data/DMDB/arch';
RECOVER DATABASE '/dm/data/DAMENG_FOR_RES/dm.ini' WITH ARCHIVEDIR '/dm/data/DMDB/arch';
Database mode = 2, oguid = 0
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP's cur_lsn, file_lsn
recover successfully!
time used: 00:00:04.228 恢复数据库到指定lsn
1.准备数据；
CREATE TABLE TAB_FOR_RECOVER_01(C1 INT);
INSERT INTO TAB_FOR_RECOVER_01 VALUES(1);
COMMIT; 2.备份数据库；
BACKUP DATABASE BACKUPSET '/dm/backup/dm_bak/db_full_bak_for_time_lsn'; 操纵数据库，产生一些归档；
CREATE TABLE TAB_FOR_RECOVER_02(C1 INT);
INSERT INTO TAB_FOR_RECOVER_02 VALUES(1);
COMMIT; 使用 SELECT SYSDATE 命令查询此时的时间为：2018-11-16 10:56:40。
使用 SELECT FILE_LSN FROM V$RLOG 命令查询此时的 LSN 为：50857。
4.误操纵数据库。此步骤误删除了表 TAB_FOR_RECOVER_01 中数据；
DELETE FROM TAB_FOR_RECOVER_01;
COMMIT; 5.还原数据库
RMAN> RESTORE DATABASE '/dm/data/DAMENG_FOR_RESTORE/dm.ini' FROM BACKUPSET '/dm/backup/dm_bak/db_full_bak_for_time_lsn'; 6.恢复数据库到指定时间点/LSN
RMAN>RECOVER DATABASE '/dm/data/DAMENG_FOR_RESTORE/dm.ini' WITH ARCHIVEDIR '/dm/data/DMDB/arch' UNTIL TIME '2024-06-25 14:50:13'; 或
RMAN>RECOVER DATABASE '/dm/data/DAMENG_FOR_RESTORE/dm.ini' WITH ARCHIVEDIR '/dm/data/DMDB/arch' UNTIL LSN 50857;
更新 DB_MAGIC
RMAN>RECOVER DATABASE '/dm/data/DAMENG_FOR_RESTORE/dm.ini' UPDATE DB_MAGIC;
8、增量备份还原恢复
1.做一个联机全备。
BACKUP DATABASE FULL BACKUPSET '/dm/backup/dm_bak/db_full_bak_01'; 2.造测试数据。
CREATE TABLE TAB_FOR_RECOVER_03(C1 INT); BEGIN
FOR I IN 1..1000 LOOP
INSERT INTO TAB_FOR_RECOVER_03 VALUES(I);
COMMIT;
END LOOP;
END;
/ 3.做一个联机增量备份。
BACKUP DATABASE INCREMENT WITH BACKUPDIR '/dm/backup/dm_bak' BACKUPSET '/dm/backup/dm_bak/db_increment_bak_02'; 4.初始化一个新的数据库（还原库）
dminit path=/dm/data db_name=DAMENG_FOR_RESTORE auto_overwrite=1 5.全量还原恢复
RESTORE DATABASE '/dm/data/DAMENG_FOR_RESTORE/dm.ini' FROM BACKUPSET '/dm/backup/dm_bak/db_full_bak_01';
RECOVER DATABASE '/dm/data/DAMENG_FOR_RESTORE/dm.ini' FROM BACKUPSET '/dm/backup/dm_bak/db_full_bak_01'; 6.增量还原恢复
RESTORE DATABASE '/dm/data/DAMENG_FOR_RESTORE/dm.ini' FROM BACKUPSET '/dm/backup/dm_bak/db_increment_bak_02';
RECOVER DATABASE '/dm/data/DAMENG_FOR_RESTORE/dm.ini' FROM BACKUPSET '/dm/backup/dm_bak/db_increment_bak_02'; 7.更新DB_MAGIC
RECOVER DATABASE '/dm/data/DAMENG_FOR_RESTORE/dm.ini' UPDATE DB_MAGIC; 8.验证数据
su - root
cd /dm/script/root/
./dm_service_installer.sh -t dmserver -dm_ini /dm/data/DAMENG_FOR_RESTORE/dm.ini -p DAMENG_FOR_RESTORE
$ disql SYSDBA
SQL> select count(*) from TAB_FOR_RECOVER_03; 达梦技能社区：https://eco.dameng.com/
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]