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

M3 techtalk

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: