使用dmrestore工具利用不同数据库的归档恢复数据库
使用不同库的归档恢复数据库，应用场景如下：
1. 创建一个源库D1；
2. 做一个备份B1；
3. 在D1上做一些操作，目的是生成一些归档A1；
4. 创建一个新库D2；
5. 用B1+A1还原恢复；
6. 在D2上做一些操作，生成一些归档A2；
7. 现在有了B1，A1，A2, 理论上应该可以恢复出一个最新状态的D2。
1) 创建源库D1，即待备份的数据库，然后启动数据库并配置归档，归档目录为/dm_home/dmdba/dmdbms/data/d1_arch。
[dmdba@shard1 data]$ dminit path=/dm_home/dmdba/dmdbms/data db_name=d1 instance_name=d1 port_num=5237 unicode_flag=0 initdb V7.1.6.46-Build(2018.02.08-89107)ENT db version: 0x7000a file dm.key not found, use default license! License will expire in 14 day(s) on 2020-08-18 log file path: /dm_home/dmdba/dmdbms/data/d1/d101.log log file path: /dm_home/dmdba/dmdbms/data/d1/d102.log write to dir [/dm_home/dmdba/dmdbms/data/d1]. create dm database success. 2020-08-04 12:53:42 [root@shard1 root]# ./dm_service_installer.sh -i /dm_home/dmdba/dmdbms/data/d1/dm.ini -p d1 -t dmserver ln -s '/usr/lib/systemd/system/DmServiced1.service' '/etc/systemd/system/multi-user.target.wants/DmServiced1.service' Finished to create the service (DmServiced1) [root@shard1 root]# service DmServiced1 start Redirecting to /bin/systemctl start DmServiced1.service
–连接数据库配置归档
[dmdba@shard1 backup]$ disql SYSDBA/SYSDBA@localhost:5237 Server[localhost:5237]:mode is normal, state is open login used time: 10.168(ms) disql V7.1.6.46-Build(2018.02.08-89107)ENT Connected to: DM 7.1.6.46 SQL> alter database mount; executed successfully used time: 00:00:01.844. Execute id is 0. SQL> alter database add archivelog 'dest=/dm_home/dmdba/dmdbms/data/d1_arch,type=local,file_size=256,space_limit=10240'; executed successfully used time: 19.055(ms). Execute id is 0. SQL> alter database archivelog; executed successfully used time: 28.622(ms). Execute id is 0. SQL> alter database open; executed successfully used time: 666.842(ms). Execute id is 0. SQL> select permanent_magic; LINEID PERMANENT_MAGIC ---------- --------------- 1 -640527680 used time: 2.234(ms). Execute id is 807. SQL> select db_magic from v$rlog; LINEID DB_MAGIC ---------- ----------- 1 -181202026 used time: 1.146(ms). Execute id is 808.
2)对数据库d1执行完全备份,备份名为B1
SQL> backup database full to b1 bakfile '/dm_home/dmdba/dmdbms/backup/b1.bak'; executed successfully used time: 00:00:01.779. Execute id is 6.
3)在D1上做一些操作，目的是生成一些归档A1
SQL> create table tab_for_recover_01(c1 int); executed successfully used time: 14.595(ms). Execute id is 810. SQL> begin 2 for i in 1 .. 100000 loop 3 insert into tab_for_recover_01 values(i); 4 end loop; 5 commit; 6 end; 7 / DMSQL executed successfully used time: 00:00:01.578. Execute id is 813. [root@shard1 root]# service DmServiced1 stop Redirecting to /bin/systemctl stop DmServiced1.service
4) 创建源库D2，即待还原的数据库，然后启动数据库并配置归档，归档目录为/dm_home/dmdba/dmdbms/data/d2_arch。
[dmdba@shard1 data]$ dminit path=/dm_home/dmdba/dmdbms/data db_name=d2 instance_name=d2 port_num=5238 unicode_flag=0 initdb V7.1.6.46-Build(2018.02.08-89107)ENT db version: 0x7000a file dm.key not found, use default license! License will expire in 14 day(s) on 2020-08-18 log file path: /dm_home/dmdba/dmdbms/data/d2/d201.log log file path: /dm_home/dmdba/dmdbms/data/d2/d202.log write to dir [/dm_home/dmdba/dmdbms/data/d2]. create dm database success. 2020-08-04 13:10:20 [root@shard1 root]# ./dm_service_installer.sh -i /dm_home/dmdba/dmdbms/data/d2/dm.ini -p d2 -t dmserver ln -s '/usr/lib/systemd/system/DmServiced2.service' '/etc/systemd/system/multi-user.target.wants/DmServiced2.service' Finished to create the service (DmServiced2) [root@shard1 root]# service DmServiced2 start Redirecting to /bin/systemctl start DmServiced2.service [dmdba@shard1 backup]$ disql SYSDBA/SYSDBA@localhost:5238 Server[localhost:5238]:mode is normal, state is open login used time: 9.777(ms) disql V7.1.6.46-Build(2018.02.08-89107)ENT Connected to: DM 7.1.6.46 SQL> alter database mount; executed successfully used time: 00:00:01.878. Execute id is 0. SQL> alter database add archivelog 'dest=/dm_home/dmdba/dmdbms/data/d2_arch,type=local,file_size=256,space_limit=10240'; executed successfully used time: 32.468(ms). Execute id is 0. SQL> alter database archivelog; executed successfully used time: 70.806(ms). Execute id is 0. SQL> alter database open; executed successfully used time: 752.732(ms). Execute id is 0.
5)利用源库D1的备份B1及归档A1还原数据库到D2。
[root@shard1 root]# service DmServiced2 stop Redirecting to /bin/systemctl stop DmServiced2.service [dmdba@shard1 backup]$ dmrestore ini_path=/dm_home/dmdba/dmdbms/data/d2/dm.ini file=/dm_home/dmdba/dmdbms/backup/b1.bak archive_dir=/dm_home/dmdba/dmdbms/data/d1_arch 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: d1 backup db magic: -181202026 backup pemnt magic: -640527680 backup name: B1 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-08-04 13:26:27 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: 1596518786 crc_check: TRUE parallel type: 0 parallel info len: 0 backup db fil num: 4 archive flag: 1 backup with log: Yes before backup LSN: 137137 after backup LSN: 137137 $bak_seq |$file_path |$size(K) |$used(K) 1|/dm_home/dmdba/dmdbms/backup/b1.bak | 32768.00| 15558.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 | 21504| 1| 22528| 5095424|/dm_home/dmdba/dmdbms/data/d1/SYSTEM.DBF | 2|DBF |ROLL |ONLINE | 131072| 1| 5117952| 10780672|/dm_home/dmdba/dmdbms/data/d1/ROLL.DBF | 3|DBF |MAIN |ONLINE | 131072| 1| 15898624| 32768|/dm_home/dmdba/dmdbms/data/d1/MAIN.DBF | 4|LOG |ARCHLOG |ONLINE | 0| 1| 15931392| 0| | Continue?[Y/N]:Y restore bak_file: /dm_home/dmdba/dmdbms/backup/b1.bak ... start restore database... start restore file: /dm_home/dmdba/dmdbms/data/d2/SYSTEM.DBF(SYSTEM), pages: 622 end restore file: /dm_home/dmdba/dmdbms/data/d2/SYSTEM.DBF(SYSTEM) start restore file: /dm_home/dmdba/dmdbms/data/d2/ROLL.DBF(ROLL), pages: 1316 end restore file: /dm_home/dmdba/dmdbms/data/d2/ROLL.DBF(ROLL) start restore file: /dm_home/dmdba/dmdbms/data/d2/MAIN.DBF(MAIN), pages: 4 end restore file: /dm_home/dmdba/dmdbms/data/d2/MAIN.DBF(MAIN) end restore database data files. Apply archive log LSN from 137138 to 239573, time used:6.531s. restore finished, code = 0! restore successfully! restore time used: 9133.827(ms)
6)在D2上做一些操作，生成一些归档A2；
[root@shard1 root]# service DmServiced2 start Redirecting to /bin/systemctl start DmServiced2.service [dmdba@shard1 backup]$ disql SYSDBA/SYSDBA@localhost:5238 Server[localhost:5238]:mode is normal, state is open login used time: 10.015(ms) disql V7.1.6.46-Build(2018.02.08-89107)ENT Connected to: DM 7.1.6.46 SQL> select count(*) from tab_for_recover_01; LINEID COUNT(*) ---------- -------------------- 1 100000 used time: 3.284(ms). Execute id is 3. SQL> begin 2 for i in 100001 .. 200000 loop 3 insert into tab_for_recover_01 values(i); 4 end loop; 5 commit; 6 end; 7 / DMSQL executed successfully used time: 00:00:01.428. Execute id is 4. SQL> commit; executed successfully used time: 0.338(ms). Execute id is 5. SQL> select count(*) from tab_for_recover_01; LINEID COUNT(*) ---------- -------------------- 1 200000 used time: 0.289(ms). Execute id is 6.
7)关闭数据库D2，利用备份文件B1、归档A1、A2还原数据库到D2。
[root@shard1 root]# service DmServiced2 stop Redirecting to /bin/systemctl stop DmServiced2.service
利用备份文件B1、归档A1还原目标库
[dmdba@shard1 backup]$ dmrestore ini_path=/dm_home/dmdba/dmdbms/data/d2/dm.ini file=/dm_home/dmdba/dmdbms/backup/b1.bak archive_dir=/dm_home/dmdba/dmdbms/data/d1_arch 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: d1 backup db magic: -181202026 backup pemnt magic: -640527680 backup name: B1 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-08-04 13:26:27 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: 1596518786 crc_check: TRUE parallel type: 0 parallel info len: 0 backup db fil num: 4 archive flag: 1 backup with log: Yes before backup LSN: 137137 after backup LSN: 137137 $bak_seq |$file_path |$size(K) |$used(K) 1|/dm_home/dmdba/dmdbms/backup/b1.bak | 32768.00| 15558.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 | 21504| 1| 22528| 5095424|/dm_home/dmdba/dmdbms/data/d1/SYSTEM.DBF | 2|DBF |ROLL |ONLINE | 131072| 1| 5117952| 10780672|/dm_home/dmdba/dmdbms/data/d1/ROLL.DBF | 3|DBF |MAIN |ONLINE | 131072| 1| 15898624| 32768|/dm_home/dmdba/dmdbms/data/d1/MAIN.DBF | 4|LOG |ARCHLOG |ONLINE | 0| 1| 15931392| 0| | Continue?[Y/N]:Y restore bak_file: /dm_home/dmdba/dmdbms/backup/b1.bak ... start restore database... start restore file: /dm_home/dmdba/dmdbms/data/d2/SYSTEM.DBF(SYSTEM), pages: 622 end restore file: /dm_home/dmdba/dmdbms/data/d2/SYSTEM.DBF(SYSTEM) start restore file: /dm_home/dmdba/dmdbms/data/d2/ROLL.DBF(ROLL), pages: 1316 end restore file: /dm_home/dmdba/dmdbms/data/d2/ROLL.DBF(ROLL) start restore file: /dm_home/dmdba/dmdbms/data/d2/MAIN.DBF(MAIN), pages: 4 end restore file: /dm_home/dmdba/dmdbms/data/d2/MAIN.DBF(MAIN) end restore database data files. Apply archive log LSN from 137138 to 239573, time used:6.839s. restore finished, code = 0! restore successfully! restore time used: 9586.006(ms)
查看归档A2的db_magic
[dmdba@shard1 d2_arch]$ ls -lrt 总用量 12984 -rw-r--r-- 1 dmdba dinstall 207872 8月 4 13:17 ARCHIVE_LOCAL1_20200804131521529_0.log -rw-r--r-- 1 dmdba dinstall 268435456 8月 4 13:32 ARCHIVE_LOCAL1_20200804133104014_0.log [dmdba@shard1 backup]$ dmrachk arch_fil=/dm_home/dmdba/dmdbms/data/d2_arch/ARCHIVE_LOCAL1_20200804131521529_0.log rachk V7.1.6.46-Build(2018.02.08-89107)ENT /*******************************************************************/ archive file /dm_home/dmdba/dmdbms/data/d2_arch/ARCHIVE_LOCAL1_20200804131521529_0.log itemize. version : 0x7004 status : INACTIVE n_rpags : 398 db_magic : 399359200 pemnt_magic : -1514196346 arch_lsn : 31701 arch_seq : 13564 clsn : 34492 next_seq : 13961 file len : 207872 file free : 207872 create time : 2020-08-04 13:15:21 close time : 2020-08-04 13:17:38 crc_check : TRUE /*******************************************************************/ The SUMMARY(seqno[0]): total files: 1 okey files: 1 fail file: 0 repeat file: 0 the rachk tool running cost 0.435 ms [dmdba@shard1 backup]$ dmrachk arch_fil=/dm_home/dmdba/dmdbms/data/d2_arch/ARCHIVE_LOCAL1_20200804133104014_0.log rachk V7.1.6.46-Build(2018.02.08-89107)ENT /*******************************************************************/ archive file /dm_home/dmdba/dmdbms/data/d2_arch/ARCHIVE_LOCAL1_20200804133104014_0.log itemize. version : 0x7004 status : INACTIVE n_rpags : 25688 db_magic : 349450744 pemnt_magic : -640527680 arch_lsn : 239573 arch_seq : 13962 clsn : 343441 next_seq : 39649 file len : 13156352 file free : 13156352 create time : 2020-08-04 13:31:04 close time : 2020-08-04 13:40:35 crc_check : TRUE /*******************************************************************/ The SUMMARY(seqno[0]): total files: 1 okey files: 1 fail file: 0 repeat file: 0 the rachk tool running cost 0.394 ms
修改目标库D2的db_magic与A2的一致，A2的db_magic为349450744
[dmdba@shard1 d2]$ ls -lrt 总用量 544144 drwxr-xr-x 2 dmdba dinstall 6 8月 4 13:10 bak -rw-r--r-- 1 dmdba dinstall 479 8月 4 13:10 sqllog.ini drwxr-xr-x 2 dmdba dinstall 6 8月 4 13:10 HMAIN -rw-r--r-- 1 dmdba dinstall 880 8月 4 13:10 dminit20200804131010.log drwxr-xr-x 2 dmdba dinstall 6 8月 4 13:14 trace -rw-r--r-- 1 dmdba dinstall 12 8月 4 13:14 rep_conflict.log -rw-r--r-- 1 dmdba dinstall 269 8月 4 13:15 dmarch.ini -rw-r--r-- 1 dmdba dinstall 220 8月 4 13:43 dminst.sys -rw-r--r-- 1 dmdba dinstall 10485760 8月 4 13:43 TEMP.DBF -rw-r--r-- 1 dmdba dinstall 5120 8月 4 13:43 dm.ctl drwxr-xr-x 2 dmdba dinstall 4096 8月 4 13:43 ctl_bak -rw-r--r-- 1 dmdba dinstall 134217728 8月 4 13:43 ROLL.DBF -rw-r--r-- 1 dmdba dinstall 134217728 8月 4 13:43 MAIN.DBF -rw-r--r-- 1 dmdba dinstall 268435456 8月 4 13:43 d201.log -rw-r--r-- 1 dmdba dinstall 268435456 8月 4 13:43 d202.log -rw-r--r-- 1 dmdba dinstall 633 8月 4 13:43 dm_service.prikey -rw-r--r-- 1 dmdba dinstall 40845 8月 4 13:43 dm.ini -rw-r--r-- 1 dmdba dinstall 22020096 8月 4 13:43 SYSTEM.DBF [dmdba@shard1 d2]$ dmmdf type=1 file=SYSTEM.DBF dmmdf V7.1.6.46-Build(2018.02.08-89107)ENT ********************************************************** 1 db_magic=-844697348 2 next_trxid=1127 3 pemnt_magic=-640527680 ********************************************************** Please input which parameter you want to change(1-3), q to quit: 1 Please input new value: 349450744 ********************************************************** 1 db_magic=349450744 2 next_trxid=1127 3 pemnt_magic=-640527680 ********************************************************** Do you want to save the change to file (y/n): Y Save to file success! [dmdba@shard1 d2]$ dmmdf type=2 file=d201.log dmmdf V7.1.6.46-Build(2018.02.08-89107)ENT ********************************************************** 1 sig = DMRLOG 2 ver = 7004 3 chksum = 0 4 dbversion = 0x7000a 5 sta = 0 6 n_magic = 35 7 db_magic = -844697348 8 clsn_fil = 0 10 next_seq = 39650 11 arch_seq = 0 12 len = 268435456 13 free = 4096 14 clsn = 239573 15 clsn_off = 4096 16 arch_lsn = 0 17 pemnt_magic = -640527680 18 fil_id = 0 19 crc_check = 1 ********************************************************** You can only reset sta(5) or db_magic (7) or clsn (14) or pemnt_magic(17) or fil_id(18). Please input the num which one you want to change, q to quit: 7 Input the new value: 349450744 ********************************************************** 1 sig = DMRLOG 2 ver = 7004 3 chksum = 0 4 dbversion = 0x7000a 5 sta = 0 6 n_magic = 35 7 db_magic = 349450744 8 clsn_fil = 0 10 next_seq = 39650 11 arch_seq = 0 12 len = 268435456 13 free = 4096 14 clsn = 239573 15 clsn_off = 4096 16 arch_lsn = 0 17 pemnt_magic = -640527680 18 fil_id = 0 19 crc_check = 1 ********************************************************** Do you want to quit and save the change to file (y/n): Y Save to file success! [dmdba@shard1 d2]$ dmmdf type=2 file=d202.log dmmdf V7.1.6.46-Build(2018.02.08-89107)ENT ********************************************************** 1 sig = DMRLOG 2 ver = 7004 3 chksum = 0 4 dbversion = 0x7000a 5 sta = 0 6 n_magic = 35 7 db_magic = -844697348 8 clsn_fil = 0 10 next_seq = 0 11 arch_seq = 0 12 len = 268435456 13 free = 4096 14 clsn = 0 15 clsn_off = 0 16 arch_lsn = 0 17 pemnt_magic = -640527680 18 fil_id = 1 19 crc_check = 1 ********************************************************** You can only reset sta(5) or db_magic (7) or clsn (14) or pemnt_magic(17) or fil_id(18). Please input the num which one you want to change, q to quit: 7 Input the new value: 349450744 ********************************************************** 1 sig = DMRLOG 2 ver = 7004 3 chksum = 0 4 dbversion = 0x7000a 5 sta = 0 6 n_magic = 35 7 db_magic = 349450744 8 clsn_fil = 0 10 next_seq = 0 11 arch_seq = 0 12 len = 268435456 13 free = 4096 14 clsn = 0 15 clsn_off = 0 16 arch_lsn = 0 17 pemnt_magic = -640527680 18 fil_id = 1 19 crc_check = 1 ********************************************************** Do you want to quit and save the change to file (y/n): Y Save to file success!
利用归档A2恢复。
[dmdba@shard1 backup]$ dmrestore ini_path=/dm_home/dmdba/dmdbms/data/d2/dm.ini archive_dir=/dm_home/dmdba/dmdbms/data/d2_arch res_type=2 restore V7.1.6.46-Build(2018.02.08-89107)ENT file dm.key not found, use default license! Continue?[Y/N]:Y try to apply archive log from LSN: 239573 to LSN: 9223372036854775807. Apply archive log LSN from 239574 to 343441, time used:1.372s. restore successfully! restore time used: 2406.492(ms)
8)启动数据D2查看结果。
[root@shard1 root]# service DmServiced2 start Redirecting to /bin/systemctl start DmServiced2.service SQL> select count(*) from tab_for_recover_01; LINEID COUNT(*) ---------- -------------------- 1 200000 used time: 0.309(ms). Execute id is 4.