实战演练
88504018喜欢 db2 oracle java的朋友进来了表空间的备份和恢复
在大型数据库里，表空间备份有很多灵活性。
一般来说大表的基础表，索引，LOB字段都需要建立在独立的表空间里。
1。建立非自动存储数据库
CMD: db2 "create db backupts automatic storage no on /home/db2inst1/backupts"
DB20000I The CREATE DATABASE command completed successfully.
2.建立DMS表空间
CMD: db2 "create tablespace headerts managed by database using (file '/home/db2inst1/ts/HEADERTS' 1000)"
DB20000I The SQL command completed successfully.
CMD: db2 "create tablespace detailts managed by database using (file '/home/db2inst1/ts/DETAILTS' 1000)"
DB20000I The SQL command completed successfully.
CMD: db2 "create tablespace masterts managed by database using (file '/home/db2inst1/ts/MASTERTS' 1000)"
DB20000I The SQL command completed successfully.
CMD: db2 list tablespaces
Tablespaces for Current Database
(部分略)
Tablespace ID = 4
Name = MASTERTS
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 5
Name = DETAILTS
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 6
Name = HEADERTS
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
3。在各自的表空间建立表并插入数据。
HEADER : DETAILS — 1 : N
CMD: db2 "create table header (id INTEGER NOT NULL PRIMARY KEY,FROM CHAR(2),TO CHAR(2),STARTDATE DATE) IN HEADERTS"
DB20000I The SQL command completed successfully.
CMD: db2 describe table header
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID SYSIBM INTEGER 4 0 No
FROM SYSIBM CHARACTER 2 0 Yes
TO SYSIBM CHARACTER 2 0 Yes
STARTDATE SYSIBM DATE 4 0 Yes
4 record(s) selected.
CMD: db2 "create table details (id INTEGER NOT NULL,seq INTEGER NOT NULL,ITEM_CODE CHAR(4), FLAG CHAR(1), DEL_FLG CHAR(1), PRIMARY KEY(id,seq),FOREIGN KEY (id) REFERENCES HEADER(id)) IN DETAILTS"
DB20000I The SQL command completed successfully.
CMD: db2 describe table details
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID SYSIBM INTEGER 4 0 No
SEQ SYSIBM INTEGER 4 0 No
ITEM_CODE SYSIBM CHARACTER 4 0 Yes
FLAG SYSIBM CHARACTER 1 0 Yes
DEL_FLG SYSIBM CHARACTER 1 0 Yes
5 record(s) selected.
CMD: db2 "create table master (id INTEGER NOT NULL PRIMARY KEY,item_code CHAR(4),FROM CHAR(2),TO CHAR(2),EFFECTIVE_STARTDATE DATE) IN MASTERTS"
DB20000I The SQL command completed successfully.
CMD: db2 "insert into header values(1,'AA','11','2010-07-10')"
DB20000I The SQL command completed successfully.
CMD: db2 "insert into header values(2,'BB','22','2010-07-10')"
DB20000I The SQL command completed successfully.
CMD: db2 "insert into header values(3,'CC','33','2010-07-10')"
DB20000I The SQL command completed successfully.
CMD: db2 "insert into header values(4,'DD','44','2010-07-10')"
DB20000I The SQL command completed successfully.
CMD: db2 "insert into details values(1,1,'AAAA','0','0')"
DB20000I The SQL command completed successfully.
CMD: db2 "insert into details values(1,2,'BBBB','0','0')"
DB20000I The SQL command completed successfully.
CMD: db2 "insert into details values(1,3,'CCCC','0','0')"
DB20000I The SQL command completed successfully.
CMD: db2 "insert into details values(2,1,'AAAA','0','0')"
DB20000I The SQL command completed successfully.
CMD: db2 "insert into details values(2,2,'BBBB','0','0')"
DB20000I The SQL command completed successfully.
CMD: db2 "insert into details values(2,2,'BBBB','0','0')"
CMD: db2 "insert into master values(1,'AAAA','AA','11','2010-07-10')"
DB20000I The SQL command completed successfully.
CMD: db2 "insert into master values(2,'BBBB','BB','22','2010-07-10')"
DB20000I The SQL command completed successfully.
4。数据库参数的设定
CMD: db2 update db cfg using LOGARCHMETH1 logretain
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.
CMD: db2 connect reset
DB20000I The SQL command completed successfully.
CMD: db2 backup database backupts to /mnt/hgfs/F/backup/
Backup successful. The timestamp for this backup image is : 20100710212625
CMD: db2 connect to backupts
CMD: db2 update db cfg using 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.
CMD: db2 connect reset
DB20000I The SQL command completed successfully.
5.表空间备份，注意有关联的表空间同时备份。
CMD: db2 "backup database backupts tablespace(masterts) to /mnt/hgfs/F/backup/"
Backup successful. The timestamp for this backup image is : 20100710213112
CMD: db2 "backup database backupts tablespace(headerts,detailts) to/mnt/hgfs/F/backup/"
Backup successful. The timestamp for this backup image is : 20100710213134
备份后的文件(注意大小):
CMD: ls /mnt/hgfs/F/backup/ -l
total 75660
-rwxrwxrwx 1 root root 99184640 Jul 10 21:26 BACKUPTS.0.db2inst1.NODE0000.CATN0000.20100710212625.001
-rwxrwxrwx 1 root root 22310912 Jul 10 21:31 BACKUPTS.3.db2inst1.NODE0000.CATN0000.20100710213112.001
-rwxrwxrwx 1 root root 33456128 Jul 10 21:31 BACKUPTS.3.db2inst1.NODE0000.CATN0000.20100710213134.001
6。故障！MASTER数据误删除
CMD: db2 connect to backupts
CMD: db2 "delete from master"
DB20000I The SQL command completed successfully.
CMD: db2 "select * from master"
ID ITEM_CODE FROM TO EFFECTIVE_STARTDATE
----------- --------- ---- -- -------------------
0 record(s) selected.
7。表空间恢复
CMD: db2 "restore db backupts tablespace(masterts) from /mnt/hgfs/F/backup taken at 20100710213112"
DB20000I The RESTORE DATABASE command completed successfully.
8。此时表空间处于前滚暂挂状态(Roll forward pending)
CMD: db2 connect to backupts
CMD: db2 "select * from master"
ID ITEM_CODE FROM TO EFFECTIVE_STARTDATE
----------- --------- ---- -- -------------------
SQL0290N Table space access is not allowed. SQLSTATE=55039
CMD: db2 list tablespaces
Tablespaces for Current Database
Tablespace ID = 4
Name = MASTERTS
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0080
Detailed explanation:
Roll forward pending
9.前滚恢复到误删除前的状态
CMD: db2 "rollforward database backupts to 2010-07-10-21.32.35 using local time and stop tablespace(masterts)"
Rollforward Status
Input database alias = backupts
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 = 1970-01-01-08.00.00.000000 Local
DB20000I The ROLLFORWARD command completed successfully.
10。数据确认恢复成功。
CMD: db2 connect to backupts
CMD: db2 "select * from master"
ID ITEM_CODE FROM TO EFFECTIVE_STARTDATE
----------- --------- ---- -- -------------------
1 AAAA AA 11 07/10/2010
2 BBBB BB 22 07/10/2010
2 record(s) selected.
页:
[1]