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. ͜ͷ࿩͸ʁ 3 https://speakerdeck.com/blue0513/on-premises2on-premises άϧʔϓاۀͷ“ΦϯϓϨ to ΦϯϓϨͨ͠࿩” ͷޙ೔ஊ 1. Oracle DB

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

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

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

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

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

    a Քಇத DB Clone DB Ϩίʔυ਺: X + a ϨίʔυͷҠߦ׬શ੒ޭʂʂ΍ͬͨͥʂʂ
  8. ΧΠΪ ϑΝΠφϧήʔϜ 13 Ҡߦཌ೔ɺΤϥʔ͕ൃੜ Ϩίʔυ id: 9999 Sequence Last Number:

    9000ʢϨίʔυ਺ʹൺ΂ͯখ͍͞ʣ INSERT ͠Α͏ͱ͢ΔͱɺUnique key violation
  9. ΧΠΪ ϑΝΠφϧήʔϜ 14 Ҡߦཌ೔ɺۀ຿εΩʔϚͰΤϥʔ͕ൃੜ expdp / impdp full=y Ͱ͸ Sequence

    ͕ಉظ͞Εͳ͔ͬͨ Ϩίʔυ਺: X + a Sequence਺: Y + b Քಇத DB Clone DB Ϩίʔυ਺: X + a Sequence਺: Y $ expdp full=y $ impdp full=y
  10. ΧΠΪ ϑΝΠφϧήʔϜ 15 Ϩίʔυ਺: X + a Sequence਺: Y +

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

    b Քಇத DB Clone DB Ϩίʔυ਺: X + a Sequence਺: Y + b खಈͰ Sequence Λ Inc ͨ͠ ੈ͸ͳ΂ͯ͜ͱ΋ͳ͠ʂʂʂ
  12. 19 Full Export / Improt Mode Ϣʔβʔ͕࡞ͬͨ schema Ҏ֎ͷ system

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

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

    ϝλσʔλ؅ཧ http://www.oracle-wiki.net/premium:startdocsdefaultschemas#toc2 etc…. ͜ΕΒ͕ਖ਼ৗʹಈ͔ͳ͍Մೳੑ͕͋Δͱ…….ʁ OMR ~ ΦϥΫϧϛεςϦʔௐࠪ൝ ~
  16. Oracle SARABA ~ ૑ੈฤ ~ 25 աڈͷεφοϓγϣοτ ͔Β෮ݩ Քಇத DB

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

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

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

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

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

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

    system ܥεΩʔϚ => ͦͷ··ͳͷͰ៉ྷ system ܥ *Ҏ֎ͷ* ςʔϒϧ => σʔλͷΈߋ৽ system ܥ *Ҏ֎ͷ* sequence => ߋ৽