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

1、技術(shù)創(chuàng)新，變革未來Oracle數(shù)據(jù)庫整體架構(gòu)及啟停介紹數(shù)據(jù)庫啟動與關(guān)閉4數(shù)據(jù)庫物理結(jié)構(gòu)2數(shù)據(jù)庫邏輯結(jié)構(gòu)31數(shù)據(jù)庫創(chuàng)建過程概念性知識-數(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。這是在主版本號上發(fā)布的補丁集，修復(fù)了較多的Bug，可能會包 含一些增強功能（Enhancement）。比如11.2.0.1是一個主版本，那

5、么11.2.0.2、11.2.0.3就是2個不 同的Patch set。這種補丁集經(jīng)過了嚴格的集成測試，也是累積型的。所以推薦安裝最新的Patch SetOracle-數(shù)據(jù)庫Patch概念性小常識.pdfQuick Reference to Patch Numbers for Database PSU, SPU(CPU), BundlePatches and Patchsets (文檔 ID 1454618.1)概念性知識-數(shù)據(jù)庫版本主機A主機BRAC架構(gòu)SAN交換機千兆交換機存儲網(wǎng)絡(luò)2主機B存儲千兆交換機單機架構(gòu)概念性知識-數(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)建一個數(shù)據(jù)庫安裝數(shù)據(jù)庫軟件創(chuàng)建監(jiān)聽DBCA建庫（手工建庫）最優(yōu)化參數(shù)調(diào)整系統(tǒng)監(jiān)控部署創(chuàng)建一個數(shù)據(jù)庫軟件安裝一、環(huán)境確認，參數(shù)調(diào)整根據(jù)不同的主機調(diào)整相應(yīng)的參數(shù)創(chuàng)建基本的安裝用戶確認主機包及補丁安裝完全確認節(jié)點時區(qū)，時間是否正確確認交換空間，內(nèi)存，CPU是否符合要求確認多路徑是否配置完全，磁盤狀態(tài)是否正常二、

9、圖形界面調(diào)用，安裝軟件確認安裝版本確認安裝路徑創(chuàng)建一個數(shù)據(jù)庫建庫一、注意以下要點數(shù)據(jù)庫名字符集內(nèi)存大小設(shè)置連接數(shù)連接模式二、創(chuàng)建模式DBCA建庫手工建庫創(chuàng)建一個數(shù)據(jù)庫最優(yōu)化參數(shù)調(diào)整密碼過期參數(shù)密碼錯誤登錄次數(shù)密碼大小寫敏感密碼錯誤延遲登錄Oracle審計參數(shù)延遲段創(chuàng)建直接路徑讀SCN參數(shù)優(yōu)化內(nèi)存參數(shù)優(yōu)化REDO大小優(yōu)化創(chuàng)建一個數(shù)據(jù)庫系統(tǒng)監(jiān)控部署OSWatch部署AWR策略調(diào)整AWR基線創(chuàng)建數(shù)據(jù)庫啟動與關(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ù)據(jù)日志文件:記錄對數(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)限遠程連接 數(shù)據(jù)庫，必須使用口令文件，否則不能連上，由于sys用戶在連 接數(shù)據(jù)庫時必須

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)控制文件使用控制文件的原則使用控制文件時應(yīng)該:-對控制文件進行多元化處理-在初始化參數(shù) CONTROL_FILES中指定控制文件的完整路徑-當數(shù)據(jù)庫結(jié)構(gòu)改變后要備份控制文件對其進行修改而控制文件:-在創(chuàng)建數(shù)據(jù)庫時大小即確定-有一個可重用的部分，因為Recovery Manager進行擴展數(shù)據(jù)庫物理結(jié)構(gòu)控制文件多元化控制文件關(guān)閉數(shù)據(jù)庫復(fù)制控制文件修改參數(shù)位置啟動數(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ù)文件注意點：業(yè)務(wù)表索引分離，分布不同表空間建議每個數(shù)據(jù)文件大小統(tǒng)一數(shù)據(jù)文件命令規(guī)范有序數(shù)據(jù)文件自動擴展關(guān)閉創(chuà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ù)庫啟動與關(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ù)庫啟動與關(guān)閉4數(shù)據(jù)庫物理結(jié)構(gòu)2數(shù)據(jù)庫邏輯結(jié)構(gòu)31數(shù)據(jù)庫創(chuàng)建過程數(shù)據(jù)庫啟動揭秘Oracle 數(shù)據(jù)庫的啟動分為3 個階段，首先是啟動實例，然后是裝載 數(shù)據(jù)庫，最后是打開數(shù)據(jù)庫如果沒有啟動Oracle 實例，當使用SQL*Plus 以普通用戶身份連接時，會顯示錯誤信息數(shù)據(jù)庫啟動揭秘Starting Up a Database-NOMOUNTOPENMOUNTSHUTDOWNNOMOUNTInstancestarte

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

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

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

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

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