Oracle基礎(chǔ)知識三講

1、第三講管理ORACLE數(shù)據(jù)庫二零一零年六月目標(biāo)學(xué)習(xí)此章，需要掌握以下知識:啟停 Enterprise Manager Database Control啟停 Oracle Listener啟停 Oracle Database 10g了解數(shù)據(jù)庫日志文件和初始化參數(shù)文件管理架構(gòu)ORACLE10G數(shù)據(jù)庫的管理架構(gòu)由三部分組成：數(shù)據(jù)實例 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.訪問Database ControlSYSOPER 和SYSDBADatabase Home Page改變 Listener 狀態(tài)啟動和停止Click Advanced Options to select startup options and shutdown mode啟動數(shù)據(jù)庫NOMOUNTOPENMOUNTNOMOUNTSHUTDOWNInstance startedSTARTUP啟動數(shù)據(jù)庫MOUNTOPENMOUNTNOMOUNTSHUTDOWNControl file opened for this instanceInsta

5、nce startedSTARTUPSHUTDOWN啟動數(shù)據(jù)庫OPENOPENMOUNTNOMOUNTSHUTDOWNAll files opened as described by the control file for this instanceControl file opened for this instanceInstance startedSTARTUPSHUTDOWN停止數(shù)據(jù)庫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 選項此種方式停止:數(shù)據(jù)緩沖區(qū)的內(nèi)容已經(jīng)寫回數(shù)據(jù)文件未提交事務(wù)進(jìn)行回滾資源被釋放此種方式啟動:無須進(jìn)行實例恢復(fù)During SHUTDOWN NORMALorSHUTDOWN TRANSACTIONALorSHUTDOWN IMMEDIATEConsistent database(clean databa

7、se)SHUTDOWN 選項此種方式停止:更改后的數(shù)據(jù)緩沖區(qū)未寫回數(shù)據(jù)文件未提交事務(wù)未進(jìn)行回滾此種方式啟動:聯(lián)機日志文件用來進(jìn)行前滾回滾段用來進(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ù)庫服

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

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