db2恢復到指定時間點
建立testa表
db2 => create table testa like m_mer
DB20000I The SQL command completed successfully.
db2 => insert into testa select * from m_mer
DB20000I The SQL command completed successfully.
db2 => !date
手動切換日誌
db2 archive log for db cms
2015年 06月 19日 星期五 10:40:17 CST
進行線上備份
db2 => drop table M_MER_20150106
drop table M_MER_20150109
drop table M_MER_20150202
drop table M_MER_20150508
DB20000I The SQL command completed successfully.
db2 => DB20000I The SQL command completed successfully.
db2 => DB20000I The SQL command completed successfully.
db2 => DB20000I The SQL command completed successfully.
db2 => !date
2015年 06月 19日 星期五 10:46:13 CST
進行歸檔
db2 archive log for db cms
db2 => delete from testa
DB20000I The SQL command completed successfully.
db2 => select count(1) from testa
1
-----------
0
1 record(s) selected.
db2 => quit
[db2inst2@baktest130 backup_dir]$ db2 restore db cms taken at 20150619104158
SQL1035N The database is currently in use. SQLSTATE=57019
[db2inst2@baktest130 backup_dir]$ db2 connect reset
SQL1024N A database connection does not exist. SQLSTATE=08003
[db2inst2@baktest130 backup_dir]$ db2 restore db cms taken at 20150619104158
SQL1035N The database is currently in use. SQLSTATE=57019
[db2inst2@baktest130 backup_dir]$ db2 force application all
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
[db2inst2@baktest130 backup_dir]$ db2 restore db cms taken at 20150619104158
SQL2539W Warning! Restoring to an existing database that is the same as the
backup image database. The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst2@baktest130 backup_dir]$ db2 connect to cms
SQL1117N A connection to or activation of database "CMS" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
[db2inst2@baktest130 backup_dir]$ db2 "rollforward db cms to 2015-06-19-10-40-17.000000 using local time"
SQL1275N The stop time passed to the rollforward utility must be greater than
or equal to timestamp "2015-06-19-10.42.14.000000 Local", because database
"CMS" on node(s) "0" contains information later than the specified time.
[db2inst2@baktest130 backup_dir]$ db2 "rollforward db cms to 2015-06-19-10.42.14.000000 using local time"
Rollforward Status
Input database alias = cms
Number of nodes have returned status = 1
Node number = 0
Rollforward status = DB working
Next log file to be read = S0000559.LOG
Log files processed = S0000557.LOG - S0000558.LOG
Last committed transaction = 2015-06-19-10.42.14.000000 Local
DB20000I The ROLLFORWARD command completed successfully.
[db2inst2@baktest130 backup_dir]$ db2 connect to cms
SQL1117N A connection to or activation of database "CMS" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
[db2inst2@baktest130 backup_dir]$ db2 "rollforward db cms to 2015-06-19-10.42.14.000000 using local time and stop"
Rollforward Status
Input database alias = cms
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000557.LOG - S0000559.LOG
Last committed transaction = 2015-06-19-10.42.14.000000 Local
DB20000I The ROLLFORWARD command completed successfully.
[db2inst2@baktest130 backup_dir]$ db2 connect to cms
Database Connection Information
Database server = DB2/LINUXX8664 9.7.6
SQL authorization ID = DB2INST2
Local database alias = CMS
[db2inst2@baktest130 backup_dir]$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.7.6
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => select table_name from sysibm.tables where table_schema='DB2INST2' and table_name like 'M_MER%' order by table_name
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
M_MER
M_MER_20150106
M_MER_20150109
M_MER_20150202
M_MER_20150508
M_MER_20150512
M_MER_20150609
18 record(s) selected.
db2 => select count(1) from testa;
SQL0104N An unexpected token ";" was found following "count(1) from testa".
Expected tokens may include: "END-OF-STATEMENT". SQLSTATE=42601
db2 => select count(1) from testa
1
-----------
1525
1 record(s) selected.
db2 =>
db2 => create table testb like m_mer
DB20000I The SQL command completed successfully.
db2 => insert into testb select * from m_mer
DB20000I The SQL command completed successfully.
db2 => select count(1) from testb
1
-----------
1525
1 record(s) selected.
db2 => !date
2015年 06月 19日 星期五 11:46:10 CST
db2 => !date
2015年 06月 19日 星期五 11:46:24 CST
db2 => !date
2015年 06月 19日 星期五 11:46:48 CST
db2 => !date
2015年 06月 19日 星期五 11:47:29 CST
db2 => drop table M_MER_20150106
drop table M_MER_20150109
drop table M_MER_20150202
drop table M_MER_20150508
DB20000I The SQL command completed successfully.
db2 => DB20000I The SQL command completed successfully.
db2 => DB20000I The SQL command completed successfully.
db2 => DB20000I The SQL command completed successfully.
db2 => delete from testa
DB20000I The SQL command completed successfully.
db2 => delete from testb
DB20000I The SQL command completed successfully.
db2 => quit
DB20000I The QUIT command completed successfully.
[db2inst2@baktest130 shell]$ cd ../backup_dir/
[db2inst2@baktest130 backup_dir]$
[db2inst2@baktest130 backup_dir]$ db2 "rollforward db cms to 2015-06-19-11.46.48.000000 using local time and stop "
Rollforward Status
Input database alias = cms
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000560.LOG - S0000561.LOG
Last committed transaction = 2015-06-19-11.46.02.000000 Local
DB20000I The ROLLFORWARD command completed successfully.
--檢視恢復進度
[db2inst2@baktest130 backup_dir]$ db2 list utilities show detail
ID = 32
Type = ROLLFORWARD RECOVERY
Database Name = CMS
Partition Number = 0
Description = Database Rollforward Recovery
Start Time = 2015-06-19 11:52:38.836935
State = Executing
Invocation Type = User
Progress Monitoring:
Estimated Percentage Complete = 100
Phase Number = 1
Description = Forward
Total Work = 205895070 bytes
Completed Work = 205895070 bytes
Start Time = 2015-06-19 11:52:38.836942
Phase Number [Current] = 2
Description = Backward
Total Work = 687433 bytes
Completed Work = 0 bytes
Start Time = 2015-06-19 11:52:39.976570
[db2inst2@baktest130 backup_dir]$ db2 list utilities show detail
ID = 32
Type = ROLLFORWARD RECOVERY
Database Name = CMS
Partition Number = 0
Description = Database Rollforward Recovery
Start Time = 2015-06-19 11:52:38.836935
State = Executing
Invocation Type = User
Progress Monitoring:
Estimated Percentage Complete = 100
Phase Number = 1
Description = Forward
Total Work = 205895070 bytes
Completed Work = 205895070 bytes
Start Time = 2015-06-19 11:52:38.836942
Phase Number [Current] = 2
Description = Backward
Total Work = 687433 bytes
Completed Work = 0 bytes
Start Time = 2015-06-19 11:52:39.976570
參照：http://blog.itpub.net/28258625/viewspace-1350214/
db2 => create table testa like m_mer
DB20000I The SQL command completed successfully.
db2 => insert into testa select * from m_mer
DB20000I The SQL command completed successfully.
db2 => !date
手動切換日誌
db2 archive log for db cms
2015年 06月 19日 星期五 10:40:17 CST
進行線上備份
db2 => drop table M_MER_20150106
drop table M_MER_20150109
drop table M_MER_20150202
drop table M_MER_20150508
DB20000I The SQL command completed successfully.
db2 => DB20000I The SQL command completed successfully.
db2 => DB20000I The SQL command completed successfully.
db2 => DB20000I The SQL command completed successfully.
db2 => !date
2015年 06月 19日 星期五 10:46:13 CST
進行歸檔
db2 archive log for db cms
db2 => delete from testa
DB20000I The SQL command completed successfully.
db2 => select count(1) from testa
1
-----------
0
1 record(s) selected.
db2 => quit
[db2inst2@baktest130 backup_dir]$ db2 restore db cms taken at 20150619104158
SQL1035N The database is currently in use. SQLSTATE=57019
[db2inst2@baktest130 backup_dir]$ db2 connect reset
SQL1024N A database connection does not exist. SQLSTATE=08003
[db2inst2@baktest130 backup_dir]$ db2 restore db cms taken at 20150619104158
SQL1035N The database is currently in use. SQLSTATE=57019
[db2inst2@baktest130 backup_dir]$ db2 force application all
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
[db2inst2@baktest130 backup_dir]$ db2 restore db cms taken at 20150619104158
SQL2539W Warning! Restoring to an existing database that is the same as the
backup image database. The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst2@baktest130 backup_dir]$ db2 connect to cms
SQL1117N A connection to or activation of database "CMS" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
[db2inst2@baktest130 backup_dir]$ db2 "rollforward db cms to 2015-06-19-10-40-17.000000 using local time"
SQL1275N The stop time passed to the rollforward utility must be greater than
or equal to timestamp "2015-06-19-10.42.14.000000 Local", because database
"CMS" on node(s) "0" contains information later than the specified time.
[db2inst2@baktest130 backup_dir]$ db2 "rollforward db cms to 2015-06-19-10.42.14.000000 using local time"
Rollforward Status
Input database alias = cms
Number of nodes have returned status = 1
Node number = 0
Rollforward status = DB working
Next log file to be read = S0000559.LOG
Log files processed = S0000557.LOG - S0000558.LOG
Last committed transaction = 2015-06-19-10.42.14.000000 Local
DB20000I The ROLLFORWARD command completed successfully.
[db2inst2@baktest130 backup_dir]$ db2 connect to cms
SQL1117N A connection to or activation of database "CMS" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
[db2inst2@baktest130 backup_dir]$ db2 "rollforward db cms to 2015-06-19-10.42.14.000000 using local time and stop"
Rollforward Status
Input database alias = cms
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000557.LOG - S0000559.LOG
Last committed transaction = 2015-06-19-10.42.14.000000 Local
DB20000I The ROLLFORWARD command completed successfully.
[db2inst2@baktest130 backup_dir]$ db2 connect to cms
Database Connection Information
Database server = DB2/LINUXX8664 9.7.6
SQL authorization ID = DB2INST2
Local database alias = CMS
[db2inst2@baktest130 backup_dir]$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.7.6
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => select table_name from sysibm.tables where table_schema='DB2INST2' and table_name like 'M_MER%' order by table_name
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
M_MER
M_MER_20150106
M_MER_20150109
M_MER_20150202
M_MER_20150508
M_MER_20150512
M_MER_20150609
18 record(s) selected.
db2 => select count(1) from testa;
SQL0104N An unexpected token ";" was found following "count(1) from testa".
Expected tokens may include: "END-OF-STATEMENT". SQLSTATE=42601
db2 => select count(1) from testa
1
-----------
1525
1 record(s) selected.
db2 =>
db2 => create table testb like m_mer
DB20000I The SQL command completed successfully.
db2 => insert into testb select * from m_mer
DB20000I The SQL command completed successfully.
db2 => select count(1) from testb
1
-----------
1525
1 record(s) selected.
db2 => !date
2015年 06月 19日 星期五 11:46:10 CST
db2 => !date
2015年 06月 19日 星期五 11:46:24 CST
db2 => !date
2015年 06月 19日 星期五 11:46:48 CST
db2 => !date
2015年 06月 19日 星期五 11:47:29 CST
db2 => drop table M_MER_20150106
drop table M_MER_20150109
drop table M_MER_20150202
drop table M_MER_20150508
DB20000I The SQL command completed successfully.
db2 => DB20000I The SQL command completed successfully.
db2 => DB20000I The SQL command completed successfully.
db2 => DB20000I The SQL command completed successfully.
db2 => delete from testa
DB20000I The SQL command completed successfully.
db2 => delete from testb
DB20000I The SQL command completed successfully.
db2 => quit
DB20000I The QUIT command completed successfully.
[db2inst2@baktest130 shell]$ cd ../backup_dir/
[db2inst2@baktest130 backup_dir]$
[db2inst2@baktest130 backup_dir]$ db2 "rollforward db cms to 2015-06-19-11.46.48.000000 using local time and stop "
Rollforward Status
Input database alias = cms
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000560.LOG - S0000561.LOG
Last committed transaction = 2015-06-19-11.46.02.000000 Local
DB20000I The ROLLFORWARD command completed successfully.
--檢視恢復進度
[db2inst2@baktest130 backup_dir]$ db2 list utilities show detail
ID = 32
Type = ROLLFORWARD RECOVERY
Database Name = CMS
Partition Number = 0
Description = Database Rollforward Recovery
Start Time = 2015-06-19 11:52:38.836935
State = Executing
Invocation Type = User
Progress Monitoring:
Estimated Percentage Complete = 100
Phase Number = 1
Description = Forward
Total Work = 205895070 bytes
Completed Work = 205895070 bytes
Start Time = 2015-06-19 11:52:38.836942
Phase Number [Current] = 2
Description = Backward
Total Work = 687433 bytes
Completed Work = 0 bytes
Start Time = 2015-06-19 11:52:39.976570
[db2inst2@baktest130 backup_dir]$ db2 list utilities show detail
ID = 32
Type = ROLLFORWARD RECOVERY
Database Name = CMS
Partition Number = 0
Description = Database Rollforward Recovery
Start Time = 2015-06-19 11:52:38.836935
State = Executing
Invocation Type = User
Progress Monitoring:
Estimated Percentage Complete = 100
Phase Number = 1
Description = Forward
Total Work = 205895070 bytes
Completed Work = 205895070 bytes
Start Time = 2015-06-19 11:52:38.836942
Phase Number [Current] = 2
Description = Backward
Total Work = 687433 bytes
Completed Work = 0 bytes
Start Time = 2015-06-19 11:52:39.976570
參照：http://blog.itpub.net/28258625/viewspace-1350214/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28258625/viewspace-1705211/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- DM7使用DMRMAN恢復資料庫到指定時間點/LSN資料庫
- PostgreSQL 時間點恢復SQL
- PostgreSQL啟動恢復期間，恢復到的時間線的確定SQL
- mongodb 基於oplog的時間點恢復MongoDB
- DM8 基於時間點的恢復
- 備份與恢復：Polardb資料庫資料基於時間點恢復資料庫
- DB2備份與恢復DB2
- 【DATAGUARD】Oracle 通過Dataguard指定恢復時間用於找回丟失資料Oracle
- mongodb使用備份後的oplog做時間點恢復MongoDB
- 7.5 使用binary log 做基於時間點的恢復
- 通過事務日誌恢復SqlServer資料庫到一個特定的時間點SQLServer資料庫
- db2備份恢復（backup restore）DB2REST
- 使用NineData構建任意時間點（PITR）資料恢復能力資料恢復
- win10恢復到上一個時間節點的方法Win10
- java休眠到指定時間怎麼寫？Java
- mongodb異機做時間點恢復(基於時間範圍查詢匯出oplog)MongoDB
- db2 命令列備份和恢復DB2命令列
- ORACLE資料庫基於時間點的不完全恢復Oracle資料庫
- PyTorch如何恢復指定權重PyTorch
- PostgreSQL12中實現增量備份與任意時間點恢復SQL
- MySQL binlog基於時間點恢復資料失敗是什麼鬼？MySql
- 2.MongoDB 4.2副本集環境基於時間點的恢復MongoDB
- 《PostgreSQL 指南：內幕探索》之基礎備份與時間點恢復（上）SQL
- 將 SQL Server 資料庫還原到某個時點（完整恢復模式）SQLServer資料庫模式
- 利用innobackupex備份集恢復指定庫
- postgreSQL 恢復至故障點 精準恢復SQL
- 基於percona xtrabackup之innobackupex實現基於時間點資料庫恢復資料庫
- win10恢復到上一個時間節點的檔案還在嗎Win10
- 大事務導致資料庫恢復時間長資料庫
- Prometheus 告警恢復時，怎麼獲取恢復時的值？Prometheus
- RAC恢復到單機
- 1.MongoDB 2.7主從複製(master –> slave)環境基於時間點的恢復MongoDBAST
- 【轉載】TortoiseSVN怎麼恢復到以前版本-恢復到以前版本的方法
- 教你一招，告警恢復時如何拿到恢復時的值？
- 易到用車回應遭黑客攻擊：恢復還需時間 提現將暫停黑客
- Oracle RMAN 表空間恢復Oracle
- 教你一招，告警恢復時如何拿到恢復時的值？ 合
- rac恢復到單例項單例
- (Les16 執行資料庫恢復)-表空間恢復資料庫