Oracle基礎(chǔ)知識(shí)三講

1、第三講管理ORACLE數(shù)據(jù)庫(kù)二零一零年六月目標(biāo)學(xué)習(xí)此章，需要掌握以下知識(shí):啟停 Enterprise Manager Database Control啟停 Oracle Listener啟停 Oracle Database 10g了解數(shù)據(jù)庫(kù)日志文件和初始化參數(shù)文件管理架構(gòu)ORACLE10G數(shù)據(jù)庫(kù)的管理架構(gòu)由三部分組成：數(shù)據(jù)實(shí)例 Listener管理工具 Database ControlManagement agent (when using Grid Control)ListenerDatabase ControlManagementagentManagement interface-or-啟

2、停 Database Control$ emctl start dbconsoleTZ set to US/PacificOracle Enterprise Manager 10g Database Control Release .0Copyright (c) 1996, 2004 Oracle Corporation. All rights reserved.:5500/em/console/aboutApplicationStarting Oracle Enterprise Manager 10g Database Control started.Logs are generated i

3、n directory /u01/app/oracle/product/10.1.0/db_1/_orcl/sysman/log$ emctl stop dbconsoleTZ set to US/PacificOracle Enterprise Manager 10g Database Control Release .0Copyright (c) 1996, 2004 Oracle Corporation. All rights reserved.:5500/em/console/aboutApplicationStopping Oracle Enterprise Manager 10g

4、Database Control . . Stopped.訪問(wèn)Database ControlSYSOPER 和SYSDBADatabase Home Page改變 Listener 狀態(tài)啟動(dòng)和停止Click Advanced Options to select startup options and shutdown mode啟動(dòng)數(shù)據(jù)庫(kù)NOMOUNTOPENMOUNTNOMOUNTSHUTDOWNInstance startedSTARTUP啟動(dòng)數(shù)據(jù)庫(kù)MOUNTOPENMOUNTNOMOUNTSHUTDOWNControl file opened for this instanceInsta

5、nce startedSTARTUPSHUTDOWN啟動(dòng)數(shù)據(jù)庫(kù)OPENOPENMOUNTNOMOUNTSHUTDOWNAll files opened as described by the control file for this instanceControl file opened for this instanceInstance startedSTARTUPSHUTDOWN停止數(shù)據(jù)庫(kù)Shutdown mode:A = ABORTI = IMMEDIATET = TRANSACTIONALN = NORMALANoNoNoNoTNoNoYesYesINoNoNoYesShutdown

6、 ModeAllow new connectionsWait until current sessions end Wait until current transactions endForce a checkpoint and close filesNNoYesYesYesSHUTDOWN 選項(xiàng)此種方式停止:數(shù)據(jù)緩沖區(qū)的內(nèi)容已經(jīng)寫回?cái)?shù)據(jù)文件未提交事務(wù)進(jìn)行回滾資源被釋放此種方式啟動(dòng):無(wú)須進(jìn)行實(shí)例恢復(fù)During SHUTDOWN NORMALorSHUTDOWN TRANSACTIONALorSHUTDOWN IMMEDIATEConsistent database(clean databa

7、se)SHUTDOWN 選項(xiàng)此種方式停止:更改后的數(shù)據(jù)緩沖區(qū)未寫回?cái)?shù)據(jù)文件未提交事務(wù)未進(jìn)行回滾此種方式啟動(dòng):聯(lián)機(jī)日志文件用來(lái)進(jìn)行前滾回滾段用來(lái)進(jìn)行未提交事務(wù)的回滾資源被釋放During SHUTDOWN ABORTorInstance FailureorSTARTUP FORCEInconsistent database(dirty database)spfiledb01.ora初始化參數(shù)文件初始化參數(shù)文件（續(xù)）一般放在$ORACLE_HOME/dbs(Unix)或$ORACLE_HOME/database(Windows)下spfile文件名默認(rèn)為spfile.ora二進(jìn)制文件，可由數(shù)據(jù)庫(kù)服

8、務(wù)器寫入或讀取，不能手動(dòng)進(jìn)行編輯。數(shù)據(jù)庫(kù)啟動(dòng)時(shí)首選spfilepfile文件名默認(rèn)為init.ora文本文件，數(shù)據(jù)庫(kù)服務(wù)器可以讀取但不能寫入。如果啟動(dòng)時(shí)沒有找到spfile，則自動(dòng)搜索此文件。對(duì)pfile的修改在下次數(shù)據(jù)庫(kù)重啟時(shí)生效。初始化參數(shù)基本初始化參數(shù)大多數(shù)情況下，只需要設(shè)置和優(yōu)化32個(gè)基本參數(shù)便可獲得合理性能CONTROL_FILES、DB_BLOCK_SIZE、DB_CACHE_SIZE、DB_FILES、PGE_AGGREGATE_TARGENT、SHARED_POOL_SIZE、PROCESSES等。高級(jí)初始化參數(shù)除基本參數(shù)外的其他參數(shù)一般采用默認(rèn)值即可初始化參數(shù)（續(xù)）動(dòng)態(tài)初始化

9、參數(shù)能使用alter session或者alter system進(jìn)行修改。修改之后立即生效，無(wú)需重啟數(shù)據(jù)庫(kù)靜態(tài)初始化參數(shù)修改之后需要重啟數(shù)據(jù)庫(kù)才能生效查看初始化參數(shù)告警日志文件文件名為alert_.log保存在參數(shù)下BACKGROUND_DUMP_DEST指定的目錄下日志文件按時(shí)間順序列出消息日志和錯(cuò)誤日志：?jiǎn)?dòng)時(shí)使用的所有非默認(rèn)初始化參數(shù)發(fā)生的所有內(nèi)部錯(cuò)誤（ORA- 600）、塊損壞錯(cuò)誤（ORA-1578）和死鎖錯(cuò)誤（ORA- 60）管理操作、EM或SQL Plus語(yǔ)句startup、shutdown、archive log和recover自動(dòng)刷新實(shí)體化視圖過(guò)程中發(fā)生的錯(cuò)誤查看告警日志文件跟蹤文件（.trc）后臺(tái)跟蹤文件（background trace file）用來(lái)記錄后臺(tái)進(jìn)程所檢測(cè)到的錯(cuò)誤保存在參數(shù)下BACKGROUND_DUMP_DEST指定的目錄下用戶跟蹤文件（user trace file）包含了所跟蹤的SQL語(yǔ)句的統(tǒng)計(jì)信息和用戶進(jìn)程的錯(cuò)誤信息保