达梦数据库归档、备份、还原与恢复

最新推荐文章于 2024-08-21 21:37:47 发布

txq000

最新推荐文章于 2024-08-21 21:37:47 发布

阅读量2.4k

点赞数 1

分类专栏： dameng达梦文章标签：数据库

本文链接：https://blog.csdn.net/txq000/article/details/112027454

版权

dameng达梦专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文详细介绍了数据库的归档模式，包括本地、实时、即时、异步和远程归档，以及归档原理。讨论了远程和本地归档失败的处理方式，重点阐述了归档恢复的步骤，包括数据库的一致性状态恢复、最新状态恢复等场景。同时，讲解了逻辑和物理备份、一致性和非一致性备份的区别，以及如何进行数据和归档日志的备份与还原。此外，还涵盖了使用DMRMAN工具进行备份和恢复的具体操作，以及图形化客户端工具的应用。

摘要由CSDN通过智能技术生成

备份还原实操

归档准备工作

 联机归档：
1)修改数据库为 MOUNT 状态。

    	sql>alter database mount;

2)配置本地归档。

   	 sql>alter database add archivelog 'dest = /home/dm_arch/arch, type = local,file_size = 1024, space_limit = 2048';

3)开启归档模式。

   	 sql>alter database archivelog;

4)修改数据库为 open 状态。

   	 sql>alter database open;

 手动归档：（配置dmarch.ini与dm.ini在同一路径）

[archive_local1] 
arch_type = local
arch_dest = d:\dm_arch\arch
arch_file_size = 1024
arch_space_limit = 2048
  （设置dm.ini参数arch_ini=1）

 远程归档（配置dmarch.ini）
1）本地配置

 arch_type=remote
 arch_dest=远程数据库实例名
 arch_incoming_path=本地存储路径

2）远程俩节点配置（dsc0和dsc1）

[archive_local1]
arch_type = local
arch_dest = /dmdata/dameng/arch_dsc0
arch_file_size = 128
arch_space_limit = 0
[arch_remote1]
arch_type = remote
arch_dest = dsc1
arch_incoming_path = /dmdata/dameng/arch_dsc1
arch_file_size = 128
arch_space_limit = 0

——————————————————————

[archive_local1]
arch_type = local
arch_dest = /dmdata/dameng/arch_dsc1
arch_file_size = 128
arch_space_limit = 0
[arch_remote1]
arch_type = remote
arch_dest = dsc0
arch_incoming_path = /dmdata/dameng/arch_dsc0
arch_file_size = 128
arch_space_limit = 0

使用联机执行 SQL 语句进行备份还原

**
1）备份数据（默认路径dm.ini制定）

	sql>backup database backupset 'db_bak_01';

后缀.meta（备份元数据文件）后缀.bak（备份片文件）
2）输出归档日志序列号范围

SQL>select ARCH_LSN, CLSN, PATH from V$ARCH_FILE;

3）备份归档

sql>backup archiveloglsn between 50414 and 50478 backupset '/home/dm_bak/arch_bak_time_14-78';

4）加密备份

sql>backup database backupset '/home/dm_bak/db_bak_for_encrypt' identified by "cdb546789";
sql>backup database backupset '/home/dm_bak/db_bak_for_encrypt' identified by "cdb546789" encrypt with rc4;

5）非加密备份

sql>backup database backupset '/home/dm_bak/db_bak_for_encrypt' identified by
"cdb546789" with encryption 0;       例：加密备份数据库密码“cdb546789”，加密算法“rc4”
sql>backup database backupset '/home/dm_bak/db_bak_for_encrypt_01' identified
by "cdb546789" with encryption 2 encrypt with rc4;

6）设置跟踪日志文件（可跟踪备份还原过程）

sql>backup database backupset '/home/dm_bak/db_bak_for_trac_01' trace file'/home/dm_log/db_bak_trace.log' trace level 2; （1关闭trace功能2为开启）

7）查看备份信息

sql>select sf_bakset_backup_dir_add('disk', '/home/dm_bak');

——————————————————————

#设置归档日志
alter database mount;
alter database add archivelog 'dest=/home/dmdba/arch1,type=local,file_size=1024,space_limit=2048';
alter database archivelog;
alter database open;
#全备
backup database backupset '/home/dmdba/bak/db_bak_01';
  ##备份表空间
backup tablespace main backupset '/home/dmdba/bak/tb_bak_01';
  ##增备main表空间
backup tablespace TTT backupset '/home/dmdba/bak/table/TTT_full_bak_01';
backup tablespace TTT increment backupset '/home/dmdba/bak/table/TTT_increment_bak_01';
backup tablespace TTT increment base on backupset '/home/dmdba/bak/table/TTT_full_bak_01' backupset '/home/dmdba/bak/table/TTT_increment_bak_02';
  ##表备份
backup table TXQ backupset '/home/dmdba/bak/table/TXQ_bak_01';
#归档备份
  ##查看归档日志序列号范围
select arch_lsn,clsn,path from V$ARCH_FILE;
backup archivelog all backupset '/home/dmdba/arch/arch_bak_01';
backup ARCHIVELOG LSN between 37292 and 41900 backupset '/home/dmdba/arch/arch_12.8';
#还原
  ##表还原
create table tab_01(txq int);
create index suoyin on tab_01(txq);
backup table tab_01 backupset '/home/dmdba/dm_bak/tab_01.bak';
  ##校验备份
select sf_backset_check('disk','/home/dmdba/dm_bak/tab_01.bak');
  ##不重建索引还原
restore table tab_01 without index from backupset '/home/dmdba/dm_bak/tab_01.bak';
  ##不重建约束还原
restore table tab_01 without constraint from backupset '/home/dmdba/dm_bak/tab_01.bak';

使用脱机工具 DMRMAN（命令行工具）进行备份还原

1）启动和配置DMRMAN

cd /opt/dmdbms/bin
./dmrman
在这里插入图片描述

configure;   查看dmrman配置项
configure 配置项 clear;  配置项恢复默认
configure clear;    清除所有配置项

配置备份集搜索目录

rman>configure default backupdir; 
rman>configure default backupdir '/home/dm_bak1‟,'/home/dm_bak2';

添加或删除

rman>configure default backupdir add '/home/dm_bak3'; 
rman>configure default backupdir delete '/home/dm_bak3';

配置归档日志搜集目录

rman>configure default archivedir; 
rman>configure default archivedir '/home/dm_arch1‟,'/home/dm_arch2';

添加或删除

rman>configure default archivedir add '/home/dm_arch3'; 
rman>configure default archivedir delete '/home/dm_arch3';

配置跟踪日志文件

  查看trace配置   configure default trace;
  配置文件、级别  configure default trace file '/home/dm_trace/trace.log ' trace level 2;

2）数据备份
根据ini进行备份配置：

backup database '/opt/dmdbms/data/dameng/ dm.ini'；

完全备份：

backup database '/opt/dmdbms/data/dameng/dm.ini' full backupset '/home/dm_bak/db_full_bak_01';

增量备份：

backup database '/opt/dmdbms/data/dameng/dm.ini' increment with backupdir '/home/dm_bak'backupset '/home/dm_bak/db_increment_bak_02';

归档备份：

backup archive log lsn between 50000 and 120000database '/opt/dmdbms/data/dameng/dm.ini'backupset '/home/dm_bak/db_increment_bak_02';

3）管理备份
查看备份集信息，元数据信息（META INFO）、文件信息（FILE INFO）和数据库信息（DB INFO）可看db_magic

show backupset '/home/dm_bak/db_full_dameng_20190522_133248_000770'；

批量查看

show backupsets with backupdir '/home/dm_bak1','/home/dm_bak2';

查看备份集元信息

show backupset'/home/dm_bak/db_full_dameng_20190522_133248_000770' info meta

以 xml 格式输出备份信息到文件

show backupset'/home/dm_bak1/db_bak_for_xml_01' to '/home/dm_info/bkp_info.txt' format xml;

指定相对路径校验备份集

check backupset 'db_bak_for_check_02' database '/opt/dmdbms/data/dameng/dm.ini';

删除备份集

remove backupset '/home/dm_bak/db_bak_for_remove_01';

批量删除指定时间前的备份集

remove backupsets with backupdir '/home/dm_bak' until time'2019-6-1 00:00:00';

导出磁盘区所有备份集

export tape=/dev/nst0 load backupsets from device type tape to backupdir '/mnt/hgfs/dmsrc/bak_dir';

导出备份集映射文件

 	dump backupset'/mnt/dmsrc/db_bak'device type diskdatabase '/opt/dmdbms/data/dameng/dm.ini'mapped file '/mnt/dmsrc/db_bak_mapped.txt';

3）还原和恢复
还原

restore database '/opt/dmdbms/data/dameng_for_restore/dm.ini' from backupset '/home/dm_bak/db_full_bak_for_restore';

备份集恢复

recover database '/opt/dmdbms/data/dameng_for_restore/dm.ini' from backupset '/home/dm_bak/db_full_bak_for_recover_backupset';

归档恢复

recover database '/opt/dmdbms/data/dameng_for_restore/dm.ini' with archivedir'/home/dm_arch/arch' use db_magic 1447060265;

4）数据库更新

recover database '/opt/dmdbms/data/dameng_for_restore/dm.ini' update db_magic;

5）表空间还原恢复（通过归档）

restore archive log from backupset '/home/dm_bak/arch_all_for_restore' to database '/opt/dmdbms/data/dameng_for_restore/dm.ini' overwrite 2;
repair archivelog database '/opt/dmdbms/data/dm.ini';

使用图形化客户端工具进行备份还原

1）使用manager联机备份还原
数据备份
在这里插入图片描述

备份管理
在这里插入图片描述

数据还原（仅表备份支持）
2）使用 CONSOLE 工具进行脱机备份还原
数据备份

新建备份→配置参数→确定

备份管理

指定搜索目录→获取备份→属性

数据还原
生成映射文件
数据恢复
数据更新
归档恢复

txq000

关注

1
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录