Oracle數(shù)據(jù)庫整體架構(gòu)及啟停介紹

1、技術(shù)創(chuàng)新，變革未來Oracle數(shù)據(jù)庫整體架構(gòu)及啟停介紹數(shù)據(jù)庫啟動(dòng)與關(guān)閉4數(shù)據(jù)庫物理結(jié)構(gòu)2數(shù)據(jù)庫邏輯結(jié)構(gòu)31數(shù)據(jù)庫創(chuàng)建過程概念性知識(shí)-數(shù)據(jù)庫upgradeCritical Patch Update (CPU)now refers to the overall release of security fixes each quarter rather than the cumulative database security patch for the quarter. Think of the CPU as the overarching quarterly release and not as

2、 a single patch.Patch Set Updates (PSU)are the same cumulative patches that include both the security fixes and priority fixes. The key with PSUs is they are minor version upgrades (e.g., 11.2.0.1.1 to 11.2.0.1.2). Once a PSU is applied, only PSUs can be applied in future quarters until the database

3、 is upgraded to a new base version.Security Patch Update (SPU)terminology is introduced in the October 2012 Critical Patch Update as the term for the quarterly security patch. SPU patches are the same as previous CPU patches, just a new name. For the database, SPUs can not be applied once PSUs have

4、been applied until the database is upgraded to a new base version.Bundle Patches are the quarterly patches for Windows and Exadata which include both the quarterly security patches as well as recommended fixes.Patch就是早期大家常說的PSRPSR。這是在主版本號(hào)上發(fā)布的補(bǔ)丁集，修復(fù)了較多的Bug，可能會(huì)包 含一些增強(qiáng)功能（Enhancement）。比如11.2.0.1是一個(gè)主版本，那

5、么11.2.0.2、11.2.0.3就是2個(gè)不 同的Patch set。這種補(bǔ)丁集經(jīng)過了嚴(yán)格的集成測(cè)試，也是累積型的。所以推薦安裝最新的Patch SetOracle-數(shù)據(jù)庫Patch概念性小常識(shí).pdfQuick Reference to Patch Numbers for Database PSU, SPU(CPU), BundlePatches and Patchsets (文檔 ID 1454618.1)概念性知識(shí)-數(shù)據(jù)庫版本主機(jī)A主機(jī)BRAC架構(gòu)SAN交換機(jī)千兆交換機(jī)存儲(chǔ)網(wǎng)絡(luò)2主機(jī)B存儲(chǔ)千兆交換機(jī)單機(jī)架構(gòu)概念性知識(shí)-數(shù)據(jù)庫硬件架構(gòu)Primary ComponentsSGARedo l

6、og buffer cacheShared poolLibrary cacheData Dict.cacheInstancePMONSMONDBWRLGWRCKPTOthersUser processServer processPGAControl filesData filesArchived log filesParameter filePasswordfileRedo log filesDatabaseDatabase buffer cacheOracle InstanceAn Oracle instance:Is a means to access an Oracle database

7、Always opens one and only one databaseConsists of memory and process structuresBackgroundstructuresMemory structuresSGARedo logbuffer cacheDatabasebuffer cacheShared poolLibrary cacheData Dictionary cacheInstancePMONSMONDBWRLGWRCKPTOthersOracle DatabaseAn Oracle database:Is a collection of data that

8、 is treated as a unitConsists of three file typesControl filesData filesArchivedlog filesParameter filePasswordfileRedo log filesOracle Database正確創(chuàng)建一個(gè)數(shù)據(jù)庫安裝數(shù)據(jù)庫軟件創(chuàng)建監(jiān)聽DBCA建庫（手工建庫）最優(yōu)化參數(shù)調(diào)整系統(tǒng)監(jiān)控部署創(chuàng)建一個(gè)數(shù)據(jù)庫軟件安裝一、環(huán)境確認(rèn)，參數(shù)調(diào)整根據(jù)不同的主機(jī)調(diào)整相應(yīng)的參數(shù)創(chuàng)建基本的安裝用戶確認(rèn)主機(jī)包及補(bǔ)丁安裝完全確認(rèn)節(jié)點(diǎn)時(shí)區(qū)，時(shí)間是否正確確認(rèn)交換空間，內(nèi)存，CPU是否符合要求確認(rèn)多路徑是否配置完全，磁盤狀態(tài)是否正常二、

9、圖形界面調(diào)用，安裝軟件確認(rèn)安裝版本確認(rèn)安裝路徑創(chuàng)建一個(gè)數(shù)據(jù)庫建庫一、注意以下要點(diǎn)數(shù)據(jù)庫名字符集內(nèi)存大小設(shè)置連接數(shù)連接模式二、創(chuàng)建模式DBCA建庫手工建庫創(chuàng)建一個(gè)數(shù)據(jù)庫最優(yōu)化參數(shù)調(diào)整密碼過期參數(shù)密碼錯(cuò)誤登錄次數(shù)密碼大小寫敏感密碼錯(cuò)誤延遲登錄Oracle審計(jì)參數(shù)延遲段創(chuàng)建直接路徑讀SCN參數(shù)優(yōu)化內(nèi)存參數(shù)優(yōu)化REDO大小優(yōu)化創(chuàng)建一個(gè)數(shù)據(jù)庫系統(tǒng)監(jiān)控部署OSWatch部署AWR策略調(diào)整AWR基線創(chuàng)建數(shù)據(jù)庫啟動(dòng)與關(guān)閉4數(shù)據(jù)庫物理結(jié)構(gòu)2數(shù)據(jù)庫邏輯結(jié)構(gòu)31數(shù)據(jù)庫創(chuàng)建過程數(shù)據(jù)庫物理結(jié)構(gòu)參數(shù)文件:$ORACLE_HOME/dbs/spfilesid.ora密碼文件:$ORACLE_HOME/dbs/orapwsi

10、d控制文件:數(shù)據(jù)文件同目錄/control0 x.ctl數(shù)據(jù)文件:存放真實(shí)數(shù)據(jù)日志文件:記錄對(duì)數(shù)據(jù)所作的修改歸檔文件:在線日志的歸檔數(shù)據(jù)庫物理結(jié)構(gòu)參數(shù)文件SGARedo log bufferData buffer cacheShared poolLibrary cacheData dict. cacheOracle InstancePMONSMONDBW0LGWRCKPTOthersspfiledb01.oraInitialization Parameter FilesSQL CONNECT / AS SYSDBA SQL STARTUP數(shù)據(jù)庫物理結(jié)構(gòu)參數(shù)文件PFILE Example# In

11、itialization Parameter File: initdb01.oradb_name instance_name control_files= db01= db01= ( /u03/oradata/db01/control01db01.ctl,/u03/oradata/db01/control02db01.ctl)db_block_size db_block_buffers shared_pool_size= 4096= 500= 31457280 # 30M Shared Pooldb_files= 1024max_dump_file_size= 10240background_

12、dump_dest = /u05/oracle9i/admin/db01/bdump= /u05/oracle9i/admin/db01/udump= /u05/oracle9i/admin/db01/cdump= auto= undtbsuser_dump_dest core_dump_dest undo_management undo_tablespace. . .數(shù)據(jù)庫物理結(jié)構(gòu)密碼文件oracle的口令文件的作用是存放所有以sysdba或者sysoper權(quán)限連接數(shù)據(jù)庫的用戶的口令，如果想以sysdba權(quán)限遠(yuǎn)程連接 數(shù)據(jù)庫，必須使用口令文件，否則不能連上，由于sys用戶在連 接數(shù)據(jù)庫時(shí)必須

13、以sysdba or sysoper方式，也就是說sys用 戶要想連接數(shù)據(jù)庫必須使用口令文件數(shù)據(jù)庫物理結(jié)構(gòu)控制文件Control FileThe control file is a binary file that defines the current state of the physical database.Loss of the control file requires recoveryIs read at MOUNT stageIs required to operateIs linked to a single databaseShould be multiplexedMain

14、tains integrity of databaseDatabaseControl files數(shù)據(jù)庫物理結(jié)構(gòu)控制文件使用控制文件的原則使用控制文件時(shí)應(yīng)該:-對(duì)控制文件進(jìn)行多元化處理-在初始化參數(shù) CONTROL_FILES中指定控制文件的完整路徑-當(dāng)數(shù)據(jù)庫結(jié)構(gòu)改變后要備份控制文件對(duì)其進(jìn)行修改而控制文件:-在創(chuàng)建數(shù)據(jù)庫時(shí)大小即確定-有一個(gè)可重用的部分，因?yàn)镽ecovery Manager進(jìn)行擴(kuò)展數(shù)據(jù)庫物理結(jié)構(gòu)控制文件多元化控制文件關(guān)閉數(shù)據(jù)庫復(fù)制控制文件修改參數(shù)位置啟動(dòng)數(shù)據(jù)庫control_files=(/DISK1/control01.con,/DISK2/control02.con)Disk

15、 1control01.conDisk 2control02.con數(shù)據(jù)庫物理結(jié)構(gòu)控制文件獲取控制文件具體信息：oraclewekiserver tmp$ sqlplus / as sysdba SQL oradebug setmypidStatement processed. SQL oradebug unlimit Statement processed.SQL alter database backup controlfile to trace;Database altered.SQL oradebug tracefile_name/server/oracle/app/diag/rdbm

16、s/weki/weki/trace/weki_ora_29003.trc數(shù)據(jù)庫物理結(jié)構(gòu)控制文件CREATE CONTROLFILE REUSE DATABASE WEKI RESETLOGS FORCE LOGGING ARCHIVELOGMAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 1168 LOGFILEGROUP 1 /server/oracle/oradata/weki/redo01.log SIZE 50M BLOCKSIZE 512, GROUP 2 /server/oracle/o

17、radata/weki/redo02.log SIZE 50M BLOCKSIZE 512, GROUP 3 /server/oracle/oradata/weki/redo03.log SIZE 50M BLOCKSIZE 512- STANDBY LOGFILE DATAFILE/server/oracle/oradata/weki/system01.dbf, /server/oracle/oradata/weki/sysaux01.dbf, /server/oracle/oradata/weki/undotbs01.dbf, /server/oracle/oradata/weki/use

18、rs01.dbf, /server/oracle/oradata/weki/confluence_data_01.dbf CHARACTER SET ZHS16GBK;數(shù)據(jù)庫物理結(jié)構(gòu)控制文件v$controlfilev$controlfile_record_section v$logfilev$datafile v$tempfile v$tablespace數(shù)據(jù)庫物理結(jié)構(gòu)數(shù)據(jù)文件注意點(diǎn)：業(yè)務(wù)表索引分離，分布不同表空間建議每個(gè)數(shù)據(jù)文件大小統(tǒng)一數(shù)據(jù)文件命令規(guī)范有序數(shù)據(jù)文件自動(dòng)擴(kuò)展關(guān)閉創(chuàng)建表空間步驟：檢查當(dāng)前表空間數(shù)據(jù)文件位置：SQL select name from v$datafile;+MCD

19、ATA/ora11g/system01.dbf創(chuàng)建表空間：create tablespace test datafile +MCDATA/ora11g/app_data01.dbf size 8g extent management localuniform size 1msegment space management auto;添加與創(chuàng)建一致，同樣要確保數(shù)據(jù)文件位置，大小，目前順序數(shù)據(jù)庫物理結(jié)構(gòu)在線日志文件Using Redo Log FilesRedo log files record all changes made to data and provide a recovery mec

20、hanism from a system or media failure.Redo log files are organized into groups.An Oracle database requires at least two groups.Each redo log within agroup is called a member.DatabaseRedo log files數(shù)據(jù)庫物理結(jié)構(gòu)在線日志文件Structure of Redo Log FilesGroup 2Group 3Group 1MemberDisk 1Disk 2MemberMemberMemberMemberM

21、ember數(shù)據(jù)庫啟動(dòng)與關(guān)閉4數(shù)據(jù)庫物理結(jié)構(gòu)2數(shù)據(jù)庫邏輯結(jié)構(gòu)31數(shù)據(jù)庫創(chuàng)建過程數(shù)據(jù)庫邏輯結(jié)構(gòu)SchemaTablespaceSegmentExtentsOracle data block數(shù)據(jù)庫啟動(dòng)與關(guān)閉4數(shù)據(jù)庫物理結(jié)構(gòu)2數(shù)據(jù)庫邏輯結(jié)構(gòu)31數(shù)據(jù)庫創(chuàng)建過程數(shù)據(jù)庫啟動(dòng)揭秘Oracle 數(shù)據(jù)庫的啟動(dòng)分為3 個(gè)階段，首先是啟動(dòng)實(shí)例，然后是裝載 數(shù)據(jù)庫，最后是打開數(shù)據(jù)庫如果沒有啟動(dòng)Oracle 實(shí)例，當(dāng)使用SQL*Plus 以普通用戶身份連接時(shí)，會(huì)顯示錯(cuò)誤信息數(shù)據(jù)庫啟動(dòng)揭秘Starting Up a Database-NOMOUNTOPENMOUNTSHUTDOWNNOMOUNTInstancestarte

22、dSTARTUPSHUTDOWN數(shù)據(jù)庫啟動(dòng)揭秘-nomount：一、在兩種情況下需要啟動(dòng)數(shù)據(jù)庫到該階段：創(chuàng)建數(shù)據(jù)庫創(chuàng)建控制文件二、在該階段打開的文件有初始化參數(shù)文件警告日志文件當(dāng)啟動(dòng)實(shí)例時(shí)，系統(tǒng)首先要讀取初始化參數(shù)文件，然后根據(jù)初始化 參數(shù)文件的設(shè)置分配系統(tǒng)全局區(qū)(SGA)，并啟動(dòng)Oracle 后臺(tái)進(jìn)程。另外，操作系統(tǒng)還會(huì)打開“警告日志”， 并且將啟動(dòng)信息存放到“ 警告日志” 中警告日志存放在初始化參數(shù)background_dump_dest 所對(duì)應(yīng)的目錄中，其文件名格式為alert.log數(shù)據(jù)庫啟動(dòng)揭秘-nomount數(shù)據(jù)庫啟動(dòng)揭秘Starting Up a Database-MOUNTOP

23、ENMOUNTNOMOUNTSHUTDOWNInstancestartedSTARTUPSHUTDOWNControl file opened for this instanceStarting Up a Database-MOUNT進(jìn)入該階段的目的是執(zhí)行特定的數(shù)據(jù)庫維護(hù)操作。有如下三個(gè)典型的維護(hù) 操作：修改數(shù)據(jù)文件名。執(zhí)行數(shù)據(jù)庫完全恢復(fù)。日志文件的聯(lián)機(jī)/脫機(jī)，修改歸檔方式。 在該階段打開的文件是：控制文件。裝載數(shù)據(jù)庫時(shí)，系統(tǒng)會(huì)按照初始化參數(shù)control_files 的設(shè)置查找并打開控制文件。Oracle是通過控制文件在實(shí)例和數(shù)據(jù)庫之間建立關(guān)聯(lián)的，通過控制文件 可以取得數(shù)據(jù)文件和重做日志的名

24、稱和所處狀態(tài)。但對(duì)數(shù)據(jù)文件和日志文件是否存在不作檢查。數(shù)據(jù)庫啟動(dòng)揭秘?cái)?shù)據(jù)庫啟動(dòng)揭秘Starting Up a Database-OPENOPENMOUNTNOMOUNTSHUTDOWNInstancestartedSTARTUPSHUTDOWNControl file opened for this instanceAll files opened as described by the control file for this instance數(shù)據(jù)庫啟動(dòng)揭秘只有在打開數(shù)據(jù)庫之后，客戶才能夠訪問Oracle服務(wù)器,并執(zhí)行各種數(shù)據(jù)訪問操作。在該階段，打開的文件有:聯(lián)機(jī)的數(shù)據(jù)文件。聯(lián)機(jī)的重做日志

25、文件。如果在嘗試打開數(shù)據(jù)庫時(shí)有任何數(shù)據(jù)文件或聯(lián)機(jī)重做日志文件不存在，Oracle 服 務(wù)器將返回錯(cuò)誤消息后臺(tái)進(jìn)程SMON 要檢查控制文件、數(shù)據(jù)文件以及重做日志，并確定它們是否處于同步狀態(tài)。如果處于同步狀態(tài)，則Oracle 會(huì)直接打開所有數(shù)據(jù)文件和重做日志；如果不處于 同步狀態(tài)，并且可以進(jìn)行實(shí)例恢復(fù)，那么SMON 將自動(dòng)進(jìn)行實(shí)例恢復(fù)，然后打開 數(shù)據(jù)庫；如果不處于同步狀態(tài)，并且不能進(jìn)行實(shí)例恢復(fù)，那么SMON 會(huì)提示數(shù)據(jù) 庫管理員進(jìn)行介質(zhì)恢復(fù)。數(shù)據(jù)庫啟動(dòng)揭秘-open mode(read only)只讀狀態(tài)打開數(shù)據(jù)庫,在只讀狀態(tài)下，用戶只能查詢數(shù)據(jù)庫，但不能以任何方式 對(duì)數(shù)據(jù)庫對(duì)象進(jìn)行修改(rest

26、rict)限制模式打開數(shù)據(jù)庫,只有Create Session 和Restricted Session系統(tǒng)權(quán)限 或者具有 SYSDBA 和SYSPORE 系統(tǒng)權(quán)限的用戶才能連接到數(shù)據(jù)庫，已經(jīng)連接的用戶不 斷開(upgrade)升級(jí)模式打開數(shù)據(jù)庫(QUIESCE)會(huì)讓新的非SYS和SYSTEM用戶登錄HANGH住，而且登錄的會(huì)話如果再想執(zhí)行語句也會(huì)被HANG住。注意改變?yōu)殪o默模式需要等待當(dāng)前所有的ACTIVE會(huì)話完成其操作，可能需要較長的時(shí)間(suspend)掛起狀態(tài)，掛起數(shù)據(jù)文件和控制文件的I/O操作。掛起狀態(tài)可以避免備份數(shù)據(jù)庫 時(shí)的IO操作。當(dāng)數(shù)據(jù)庫試圖進(jìn)入掛起狀態(tài)時(shí)，允許當(dāng)前活動(dòng)的事務(wù)完成，新 發(fā)起的事務(wù)會(huì)進(jìn)入隊(duì)列中數(shù)據(jù)庫關(guān)閉-關(guān)閉模式當(dāng)DBA 要執(zhí)行完全數(shù)據(jù)庫備份、修改初始化參數(shù)以及其他系統(tǒng)維護(hù)操作時(shí)，需要停止Oracle 服務(wù)器。根據(jù)數(shù)據(jù)庫關(guān)閉方式,Oracle 數(shù)據(jù)庫共有4 種關(guān)閉方式，根據(jù)不同的情況，管理員可采用 不同的方式關(guān)閉數(shù)據(jù)庫。NORMAL：正常關(guān)閉方式。TRANSACTIONAL：事務(wù)關(guān)閉方式。IMMEDIATE：立即關(guān)閉方式。ABORT：強(qiáng)制終止關(guān)閉方式。Shutdown ModeAITNAllow new connectionsxxxxWait unti