[DB2]在线备份数据库与表空间

         在线备份数据库和在线备份表空间，应该是大多数生产环境下使用的备份方式。其最大的优点是，能够在不断开用户连接的情况下，对数据进行备份。

         在还原的时候，若是针对数据库级的还原，则需要断开所有用户的连接；而表空间级别的还原，则可以在online状态下进行，且还原可直接使用数据库的备份文件进行。

一、在线备份与数据库“脱机”还原

1、  备份数据库，并且备份日志（备份的是备份期间其他用户的事务操作，产生的日志）：

在另一个会话中，当备份开始时，就插入一条记录，并进行提交，本例中将该数据提交完之后，发现备份还在进行之中：

Session A进行备份：

db2 => commit

DB20000I  The SQL command completed successfully.

db2 => backup database sample online to /home/db2inst1/bak_sample include logs

 

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

 

Session B在Session A备份开始后，立刻插入数据：

db2 => insert into newtable values(777)

DB20000I  The SQL command completed successfully.

db2 => commit

DB20000I  The SQL command completed successfully.

 

Session A在备份完成之后，再次连上数据库，就发现已经多了一条数据：

db2 => connect to sample

   Database Connection Information

 

 Database server        = DB2/LINUX 9.7.1

 SQL authorization ID   = DB2INST1

 Local database alias   = SAMPLE

 

db2 => select * from newtable

 

AA        

-----------

        123

        222

        333

        444

        555

        777

  6 record(s) selected.

 

2、现在模拟故障，表newtable所在的表空间容器，意外丢失：

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

 

db2 => select * from newtable

 

AA        

-----------

SQL0290N  Table space access is not allowed.  SQLSTATE=55039

 

3断开所有连接，对数据库进行restore和rollforward：

db2 => force applications all

DB20000I  The FORCE APPLICATION command completed successfully.

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

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

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.

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                    = S0000004.LOG - S0000005.LOG

 Last committed transaction             = 2010-08-04-23.18.19.000000 Local

 

DB20000I  The ROLLFORWARD command completed successfully.

 

4登陆上数据库，发现在步骤1中插入的记录，也恢复成功了：

db2 => connect to sample

 

   Database Connection Information

 

 Database server        = DB2/LINUX 9.7.1

 SQL authorization ID   = DB2INST1

 Local database alias   = SAMPLE

 

db2 => select * from newtable

 

AA        

-----------

        123

        222

        333

        444

        555

        777

 

  6 record(s) selected.

 

         通过上述实验发现，直接restore和rollforward数据库，需要断开所有用户的连接，“代价”过大。

 

二、使用数据库备份文件，进行表空间在线还原

下面，还是使用上面实验中的数据库的备份，我们仅对表空间进行online状态下的操作，可以在不断开用户连接的情况下进行。

1、  先将表空间容器删除

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

2、  进行restore，用数据库的备份文件来还原，仅还原表空间，状态处于online：

db2 => restore database sample  TABLESPACE ( USERSPACE1 )  ONLINE from /home/db2inst1/bak_sample taken at 20100804225927

DB20000I  The RESTORE DATABASE command completed successfully.

3、进行rollforward，状态同样是online：

db2 => rollforward database sample  to end of logs and stop tablespace (USERSPACE1) online

                                 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                    =  -

 Last committed transaction             = 2010-08-04-23.18.19.000000 Local

 

DB20000I  The ROLLFORWARD command completed successfully.

4、其他已经连接上的用户，此时就能查询到结果了。并且在DBA进行恢复的时候，不会强制断开用户的连接：

db2 => select * from newtable

 

AA        

-----------

SQL0290N  Table space access is not allowed.  SQLSTATE=55039

db2 => select * from newtable

 

AA        

-----------

        123

        333

        444

        555

        777

 

  5 record(s) selected.

问：第一次查询时，怎么显示了“SQL0290N  Table space access is not allowed.  SQLSTATE=55039”的结果？

答：表空间未完成恢复，状态不是normal时，是不允许其他用户访问表空间的数据的。一旦恢复成功，用户就能够进行相应的操作。

 

问：对表空间进行restore和rollforward操作的用户，其本身与数据的连接，是否会被断开？

答：会被断开。详情如下：

db2 => rollforward database sample  to end of logs and stop tablespace (USERSPACE1) online

 

                                 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                    =  -

 Last committed transaction             = 2010-08-04-23.18.19.000000 Local

 

DB20000I  The ROLLFORWARD command completed successfully.

db2 => select * from newtable

SQL1024N  A database connection does not exist.  SQLSTATE=08003

db2 => connect to sample

 

   Database Connection Information

 

 Database server        = DB2/LINUX 9.7.1

 SQL authorization ID   = DB2INST1

 Local database alias   = SAMPLE

 

db2 => select * from newtable

 

AA        

-----------

        123

        333

        444

        555

        777

 

  5 record(s) selected.

 

db2 =>

    小结：表空间的在线还原，可以在不断开已有数据库用户连接的情况下进行。且当我们没有表空间的备份文件的时候，可以选择一个合适的数据库的备份文件来进行还原。

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

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