创建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恢复到指定时间点
-
like052629
2015-06-19 13:57:07 -
数据库开发技术
- 原创