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

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

ୈ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 ͳͲ΋ର৅ʹͳ͍ͬͯΔ ͜ΕҰݸͰ͢΂ͯ࿫͑Δ͍͢͝΍ͭ

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

ΧΠΪ ϑΝΠφϧήʔϜ

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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 ~ ΦϥΫϧϛεςϦʔௐࠪ൝ ~

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

Oracle SARABA ~ ૑ੈฤ ~

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

·ͱΊ

Slide 34

Slide 34 text

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