达梦备份恢复

标签：database 恢复 备份 backupset select dm8 backup 达梦

查看是否开归档
select * from v$database;
查看作业
select * from SYSJOB.SYSJOBS;
查看作业历史
select * from SYSJOB.SYSJOBHISTORIES2;
查看数据库版本
select id_code();
查看归档状态
select * from v$arch_status;
查看归档参数
select * from v$dm_arch_ini;

脱机备份（冷备）
DMRMAN 和 Console DM 控制台工具可以脱机备份，脱机备份是数据库关闭状态下的备份
backup database '/dm8/data/DAMENG/dm.ini' to Dmfullbak04 backupset '/dm8/backup/full/dmfullbak04';

设定备份集（备份目录）
show backupset '/data/dmdata/DAMENG/dmbak/dmfullbak';
check backupset '/data/dmdata/DAMENG/dmbak/dmfullbak';
remove backupset '/data/dmdata/DAMENG/dmbak/dmfullbak';

select * from SYS."V$PARAMETER" t where t.name ='BAK_PATH';
select * from SYS."V$PARAMETER" t where t.name ='BAK_USE_AP';
select * from SYS."V$BACKUPSET";
SF_BAKSET_BACKUP_DIR_ADD('DISK','/data/dmdata/DAMENG/dmbak/');
SF_BAKSET_BACKUP_DIR_ADD('DISK','/data/dmdata/DAMENG/dmbak/');

select * from v$ifun t where t.name like '%BAKSET%';

select * from v$database;
select * from SYSJOB.SYSJOBS;
select * from SYSJOB.SYSJOBHISTORIES2 where start_time >= sysdate -1;
select * from SYS."V$BACKUPSET";
select * from SYS."V$PARAMETER" t where t.name ='BAK_USE_AP';
select * from SYS.V$BACKUPSET_SEARCH_DIRS;
select * from v$ifun t where t.name like '%BAKSET%';
SELECT SF_BAKSET_BACKUP_DIR_ADD('DISK','/data/dmdata/DAMENG/dmbak/');
SELECT SF_BAKSET_REMOVE_BATCH ('DISK', now(), NULL, NULL);
SELECT SF_BAKSET_REMOVE_BATCH ('DISK', sysdate -1 , NULL, NULL);

CALL SF_BAKSET_BACKUP_DIR_ADD('DISK','/data01/dm/dmbak');--在会话生效，每次必须增加备份集目录
CALL SF_BAKSET_REMOVE_BATCH ('DISK',now()-22,NULL,NULL);--now()为当前时间，指定删除7天前的备份

CALL SF_BAKSET_BACKUP_DIR_ADD('DISK','/data/dm/dmbak');
CALL SF_BAKSET_REMOVE_BATCH ('DISK', sysdate -1 , NULL, NULL);

backup database full to dmfullbak backupset '/data/dmdata/DAMENG/dmbak/dmfullbak';

联机备份（热备）

./DmAPService status
./DmAPService start

备份全库
SQL>
backup database;
backup database full to dmfullbak backupset '/data/dmdata/DAMENG/dmbak/dmfullbak';

RMAN>
backup database '/data/dmdata/DAMENG/dm.ini' backupset'/data/dmdata/DAMENG/dmbak/dmfullbak';

备份差异增量
backup database increment to inck01 backupset '/dm8/backup/incr/inck01';
backup database increment with backupdir '/dm8/backup/full/' to inck02 backupset '/dm8/backup/incr/inck02';

备份累计增量
backup database increment cumulative with backupdir '/dm8/backup/full/' to inck03 backupset '/dm8/backup/incr/inck03';

备份表空间
backup tablespace dmtbs to dmtbsbak backupset '/dm8/backup/full/dmtbsbak';

备份表
backup table dmtest.t_emp;

备份归档
backup archive log all to dmarchbak backupset '/data/dmdata/DAMENG/dmbak/archbak';

逻辑备份

dexp 逻辑导出，包含四个级别全库导出、按用户导出、按模式导出、按表导出

全库导出：
dexp userid=sysdba/SYSDBA file=/dm8/backup/full.dmp log=/dm8/backup/full.log full=y

模式导出：
dexp userid=DMTEST/Dameng123 file=/dm8/backup/DMTEST.dmp log=/dm8/backup/DMTEST.log schemas=DMTEST

dimp SYSDBA/SYSDBA@localhost:5236 file=/dm8/databak/sysdba.stu.dmp TABLES=sysdba.stu

restore database '/dm8/data2/DAMENG/dm.ini' from backupset '/dm8/databak/20220406database.bak'

recover database '/dm8/data2/DAMENG/dm.ini' from backupset '/dm8/databak/20220406database.bak'

recover database '/dm8/data2/DAMENG/dm.ini' update db_magic

select arch_mode from v$database;
alter database mount;
alter database add archivelog 'type=local,dest=/dm8/arch,file_size=128,space_limit=0';
alter database archivelog;
alter database open;
select arch_mode from v$database;

备份全库（恢复时需要关库）
backup database backupset '/dm8/databak/0406_full';

备份表空间（恢复时需要关库）
backup tablespace main backupset '/dm8/databak/main.tabp.bak0407';

备份表（恢复时不需要关库）
backup table sysdba."test" backupset '/dm8/databak/sysdba.test.bak0406';

备份归档日志（恢复时不需要关库）
backup archivelog backupset '/dm8/databak/arch.bak0406';

恢复表空间
./DmServiceDMSERVER stop
/dmrman
restore database '/dm8/data/DAMENG/dm.ini' tablespace main from backupset '/dm8/databak/main.tabp.bak0407'
recover database '/dm8/data/DAMENG/dm.ini' tablespace main
启动数据库
./DmServiceDMSERVER start

1.修改配置参数dmarch.ini
ARCH_SPACE_LIMIT
ARCH_SPACE_LIMIT:Redo日志归档空间限制,当同一节点的本地归档文件或远程归档文件达到限制值时,系统自动删除最早生成的归档日志文件.
0表示无空间限制,取值范围(1024M~2147483647M),对本地归档和远程归档有效,缺省为0

或是通过SQL修改
alter database modify archivelog 'DEST=/dmdbms/arch, TYPE=LOCAL, FILE_SIZE=2048, SPACE_LIMIT=102400';

2.修改配置参数dmarch.ini
ARCH_RESERVE_TIME:归档日志保留时间,单位分钟,取值范围(0~4294967294)只对远程归档和本地归档有效.服务器每隔5分钟检查是否存在超过保留时间的归档并删除.默认为0,表示不删除归档.

3.调用存储过程SF_ARCHIVELOG_DELETE_BEFORE_TIME
如:删除一天之前的归档日志
SELECT SF_ARCHIVELOG_DELETE_BEFORE_TIME(SYSDATE-1);

可以创建执行脚本后台运行
vi test.sh
#!/bin/bash
disql SYSDBA/'"dameng123"'@192.168.1.136:5236<<EOF
select sf_archivelog_delete_before_time(sysdate-1);
exit
EOF

4.调用存储过程SF_ARCHIVELOG_DELETE_BEFORE_LSN
该函数返回删除的归档日志文件个数，其中归档日志的有效LSN范围可以通过V$ARCH_FILE查看.
删除 LSN 值小于 95560 的归档日志文件
SELECT SF_ARCHIVELOG_DELETE_BEFORE_LSN(95560);

5.dmrachk工具
删除该时间点之前的文件
dmrachk ARCH_PATH=/dmdbms/arch CHECK=4 BEFORE_TIME=2022-05-16

删除该LSN之前的文件
dmrachk ARCH_PATH=/dmdbms/arch CHECK=4 BEFORE_LSN=4296300

标签：database,恢复,备份,backupset,select,dm8,backup,达梦
From： https://www.cnblogs.com/priestess-zhao/p/16987126.html