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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- rman恢復到指定時間點
- MySQL使用bin-log異庫恢復到指定時間點MySql
- DM7使用DMRMAN恢復資料庫到指定時間點/LSN資料庫
- 【RMAN】使用RMAN備份將資料庫不完全恢復到指定時間點資料庫
- [RMAN]使用RMAN備份將資料庫不完全恢復到指定時間點資料庫
- 恢復到特定點（時間點、scn、日誌序列號），rman不完全恢復
- 12c恢復表到指定時刻
- db2 前滾最小恢復時間和時間戳問題DB2時間戳
- RAC 資料庫恢復到單例項下並且基於時間點恢復資料庫單例
- PostgreSQL啟動恢復期間，恢復到的時間線的確定SQL
- Oracle基於時間點的恢復Oracle
- oracle實驗記錄 （恢復-表空間基於時間點恢復(rman)）Oracle
- restore、recover到指定時間REST
- [20170213]現有控制檔案恢復到以前時間點
- oracle實驗記錄 （恢復-表空間基於時間點恢復(手動)）Oracle
- Oracle表空間時間點恢復技術TSPITROracle
- mongodb 基於oplog的時間點恢復MongoDB
- DB2恢復DB2
- oracle基於時間點的不完全恢復Oracle
- 使用RMAN對PDB執行按時間點恢復
- 使用RMAN對CDB執行按時間點恢復
- RMAN基於時間點恢復Oracle資料庫Oracle資料庫
- oracle小知識點10--表空間時間點恢復(TSPITR)Oracle
- 【RMAN】表空間基於時間點的RMAN恢復-TSPITR
- 備份與恢復：Polardb資料庫資料基於時間點恢復資料庫
- DB2 使用表空間備份恢復庫DB2
- rman給歸檔指定備份位置；庫先備份盤同時到帶庫；更改控制檔案自備路徑；時間點的恢復
- 【DATAGUARD】Oracle 通過Dataguard指定恢復時間用於找回丟失資料Oracle
- 【TSPITR】RMAN表空間基於時間點的自動恢復
- 通過事務日誌恢復SqlServer資料庫到一個特定的時間點SQLServer資料庫
- java休眠到指定時間怎麼寫？Java
- win10恢復到上一個時間節點的方法Win10
- mongodb使用備份後的oplog做時間點恢復MongoDB
- [DB2]DB2備份和恢復DB2
- [DB2]db2重定向恢復DB2
- 執行RMAN表空間及時點恢復——使用者管理備份和恢復手冊
- ORACLE資料庫基於時間點的不完全恢復Oracle資料庫
- 使用NineData構建任意時間點（PITR）資料恢復能力資料恢復