课程第39次（2017-09-26星期二）

botang

1. [oracle@station90 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 26 19:55:20 2017
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn hr/oracle_4U
   
8. Connected.
   
9. SQL> select  department_id, rowid from departments;
   
10. 
    
11. DEPARTMENT_ID ROWID
    
12. ------------- ------------------
    
13.            10 AAAR5kAAFAAAACvAAA
    
14.            20 AAAR5kAAFAAAACvAAB
    
15.            30 AAAR5kAAFAAAACvAAC
    
16.            40 AAAR5kAAFAAAACvAAD
    
17.            50 AAAR5kAAFAAAACvAAE
    
18.            60 AAAR5kAAFAAAACvAAF
    
19.            70 AAAR5kAAFAAAACvAAG
    
20.            80 AAAR5kAAFAAAACvAAH
    
21.            90 AAAR5kAAFAAAACvAAI
    
22.           100 AAAR5kAAFAAAACvAAJ
    
23.           110 AAAR5kAAFAAAACvAAK
    
24. 
    
25. DEPARTMENT_ID ROWID
    
26. ------------- ------------------
    
27.           120 AAAR5kAAFAAAACvAAL
    
28.           130 AAAR5kAAFAAAACvAAM
    
29.           140 AAAR5kAAFAAAACvAAN
    
30.           150 AAAR5kAAFAAAACvAAO
    
31.           160 AAAR5kAAFAAAACvAAP
    
32.           170 AAAR5kAAFAAAACvAAQ
    
33.           180 AAAR5kAAFAAAACvAAR
    
34.           190 AAAR5kAAFAAAACvAAS
    
35.           200 AAAR5kAAFAAAACvAAT
    
36.           210 AAAR5kAAFAAAACvAAU
    
37.           220 AAAR5kAAFAAAACvAAV
    
38. 
    
39. DEPARTMENT_ID ROWID
    
40. ------------- ------------------
    
41.           230 AAAR5kAAFAAAACvAAW
    
42.           240 AAAR5kAAFAAAACvAAX
    
43.           250 AAAR5kAAFAAAACvAAY
    
44.           260 AAAR5kAAFAAAACvAAZ
    
45.           270 AAAR5kAAFAAAACvAAa
    
46. 
    
47. 27 rows selected.
    
48. 
    
49. SQL> select  department_id, rowid, substr(rowid, 10 ,6 )  from departments;
    
50. 
    
51. DEPARTMENT_ID ROWID                 SUBSTR(ROWID,10,6)
    
52. ------------- ------------------ ------------------------
    
53.            10 AAAR5kAAFAAAACvAAA AAAACv
    
54.            20 AAAR5kAAFAAAACvAAB AAAACv
    
55.            30 AAAR5kAAFAAAACvAAC AAAACv
    
56.            40 AAAR5kAAFAAAACvAAD AAAACv
    
57.            50 AAAR5kAAFAAAACvAAE AAAACv
    
58.            60 AAAR5kAAFAAAACvAAF AAAACv
    
59.            70 AAAR5kAAFAAAACvAAG AAAACv
    
60.            80 AAAR5kAAFAAAACvAAH AAAACv
    
61.            90 AAAR5kAAFAAAACvAAI AAAACv
    
62.           100 AAAR5kAAFAAAACvAAJ AAAACv
    
63.           110 AAAR5kAAFAAAACvAAK AAAACv
    
64. 
    
65. DEPARTMENT_ID ROWID                 SUBSTR(ROWID,10,6)
    
66. ------------- ------------------ ------------------------
    
67.           120 AAAR5kAAFAAAACvAAL AAAACv
    
68.           130 AAAR5kAAFAAAACvAAM AAAACv
    
69.           140 AAAR5kAAFAAAACvAAN AAAACv
    
70.           150 AAAR5kAAFAAAACvAAO AAAACv
    
71.           160 AAAR5kAAFAAAACvAAP AAAACv
    
72.           170 AAAR5kAAFAAAACvAAQ AAAACv
    
73.           180 AAAR5kAAFAAAACvAAR AAAACv
    
74.           190 AAAR5kAAFAAAACvAAS AAAACv
    
75.           200 AAAR5kAAFAAAACvAAT AAAACv
    
76.           210 AAAR5kAAFAAAACvAAU AAAACv
    
77.           220 AAAR5kAAFAAAACvAAV AAAACv
    
78. 
    
79. DEPARTMENT_ID ROWID                 SUBSTR(ROWID,10,6)
    
80. ------------- ------------------ ------------------------
    
81.           230 AAAR5kAAFAAAACvAAW AAAACv
    
82.           240 AAAR5kAAFAAAACvAAX AAAACv
    
83.           250 AAAR5kAAFAAAACvAAY AAAACv
    
84.           260 AAAR5kAAFAAAACvAAZ AAAACv
    
85.           270 AAAR5kAAFAAAACvAAa AAAACv
    
86. 
    
87. 27 rows selected.
    
88. 
    
89. SQL> select  department_id, rowid, substr(rowid, 10 ,6 ), dbms_rowid.rowid_block_number(rowid)  from departments;
    
90. 
    
91. DEPARTMENT_ID ROWID                 SUBSTR(ROWID,10,6)
    
92. ------------- ------------------ ------------------------
    
93. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
    
94. ------------------------------------
    
95.            10 AAAR5kAAFAAAACvAAA AAAACv
    
96.                                  175
    
97. 
    
98.            20 AAAR5kAAFAAAACvAAB AAAACv
    
99.                                  175
    
100. 
     
101.            30 AAAR5kAAFAAAACvAAC AAAACv
     
102.                                  175
     
103. 
     
104. 
     
105. DEPARTMENT_ID ROWID                 SUBSTR(ROWID,10,6)
     
106. ------------- ------------------ ------------------------
     
107. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
     
108. ------------------------------------
     
109.            40 AAAR5kAAFAAAACvAAD AAAACv
     
110.                                  175
     
111. 
     
112.            50 AAAR5kAAFAAAACvAAE AAAACv
     
113.                                  175
     
114. 
     
115.            60 AAAR5kAAFAAAACvAAF AAAACv
     
116.                                  175
     
117. 
     
118. 
     
119. DEPARTMENT_ID ROWID                 SUBSTR(ROWID,10,6)
     
120. ------------- ------------------ ------------------------
     
121. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
     
122. ------------------------------------
     
123.            70 AAAR5kAAFAAAACvAAG AAAACv
     
124.                                  175
     
125. 
     
126.            80 AAAR5kAAFAAAACvAAH AAAACv
     
127.                                  175
     
128. 
     
129.            90 AAAR5kAAFAAAACvAAI AAAACv
     
130.                                  175
     
131. 
     
132. 
     
133. DEPARTMENT_ID ROWID                 SUBSTR(ROWID,10,6)
     
134. ------------- ------------------ ------------------------
     
135. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
     
136. ------------------------------------
     
137.           100 AAAR5kAAFAAAACvAAJ AAAACv
     
138.                                  175
     
139. 
     
140.           110 AAAR5kAAFAAAACvAAK AAAACv
     
141.                                  175
     
142. 
     
143.           120 AAAR5kAAFAAAACvAAL AAAACv
     
144.                                  175
     
145. 
     
146. 
     
147. DEPARTMENT_ID ROWID                 SUBSTR(ROWID,10,6)
     
148. ------------- ------------------ ------------------------
     
149. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
     
150. ------------------------------------
     
151.           130 AAAR5kAAFAAAACvAAM AAAACv
     
152.                                  175
     
153. 
     
154.           140 AAAR5kAAFAAAACvAAN AAAACv
     
155.                                  175
     
156. 
     
157.           150 AAAR5kAAFAAAACvAAO AAAACv
     
158.                                  175
     
159. 
     
160. 
     
161. DEPARTMENT_ID ROWID                 SUBSTR(ROWID,10,6)
     
162. ------------- ------------------ ------------------------
     
163. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
     
164. ------------------------------------
     
165.           160 AAAR5kAAFAAAACvAAP AAAACv
     
166.                                  175
     
167. 
     
168.           170 AAAR5kAAFAAAACvAAQ AAAACv
     
169.                                  175
     
170. 
     
171.           180 AAAR5kAAFAAAACvAAR AAAACv
     
172.                                  175
     
173. 
     
174. 
     
175. DEPARTMENT_ID ROWID                 SUBSTR(ROWID,10,6)
     
176. ------------- ------------------ ------------------------
     
177. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
     
178. ------------------------------------
     
179.           190 AAAR5kAAFAAAACvAAS AAAACv
     
180.                                  175
     
181. 
     
182.           200 AAAR5kAAFAAAACvAAT AAAACv
     
183.                                  175
     
184. 
     
185.           210 AAAR5kAAFAAAACvAAU AAAACv
     
186.                                  175
     
187. 
     
188. 
     
189. DEPARTMENT_ID ROWID                 SUBSTR(ROWID,10,6)
     
190. ------------- ------------------ ------------------------
     
191. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
     
192. ------------------------------------
     
193.           220 AAAR5kAAFAAAACvAAV AAAACv
     
194.                                  175
     
195. 
     
196.           230 AAAR5kAAFAAAACvAAW AAAACv
     
197.                                  175
     
198. 
     
199.           240 AAAR5kAAFAAAACvAAX AAAACv
     
200.                                  175
     
201. 
     
202. 
     
203. DEPARTMENT_ID ROWID                 SUBSTR(ROWID,10,6)
     
204. ------------- ------------------ ------------------------
     
205. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
     
206. ------------------------------------
     
207.           250 AAAR5kAAFAAAACvAAY AAAACv
     
208.                                  175
     
209. 
     
210.           260 AAAR5kAAFAAAACvAAZ AAAACv
     
211.                                  175
     
212. 
     
213.           270 AAAR5kAAFAAAACvAAa AAAACv
     
214.                                  175
     
215. 
     
216. 
     
217. 27 rows selected.
     
218. 
     
219. SQL> select  department_id, rowid, substr(rowid, 10 ,6 ), dbms_rowid.rowid_block_number(rowid)  from departments;
     
220. 
     
221. DEPARTMENT_ID ROWID                 SUBSTR(ROWID,10,6)
     
222. ------------- ------------------ ------------------------
     
223. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
     
224. ------------------------------------
     
225.            10 AAAR5kAAFAAAACvAAA AAAACv
     
226.                                  175
     
227. 
     
228.            20 AAAR5kAAFAAAACvAAB AAAACv
     
229.                                  175
     
230. 
     
231.            30 AAAR5kAAFAAAACvAAC AAAACv
     
232.                                  175
     
233. 
     
234. 
     
235. DEPARTMENT_ID ROWID                 SUBSTR(ROWID,10,6)
     
236. ------------- ------------------ ------------------------
     
237. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
     
238. ------------------------------------
     
239.            40 AAAR5kAAFAAAACvAAD AAAACv
     
240.                                  175
     
241. 
     
242.            50 AAAR5kAAFAAAACvAAE AAAACv
     
243.                                  175
     
244. 
     
245.            60 AAAR5kAAFAAAACvAAF AAAACv
     
246.                                  175
     
247. 
     
248. 
     
249. DEPARTMENT_ID ROWID                 SUBSTR(ROWID,10,6)
     
250. ------------- ------------------ ------------------------
     
251. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
     
252. ------------------------------------
     
253.            70 AAAR5kAAFAAAACvAAG AAAACv
     
254.                                  175
     
255. 
     
256.            80 AAAR5kAAFAAAACvAAH AAAACv
     
257.                                  175
     
258. 
     
259.            90 AAAR5kAAFAAAACvAAI AAAACv
     
260.                                  175
     
261. 
     
262. 
     
263. DEPARTMENT_ID ROWID                 SUBSTR(ROWID,10,6)
     
264. ------------- ------------------ ------------------------
     
265. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
     
266. ------------------------------------
     
267.           100 AAAR5kAAFAAAACvAAJ AAAACv
     
268.                                  175
     
269. 
     
270.           110 AAAR5kAAFAAAACvAAK AAAACv
     
271.                                  175
     
272. 
     
273.           120 AAAR5kAAFAAAACvAAL AAAACv
     
274.                                  175
     
275. 
     
276. 
     
277. DEPARTMENT_ID ROWID                 SUBSTR(ROWID,10,6)
     
278. ------------- ------------------ ------------------------
     
279. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
     
280. ------------------------------------
     
281.           130 AAAR5kAAFAAAACvAAM AAAACv
     
282.                                  175
     
283. 
     
284.           140 AAAR5kAAFAAAACvAAN AAAACv
     
285.                                  175
     
286. 
     
287.           150 AAAR5kAAFAAAACvAAO AAAACv
     
288.                                  175
     
289. 
     
290. 
     
291. DEPARTMENT_ID ROWID                 SUBSTR(ROWID,10,6)
     
292. ------------- ------------------ ------------------------
     
293. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
     
294. ------------------------------------
     
295.           160 AAAR5kAAFAAAACvAAP AAAACv
     
296.                                  175
     
297. 
     
298.           170 AAAR5kAAFAAAACvAAQ AAAACv
     
299.                                  175
     
300. 
     
301.           180 AAAR5kAAFAAAACvAAR AAAACv
     
302.                                  175
     
303. 
     
304. 
     
305. DEPARTMENT_ID ROWID                 SUBSTR(ROWID,10,6)
     
306. ------------- ------------------ ------------------------
     
307. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
     
308. ------------------------------------
     
309.           190 AAAR5kAAFAAAACvAAS AAAACv
     
310.                                  175
     
311. 
     
312.           200 AAAR5kAAFAAAACvAAT AAAACv
     
313.                                  175
     
314. 
     
315.           210 AAAR5kAAFAAAACvAAU AAAACv
     
316.                                  175
     
317. 
     
318. 
     
319. DEPARTMENT_ID ROWID                 SUBSTR(ROWID,10,6)
     
320. ------------- ------------------ ------------------------
     
321. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
     
322. ------------------------------------
     
323.           220 AAAR5kAAFAAAACvAAV AAAACv
     
324.                                  175
     
325. 
     
326.           230 AAAR5kAAFAAAACvAAW AAAACv
     
327.                                  175
     
328. 
     
329.           240 AAAR5kAAFAAAACvAAX AAAACv
     
330.                                  175
     
331. 
     
332. 
     
333. DEPARTMENT_ID ROWID                 SUBSTR(ROWID,10,6)
     
334. ------------- ------------------ ------------------------
     
335. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
     
336. ------------------------------------
     
337.           250 AAAR5kAAFAAAACvAAY AAAACv
     
338.                                  175
     
339. 
     
340.           260 AAAR5kAAFAAAACvAAZ AAAACv
     
341.                                  175
     
342. 
     
343.           270 AAAR5kAAFAAAACvAAa AAAACv
     
344.                                  175
     
345. 
     
346. 
     
347. 27 rows selected.
     
348. 
     
349. SQL> select  department_id, rowid, substr(rowid, 10 ,6 ), dbms_rowid.rowid_block_number(rowid)  from departments;

复制代码

1. SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 26 20:19:56 2017
   
2. 
   
3. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
   
4. 
   
5. SQL> Connected.
   
6. SQL> ORACLE instance shut down.
   
7. SQL> ORACLE instance started.
   
8. 
   
9. Total System Global Area 6664212480 bytes
   
10. Fixed Size                    2240944 bytes
    
11. Variable Size                 3640659536 bytes
    
12. Database Buffers         3003121664 bytes
    
13. Redo Buffers                   18190336 bytes
    
14. Database mounted.
    
15. SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    
16. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    
17. and Real Application Testing options
    
18. 
    
19. Recovery Manager: Release 11.2.0.3.0 - Production on Tue Sep 26 20:20:08 2017
    
20. 
    
21. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
22. 
    
23. connected to target database: ORCL (DBID=1343950367, not open)
    
24. 
    
25. RMAN>
    
26. Starting backup at 2017-09-26:20:20:09
    
27. using target database control file instead of recovery catalog
    
28. allocated channel: ORA_DISK_1
    
29. channel ORA_DISK_1: SID=130 device type=DISK
    
30. allocated channel: ORA_DISK_2
    
31. channel ORA_DISK_2: SID=194 device type=DISK
    
32. allocated channel: ORA_DISK_3
    
33. channel ORA_DISK_3: SID=6 device type=DISK
    
34. allocated channel: ORA_DISK_4
    
35. channel ORA_DISK_4: SID=70 device type=DISK
    
36. allocated channel: ORA_DISK_5
    
37. channel ORA_DISK_5: SID=131 device type=DISK
    
38. allocated channel: ORA_DISK_6
    
39. channel ORA_DISK_6: SID=195 device type=DISK
    
40. allocated channel: ORA_DISK_7
    
41. channel ORA_DISK_7: SID=7 device type=DISK
    
42. allocated channel: ORA_DISK_8
    
43. channel ORA_DISK_8: SID=71 device type=DISK
    
44. channel ORA_DISK_1: starting datafile copy
    
45. input datafile file number=00005 name=+DATA/orcl/datafile/example.265.955743535
    
46. output file name=/home/oracle/example01.dbf tag=TAG20170926T202011 RECID=3 STAMP=955743611
    
47. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
    
48. Finished backup at 2017-09-26:20:20:12
    
49. 
    
50. Starting Control File and SPFILE Autobackup at 2017-09-26:20:20:12
    
51. piece handle=+FRA/orcl/autobackup/2017_09_26/s_955743590.329.955743615 comment=NONE
    
52. Finished Control File and SPFILE Autobackup at 2017-09-26:20:20:15
    
53. 
    
54. RMAN>
    
55. 
    
56. Recovery Manager complete.
    
57. 记录了1+0 的读入
    
58. 记录了1+0 的写出
    
59. 8192字节(8.2 kB)已复制，2.2317e-05 秒，367 MB/秒
    
60. ASMCMD> ASMCMD> exit
    
61. 
    
62. Recovery Manager: Release 11.2.0.3.0 - Production on Tue Sep 26 20:20:16 2017
    
63. 
    
64. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
65. 
    
66. connected to target database: ORCL (DBID=1343950367, not open)
    
67. 
    
68. RMAN> 2> 3> 4>
    
69. executing command: SET MAX CORRUPT
    
70. using target database control file instead of recovery catalog
    
71. 
    
72. Starting backup at 2017-09-26:20:20:17
    
73. allocated channel: ORA_DISK_1
    
74. channel ORA_DISK_1: SID=131 device type=DISK
    
75. allocated channel: ORA_DISK_2
    
76. channel ORA_DISK_2: SID=194 device type=DISK
    
77. allocated channel: ORA_DISK_3
    
78. channel ORA_DISK_3: SID=7 device type=DISK
    
79. allocated channel: ORA_DISK_4
    
80. channel ORA_DISK_4: SID=70 device type=DISK
    
81. allocated channel: ORA_DISK_5
    
82. channel ORA_DISK_5: SID=130 device type=DISK
    
83. allocated channel: ORA_DISK_6
    
84. channel ORA_DISK_6: SID=193 device type=DISK
    
85. allocated channel: ORA_DISK_7
    
86. channel ORA_DISK_7: SID=6 device type=DISK
    
87. allocated channel: ORA_DISK_8
    
88. channel ORA_DISK_8: SID=69 device type=DISK
    
89. channel ORA_DISK_1: starting datafile copy
    
90. input is copy of datafile 00005: /home/oracle/example01.dbf
    
91. output file name=+DATA/orcl/datafile/example.265.955743619 tag=TAG20170926T202011 RECID=4 STAMP=955743621
    
92. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
    
93. Finished backup at 2017-09-26:20:20:22
    
94. 
    
95. Starting Control File and SPFILE Autobackup at 2017-09-26:20:20:22
    
96. piece handle=+FRA/orcl/autobackup/2017_09_26/s_955743590.332.955743623 comment=NONE
    
97. Finished Control File and SPFILE Autobackup at 2017-09-26:20:20:25
    
98. 
    
99. RMAN>
    
100. 
     
101. Recovery Manager complete.
     
102. 
     
103. Recovery Manager: Release 11.2.0.3.0 - Production on Tue Sep 26 20:20:29 2017
     
104. 
     
105. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
     
106. 
     
107. connected to target database: ORCL (DBID=1343950367, not open)
     
108. 
     
109. RMAN>
     
110. using target database control file instead of recovery catalog
     
111. allocated channel: ORA_DISK_1
     
112. channel ORA_DISK_1: SID=130 device type=DISK
     
113. allocated channel: ORA_DISK_2
     
114. channel ORA_DISK_2: SID=194 device type=DISK
     
115. allocated channel: ORA_DISK_3
     
116. channel ORA_DISK_3: SID=6 device type=DISK
     
117. allocated channel: ORA_DISK_4
     
118. channel ORA_DISK_4: SID=70 device type=DISK
     
119. allocated channel: ORA_DISK_5
     
120. channel ORA_DISK_5: SID=131 device type=DISK
     
121. allocated channel: ORA_DISK_6
     
122. channel ORA_DISK_6: SID=195 device type=DISK
     
123. allocated channel: ORA_DISK_7
     
124. channel ORA_DISK_7: SID=7 device type=DISK
     
125. allocated channel: ORA_DISK_8
     
126. channel ORA_DISK_8: SID=71 device type=DISK
     
127. List of Datafile Copies
     
128. =======================
     
129. 
     
130. Key     File S Completion Time     Ckp SCN    Ckp Time           
     
131. ------- ---- - ------------------- ---------- -------------------
     
132. 3       5    A 2017-09-26:20:20:11 2072566    2017-09-26:20:19:47
     
133.         Name: /home/oracle/example01.dbf
     
134.         Tag: TAG20170926T202011
     
135. 
     
136. deleted datafile copy
     
137. datafile copy file name=/home/oracle/example01.dbf RECID=3 STAMP=955743611
     
138. Deleted 1 objects
     
139. 
     
140. 
     
141. RMAN>
     
142. datafile 5 switched to datafile copy "+DATA/orcl/datafile/example.265.955743619"
     
143. 
     
144. RMAN>
     
145. 
     
146. Recovery Manager complete.
     
147. 
     
148. SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 26 20:20:33 2017
     
149. 
     
150. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
     
151. 
     
152. SQL> Connected.
     
153. SQL>
     
154. Database altered.
     
155. 
     
156. SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
     
157. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
     
158. and Real Application Testing options
     
159. [root@station90 pub]# su - oracle
     
160. [oracle@station90 ~]$ sqlplus /nolog
     
161. 
     
162. SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 26 20:20:44 2017
     
163. 
     
164. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
     
165. 
     
166. SQL> conn hr/oracle_4U
     
167. Connected.
     
168. SQL> select  * from departments;
     
169. select        * from departments
     
170.                *
     
171. ERROR at line 1:
     
172. ORA-01578: ORACLE data block corrupted (file # 5, block # 175)
     
173. ORA-01110: data file 5: '+DATA/orcl/datafile/example.265.955743619'
     
174. 
     
175. 
     
176. SQL> select  * from departments;
     
177. select        * from departments
     
178.                *
     
179. ERROR at line 1:
     
180. ORA-01578: ORACLE data block corrupted (file # 5, block # 175)
     
181. ORA-01110: data file 5: '+DATA/orcl/datafile/example.265.955743619'
     
182. 
     
183. 
     
184. SQL> select  * from departments;
     
185. select        * from departments
     
186. *
     
187. ERROR at line 1:
     
188. ORA-01578: ORACLE data block corrupted (file # 5, block # 175)
     
189. ORA-01110: data file 5: '+DATA/orcl/datafile/example.265.955743619'
     
190. 
     
191. 
     
192. SQL> desc departments
     
193. Name                                           Null?    Type
     
194. ----------------------------------------- -------- ----------------------------
     
195. DEPARTMENT_ID                                   NOT NULL NUMBER(4)
     
196. DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)
     
197. MANAGER_ID                                            NUMBER(6)
     
198. LOCATION_ID                                            NUMBER(4)
     
199. 
     
200. SQL> insert into departments values ( 999, 'XXX', 100, 1700 );
     
201. 
     
202. 1 row created.
     
203. 
     
204. SQL> commit;
     
205. 
     
206. Commit complete.
     
207. 
     
208. SQL> select  * from departments;
     
209. ERROR:
     
210. ORA-01578: ORACLE data block corrupted (file # 5, block # 175)
     
211. ORA-01110: data file 5: '+DATA/orcl/datafile/example.265.955743619'
     
212. 
     
213. 
     
214. 
     
215. no rows selected
     
216. 
     
217. SQL> select  * from departments where department_id=999;
     
218. 
     
219. DEPARTMENT_ID DEPARTMENT_NAME                     MANAGER_ID LOCATION_ID
     
220. ------------- ------------------------------ ---------- -----------
     
221.           999 XXX                                    100        1700
     
222. 
     
223. SQL> select  department_id , dbms_rowid.rowid_block_number(rowid)  from departments where department_id=999;
     
224. 
     
225. DEPARTMENT_ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
     
226. ------------- ------------------------------------
     
227.           999                                       171
     
228. 
     

复制代码

逻辑坏块的制造：

1. [root@station90 ~]# su - oracle
   
2. [oracle@station90 ~]$ sqlplus /nolog
   
3. 
   
4. SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 26 20:43:23 2017
   
5. 
   
6. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
   
7. 
   
8. SQL> conn / as sysdba
   
9. Connected.
   
10. SQL> create tablespace tbslogical   datafile size 5M  nologging;
    
11. 
    
12. Tablespace created.
    
13. 
    
14. SQL> conn hr/oracle_4U
    
15. Connected.
    
16. SQL> create table tlogical( a  varchar2(20), b number )  tablespace tbslogical;
    
17. 
    
18. Table created.
    
19. 
    
20. SQL> insert into tlogical values (  'lcwclzczl'     , 40018510);
    
21. 
    
22. 1 row created.
    
23. 
    
24. SQL> commit;
    
25. 
    
26. Commit complete.
    
27. 
    
28. SQL> exit
    
29. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    
30. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    
31. and Real Application Testing options
    
32. [oracle@station90 ~]$ rman target  /
    
33. 
    
34. Recovery Manager: Release 11.2.0.3.0 - Production on Tue Sep 26 20:48:49 2017
    
35. 
    
36. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
37. 
    
38. connected to target database: ORCL (DBID=1343950367)
    
39. 
    
40. RMAN> backup tablespace tbslogical;
    
41. 
    
42. Starting backup at 2017-09-26:20:48:57
    
43. using target database control file instead of recovery catalog
    
44. allocated channel: ORA_SBT_TAPE_1
    
45. channel ORA_SBT_TAPE_1: SID=68 device type=SBT_TAPE
    
46. channel ORA_SBT_TAPE_1: Oracle Secure Backup
    
47. allocated channel: ORA_SBT_TAPE_2
    
48. channel ORA_SBT_TAPE_2: SID=14 device type=SBT_TAPE
    
49. channel ORA_SBT_TAPE_2: Oracle Secure Backup
    
50. channel ORA_SBT_TAPE_1: starting full datafile backup set
    
51. channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
    
52. input datafile file number=00006 name=+DATA/orcl/datafile/tbslogical.267.955745025
    
53. channel ORA_SBT_TAPE_1: starting piece 1 at 2017-09-26:20:49:09
    
54. channel ORA_SBT_TAPE_1: finished piece 1 at 2017-09-26:20:49:34
    
55. piece handle=1tsff125_1_1 tag=TAG20170926T204909 comment=API Version 2.0,MMS Version 10.4.0.4
    
56. channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
    
57. Finished backup at 2017-09-26:20:49:34
    
58. 
    
59. Starting Control File and SPFILE Autobackup at 2017-09-26:20:49:34
    
60. piece handle=c-1343950367-20170926-02 comment=API Version 2.0,MMS Version 10.4.0.4
    
61. Finished Control File and SPFILE Autobackup at 2017-09-26:20:49:59
    
62. 
    
63. RMAN> exit
    
64. 
    
65. 
    
66. Recovery Manager complete.
    
67. [oracle@station90 ~]$ sqlplus /nolog
    
68. 
    
69. SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 26 20:50:51 2017
    
70. 
    
71. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
    
72. 
    
73. SQL> conn hr/oracle_4U
    
74. Connected.
    
75. SQL> create index ilogical_a on tlogical( a ) ;
    
76. 
    
77. Index created.
    
78. 
    
79. SQL> create index ilogical_b on tlogical( b ) ;
    
80. 
    
81. Index created.
    
82. 
    
83. SQL> select  a, b, dbms_rowi.rowid_block_number(rowid)  from tlogical;
    
84. select        a, b, dbms_rowi.rowid_block_number(rowid)  from tlogical
    
85.               *
    
86. ERROR at line 1:
    
87. ORA-00904: "DBMS_ROWI"."ROWID_BLOCK_NUMBER": invalid identifier
    
88. 
    
89. 
    
90. SQL> select  a, b, dbms_rowid.rowid_block_number(rowid)  from tlogical;
    
91. 
    
92. A                              B DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
    
93. -------------------- ---------- ------------------------------------
    
94. lcwclzczl               40018510                                  135
    
95. 
    
96. SQL> insert /*+ append */ into tlogical select * from tlogical ;
    
97. 
    
98. 1 row created.
    
99. 
    
100. SQL> select  * from tlogical;
     
101. select        * from tlogical
     
102.                *
     
103. ERROR at line 1:
     
104. ORA-12838: cannot read/modify an object after modifying it in parallel
     
105. 
     
106. 
     
107. SQL> commit;
     
108. 
     
109. Commit complete.
     
110. 
     
111. SQL> select  * from tlogical;
     
112. 
     
113. A                              B
     
114. -------------------- ----------
     
115. lcwclzczl               40018510
     
116. lcwclzczl               40018510
     
117. 
     
118. SQL> select  a, b, dbms_rowid.rowid_block_number(rowid)  from tlogical;
     
119. 
     
120. A                              B DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
     
121. -------------------- ---------- ------------------------------------
     
122. lcwclzczl               40018510                                  135
     
123. lcwclzczl               40018510                                  136
     
124. 
     
125. SQL> conn / as sysdba
     
126. Connected.
     
127. SQL> alter tablespace tbslogical offline immediate ;
     
128. 
     
129. Tablespace altered.
     
130. 
     
131. SQL> alter tablespace tbslogical online;
     
132. alter tablespace tbslogical online
     
133. *
     
134. ERROR at line 1:
     
135. ORA-01113: file 6 needs media recovery
     
136. ORA-01110: data file 6: '+DATA/orcl/datafile/tbslogical.267.955745025'
     
137. 
     
138. 
     
139. SQL> exit
     
140. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
     
141. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
     
142. and Real Application Testing options
     
143. [oracle@station90 ~]$ rman target /
     
144. 
     
145. Recovery Manager: Release 11.2.0.3.0 - Production on Tue Sep 26 20:55:32 2017
     
146. 
     
147. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
     
148. 
     
149. connected to target database: ORCL (DBID=1343950367)
     
150. 
     
151. RMAN> restore tablespace tbslogical ;
     
152. 
     
153. Starting restore at 2017-09-26:20:55:48
     
154. using target database control file instead of recovery catalog
     
155. allocated channel: ORA_DISK_1
     
156. channel ORA_DISK_1: SID=12 device type=DISK
     
157. allocated channel: ORA_DISK_2
     
158. channel ORA_DISK_2: SID=204 device type=DISK
     
159. allocated channel: ORA_DISK_3
     
160. channel ORA_DISK_3: SID=15 device type=DISK
     
161. allocated channel: ORA_DISK_4
     
162. channel ORA_DISK_4: SID=78 device type=DISK
     
163. allocated channel: ORA_DISK_5
     
164. channel ORA_DISK_5: SID=203 device type=DISK
     
165. allocated channel: ORA_DISK_6
     
166. channel ORA_DISK_6: SID=6 device type=DISK
     
167. allocated channel: ORA_DISK_7
     
168. channel ORA_DISK_7: SID=79 device type=DISK
     
169. allocated channel: ORA_DISK_8
     
170. channel ORA_DISK_8: SID=141 device type=DISK
     
171. allocated channel: ORA_SBT_TAPE_1
     
172. channel ORA_SBT_TAPE_1: SID=205 device type=SBT_TAPE
     
173. channel ORA_SBT_TAPE_1: Oracle Secure Backup
     
174. allocated channel: ORA_SBT_TAPE_2
     
175. channel ORA_SBT_TAPE_2: SID=80 device type=SBT_TAPE
     
176. channel ORA_SBT_TAPE_2: Oracle Secure Backup
     
177. 
     
178. channel ORA_SBT_TAPE_1: starting datafile backup set restore
     
179. channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
     
180. channel ORA_SBT_TAPE_1: restoring datafile 00006 to +DATA/orcl/datafile/tbslogical.267.955745025
     
181. channel ORA_SBT_TAPE_1: reading from backup piece 1tsff125_1_1
     
182. channel ORA_SBT_TAPE_1: piece handle=1tsff125_1_1 tag=TAG20170926T204909
     
183. channel ORA_SBT_TAPE_1: restored backup piece 1
     
184. channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:25
     
185. Finished restore at 2017-09-26:20:56:27
     
186. 
     
187. RMAN> recover tablespace tbslogical ;
     
188. 
     
189. Starting recover at 2017-09-26:20:56:39
     
190. using channel ORA_DISK_1
     
191. using channel ORA_DISK_2
     
192. using channel ORA_DISK_3
     
193. using channel ORA_DISK_4
     
194. using channel ORA_DISK_5
     
195. using channel ORA_DISK_6
     
196. using channel ORA_DISK_7
     
197. using channel ORA_DISK_8
     
198. using channel ORA_SBT_TAPE_1
     
199. using channel ORA_SBT_TAPE_2
     
200. 
     
201. starting media recovery
     
202. media recovery complete, elapsed time: 00:00:01
     
203. 
     
204. Finished recover at 2017-09-26:20:56:40
     
205. 
     
206. RMAN> exit
     
207. 
     
208. 
     
209. Recovery Manager complete.
     
210. [oracle@station90 ~]$ sqlplus /nolog
     
211. 
     
212. SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 26 20:57:07 2017
     
213. 
     
214. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
     
215. 
     
216. SQL> conn / as sysdba
     
217. Connected.
     
218. SQL> alter tablespace tbslogical online;
     
219. 
     
220. Tablespace altered.
     
221. 
     
222. SQL> conn hr/oracle_4U;
     
223. Connected.
     
224. SQL> select  * from tlogical;
     
225. ERROR:
     
226. ORA-01578: ORACLE data block corrupted (file # 6, block # 136)
     
227. ORA-01110: data file 6: '+DATA/orcl/datafile/tbslogical.267.955745025'
     
228. ORA-26040: Data block was loaded using the NOLOGGING option
     
229. 
     
230. 
     
231. 
     
232. no rows selected
     
233. 
     
234. SQL>
     

复制代码

1. select  * from dba_extents e
   
2. where e.owner='HR' and e.segment_name='DEPARTMENTS';
   
3. 
   
4. select * from dba_segments where
   
5.    owner='HR' and segment_name='DEPARTMENTS';
   
6.    
   
7.    
   
8.    select  * from v$database_block_corruption;
   
9.    
   
10.    select  * from v$backup_corruption;
    
11.    
    
12.    select  * from v$copy_corruption;

复制代码