实战演练

zhuhichn

88504018喜欢 db2 oracle java的朋友进来了
 
循环日志下的数据库重定向
可以利用备份镜像重定向功能建立新的数据库

1.
重定向恢复
CMD: db2 "restore database sample from /mnt/hgfs/F/backup/ INTO SAMPLE2 REDIRECT WITHOUT ROLLING FORWARD"
RESULT:
SQL1277W  A redirected restore operation is being performed.  Table space
configuration can now be viewed and table spaces that do not use automatic
storage can have their containers reconfigured.
DB20000I  The RESTORE DATABASE command completed successfully.
因为是数据库是自动存储，不需要指定容器。
确认容器的状态,此时表空间处在恢复暂挂（Restore pending)下，需要进一步进行恢复处理。
CMD: db2 list tablespaces
RESULT:
           Tablespaces for Current Database

 Tablespace ID                        = 0
 Name                                 = SYSCATSPACE
 Type                                 = Database managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x2000100
   Detailed explanation:
     Restore pending
     Storage may be defined

 Tablespace ID                        = 1
 Name                                 = TEMPSPACE1
 Type                                 = System managed space
 Contents                             = System Temporary data
 State                                = 0x2000100
   Detailed explanation:
     Restore pending
     Storage may be defined

 Tablespace ID                        = 2
 Name                                 = USERSPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x2000100
   Detailed explanation:
     Restore pending
     Storage may be defined

 Tablespace ID                        = 3
 Name                                 = IBMDB2SAMPLEREL
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x2000100
   Detailed explanation:
     Restore pending
     Storage may be defined

 Tablespace ID                        = 4
 Name                                 = IBMDB2SAMPLEXML
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x2000100
   Detailed explanation:
     Restore pending
     Storage may be defined

 Tablespace ID                        = 5
 Name                                 = SYSTOOLSPACE
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x2000100
   Detailed explanation:
     Restore pending
     Storage may be defined

2.
继续恢复操作，数据库完全恢复正常。表空间处于NORMAL状态。
SAMPLE2数据库被建立。
CMD: db2 "restore database sample continue"
RESULT: DB20000I  The RESTORE DATABASE command completed successfully.

3.
可以利用选项GENERATE SCRIPT自动生成重定向恢复脚本
CMD:  db2 "restore database sample from /mnt/hgfs/F/backup/ INTO SAMPLE2 REDIRECT GENERATE SCRIPT redirectscript"

redirectscript内容:
-- *****************************************************************************
-- ** automatically created redirect restore script
-- *****************************************************************************
UPDATE COMMAND OPTIONS USING S ON Z ON SAMPLE_NODE0000.out V ON;
SET CLIENT ATTACH_DBPARTITIONNUM  0;
SET CLIENT CONNECT_DBPARTITIONNUM 0;
-- *****************************************************************************
-- ** automatically created redirect restore script
-- *****************************************************************************
RESTORE DATABASE SAMPLE
-- USER  <username>
-- USING '<password>'
FROM '/mnt/hgfs/F/backup/'
TAKEN AT 20100509155627
-- ON '/home/db2inst1'
-- DBPATH ON '<target-directory>'
INTO SAMPLE2
-- NEWLOGPATH '/home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/'
-- WITH <num-buff> BUFFERS
-- BUFFER <buffer-size>
-- REPLACE HISTORY FILE
-- REPLACE EXISTING
REDIRECT
-- PARALLELISM <n>
WITHOUT ROLLING FORWARD
-- WITHOUT PROMPTING
;
-- *****************************************************************************
-- ** table space definition
-- *****************************************************************************
-- *****************************************************************************
-- ** Tablespace name                            = SYSCATSPACE
-- **   Tablespace ID                            = 0
-- **   Tablespace Type                          = Database managed space                
-- **   Tablespace Content Type                  = All permanent data. Regular table space.
-- **   Tablespace Page size (bytes)             = 8192
-- **   Tablespace Extent size (pages)           = 4
-- **   Using automatic storage                  = Yes
-- **   Auto-resize enabled                      = Yes
-- **   Total number of pages                    = 12288
-- **   Number of usable pages                   = 12284
-- **   High water mark (pages)                  = 12256
-- *****************************************************************************
-- *****************************************************************************
-- ** Tablespace name                            = TEMPSPACE1
-- **   Tablespace ID                            = 1
-- **   Tablespace Type                          = System managed space                  
-- **   Tablespace Content Type                  = System Temporary data                 
-- **   Tablespace Page size (bytes)             = 8192
-- **   Tablespace Extent size (pages)           = 32
-- **   Using automatic storage                  = Yes
-- **   Total number of pages                    = 1
-- *****************************************************************************
-- *****************************************************************************
-- ** Tablespace name                            = USERSPACE1
-- **   Tablespace ID                            = 2
-- **   Tablespace Type                          = Database managed space                
-- **   Tablespace Content Type                  = All permanent data. Large table space.
-- **   Tablespace Page size (bytes)             = 8192
-- **   Tablespace Extent size (pages)           = 32
-- **   Using automatic storage                  = Yes
-- **   Auto-resize enabled                      = Yes
-- **   Total number of pages                    = 4096
-- **   Number of usable pages                   = 4064
-- **   High water mark (pages)                  = 1824
-- *****************************************************************************
-- *****************************************************************************
-- ** Tablespace name                            = IBMDB2SAMPLEREL
-- **   Tablespace ID                            = 3
-- **   Tablespace Type                          = Database managed space                
-- **   Tablespace Content Type                  = All permanent data. Large table space.
-- **   Tablespace Page size (bytes)             = 8192
-- **   Tablespace Extent size (pages)           = 32
-- **   Using automatic storage                  = Yes
-- **   Auto-resize enabled                      = Yes
-- **   Total number of pages                    = 4096
-- **   Number of usable pages                   = 4064
-- **   High water mark (pages)                  = 608
-- *****************************************************************************
-- *****************************************************************************
-- ** Tablespace name                            = IBMDB2SAMPLEXML
-- **   Tablespace ID                            = 4
-- **   Tablespace Type                          = Database managed space                
-- **   Tablespace Content Type                  = All permanent data. Large table space.
-- **   Tablespace Page size (bytes)             = 8192
-- **   Tablespace Extent size (pages)           = 32
-- **   Using automatic storage                  = Yes
-- **   Auto-resize enabled                      = Yes
-- **   Total number of pages                    = 4096
-- **   Number of usable pages                   = 4064
-- **   High water mark (pages)                  = 1440
-- *****************************************************************************
-- *****************************************************************************
-- ** Tablespace name                            = SYSTOOLSPACE
-- **   Tablespace ID                            = 5
-- **   Tablespace Type                          = Database managed space                
-- **   Tablespace Content Type                  = All permanent data. Large table space.
-- **   Tablespace Page size (bytes)             = 8192
-- **   Tablespace Extent size (pages)           = 4
-- **   Using automatic storage                  = Yes
-- **   Auto-resize enabled                      = Yes
-- **   Total number of pages                    = 4096
-- **   Number of usable pages                   = 4092
-- **   High water mark (pages)                  = 72
-- *****************************************************************************
-- *****************************************************************************
-- ** start redirected restore
-- *****************************************************************************
RESTORE DATABASE SAMPLE CONTINUE;
-- *****************************************************************************
-- ** end of file
-- *****************************************************************************

4.
可以根据需要定制修改重定向脚本redirectscript，然后再用 db2 -tvf redirectscript执行。
这个功能很实用。