DM7使用dmrestore執行指定時間點或LSN還原
使用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可以達到同樣的效果。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2762172/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- DM7使用dmrestore執行並行對映檔案還原REST並行
- DM7使用DMRMAN恢復資料庫到指定時間點/LSN資料庫
- DM7使用dmrestore工具還原資料庫REST資料庫
- DM7使用Disql執行表空間還原SQL
- DM7使用DMRMAN執行表空間還原
- DM7使用dmrestore工具利用歸檔日誌還原REST
- DM7使用Disql執行表還原SQL
- DM7使用DMRMAN執行歸檔還原
- DM7使用DMRMAN對資料庫執行指定對映檔案還原資料庫
- DM7使用Disql執行表還原的複雜用法SQL
- DM7使用DMRMAN執行資料庫還原和恢復資料庫
- DM7使用聯機執行SQL語句進行備份還原SQL
- 使用聯機SQL執行表空間還原(一)SQL
- 使用Disql執行表空間還原的複雜用法SQL
- 達夢使用聯機SQL執行表空間還原(二)SQL
- Linux時間還原Linux
- 我命由我不由天！如何只讓程式執行指定時間？
- DM7使用DMRMAN執行歸檔備份
- DM7使用DMRAMN執行歸檔恢復
- DM7使用DMRMAN執行歸檔修復
- win10怎麼使用還原點還原系統_win10使用還原點還原系統的步驟Win10
- EsgynDB執行備份還原時提示：Snapshot metadata is currently locked
- DM7使用DMRAMN執行備份集恢復
- java獲取指定日期之前或之後的時間Java
- Springboot啟動時執行指定程式碼Spring Boot
- DM7聯機執行SQL語句進行表空間備份SQL
- apscheduler 在伺服器上沒有按照指定的時間執行定時任務伺服器
- 如何用GNU time查詢Linux命令或程式的執行時間Linux
- DM7使用DMRAMN執行更新DB_MAGIC恢復
- DM7使用DMRMAN執行RAC資料庫恢復資料庫
- PAT-B 1026 程式執行時間【時間】
- DM7使用dmrestore工具利用不同資料庫的歸檔恢復資料庫REST資料庫
- MyBatis列印SQL執行時間MyBatisSQL
- 正常執行時間監控
- 提高codeing執行時間效率
- sleep 時間段不佔指令碼執行時間指令碼
- Linux 檢視程式啟動時間、執行時間Linux
- oracle查詢sql執行耗時、執行時間、sql_idOracleSQL