db2 前滚数据库_list utilities监视数据库前滚操作

您可以使用 db2pd 或 LIST UTILITIES 命令来监视数据库前滚操作的进度。

过程

发出 LIST UTILITIES 命令并指定 SHOW DETAIL 参数

db2 LIST UTILITIES SHOW DETAIL

发出 db2pd 命令并指定 -recovery 参数：

db2pd -d 数据库名 -reco

结果

对于前滚恢复，进度监视分为两个阶段：FORWARD 和 BACKWARD。FORWARD 阶段期间中，读取日志文件并将日志记录应用于数据库。对于前滚恢复，当此阶段开始时，将工作总量估计值指定为 UNKNOWN。按字节计的已处理工作量将随进程的继续而更新。

在 BACKWARD 阶段中，回滚 FORWARD 阶段期间中应用任何未落实的更改。提供了要处理的日志数据量的估计值(按字节计)。当进程继续运行时，会更新已处理的工作量(按字节计)。

示例

以下示例是使用 db2pd 命令监视前滚操作性能所获得的输出：

Recovery:

Recovery Status 0x00000401

Current Log S0000005.LOG

Current LSN 0000001F07BC

Current LSO 000002551BEA

Job Type ROLLFORWARD RECOVERY

Job ID 7

Job Start Time (1107380474) Wed Feb 2 16:41:14 2005

Job Description Database Rollforward Recovery

Invoker Type User

Total Phases 2

Current Phase 1

Progress:

Address PhaseNum Description StartTime CompletedWork TotalWork

0x0000000200667160 1 Forward Wed Feb 2 16:41:14 2005 2268098 bytes Unknown

0x0000000200667258 2 Backward NotStarted 0 bytes Unknown

以下示例是使用附带 SHOW DETAIL 选项的 LIST UTILITIES 命令监视数据库前滚操作性能所获得的输出：

ID = 7

Type = ROLLFORWARD RECOVERY

Database Name = TESTDB

Member Number = 0

Description = Database Rollforward Recovery

Start Time = 01/11/2012 16:56:53.770404

State = Executing

Invocation Type = User

Progress Monitoring:

Estimated Percentage Complete = 50

Phase Number = 1

Description = Forward

Total Work = 928236 bytes

Completed Work = 928236 bytes

Start Time = 01/11/2012 16:56:53.770492

Phase Number [Current] = 2

Description = Backward

Total Work = 928236 bytes

Completed Work = 0 bytes

Start Time = 01/11/2012 16:56:56.886036

以下示例是使用附带 SHOW DETAIL 选项的 LIST UTILITIES 命令监视表空间前滚操作性能所获得的输出：

ID = 17

Type = ROLLFORWARD RECOVERY

Database Name = TESTDB

Member Number = 0

Description = Offline Tablespace Rollforward Recovery: 3

Start Time = 01/11/2012 17:04:27.269171

State = Executing

Invocation Type = User

Progress Monitoring:

Estimated Percentage Complete = 63

Phase Number = 1

Description = Forward

Total Work = 142

Completed Work = 90

Start Time = 01/11/2012 17:04:27.269283

Phase Number [Current] = 2

Description = Backward

Total Work = 0

Completed Work = 0

Start Time = Not Started

关于crash recovery

如果DB2数据库遭受断电或者异常关闭，数据库没有干净的关闭，那么数据库在启动的时候将会

进行crash recovery. 但是如果数据库参数AUTORESTART设置为OFF的话，在启动数据库后DB2不会

进行CRASH RECOVERY。我们在连接到数据库的时候将会报SQL1015N 错误。

[yansp@db2server ~]$ db2 get db cfg for oracle | grep AUTORESTART

Auto restart enabled                      (AUTORESTART) = OFF

db2 => connect to oracle @

SQL1015N  The database is in an inconsistent state.  SQLSTATE=55025

这个时候需要我们手工进行CRASH RECOVERY。

db2 => restart database oracle @

DB20000I  The RESTART DATABASE command completed successfully.

db2 => connect to oracle @

Database Connection Information

Database server        = DB2/LINUX 9.7.0

SQL authorization ID   = YANSP

Local database alias   = ORACLE

通过日志文件我们看一下DB2 CRASH RECOVERY的过程。

2013-02-03-06.27.22.704092+480 I1529901G434       LEVEL: Warning

PID     : 9899                 TID  : 2947541904  PROC : db2sysc 0

INSTANCE: yansp                NODE : 000         DB   : ORACLE

APPHDL  : 0-15                 APPID: *LOCAL.yansp.130202222719

AUTHID  : YANSP

EDUID   : 35                   EDUNAME: db2agent (ORACLE) 0

FUNCTION: DB2 UDB, base sys utilities, sqledint, probe:30

MESSAGE : Crash Recovery is needed.   

2013-02-03-06.27.31.389376+480 I1530336G497       LEVEL: Warning

PID     : 9899                 TID  : 2947541904  PROC : db2sysc 0

INSTANCE: yansp                NODE : 000         DB   : ORACLE

APPHDL  : 0-15                 APPID: *LOCAL.yansp.130202222719

AUTHID  : YANSP

EDUID   : 35                   EDUNAME: db2agent (ORACLE) 0

FUNCTION: DB2 UDB, recovery manager, sqlpresr, probe:410

MESSAGE : Crash recovery started. LowtranLSN 00000000252E0010 MinbuffLSN 

00000000252E0010

2013-02-03-06.27.31.463994+480 E1530834G451       LEVEL: Warning

PID     : 9899                 TID  : 2947541904  PROC : db2sysc 0

INSTANCE: yansp                NODE : 000         DB   : ORACLE

APPHDL  : 0-15                 APPID: *LOCAL.yansp.130202222719

AUTHID  : YANSP

EDUID   : 35                   EDUNAME: db2agent (ORACLE) 0

FUNCTION: DB2 UDB, recovery manager, sqlpresr, probe:410

MESSAGE : ADM1530E  Crash recovery has been initiated. 

2013-02-03-06.27.31.643060+480 I1531286G492       LEVEL: Warning

PID     : 9899                 TID  : 2947541904  PROC : db2sysc 0

INSTANCE: yansp                NODE : 000         DB   : ORACLE

APPHDL  : 0-15                 APPID: *LOCAL.yansp.130202222719

AUTHID  : YANSP

EDUID   : 35                   EDUNAME: db2agent (ORACLE) 0

FUNCTION: DB2 UDB, recovery manager, sqlprecm, probe:2000

DATA #1 :

Using parallel recovery with 3 agents 8 QSets 24 queues and 16 chunks  

2013-02-03-06.27.31.933175+480 I1531779G351       LEVEL: Warning

PID     : 9899                 TID  : 2945444752  PROC : db2sysc 0

INSTANCE: yansp                NODE : 000

EDUID   : 40                   EDUNAME: db2lfr (ORACLE) 0

FUNCTION: DB2 UDB, data protection services, sqlpgarl, probe:99

MESSAGE : INFO ONLY: Found an old page in the log file               

2013-02-03-06.27.32.700437+480 I1532131G507       LEVEL: Warning

PID     : 9899                 TID  : 2947541904  PROC : db2sysc 0

INSTANCE: yansp                NODE : 000         DB   : ORACLE

APPHDL  : 0-15                 APPID: *LOCAL.yansp.130202222719

AUTHID  : YANSP

EDUID   : 35                   EDUNAME: db2agent (ORACLE) 0

FUNCTION: DB2 UDB, recovery manager, sqlprecm, probe:4000

MESSAGE : DIA2051W Forward phase of crash recovery has completed.  Next LSN is  

"00000000252E7400".

2013-02-03-06.27.33.293191+480 E1532639G460       LEVEL: Warning

PID     : 9899                 TID  : 2947541904  PROC : db2sysc 0

INSTANCE: yansp                NODE : 000         DB   : ORACLE

APPHDL  : 0-15                 APPID: *LOCAL.yansp.130202222719

AUTHID  : YANSP

EDUID   : 35                   EDUNAME: db2agent (ORACLE) 0

FUNCTION: DB2 UDB, recovery manager, sqlpresr, probe:3170

MESSAGE : ADM1531E  Crash recovery has completed successfully.  

2013-02-03-06.27.33.294698+480 I1533100G462       LEVEL: Warning

PID     : 9899                 TID  : 2947541904  PROC : db2sysc 0

INSTANCE: yansp                NODE : 000         DB   : ORACLE

APPHDL  : 0-15                 APPID: *LOCAL.yansp.130202222719

AUTHID  : YANSP

EDUID   : 35                   EDUNAME: db2agent (ORACLE) 0

FUNCTION: DB2 UDB, recovery manager, sqlpresr, probe:3170

MESSAGE : Crash recovery completed. Next LSN is 00000000252E7400

2013-02-03-06.27.34.627484+480 E1533563G464       LEVEL: Event

PID     : 9899                 TID  : 2949639056  PROC : db2sysc 0

INSTANCE: yansp                NODE : 000         DB   : ORACLE

APPHDL  : 0-16                 APPID: *LOCAL.DB2.130202222734

AUTHID  : YANSP

EDUID   : 34                   EDUNAME: db2stmm (ORACLE) 0

FUNCTION: DB2 UDB, Self tuning memory manager, stmmLog, probe:1008

DATA #1 :

Starting STMM log from file number 0

整个恢复过程都是有主控进程PID=9899 DB2SYSC进程来完成的。

[yansp@db2server ~]$ ps -ef | grep 9899

yansp     9899  9897  0 06:16 pts/0    00:00:06 db2sysc 0

root      9900  9899  0 06:16 pts/0    00:00:00 db2ckpwd 0

root      9901  9899  0 06:16 pts/0    00:00:00 db2ckpwd 0

root      9902  9899  0 06:16 pts/0    00:00:00 db2ckpwd 0

yansp    12571 11322  1 06:33 pts/2    00:00:00 grep 9899