Oracle體繫結構和使用者管理

標籤：oracle   linux   楊書凡   

   本篇部落格將對Oracle的體繫結構、儲存結構、記憶體結構和進程結構進行初步介紹，從而從宏觀上把握它的物理組成、檔案組成和各種進程，對於進一步的瞭解可以起到很好地作用

一、Oralce體繫結構

1、概述

    Oracle的體繫結構是資料庫的組成，工作過程，以及資料庫中資料的組織與管理機制，要瞭解oracle資料庫的體繫結構，就必須要理解oracle的主要組件和重要的概念。Oracle體繫結構包含一系列組件，包括執行個體、使用者進程、服務進程、資料檔案及其他檔案，如參數檔案、口令檔案和歸檔記錄檔等。如所示：

執行個體和資料庫是oracle資料庫體繫結構的核心組成部分，也是最重要的兩個概念，dba的主要工作就是維護執行個體和資料庫。

1）執行個體

執行個體是後台進程和記憶體的集合，必須啟動執行個體才能訪問資料庫中的資料。

 

Oracle啟動時，將分配一個系統全域區（SGA），並啟動一系列oracle後台進程

一個執行個體只能開啟並使用一個資料庫

Oracle分為單進程執行個體和多進程執行個體，現在普遍都是多進程執行個體，多進程就是多使用者，不同使用者使用不同進程來執行oracle的不同部分。

 

2）資料庫

資料庫是一個資料的集合，該集合被視為一個邏輯單元。

Oracle 資料庫由作業系統檔案組成，這些檔案為資料庫資訊提供實際實體儲存體區

Oracle 資料庫包括邏輯結構和物理結構。

物理結構：是指儲存資料庫資訊的一組作業系統檔案，每個資料庫有一個邏輯結構和一個物理結構。物理結構是指構成資料庫的一組作業系統檔案，主要由3種類型檔案組成：資料檔案，控制檔案和重做記錄檔。

邏輯結構：是指資料庫資料的邏輯儲存結構，包括：資料表空間，資料區段，表，視圖等。

 

二、oracle儲存結構

Oracle的儲存結構分為物理結構和邏輯結構，這兩種儲存結構既相互獨立又相互聯絡。

1、物理結構：

物理結構就是oracle資料庫建立後使用的作業系統物理檔案，分為以下兩種：

1）主要檔案

資料檔案：資料檔案是實體儲存體資料庫的檔案，資料檔案的特點：每個資料檔案只與一個資料庫相聯絡，一個資料表空間可以包含一個或多個資料檔案，一個資料檔案只能屬於一個資料表空間。資料檔案包含資料庫的使用者或應用程式資料，以及中繼資料和資料字典。

 

重做記錄檔：它記錄了對資料的所有更改資訊，並提供一種資料恢複機制，如果資料庫伺服器發生崩潰，但未丟失任何資料檔案，那麼執行個體便可使用這些檔案中的資訊恢複資料庫。

 

控制檔案：當資料庫讀取時，要根據控制檔案的資訊尋找資料檔案。控制檔案儲存體了資料檔案和重做記錄檔的名稱和位置。一個資料庫至少有兩份控制檔案，oracle11g預設包括3個控制檔案，各個控制檔案內容相同，可以避免因為一個控制檔案的損壞導致無法啟動資料庫。控制檔案記錄了以下關鍵資訊：資料檔案的位置和大小、重做記錄檔的位置及大小、資料庫名稱及建立時間、記錄序號。控制檔案對資料庫至關重要。沒有這些檔案，就無法開啟資料檔案以訪問資料庫中的資料。

 

 

2）其他檔案

參數檔案：用於定義執行個體啟動時的配置

口令檔案：允許sysdba、sysoper 和sysasm 遠端連線到執行個體並執行管理工作

歸檔記錄檔：使用這些檔案和Database Backup，可以恢複丟失的資料檔案。也就是說，歸檔日誌能夠恢複還原的資料檔案

 

2、oracle邏輯結構

Oracle的邏輯組件包括資料表空間、段、區、塊和模式等。

1）資料表空間

  資料表空間是資料庫中最大的邏輯單位， SYSTEM 和SYSAUX 資料表空間是在建立資料庫時建立的必須存在的資料表空間，還會有TEMP，USERS等。

  每個資料表空間是由一個或多個資料檔案組成的，一個資料檔案只能與一個資料表空間相關聯。

  資料表空間的大小等於構成該資料表空間的所有資料檔案大小之和。

  資料表空間和資料檔案是相對應的，如果沒有資料檔案，資料表空間不可能獨立存在，如果只有資料表空間沒有資料檔案也是不行的，就好比如果電腦沒有硬碟，你不能再邏輯上看到C盤D盤，在這裡硬碟就相當於是資料檔案，而C盤就相當於是資料表空間。我們可以把多個資料檔案組成一個資料表空間，然後在資料表空間裡面儲存資料，資料表空間和資料檔案是對應的，一個資料表空間可以是由一個或多個資料檔案組成的，但是一個資料檔案只能在一個資料表空間當中。

 

資料表空間的作用：

  對於不同的使用者指派不同的資料表空間，對不同的模式對象分配不同的資料表空間，方便對使用者操作和模式對象的管理。

  可以將不同資料檔案建立到不同的磁碟中，有利於管理磁碟空間，提高i/o效能，備份和恢複資料等。

 

  SYSTEM資料表空間：用於存放oracle系統內部表和資料字典的資料，如表明、列名、使用者名稱等。

  SYSAUX資料表空間：作為SYSTEM的輔助資料表空間，用於存放各種資料庫工具（如企業管理器、StasPack、logMiner等）用到的資料。用於存放各種模式的對象資料，如智能代理使用者DBSNMP、資料採礦使用者ODM等。

  USERS資料表空間：作為使用者使用的資料表空間，可以在這個資料表空間上建立各種對象，如建立表，索引等。

  TEMP資料表空間：存放臨時資料的特殊資料表空間，例如：當需要進行排序時，系統就將排序的資料臨時存放在該資料表空間，排序處理完成後，即可釋放排序資料所佔用的空間，稱之為暫存資料表空間。

 

 

 

以上資料表空間我們不能建立，系統安裝好之後自動建立好了，我們也可以根據需要自己建立。

 

建立資料表空間的文法是：

CREATE TABLESPACE tablespacename

DATAFILE ‘filename’ [SIZE integer [K|M]]

[AUTOEXTEND [OFF|ON]];

 

 

案例一：建立一個自動成長的資料表空間tbs_work

如果不能建立，說明資料庫沒有啟動，執行STARTUP命令之後再建立。

 

如果空間不夠用了，可以調整資料表空間大小。

方法一：更改資料檔案大小，並指明資料檔案的存放路徑，使用RESIZE關鍵字執行調整後的資料表空間大小。

 

方法二：向資料表空間內添加新的資料檔案，當資料檔案滿了之後自動儲存到新的資料檔案。

 

 

案例二：改變資料表空間的讀寫狀態，ONLY表示唯讀，WRITE表示可讀寫。

 

刪除資料表空間

 

 

案例三、想查看系統中有多少個資料檔案

 

 

案例四、如何查看錶空間

 

案例五、查看錶空間和資料檔案之間的對應關係

 

 

 

2）段

  段存在於資料表空間中，是一種指定類型的邏輯儲存結構。

  段由一組區組成

  段分為4類：資料區段、索引段、回退段、臨時段。

 

3）區

  區是磁碟空間分配的最小單位。磁碟按區劃分，每次至少分配一個區。

  區是由連續的資料區塊組成。

  段主要有一個或多個區構成，當建立段時，至少包含一個區。

  區不能跨資料檔案存在，只能存在於一個資料檔案中。

 

4）資料區塊

  是資料中最小的資料群組織單位與管理單位

  Oracle資料中的資料存放區於資料區塊中

  資料區塊是oracle伺服器所能讀取或寫入的最小儲存單元

  資料區塊的取值範圍是2K-64KB，預設值與oracle版本有關

 

5）模式

  模式是資料庫物件的集合。包括：表、視圖、索引、同義字、序列、過程和程式包等。

  當建立一個使用者時，oracle會自動建立一個與使用者名稱相同的模式，因此，模式又稱為使用者模式。

  使用者登入後，預設訪問的是與自己的名稱相同的模式中的數量庫對象。

 

 

 

三、oracle的記憶體結構

記憶體是影響資料庫效能的第一要素，oracle記憶體儲存的主要內容如下：

  程式碼

  關於已經串連的會話資訊，包括當前所有活動會話和非活動會話

  程式運行時必須的相關資訊，如查詢計劃

  Oracle進程之間通訊和共用的資訊，如鎖

 

按照記憶體的使用方法不同，oracle資料庫的記憶體又可分為系統全域區（SGA）、程式全域區（PGA）、使用者全域區（UGA）

 

1、系統全域區 （ SGA ）

資料庫資訊儲存於系統全域區，由多個資料庫進程共用，當資料庫執行個體啟動時SGA的記憶體本自動分配，SGA是資料庫中佔用伺服器記憶體最大的一個地區，也是影響資料庫效能的一個重要指標，SGA按作用不同，分為以下幾個部分。

 

 

 

2、PGA

  PGA不是執行個體的一部分，包含單個伺服器處理序或者單個後台進程所需的資料和控制資訊。

  PGA是在使用者進程串連到資料庫並建立一個會話時自動分配

  該地區內保留每個與oracle資料庫連接的使用者進程所需的記憶體

  當一個使用者會話結束，PAG就會釋放

 

 

3、UGA

  使用者全域區（UGA）為使用者進程儲存工作階段狀態

  UGA可以作為SGA或者PGA的一部分。具體位置取決於如何串連Oracle：

  如果通過一個共用伺服器串連，UGA包含在SAG中

  如果通過一個專有伺服器串連，UGA就包含在專有伺服器的PGA中

 

 

案例一：驗證記憶體結構工作的原理（select語句是如何被執行的？）

 

當使用者執行語句select  *  from scott.emp的時候，工作原理分為四步，如下：

 

第一步：先做編譯，編譯包含了文法的檢查和語義的檢查。

 

第二步：如果編譯沒有問題會進入shared pool(共用池 )中，而shared pool 是由library cache和data dictionary cache（資料字典緩衝）組成。Sql語句第一時間會進入library cache中，即library cache中會存放被編譯過的正確的sql語句，僅僅是sql語句而已。library cache中沒有緩衝的sql語句需要進行硬解析（即需要耗費裝置的I/0資源從硬碟讀取）。假設library cache中有這個sql語句，就不需要做硬解析，接著會查 data dictionary cache。data dictionary cache中緩衝了許可權和對象資料及屬性，所有data dictionary cache檢查使用者是否對scott.emp表是否具有存取權限，如果有許可權繼續往下執行，如果沒有許可權，直接給使用者返回一個結果。如果一條語句從來都沒有被執行過，這條語句首先被緩衝在share pool中的library cache中，下一次被執行的時候直接從share pool中取sql語句。存放sql語句也需要佔用空間，share pool的空間也是有限的，為了防止share pool空間不夠用，採用先進先出的規則即後執行的sql語句會覆蓋先執行的sql語句來釋放空間，當然share pool的空間越大越好。

 

第三步：data buffer cache現在hr使用者可以訪問scott.emp表了，data buffer cache的作用就是從磁碟或儲存中將資料區塊調入記憶體，緩衝在buffer cache中。總結：buffer cache快取資料本身，library cache緩衝sql語句本身。

 

第四步：redo log buffer<重做日誌緩衝區>當使用者執行insert,update,delete,create,alter等操作後，資料發生了變化，這些變化了的資料寫入資料緩衝區（buffer cache）之前，先寫入重做日誌緩衝區，同時變化之前的資料也放入重做日誌緩衝中，這樣做，是為了保證在資料恢複時oracle就知道哪些事務需要提交，哪些事務需要撤回。

 

可以通過企業管理器來查看share pool的相關資訊

如果控制台打不開，可以執行命令開啟：

 

 

 

 

 

 

可以從v$sgainfo中查看shared pool size

 

測試語句執行時間：

SQL> set timing on

SQL> select * from scott.emp;第一次執行sql語句

 

經過時間:  00: 00: 00.05

 

SQL> /再次執行上一條命令所用的時間為00: 00: 00.00

 

經過時間:  00: 00: 00.00

 

通過上面的測試發現，緩衝提高了查詢的速度。

 

 

 

 

 

 

四、oracle進程結構

 

 

Oracle有5個後台進程是必須啟動的，否則資料庫執行個體無法啟動成功，分別是：進程監控（PMON）進程、系統監控（SMON）進程、資料寫入（DBWR）進程、日誌寫入（LGWR）進程、檢查點（CKPT）進程。

 

CKPT進程：確保資料緩衝區中所有修改過的資料區塊都被寫入資料庫檔案的機制。保證資料庫記錄檔和資料檔案的同步。

 

 

相關命令：

1、查看使用者進程和資料庫進程

Ps –elf | greporacleocl |grep–v grep

 

 

2、守護進程

Ps –elf |grep oha

 

3、查看日誌進程LGWR

Ps –elf |grep ora_ | grep lgwr 

 

五、oracle使用者管理

當建立一個新資料庫時，oracle將常見一些預設的資料庫使用者，如SYS,SYSTEM和SCOTT等使用者。SYS和SYSTEM使用者都是oracle系統管理使用者，而SCOTT使用者是oracle資料庫的一個示範使用者，SCOTT資料庫裡麵包含一些測試展示用的樣本表（用於學習交流）

1、預設資料庫使用者

1）SYS

SYS使用者是oracle中的一個超級使用者，資料庫中所有資料字典和視圖都存在SYS模式中，資料字典儲存了用來管理資料庫對象的所有資訊，是oracle資料庫中非常重要的系統資訊。SYS使用者主要用來維護系統資訊和管理執行個體。SYS使用者只能以SYSOPER或SYSDBA角色登入系統。

 

2）SYSTEM

SYSTEM使用者是oracle中預設的資料庫管理員，它擁有DBA許可權。該使用者模式中儲存了oracle管理工具使用的內部表和視圖。通常通過SYSTEM使用者管理資料庫的使用者、許可權和儲存等。不建議在SYSTEM模式中建立使用者表。SYSTEM不能以SYSOPER和SYSDBA角色登入系統，只能以預設登入。

 

3）SCOTT

SCOTT使用者是oracle資料庫的一個示範使用者，一般在資料庫安裝時建立。SCOTT使用者模式包含4個樣本表，其中一個是EMP表，使用USERS資料表空間儲存模式對象。

通常出於安全考慮，對於不同用途的表需要不同的存取權限，此時，就需要建立不同的使用者，oracle中的create user命令用於建立使用者，每個使用者都有一個預設資料表空間和一個暫存資料表空間。如果沒有指定，oracle就將USERS設為預設資料表空間，將TEMP設為暫存資料表空間。

 

4）Sysdba和sysoper兩個系統許可權區別 

sysdba和sysoper屬於system privilege，也稱為administrative privilege，擁有例如資料庫開啟關閉之類一些系統管理層級的許可權。sysdba和sysoper具體的許可權如下： 

【SYSOPER】許可權，即資料庫操作員許可權，許可權包括：

  開啟資料庫伺服器

  關閉資料庫伺服器

  備份資料庫

  恢複資料庫

  日誌歸檔

  會話限制

【SYSDBA】許可權，即資料庫管理員許可權，許可權包括：

  開啟資料庫伺服器

  關閉資料庫伺服器

  備份資料庫 恢複資料庫

  日誌歸檔

  會話限制

  管理功能

  建立資料庫 

 

【sysdba】擁有最高的系統許可權，登陸後是sys。

【sysoper】主要用來啟動、關閉資料庫，sysoper 登陸後使用者是public。

 

2、建立自訂使用者

步驟：

  選擇使用者名稱和密碼

  識別使用者需用於儲存物件的資料表空間

  決定每個資料表空間的限額

  分配預設資料表空間和暫存資料表空間

  建立使用者

  向使用者授予許可權和角色

 

 

文法：

CREATE USER user

       IDENTIFIED BY password

       [DEFAULT  TABLESPACE tablespace]

       [TEMPORARY  TABLESPACE tablespace]

       [QUOTA {integer [K|M] | UNLIMITED}ON tablespace

       [QUOTA {integer [K|M] | UNLIMITED}ON tablespace ] ...]

       [PASSWORD EXPIRE ]

 

案例1：建立使用者YDW

CREATE USER YDW                     --使用者名稱為YDW

  IDENTIFIED BY pwd123                --密碼為martinpwd

  DEFAULT TABLESPACE tbs_work         --預設資料表空間為tbs_work

  TEMPORARY TABLESPACE temp         --暫存資料表空間為temp

  QUOTA UNLIMITED ON tbs_work        --對tbs_work資料表空間的使用沒有限制

  PASSWORD EXPIRE;                   --第一次登入需要修改密碼

 

 

3、修改YDW使用者的密碼為pwd123456

 

 

4、刪除使用者YDW

 

 

5、查詢oracle的所有使用者

 

6、解鎖使用者

1）先查看hr使用者的狀態是鎖定的

 

 

六、oracle資料庫許可權管理

  許可權指的是執行特定命令或訪問資料庫物件的權利

  許可權有兩種類型，系統許可權和對象許可權

  系統許可權允許使用者執行某些資料庫操作，如建立表就是一個系統許可權

  對象許可權允許使用者對資料庫物件（如表、視圖、序列等）執行特定操作

  角色是一組相關許可權的組合，可以將許可權授予角色，再把角色授予使用者，以簡化許可權管理

 

1、使用系統許可權

系統許可權是指在資料庫中執行某種系統層級的操作，或者針對某一類對象執行某種操作的權利，例如：在資料庫中建立資料表空間的權利，或者建立表的權利，都屬於系統許可權，具體如：

 

 

常用的系統許可權如下：

CREATE  SESSION：串連到資料庫

CREATE TABLE：建立表

CREATE VIEW：建立視圖

CREATE SEQUENCE：建立序列

 

2、對象許可權

對象許可權是指標對某個特定模式對象執行操作的權利，只能針對模式對象來設定管理對象許可權，包括：表、視圖、序列、預存程序等。

 

3、授權

Oracle資料庫使用者有兩種途徑獲得許可權

1）直接向使用者授予許可權

2）將許可權授予給角色，再將角色授予給一個或者多個使用者

使用角色能更高效，方便地對許可權進行管理，所以資料庫管理員通常使用角色向使用者授予許可權，而不是直接向使用者授予許可權。

Oracle中常用系統預定義角色有以下種類：

1）CONNECT：擁有串連資料庫許可權

2）RESOURCE：擁有建立表、觸發器、過程等許可權

3）DBA：資料庫管理員，擁有管理資料庫最高許可權

 

新使用者必須授予一定的許可權才能進行相關資料庫操作，授權通過grant語句，取消授權通過revoke語句

 

授權的文法：

1）GRANT CONNECT  TO  MARTIN;

授予授予CONNET角色給MARTIN

 

2）GRANT CONNECT,RESOURCE TO MARTIN;

同時授予CONNET，RESOURCE這2個角色給MARTIN

 

3）GRANT SELECT ON SCOTT.emp TO MARTIN;

允許使用者MARTIN查詢 SCOTT.emp表的記錄

 

4）GRANT UPDATE ON SCOTT.emp TO MARTIN;

允許使用者MARTIN更新 SCOTT.emp表中的記錄

 

5）GRANT ALL ON SCOTT.emp TO MARTIN;

允許使用者MARTIN插入、刪除、更新和查詢SCOTT.emp表中的記錄

 

 

4、撤銷許可權的文法：

使用REVOKE命令撤銷使用者指派的許可權或角色

 

1）REVOKE CONNECT,RESOURCE FROM MARTIN;

撤銷CONNET，RESOURCE這2個角色

 

2）REVOKE SELECT FROM SCOTT.emp TO MARTIN;

撤銷MARTIN查詢 SCOTT.emp表的記錄的許可權

 

 

 

 

案例1：為使用者佈建許可權

1）首先建立使用者YDW

 

2）使用YDW使用者登入

提示第一次登入需要更改密碼

 

3）使用新密碼重新登入，提示沒有任何許可權。

 

4）為使用者授予session（串連到資料庫）許可權

 

5）重新登入oracle

 

6）測試YDW能否建立表，提示許可權不足

 

7）授予使用者建立表的許可權

 

8）再次使用YDW登入，測試能夠建立表

建立成功了

 

9）測試插入資料，並查詢

 

10）查看使用者擁有的許可權

 

11）撤銷使用者的許可權

 

12）測試使用者YDW能夠查詢scott.emp

提示沒有許可權

 

13）授予使用者查詢scott.emp的許可權

 

14）測試查詢scott.emp

查詢成功了

 

 

 

 

 

 

 

Oracle體繫結構和使用者管理