DM7使用DMRMAN恢復資料庫到指定時間點/LSN
使用DMRMAN恢復資料庫到指定時間點/LSN
恢復資料庫到指定時間點/LSN是從歸檔恢復的一種方式，也稱為不完全恢復。從歸檔恢復允許恢復到指定的時間點及指定的LSN值。若同時指定了時間點和LSN，則以較早的為結束點。使用者可以透過指定一個時間點/LSN，使資料庫恢復到這個指定的時間點/LSN。
例如，使用者在下午5點做了一個誤操作，刪除了某些重要資料；我們可以指定恢復時間點到下午4:59分，恢復被誤刪除的資料。
下面以聯機資料庫備份為例說明如何恢復資料庫到指定的時間點/LSN。
1) 準備資料。
SQL> create table tab_for_recover_01(c1 int); executed successfully used time: 62.140(ms). Execute id is 29. SQL> insert into tab_for_recover_01 values(1); affect rows 1 used time: 1.765(ms). Execute id is 30. SQL> commit; executed successfully used time: 1.582(ms). Execute id is 31.
2) 備份資料庫。
SQL> backup database full to db_rac_full_bak_for_time_lsn backupset '/dm7/backup/dm_rac_full_bak_for_time_lsn'; executed successfully used time: 00:00:01.687. Execute id is 32.
3) 正確運算元據庫，產生一些歸檔。
SQL> create table tab_for_recover_02(c1 int); executed successfully used time: 12.213(ms). Execute id is 33. SQL> insert into tab_for_recover_02 values(1); affect rows 1 used time: 1.654(ms). Execute id is 34. SQL> commit; executed successfully used time: 1.331(ms). Execute id is 35.
使用select sysdate命令查詢此時的時間為：2020-06-19 15:23:17.809757
SQL> select sysdate; LINEID SYSDATE ---------- --------------------------- 1 2020-06-19 15:23:17.809757 used time: 1.571(ms). Execute id is 36.
使用select file_lsn from v$rlog命令查詢此時的LSN為:80126
SQL> select file_lsn from v$rlog; LINEID FILE_LSN ---------- -------------------- 1 80126 used time: 1.490(ms). Execute id is 37.
4) 誤運算元據庫。此步驟誤刪除了表tab_for_recover_01中資料。
SQL> delete from tab_for_recover_01; affect rows 1 used time: 1.798(ms). Execute id is 38. SQL> commit; executed successfully used time: 1.435(ms). Execute id is 39.
使用select sysdate命令查詢此時的時間為：2020-06-19 15:25:56.982456
SQL> select sysdate; LINEID SYSDATE ---------- --------------------------- 1 2020-06-19 15:25:56.982456 used time: 0.898(ms). Execute id is 40.
使用select file_lsn from v$rlog命令查詢此時的LSN為:80131
SQL> select file_lsn from v$rlog; LINEID FILE_LSN ---------- -------------------- 1 80131 used time: 0.536(ms). Execute id is 41.
5)將備份檔案與歸檔日誌傳輸到目標庫(這裡不在源庫進行恢復)
[dmdba@dmrac1 backup]$ scp -r dm_rac_full_bak_for_time_lsn/ dmdba@10.138.130.187:/dm_home/dmdbms/backup/ dmdba@10.138.130.187's password: dm_rac_full_bak_for_time_lsn.bak 100% 25MB 25.5MB/s 00:00 dm_rac_full_bak_for_time_lsn_1.bak 100% 20KB 20.0KB/s 00:00 dm_rac_full_bak_for_time_lsn.meta 100% 81KB 80.5KB/s 00:00 [dmdba@dmrac1 backup]$
這裡是RAC環境，需要將兩個例項的歸檔都要傳輸到目標庫
[dmdba@dmrac1 data]$ scp -r rac0_arch dmdba@10.18.10.187:/dm_home/dmdbms/backup/ dmdba@10.18.10.187's password: ARCHIVE_LOCAL1_20200617163125381_0.log 100% 256MB 85.3MB/s 00:03 ARCHIVE_LOCAL1_20200617154121539_0.log 100% 8704 8.5KB/s 00:00 ARCHIVE_LOCAL1_20200615164953273_0.log 100% 160KB 160.0KB/s 00:00 ARCHIVE_LOCAL1_20200615165648166_0.log 100% 157KB 157.0KB/s 00:00 ARCHIVE_LOCAL1_20200528202150715_0.log 100% 277KB 277.0KB/s 00:00 ARCHIVE_LOCAL1_20200608151836879_0.log 100% 158KB 157.5KB/s 00:00 ARCHIVE_LOCAL1_20200609150852829_0.log 100% 157KB 157.0KB/s 00:00 ARCHIVE_LOCAL1_20200608152638617_0.log 100% 159KB 158.5KB/s 00:00 ARCHIVE_LOCAL1_20200609170732487_0.log 100% 161KB 160.5KB/s 00:00 ARCHIVE_LOCAL1_20200615172117341_0.log 100% 159KB 158.5KB/s 00:00 ARCHIVE_LOCAL1_20200615171042444_0.log 100% 159KB 159.0KB/s 00:00 ARCHIVE_LOCAL1_20200605154214367_0.log 100% 157KB 157.0KB/s 00:00 ARCHIVE_LOCAL1_20200616164818700_0.log 100% 160KB 160.0KB/s 00:00 [dmdba@dmrac1 data]$ scp -r rac1_arch dmdba@10.18.10.187:/dm_home/dmdbms/backup/ dmdba@10.18.10.187's password: ARCH_REMOTE1_20200608152641970_1.log 100% 158KB 158.0KB/s 00:00 ARCH_REMOTE1_20200609170737070_1.log 100% 159KB 159.0KB/s 00:00 ARCH_REMOTE1_20200609150857056_1.log 100% 158KB 158.0KB/s 00:00 ARCH_REMOTE1_20200616164822181_1.log 100% 256MB 85.3MB/s 00:03 ARCH_REMOTE1_20200615172121038_1.log 100% 158KB 158.0KB/s 00:00 ARCH_REMOTE1_20200608151838201_1.log 100% 5632 5.5KB/s 00:00 ARCH_REMOTE1_20200528202146001_1.log 100% 158KB 158.0KB/s 00:00 ARCH_REMOTE1_20200615164957743_1.log 100% 158KB 158.0KB/s 00:00 ARCH_REMOTE1_20200605154211189_1.log 100% 159KB 159.0KB/s 00:00 ARCH_REMOTE1_20200615165652504_1.log 100% 158KB 158.0KB/s 00:00 ARCH_REMOTE1_20200615171046600_1.log 100% 158KB 158.0KB/s 00:00 [dmdba@dmrac1 data]$
6) 還原資料庫。步驟4為誤操作，因此我們需要將資料庫恢復到步驟3的狀態。首先需要還原資料庫:
RMAN> restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/dm_rac_full_bak_for_time_lsn'; restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/dm_rac_full_bak_for_time_lsn'; file dm.key not found, use default license! checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[4]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[3]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[2]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[1]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[0]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running, write dmrman info. RESTORE DATABASE CHECK...... RESTORE DATABASE , dbf collect...... RESTORE DATABASE , dbf refresh ...... RESTORE BACKUPSET [/dm_home/dmdbms/backup/dm_rac_full_bak_for_time_lsn] START...... total 5 packages processed... RESTORE DATABASE , UPDATE ctl file...... RESTORE DATABASE , REBUILD key file...... RESTORE DATABASE , CHECK db info...... RESTORE DATABASE , UPDATE db info...... total 6 packages processed! CMD END.CODE:[0] restore successfully. time used: 16581.139(ms)
7) 恢復資料庫到指定時間點/LSN。還原後資料庫的資料與備份時一致，如果要恢復資料庫至步驟3的狀態可以指定UNTIL TIME或UNTIL LSN參
數重做部分歸檔。
使用RECOVER DATABASE...UNTIL TIME命令恢復到指定的時間：
RMAN> recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' with archivedir '/dm_home/dmdbms/backup/rac0_arch' , '/dm_home/dmdbms/backup/rac1_arch' until time '2020-06-19 15:23:17.809757'; recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' with archivedir '/dm_home/dmdbms/backup/rac0_arch' , '/dm_home/dmdbms/backup/rac1_arch' until time '2020-06-19 15:23:17.809757'; checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[4]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[3]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[2]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[1]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[0]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running, write dmrman info. EP[0] max_lsn: 80023 EP 0's ckpt_lsn = 80054 min_ckpt_lsn = 80054 The RAC redo archive log 1 ptx The RAC redo archive log 2 ptx The RAC redo archive log 3 ptx The RAC redo archive log 4 ptx The RAC redo archive log 5 ptx The RAC redo archive log 6 ptx The RAC redo archive log 7 ptx The RAC redo archive log 8 ptx The RAC redo archive log 9 ptx The RAC redo archive log 10 ptx The RAC redo archive log 11 ptx The RAC redo archive log 12 ptx The RAC redo archive log 13 ptx The RAC redo archive log 14 ptx The RAC redo archive log 15 ptx The RAC redo archive log 16 ptx The RAC redo archive log 17 ptx The RAC redo archive log 18 ptx The RAC redo archive log 19 ptx The RAC redo archive log 20 ptx The RAC redo archive log 21 ptx The RAC redo archive log 22 ptx The RAC redo archive log 23 ptx The RAC redo archive log 24 ptx The RAC redo archive log 25 ptx The RAC redo archive log 26 ptx The RAC redo archive log 27 ptx The RAC redo archive log 28 ptx The RAC redo archive log 29 ptx The RAC redo archive log 30 ptx The RAC redo archive log 31 ptx The RAC redo archive log 32 ptx The RAC redo archive log 33 ptx The RAC redo archive log 34 ptx The RAC redo archive log 35 ptx The RAC redo archive log 36 ptx The RAC redo archive log 37 ptx The RAC redo archive log 38 ptx The RAC redo archive log 39 ptx The RAC redo archive log 40 ptx The RAC redo archive log 41 ptx The RAC redo archive log 42 ptx The RAC redo archive log 43 ptx The RAC redo archive log 44 ptx The RAC redo archive log 45 ptx The RAC redo archive log 46 ptx The RAC redo archive log 47 ptx The RAC redo archive log 48 ptx The RAC redo archive log 49 ptx The RAC redo archive log 50 ptx The RAC redo archive log 51 ptx The RAC redo archive log 52 ptx The RAC redo archive log 53 ptx The RAC redo archive log 54 ptx The RAC redo archive log 55 ptx The RAC redo archive log 56 ptx The RAC redo archive log 57 ptx The RAC redo archive log 58 ptx The RAC redo archive log 59 ptx The RAC redo archive log 60 ptx The RAC redo archive log 61 ptx The RAC redo archive log 62 ptx The RAC redo archive log 63 ptx The RAC redo archive log 64 ptx The RAC redo archive log 65 ptx The RAC redo archive log 66 ptx The RAC redo archive log 67 ptx The RAC redo archive log 68 ptx The RAC redo archive log 69 ptx The RAC redo archive log 70 ptx The RAC redo archive log 71 ptx The RAC redo archive log 72 ptx The RAC redo archive log 73 ptx The RAC redo archive log 74 ptx The RAC redo archive log 75 ptx The RAC redo archive log 76 ptx The RAC redo archive log 77 ptx The RAC redo archive log 78 ptx The RAC redo archive log 79 ptx The RAC redo archive log 80 ptx The RAC redo archive log 81 ptx The RAC redo archive log 82 ptx The RAC redo archive log 83 ptx The RAC redo archive log 84 ptx The RAC redo archive log 85 ptx The RAC redo archive log 86 ptx The RAC redo archive log 87 ptx The RAC redo archive log 88 ptx The RAC redo archive log 89 ptx The RAC redo archive log 90 ptx The RAC redo archive log 91 ptx The RAC redo archive log 92 ptx The RAC redo archive log 93 ptx The RAC redo archive log 94 ptx The RAC redo archive log 95 ptx The RAC redo archive log 96 ptx The RAC redo archive log 97 ptx The RAC redo archive log 98 ptx The RAC redo archive log 99 ptx The RAC redo archive log 100 ptx The RAC redo archive log 101 ptx The RAC redo archive log 102 ptx The RAC redo archive log 103 ptx The RAC redo archive log 104 ptx The RAC redo archive log 105 ptx The RAC redo archive log 106 ptx The RAC redo archive log 107 ptx The RAC redo archive log 108 ptx The RAC redo archive log 109 ptx The RAC recover total redo 109 ptx EP(0) slot ctl page(1, 0, 16) trxid = 10105 EP(1) slot ctl page(1, 0, 17) trxid = 8457 EP[0] Recover LSN from 80054 to 80130. EP[1] Recover LSN from 80024 to 80023. Recover from archive log finished, time used:0.147s. recover successfully! time used: 7027.267(ms)
或者使用RECOVER DATABASE...UNTIL TIME命令恢復到指定的LSN：
RMAN> recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' with archivedir '/dm_home/dmdbms/backup/rac0_arch' , '/dm_home/dmdbms/backup/rac1_arch' until lsn 80126; recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' with archivedir '/dm_home/dmdbms/backup/rac0_arch' , '/dm_home/dmdbms/backup/rac1_arch' until lsn 80126; checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[4]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[3]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[2]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[1]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[0]. checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running, write dmrman info. EP[0] max_lsn: 80023 EP 0's ckpt_lsn = 80054 min_ckpt_lsn = 80054 The RAC redo archive log 1 ptx The RAC redo archive log 2 ptx The RAC redo archive log 3 ptx The RAC redo archive log 4 ptx The RAC redo archive log 5 ptx The RAC redo archive log 6 ptx The RAC redo archive log 7 ptx The RAC redo archive log 8 ptx The RAC redo archive log 9 ptx The RAC redo archive log 10 ptx The RAC redo archive log 11 ptx The RAC redo archive log 12 ptx The RAC redo archive log 13 ptx The RAC redo archive log 14 ptx The RAC redo archive log 15 ptx The RAC redo archive log 16 ptx The RAC redo archive log 17 ptx The RAC redo archive log 18 ptx The RAC redo archive log 19 ptx The RAC redo archive log 20 ptx The RAC redo archive log 21 ptx The RAC redo archive log 22 ptx The RAC redo archive log 23 ptx The RAC redo archive log 24 ptx The RAC redo archive log 25 ptx The RAC redo archive log 26 ptx The RAC redo archive log 27 ptx The RAC redo archive log 28 ptx The RAC redo archive log 29 ptx The RAC redo archive log 30 ptx The RAC redo archive log 31 ptx The RAC redo archive log 32 ptx The RAC redo archive log 33 ptx The RAC redo archive log 34 ptx The RAC redo archive log 35 ptx The RAC redo archive log 36 ptx The RAC redo archive log 37 ptx The RAC redo archive log 38 ptx The RAC redo archive log 39 ptx The RAC redo archive log 40 ptx The RAC redo archive log 41 ptx The RAC redo archive log 42 ptx The RAC redo archive log 43 ptx The RAC redo archive log 44 ptx The RAC redo archive log 45 ptx The RAC redo archive log 46 ptx The RAC redo archive log 47 ptx The RAC redo archive log 48 ptx The RAC redo archive log 49 ptx The RAC redo archive log 50 ptx The RAC redo archive log 51 ptx The RAC redo archive log 52 ptx The RAC redo archive log 53 ptx The RAC redo archive log 54 ptx The RAC redo archive log 55 ptx The RAC redo archive log 56 ptx The RAC redo archive log 57 ptx The RAC redo archive log 58 ptx The RAC redo archive log 59 ptx The RAC redo archive log 60 ptx The RAC redo archive log 61 ptx The RAC redo archive log 62 ptx The RAC redo archive log 63 ptx The RAC redo archive log 64 ptx The RAC redo archive log 65 ptx The RAC redo archive log 66 ptx The RAC redo archive log 67 ptx The RAC redo archive log 68 ptx The RAC redo archive log 69 ptx The RAC redo archive log 70 ptx The RAC redo archive log 71 ptx The RAC redo archive log 72 ptx The RAC redo archive log 73 ptx The RAC redo archive log 74 ptx The RAC redo archive log 75 ptx The RAC redo archive log 76 ptx The RAC redo archive log 77 ptx The RAC redo archive log 78 ptx The RAC redo archive log 79 ptx The RAC recover total redo 79 ptx EP(0) slot ctl page(1, 0, 16) trxid = 10104 EP(1) slot ctl page(1, 0, 17) trxid = 8457 EP[0] Recover LSN from 80054 to 80126. EP[1] Recover LSN from 80024 to 80023. Recover from archive log finished, time used:0.145s. recover successfully! time used: 7026.956(ms)
8)在目標庫驗證表tab_for_recover_01中的資料
[root@dmks init.d]# service DmServicedmrc start Starting DmServicedmrc: [ OK ] [dmdba@dmks ~]$ disql SYSDBA/SYSDBA@localhost:5336 Server[localhost:5336]:mode is normal, state is open login used time: 10.592(ms) disql V7.1.6.46-Build(2018.02.08-89107)ENT Connected to: DM 7.1.6.46 SQL> select * from tab_for_recover_01; LINEID C1 ---------- ----------- 1 1 used time: 2.879(ms). Execute id is 3.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2741277/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- DM7使用DMRMAN執行RAC資料庫恢復資料庫
- DM7使用dmrestore執行指定時間點或LSN還原REST
- DM7使用DMRMAN執行資料庫還原和恢復資料庫
- DM7使用DMRMAN對資料庫執行指定對映檔案還原資料庫
- 備份與恢復：Polardb資料庫資料基於時間點恢復資料庫
- DM7 RAC資料庫恢復成單機資料庫資料庫
- DM7使用離線工具DMRMAN執行資料庫備份資料庫
- 使用NineData構建任意時間點（PITR）資料恢復能力資料恢復
- ORACLE資料庫基於時間點的不完全恢復Oracle資料庫
- DM7使用dmrestore工具利用不同資料庫的歸檔恢復資料庫REST資料庫
- 通過事務日誌恢復SqlServer資料庫到一個特定的時間點SQLServer資料庫
- DM7使用DMRMAN執行歸檔修復
- DM7使用DMRAMN對多次故障恢復後使用不同資料庫的歸檔進行恢復資料庫
- PostgreSQL 時間點恢復SQL
- 大事務導致資料庫恢復時間長資料庫
- DM7使用DMRMAN執行表空間還原
- 將 SQL Server 資料庫還原到某個時點（完整恢復模式）SQLServer資料庫模式
- 【資料庫資料恢復】SQL Server資料庫磁碟空間不足的資料恢復案例資料庫資料恢復SQLServer
- 基於percona xtrabackup之innobackupex實現基於時間點資料庫恢復資料庫
- 【DATAGUARD】Oracle 通過Dataguard指定恢復時間用於找回丟失資料Oracle
- 【資料庫資料恢復】SAP資料庫資料恢復案例資料庫資料恢復
- 寶塔資料庫恢復 mysql資料庫丟失恢復 mysql資料庫刪除庫恢復 寶塔mysql資料庫恢復資料庫MySql
- (Les16 執行資料庫恢復)-表空間恢復資料庫
- 【資料庫資料恢復】Sql Server資料庫資料恢復案例資料庫資料恢復SQLServer
- 【資料庫資料恢復】透過恢復NDF檔案修復資料庫的資料恢復過程資料庫資料恢復
- 資料庫修復資料恢復資料庫資料恢復
- PostgreSQL啟動恢復期間，恢復到的時間線的確定SQL
- 【資料庫資料恢復】磁碟空間不足導致sql server錯誤的資料恢復資料庫資料恢復SQLServer
- 【資料庫資料恢復】windows server下SqlServer資料庫的資料恢復資料庫資料恢復WindowsServerSQL
- 【資料庫資料恢復】如何恢復Oracle資料庫truncate表的資料資料庫資料恢復Oracle
- mongodb使用備份後的oplog做時間點恢復MongoDB
- 7.5 使用binary log 做基於時間點的恢復
- 記一次刪庫到資料恢復資料恢復
- 【資料庫資料恢復】Oracle資料庫誤truncate table的資料恢復案例資料庫資料恢復Oracle
- 【資料庫資料恢復】誤truncate table的Oracle資料庫資料恢復方案資料庫資料恢復Oracle
- 【資料庫資料恢復】oracle資料庫誤truncate table怎麼恢復資料？資料庫資料恢復Oracle
- 【資料庫資料恢復】linux系統下MYSQL資料庫資料恢復案例資料庫資料恢復LinuxMySql
- 伺服器資料恢復—透過拼接資料庫碎片恢復SqlServer資料庫資料的資料恢復案例伺服器資料恢復資料庫SQLServer