Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Speaker Deck
PRO
Sign in
Sign up for free
本番運用中 Oracle を破壊してしまった話 ~ 破壊と創世 ~ / Oracle Destruction and Creation
Taiju Aoki
November 20, 2020
Programming
0
2.2k
本番運用中 Oracle を破壊してしまった話 ~ 破壊と創世 ~ / Oracle Destruction and Creation
M3 techtalk
Taiju Aoki
November 20, 2020
Tweet
Share
More Decks by Taiju Aoki
See All by Taiju Aoki
オンプレ to オンプレした話/on-premises2on-premises
blue0513
0
2.3k
GitもCIもテストもないプロジェクトでデプロイを自動化するまでの道 / rebuild of DevOps
blue0513
0
2.4k
Git & GitLab & コードレビューって? / about Git, GitLab, CodeReview
blue0513
0
120
ElectronでSlackをさらに便利にしちゃう / Slack with Electron
blue0513
3
430
GitHub の README をいい感じにする / Cool Readme
blue0513
3
1.4k
Emacs × Sound やってみた / Emacs with Sound
blue0513
2
690
Slack を TweetDeck にしてみた件 / slackdeck-proto
blue0513
1
2.4k
知ってるとお得な iTerm2 と zsh/bash の小技
blue0513
3
610
クラウド電子カルテを支える魂の技術
blue0513
0
2k
Other Decks in Programming
See All in Programming
Hasura の Relationship と権限管理
karszawa
0
180
AWSとCPUのムフフな関係
cmdemura
0
480
量子コンピュータ時代のプログラミングセミナー / 20221222_Amplify_seminar _route_optimization
fixstars
0
250
監視せなあかんし、五大紙だけにオオカミってな🐺🐺🐺🐺🐺
sadnessojisan
2
1.5k
Remote SSHで行うVS Codeリモートホスト開発とトラブルシューティング
smt7174
1
500
LIFFで動く割り勘アプリTATEKAをリリースしてみた話
inoue2002
0
260
Cloudflare Workersと状態管理
chimame
3
490
[2023년 1월 세미나] 데이터 분석가 되면 어떤 일을 하나요?
datarian
0
610
tidy_rpart
bk_18
0
600
Rによる大規模データの処理
s_uryu
2
640
Cloudflare WorkersでGoを動かすライブラリを作っている話
syumai
1
320
Hatena Engineer Seminar #23「新卒研修で気軽に『ありがとう』を伝え合える Slack アプリを開発した話」
slashnephy
0
350
Featured
See All Featured
Bootstrapping a Software Product
garrettdimon
299
110k
Distributed Sagas: A Protocol for Coordinating Microservices
caitiem20
318
19k
jQuery: Nuts, Bolts and Bling
dougneiner
57
6.6k
The Art of Programming - Codeland 2020
erikaheidi
36
11k
The Power of CSS Pseudo Elements
geoffreycrofte
52
4.3k
Three Pipe Problems
jasonvnalue
89
8.9k
Scaling GitHub
holman
453
140k
Fashionably flexible responsive web design (full day workshop)
malarkey
396
63k
Practical Orchestrator
shlominoach
178
8.9k
Why You Should Never Use an ORM
jnunemaker
PRO
49
7.9k
A designer walks into a library…
pauljervisheath
199
16k
Visualizing Your Data: Incorporating Mongo into Loggly Infrastructure
mongodb
32
6.7k
Transcript
ຊ൪ӡ༻த Oracle Λഁյͯ͠͠·ͬͨ ~ ഁյͱੈ ~ @blue_1617
ࢲ୭ʁ blue (@blue_1617) https://blue0513.github.io/show_room/ 2 ࠷ۙϋϚ͍ͬͯΔͷ ‣ ϚΠϯΫϥϑτʢSwitch ൛ʣ ‣
ϐΫϛϯ3 ‣ ΑΙ͜ͷ˓˓Ͱ˓˓ੜ׆
͜ͷʁ 3 https://speakerdeck.com/blue0513/on-premises2on-premises άϧʔϓاۀͷ“ΦϯϓϨ to ΦϯϓϨͨ͠” ͷޙஊ 1. Oracle DB
Λ֎ઌ͔ΒάϧʔϓاۀʹҠߦ 2. ӡ༻։࢝ 3. ͓ɺOracle ͷ༷ࢠ͕….. <= ͔͜͜Βͷ͓
࣍ 4 ୈ1࣍εʔύʔ Oracle Ҡߦ࡞ઓ ~ ഁյἫ ~ OMR ~
ΦϥΫϧϛεςϦʔௐࠪ൝ ~ ΧΠΪ ϑΝΠφϧήʔϜ Oracle SARABA ~ ੈฤ ~ ·ͱΊ
ୈ1࣍εʔύʔ Oracle Ҡߦ࡞ઓ ~ ഁյἫ ~
ୈ1࣍εʔύʔ Oracle Ҡߦ࡞ઓ ~ ഁյἫ ~ 6 ·ͣɺࣄલʹՔಇத DB Λ
clone ͯ͠४උΛͨ͠ Քಇத DB Clone DB
ୈ1࣍εʔύʔ Oracle Ҡߦ࡞ઓ ~ ഁյἫ ~ 7 Ϩίʔυ: X Sequence:
Y Քಇத DB Clone DB Ϩίʔυ: X Sequence: Y
ୈ1࣍εʔύʔ Oracle Ҡߦ࡞ઓ ~ ഁյἫ ~ 8 Ϩίʔυ: X +
a Sequence: Y + b Քಇத DB Clone DB Ϩίʔυ: X Sequence: Y Clone ޙՔಇΛଓ͚ΔͱɺϨίʔυͳͲʹࠩҟ
ୈ1࣍εʔύʔ Oracle Ҡߦ࡞ઓ ~ ഁյἫ ~ 9 Քಇத DB Clone
DB Ҡߦɺexport / import Λ࣮ߦ $ expdp full=y $ impdp full=y dump file
ୈ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 ͳͲରʹͳ͍ͬͯΔ ͜ΕҰݸͰͯ͑͢Δ͍ͭ͢͝
ୈ1࣍εʔύʔ Oracle Ҡߦ࡞ઓ ~ ഁյἫ ~ 11 Ϩίʔυ: X +
a Քಇத DB Clone DB Ϩίʔυ: X + a ϨίʔυͷҠߦશޭʂʂͬͨͥʂʂ
ΧΠΪ ϑΝΠφϧήʔϜ
ΧΠΪ ϑΝΠφϧήʔϜ 13 ҠߦཌɺΤϥʔ͕ൃੜ Ϩίʔυ id: 9999 Sequence Last Number:
9000ʢϨίʔυʹൺͯখ͍͞ʣ INSERT ͠Α͏ͱ͢ΔͱɺUnique key violation
ΧΠΪ ϑΝΠφϧήʔϜ 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 Ϩίʔυ: X + a Sequence: Y +
b Քಇத DB Clone DB Ϩίʔυ: X + a Sequence: Y + b खಈͰ Sequence Λ Inc ͨ͠
ΧΠΪ ϑΝΠφϧήʔϜ 16 Ϩίʔυ: X + a Sequence: Y +
b Քಇத DB Clone DB Ϩίʔυ: X + a Sequence: Y + b खಈͰ Sequence Λ Inc ͨ͠ ੈͳͯ͜ͱͳ͠ʂʂʂ
ΧΠΪ ϑΝΠφϧήʔϜ 17 ཌिͷձٞʹͯ VPoE CTO ଞͷεΩʔϚେৎͳͷʁ ͛ͳงғؾ͕͢Δ
OMR ~ ΦϥΫϧϛεςϦʔௐࠪ൝ ~
19 Full Export / Improt Mode Ϣʔβʔ͕࡞ͬͨ schema Ҏ֎ͷ system
schema ͬͯ͘ΕΔ Ϩίʔυ: X + a Sequence: Y + b Քಇத DB Clone DB Ϩίʔυ: X + a Sequence: Y System ܥεΩʔϚ System ܥεΩʔϚ OMR ~ ΦϥΫϧϛεςϦʔௐࠪ൝ ~
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 System ܥεΩʔϚ SYSTEM ORDDATA PERFSTAT etc ….. γεςϜཧݖݶ ౷ܭใऔಘ
ϝλσʔλཧ http://www.oracle-wiki.net/premium:startdocsdefaultschemas#toc2 etc…. OMR ~ ΦϥΫϧϛεςϦʔௐࠪ൝ ~
22 System ܥεΩʔϚ SYSTEM ORDDATA PERFSTAT etc ….. γεςϜཧݖݶ ౷ܭใऔಘ
ϝλσʔλཧ http://www.oracle-wiki.net/premium:startdocsdefaultschemas#toc2 etc…. ͜ΕΒ͕ਖ਼ৗʹಈ͔ͳ͍Մೳੑ͕͋Δͱ…….ʁ OMR ~ ΦϥΫϧϛεςϦʔௐࠪ൝ ~
OMR ~ ΦϥΫϧϛεςϦʔௐࠪ൝ ~ 23 Log User ౷ܭใऔಘࣦഊ͠ͱΔͰ ͳΜ͔લʹൺ͍ͯ…… ͜Ε·͍ͣɻͦͷ͏ͪେരൃ͢Δ͔͠Εͳ͍
Oracle SARABA ~ ੈฤ ~
Oracle SARABA ~ ੈฤ ~ 25 աڈͷεφοϓγϣοτ ͔Β෮ݩ Քಇத DB
ຊ൪DBɺաڈʹεφοϓγϣοτΛऔ͍ͬͯͨ ͦΕΛ༻͍ͯ৽͘͠ DB Λ࡞ εφοϓγϣοτͷ࣌Ͱ system ܥεΩʔϚഁյ͞Ε͍ͯͳ͍ ʢ៉ྷͳ DBʣ
Oracle SARABA ~ ੈฤ ~ 26 աڈͷεφοϓγϣοτ ͔Β෮ݩ Քಇத DB
system ܥεΩʔϚ *Ҏ֎* ͷϨίʔυͷΈimport system ܥεΩʔϚ *Ҏ֎* ͷ sequence ͷΈ import جຊઓུ
Oracle SARABA ~ ੈฤ ~ 27 աڈͷεφοϓγϣοτ ͔Β෮ݩ system ܥεΩʔϚ
*Ҏ֎* ͷεΩʔϚΛରʹ͢Δ (system ܥεΩʔϚ៉ྷͳͷͰ no touch ͰϤγ) 1. TRUNCATE table 2. DROP sequence Քಇத DB
Oracle SARABA ~ ੈฤ ~ 28 աڈͷεφοϓγϣοτ ͔Β෮ݩ system ܥεΩʔϚ
*Ҏ֎* ͷεΩʔϚΛରʹ͢Δ (system ܥεΩʔϚ៉ྷͳͷͰ no touch ͰϤγ) 1. TRUNCATE table 2. DROP sequence ςʔϒϧۭ εΩʔϚఆٛଘࡏ Role, Synonym ͦͷ·· Քಇத DB
Oracle SARABA ~ ੈฤ ~ 29 աڈͷεφοϓγϣοτ ͔Β෮ݩ system ܥεΩʔϚ
*Ҏ֎* ͷεΩʔϚΛରʹ͢Δ (system ܥεΩʔϚ៉ྷͳͷͰ no touch ͰϤγ) 3. expdp full=y Քಇத DB ςʔϒϧใ Sequence, Schema etc ͯ͢Λग़ྗ
Oracle SARABA ~ ੈฤ ~ 30 աڈͷεφοϓγϣοτ ͔Β෮ݩ system ܥεΩʔϚ
*Ҏ֎* ͷεΩʔϚΛରʹ͢Δ (system ܥεΩʔϚ៉ྷͳͷͰ no touch ͰϤγ) 4. impdp schemas data_only 5. impdp schemas include=SEQUENCE Քಇத DB ςʔϒϧͷϨίʔυΛ import Sequence object Λ import
Oracle SARABA ~ ੈฤ ~ 31 աڈͷεφοϓγϣοτ ͔Β෮ݩ Քಇத DB
system ܥεΩʔϚ => ͦͷ··ͳͷͰ៉ྷ system ܥ *Ҏ֎ͷ* ςʔϒϧ => σʔλͷΈߋ৽ system ܥ *Ҏ֎ͷ* sequence => ߋ৽
Oracle SARABA ~ ੈฤ ~ 32 ᘳͳ DB ͷ
·ͱΊ
·ͱΊ 34 DB ͷҠߦͳͲͷਖ਼֬ੑ͕ٻΊΒΕΔ࡞ۀͰԲͤͣ ઐՈͷҙݟΛڼ͝͏ ใ࿙ӮۀσʔλഁଛͷΑ͏ͳෆՄٯͳࣄଶͰͳ ͚Εόάमਖ਼ϦΧόϦ͕Ͱ͖Δ͔Βɺམͪண͜͏ ڠྗ͍͍ͯͨͩͨ͠ SRE νʔϜͷํʑɺCTO
͋Γ͕ͱ͏͍͟͝·ͨ͠ :bow: