课程第43次：2016-07-04星期一

botang

【上完1Z0-052的第18章】ORACLE SUPPORT，打补丁，打热补丁
【上完1Z0-053的第9章】ADR以及物理坏块和逻辑坏块，DBMS_REPAIR用孤儿键值表修复逻辑坏块
【1Z0-051】：共10章（0 1 2 3 4 5 6 7 8 9 10 11）
【1Z0-052】：共13章（0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18）
【1Z0-053】：共15章（0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20）
表示已经上过的，表示还没上的。

1. 
   
2. select  * from REPAIR_TABLE;
   
3. 
   
4. select  * from  ORPHAN_KEY_TABLE;
   
5. 
   
6. BEGIN
   
7. DBMS_REPAIR.ADMIN_TABLES (
   
8.    table_name => 'ORPHAN_KEY_TABLE',
   
9.    table_type => DBMS_REPAIR.ORPHAN_TABLE,
   
10.    action => DBMS_REPAIR.CREATE_ACTION,
    
11.    tablespace => 'USERS');
    
12. END;
    
13. 
    
14. ---
    
15. 
    
16. DECLARE num_corrupt INT;
    
17. BEGIN
    
18. num_corrupt := 0;
    
19. DBMS_REPAIR.CHECK_OBJECT (
    
20.    schema_name => 'HR',
    
21.            object_name => 'T05309',
    
22.    repair_table_name => 'REPAIR_TABLE',
    
23.    corrupt_count => num_corrupt);
    
24.   dbms_output.put_line( 'Find  '|| num_corrupt||' Bolcks.'  );
    
25. END;
    
26. 
    
27. ---
    
28. DECLARE num_orphans INT;
    
29. BEGIN
    
30. num_orphans := 0;
    
31. DBMS_REPAIR.DUMP_ORPHAN_KEYS (
    
32.   schema_name => 'HR',
    
33.   object_name => 'I05309_A',
    
34.   object_type => DBMS_REPAIR.INDEX_OBJECT,
    
35.   repair_table_name => 'REPAIR_TABLE',
    
36.   orphan_table_name => 'ORPHAN_KEY_TABLE',
    
37.   key_count => num_orphans);
    
38. DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
    
39. END;
    
40. 
    
41. 
    
42. DECLARE num_orphans INT;
    
43. BEGIN
    
44. num_orphans := 0;
    
45. DBMS_REPAIR.DUMP_ORPHAN_KEYS (
    
46.   schema_name => 'HR',
    
47.   object_name => 'I05309_B',
    
48.   object_type => DBMS_REPAIR.INDEX_OBJECT,
    
49.   repair_table_name => 'REPAIR_TABLE',
    
50.   orphan_table_name => 'ORPHAN_KEY_TABLE',
    
51.   key_count => num_orphans);
    
52. DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
    
53. END;
    
54. 
    
55. ----
    
56. select  * from  ORPHAN_KEY_TABLE  
    
57.   where index_name='I05309_B' and keyrowid||' '='AAATq/AIgAAAIABAAA'||' ';
    
58.   
    
59.   ---
    
60.   begin
    
61.      dbms_repair.skip_corrupt_blocks('HR','T05309');
    
62.    end;
    
63.   
    
64. --
    
65. ---
    
66. 
    
67.   select  dump(key)   from  ORPHAN_KEY_TABLE;
    
68. 
    
69. 
    
70. 
    

复制代码

      

	DUMP(KEY)
1	Typ=208 Len=17: 2,4,0,0,0,0,9,108,105,110,104,97,105,98,105,110,254
2	Typ=208 Len=12: 2,4,0,0,0,0,4,195,3,53,61,254

                          

100^(193-193-0)(2-1)=1

100^(193-193-0)(11-1)=10

100^(194-193-0)(2-1)=100

100^(194-193-0)(11-1)=1000

100^(194-193-0)(2-1)+

  100^(194-193-1)(11-1)=110

100^(194-193-0)(12-1)=1100

100^(62-62-0)(101-100)=1

100^(62-62-0)(101-91)=10

100^(62-61-0)(101-100)=100

123456.789

100^(195-193-0)(13-1)+

100^(195-193-1)(35-1)+

100^(195-193-2)(57-1)+

100^(195-193-3)(79-1)+

100^(195-193-4)(91-1)=120000+3400+56+0.78+0.009=

123456.789

-123456.789

100^(62-60-0)(101-89)+

100^(62-60-1)(101-67)+

100^(62-60-2)(101-45)+

100^(62-60-3)(101-23)+

100^(62-60-4)(101-11)=120000+3400+56+0.78+0.009=

-123456.789

-123456.78901

100^(62-60-0)(101-89)+

100^(62-60-1)(101-67)+

100^(62-60-2)(101-45)+

100^(62-60-3)(101-23)+

100^(62-60-4)(101-11)+

100^(62-60-5)(101-91)

=120000+3400+56+0.78+0.009+0.00001=

-123456.78901

1. SQL> select  * from t05309;
   
2. ERROR:
   
3. ORA-01578: ORACLE data block corrupted (file # 6, block # 136)
   
4. ORA-01110: data file 6: '+DATA/orcl/datafile/tbs05309.268.916084781'
   
5. ORA-26040: Data block was loaded using the NOLOGGING option
   

复制代码

1. begin
   
2.      dbms_repair.skip_corrupt_blocks('HR','T05309');
   
3.    end;

复制代码

1. SQL> select  * from t05309;
   
2. 
   
3.          A B
   
4. ---------- --------------------
   
5.      25260 linhaibin
   
6. 
   
7. SQL> select  dump(1) from dual;
   
8. 
   
9. DUMP(1)
   
10. ------------------
    
11. Typ=2 Len=2: 193,2
    
12. 
    
13. SQL> select  dump(10) from dual;
    
14. 
    
15. DUMP(10)
    
16. -------------------
    
17. Typ=2 Len=2: 193,11
    
18. 
    
19. SQL> select  dump(100) from dual;
    
20. 
    
21. DUMP(100)
    
22. ------------------
    
23. Typ=2 Len=2: 194,2
    
24. 
    
25. SQL>
    
26. SQL> select  dump(1000) from dual;
    
27. 
    
28. DUMP(1000)
    
29. -------------------
    
30. Typ=2 Len=2: 194,11
    
31. 
    
32. SQL> select  dump(110) from dual;
    
33. 
    
34. DUMP(110)
    
35. ---------------------
    
36. Typ=2 Len=3: 194,2,11
    
37. 
    
38. SQL> select  dump(-1) from dual;
    
39. 
    
40. DUMP(-1)
    
41. -----------------------
    
42. Typ=2 Len=3: 62,100,102
    
43. 
    
44. SQL> select  dump(-10) from dual;
    
45. 
    
46. DUMP(-10)
    
47. ----------------------
    
48. Typ=2 Len=3: 62,91,102
    
49. 
    
50. SQL> select  dump(-100) from dual;
    
51. 
    
52. DUMP(-100)
    
53. -----------------------
    
54. Typ=2 Len=3: 61,100,102
    
55. 
    
56. SQL> select  dump(-110) from dual;
    
57. 
    
58. DUMP(-110)
    
59. --------------------------
    
60. Typ=2 Len=4: 61,100,91,102
    
61. 
    
62. SQL> select  dump(-1000) from dual;
    
63. 
    
64. DUMP(-1000)
    
65. ----------------------
    
66. Typ=2 Len=3: 61,91,102
    
67. 
    
68. SQL> select  dump(123456.789) from dual;
    
69. 
    
70. DUMP(123456.789)
    
71. -------------------------------
    
72. Typ=2 Len=6: 195,13,35,57,79,91
    
73. 
    
74. SQL> select  dump(-123456.789) from dual;
    
75. 
    
76. DUMP(-123456.789)
    
77. ----------------------------------
    
78. Typ=2 Len=7: 60,89,67,45,23,11,102
    
79. 
    
80. SQL> select  dump(-123456.78901) from dual;
    
81. 
    
82. DUMP(-123456.78901)
    
83. -------------------------------------
    
84. Typ=2 Len=8: 60,89,67,45,23,11,91,102
    
85. 
    
86. SQL>
    

复制代码