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/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28258625/viewspace-1705211/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28258625/viewspace-1705211/