在线备份数据库和在线备份表空间,应该是大多数生产环境下使用的备份方式。其最大的优点是,能够在不断开用户连接的情况下,对数据进行备份。
在还原的时候,若是针对数据库级的还原,则需要断开所有用户的连接;而表空间级别的还原,则可以在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/