Bo's Oracle Station

查看: 2001|回复: 0

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

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2016-7-4 23:09:08 | 显示全部楼层 |阅读模式
本帖最后由 botang 于 2016-7-4 23:31 编辑

【上完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. select  * from REPAIR_TABLE;

  2. select  * from  ORPHAN_KEY_TABLE;

  3. BEGIN
  4. DBMS_REPAIR.ADMIN_TABLES (
  5.    table_name => 'ORPHAN_KEY_TABLE',
  6.    table_type => DBMS_REPAIR.ORPHAN_TABLE,
  7.    action => DBMS_REPAIR.CREATE_ACTION,
  8.    tablespace => 'USERS');
  9. END;

  10. ---

  11. DECLARE num_corrupt INT;
  12. BEGIN
  13. num_corrupt := 0;
  14. DBMS_REPAIR.CHECK_OBJECT (
  15.    schema_name => 'HR',
  16.            object_name => 'T05309',
  17.    repair_table_name => 'REPAIR_TABLE',
  18.    corrupt_count => num_corrupt);
  19.   dbms_output.put_line( 'Find  '|| num_corrupt||' Bolcks.'  );
  20. END;

  21. ---
  22. DECLARE num_orphans INT;
  23. BEGIN
  24. num_orphans := 0;
  25. DBMS_REPAIR.DUMP_ORPHAN_KEYS (
  26.   schema_name => 'HR',
  27.   object_name => 'I05309_A',
  28.   object_type => DBMS_REPAIR.INDEX_OBJECT,
  29.   repair_table_name => 'REPAIR_TABLE',
  30.   orphan_table_name => 'ORPHAN_KEY_TABLE',
  31.   key_count => num_orphans);
  32. DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
  33. END;


  34. DECLARE num_orphans INT;
  35. BEGIN
  36. num_orphans := 0;
  37. DBMS_REPAIR.DUMP_ORPHAN_KEYS (
  38.   schema_name => 'HR',
  39.   object_name => 'I05309_B',
  40.   object_type => DBMS_REPAIR.INDEX_OBJECT,
  41.   repair_table_name => 'REPAIR_TABLE',
  42.   orphan_table_name => 'ORPHAN_KEY_TABLE',
  43.   key_count => num_orphans);
  44. DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
  45. END;

  46. ----
  47. select  * from  ORPHAN_KEY_TABLE  
  48.   where index_name='I05309_B' and keyrowid||' '='AAATq/AIgAAAIABAAA'||' ';
  49.   
  50.   ---
  51.   begin
  52.      dbms_repair.skip_corrupt_blocks('HR','T05309');
  53.    end;
  54.   
  55. --
  56. ---

  57.   select  dump(key)   from  ORPHAN_KEY_TABLE;



复制代码

      
   DUMP(KEY)
1Typ=208 Len=17: 2,4,0,0,0,0,9,108,105,110,104,97,105,98,105,110,254
2Typ=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.          A B
  3. ---------- --------------------
  4.      25260 linhaibin

  5. SQL> select  dump(1) from dual;

  6. DUMP(1)
  7. ------------------
  8. Typ=2 Len=2: 193,2

  9. SQL> select  dump(10) from dual;

  10. DUMP(10)
  11. -------------------
  12. Typ=2 Len=2: 193,11

  13. SQL> select  dump(100) from dual;

  14. DUMP(100)
  15. ------------------
  16. Typ=2 Len=2: 194,2

  17. SQL>
  18. SQL> select  dump(1000) from dual;

  19. DUMP(1000)
  20. -------------------
  21. Typ=2 Len=2: 194,11

  22. SQL> select  dump(110) from dual;

  23. DUMP(110)
  24. ---------------------
  25. Typ=2 Len=3: 194,2,11

  26. SQL> select  dump(-1) from dual;

  27. DUMP(-1)
  28. -----------------------
  29. Typ=2 Len=3: 62,100,102

  30. SQL> select  dump(-10) from dual;

  31. DUMP(-10)
  32. ----------------------
  33. Typ=2 Len=3: 62,91,102

  34. SQL> select  dump(-100) from dual;

  35. DUMP(-100)
  36. -----------------------
  37. Typ=2 Len=3: 61,100,102

  38. SQL> select  dump(-110) from dual;

  39. DUMP(-110)
  40. --------------------------
  41. Typ=2 Len=4: 61,100,91,102

  42. SQL> select  dump(-1000) from dual;

  43. DUMP(-1000)
  44. ----------------------
  45. Typ=2 Len=3: 61,91,102

  46. SQL> select  dump(123456.789) from dual;

  47. DUMP(123456.789)
  48. -------------------------------
  49. Typ=2 Len=6: 195,13,35,57,79,91

  50. SQL> select  dump(-123456.789) from dual;

  51. DUMP(-123456.789)
  52. ----------------------------------
  53. Typ=2 Len=7: 60,89,67,45,23,11,102

  54. SQL> select  dump(-123456.78901) from dual;

  55. DUMP(-123456.78901)
  56. -------------------------------------
  57. Typ=2 Len=8: 60,89,67,45,23,11,91,102

  58. SQL>
复制代码


5943776.zip

72.42 KB, 下载次数: 636

p13566938_112030_LINUX.zip

209.83 KB, 下载次数: 704

回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-28 23:24 , Processed in 0.041826 second(s), 27 queries .

快速回复 返回顶部 返回列表