DM7使用dmrestore执行指定时间点或LSN还原

原创敬勇 2021-03-10

919

使用dmrestore执行指定时间点或LSN还原

达梦系统使用归档日志将数据库还原到最新的状态，这一节讨论与归档日志相关的另一个功能：还原到指定的时间点。根据用户需求，可以将数据库还原到指定的时间点。还原到时间点的功能依赖于归档日志，在备份完成时，系统会记录一个备份时间，因此要还原的时间点一定在备份完成时间之后，否则系统会提示报错信息。借助备份文件完成还原后，开始重做归档日志，区别于完全还原将所有可用的归档日志全部重做，若指定还原到时间点，则只重做早于时间点的日志，从而达到将数据库还原到指定时间的状态的目的。需要注意的是，由于表空间只是数据库的一部分，为保证还原后，数据库中的所有数据处于最新状态，还原表空间会重做该表空间所有可用的归档日志，因此还原表空间不支持还原到时间点功能。

比如用户在2020-7-21 19:40:20时对数据库jydm做了一个备份，在2020-7-21 20:34:20想将数据库jydm还原到时间点2020-07-21 20:00:03，若用户保证从备份完成的时间点到指定时间点这段时间的归档日志都完好，则通过如下还原命令就能达到预期效果。

下面举例说明

1.在2020-7-21 19:40:20时对数据库jydm进行备份

SQL> backup database full to full_bak_for_arch_restor bakfile '/dm_home/dmdba/dmdbms/backup/full_bak_for_arch_restor.bak';
executed successfully
used time: 00:00:07.697. Execute id is 43.

SQL> select * from t1;

LINEID     C1          C2
---------- ----------- -----------
1          1           1
2          2           2
3          3           3
4          4           4
5          5           5
6          6           6

6 rows got

used time: 0.454(ms). Execute id is 97.
SQL> select sysdate from dual;

LINEID     SYSDATE
---------- ---------------------------
1          2020-07-21 20:00:03.165365

used time: 0.994(ms). Execute id is 337.

使用select file_lsn from v$rlog命令查询此时的LSN为:30043605
SQL> select file_lsn from v$rlog;

LINEID     FILE_LSN
---------- --------------------
1          30043605

used time: 1.344(ms). Execute id is 654.

2.删除表t1中的数据

SQL> delete from t1;
affect rows 6

used time: 1.309(ms). Execute id is 681.
SQL> commit;
executed successfully
used time: 33.649(ms). Execute id is 682.
SQL> select * from t1;
no rows

used time: 18.944(ms). Execute id is 696.

3.将数据库还原到指定时间2020-07-21 20:00:03.165365

[root@shard1 oracle]# service DmServicejydm stop
Redirecting to /bin/systemctl stop  DmServicejydm.service

[dmdba@shard1 backup]$ dmrestore ini_path=/dm_home/dmdba/dmdbms/data/jydm/dm.ini file=/dm_home/dmdba/dmdbms/backup/full_bak_for_arch_restor.bak archive_dir=/dm_home/dmdba/dmdbms/data/arch time="2020-07-21 20:00:03.165365";
restore V7.1.6.46-Build(2018.02.08-89107)ENT
file dm.key not found, use default license!


backup sig:            BA
backup tool version:   12595
backup db name:        jydm
backup db magic:       1371967312
backup pemnt magic:    1250320462
backup name:           FULL_BAK_FOR_ARCH_RESTOR
backup type:           full
backup level:          online
backup range:          database
backup description:
compressed level:      0
encrypt_type:          0
encrypt_id:            2052
rac node:              0
page check:            0
rlog encrypt:          0
external cipher id:    0
external hash id:      0
length in char:        0
use new hash:          1
backup time:           2020-07-21 19:52:09
page size:             8 KB
extent size:           16
case sensitive:        1
log page size:         512 B
charset:               0
data version:          0x7000A
sys version:           V7.1.6.46-Build(2018.02.08-89107)ENT
enable policy:         0
mpp_timestamp:         1595332328
crc_check:             TRUE
parallel type:         0
parallel info len:     0
backup db fil num:     9
archive flag:          1
backup with log:       Yes
before backup LSN:     30040400
after backup LSN:      30040406


$bak_seq |$file_path                                        |$size(K)       |$used(K)
        1|/dm_home/dmdba/dmdbms/backup/full_bak_for_arch_restor.bak|      393216.00|      362036.00


$file_seq |$type|$ts_name       |$state    |$size(K)  |$seq_in_bak    |$off_in_bak(B) |$len_in_bak(B) |$file_path                                        |$mirror_path
         1|DBF  |SYSTEM         |ONLINE    |     23552|              1|          43008|        6766592|/dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF        |
         2|DBF  |ROLL           |ONLINE    |    229376|              1|        6809600|       10887168|/dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF          |
         3|DBF  |MAIN           |ONLINE    |    276480|              1|       17696768|      240287744|/dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF          |
         4|DBF  |BOOKSHOP       |ONLINE    |    153600|              1|      257984512|         933888|/dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF      |
         5|DBF  |DMHR           |ONLINE    |    131072|              1|      258918400|         425984|/dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF          |
         6|DBF  |USERS          |ONLINE    |     51200|              1|      259344384|        1105920|/dm_home/dmdba/dmdbms/data/jydm/users01.dbf       |
         7|DBF  |SYSAUX         |ONLINE    |    179200|              1|      260450304|      110256128|/dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF        |
         8|DBF  |FG_PERSON      |ONLINE    |    131072|              1|      370706432|          16384|/dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF     |
         9|LOG  |ARCHLOG        |ONLINE    |         0|              1|      370722816|           2048|                                                  |

Continue?[Y/N]:Y
can't find useable archive file when search assigned archive directory
restore bak_file: /dm_home/dmdba/dmdbms/backup/full_bak_for_arch_restor.bak ...
start restore database...
start restore file: /dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF(SYSTEM), pages: 826
end restore file: /dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF(SYSTEM)
start restore file: /dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF(ROLL), pages: 1329
end restore file: /dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF(ROLL)
start restore file: /dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF(MAIN), pages: 29332
end restore file: /dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF(MAIN)
start restore file: /dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF(BOOKSHOP), pages: 114
end restore file: /dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF(BOOKSHOP)
start restore file: /dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF(DMHR), pages: 52
end restore file: /dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF(DMHR)
start restore file: /dm_home/dmdba/dmdbms/data/jydm/users01.dbf(USERS), pages: 135
end restore file: /dm_home/dmdba/dmdbms/data/jydm/users01.dbf(USERS)
start restore file: /dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF(SYSAUX), pages: 13459
end restore file: /dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF(SYSAUX)
start restore file: /dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF(FG_PERSON), pages: 2
end restore file: /dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF(FG_PERSON)
redo tmp arch file: /dm_home/dmdba/dmdbms/backup/RES_TMP_ARCH_20200721200858135_0.log, rpages: 16

end restore database data files.

Apply archive log LSN from 30040407 to 30040406, time used:0.000s.
restore finished, code = 0!

restore successfully!
restore time used: 7652.178(ms)

4.检查表t1的数据是否已经恢复回来了

[root@shard1 oracle]# service DmServicejydm start
Redirecting to /bin/systemctl start  DmServicejydm.service

SQL> select * from t1;

LINEID     C1          C2
---------- ----------- -----------
1          1           1
2          2           2
3          3           3
4          4           4
5          5           5
6          6           6

6 rows got

used time: 0.440(ms). Execute id is 86.

达梦系统使用归档还原，还可以指定还原特定的END_LSN，备份文件中会记录一个备份结束的LSN，如果指定END_LSN，则必须保证该END_LSN大于备份文件中的记录的最后一个LSN，否则会无视该END_LSN，而还原到最新状态，如果同时指定了TIME则会以最早的为标准。

5.上面还原数据库jydm时除了可以指定时间外也可以指定LSN：30043605

[root@shard1 oracle]# service DmServicejydm stop
Redirecting to /bin/systemctl stop  DmServicejydm.service

[dmdba@shard1 backup]$ dmrestore ini_path=/dm_home/dmdba/dmdbms/data/jydm/dm.ini file=/dm_home/dmdba/dmdbms/backup/full_bak_for_arch_restor.bak archive_dir=/dm_home/dmdba/dmdbms/data/arch end_lsn=30043605
restore V7.1.6.46-Build(2018.02.08-89107)ENT
file dm.key not found, use default license!


backup sig:            BA
backup tool version:   12595
backup db name:        jydm
backup db magic:       1371967312
backup pemnt magic:    1250320462
backup name:           FULL_BAK_FOR_ARCH_RESTOR
backup type:           full
backup level:          online
backup range:          database
backup description:
compressed level:      0
encrypt_type:          0
encrypt_id:            2052
rac node:              0
page check:            0
rlog encrypt:          0
external cipher id:    0
external hash id:      0
length in char:        0
use new hash:          1
backup time:           2020-07-21 19:52:09
page size:             8 KB
extent size:           16
case sensitive:        1
log page size:         512 B
charset:               0
data version:          0x7000A
sys version:           V7.1.6.46-Build(2018.02.08-89107)ENT
enable policy:         0
mpp_timestamp:         1595332328
crc_check:             TRUE
parallel type:         0
parallel info len:     0
backup db fil num:     9
archive flag:          1
backup with log:       Yes
before backup LSN:     30040400
after backup LSN:      30040406


$bak_seq |$file_path                                        |$size(K)       |$used(K)
        1|/dm_home/dmdba/dmdbms/backup/full_bak_for_arch_restor.bak|      393216.00|      362036.00


$file_seq |$type|$ts_name       |$state    |$size(K)  |$seq_in_bak    |$off_in_bak(B) |$len_in_bak(B) |$file_path                                        |$mirror_path
         1|DBF  |SYSTEM         |ONLINE    |     23552|              1|          43008|        6766592|/dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF        |
         2|DBF  |ROLL           |ONLINE    |    229376|              1|        6809600|       10887168|/dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF          |
         3|DBF  |MAIN           |ONLINE    |    276480|              1|       17696768|      240287744|/dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF          |
         4|DBF  |BOOKSHOP       |ONLINE    |    153600|              1|      257984512|         933888|/dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF      |
         5|DBF  |DMHR           |ONLINE    |    131072|              1|      258918400|         425984|/dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF          |
         6|DBF  |USERS          |ONLINE    |     51200|              1|      259344384|        1105920|/dm_home/dmdba/dmdbms/data/jydm/users01.dbf       |
         7|DBF  |SYSAUX         |ONLINE    |    179200|              1|      260450304|      110256128|/dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF        |
         8|DBF  |FG_PERSON      |ONLINE    |    131072|              1|      370706432|          16384|/dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF     |
         9|LOG  |ARCHLOG        |ONLINE    |         0|              1|      370722816|           2048|                                                  |

Continue?[Y/N]:Y
can't find useable archive file when search assigned archive directory
restore bak_file: /dm_home/dmdba/dmdbms/backup/full_bak_for_arch_restor.bak ...
start restore database...
start restore file: /dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF(SYSTEM), pages: 826
end restore file: /dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF(SYSTEM)
start restore file: /dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF(ROLL), pages: 1329
end restore file: /dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF(ROLL)
start restore file: /dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF(MAIN), pages: 29332
end restore file: /dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF(MAIN)
start restore file: /dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF(BOOKSHOP), pages: 114
end restore file: /dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF(BOOKSHOP)
start restore file: /dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF(DMHR), pages: 52
end restore file: /dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF(DMHR)
start restore file: /dm_home/dmdba/dmdbms/data/jydm/users01.dbf(USERS), pages: 135
end restore file: /dm_home/dmdba/dmdbms/data/jydm/users01.dbf(USERS)
start restore file: /dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF(SYSAUX), pages: 13459
end restore file: /dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF(SYSAUX)
start restore file: /dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF(FG_PERSON), pages: 2
end restore file: /dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF(FG_PERSON)
redo tmp arch file: /dm_home/dmdba/dmdbms/backup/RES_TMP_ARCH_20200721201304925_0.log, rpages: 16

end restore database data files.

Apply archive log LSN from 30040407 to 30040406, time used:0.000s.
restore finished, code = 0!

restore successfully!
restore time used: 7764.389(ms)

6.检查表t1的数据是否已经恢复回来了

[root@shard1 oracle]# service DmServicejydm start
Redirecting to /bin/systemctl start  DmServicejydm.service

SQL> select * from t1;

LINEID     C1          C2
---------- ----------- -----------
1          1           1
2          2           2
3          3           3
4          4           4
5          5           5
6          6           6

6 rows got

used time: 0.445(ms). Execute id is 47.

可以看到指定时间或LSN可以达到同样的效果。#

达梦