Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

Taiju Aoki
November 20, 2020

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

M3 techtalk

Taiju Aoki

November 20, 2020
Tweet

More Decks by Taiju Aoki

Other Decks in Programming

Transcript

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

  2. ࢲ͸୭ʁ blue (@blue_1617) https://blue0513.github.io/show_room/ 2 ࠷ۙϋϚ͍ͬͯΔ΋ͷ ‣ ϚΠϯΫϥϑτʢSwitch ൛ʣ ‣

    ϐΫϛϯ3 ‣ ΑΙ͜ͷ˓˓Ͱ˓˓ੜ׆
  3. ͜ͷ࿩͸ʁ 3 https://speakerdeck.com/blue0513/on-premises2on-premises άϧʔϓاۀͷ“ΦϯϓϨ to ΦϯϓϨͨ͠࿩” ͷޙ೔ஊ 1. Oracle DB

    Λ֎஫ઌ͔Βάϧʔϓاۀ಺ʹҠߦ 2. ӡ༻։࢝ 3. ͓΍ɺOracle ͷ༷ࢠ͕….. <= ͔͜͜Βͷ͓࿩
  4. ໨࣍ 4 ୈ1࣍εʔύʔ Oracle Ҡߦ࡞ઓ ~ ഁյἫ ~ OMR ~

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

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

    clone ͯ͠४උΛͨ͠ Քಇத DB Clone DB
  7. ୈ1࣍εʔύʔ Oracle Ҡߦ࡞ઓ ~ ഁյἫ ~ 7 Ϩίʔυ਺: X Sequence਺:

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

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

    DB Ҡߦ౰೔ɺexport / import Λ࣮ߦ $ expdp full=y $ impdp full=y dump file
  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 ͳͲ΋ର৅ʹͳ͍ͬͯΔ ͜ΕҰݸͰ͢΂ͯ࿫͑Δ͍͢͝΍ͭ
  11. ୈ1࣍εʔύʔ Oracle Ҡߦ࡞ઓ ~ ഁյἫ ~ 11 Ϩίʔυ਺: X +

    a Քಇத DB Clone DB Ϩίʔυ਺: X + a ϨίʔυͷҠߦ׬શ੒ޭʂʂ΍ͬͨͥʂʂ
  12. ΧΠΪ ϑΝΠφϧήʔϜ

  13. ΧΠΪ ϑΝΠφϧήʔϜ 13 Ҡߦཌ೔ɺΤϥʔ͕ൃੜ Ϩίʔυ id: 9999 Sequence Last Number:

    9000ʢϨίʔυ਺ʹൺ΂ͯখ͍͞ʣ INSERT ͠Α͏ͱ͢ΔͱɺUnique key violation
  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
  15. ΧΠΪ ϑΝΠφϧήʔϜ 15 Ϩίʔυ਺: X + a Sequence਺: Y +

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

    b Քಇத DB Clone DB Ϩίʔυ਺: X + a Sequence਺: Y + b खಈͰ Sequence Λ Inc ͨ͠ ੈ͸ͳ΂ͯ͜ͱ΋ͳ͠ʂʂʂ
  17. ΧΠΪ ϑΝΠφϧήʔϜ 17 ཌिͷձٞʹͯ VPoE CTO ଞͷεΩʔϚେৎ෉ͳͷʁ ΍͹͛ͳงғؾ͕͢Δ

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

  19. 19 Full Export / Improt Mode Ϣʔβʔ͕࡞ͬͨ schema Ҏ֎ͷ system

    schema ΋΍ͬͯ͘ΕΔ Ϩίʔυ਺: X + a Sequence਺: Y + b Քಇத DB Clone DB Ϩίʔυ਺: X + a Sequence਺: Y System ܥεΩʔϚ System ܥεΩʔϚ OMR ~ ΦϥΫϧϛεςϦʔௐࠪ൝ ~
  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 ~ ΦϥΫϧϛεςϦʔௐࠪ൝ ~
  21. 21 System ܥεΩʔϚ SYSTEM ORDDATA PERFSTAT etc ….. γεςϜ؅ཧݖݶ ౷ܭ৘ใऔಘ

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

    ϝλσʔλ؅ཧ http://www.oracle-wiki.net/premium:startdocsdefaultschemas#toc2 etc…. ͜ΕΒ͕ਖ਼ৗʹಈ͔ͳ͍Մೳੑ͕͋Δͱ…….ʁ OMR ~ ΦϥΫϧϛεςϦʔௐࠪ൝ ~
  23. OMR ~ ΦϥΫϧϛεςϦʔௐࠪ൝ ~ 23 Log User ౷ܭ৘ใऔಘࣦഊ͠ͱΔͰ ͳΜ͔લʹൺ΂ͯ஗͍…… ͜Ε͸·͍ͣɻͦͷ͏ͪେരൃ͢Δ͔΋͠Εͳ͍

  24. Oracle SARABA ~ ૑ੈฤ ~

  25. Oracle SARABA ~ ૑ੈฤ ~ 25 աڈͷεφοϓγϣοτ ͔Β෮ݩ Քಇத DB

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

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

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

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

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

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

    system ܥεΩʔϚ => ͦͷ··ͳͷͰ៉ྷ system ܥ *Ҏ֎ͷ* ςʔϒϧ => σʔλͷΈߋ৽ system ܥ *Ҏ֎ͷ* sequence => ߋ৽
  32. Oracle SARABA ~ ૑ੈฤ ~ 32 ׬ᘳͳ DB ͷ׬੒

  33. ·ͱΊ

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

    ͋Γ͕ͱ͏͍͟͝·ͨ͠ :bow: