Oracle基礎(chǔ)知識三講

1、第三講管理ORACLE數(shù)據(jù)庫二零一零年六月目標學習此章，需要掌握以下知識:啟停 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ù)文件未提交事務進行回滾資源被釋放此種方式啟動:無須進行實例恢復During SHUTDOWN NORMALorSHUTDOWN TRANSACTIONALorSHUTDOWN IMMEDIATEConsistent database(clean databa

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

8、務器寫入或讀取，不能手動進行編輯。數(shù)據(jù)庫啟動時首選spfilepfile文件名默認為init.ora文本文件，數(shù)據(jù)庫服務器可以讀取但不能寫入。如果啟動時沒有找到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ù)一般采用默認值即可初始化參數(shù)（續(xù)）動態(tài)初始化

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