日志模式
[db2inst1@xifenfei ~]$ db2 get db cfg for xff |grep -i log Log retain for recovery status = NO User exit for logging status = YES --(1) Catalog cache size (4KB) (CATALOGCACHE_SZ) = 260 Log buffer size (4KB) (LOGBUFSZ) = 98 Log file size (4KB) (LOGFILSIZ) = 1024 Number of primary log files (LOGPRIMARY) = 6 Number of secondary log files (LOGSECOND) = 4 Changed path to log files (NEWLOGPATH) = Path to log files = /home/db2inst1/xff/redolog/NODE0000/ Overflow log path (OVERFLOWLOGPATH) = Mirror log path (MIRRORLOGPATH) = First active log file = S0000013.LOG Block log on disk full (BLK_LOG_DSK_FUL) = NO Block non logged operations (BLOCKNONLOGGED) = NO Percent max primary log space by transaction (MAX_LOG) = 0 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520 Log retain for recovery enabled (LOGRETAIN) = OFF User exit for logging enabled (USEREXIT) = OFF HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC First log archive method (LOGARCHMETH1) = DISK:/home/db2inst1/xff/archivelog/ --(2) Options for logarchmeth1 (LOGARCHOPT1) = Second log archive method (LOGARCHMETH2) = OFF Options for logarchmeth2 (LOGARCHOPT2) = Failover log archive path (FAILARCHPATH) = Number of log archive retries on error (NUMARCHRETRY) = 5 Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20 Log pages during index build (LOGINDEXBUILD) = OFF
由(1)和(2)可以判断该数据库处于归档日志模式下
查看当前存在备份
[db2inst1@xifenfei ~]$ db2 list history backup all for xff
List History File for xff
Number of matching file entries = 1
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20120406053431001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 2 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
----------------------------------------------------------------------------
Comment: DB2 BACKUP XFF OFFLINE --离线备份
Start Time: 20120406053431
End Time: 20120406053439
Status: A
----------------------------------------------------------------------------
EID: 1 Location: /tmp
在线全备
[db2inst1@xifenfei ~]$ db2 backup db xff online to /tmp include logs
Backup successful. The timestamp for this backup image is : 20120411165312
[db2inst1@xifenfei ~]$ db2 list history backup all for xff
List History File for xff
Number of matching file entries = 2
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20120406053431001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 2 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
----------------------------------------------------------------------------
Comment: DB2 BACKUP XFF OFFLINE --本次试验的online备份
Start Time: 20120406053431
End Time: 20120406053439
Status: A
----------------------------------------------------------------------------
EID: 1 Location: /tmp
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20120411165312001 N D S0000013.LOG S0000013.LOG
----------------------------------------------------------------------------
Contains 3 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
----------------------------------------------------------------------------
Comment: DB2 BACKUP XFF ONLINE
Start Time: 20120411165312
End Time: 20120411165322
Status: A
----------------------------------------------------------------------------
EID: 19 Location: /tmp
当前数据库当前数据
[db2inst1@xifenfei ~]$ db2 connect to xff
Database Connection Information
Database server = DB2/LINUX 9.5.9
SQL authorization ID = DB2INST1
Local database alias = XFF
[db2inst1@xifenfei ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
T_XFF DB2INST1 T 2012-04-05-09.45.29.148434
T_XIFENFEI DB2INST1 T 2012-04-06-05.50.11.111469
2 record(s) selected.
[db2inst1@xifenfei ~]$ db2 "create table t_xifenfei01 like t_xff"
DB20000I The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xifenfei01
> select * from t_xff"
DB20000I The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xifenfei01
select * from t_xff"
DB20000I The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_xifenfei01"
1
-----------
734
1 record(s) selected.
恢复数据库
[db2inst1@xifenfei ~]$ db2 restore db xff from /tmp taken at 20120411165312
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.
[db2inst1@xifenfei ~]$ db2 "rollforward db xff to end of logs and stop"
Rollforward Status
Input database alias = xff
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000013.LOG - S0000014.LOG
Last committed transaction = 2012-04-11-08.56.20.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[db2inst1@xifenfei ~]$ db connect to xff
-bash: db: command not found
[db2inst1@xifenfei ~]$ db2 connect to xff
Database Connection Information
Database server = DB2/LINUX 9.5.9
SQL authorization ID = DB2INST1
Local database alias = XFF
[db2inst1@xifenfei ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
T_XFF DB2INST1 T 2012-04-05-09.45.29.148434
T_XIFENFEI DB2INST1 T 2012-04-06-05.50.11.111469
T_XIFENFEI01 DB2INST1 T 2012-04-11-16.55.51.853649
3 record(s) selected.
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_xifenfei01"
1
-----------
734
1 record(s) selected.
备份恢复是dba最重要的职责,本篇做为db2学习过程中第一篇关于备份恢复文章,后续将继续学习db2增量备份恢复等知识.
