NOVOTS KMS 词汇表 Glossary    联系我们 Contact Us
查询 Search  
   
按类别浏览 Browse by Category
NOVOTS KMS .: 数据库 .: db2数据库前滚恢复测试

db2数据库前滚恢复测试

1、登陆实例用户gmcw
pedb02:[/home/db2admin]$su - gmcw
gmcw's Password:
[YOU HAVE NEW MAIL]

2,检查一下实例下有几个数据库
pedb02:[/db2data/db1]$db2 list db directory

 System Database Directory

 Number of entries in the directory = 2

Database 1 entry:

 Database alias                       = LGM_MFG
 Database name                        = LGM_MFG
 Local database directory             = /db2data/lgmdb
 Database release level               = c.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

Database 2 entry:

 Database alias                       = LGM_OA
 Database name                        = LGM_OA
 Local database directory             = /db2data/lgmdb
 Database release level               = c.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

pedb02:[/db2data/db1]$
pedb02:[/db2data/db1]$db2 connect to lgm_mfg

   Database Connection Information

 Database server        = DB2/AIX64 9.5.4
 SQL authorization ID   = GMCW
 Local database alias   = LGM_MFG

pedb02:[/db2data/db1]$


3,
进入备份文件的存储目录
pedb02:[/db2data/db1]$ls
LGM_MFG.0.gmcw.NODE0000.CATN0000.20120219040001.001  itadmin
LGM_MFG.0.gmcw.NODE0000.CATN0000.20120226040001.001  lost+found


4,连接数据库,查看目前连接数据库的进程

 


pedb02:[/db2data/db1]$db2 list applications

Auth Id  Application    Appl.      Application Id                                                 DB       # of
         Name           Handle                                                                    Name    Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
GMCW     db2bp          35624      *LOCAL.gmcw.120229025714                                       LGM_MFG  1     


6,断开所有的连接


pedb02:[/db2data/db1]$db2 force applications all
DB20000I  The FORCE APPLICATION command completed successfully.
DB21024I  This command is asynchronous and may not be effective immediately.

 

7,恢复数据库

db2 restore db lgm_mfg from /db2data/db1 taken at 20120422040001 replace existing without prompting

pedb02:[/db2data/db1]$db2 restore db lgm_mfg from /db2data/db1 taken at 20120226040001 replace existing without prompting
SQL2540W  Restore is successful, however a warning "2539" was encountered
during Database Restore while processing in No Interrupt mode.


8,开一个新的窗口连接数据库,监控恢复数据库的进度

pedb02:[/home/gmcw]$db2 list utilities show detail

ID                               = 20
Type                             = RESTORE
Database Name                    = LGM_MFG
Partition Number                 = 0
Description                      = db
Start Time                       = 02/29/2012 11:01:17.251879
State                            = Executing
Invocation Type                  = User
Progress Monitoring:
      Completed Work             = 1654951936 bytes
      Start Time                 = 02/29/2012 11:01:17.251884

 

pedb02:[/home/gmcw]$db2 list utilities show detail
SQL1611W  No data was returned by Database System Monitor.  SQLSTATE=00000
You have mail in /usr/spool/mail/gmcw

9,恢复完成后,前滚日志
/dbbackup/gmcw/archivelog/lgm_mfg

     pedb02:[/dbbackup/online/db2admin]$
db2 "rollforward db lgm_mfg  to end of logs  overflow log path (/dbbackup/gmcw/archivelog/lgm_mfg)"

pedb02:[/dbbackup/gmcw/archivelog/lgm_mfg]$db2 "rollforward db lgm_mfg  to end of logs  overflow log path (/dbbackup/gmcw/archivelog/lgm_mfg)"

                                 Rollforward Status

 Input database alias                   = lgm_mfg
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = DB  working
 Next log file to be read               = S0021261.LOG
 Log files processed                    = S0021177.LOG - S0021259.LOG
 Last committed transaction             = 2012-02-29-12.11.55.000000 Local

DB20000I  The ROLLFORWARD command completed successfully.


前滚完成

 


10,监控前滚的状态:

pedb02:[/home/gmcw]$db2 list utilities show detail

ID                               = 21
Type                             = ROLLFORWARD RECOVERY
Database Name                    = LGM_MFG
Partition Number                 = 0
Description                      = Database Rollforward Recovery
Start Time                       = 02/29/2012 13:43:28.350131
State                            = Executing
Invocation Type                  = User
Progress Monitoring:
   Phase Number [Current]        = 1
      Description                = Forward
      Completed Work             = 71578315 bytes
      Start Time                 = 02/29/2012 13:43:28.350138

   Phase Number                  = 2
      Description                = Backward
      Completed Work             = 0 bytes
      Start Time                 = Not Started


11,停止前滚
  pedb02:[/dbbackup/online/db2admin]$
    db2 "rollforward db lgm_mfg  to end of logs and  complete  overflow log path (/dbbackup/gmcw/archivelog/lgm_mfg)"


                                 Rollforward Status

 Input database alias                   = lgm_mfg
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0021177.LOG - S0021259.LOG
 Last committed transaction             = 2012-02-29-12.11.55.000000 Local

DB20000I  The ROLLFORWARD command completed successfully.

12,连接测试

db2  connect to lgm_mfg
db2 list tables

13,  lgm_mfg的各种参数配置

pedb02:[/home/gmcw]$db2 get db cfg for lgm_mfg

       Database Configuration for Database lgm_mfg

 Database configuration release level                    = 0x0c00
 Database release level                                  = 0x0c00

 Database territory                                      = CN
 Database code page                                      = 1208
 Database code set                                       = UTF-8
 Database country/region code                            = 86
 Database collating sequence                             = IDENTITY
 Alternate collating sequence              (ALT_COLLATE) =
 Number compatibility                                    = OFF
 Varchar2 compatibility                                  = OFF
 Database page size                                      = OFF

 Dynamic SQL Query management           (DYN_QUERY_MGMT) = DISABLE

 Discovery support for this database       (DISCOVER_DB) = ENABLE

 Restrict access                                         = NO
 Default query optimization class         (DFT_QUERYOPT) = 5
 Degree of parallelism                      (DFT_DEGREE) = 1
 Continue upon arithmetic exceptions   (DFT_SQLMATHWARN) = NO
 Default refresh age                   (DFT_REFRESH_AGE) = 0
 Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM
 Number of frequent values retained     (NUM_FREQVALUES) = 10
 Number of quantiles retained            (NUM_QUANTILES) = 20

 Decimal floating point rounding mode  (DECFLT_ROUNDING) = ROUND_HALF_EVEN

 Backup pending                                          = NO

 Database is consistent                                  = YES
 Rollforward pending                                     = NO
 Restore pending                                         = NO

 Multi-page file allocation enabled                      = YES

 Log retain for recovery status                          = NO
 User exit for logging status                            = YES

 Self tuning memory                    (SELF_TUNING_MEM) = OFF
 Size of database shared memory (4KB)  (DATABASE_MEMORY) = AUTOMATIC(278624)
 Database memory threshold               (DB_MEM_THRESH) = 10
 Max storage for lock list (4KB)              (LOCKLIST) = 100
 Percent. of lock lists per application       (MAXLOCKS) = 10
 Package cache size (4KB)                   (PCKCACHESZ) = (MAXAPPLS*8)
 Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = 5000
 Sort list heap (4KB)                         (SORTHEAP) = 256

 Database heap (4KB)                            (DBHEAP) = AUTOMATIC(1200)
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = (MAXAPPLS*5)
 Log buffer size (4KB)                        (LOGBUFSZ) = 8
 Utilities heap size (4KB)                (UTIL_HEAP_SZ) = 5000
 Buffer pool size (pages)                     (BUFFPAGE) = 1000
 SQL statement heap (4KB)                     (STMTHEAP) = AUTOMATIC(4096)
 Default application heap (4KB)             (APPLHEAPSZ) = AUTOMATIC(256)
 Application Memory Size (4KB)             (APPL_MEMORY) = AUTOMATIC(40000)
 Statistics heap size (4KB)               (STAT_HEAP_SZ) = AUTOMATIC(4384)

 Interval for checking deadlock (ms)         (DLCHKTIME) = 10000
 Lock timeout (sec)                        (LOCKTIMEOUT) = 10

 Changed pages threshold                (CHNGPGS_THRESH) = 60
 Number of asynchronous page cleaners   (NUM_IOCLEANERS) = AUTOMATIC(15)
 Number of I/O servers                   (NUM_IOSERVERS) = AUTOMATIC(4)
 Index sort flag                             (INDEXSORT) = YES
 Sequential detect flag                      (SEQDETECT) = YES
 Default prefetch size (pages)         (DFT_PREFETCH_SZ) = AUTOMATIC

 Track modified pages                         (TRACKMOD) = OFF

 Default number of containers                            = 1
 Default tablespace extentsize (pages)   (DFT_EXTENT_SZ) = 32

 Max number of active applications            (MAXAPPLS) = AUTOMATIC(109)
 Average number of active applications       (AVG_APPLS) = AUTOMATIC(1)
 Max DB files open per application            (MAXFILOP) = 61440

 Log file size (4KB)                         (LOGFILSIZ) = 1000
 Number of primary log files                (LOGPRIMARY) = 7
 Number of secondary log files               (LOGSECOND) = 249
 Changed path to log files                  (NEWLOGPATH) =
 Path to log files                                       = /db2data/lgmdb/gmcw/NODE0000/SQL00001/SQLOGDIR/
 Overflow log path                     (OVERFLOWLOGPATH) =
 Mirror log path                         (MIRRORLOGPATH) =
 First active log file                                   = S0021261.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

 Group commit count                          (MINCOMMIT) = 1
 Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
 Log retain for recovery enabled             (LOGRETAIN) = OFF
 User exit for logging enabled                (USEREXIT) = OFF

 HADR database role                                      = STANDARD
 HADR local host name                  (HADR_LOCAL_HOST) =
 HADR local service name                (HADR_LOCAL_SVC) =
 HADR remote host name                (HADR_REMOTE_HOST) =
 HADR remote service name              (HADR_REMOTE_SVC) =
 HADR instance name of remote server  (HADR_REMOTE_INST) =
 HADR timeout value                       (HADR_TIMEOUT) = 120
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
 HADR peer window duration (seconds)  (HADR_PEER_WINDOW) = 0

 First log archive method                 (LOGARCHMETH1) = DISK:/db2data/lgm_archive_log/LGM_MFG/
 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
 Vendor options                              (VENDOROPT) =

 Auto restart enabled                      (AUTORESTART) = ON
 Index re-creation time and redo index build  (INDEXREC) = SYSTEM (RESTART)
 Log pages during index build            (LOGINDEXBUILD) = OFF
 Default number of loadrec sessions    (DFT_LOADREC_SES) = 1
 Number of database backups to retain   (NUM_DB_BACKUPS) = 12
 Recovery history retention (days)     (REC_HIS_RETENTN) = 366
 Auto deletion of recovery objects    (AUTO_DEL_REC_OBJ) = OFF

 TSM management class                    (TSM_MGMTCLASS) =
 TSM node name                            (TSM_NODENAME) =
 TSM owner                                   (TSM_OWNER) =
 TSM password                             (TSM_PASSWORD) =

 Automatic maintenance                      (AUTO_MAINT) = ON
   Automatic database backup            (AUTO_DB_BACKUP) = OFF
   Automatic table maintenance          (AUTO_TBL_MAINT) = ON
     Automatic runstats                  (AUTO_RUNSTATS) = ON
       Automatic statement statistics  (AUTO_STMT_STATS) = OFF
     Automatic statistics profiling    (AUTO_STATS_PROF) = OFF
       Automatic profile updates         (AUTO_PROF_UPD) = OFF
     Automatic reorganization               (AUTO_REORG) = OFF

 Enable XML Character operations        (ENABLE_XMLCHAR) = YES
 WLM Collection Interval (minutes)     (WLM_COLLECT_INT) = 0

14,   dbm的各种参数配置

pedb02:[/home/gmcw]$db2 get dbm cfg

          Database Manager Configuration

     Node type = Enterprise Server Edition with local and remote clients

 Database manager configuration release level            = 0x0c00

 CPU speed (millisec/instruction)             (CPUSPEED) = 4.998977e-07
 Communications bandwidth (MB/sec)      (COMM_BANDWIDTH) = 1.000000e+02

 Max number of concurrently active databases     (NUMDB) = 8
 Federated Database System Support           (FEDERATED) = NO
 Transaction processor monitor name        (TP_MON_NAME) =

 Default charge-back account           (DFT_ACCOUNT_STR) =

 Java Development Kit installation path       (JDK_PATH) = /home/gmcw/sqllib/java/jdk64

 Diagnostic error capture level              (DIAGLEVEL) = 3
 Notify Level                              (NOTIFYLEVEL) = 3
 Diagnostic data directory path               (DIAGPATH) = /db2data/dblog/gmcw

 Default database monitor switches
   Buffer pool                         (DFT_MON_BUFPOOL) = OFF
   Lock                                   (DFT_MON_LOCK) = OFF
   Sort                                   (DFT_MON_SORT) = OFF
   Statement                              (DFT_MON_STMT) = OFF
   Table                                 (DFT_MON_TABLE) = OFF
   Timestamp                         (DFT_MON_TIMESTAMP) = ON
   Unit of work                            (DFT_MON_UOW) = OFF
 Monitor health of instance and databases   (HEALTH_MON) = ON

 SYSADM group name                        (SYSADM_GROUP) = DBADMING
 SYSCTRL group name                      (SYSCTRL_GROUP) =
 SYSMAINT group name                    (SYSMAINT_GROUP) =
 SYSMON group name                        (SYSMON_GROUP) =

 Client Userid-Password Plugin          (CLNT_PW_PLUGIN) =
 Client Kerberos Plugin                (CLNT_KRB_PLUGIN) =
 Group Plugin                             (GROUP_PLUGIN) =
 GSS Plugin for Local Authorization    (LOCAL_GSSPLUGIN) =
 Server Plugin Mode                    (SRV_PLUGIN_MODE) = UNFENCED
 Server List of GSS Plugins      (SRVCON_GSSPLUGIN_LIST) =
 Server Userid-Password Plugin        (SRVCON_PW_PLUGIN) =
 Server Connection Authentication          (SRVCON_AUTH) = NOT_SPECIFIED
 Cluster manager                           (CLUSTER_MGR) =

 Database manager authentication        (AUTHENTICATION) = SERVER
 Cataloging allowed without authority   (CATALOG_NOAUTH) = NO
 Trust all clients                      (TRUST_ALLCLNTS) = YES
 Trusted client authentication          (TRUST_CLNTAUTH) = CLIENT
 Bypass federated authentication            (FED_NOAUTH) = NO

 Default database path                       (DFTDBPATH) = /home/gmcw

 Database monitor heap size (4KB)          (MON_HEAP_SZ) = AUTOMATIC(90)
 Java Virtual Machine heap size (4KB)     (JAVA_HEAP_SZ) = 2048
 Audit buffer size (4KB)                  (AUDIT_BUF_SZ) = 0
 Size of instance shared memory (4KB)  (INSTANCE_MEMORY) = AUTOMATIC(3498659)
 Backup buffer default size (4KB)            (BACKBUFSZ) =, 1024
 Restore buffer default size (4KB)           (RESTBUFSZ) = 1024

 Agent stack size                       (AGENT_STACK_SZ) = 1024
 Sort heap threshold (4KB)                  (SHEAPTHRES) = 0

 Directory cache support                     (DIR_CACHE) = YES

 Application support layer heap size (4KB)   (ASLHEAPSZ) = 15
 Max requester I/O block size (bytes)         (RQRIOBLK) = 32767
 Query heap size (4KB)                   (QUERY_HEAP_SZ) = 1000

 Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10

 Priority of agents                           (AGENTPRI) = SYSTEM
 Agent pool size                        (NUM_POOLAGENTS) = AUTOMATIC(100)
 Initial number of agents in pool       (NUM_INITAGENTS) = 0
 Max number of coordinating agents     (MAX_COORDAGENTS) = AUTOMATIC(200)
 Max number of client connections      (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)

 Keep fenced process                        (KEEPFENCED) = YES
 Number of pooled fenced processes         (FENCED_POOL) = AUTOMATIC(MAX_COORDAGENTS)
 Initial number of fenced processes     (NUM_INITFENCED) = 0

 Index re-creation time and redo index build  (INDEXREC) = RESTART

 Transaction manager database name         (TM_DATABASE) = 1ST_CONN
 Transaction resync interval (sec)     (RESYNC_INTERVAL) = 180

 SPM name                                     (SPM_NAME) =
 SPM log size                          (SPM_LOG_FILE_SZ) = 256
 SPM resync agent limit                 (SPM_MAX_RESYNC) = 20
 SPM log path                             (SPM_LOG_PATH) =

 TCP/IP Service name                          (SVCENAME) = 50000
 Discovery mode                               (DISCOVER) = SEARCH
 Discover server instance                (DISCOVER_INST) = ENABLE

 Maximum query degree of parallelism   (MAX_QUERYDEGREE) = ANY
 Enable intra-partition parallelism     (INTRA_PARALLEL) = NO

 Maximum Asynchronous TQs per query    (FEDERATED_ASYNC) = 0

 No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC(4096)
 No. of int. communication channels   (FCM_NUM_CHANNELS) = AUTOMATIC(2048)
 Node connection elapse time (sec)         (CONN_ELAPSE) = 10
 Max number of node connection retries (MAX_CONNRETRIES) = 5
 Max time difference between nodes (min) (MAX_TIME_DIFF) = 60

 db2start/db2stop timeout (min)        (START_STOP_TIME) = 10


15,环境变量

pedb02:[/home/gmcw]$db2set -all
[i] AUTOSTART=YES
[i] DB2COMM=tcpip
[g] DB2SYSTEM=pedb02
[g] DB2INSTDEF=db2admin

 

pedb02:[/home/gmcw]$env
_=/usr/bin/env
LANG=en_US
LOGIN=gmcw
PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:/home/gmcw/bin:/usr/bin/X11:/sbin:.:/home/gmcw/sqllib/bin:/home/gmcw/sqllib/adm:/home/gmcw/sqllib/misc
LC__FASTMSG=true
HISTFILE=/var/hist/tzpj/20120229_105212_qo2nqzva
CLASSPATH=/home/gmcw/sqllib/java/db2java.zip:/home/gmcw/sqllib/java/db2jcc.jar:/home/gmcw/sqllib/java/sqlj.zip:/home/gmcw/sqllib/function:/home/gmcw/sqllib/java/db2jcc_license_cu.jar:.
LOGNAME=gmcw
MAIL=/usr/spool/mail/gmcw
LOCPATH=/usr/lib/nls/loc
PS1=pedb02:[$PWD]$
USER=gmcw
AUTHSTATE=compat
SHELL=/usr/bin/ksh
ODMDIR=/etc/objrepos
HOME=/home/gmcw
DB2INSTANCE=gmcw
DT=20120229_105212
TERM=xterm
MAILMSG=[YOU HAVE NEW MAIL]
PWD=/home/gmcw
TZ=BEIST-8
A__z=! LOGNAME
NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat
LIBPATH=/usr/lib:/lib:/home/gmcw/sqllib/lib64


这篇文章对你多有用?

相关文章

article DB2数据库的备份和恢复
一、备份(backup)数据库 1、离线全备份 ...

(No rating)  11-28-2017    Views: 493   
article DB2数据库的备份和恢复

(No rating)  11-28-2017    Views: 477   
article 如何使用db2ckbkp命令查看DB2数据库备份的类型及有效性

(No rating)  1-7-2013    Views: 1936   

用户评语

添加评语
当前还没有评语.


.: .: .: .: .:
[ 登陆 ]
北京护航科技有限公司 2006

Novots Technologies Limited