本番運用中 Oracle を破壊してしまった話 ~ 破壊と創世 ~ / Oracle Destruction and Creation

M3 techtalk

Transcript

1. ຊ൪ӡ༻த Oracle Λഁյͯ͠͠·ͬͨ࿩
   ~ ഁյͱ૑ੈ ~
   @blue_1617
   

   View Slide

2. ࢲ͸୭ʁ
   blue (@blue_1617)
   https://blue0513.github.io/show_room/
   2
   ࠷ۙϋϚ͍ͬͯΔ΋ͷ
   ‣ ϚΠϯΫϥϑτʢSwitch ൛ʣ
   ‣ ϐΫϛϯ3
   ‣ ΑΙ͜ͷ˓˓Ͱ˓˓ੜ׆
   

   View Slide

3. ͜ͷ࿩͸ʁ 3
   https://speakerdeck.com/blue0513/on-premises2on-premises
   άϧʔϓاۀͷ“ΦϯϓϨ to ΦϯϓϨͨ͠࿩” ͷޙ೔ஊ
   1. Oracle DB Λ֎஫ઌ͔Βάϧʔϓاۀ಺ʹҠߦ
   2. ӡ༻։࢝
   3. ͓΍ɺOracle ͷ༷ࢠ͕….. <= ͔͜͜Βͷ͓࿩
   

   View Slide

4. ໨࣍ 4
   ୈ1࣍εʔύʔ Oracle Ҡߦ࡞ઓ ~ ഁյἫ ~
   OMR ~ ΦϥΫϧϛεςϦʔௐࠪ൝ ~
   ΧΠΪ ϑΝΠφϧήʔϜ
   Oracle SARABA ~ ૑ੈฤ ~
   ·ͱΊ
   

   View Slide

5. ୈ1࣍εʔύʔ Oracle Ҡߦ࡞ઓ ~ ഁյἫ ~
   

   View Slide

6. ୈ1࣍εʔύʔ Oracle Ҡߦ࡞ઓ ~ ഁյἫ ~ 6
   ·ͣɺࣄલʹՔಇத DB Λ clone ͯ͠४උΛͨ͠
   Քಇத DB Clone DB
   

   View Slide

7. ୈ1࣍εʔύʔ Oracle Ҡߦ࡞ઓ ~ ഁյἫ ~ 7
   Ϩίʔυ਺: X
   Sequence਺: Y
   Քಇத DB Clone DB
   Ϩίʔυ਺: X
   Sequence਺: Y
   

   View Slide

8. ୈ1࣍εʔύʔ Oracle Ҡߦ࡞ઓ ~ ഁյἫ ~ 8
   Ϩίʔυ਺: X + a
   Sequence਺: Y + b
   Քಇத DB Clone DB
   Ϩίʔυ਺: X
   Sequence਺: Y
   Clone ޙՔಇΛଓ͚ΔͱɺϨίʔυ਺ͳͲʹࠩҟ
   

   View Slide

9. ୈ1࣍εʔύʔ Oracle Ҡߦ࡞ઓ ~ ഁյἫ ~ 9
   Քಇத DB Clone DB
   Ҡߦ౰೔ɺexport / import Λ࣮ߦ
   $ expdp full=y $ impdp full=y
   dump file
   

   View Slide

10. ୈ1࣍εʔύʔ Oracle Ҡߦ࡞ઓ ~ ഁյἫ ~ 10
    https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL825
    https://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_import.htm
    Full Export / Improt Mode
    DB ͷத਎Λ”શͯ” export / import ͯ͘͠ΕΔ
    Ϣʔβʔ͕࡞ͬͨ schema Ҏ֎ͷ system schema ΋΍ͬͯ͘ΕΔ
    Role ΍ Sequence ͳͲ΋ର৅ʹͳ͍ͬͯΔ
    ͜ΕҰݸͰ͢΂ͯ࿫͑Δ͍͢͝΍ͭ
    

    View Slide

11. ୈ1࣍εʔύʔ Oracle Ҡߦ࡞ઓ ~ ഁյἫ ~ 11
    Ϩίʔυ਺: X + a
    Քಇத DB Clone DB
    Ϩίʔυ਺: X + a
    ϨίʔυͷҠߦ׬શ੒ޭʂʂ΍ͬͨͥʂʂ
    

    View Slide

12. ΧΠΪ ϑΝΠφϧήʔϜ
    

    View Slide

13. ΧΠΪ ϑΝΠφϧήʔϜ 13
    Ҡߦཌ೔ɺΤϥʔ͕ൃੜ
    Ϩίʔυ id: 9999
    Sequence Last Number: 9000ʢϨίʔυ਺ʹൺ΂ͯখ͍͞ʣ
    INSERT ͠Α͏ͱ͢ΔͱɺUnique key violation
    

    View Slide

14. ΧΠΪ ϑΝΠφϧήʔϜ 14
    Ҡߦཌ೔ɺۀ຿εΩʔϚͰΤϥʔ͕ൃੜ
    expdp / impdp full=y Ͱ͸ Sequence ͕ಉظ͞Εͳ͔ͬͨ
    Ϩίʔυ਺: X + a
    Sequence਺: Y + b
    Քಇத DB Clone DB
    Ϩίʔυ਺: X + a
    Sequence਺: Y
    $ expdp full=y
    $ impdp full=y
    

    View Slide

15. ΧΠΪ ϑΝΠφϧήʔϜ 15
    Ϩίʔυ਺: X + a
    Sequence਺: Y + b
    Քಇத DB Clone DB
    Ϩίʔυ਺: X + a
    Sequence਺: Y + b
    खಈͰ Sequence Λ Inc ͨ͠
    

    View Slide

16. ΧΠΪ ϑΝΠφϧήʔϜ 16
    Ϩίʔυ਺: X + a
    Sequence਺: Y + b
    Քಇத DB Clone DB
    Ϩίʔυ਺: X + a
    Sequence਺: Y + b
    खಈͰ Sequence Λ Inc ͨ͠
    ੈ͸ͳ΂ͯ͜ͱ΋ͳ͠ʂʂʂ
    

    View Slide

17. ΧΠΪ ϑΝΠφϧήʔϜ 17
    ཌिͷձٞʹͯ
    VPoE
    CTO
    ଞͷεΩʔϚେৎ෉ͳͷʁ
    ΍͹͛ͳงғؾ͕͢Δ
    

    View Slide

18. OMR ~ ΦϥΫϧϛεςϦʔௐࠪ൝ ~
    

    View Slide

19. 19
    Full Export / Improt Mode
    Ϣʔβʔ͕࡞ͬͨ schema Ҏ֎ͷ system schema ΋΍ͬͯ͘ΕΔ
    Ϩίʔυ਺: X + a
    Sequence਺: Y + b
    Քಇத DB Clone DB
    Ϩίʔυ਺: X + a
    Sequence਺: Y
    System ܥεΩʔϚ System ܥεΩʔϚ
    OMR ~ ΦϥΫϧϛεςϦʔௐࠪ൝ ~
    

    View Slide

20. 20
    Full Export / Improt Mode
    Ϣʔβʔ͕࡞ͬͨ schema Ҏ֎ͷ system schema ΋΍ͬͯ͘ΕΔ
    Ϩίʔυ਺: X + a
    Sequence਺: Y + b
    Քಇத DB Clone DB
    Ϩίʔυ਺: X + a
    Sequence਺: Y
    System ܥεΩʔϚ System ܥεΩʔϚ
    ੩͔ʹ Unique key violation
    OMR ~ ΦϥΫϧϛεςϦʔௐࠪ൝ ~
    

    View Slide

21. 21
    System ܥεΩʔϚ
    SYSTEM
    ORDDATA
    PERFSTAT
    etc …..
    γεςϜ؅ཧݖݶ
    ౷ܭ৘ใऔಘ
    ϝλσʔλ؅ཧ
    http://www.oracle-wiki.net/premium:startdocsdefaultschemas#toc2
    etc….
    OMR ~ ΦϥΫϧϛεςϦʔௐࠪ൝ ~
    

    View Slide

22. 22
    System ܥεΩʔϚ
    SYSTEM
    ORDDATA
    PERFSTAT
    etc …..
    γεςϜ؅ཧݖݶ
    ౷ܭ৘ใऔಘ
    ϝλσʔλ؅ཧ
    http://www.oracle-wiki.net/premium:startdocsdefaultschemas#toc2
    etc….
    ͜ΕΒ͕ਖ਼ৗʹಈ͔ͳ͍Մೳੑ͕͋Δͱ…….ʁ
    OMR ~ ΦϥΫϧϛεςϦʔௐࠪ൝ ~
    

    View Slide

23. OMR ~ ΦϥΫϧϛεςϦʔௐࠪ൝ ~ 23
    Log
    User
    ౷ܭ৘ใऔಘࣦഊ͠ͱΔͰ
    ͳΜ͔લʹൺ΂ͯ஗͍……
    ͜Ε͸·͍ͣɻͦͷ͏ͪେരൃ͢Δ͔΋͠Εͳ͍
    

    View Slide

24. Oracle SARABA ~ ૑ੈฤ ~
    

    View Slide

25. Oracle SARABA ~ ૑ੈฤ ~ 25
    աڈͷεφοϓγϣοτ
    ͔Β෮ݩ
    Քಇத DB
    ຊ൪DBɺաڈʹεφοϓγϣοτΛऔ͍ͬͯͨ
    ͦΕΛ༻͍ͯ৽͘͠ DB Λ࡞੒
    εφοϓγϣοτͷ࣌఺Ͱ͸ system ܥεΩʔϚ͸ഁյ͞Ε͍ͯͳ͍
    ʢ៉ྷͳ DBʣ
    

    View Slide

26. Oracle SARABA ~ ૑ੈฤ ~ 26
    աڈͷεφοϓγϣοτ
    ͔Β෮ݩ
    Քಇத DB
    system ܥεΩʔϚ *Ҏ֎* ͷϨίʔυͷΈimport
    system ܥεΩʔϚ *Ҏ֎* ͷ sequence ͷΈ import
    جຊઓུ
    

    View Slide

27. Oracle SARABA ~ ૑ੈฤ ~ 27
    աڈͷεφοϓγϣοτ
    ͔Β෮ݩ
    system ܥεΩʔϚ *Ҏ֎* ͷεΩʔϚΛର৅ʹ͢Δ
    (system ܥεΩʔϚ͸៉ྷͳͷͰ no touch ͰϤγ)
    1. TRUNCATE table
    2. DROP sequence
    Քಇத DB
    

    View Slide

28. Oracle SARABA ~ ૑ੈฤ ~ 28
    աڈͷεφοϓγϣοτ
    ͔Β෮ݩ
    system ܥεΩʔϚ *Ҏ֎* ͷεΩʔϚΛର৅ʹ͢Δ
    (system ܥεΩʔϚ͸៉ྷͳͷͰ no touch ͰϤγ)
    1. TRUNCATE table
    2. DROP sequence
    ςʔϒϧ͸ۭ
    εΩʔϚఆٛ͸ଘࡏ
    Role, Synonym ͸ͦͷ··
    Քಇத DB
    

    View Slide

29. Oracle SARABA ~ ૑ੈฤ ~ 29
    աڈͷεφοϓγϣοτ
    ͔Β෮ݩ
    system ܥεΩʔϚ *Ҏ֎* ͷεΩʔϚΛର৅ʹ͢Δ
    (system ܥεΩʔϚ͸៉ྷͳͷͰ no touch ͰϤγ)
    3. expdp full=y
    Քಇத DB
    ςʔϒϧ৘ใ
    Sequence, Schema etc
    ͢΂ͯΛग़ྗ
    

    View Slide

30. Oracle SARABA ~ ૑ੈฤ ~ 30
    աڈͷεφοϓγϣοτ
    ͔Β෮ݩ
    system ܥεΩʔϚ *Ҏ֎* ͷεΩʔϚΛର৅ʹ͢Δ
    (system ܥεΩʔϚ͸៉ྷͳͷͰ no touch ͰϤγ)
    4. impdp schemas data_only
    5. impdp schemas include=SEQUENCE
    Քಇத DB
    ςʔϒϧͷϨίʔυΛ import
    Sequence object Λ import
    

    View Slide

31. Oracle SARABA ~ ૑ੈฤ ~ 31
    աڈͷεφοϓγϣοτ
    ͔Β෮ݩ
    Քಇத DB
    system ܥεΩʔϚ => ͦͷ··ͳͷͰ៉ྷ
    system ܥ *Ҏ֎ͷ* ςʔϒϧ => σʔλͷΈߋ৽
    system ܥ *Ҏ֎ͷ* sequence => ߋ৽
    

    View Slide

32. Oracle SARABA ~ ૑ੈฤ ~ 32
    ׬ᘳͳ DB ͷ׬੒
    

    View Slide

33. ·ͱΊ
    

    View Slide

34. ·ͱΊ 34
    DB ͷҠߦͳͲͷਖ਼֬ੑ͕ٻΊΒΕΔ࡞ۀͰ͸Բͤͣ
    ઐ໳ՈͷҙݟΛڼ͝͏
    ৘ใ࿙Ӯ΍ۀ຿σʔλഁଛͷΑ͏ͳෆՄٯͳࣄଶͰͳ
    ͚Ε͹όάमਖ਼΍ϦΧόϦ͕Ͱ͖Δ͔Βɺམͪண͜͏
    ڠྗ͍͍ͯͨͩͨ͠ SRE νʔϜͷํʑɺCTO
    ͋Γ͕ͱ͏͍͟͝·ͨ͠ :bow:
    

    View Slide