DBA ã€ã³ã¹ã¿ã³ã¹
ç®æ¬¡
Oracleã®æ§æ
ã» ã¡ã¢ãªäžã®ã€ã³ã¹ã¿ã³ã¹(ã¡ã¢ãªé åãããã»ã¹)
ã» ãã£ã¹ã¯äžã®ããŒã¿ããŒã¹ãã¡ã€ã«(ç©çãã¡ã€ã«)
ããæã
ã€ã³ã¹ã¿ã³ã¹ãšã¯ïŒ
ããŒã¿ããŒã¹ãæ§æãã
ã» ã¡ã¢ãªæ
å ±
ã» ç©çãã¡ã€ã«(ããŒã¿ããŒã¹ãã¡ã€ã«)
ã®å
ã®ã¡ã¢ãªæ
å ±
ã» SGA(ã·ã¹ãã ã°ããŒãã«ãšãªã¢)
ã» ããã¯ã°ã©ãŠã³ãããã»ã¹
ããæã
SGA
ã·ã¹ãã ã°ããŒãã«ãšãªã¢
é«éã¢ã¯ã»ã¹ã®çºã«OracleããŒã¿ããŒã¹ã皌åããã³ã³ãã¥ãŒã¿å
ã«ä¿æãããã¡ã¢ãªé å
è€æ°ã®ãŠãŒã¶ãŒã§å
±æããã
ããŒã¿ããŒã¹ãããã¡ãã£ãã·ã¥
ããŒã¿ãã¡ã€ã«ã®å
容ãä¿æããé å
ããŒã¿ããã¡ã€ã«ã§ãªãã¡ã¢ãªäžã«çœ®ãäºã§åŠçé床ã®åäžãå³ã£ãŠãã
ãµãŒããŒããã»ã¹ã¯æåã«ããããèªã¿æžãã(éã)ãç¡ãå Žåã«ããŒã¿ãã¡ã€ã«ãæäœãã(é
ã)ã
æŽæ°å
容ã¯DBWnããã»ã¹ã«ããããŒã¿ãã¡ã€ã«ãžæžã蟌ãŸããã
å ±æããŒã«
ã» SQLæè§£æçµæ
ã» SQLæå®è¡èšç»
ã» ããŒã¿ãã£ã¯ã·ã§ããªæ
å ±
ããOracle ããŒã¿ãã£ã¯ã·ã§ããªã»ãã¥ãŒãåç
§
REDOãã°ãããã¡
REDOæ
å ±ãä¿æããé å
REDOæ
å ± ïŒ ããŒã¿ããŒã¹ãžã®å€æŽæ
å ±
(Insertçã®DMLæãCommit/RollBackçãå«ã)
commitæãäžå®æéçµéæçã«ã
REDOãã°ãããã¡
â
REDOãã°ãã¡ã€ã« ãžã
LGWRããã»ã¹ã«ãã£ãŠæžã蟌ãŸããã
ãã®åŸREDOãã°ãããã¡ã¯ã¯ãªã¢ããã
ã©ãŒãžããŒã«
ãªãã·ã§ã³
ã» å
±æãµãŒããŒæã®ã»ãã·ã§ã³æ
å ±
ã» RMANäœ¿çšæã®ããŒã¿
JavaããŒã«
ãªãã·ã§ã³
JVMã«å«ãŸããã»ãã·ã§ã³åºæã®ããŒã¿ãä¿æ
JAVAã¹ãã¢ãããã·ãŒãžã£å®è¡æã®JAVAã®æ
å ±
ããã¯ã°ã©ãŠã³ãããã»ã¹
ããã»ã¹å | æ©èœ | è£è¶³ |
---|---|---|
SMON | ãµãŒããŒããã»ã¹ç£èŠ ã€ã³ã¹ã¿ã³ã¹ãªã«ã㪠|
|
PMON | ãŠãŒã¶ãŒããã»ã¹ç®¡ç ããŒã«ãã㯠|
|
DBWn | DBãããã¡ãã£ãã·ã¥âããŒã¿ãã¡ã€ã«ãžæžèŸŒã¿ | |
LGWR | REDOãã°ãããã¡âREDOãã°ãã¡ã€ã«ãžæžèŸŒã¿ | |
CKPT | DBWnãžå®è¡æç€º | |
ARCn | REDOãã°ãã¡ã€ã«âArchiveãã°ãã¡ã€ã«ãäœæ | |
RMAN | ããã¯ã¢ãã/ãªã«ããªããŒã« | â ããã¯ã°ã©ãŠã³ãããã»ã¹ |
MMON | ã¢ã©ãŒãéç¥ | |
SMON
ã·ã¹ãã ã¢ãã¿ãŒ
(1)éèŠãªã¿ã¹ã¯
ãã» ã€ã³ã¹ã¿ã³ã¹ãªã«ããª
ãã(ãµãŒããŒåŽã®é害æâ PMON)
ãã» ç¡å¹ã«ãªã£ããã©ã³ã¶ã¯ã·ã§ã³ã®ãªã«ããªç
(2)ã¡ã³ããã³ã¹ã»ã¿ã¹ã¯
ãã» äžæé åã®åå©çš
ãã» ããŒã¿ã»ãã£ã¯ã·ã§ããªã®ã¯ãªãŒã³ã¢ãã
ãã» UNDO衚é åã®ç®¡çç
ãå®è¡
PMON
ããã»ã¹ã¢ãã¿ãŒ
ã» ãŠãŒã¶ãŒããã»ã¹
ã(ã¯ã©ã€ã¢ã³ãåŽã®é害æâ SMON)
ã» ãã£ã¹ãããã£ããã»ã¹
ãç°åžžçµäºããå Žåã«ããã»ã¹ã®ãªã«ããªãå®è¡
ã»ããŒã¿ããŒã¹ãããã¡ãã£ãã·ã¥ã®ã¯ãªãŒã³ã¢ãã
ã»ãªãœãŒã¹è§£æŸ(ããŒã«ããã¯ãããã¯è§£é€)
ã»ããã»ã¹ããã£ã¹ãããã£åèµ·å
DBWn
ããŒã¿ããŒã¹ã©ã€ã¿ãŒããã»ã¹
ããŒã¿ããŒã¹ãããã¡ãã£ãã·ã¥
â
ããŒã¿ãã¡ã€ã«
ãžæžã蟌ãã
æžã蟌ãã¿ã€ãã³ã°ã¯CKPTããã»ã¹ãæç€ºãã
LGWR
ãã°ã©ã€ã¿ãŒããã»ã¹
REDOãã°ãããã¡
â
REDOãã°ãã¡ã€ã«
ãžæžã蟌ãã
ãREDOãã°ãããã¡ãåç
§
CKPT
ãã§ãã¯ãã€ã³ãããã»ã¹
ãã§ãã¯ãã€ã³ãã§DBWnã«ã·ã°ãã«ãéã
ããŒã¿ããŒã¹ã®å
šãŠã®ããŒã¿ãã¡ã€ã«ãšå¶åŸ¡ãã¡ã€ã«ãæŽæ°
ææ°ã®ãã§ãã¯ãã€ã³ãã瀺ã
ARCn
ã¢ãŒã«ã€ãããã»ã¹
ã» REDOãã°ã»ãã¡ã€ã«ãäžæ¯ã«ãªã
ã» ãªã³ã©ã€ã³REDOãã°ã®åæ¿ããçºç
ã®å Žåã«ã
REDOãã°ãã¡ã€ã«
â
ã¢ãŒã«ã€ãèšæ¶é å
ãžã³ããŒãã
RMAN
Recovery Manager
ã» ããã¯ã¢ãã
ã» ãªã¹ãã¢
ã» ãªã«ããª
çšOracleæšæºããŒã«
以äžã®æ¹æ³ã§èµ·åã»æäœå¯èœ
ã» OSããèµ·åãã³ãã³ãã©ã€ã³ã§æäœ
ã» EMã§æäœ(ããã¯ã°ã©ãŠã³ãã§èµ·åã»å®è¡ããã)
RMANã䜿çšããã«OSã®æ©èœã ãã§ã®ããã¯ã¢ããããªã¹ãã¢ããªã«ããªãšæ¯èŒããŠã
ã» ããã¯ã¢ããïŒäžä»£ç®¡çãäžèŠããã¯ã¢ããã®èªååé€
ã» ãªã¹ãã¢ïŒå¿
èŠãªããã¯ã¢ãããã¡ã€ã«ã®èªåååŸ
çã®ã¡ãªãããããã
ãDBA ããã¯ã¢ãããåç
§
ãDBA ãªã«ããªãåç
§
MMON
ãããŒãžã¡ã³ãã»ã¢ãã¿ãŒ
ã»çµ±èšæ
å ±ã®ååŸ
ã»åå€ã«å¯ŸããŠããããå€(ã¡ããªãã¯)ãè¶
ããå Žåã®ã¢ã©ãŒãéç¥åŠç
衚é åã®äžè¶³ç
ããŒã¿ããŒã¹èµ·åã®æµã
ç¶æ | 説æ | å¿ èŠãªãã¡ã€ã« | SQLã³ãã³ã |
---|---|---|---|
Open â |
䜿çšå¯èœ |
ã» REDOãã°ãã¡ã€ã« ã» ããŒã¿ãã¡ã€ã« |
STARTUP ALTER DATABASE OPEN |
Mount â |
ããŒã¿ããŒã¹ãããŠã³ã Oracleãç©çæ§æãä¿æ |
ã» å¶åŸ¡ãã¡ã€ã« |
STARTUP MOUNT ALTER DATABASE MOUNT |
NoMount â |
ã€ã³ã¹ã¿ã³ã¹èµ·å |
ã» PFile ã» SPFile |
STARTUP NOMOUNT |
ShutDown | 忢 | ãåæãåç § |
ã€ã³ã¹ã¿ã³ã¹ã®ç¶æ
ã確èªããSQLã¯ãOracle ããŒã¿ãã£ã¯ã·ã§ããªã»ãã¥ãŒ/ã€ã³ã¹ã¿ã³ã¹ãåç
§
èµ·åã«å¿
èŠãªæš©éã«ã€ããŠã¯ãSQL DCLïŒãŠãŒã¶ãŒãããŒã«ãæš©é/ã·ã¹ãã æš©éãåç
§
ããã»ã¹
â»å³ã¯RACå©çšæã§ãè€æ°ã€ã³ã¹ã¿ã³ã¹ãè€æ°DBãã¡ã€ã«ã䜿çš
ãŠãŒã¶ãŒããã»ã¹
ãŠãŒã¶ã®æäœãåãä»ãããµãŒããŒããã»ã¹ã«å¯ŸããŠãªã¯ãšã¹ããéä¿¡ãã
ãµãŒããŒããã»ã¹
ã» ãŠãŒã¶ãŒããã»ã¹ããã®ãªã¯ãšã¹ããå®è¡
ã» å®è¡çµæããŠãŒã¶ãŒããã»ã¹ãžã¬ã¹ãã³ã¹
å°çšãµãŒããŒæ¥ç¶
tnsnames.oraïŒSERVER=SHARED
å
±æãµãŒããŒã«æ¥ç¶ããªã
ã¯ã©ã€ã¢ã³ãããã®æ¥ç¶(ã»ãã·ã§ã³)ã«å¯ŸããŠ1ã€ã®ãµãŒãããã»ã¹ãå²ãåœãŠããã
é«é
å°æ°ã®ãŠãŒã¶ã鷿鿥ç¶ãç¶æãããããåŠçåã
倿°ã®ãŠãŒã¶ãé »ç¹ã«æ¥ç¶ïœåæãç¹°ãè¿ãã¢ããªã§ã¯é
ã
(Webã·ã¹ãã ã§ã¯æ¥ç¶ããŒãªã³ã°æ©èœã«ããåé¿å¯èœ)
UNIXç³»
ããŒã¿ããŒã¹ãžã®æ¥ç¶ïœåæã1ããã»ã¹
Windowsç³»
ããŒã¿ããŒã¹ãžã®æ¥ç¶ïœåæã1ã¹ã¬ãã
å ±æãµãŒããŒæ¥ç¶
tnsnames.oraïŒSERVER=SHARED
å
±æãµãŒããŒã«æ¥ç¶ã§ããªãå Žåã¯ãšã©ãŒ
åžžæè€æ°ã®ãµãŒããŒããã»ã¹ãåŸ
æ©ããŠããã
ãã£ã¹ãããã£ãšããåä»ãµãŒãã¹ãã¯ã©ã€ã¢ã³ãããã®èŠæ±ã空ããŠãããµãŒãããã»ã¹ãžæ¯ãåãã
ãã«ãã¹ã¬ãããµãŒããŒ(MTS:Multi Thread Server)æ¥ç¶ãšãåŒã°ããã
ãµãŒãã®ãªãœãŒã¹ãå¹ççã«äœ¿çšã§ããåé¢ãé床ã¯å°çšãµãŒãã«å£ã
PGA
ããã°ã©ã ã°ããŒãã«é å
ãµãŒããŒããã»ã¹ã«å¯ŸããŠå²ãåœãŠãããã
ã» ãã©ã€ããŒãSQLé å
ã ã»ãã·ã§ã³æ¯ã®ã«ãŒãœã«ããã€ã³ãæ
å ±
ã» ã»ãã·ã§ã³ã¡ã¢ãªãŒ
ã ã»ãã·ã§ã³æ¯ã®ãã°ã€ã³æ
å ±
ã» SQLäœæ¥é å
ã SQLæã§äœ¿çšãããœãŒããçµåæ
å ±ãæãå€ãæ¶è²»ããæã
ããæãã
ä»ã®ãµãŒããŒããã»ã¹ãšã¯å
±æãããªãã
è€æ°ã®ãµãŒããŒããã»ã¹ãèµ·åããŠããå Žåãè€æ°ã®PGAãèµ·åããã
åã
ã®PGAã®éåã¯ã
ã€ã³ã¹ã¿ã³ã¹PGA(åèšã€ã³ã¹ã¿ã³ã¹PGA)
ãšåŒã°ããã
UGA
User Global Area
SQLå®è¡ã®éããœãŒããçµåæã«äœ¿çšãããã¡ã¢ãªé å
ã» å°çšãµãŒãæ¥ç¶æïŒPGAããå²ãåœãŠ
ã» å
±æãµãŒãæ¥ç¶æïŒã©ãŒãžããŒã«ããå²ãåœãŠ
ã¡ã¢ãªç®¡ç
èªåã¡ã¢ãªãŒç®¡ç
SGAãPGAã®åèšã¡ã¢ãªãµã€ãºãæå®ãããšã
è² è·ã«å¿ããŠSGAãPGAã«å¯ŸããŠåçã«ã¡ã¢ãªå®¹éãå²ãåœãŠããã
PGAãè€æ°ããå Žåãåæ§ã«åçã«å²ãåœãŠããã
æåã¡ã¢ãªãŒç®¡ç
ã» èªåå
±æã¡ã¢ãªãŒç®¡ç: SGAçš
ã æå€§ã¡ã¢ãªå®¹éãèšå®ãããšãåçã«ã¡ã¢ãªå®¹éã調æŽããã
ã» æåå
±æã¡ã¢ãªãŒç®¡ç: SGAçš
ã æåã§ã¡ã¢ãªå®¹éãèšå®ãã
ã» èªåPGAã¡ã¢ãªãŒç®¡ç
ã æå€§ã¡ã¢ãªå®¹éãèšå®ãããšãåçã«ã¡ã¢ãªå®¹éã調æŽããã
ã» æåPGAã¡ã¢ãªãŒç®¡ç
ã æåã§ã¡ã¢ãªå®¹éãèšå®ãã
ãããã
ã¡ã¢ãªã¢ããã€ã¶
æé©ãªã¡ã¢ãªãŒãµã€ãºãéžæã§ããã°ã©ãã£ã«ã«ããŒã«
Oracle ãšã³ã¿ãã©ã€ãºãããŒãžã£ãŒäœ¿ç𿹿³åç
§
管çã¢ãŒã | ã¢ããã€ã¶ |
---|---|
èªåã¡ã¢ãªãŒç®¡ç | ã¡ã¢ãªãŒã¢ããã€ã¶ |
èªåå
±æã¡ã¢ãªãŒç®¡ç ïŒ èªåPGAã¡ã¢ãªãŒç®¡ç (èªåã¡ã¢ãªãŒç®¡çïŒç¡å¹) |
SGAã¢ããã€ã¶ PGAã¢ããã€ã¶ |
æåå
±æã¡ã¢ãªãŒç®¡ç ïŒ èªåPGAã¡ã¢ãªãŒç®¡ç (èªåã¡ã¢ãªãŒç®¡çïŒç¡å¹ / èªåå ±æã¡ã¢ãªãŒç®¡çïŒç¡å¹) |
PGAã¢ããã€ã¶ å ±æããŒã«ã¢ããã€ã¶ ãããã¡ãã£ãã·ã¥ã¢ããã€ã¶ |
ãOracle ãšã³ã¿ãã©ã€ãºãããŒãžã£ãŒäœ¿ç𿹿³â¡ãµãŒããŒ/ã¡ã¢ãªãŒã»ã¢ããã€ã¶ãåç §
åè
REDO/UNDO
REDO
REDOæ
å ± ïŒ ããŒã¿ããŒã¹ãžã®å€æŽæ
å ±
DDLæãCOMMIT/ROLLBACKåœä»€çãå«ãŸãã
ä¿åå
ïŒREDOãã°ãããã¡ â REDOãã°ãã¡ã€ã«
UNDO
UNDOæ
å ± ïŒ å€æŽå(COMMITå)ããŒã¿
ROLLBACKã®çºã«äœ¿çš
ã»å€æŽãçºçããåºŠã«æŽæ°ãããïŒCOMMITããããŸã§ã¯ããŒã¿ãä¿éããã
ã»ä¿åå
ïŒUNDO衚é å
UNDO衚é åãäžè¶³ãããšãã¹ãããã·ã§ãããå€ãããŸãããšã©ãŒã衚瀺ããã
ã»UNDOä¿åïŒã³ãããåŸã®ä¿åæé
ãã©ã¡ãŒã¿ïŒUNDO_RETENTIONã§æå®(èŠå®ïŒ900)