学习笔记：8.备份恢复
前言：搞了10年的Oracle,最近开始学习DB2，从牛书《循序渐进DB2.DBA系统管理、运维与应用案例学习笔记》开始，将我学习过程中的测试命令及过程和大家共享，遇到的问题也如实写在上面，希望和大家一起学习！
大家也可以参考我的BLOG：http://www.db2china.net/home/space.php?uid=45420&do=blog，欢迎大家指正，谢谢！
DB2数据库备份恢复实战指南：
1、设置归档模式、路径并做首次备份处理
--修改LOGREATIN参数
[db2inst1@localhost ~]$ db2 update db cfg for mydb using LOGRETAIN RECOVERY trackmod on
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, all
applications must disconnect from this database before the changes become
effective.
--设置归档路径:目录必须存在并有读写功能
[db2inst1@localhost ~]$ db2 update db cfg for mydb using logarchmeth1 "disk:/home/db2inst1/arch/mydb"
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, all
applications must disconnect from this database before the changes become
effective.
[db2inst1@localhost ~]$ db2 connect reset
DB20000I The SQL command completed successfully.
--设置归档日志模式后需要进行首次全备份
[db2inst1@localhost ~]$ db2 connect to mydb
SQL1116N A connection to or activation of database "MYDB" cannot be made
because of BACKUP PENDING. SQLSTATE=57019
[db2inst1@localhost ~]$ db2 backup database mydb to /home/db2inst1/dbbak
Backup successful. The timestamp for this backup image is : 20120116205427
--查看备份文件
[db2inst1@localhost ~]$ ls /home/db2inst1/dbbak
MYDB.0.db2inst1.NODE0000.CATN0000.20120116205427.001
[db2inst1@localhost ~]$ db2 connect to mydb
Database Connection Information
Database server = DB2/LINUX 9.7.4
SQL authorization ID = DB2INST1
Local database alias = MYDB
--检查备份
[db2inst1@localhost ~]$ db2ckbkp -h dbbak/MYDB.0.db2inst1.NODE0000.CATN0000.20120116205427.001
=====================
MEDIA HEADER REACHED:
=====================
Server Database Name -- MYDB
Server Database Alias -- MYDB
Client Database Alias -- MYDB
Timestamp -- 20120116205427
Database Partition Number -- 0
Instance -- db2inst1
Sequence Number -- 1
Release ID -- D00
Database Seed -- 567A2ED8
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
Merged Backup Image -- 0
Status Flags -- 35
System Cats inc -- 1
Catalog Partition Number -- 0
DB Codeset -- GBK
DB Territory --
LogID -- 1325475591
LogPath -- /home/db2inst1/mydb/mydbpath1/db2inst1/NODE0000/SQL00001/SQLOGDIR/
Backup Buffer Size -- 4460544
Number of Sessions -- 1
Platform -- 12
The proper image file name would be:
MYDB.0.db2inst1.NODE0000.CATN0000.20120116205427.001
[1] Buffers processed: ########################
Image Verification Complete - successful.
--Execute Some DDL Operation
[db2inst1@localhost ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
TEST DB2INST1 T 2012-01-07-06.29.38.215898
TEST2 DB2INST1 T 2012-01-08-04.48.20.894317
2 record(s) selected.
-- 删除db2inst1.test2表
[db2inst1@localhost ~]$ db2 drop table test2
DB20000I The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
TEST DB2INST1 T 2012-01-07-06.29.38.215898
2. 使用 without rollingfoward 恢复数据库
[db2inst1@localhost ~]$ db2 "restore database mydb from "/home/db2inst1/dbbak" taken at 20120116205427 without rolling forward without prompting"
SQL2540W Restore is successful, however a warning "2539" was encountered
during Database Restore while processing in No Interrupt mode.
[db2inst1@localhost ~]$ db2 list tables
--Table has been restored!
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
TEST DB2INST1 T 2012-01-07-06.29.38.215898
TEST2 DB2INST1 T 2012-01-08-04.48.20.894317
2 record(s) selected.
3.恢复并前滚全部日志"rolling forward all logs"
[db2inst1@localhost ~]$ db2 "restore database mydb from "/home/db2inst1/dbbak" taken at 20120116205427"
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.
[db2inst1@localhost ~]$ db2 connect to mydb
SQL1117N A connection to or activation of database "MYDB" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
[db2inst1@localhost ~]$ db2 "rollforward database mydb to end of logs and complete"
Rollforward Status
Input database alias = mydb
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000000.LOG - S0000000.LOG
Last committed transaction = 2012-01-17-05.00.25.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[db2inst1@localhost ~]$ db2 connect to mydb
Database Connection Information
Database server = DB2/LINUX 9.7.4
SQL authorization ID = DB2INST1
Local database alias = MYDB
--完全恢复日志后TEST2被删除
[db2inst1@localhost ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
TEST DB2INST1 T 2012-01-07-06.29.38.215898
1 record(s) selected.
4、恢复后执行DML并重新restore,rolling forward
[db2inst1@localhost ~]$ db2 "insert into test values(111)"
DB20000I The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 "select * from test"
TID
-----------
111
1 record(s) selected.
[db2inst1@localhost ~]$ db2 "restore database mydb from "/home/db2inst1/dbbak" taken at 20120116205427"
SQL1350N The application is not in the correct state to process this request.
Reason code="1".
[db2inst1@localhost ~]$ db2 connect reset
DB20000I The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 "restore database mydb from "/home/db2inst1/dbbak" taken at 20120116205427"
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.
[db2inst1@localhost ~]$ db2 "rollforward database mydb to end of logs and complete"
Rollforward Status
Input database alias = mydb
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000000.LOG - S0000001.LOG
Last committed transaction = 2012-01-17-05.10.09.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[db2inst1@localhost ~]$ db2 connect to mydb
Database Connection Information
Database server = DB2/LINUX 9.7.4
SQL authorization ID = DB2INST1
Local database alias = MYDB
[db2inst1@localhost ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
TEST DB2INST1 T 2012-01-07-06.29.38.215898
1 record(s) selected.
[db2inst1@localhost ~]$ db2 "select * from test"
TID
-----------
111
1 record(s) selected.
--重新恢复后追加了所以的事务操作
5、rolling forward dropped table
[db2inst1@localhost ~]$ db2 "restore database mydb from "/home/db2inst1/dbbak" taken at 20120116205427"
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.
[db2inst1@localhost ~]$ db2 connect to mydb
SQL1117N A connection to or activation of database "MYDB" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
[db2inst1@localhost ~]$ db2 list history dropped table all for database mydb
List History File for mydb
Number of matching file entries = 1
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
D T 20120116205924 000000000000834a00020007
----------------------------------------------------------------------------
"DB2INST1"."TEST2" resides in 1 tablespace(s):
00001 USERSPACE1
----------------------------------------------------------------------------
Comment: DROP TABLE
Start Time: 20120116205924
End Time: 20120116205924
Status: A
----------------------------------------------------------------------------
EID: 60
DDL: CREATE TABLE "DB2INST1"."TEST2" ( "TID" INTEGER ) IN "USERSPACE1" ;
----------------------------------------------------------------------------
[db2inst1@localhost ~]$ mkdir -p /home/db2inst1/dbbak/exptab1
[db2inst1@localhost ~]$ db2 "rollforward database mydb to end of logs and stop recover dropped table 000000000000834a00020007 to /home/db2inst1/dbbak/exptab1"
Rollforward Status
Input database alias = mydb
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000000.LOG - S0000001.LOG
Last committed transaction = 2012-01-17-05.10.09.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[db2inst1@localhost ~]$ ls -l /home/db2inst1/dbbak/exptab1/NODE0000/data
-rw-r----- 1 db2inst1 db2inst1 0 Jan 16 21:17 /home/db2inst1/dbbak/exptab1/NODE0000/data
[db2inst1@localhost ~]$
--执行History 的DDL语句重建被删除的TABLE
[db2inst1@localhost ~]$ db2 "CREATE TABLE "DB2INST1"."TEST2" ( "TID" INTEGER ) IN "USERSPACE1" "
DB20000I The SQL command completed successfully.
--导出被删除的表的数据
[db2inst1@localhost ~]$ db2 "import from /home/db2inst1/dbbak/exptab1/NODE0000/data of DEL insert into db2inst1.test2"
SQL3109N The utility is beginning to load data from file
"/home/db2inst1/dbbak/exptab1/NODE0000/data".
SQL3110N The utility has completed processing. "0" rows were read from the
input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "0".
SQL3222W ...COMMIT of any database changes was successful.
SQL3149N "0" rows were processed from the input file. "0" rows were
successfully inserted into the table. "0" rows were rejected.
Number of rows read = 0
Number of rows skipped = 0
Number of rows inserted = 0
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 0
[db2inst1@localhost ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
TEST DB2INST1 T 2012-01-07-06.29.38.215898
TEST2 DB2INST1 T 2012-01-16-21.19.06.130347
--查看备份恢复信息：
[db2inst1@localhost ~]$ db2 list history backup all for mydb
List History File for mydb
Number of matching file entries = 11
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20120116205427001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 6 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 SMSSPACE1
00005 DMSSPACE1
00006 SMS8K
----------------------------------------------------------------------------
Comment: DB2 BACKUP MYDB OFFLINE
Start Time: 20120116205427
End Time: 20120116205446
Status: A
----------------------------------------------------------------------------
EID: 59 Location: /home/db2inst1/dbbak
6、使用recover database还原并前滚数据库：
[db2inst1@localhost ~]$ db2 "recover database mydb to 2012-01-16-21.00.27 using local time"
Rollforward Status
Input database alias = mydb
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000000.LOG - S0000001.LOG
Last committed transaction = 2012-01-16-21.00.25.000000 Local
DB20000I The RECOVER DATABASE command completed successfully.
[db2inst1@localhost ~]$ db2 connect to mydb
Database Connection Information
Database server = DB2/LINUX 9.7.4
SQL authorization ID = DB2INST1
Local database alias = MYDB
[db2inst1@localhost ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
TEST DB2INST1 T 2012-01-07-06.29.38.215898
7、根据部分表空间备份恢复数据库：
INSERT INTO db2inst1.test VALUES(99);
db2inst1@localhost ~]$ db2 "SELECT substr(NAME,1,10) name ,substr(creator,1,10) creator,substr(TBSPACE,1,10) tbspace FROM sysibm.systables WHERE NAME='TEST'"
NAME CREATOR TBSPACE
---------- ---------- ----------
TEST DB2FENC1 USERSPACE1
TEST DB2INST1 SMSSPACE1
TEST DBTEST USERSPACE1
TEST DBTEST2 USERSPACE1
4 record(s) selected.
db2 "backup database mydb tablespace(syscatspace,userspace1) online to /home/db2inst1/dbbak/mydb"
[db2inst1@localhost ~]$ db2 "SELECT * from dbtest.test"
TID
-----------
1
2
2 record(s) selected.
[db2inst1@localhost ~]$ db2 "SELECT * from db2inst1.test"
TID
-----------
99
1 record(s) selected.
db2 "backup database mydb tablespace(syscatspace,userspace1) online to /home/db2inst1/dbbak/mydb"
db2 "backup database mydb tablespace(smsspace1,sms8k,dmsspace1) online to /home/db2inst1/dbbak/mydb"
[db2inst1@localhost ~]$ db2 list history backup all for mydb
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B P 20120116232124000 N S0000002.LOG
----------------------------------------------------------------------------
Contains 2 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
----------------------------------------------------------------------------
Comment: DB2 BACKUP MYDB ONLINE
Start Time: 20120116232124
End Time: 20120116232125
Status: A
----------------------------------------------------------------------------
EID: 92 Location:
SQLCA Information
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -2036 sqlerrml: 30
sqlerrmc: DISK:/home/db2inst1/dbbak/mydb
sqlerrp : sqlubMWR
sqlerrd : (1) 0 (2) 0 (3) 0
(4) 0 (5) 0 (6) 0
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B P 20120116232213001 N D S0000002.LOG S0000002.LOG
----------------------------------------------------------------------------
Contains 2 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
----------------------------------------------------------------------------
Comment: DB2 BACKUP MYDB ONLINE
Start Time: 20120116232213
End Time: 20120116232227
Status: A
----------------------------------------------------------------------------
EID: 93 Location: /home/db2inst1/dbbak/mydb
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B P 20120116232649001 N D S0000003.LOG S0000003.LOG
----------------------------------------------------------------------------
Contains 3 tablespace(s):
00001 SMSSPACE1
00002 SMS8K
00003 DMSSPACE1
----------------------------------------------------------------------------
Comment: DB2 BACKUP MYDB ONLINE
Start Time: 20120116232649
End Time: 20120116232650
Status: A
----------------------------------------------------------------------------
EID: 95 Location: /home/db2inst1/dbbak/mydb
[db2inst1@localhost ~]$ ls -l /home/db2inst1/dbbak/mydb/
total 109820
-rw------- 1 db2inst1 db2inst1 99717120 Jan 16 23:22 MYDB.3.db2inst1.NODE0000.CATN0000.20120116232213.001
-rw------- 1 db2inst1 db2inst1 12619776 Jan 16 23:26 MYDB.3.db2inst1.NODE0000.CATN0000.20120116232649.001
[db2inst1@localhost ~]$ db2ckbkp -h /home/db2inst1/dbbak/MYDB.0.db2inst1.NODE0000.CATN0000.20120116205427.001
=====================
MEDIA HEADER REACHED:
=====================
Server Database Name -- MYDB
Server Database Alias -- MYDB
Client Database Alias -- MYDB
Timestamp -- 20120116205427
Database Partition Number -- 0
Instance -- db2inst1
Sequence Number -- 1
Release ID -- D00
Database Seed -- 567A2ED8
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
Merged Backup Image -- 0
Status Flags -- 35
System Cats inc -- 1
Catalog Partition Number -- 0
DB Codeset -- GBK
DB Territory --
LogID -- 1325475591
LogPath -- /home/db2inst1/mydb/mydbpath1/db2inst1/NODE0000/SQL00001/SQLOGDIR/
Backup Buffer Size -- 4460544
Number of Sessions -- 1
Platform -- 12
The proper image file name would be:
MYDB.0.db2inst1.NODE0000.CATN0000.20120116205427.001
[1] Buffers processed: ########################
Image Verification Complete - successful.
--List tablespace containers
[db2inst1@localhost ~]$ db2 list tablespace containers for 3
Tablespace Containers for Tablespace 3
Container ID = 0
Name = /home/db2inst1/mydb/mydbpath1/db2inst1/NODE0000/MYDB/T0000003/C0000000.LRG
Type = File
Container ID = 1
Name = /home/db2inst1/mydb/mydbpath2/db2inst1/NODE0000/MYDB/T0000003/C0000001.LRG
Type = File
[db2inst1@localhost ~]$ db2 list tablespace containers for 4
Tablespace Containers for Tablespace 4
Container ID = 0
Name = /home/db2inst1/mydb/syspath
Type = Path
[db2inst1@localhost ~]$ db2 list tablespace containers for 5
Tablespace Containers for Tablespace 5
Container ID = 0
Name = /home/db2inst1/mydb/dmspath1/dms2.dat
Type = File
[db2inst1@localhost ~]$ cd /home/db2inst1
[db2inst1@localhost ~]$ cd mydb
[db2inst1@localhost mydb]$ ls -l
total 32
drwx--x--x 2 db2inst1 db2inst1 4096 Jan 4 02:22 dmspath1
drwxrwxr-x 3 db2inst1 db2inst1 4096 Jan 1 19:39 mydbpath1
drwxrwxr-x 3 db2inst1 db2inst1 4096 Jan 1 19:39 mydbpath2
drwx------ 2 db2inst1 db2inst1 4096 Jan 16 22:02 syspath
[db2inst1@localhost mydb]$ db2stop
01/16/2012 23:54:52 0 0 SQL1025N The database manager was not stopped because databases are still active.
SQL1025N The database manager was not stopped because databases are still active.
[db2inst1@localhost mydb]$ db2stop force
01/16/2012 23:55:00 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
--simulate the database corruption
[db2inst1@localhost mydb]$ mv * /tmp
[db2inst1@localhost mydb]$ db2start
01/16/2012 23:56:14 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[db2inst1@localhost mydb]$ db2 connect to mydb
SQL1031N The database directory cannot be found on the indicated file system.
SQLSTATE=58031
--use part of tablespaces backup to restore the database
--尝试UNCATALOG / CATALOG
[db2inst1@localhost ~]$ db2 "catalog database mydb on /home/db2inst1/mydb/mydbpath1"
DB20000I The CATALOG DATABASE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
[db2inst1@localhost ~]$ db2 connect to mydb
Database Connection Information
Database server = DB2/LINUX 9.7.4
SQL authorization ID = DB2INST1
Local database alias = MYDB
[db2inst1@localhost ~]$ db2 list database directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = MYDB
Database name = MYDB
Local database directory = /home/db2inst1/mydb/mydbpath1
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
[db2inst1@localhost ~]$ db2 "uncatalog database mydb"
DB20000I The UNCATALOG DATABASE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
[db2inst1@localhost ~]$ db2 terminate
DB20000I The TERMINATE command completed successfully.
[db2inst1@localhost ~]$ db2 connect to mydb
SQL1013N The database alias name or database name "MYDB" could not be found.
SQLSTATE=42705
[db2inst1@localhost ~]$ db2 "catalog database mydb on /home/db2inst1/mydb/mydbpath1"
DB20000I The CATALOG DATABASE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
[db2inst1@localhost ~]$ db2 connect to mydb
Database Connection Information
Database server = DB2/LINUX 9.7.4
SQL authorization ID = DB2INST1
Local database alias = MYDB
8、restore rebuild database
(1)、drop the database which need rebuilded
db2 "drop database mydb"
--否则报错：
.[db2inst1@localhost mydb]$ db2 "restore database mydb rebuild with tablespace(syscatspace,userspace1) taken at 20120116232213"
SQL1005N The database alias "MYDB" already exists in either the local
database directory or system database directory. SQLSTATE=00000、
--和牛新庄书中的说法不同，restore rebuild database不允许存在当前数据库！
(2)、restore history file from backup files(恢复历史日志文件)
--否则报错：
[db2inst1@localhost ~]$ db2 "restore db mydb rebuild with tablespace (syscatspace,userspace1) from /home/db2inst1/dbbak/mydb taken at 20120116232649"
SQL2549N The database was not restored because either all of the table spaces
in the backup image are inaccessible, or one or more table space names in list
of table space names to restore are invalid.
[db2inst1@localhost ~]$ db2 "restore db mydb history file from /home/db2inst1/dbbak/mydb taken at 20120116232649"
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@localhost ~]$ db2 list history backup all for mydb
List History File for mydb
Number of matching file entries = 19
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20120116205427001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 6 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 SMSSPACE1
...
(3)、restore db
[db2inst1@localhost ~]$ db2 "restore db mydb rebuild with tablespace (syscatspace,userspace1) from /home/db2inst1/dbbak/mydb taken at 20120116232213"
SQL2561W Warning! Rebuilding a database from a table space image or using a
subset of table spaces. The target database will be overwritten. The restore
utility also reports the following sqlcode "2523".
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@localhost ~]$ db2 list history backup all for mydb
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
R D 20120117011641001 F 20120116232213
----------------------------------------------------------------------------
Contains 2 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
----------------------------------------------------------------------------
Comment: RESTORE MYDB WITH RF
Start Time: 20120117011641
End Time: 20120117011700
Status: A
----------------------------------------------------------------------------
EID: 100 Location:
[db2inst1@localhost ~]$ db2 connect to mydb
SQL1117N A connection to or activation of database "MYDB" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
(4)、rolling forward
[db2inst1@localhost ~]$ db2 "rollforward db mydb to end of logs"
SQL1271W Database "MYDB" is recovered but one or more table spaces are
off-line on node(s) "0".
(5)、rolling forward stop
[db2inst1@localhost ~]$ db2 connect to mydb
SQL1117N A connection to or activation of database "MYDB" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
[db2inst1@localhost ~]$ db2 "rollforward db mydb stop"
SQL1271W Database "MYDB" is recovered but one or more table spaces are
off-line on node(s) "0".
[db2inst1@localhost ~]$ db2 connect to mydb
Database Connection Information
Database server = DB2/LINUX 9.7.4
SQL authorization ID = DB2INST1
Local database alias = MYDB
[db2inst1@localhost ~]$ 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
Detailed explanation:
Normal
Tablespace ID = 3
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0100
Detailed explanation:
Restore pending
Tablespace ID = 4
Name = SMSSPACE1
Type = System managed space
Contents = All permanent data. Regular table space.
State = 0x0100
Detailed explanation:
Restore pending
Tablespace ID = 5
Name = DMSSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0100
Detailed explanation:
Restore pending
--除表空间SYSCATSPACE,USERSPACE1,TEMPSPACE1外全部为0x0100状态,因为本次恢复只包含SYSCATSPACE,USERSPACE1表空间，TEMPSPACE1估计是自动重建的？
--状态异常的数据库不能执行备份操作
[db2inst1@localhost ~]$ db2 "backup database mydb tablespace (sms8k)"
SQL2048N An error occurred while accessing object "6". Reason code: "6".
(6)从后续表空间备份中恢复其他备份表空间
[db2inst1@localhost ~]$ db2 "restore database mydb tablespace ( SMSSPACE1,DMSSPACE1,SMS8K) from /home/db2inst1/dbbak/mydb taken at 20120116232649"
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@localhost ~]$ db2 "restore database mydb tablespace (SYSTOOLSPACE) from /home/db2inst1/dbbak taken at 20120116205427 "
DB20000I The RESTORE DATABASE command completed successfully.
db2inst1@localhost ~]$ db2 connect to mydb
Database Connection Information
Database server = DB2/LINUX 9.7.4
SQL authorization ID = DB2INST1
Local database alias = MYDB
[db2inst1@localhost ~]$ 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
Detailed explanation:
Normal
Tablespace ID = 3
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0100
Detailed explanation:
Restore pending
Tablespace ID = 4
Name = SMSSPACE1
Type = System managed space
Contents = All permanent data. Regular table space.
State = 0x0080
-----------0x0080:需要roll forward处理------------------
(7) ROLL FORWARD DATABASE
[db2inst1@localhost ~]$ db2 "rollforward db mydb to end of logs"
Rollforward Status
Input database alias = mydb
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 = 2012-01-16-23.44.03.000000 Local
DB20000I The ROLLFORWARD command completed successfully.
[db2inst1@localhost ~]$ db2 connect to mydb
Database Connection Information
Database server = DB2/LINUX 9.7.4
SQL authorization ID = DB2INST1
Local database alias = MYDB
[db2inst1@localhost ~]$ 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
Detailed explanation:
Normal
Tablespace ID = 3
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 4
Name = SMSSPACE1
Type = System managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
--
[db2inst1@localhost ~]$ db2 "select * from dbtest.test"
TID
-----------
1
2
2 record(s) selected.
[db2inst1@localhost ~]$ db2 "select * from dbtest2.test"
TID
-----------
0 record(s) selected.
[db2inst1@localhost ~]$ db2 "select * from db2inst1.test"
TID
-----------
99
1 record(s) selected.
(10) BACKUP DATABASE AGAIN
大家也可以参考我的BLOG：http://www.db2china.net/home/space.php?uid=45420&do=blog，欢迎大家指正，谢谢！
DB2数据库备份恢复实战指南：
1、设置归档模式、路径并做首次备份处理
--修改LOGREATIN参数
[db2inst1@localhost ~]$ db2 update db cfg for mydb using LOGRETAIN RECOVERY trackmod on
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, all
applications must disconnect from this database before the changes become
effective.
--设置归档路径:目录必须存在并有读写功能
[db2inst1@localhost ~]$ db2 update db cfg for mydb using logarchmeth1 "disk:/home/db2inst1/arch/mydb"
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, all
applications must disconnect from this database before the changes become
effective.
[db2inst1@localhost ~]$ db2 connect reset
DB20000I The SQL command completed successfully.
--设置归档日志模式后需要进行首次全备份
[db2inst1@localhost ~]$ db2 connect to mydb
SQL1116N A connection to or activation of database "MYDB" cannot be made
because of BACKUP PENDING. SQLSTATE=57019
[db2inst1@localhost ~]$ db2 backup database mydb to /home/db2inst1/dbbak
Backup successful. The timestamp for this backup image is : 20120116205427
--查看备份文件
[db2inst1@localhost ~]$ ls /home/db2inst1/dbbak
MYDB.0.db2inst1.NODE0000.CATN0000.20120116205427.001
[db2inst1@localhost ~]$ db2 connect to mydb
Database Connection Information
Database server = DB2/LINUX 9.7.4
SQL authorization ID = DB2INST1
Local database alias = MYDB
--检查备份
[db2inst1@localhost ~]$ db2ckbkp -h dbbak/MYDB.0.db2inst1.NODE0000.CATN0000.20120116205427.001
=====================
MEDIA HEADER REACHED:
=====================
Server Database Name -- MYDB
Server Database Alias -- MYDB
Client Database Alias -- MYDB
Timestamp -- 20120116205427
Database Partition Number -- 0
Instance -- db2inst1
Sequence Number -- 1
Release ID -- D00
Database Seed -- 567A2ED8
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
Merged Backup Image -- 0
Status Flags -- 35
System Cats inc -- 1
Catalog Partition Number -- 0
DB Codeset -- GBK
DB Territory --
LogID -- 1325475591
LogPath -- /home/db2inst1/mydb/mydbpath1/db2inst1/NODE0000/SQL00001/SQLOGDIR/
Backup Buffer Size -- 4460544
Number of Sessions -- 1
Platform -- 12
The proper image file name would be:
MYDB.0.db2inst1.NODE0000.CATN0000.20120116205427.001
[1] Buffers processed: ########################
Image Verification Complete - successful.
--Execute Some DDL Operation
[db2inst1@localhost ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
TEST DB2INST1 T 2012-01-07-06.29.38.215898
TEST2 DB2INST1 T 2012-01-08-04.48.20.894317
2 record(s) selected.
-- 删除db2inst1.test2表
[db2inst1@localhost ~]$ db2 drop table test2
DB20000I The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
TEST DB2INST1 T 2012-01-07-06.29.38.215898
2. 使用 without rollingfoward 恢复数据库
[db2inst1@localhost ~]$ db2 "restore database mydb from "/home/db2inst1/dbbak" taken at 20120116205427 without rolling forward without prompting"
SQL2540W Restore is successful, however a warning "2539" was encountered
during Database Restore while processing in No Interrupt mode.
[db2inst1@localhost ~]$ db2 list tables
--Table has been restored!
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
TEST DB2INST1 T 2012-01-07-06.29.38.215898
TEST2 DB2INST1 T 2012-01-08-04.48.20.894317
2 record(s) selected.
3.恢复并前滚全部日志"rolling forward all logs"
[db2inst1@localhost ~]$ db2 "restore database mydb from "/home/db2inst1/dbbak" taken at 20120116205427"
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.
[db2inst1@localhost ~]$ db2 connect to mydb
SQL1117N A connection to or activation of database "MYDB" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
[db2inst1@localhost ~]$ db2 "rollforward database mydb to end of logs and complete"
Rollforward Status
Input database alias = mydb
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000000.LOG - S0000000.LOG
Last committed transaction = 2012-01-17-05.00.25.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[db2inst1@localhost ~]$ db2 connect to mydb
Database Connection Information
Database server = DB2/LINUX 9.7.4
SQL authorization ID = DB2INST1
Local database alias = MYDB
--完全恢复日志后TEST2被删除
[db2inst1@localhost ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
TEST DB2INST1 T 2012-01-07-06.29.38.215898
1 record(s) selected.
4、恢复后执行DML并重新restore,rolling forward
[db2inst1@localhost ~]$ db2 "insert into test values(111)"
DB20000I The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 "select * from test"
TID
-----------
111
1 record(s) selected.
[db2inst1@localhost ~]$ db2 "restore database mydb from "/home/db2inst1/dbbak" taken at 20120116205427"
SQL1350N The application is not in the correct state to process this request.
Reason code="1".
[db2inst1@localhost ~]$ db2 connect reset
DB20000I The SQL command completed successfully.
[db2inst1@localhost ~]$ db2 "restore database mydb from "/home/db2inst1/dbbak" taken at 20120116205427"
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.
[db2inst1@localhost ~]$ db2 "rollforward database mydb to end of logs and complete"
Rollforward Status
Input database alias = mydb
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000000.LOG - S0000001.LOG
Last committed transaction = 2012-01-17-05.10.09.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[db2inst1@localhost ~]$ db2 connect to mydb
Database Connection Information
Database server = DB2/LINUX 9.7.4
SQL authorization ID = DB2INST1
Local database alias = MYDB
[db2inst1@localhost ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
TEST DB2INST1 T 2012-01-07-06.29.38.215898
1 record(s) selected.
[db2inst1@localhost ~]$ db2 "select * from test"
TID
-----------
111
1 record(s) selected.
--重新恢复后追加了所以的事务操作
5、rolling forward dropped table
[db2inst1@localhost ~]$ db2 "restore database mydb from "/home/db2inst1/dbbak" taken at 20120116205427"
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.
[db2inst1@localhost ~]$ db2 connect to mydb
SQL1117N A connection to or activation of database "MYDB" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
[db2inst1@localhost ~]$ db2 list history dropped table all for database mydb
List History File for mydb
Number of matching file entries = 1
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
D T 20120116205924 000000000000834a00020007
----------------------------------------------------------------------------
"DB2INST1"."TEST2" resides in 1 tablespace(s):
00001 USERSPACE1
----------------------------------------------------------------------------
Comment: DROP TABLE
Start Time: 20120116205924
End Time: 20120116205924
Status: A
----------------------------------------------------------------------------
EID: 60
DDL: CREATE TABLE "DB2INST1"."TEST2" ( "TID" INTEGER ) IN "USERSPACE1" ;
----------------------------------------------------------------------------
[db2inst1@localhost ~]$ mkdir -p /home/db2inst1/dbbak/exptab1
[db2inst1@localhost ~]$ db2 "rollforward database mydb to end of logs and stop recover dropped table 000000000000834a00020007 to /home/db2inst1/dbbak/exptab1"
Rollforward Status
Input database alias = mydb
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000000.LOG - S0000001.LOG
Last committed transaction = 2012-01-17-05.10.09.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[db2inst1@localhost ~]$ ls -l /home/db2inst1/dbbak/exptab1/NODE0000/data
-rw-r----- 1 db2inst1 db2inst1 0 Jan 16 21:17 /home/db2inst1/dbbak/exptab1/NODE0000/data
[db2inst1@localhost ~]$
--执行History 的DDL语句重建被删除的TABLE
[db2inst1@localhost ~]$ db2 "CREATE TABLE "DB2INST1"."TEST2" ( "TID" INTEGER ) IN "USERSPACE1" "
DB20000I The SQL command completed successfully.
--导出被删除的表的数据
[db2inst1@localhost ~]$ db2 "import from /home/db2inst1/dbbak/exptab1/NODE0000/data of DEL insert into db2inst1.test2"
SQL3109N The utility is beginning to load data from file
"/home/db2inst1/dbbak/exptab1/NODE0000/data".
SQL3110N The utility has completed processing. "0" rows were read from the
input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "0".
SQL3222W ...COMMIT of any database changes was successful.
SQL3149N "0" rows were processed from the input file. "0" rows were
successfully inserted into the table. "0" rows were rejected.
Number of rows read = 0
Number of rows skipped = 0
Number of rows inserted = 0
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 0
[db2inst1@localhost ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
TEST DB2INST1 T 2012-01-07-06.29.38.215898
TEST2 DB2INST1 T 2012-01-16-21.19.06.130347
--查看备份恢复信息：
[db2inst1@localhost ~]$ db2 list history backup all for mydb
List History File for mydb
Number of matching file entries = 11
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20120116205427001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 6 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 SMSSPACE1
00005 DMSSPACE1
00006 SMS8K
----------------------------------------------------------------------------
Comment: DB2 BACKUP MYDB OFFLINE
Start Time: 20120116205427
End Time: 20120116205446
Status: A
----------------------------------------------------------------------------
EID: 59 Location: /home/db2inst1/dbbak
6、使用recover database还原并前滚数据库：
[db2inst1@localhost ~]$ db2 "recover database mydb to 2012-01-16-21.00.27 using local time"
Rollforward Status
Input database alias = mydb
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000000.LOG - S0000001.LOG
Last committed transaction = 2012-01-16-21.00.25.000000 Local
DB20000I The RECOVER DATABASE command completed successfully.
[db2inst1@localhost ~]$ db2 connect to mydb
Database Connection Information
Database server = DB2/LINUX 9.7.4
SQL authorization ID = DB2INST1
Local database alias = MYDB
[db2inst1@localhost ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
TEST DB2INST1 T 2012-01-07-06.29.38.215898
7、根据部分表空间备份恢复数据库：
INSERT INTO db2inst1.test VALUES(99);
db2inst1@localhost ~]$ db2 "SELECT substr(NAME,1,10) name ,substr(creator,1,10) creator,substr(TBSPACE,1,10) tbspace FROM sysibm.systables WHERE NAME='TEST'"
NAME CREATOR TBSPACE
---------- ---------- ----------
TEST DB2FENC1 USERSPACE1
TEST DB2INST1 SMSSPACE1
TEST DBTEST USERSPACE1
TEST DBTEST2 USERSPACE1
4 record(s) selected.
db2 "backup database mydb tablespace(syscatspace,userspace1) online to /home/db2inst1/dbbak/mydb"
[db2inst1@localhost ~]$ db2 "SELECT * from dbtest.test"
TID
-----------
1
2
2 record(s) selected.
[db2inst1@localhost ~]$ db2 "SELECT * from db2inst1.test"
TID
-----------
99
1 record(s) selected.
db2 "backup database mydb tablespace(syscatspace,userspace1) online to /home/db2inst1/dbbak/mydb"
db2 "backup database mydb tablespace(smsspace1,sms8k,dmsspace1) online to /home/db2inst1/dbbak/mydb"
[db2inst1@localhost ~]$ db2 list history backup all for mydb
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B P 20120116232124000 N S0000002.LOG
----------------------------------------------------------------------------
Contains 2 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
----------------------------------------------------------------------------
Comment: DB2 BACKUP MYDB ONLINE
Start Time: 20120116232124
End Time: 20120116232125
Status: A
----------------------------------------------------------------------------
EID: 92 Location:
SQLCA Information
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -2036 sqlerrml: 30
sqlerrmc: DISK:/home/db2inst1/dbbak/mydb
sqlerrp : sqlubMWR
sqlerrd : (1) 0 (2) 0 (3) 0
(4) 0 (5) 0 (6) 0
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B P 20120116232213001 N D S0000002.LOG S0000002.LOG
----------------------------------------------------------------------------
Contains 2 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
----------------------------------------------------------------------------
Comment: DB2 BACKUP MYDB ONLINE
Start Time: 20120116232213
End Time: 20120116232227
Status: A
----------------------------------------------------------------------------
EID: 93 Location: /home/db2inst1/dbbak/mydb
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B P 20120116232649001 N D S0000003.LOG S0000003.LOG
----------------------------------------------------------------------------
Contains 3 tablespace(s):
00001 SMSSPACE1
00002 SMS8K
00003 DMSSPACE1
----------------------------------------------------------------------------
Comment: DB2 BACKUP MYDB ONLINE
Start Time: 20120116232649
End Time: 20120116232650
Status: A
----------------------------------------------------------------------------
EID: 95 Location: /home/db2inst1/dbbak/mydb
[db2inst1@localhost ~]$ ls -l /home/db2inst1/dbbak/mydb/
total 109820
-rw------- 1 db2inst1 db2inst1 99717120 Jan 16 23:22 MYDB.3.db2inst1.NODE0000.CATN0000.20120116232213.001
-rw------- 1 db2inst1 db2inst1 12619776 Jan 16 23:26 MYDB.3.db2inst1.NODE0000.CATN0000.20120116232649.001
[db2inst1@localhost ~]$ db2ckbkp -h /home/db2inst1/dbbak/MYDB.0.db2inst1.NODE0000.CATN0000.20120116205427.001
=====================
MEDIA HEADER REACHED:
=====================
Server Database Name -- MYDB
Server Database Alias -- MYDB
Client Database Alias -- MYDB
Timestamp -- 20120116205427
Database Partition Number -- 0
Instance -- db2inst1
Sequence Number -- 1
Release ID -- D00
Database Seed -- 567A2ED8
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
Merged Backup Image -- 0
Status Flags -- 35
System Cats inc -- 1
Catalog Partition Number -- 0
DB Codeset -- GBK
DB Territory --
LogID -- 1325475591
LogPath -- /home/db2inst1/mydb/mydbpath1/db2inst1/NODE0000/SQL00001/SQLOGDIR/
Backup Buffer Size -- 4460544
Number of Sessions -- 1
Platform -- 12
The proper image file name would be:
MYDB.0.db2inst1.NODE0000.CATN0000.20120116205427.001
[1] Buffers processed: ########################
Image Verification Complete - successful.
--List tablespace containers
[db2inst1@localhost ~]$ db2 list tablespace containers for 3
Tablespace Containers for Tablespace 3
Container ID = 0
Name = /home/db2inst1/mydb/mydbpath1/db2inst1/NODE0000/MYDB/T0000003/C0000000.LRG
Type = File
Container ID = 1
Name = /home/db2inst1/mydb/mydbpath2/db2inst1/NODE0000/MYDB/T0000003/C0000001.LRG
Type = File
[db2inst1@localhost ~]$ db2 list tablespace containers for 4
Tablespace Containers for Tablespace 4
Container ID = 0
Name = /home/db2inst1/mydb/syspath
Type = Path
[db2inst1@localhost ~]$ db2 list tablespace containers for 5
Tablespace Containers for Tablespace 5
Container ID = 0
Name = /home/db2inst1/mydb/dmspath1/dms2.dat
Type = File
[db2inst1@localhost ~]$ cd /home/db2inst1
[db2inst1@localhost ~]$ cd mydb
[db2inst1@localhost mydb]$ ls -l
total 32
drwx--x--x 2 db2inst1 db2inst1 4096 Jan 4 02:22 dmspath1
drwxrwxr-x 3 db2inst1 db2inst1 4096 Jan 1 19:39 mydbpath1
drwxrwxr-x 3 db2inst1 db2inst1 4096 Jan 1 19:39 mydbpath2
drwx------ 2 db2inst1 db2inst1 4096 Jan 16 22:02 syspath
[db2inst1@localhost mydb]$ db2stop
01/16/2012 23:54:52 0 0 SQL1025N The database manager was not stopped because databases are still active.
SQL1025N The database manager was not stopped because databases are still active.
[db2inst1@localhost mydb]$ db2stop force
01/16/2012 23:55:00 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
--simulate the database corruption
[db2inst1@localhost mydb]$ mv * /tmp
[db2inst1@localhost mydb]$ db2start
01/16/2012 23:56:14 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[db2inst1@localhost mydb]$ db2 connect to mydb
SQL1031N The database directory cannot be found on the indicated file system.
SQLSTATE=58031
--use part of tablespaces backup to restore the database
--尝试UNCATALOG / CATALOG
[db2inst1@localhost ~]$ db2 "catalog database mydb on /home/db2inst1/mydb/mydbpath1"
DB20000I The CATALOG DATABASE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
[db2inst1@localhost ~]$ db2 connect to mydb
Database Connection Information
Database server = DB2/LINUX 9.7.4
SQL authorization ID = DB2INST1
Local database alias = MYDB
[db2inst1@localhost ~]$ db2 list database directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = MYDB
Database name = MYDB
Local database directory = /home/db2inst1/mydb/mydbpath1
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
[db2inst1@localhost ~]$ db2 "uncatalog database mydb"
DB20000I The UNCATALOG DATABASE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
[db2inst1@localhost ~]$ db2 terminate
DB20000I The TERMINATE command completed successfully.
[db2inst1@localhost ~]$ db2 connect to mydb
SQL1013N The database alias name or database name "MYDB" could not be found.
SQLSTATE=42705
[db2inst1@localhost ~]$ db2 "catalog database mydb on /home/db2inst1/mydb/mydbpath1"
DB20000I The CATALOG DATABASE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
[db2inst1@localhost ~]$ db2 connect to mydb
Database Connection Information
Database server = DB2/LINUX 9.7.4
SQL authorization ID = DB2INST1
Local database alias = MYDB
8、restore rebuild database
(1)、drop the database which need rebuilded
db2 "drop database mydb"
--否则报错：
.[db2inst1@localhost mydb]$ db2 "restore database mydb rebuild with tablespace(syscatspace,userspace1) taken at 20120116232213"
SQL1005N The database alias "MYDB" already exists in either the local
database directory or system database directory. SQLSTATE=00000、
--和牛新庄书中的说法不同，restore rebuild database不允许存在当前数据库！
(2)、restore history file from backup files(恢复历史日志文件)
--否则报错：
[db2inst1@localhost ~]$ db2 "restore db mydb rebuild with tablespace (syscatspace,userspace1) from /home/db2inst1/dbbak/mydb taken at 20120116232649"
SQL2549N The database was not restored because either all of the table spaces
in the backup image are inaccessible, or one or more table space names in list
of table space names to restore are invalid.
[db2inst1@localhost ~]$ db2 "restore db mydb history file from /home/db2inst1/dbbak/mydb taken at 20120116232649"
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@localhost ~]$ db2 list history backup all for mydb
List History File for mydb
Number of matching file entries = 19
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20120116205427001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 6 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 SMSSPACE1
...
(3)、restore db
[db2inst1@localhost ~]$ db2 "restore db mydb rebuild with tablespace (syscatspace,userspace1) from /home/db2inst1/dbbak/mydb taken at 20120116232213"
SQL2561W Warning! Rebuilding a database from a table space image or using a
subset of table spaces. The target database will be overwritten. The restore
utility also reports the following sqlcode "2523".
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@localhost ~]$ db2 list history backup all for mydb
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
R D 20120117011641001 F 20120116232213
----------------------------------------------------------------------------
Contains 2 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
----------------------------------------------------------------------------
Comment: RESTORE MYDB WITH RF
Start Time: 20120117011641
End Time: 20120117011700
Status: A
----------------------------------------------------------------------------
EID: 100 Location:
[db2inst1@localhost ~]$ db2 connect to mydb
SQL1117N A connection to or activation of database "MYDB" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
(4)、rolling forward
[db2inst1@localhost ~]$ db2 "rollforward db mydb to end of logs"
SQL1271W Database "MYDB" is recovered but one or more table spaces are
off-line on node(s) "0".
(5)、rolling forward stop
[db2inst1@localhost ~]$ db2 connect to mydb
SQL1117N A connection to or activation of database "MYDB" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
[db2inst1@localhost ~]$ db2 "rollforward db mydb stop"
SQL1271W Database "MYDB" is recovered but one or more table spaces are
off-line on node(s) "0".
[db2inst1@localhost ~]$ db2 connect to mydb
Database Connection Information
Database server = DB2/LINUX 9.7.4
SQL authorization ID = DB2INST1
Local database alias = MYDB
[db2inst1@localhost ~]$ 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
Detailed explanation:
Normal
Tablespace ID = 3
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0100
Detailed explanation:
Restore pending
Tablespace ID = 4
Name = SMSSPACE1
Type = System managed space
Contents = All permanent data. Regular table space.
State = 0x0100
Detailed explanation:
Restore pending
Tablespace ID = 5
Name = DMSSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0100
Detailed explanation:
Restore pending
--除表空间SYSCATSPACE,USERSPACE1,TEMPSPACE1外全部为0x0100状态,因为本次恢复只包含SYSCATSPACE,USERSPACE1表空间，TEMPSPACE1估计是自动重建的？
--状态异常的数据库不能执行备份操作
[db2inst1@localhost ~]$ db2 "backup database mydb tablespace (sms8k)"
SQL2048N An error occurred while accessing object "6". Reason code: "6".
(6)从后续表空间备份中恢复其他备份表空间
[db2inst1@localhost ~]$ db2 "restore database mydb tablespace ( SMSSPACE1,DMSSPACE1,SMS8K) from /home/db2inst1/dbbak/mydb taken at 20120116232649"
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@localhost ~]$ db2 "restore database mydb tablespace (SYSTOOLSPACE) from /home/db2inst1/dbbak taken at 20120116205427 "
DB20000I The RESTORE DATABASE command completed successfully.
db2inst1@localhost ~]$ db2 connect to mydb
Database Connection Information
Database server = DB2/LINUX 9.7.4
SQL authorization ID = DB2INST1
Local database alias = MYDB
[db2inst1@localhost ~]$ 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
Detailed explanation:
Normal
Tablespace ID = 3
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0100
Detailed explanation:
Restore pending
Tablespace ID = 4
Name = SMSSPACE1
Type = System managed space
Contents = All permanent data. Regular table space.
State = 0x0080
-----------0x0080:需要roll forward处理------------------
(7) ROLL FORWARD DATABASE
[db2inst1@localhost ~]$ db2 "rollforward db mydb to end of logs"
Rollforward Status
Input database alias = mydb
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 = 2012-01-16-23.44.03.000000 Local
DB20000I The ROLLFORWARD command completed successfully.
[db2inst1@localhost ~]$ db2 connect to mydb
Database Connection Information
Database server = DB2/LINUX 9.7.4
SQL authorization ID = DB2INST1
Local database alias = MYDB
[db2inst1@localhost ~]$ 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
Detailed explanation:
Normal
Tablespace ID = 3
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 4
Name = SMSSPACE1
Type = System managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
--
[db2inst1@localhost ~]$ db2 "select * from dbtest.test"
TID
-----------
1
2
2 record(s) selected.
[db2inst1@localhost ~]$ db2 "select * from dbtest2.test"
TID
-----------
0 record(s) selected.
[db2inst1@localhost ~]$ db2 "select * from db2inst1.test"
TID
-----------
99
1 record(s) selected.
(10) BACKUP DATABASE AGAIN
