达梦数据库主备集群还原恢复到某一时刻的操作手册
|
187
|
0
|
DM

645 字
|
12 分钟
本文于 12 天前发布，最后更新于 12 天前

一、操作思路

  1. 创建一个普通用户 TEST
  2. 停止数据库集群
  3. 将数据库集群整体进行还原恢复，恢复到创建 TEST 用户之前的某一时刻
  4. 登录数据库验证 TEST 用户如预期一样不存在，则证明数据库还原恢复成功

二、操作步骤

2.1 创建 TEST 用户

[dmdba@shine ~]$ date
Wed 01 Mar 2023 11:51:58 AM CST
[dmdba@shine ~]$ disql SYSDBA/'"***!"':5236
 
服务器[LOCALHOST:5236]:处于主库打开状态
登录使用时间 : 1.745(ms)
disql V8
SQL> CREATE USER "TEST" IDENTIFIED BY "TEST123456789"; 
操作已执行
已用时间: 13.811(毫秒). 执行号:3200.
SQL> EXIT
 
[dmdba@shine ~]$ date
Wed 01 Mar 2023 11:53:01 AM CST

注意：记录创建用户前后时间，用于决定后续还原时的具体时刻。

2.2 停止数据库集群

## 停备库守护进程
[dmdba@shine ~]$ DmWatcherServiceWatcher stop
## 停主库守护进程
[dmdba@shine ~]$ DmWatcherServiceWatcher stop
## 停主库数据库服务
[dmdba@shine ~]$ DmServiceDMDW01 stop
## 停备库数据库服务
[dmdba@shine ~]$ DmServiceDMDW02 stop

2.3 将全部备份文件传到备库服务器

全部备份文件包括：当周的全量备份 + 当日的增量备份 + 归档

全量
[dmdba@shine ~]$ scp -r /backup/5236/full/DB_DAMENG_FULL_2023_02_25_01_00_42/ dmdba@192.168.111.105:/backup/5236/
dmdba@192.168.111.105's password: 
DB_DAMENG_FULL_2023_02_25_01_00_42.bak                                                                                             100%   51MB  99.5MB/s   00:00    
DB_DAMENG_FULL_2023_02_25_01_00_42_1.bak                                                                                           100% 6656     2.2MB/s   00:00    
DB_DAMENG_FULL_2023_02_25_01_00_42.meta                                                                                            100%   85KB  19.4MB/s   00:00    
 
增量
[dmdba@shine ~]$ scp -r /backup/5236/incr/DB_DAMENG_INCREMENT_2023_02_26_02_00_43/ dmdba@192.168.111.105:/backup/5236/
dmdba@192.168.111.105's password: 
DB_DAMENG_INCREMENT_2023_02_26_02_00_43.bak                                                                                        100%  391KB  46.9MB/s   00:00    
DB_DAMENG_INCREMENT_2023_02_26_02_00_43.meta                                                                                       100%   77KB  21.1MB/s   00:00    
[dmdba@shine ~]$ scp -r /backup/5236/incr/DB_DAMENG_INCREMENT_2023_02_27_02_00_46/ dmdba@192.168.111.105:/backup/5236/
dmdba@192.168.111.105's password: 
DB_DAMENG_INCREMENT_2023_02_27_02_00_46.bak                                                                                        100%  391KB  29.5MB/s   00:00    
DB_DAMENG_INCREMENT_2023_02_27_02_00_46.meta                                                                                       100%   77KB  18.5MB/s   00:00    
[dmdba@shine ~]$ scp -r /backup/5236/incr/DB_DAMENG_INCREMENT_2023_02_28_02_00_46/ dmdba@192.168.111.105:/backup/5236/
dmdba@192.168.111.105's password: 
DB_DAMENG_INCREMENT_2023_02_28_02_00_46.bak                                                                                        100%  391KB  29.6MB/s   00:00    
DB_DAMENG_INCREMENT_2023_02_28_02_00_46.meta                                                                                       100%   77KB  20.2MB/s   00:00    
[dmdba@shine ~]$ scp -r /backup/5236/incr/DB_DAMENG_INCREMENT_2023_03_01_02_00_48/ dmdba@192.168.111.105:/backup/5236/
dmdba@192.168.111.105's password: 
DB_DAMENG_INCREMENT_2023_03_01_02_00_48.bak                                                                                        100%  391KB  29.9MB/s   00:00    
DB_DAMENG_INCREMENT_2023_03_01_02_00_48.meta                                                                                       100%   77KB  19.3MB/s   00:00    
 
归档
[dmdba@shine ~]$ cd /data/5236/DAMENG/arch/
[dmdba@shine arch]$ ls -ltr
total 2097456
-rw-r--r-- 1 dmdba dinstall     311296 Feb 21 10:54 'ARCHIVE_LOCAL1_0x49734407[0]_2023-02-21_10-51-01.log'
-rw-r--r-- 1 dmdba dinstall 1073741824 Feb 21 11:54 'ARCHIVE_LOCAL1_0x49734407[0]_2023-02-21_11-52-19.log'
-rw-r--r-- 1 dmdba dinstall 1073741824 Mar  1 11:52 'ARCHIVE_LOCAL1_0x7AA0563B[0]_2023-02-21_12-17-06.log'                                                                             
 
[dmdba@shine arch]$ scp -r ./ARCHIVE_LOCAL1_0x7AA0563B\[0\]_2023-02-21_12-17-06.log dmdba@192.168.111.105:/backup/5236/
dmdba@192.168.111.105's password: 
ARCHIVE_LOCAL1_0x7AA0563B[0]_2023-02-21_12-17-06.log                                                                                  100% 1024MB 104.9MB/s   00:09

说明：ARCHIVE_LOCAL1_0x7AA0563B [0]_2023-02-21_12-17-06.log 是 2 月 21 日以后的归档，包含当前的数据日志，所以只需要这一个归档日志即可

2.4 利用备份分别还原恢复主备库

从 2.1 可知是 11:51:58~11:53:01 之间创建的 TEST 用户，所以还原到 11:51:00 一定没有 TEST 用户。

主库还原恢复

RMAN> RESTORE DATABASE '/data/5236/DAMENG/dm.ini' FROM BACKUPSET '/backup/5236/incr/DB_DAMENG_INCREMENT_2023_03_01_02_00_48';
RMAN> RECOVER DATABASE '/data/5236/DAMENG/dm.ini' WITH ARCHIVEDIR '/data/5236/DAMENG/arch' UNTIL TIME '2023-03-01 11:51:00';
RMAN> RECOVER DATABASE '/data/5236/DAMENG/dm.ini' UPDATE DB_MAGIC;

备库还原恢复

RMAN> RESTORE DATABASE '/data/5236/DAMENG/dm.ini' FROM BACKUPSET '/backup/5236/DB_DAMENG_INCREMENT_2023_03_01_02_00_48';
RMAN> RECOVER DATABASE '/data/5236/DAMENG/dm.ini' WITH ARCHIVEDIR '/backup/5236' UNTIL TIME '2023-03-01 11:51:00';
RMAN> RECOVER DATABASE '/data/5236/DAMENG/dm.ini' UPDATE DB_MAGIC;

备注：
利用增量备份进行还原时可以指定基备份
RESTORE DATABASE '/data/5236/DAMENG/dm.ini' FROM BACKUPSET '/backup/5236/incr/DB_DAMENG_INCREMENT_2023_03_01_02_00_48' WITH BACKUPDIR '/backup/5236/full/';

还原过程记录

RMAN> RESTORE DATABASE '/data/5236/DAMENG/dm.ini' FROM BACKUPSET '/backup/5236/incr/DB_DAMENG_INCREMENT_2023_03_01_02_00_48';
RESTORE DATABASE '/data/5236/DAMENG/dm.ini' FROM BACKUPSET '/backup/5236/incr/DB_DAMENG_INCREMENT_2023_03_01_02_00_48'; 
[Percent:0.00%][Speed:0.00M/s][Cost:00:00:01][Remaining:00:00:00]Normal of FAST                     
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
[Percent:99.99%][Speed:0.00M/s][Cost:00:00:03][Remaining:00:00:00]                                  
restore successfully.
time used: 00:00:03.790
 
 
RMAN> RECOVER DATABASE '/data/5236/DAMENG/dm.ini' WITH ARCHIVEDIR '/data/5236/DAMENG/arch' UNTIL TIME '2023-03-01 11:51:00';
RECOVER DATABASE '/data/5236/DAMENG/dm.ini' WITH ARCHIVEDIR '/data/5236/DAMENG/arch' UNTIL TIME '2023-03-01 11:51:00';
Database mode = 1, oguid = 453333
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[7516983], file_lsn[7516983]
EP:0 total 2 pkgs applied, percent: 13%
EP:0 total 4 pkgs applied, percent: 26%
EP:0 total 6 pkgs applied, percent: 40%
EP:0 total 8 pkgs applied, percent: 53%
EP:0 total 10 pkgs applied, percent: 66%
EP:0 total 12 pkgs applied, percent: 80%
EP:0 total 14 pkgs applied, percent: 93%
EP:0 total 15 pkgs applied, percent: 100%
recover successfully!
time used: 258.084(ms)
 
 
RMAN> RECOVER DATABASE '/data/5236/DAMENG/dm.ini' UPDATE DB_MAGIC;
RECOVER DATABASE '/data/5236/DAMENG/dm.ini' UPDATE DB_MAGIC;
Database mode = 1, oguid = 453333
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[7517011], file_lsn[7517011]
recover successfully!
time used: 988.710(ms)
RMAN> exit
time used: 0.196(ms)

2.5 启动主备库 dmserver

主库
[dmdba@shine ~]$ DmServiceDMDW01 start
Starting DmServiceDMDW01:                                  [ OK ]
 
备库
[dmdba@shine ~]$ DmServiceDMDW02 start
Starting DmServiceDMDW02:                                  [ OK ]

2.6 修改备库模式为 STANDBY

[dmdba@shine ~]$ disql SYSDBA/'"***!"':5236
 
服务器[LOCALHOST:5236]:处于主库配置状态
登录使用时间 : 1.650(ms)
disql V8
SQL> SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS',1);
DMSQL 过程已成功完成
已用时间: 3.952(毫秒). 执行号:0.
SQL> ALTER DATABASE STANDBY; 
操作已执行
已用时间: 10.360(毫秒). 执行号:0.
SQL> SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS',0);
DMSQL 过程已成功完成
已用时间: 3.743(毫秒). 执行号:1.
SQL> EXIT

2.7 启动主备库的守护进程

主/备
[dmdba@shine ~]$ DmWatcherServiceWatcher start
Starting DmWatcherServiceWatcher:                           [ OK ]

2.8 使用监视器查看集群状态

[dmdba@shine ~]$ /dmdba/bin/dmmonitor /data/5236/DAMENG/dmmonitor.ini 
[monitor]         2023-03-01 14:05:43: DMMONITOR[4.0] V8
[monitor]         2023-03-01 14:05:43: DMMONITOR[4.0] IS READY.
 
[monitor]         2023-03-01 14:05:43: 收到守护进程(DAMENG02)消息
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN            
                  2023-03-01 14:05:43  OPEN           OK        DAMENG02      OPEN        STANDBY   NULL     6        7519460         7519460         
 
[monitor]         2023-03-01 14:05:43: 收到守护进程(DAMENG01)消息
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN            
                  2023-03-01 14:05:42  OPEN           OK        DAMENG01      OPEN        PRIMARY   VALID    6        7519460         7519460         
 
show
2023-03-01 14:05:44 
#================================================================================#
GROUP            OGUID       MON_CONFIRM     MODE            MPP_FLAG  
GRP3             453333      TRUE            AUTO            FALSE     
 
 
<<DATABASE GLOBAL INFO:>>
DW_IP               MAL_DW_PORT  WTIME                WTYPE     WCTLSTAT  WSTATUS        INAME            INST_OK   N_EP  N_OK  ISTATUS     IMODE     DSC_STATUS     RTYPE     RSTAT    
192.168.111.104     52145        2023-03-01 14:05:43  GLOBAL    VALID     OPEN           DAMENG01      OK        1     1     OPEN        PRIMARY   DSC_OPEN       REALTIME  VALID    
 
EP INFO:
INST_IP             INST_PORT  INST_OK   INAME            ISTATUS     IMODE     DSC_SEQNO  DSC_CTL_NODE RTYPE     RSTAT    FSEQ            FLSN            CSEQ            CLSN            DW_STAT_FLAG          
192.168.111.104     5236       OK        DAMENG01      OPEN        PRIMARY   0          0            REALTIME  VALID    2489041         7519460         2489041         7519460         NONE                  
 
<<DATABASE GLOBAL INFO:>>
DW_IP               MAL_DW_PORT  WTIME                WTYPE     WCTLSTAT  WSTATUS        INAME            INST_OK   N_EP  N_OK  ISTATUS     IMODE     DSC_STATUS     RTYPE     RSTAT    
192.168.111.105     52146        2023-03-01 14:05:44  GLOBAL    VALID     OPEN           DAMENG02      OK        1     1     OPEN        STANDBY   DSC_OPEN       REALTIME  VALID    
 
EP INFO:
INST_IP             INST_PORT  INST_OK   INAME            ISTATUS     IMODE     DSC_SEQNO  DSC_CTL_NODE RTYPE     RSTAT    FSEQ            FLSN            CSEQ            CLSN            DW_STAT_FLAG          
192.168.111.105     5236       OK        DAMENG02      OPEN        STANDBY   0          0            REALTIME  VALID    2489012         7519460         2489012         7519460         NONE                  
 
DATABASE(DAMENG02) APPLY INFO FROM (DAMENG01), REDOS_PARALLEL_NUM (1):
DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[2489041, 2489041, 2489041], (RLSN, SLSN, KLSN)[7519460, 7519460, 7519460], N_TSK[0], TSK_MEM_USE[0] 
REDO_LSN_ARR: (7519460)
 
 
#================================================================================#

2.9 登录数据库查看 TEST 用户是否存在

[dmdba@shine ~]$ disql SYSDBA/'"***!"':5236
服务器[LOCALHOST:5236]:处于主库打开状态
登录使用时间 : 1.752(ms)
disql V8
SQL> SELECT USERNAME FROM DBA_USERS;
 
LINEID     USERNAME   
---------- -----------
1          SYSSSO
2          USER_DAMENG
3          SYSDBA
4          SYS
5          SYSAUDITOR
 
5 rows got
 
已用时间: 5.791(毫秒). 执行号:400.
SQL> EXIT
[dmdba@shine ~]$ disql TEST/'"TEST123456789"':5236
[-2501]:用户名或密码错误.

此时说明我们创建的 TEST 用户的确不存在，证明我们成功将数据库还原到创建 TEST 用户以前的时刻。

DMDMRMAN
暂无评论

发送评论 编辑评论 

































































































|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	




颜文字
Emoji
小恐龙
花!
	


									














上一篇
下一篇 




推荐文章





达梦数据库读写分离集群安装部署





案例：将Oracle中CONVERT函数字符串编码格式转换





达梦数据库COMPATIBLE_MODE参数详解





使用dmdbchk工具对达梦数据库进行健康检查





一文讲解达梦数据库的参数LENGTH_IN_CHAR





达梦数据库中以SQL方式备份一个表





达梦数据库日志ERROR：Server page check error!





解除达梦数据库插入超过字段长度的值时自动截断插入





案例：达梦数据库添加主键报错表中已存在这样的唯一关键字或主键





达梦数据库SQL报错之变量空间溢出