Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
本番運用中 Oracle を破壊してしまった話 ~ 破壊と創世 ~ / Oracle Des...
Search
Taiju Aoki
November 20, 2020
Programming
0
2.7k
本番運用中 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.8k
GitもCIもテストもないプロジェクトでデプロイを自動化するまでの道 / rebuild of DevOps
blue0513
0
3k
Git & GitLab & コードレビューって? / about Git, GitLab, CodeReview
blue0513
0
190
ElectronでSlackをさらに便利にしちゃう / Slack with Electron
blue0513
3
750
GitHub の README をいい感じにする / Cool Readme
blue0513
3
1.6k
Emacs × Sound やってみた / Emacs with Sound
blue0513
1
920
Slack を TweetDeck にしてみた件 / slackdeck-proto
blue0513
0
2.8k
知ってるとお得な iTerm2 と zsh/bash の小技
blue0513
2
770
クラウド電子カルテを支える魂の技術
blue0513
0
2.3k
Other Decks in Programming
See All in Programming
tsconfig.jsonの最近の新機能 ファイルパス編
uhyo
6
1.4k
Subclassing, Composition, Python, and You
hynek
3
110
Micro Frontends for Java Microservices - dev2next 2024
mraible
PRO
0
190
2024-10-02 dev2next - Application Observability like you've never heard before
jonatan_ivanov
0
150
NEWTにおけるiOS18対応の進め方
ryu1sazae
0
180
App Router 悲喜交々
quramy
7
370
Beyond the RuboCop Defaults
koic
2
480
AWS CDKを用いたセキュアなCI/CDパイプラインの構築 / Build a secure CI/CD pipeline using AWS CDK
seike460
PRO
3
500
Taking LLMs out of the black box: A practical guide to human-in-the-loop distillation
inesmontani
PRO
3
1.1k
いまあるチームにフィットさせる Serverless そして Platform Engineeringへの挑戦 / Serverless Fits the Team You Have and Platform Engineering
seike460
PRO
2
1.3k
Vue :: Better Testing 2024
up1
1
370
実践Dash - 手を抜きながら本気で作るデータApplicationの基本と応用 / Dash for Python and Baseball
shinyorke
2
220
Featured
See All Featured
The Power of CSS Pseudo Elements
geoffreycrofte
71
5.3k
For a Future-Friendly Web
brad_frost
174
9.3k
Templates, Plugins, & Blocks: Oh My! Creating the theme that thinks of everything
marktimemedia
26
2k
How to name files
jennybc
77
98k
Reflections from 52 weeks, 52 projects
jeffersonlam
346
20k
The Language of Interfaces
destraynor
154
24k
Imperfection Machines: The Place of Print at Facebook
scottboms
263
13k
A Tale of Four Properties
chriscoyier
155
22k
In The Pink: A Labor of Love
frogandcode
139
22k
KATA
mclloyd
27
13k
Web development in the modern age
philhawksworth
205
10k
Designing Experiences People Love
moore
138
23k
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: