db2 恢复误删表
oracle如果误删了表，可以很方便的flash back
最重要的是不会影响业务
但是如果你在DB2中招，就目前来说，你就不那么走运了
1. --首先设置归档模式
2. [db2inst2@localhost ~]$ db2 update db cfg using logretain on
3. DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
4. SQL1363W One or more of the parameters submitted for immediate modification
5. were not changed dynamically. For these configuration parameters, all
6. applications must disconnect from this database before the changes become
7. effective.
8. [db2inst2@localhost ~]$ db2 get db cfg | grep -i logre
9. Log retain for recovery enabled (LOGRETAIN) = RECOVERY
10. First log archive method (LOGARCHMETH1) = LOGRETAIN
11.
12.
13. --然后理所当然的full backup一次
14. [db2inst2@localhost ~]$ db2 connect to sample
15. SQL1116N A connection to or activation of database "SAMPLE" cannot be made
16. because of BACKUP PENDING. SQLSTATE=57019
17. [db2inst2@localhost ~]$ db2 backup db sample
18.
19. Backup successful. The timestamp for this backup image is : 20110424143713
20.
21. --然后确认表空间是DROP_RECOVERY的
22. select TBSPACE, DROP_RECOVERY from SYSCAT.TABLESPACES
23.
24. TBSPACE DROP_RECOVERY
25. -------------------------------------------------------------------------------------------------------------------------------- -------------
26. SYSCATSPACE N
27. TEMPSPACE1 N
28. USERSPACE1 Y
29. SYSTOOLSPACE Y
30.
31. --否则可以使用以下语句打开：
32. ALTER TABLESPACE DROPPED TABLE RECOVERY ON
33.
34. --建立测试表
35. --T3表是准备被模拟误drop，并进行恢复的
36. --T4表是模拟其他不相关的表，看恢复操作是否有影响
37. [db2inst2@localhost ~]$ db2 "create table T3 (C1 INT)"
38. DB20000I The SQL command completed successfully.
39. [db2inst2@localhost ~]$ db2 "create table T4 (C1 INT)"
40. DB20000I The SQL command completed successfully.
41. [db2inst2@localhost ~]$ db2 "insert into t3 values (333)"
42. DB20000I The SQL command completed successfully.
43. [db2inst2@localhost ~]$ db2 "select * from t3"
44.
45. C1
46. -----------
47. 333
48.
49. --开始模拟误删除
50. [db2inst2@localhost ~]$ db2 drop table t3
51. DB20000I The SQL command completed successfully.
52. [db2inst2@localhost ~]$ db2 "select * from t3"
53. SQL0204N "DB2INST2.T3" is an undefined name. SQLSTATE=42704
54.
55. --这时候模拟在发现前，t4表的业务继续
56. [db2inst2@localhost ~]$ db2 "insert into t4 values (444)"
57. DB20000I The SQL command completed successfully.
58. [db2inst2@localhost ~]$ db2 "select * from t4"
59.
60. C1
61. -----------
62. 444
63.
64. 1 record(s) selected.
65.
66.
67. --使用list history看到刚刚删除的表
68. [db2inst2@localhost ~]$ db2 list history dropped table all for sample
69.
70. List History File for sample
71.
72. Number of matching file entries = 1
73.
74. Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
75. -- --- ------------------ ---- --- ------------ ------------ --------------
76. D T 20110424193315 000000000000521200020014
77. ----------------------------------------------------------------------------
78. "DB2INST2"."T3" resides in 1 tablespace(s):
79.
80. 00001 USERSPACE1
81. ----------------------------------------------------------------------------
82. Comment: DROP TABLE
83. Start Time: 20110424193315
84. End Time: 20110424193315
85. Status: A
86. ----------------------------------------------------------------------------
87. EID: 33
88.
89. DDL: CREATE TABLE "DB2INST2"."T3" ( "C1" INTEGER ) IN "USERSPACE1" ;
90. ----------------------------------------------------------------------------
91.
92. --记住backup id,稍后有用 000000000000521200020014
93.
94. --开始恢复
95. --先restore
96. [db2inst2@localhost ~]$ db2 restore db sample
97. SQL2539W Warning! Restoring to an existing database that is the same as the
98. backup image database. The database files will be deleted.
99. Do you want to continue ? (y/n) y
100. DB20000I The RESTORE DATABASE command completed successfully.
101.
102. --开始rollforward,关键的一步
103. [db2inst2@localhost ~]$ db2 rollforward db sample to end of logs and stop recover dropped table 000000000000521200020014 to /home/db2inst2
104.
105. Rollforward Status
106.
107. Input database alias = sample
108. Number of nodes have returned status = 1
109.
110. Node number = 0
111. Rollforward status = not pending
112. Next log file to be read =
113. Log files processed = S0000000.LOG - S0000003.LOG
114. Last committed transaction = 2011-04-24-11.34.03.000000 UTC
115.
116. DB20000I The ROLLFORWARD command completed successfully.
117.
118. --这时候看到指定目录下多了一个以节点为名字的目录，里面有名为data的一个文件
119. [db2inst2@localhost ~]$ ll NODE0000/
120. total 4
121. -rw-r----- 1 db2inst2 db2iadm1 4 Apr 24 19:40 data
122. [db2inst2@localhost ~]$ pwd
123. /home/db2inst2
124.
125. --开始重建表t3
126. --先建表结构，使用list history里面的DDL
127. [db2inst2@localhost ~]$ db2 connect to sample
128.
129. Database Connection Information
130.
131. Database server = DB2/LINUX 9.7.2
132. SQL authorization ID = DB2INST2
133. Local database alias = SAMPLE
134.
135. [db2inst2@localhost ~]$ db2 "CREATE TABLE "DB2INST2"."T3" ( "C1" INTEGER ) IN "USERSPACE1""
136. DB20000I The SQL command completed successfully.
137. [db2inst2@localhost ~]$ db2
138. [db2inst2@localhost ~]$ db2 "select * from t3"
139.
140. C1
141. -----------
142.
143. 0 record(s) selected.
144.
145. --看到现在是没有数据的
146.
147. --然后使用刚才rollforward出来的data import
148. [db2inst2@localhost ~]$ db2 import from /home/db2inst2/NODE0000/data of del insert into t3
149. SQL3109N The utility is beginning to load data from file
150. "/home/db2inst2/NODE0000/data".
151.
152. SQL3110N The utility has completed processing. "1" rows were read from the
153. input file.
154.
155. SQL3221W ...Begin COMMIT WORK. Input Record Count = "1".
156.
157. SQL3222W ...COMMIT of any database changes was successful.
158.
159. SQL3149N "1" rows were processed from the input file. "1" rows were
160. successfully inserted into the table. "0" rows were rejected.
161.
162.
163. Number of rows read = 1
164. Number of rows skipped = 0
165. Number of rows inserted = 1
166. Number of rows updated = 0
167. Number of rows rejected = 0
168. Number of rows committed = 1
169.
170. [db2inst2@localhost ~]$ db2 "select * from t3"
171.
172. C1
173. -----------
174. 333
175.
176. 1 record(s) selected.
177.
178. --看到数据回来了
179.
180. --因为是end of logs，所以T4的数据应该也是最新的
181. [db2inst2@localhost ~]$ db2 "select * from t4"
182.
183. C1
184. -----------
185. 444
186.
187. 1 record(s) selected.
188.
189. --完成
最重要的是不会影响业务
但是如果你在DB2中招，就目前来说，你就不那么走运了
1. --首先设置归档模式
2. [db2inst2@localhost ~]$ db2 update db cfg using logretain on
3. DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
4. SQL1363W One or more of the parameters submitted for immediate modification
5. were not changed dynamically. For these configuration parameters, all
6. applications must disconnect from this database before the changes become
7. effective.
8. [db2inst2@localhost ~]$ db2 get db cfg | grep -i logre
9. Log retain for recovery enabled (LOGRETAIN) = RECOVERY
10. First log archive method (LOGARCHMETH1) = LOGRETAIN
11.
12.
13. --然后理所当然的full backup一次
14. [db2inst2@localhost ~]$ db2 connect to sample
15. SQL1116N A connection to or activation of database "SAMPLE" cannot be made
16. because of BACKUP PENDING. SQLSTATE=57019
17. [db2inst2@localhost ~]$ db2 backup db sample
18.
19. Backup successful. The timestamp for this backup image is : 20110424143713
20.
21. --然后确认表空间是DROP_RECOVERY的
22. select TBSPACE, DROP_RECOVERY from SYSCAT.TABLESPACES
23.
24. TBSPACE DROP_RECOVERY
25. -------------------------------------------------------------------------------------------------------------------------------- -------------
26. SYSCATSPACE N
27. TEMPSPACE1 N
28. USERSPACE1 Y
29. SYSTOOLSPACE Y
30.
31. --否则可以使用以下语句打开：
32. ALTER TABLESPACE
33.
34. --建立测试表
35. --T3表是准备被模拟误drop，并进行恢复的
36. --T4表是模拟其他不相关的表，看恢复操作是否有影响
37. [db2inst2@localhost ~]$ db2 "create table T3 (C1 INT)"
38. DB20000I The SQL command completed successfully.
39. [db2inst2@localhost ~]$ db2 "create table T4 (C1 INT)"
40. DB20000I The SQL command completed successfully.
41. [db2inst2@localhost ~]$ db2 "insert into t3 values (333)"
42. DB20000I The SQL command completed successfully.
43. [db2inst2@localhost ~]$ db2 "select * from t3"
44.
45. C1
46. -----------
47. 333
48.
49. --开始模拟误删除
50. [db2inst2@localhost ~]$ db2 drop table t3
51. DB20000I The SQL command completed successfully.
52. [db2inst2@localhost ~]$ db2 "select * from t3"
53. SQL0204N "DB2INST2.T3" is an undefined name. SQLSTATE=42704
54.
55. --这时候模拟在发现前，t4表的业务继续
56. [db2inst2@localhost ~]$ db2 "insert into t4 values (444)"
57. DB20000I The SQL command completed successfully.
58. [db2inst2@localhost ~]$ db2 "select * from t4"
59.
60. C1
61. -----------
62. 444
63.
64. 1 record(s) selected.
65.
66.
67. --使用list history看到刚刚删除的表
68. [db2inst2@localhost ~]$ db2 list history dropped table all for sample
69.
70. List History File for sample
71.
72. Number of matching file entries = 1
73.
74. Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
75. -- --- ------------------ ---- --- ------------ ------------ --------------
76. D T 20110424193315 000000000000521200020014
77. ----------------------------------------------------------------------------
78. "DB2INST2"."T3" resides in 1 tablespace(s):
79.
80. 00001 USERSPACE1
81. ----------------------------------------------------------------------------
82. Comment: DROP TABLE
83. Start Time: 20110424193315
84. End Time: 20110424193315
85. Status: A
86. ----------------------------------------------------------------------------
87. EID: 33
88.
89. DDL: CREATE TABLE "DB2INST2"."T3" ( "C1" INTEGER ) IN "USERSPACE1" ;
90. ----------------------------------------------------------------------------
91.
92. --记住backup id,稍后有用 000000000000521200020014
93.
94. --开始恢复
95. --先restore
96. [db2inst2@localhost ~]$ db2 restore db sample
97. SQL2539W Warning! Restoring to an existing database that is the same as the
98. backup image database. The database files will be deleted.
99. Do you want to continue ? (y/n) y
100. DB20000I The RESTORE DATABASE command completed successfully.
101.
102. --开始rollforward,关键的一步
103. [db2inst2@localhost ~]$ db2 rollforward db sample to end of logs and stop recover dropped table 000000000000521200020014 to /home/db2inst2
104.
105. Rollforward Status
106.
107. Input database alias = sample
108. Number of nodes have returned status = 1
109.
110. Node number = 0
111. Rollforward status = not pending
112. Next log file to be read =
113. Log files processed = S0000000.LOG - S0000003.LOG
114. Last committed transaction = 2011-04-24-11.34.03.000000 UTC
115.
116. DB20000I The ROLLFORWARD command completed successfully.
117.
118. --这时候看到指定目录下多了一个以节点为名字的目录，里面有名为data的一个文件
119. [db2inst2@localhost ~]$ ll NODE0000/
120. total 4
121. -rw-r----- 1 db2inst2 db2iadm1 4 Apr 24 19:40 data
122. [db2inst2@localhost ~]$ pwd
123. /home/db2inst2
124.
125. --开始重建表t3
126. --先建表结构，使用list history里面的DDL
127. [db2inst2@localhost ~]$ db2 connect to sample
128.
129. Database Connection Information
130.
131. Database server = DB2/LINUX 9.7.2
132. SQL authorization ID = DB2INST2
133. Local database alias = SAMPLE
134.
135. [db2inst2@localhost ~]$ db2 "CREATE TABLE "DB2INST2"."T3" ( "C1" INTEGER ) IN "USERSPACE1""
136. DB20000I The SQL command completed successfully.
137. [db2inst2@localhost ~]$ db2
138. [db2inst2@localhost ~]$ db2 "select * from t3"
139.
140. C1
141. -----------
142.
143. 0 record(s) selected.
144.
145. --看到现在是没有数据的
146.
147. --然后使用刚才rollforward出来的data import
148. [db2inst2@localhost ~]$ db2 import from /home/db2inst2/NODE0000/data of del insert into t3
149. SQL3109N The utility is beginning to load data from file
150. "/home/db2inst2/NODE0000/data".
151.
152. SQL3110N The utility has completed processing. "1" rows were read from the
153. input file.
154.
155. SQL3221W ...Begin COMMIT WORK. Input Record Count = "1".
156.
157. SQL3222W ...COMMIT of any database changes was successful.
158.
159. SQL3149N "1" rows were processed from the input file. "1" rows were
160. successfully inserted into the table. "0" rows were rejected.
161.
162.
163. Number of rows read = 1
164. Number of rows skipped = 0
165. Number of rows inserted = 1
166. Number of rows updated = 0
167. Number of rows rejected = 0
168. Number of rows committed = 1
169.
170. [db2inst2@localhost ~]$ db2 "select * from t3"
171.
172. C1
173. -----------
174. 333
175.
176. 1 record(s) selected.
177.
178. --看到数据回来了
179.
180. --因为是end of logs，所以T4的数据应该也是最新的
181. [db2inst2@localhost ~]$ db2 "select * from t4"
182.
183. C1
184. -----------
185. 444
186.
187. 1 record(s) selected.
188.
189. --完成