[DB2]离线备份与离线还原

         所谓离线备份，就是在所有用户断开链接的情况下，对数据进行的备份。

         本例中，操作的数据库是示例数据sample。

1、确认日志归档功能开启：

1.1、              查看数据的配置参数LOGARCHMETH1，发现归档日志未打开：

db2 => get db cfg

。。。。。。。。。。。。。。。。

First log archive method                 (LOGARCHMETH1) = OFF

。。。。。。。。。。。。。。。。

1.2、        修改LOGARCHMETH1参数，让数据处于归档模式：

db2 =>update db cfg for sample using LOGARCHMETH1 logretain

DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

 

2、修改完上述参数后，数据库会处于BACKUP PENDING状态，需要我们进行一次备份。执行backup命令即可：

db2 => connect to sample

SQL1116N  A connection to or activation of database "SAMPLE" cannot be made

because of BACKUP PENDING.  SQLSTATE=57019

db2 => backup database sample to /home/db2inst1/bak_sample

Backup successful. The timestamp for this backup image is : 20100804192910

3、查看备份文件的信息：

[db2inst1@localhost ~]$ db2ckbkp -h /home/db2inst1/bak_sample/SAMPLE.0.db2inst1.NODE0000.CATN0000.20100804192910.001

=====================

MEDIA HEADER REACHED:

=====================

        Server Database Name           -- SAMPLE

        Server Database Alias          -- SAMPLE

        Client Database Alias          -- SAMPLE

        Timestamp                      -- 20100804192910

        Database Partition Number      -- 0

        Instance                       -- db2inst1

        Sequence Number                -- 1

        Release ID                     -- D00

        Database Seed                  -- 82D84DC2

        DB Comment's Codepage (Volume) -- 0

        DB Comment (Volume)            --                              

        DB Comment's Codepage (System) -- 0

        DB Comment (System)            --                              

        Authentication Value           -- -1

        Backup Mode                    -- 0

        Includes Logs                  -- 0

        Compression                    -- 0

        Backup Type                    -- 0

        Backup Gran.                   -- 0

        Status Flags                   -- 15

        System Cats inc                -- 1

        Catalog Partition Number       -- 0

        DB Codeset                     -- UTF-8

        DB Territory                   --

        LogID                          -- 1279630639

        LogPath                        -- /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/

        Backup Buffer Size             -- 4460544

        Number of Sessions             -- 1

        Platform                       -- 12

 

 The proper image file name would be:

SAMPLE.0.db2inst1.NODE0000.CATN0000.20100804192910.001

 

 

[1] Buffers processed:  #################################

         4、我们在某个表空间上新建一张表，然后进行离线备份，之后再向该表中插入数据，让日志中记录有事务操作。最后，模拟一次故障，将表空间所对应的容器删除。

         4．1、首先查看下，sample的表空间

db2 => list tablespaces

           Tablespaces for Current Database

 Tablespace ID                        = 0

 Name                                 = SYSCATSPACE

 Type                                 = Database managed space

 Contents                             = All permanent data. Regular table space.

 State                                = 0x0000

   Detailed explanation:

     Normal

 

 Tablespace ID                        = 1

 Name                                 = TEMPSPACE1

 Type                                 = System managed space

 Contents                             = System Temporary data

 State                                = 0x0000

   Detailed explanation:

     Normal

 

 Tablespace ID                        = 2

 Name                                 = USERSPACE1

 Type                                 = Database managed space

 Contents                             = All permanent data. Large table space.

 State                                = 0x0000

 

         4.2、查看2号表空间USERSPACE1所对应的容器：

db2 => list tablespace containers for 2 show detail

            Tablespace Containers for Tablespace 2

 

 Container ID                         = 0

 Name                                 = /home/db2inst1/db2inst1/NODE0000/SAMPLE/T0000002/C0000000.LRG

 Type                                 = File

 Total pages                          = 4096

 Useable pages                        = 4064

 Accessible                           = Yes

         4.3、现在我们在表空间上，新建一张表，并且插入一条数据：

db2 => create table newtable(aa int)  in userspace1

DB20000I  The SQL command completed successfully.

db2 => insert into newtable values(123)

DB20000I  The SQL command completed successfully.

db2 => commit

DB20000I  The SQL command completed successfully.

db2 => select * from newtable

AA        

-----------

        123

 

  1 record(s) selected.

         4.4、下面，我再进行一次离线备份，此次备份是距离数据库出现故障前的一次备份：

db2 => force applications all

DB20000I  The FORCE APPLICATION command completed successfully.

DB21024I  This command is asynchronous and may not be effective immediately.

db2 => backup database sample to /home/db2inst1/bak_sample

Backup successful. The timestamp for this backup image is : 20100804193718

         4.5、连接上数据库，在新建的表中插入三条记录：

db2 => connect to sample

 

   Database Connection Information

 

 Database server        = DB2/LINUX 9.7.1

 SQL authorization ID   = DB2INST1

 Local database alias   = SAMPLE

 

db2 => insert into newtable values(222)

DB20000I  The SQL command completed successfully.

db2 => insert into newtable values(333)

DB20000I  The SQL command completed successfully.

db2 => insert into newtable values(444)

DB20000I  The SQL command completed successfully.

db2 => commit

DB20000I  The SQL command completed successfully.

         4.6、现在将2号表空间所对应的数据文件（容器），手动删除，模拟硬盘故障。再去查看表空间的容器时，发现已经不可访问了（Accessible已经变成了No）：

[db2inst1@localhost ~]$ rm -f /home/db2inst1/db2inst1/NODE0000/SAMPLE/T0000002/C0000000.LRG

db2 => list tablespace containers for 2 show detail

 

            Tablespace Containers for Tablespace 2

 

 Container ID                         = 0

 Name                                 = /home/db2inst1/db2inst1/NODE0000/SAMPLE/T0000002/C0000000.LRG

 Type                                 = File

 Total pages                          = 4096

 Useable pages                        = 4064

 Accessible                           = No

 

         5、数据库还原

         还原分为两个步骤：restore（将数据库恢复到上次备份的时刻）和roll forward（使用事务日志前滚到失败点那一刻或用户指定的时刻）。这两个步骤，类似于oracle中的restore和recovery。

5.1   首先查看下备份的信息，确认能够restore遭到破坏的表空间

db2 => list history backup all for db sample

                    List History File for sample

Number of matching file entries = 2

 

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID

 -- --- ------------------ ---- --- ------------ ------------ --------------

  B  D  20100804120733001   F    D  S0000000.LOG S0000000.LOG 

 ----------------------------------------------------------------------------

  Contains 5 tablespace(s):

 

  00001 SYSCATSPACE                                                          

  00002 USERSPACE1                                                           

  00003 IBMDB2SAMPLEREL                                                       

  00004 SYSTOOLSPACE                                                         

  00005 IBMDB2SAMPLEXML                                                      

 ----------------------------------------------------------------------------

    Comment: DB2 BACKUP SAMPLE OFFLINE                                       

 Start Time: 20100804120733

   End Time: 20100804120743

     Status: A

 ----------------------------------------------------------------------------

  EID: 4 Location: /home/db2inst1/bak_sample

 

 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID

 -- --- ------------------ ---- --- ------------ ------------ --------------

  B  D  20100804144846001   F    D  S0000000.LOG S0000000.LOG 

 ----------------------------------------------------------------------------

  Contains 5 tablespace(s):

 

  00001 SYSCATSPACE                                                          

  00002 USERSPACE1                                                           

  00003 IBMDB2SAMPLEREL                                                      

  00004 SYSTOOLSPACE                                                         

  00005 IBMDB2SAMPLEXML                                                      

 ----------------------------------------------------------------------------

    Comment: DB2 BACKUP SAMPLE OFFLINE                                       

 Start Time: 20100804144846

   End Time: 20100804144856

     Status: A

 ----------------------------------------------------------------------------

  EID: 7 Location: /home/db2inst1/bak_sample

5.2   使用restore命令，进行恢复

5.2.1首次使用restore命令时，提示状态不正确：

db2 => restore database sample from /home/db2inst1/bak_sample

SQL1350N  The application is not in the correct state to process this request.

Reason code="1".

         查看一下，出错的原因，发现是需要执行commit或rollback后，才可以执行restore工具：

db2 => ? SQL1350N

SQL1350N  The application is not in the correct state to process this

      request. Reason code="".

Explanation:

Corresponding to "":

01       The application is currently processing SQL and cannot process

         the utility command requested.

User response:

Corresponding to "":

01       Complete the unit of work (using either COMMIT or ROLLBACK)

         before reissuing this command.

5.2.2执行commit命令，并且再次运行2.1中的命令，又提示无法进行操作（提示有多个备份文件可供使用）：

db2 => commit

DB20000I  The SQL command completed successfully.

db2 => restore database sample from /home/db2inst1/bak_sample

SQL2522N  More than one backup file matches the timestamp value provided for

the backed up database image.

5.2.3 现在我们使用taken at子句来指定restore命令使用的备份文件，taken at 后是需要接一个timestamp的，这个可以从备份文件名中提取出来，请关注下面的文件名中，标红的部分：

[db2inst1@localhost bak_sample]$ ll

total 270384

-rw------- 1 db2inst1 db2iadm1 138297344 Aug  4 12:07 SAMPLE.0.db2inst1.NODE0000.CATN0000. 20100804192910.001

-rw------- 1 db2inst1 db2iadm1 138297344 Aug  4 14:48 SAMPLE.0.db2inst1.NODE0000.CATN0000. 20100804193718.001

db2 => restore database sample from /home/db2inst1/bak_sample taken at 20100804193718

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.

         5.2.4 经过以上步骤，终于将数据库restore成功，但是此时连接不上数据库，因为还需要进行前滚：

db2 => connect to sample

SQL1117N  A connection to or activation of database "SAMPLE" cannot be made

because of ROLL-FORWARD PENDING.  SQLSTATE=57019

        

6 进行前滚

db2 => rollforward database sample to end of logs and stop

             Rollforward Status

 

 Input database alias                   = sample

 Number of nodes have returned status   = 1

 

 Node number                            = 0

 Rollforward status                     = not pending

 Next log file to be read               =

 Log files processed                    = S0000001.LOG - S0000001.LOG

 Last committed transaction             = 2010-08-04-11.39.27.000000 UTC

 

DB20000I  The ROLLFORWARD command completed successfully.

         发现使用了S0000001.LOG日志，来进行rollforward。

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

转载于:http://blog.itpub.net/14600958/viewspace-670309/