Oracle442個應用情境-----------Oracle資料庫物理結構，oracle資料庫應用

Oracle442個應用情境-----------Oracle資料庫物理結構，oracle資料庫應用

 

-------------------------Oracle資料庫物理結構-------------------------------

Oracle資料庫物理結構
    oracle的資料,實際上是以檔案的形式來儲存的,檔案中出了儲存使用者的資料之外,
還需要儲存管理資料和日誌資料等等。作為一個DBA，必須需要知道自己的資料分別儲存在什麼位置上，
而且經驗豐富的管理員還應該在設計資料庫的時候合理的規劃檔案的儲存位置、初始大小以及增量屬性等，
這樣既能滿足使用者應用程式的需要，還能保證資料庫高效的運行。
每個Oracle資料都三種類型的檔案組成，即控制檔案、資料檔案以及記錄檔。
這些檔案提供了真正的資料存放區的實體儲存體。

1、控制檔案
每一個Oracle資料庫都有一個控制檔案，用來儲存資料庫的物理結構資訊。
控制檔案包含資料庫名、資料庫資料檔案和記錄檔的名字、位置和資料庫建立日期等資訊。
資料庫的控制檔案用於標識資料庫和記錄檔，當開始資料庫操作時，它們必須被開啟。
當資料庫的組成更改的時候，Oracle自動更新該資料庫的控制檔案。資料恢複的時候，也需要用到控制檔案。
當資料庫開啟的時候，Oracle資料庫必須寫控制檔案。沒有控制檔案，資料庫將沒有辦法裝在，恢複資料庫的時候也非常的困難。

2、資料檔案
進行資料操作的時候，系統將從資料檔案中讀取資料，並且儲存到Oracle的記憶體緩衝區中。
建立或者更新的資料不必立即寫入到資料檔案中，而是把資料臨時放到記憶體中，由資料庫寫入進程（DBWn）決定在適當的時間一次性寫入到資料檔案中。這樣可以大大降低訪問磁碟的次數，從而加強系統的效能。

3、記錄檔
每個資料庫都有兩個或者多個記錄檔組，用於收集資料庫日誌。
日誌的主要功能就是記錄對資料所作的修改，所有對資料庫進行的修改都記錄在日誌中。
在出現故障的時候，如果不能將修改資料永久的寫入資料檔案，則可以通過日誌得到修改後的記錄，
從而保證已經發生的操作結果不會丟失。
記錄檔的主要作用是防止資料庫在出現故障的時候遺失資料。為了防止記錄檔本身的故障，
Oracle允許使用鏡像日誌，在不同的磁碟上維護兩個以上的日誌副本。

--------------------------------------------------------------------------------
重做日誌管理

 重做日誌(Redo Log)由兩個以上的檔案組成,用於保護資料庫的所有變化資訊.
每個Oracle資料庫執行個體都有一個相關的重做日誌,從而保證資料庫的安全.
   
重做日誌由重做記錄組成,而每個重做記錄由一組變化元素組成,變化元素中記錄了資料庫中每個單獨的資料區塊的變化情況,例如:
當使用者修改了表中的某一條資料的時候,系統將會自動的產生一條重做記錄。
可以使用重做日誌來恢複資料庫的變化，保護復原資料。當使用重做資料恢複資料庫的時候，
資料庫將從重做日誌中讀取變化元素，然後將變化應用到相關的資料區塊當中。
資料庫中至少包含兩個重做日誌，一個始終保持可寫的狀態，用於記錄資料庫的變化，
另外一個用于歸檔操作（當系統的achievelog模式開啟的時候）。
那麼Oracle是怎麼記錄重做日誌的呢？日誌寫入進程LGWR負責記錄重做日誌。
如果重做記錄檔已經被填充滿了，那麼LGWR將變化資料寫入下一個重做記錄檔；
如果最後一個有效重做日誌被填滿了，那麼LGWR將變化資料寫入第一個重做日誌。

為了防止重做日誌本身被破壞，Oracle提供了一種多元重做日誌，也就是說，系統在不同的位置上自動維護日誌的兩個或者多個副本。
從安全的角度出發，這些副本的儲存位置應該在不同的磁碟上。
    多元性是通過建立重做日誌組來實現的，組包括一個重做記錄檔和它的多元副本。每個重做日誌組由數字來定義，例如組1，組2等等。每個記錄檔必須處於啟用狀態，這樣LGWR就可以同時寫入這兩個記錄檔LGWR不會同時寫入不同組的記錄檔。
    在不同的情況下，當重做日誌無效的時候，LGWR鎖採用的動作如下：
1）LGWR可以寫入組中的至少一個成員檔案：正常完成寫操作。LGWR寫入組中的可訪問的成員檔案，忽略不可訪問的成員檔案。
2）在日誌切換的時候，LGWR無法訪問下一個組，因為該組需要被歸檔：臨時停止資料庫操作，等待該組可以被訪問或者該組已經被歸檔。
3）在日誌切換的時候，由於介質被損壞，下一組的所有成員都無法被訪問：ORACLE資料庫返回錯誤，資料庫執行個體被關閉。
此時需要從有效重做日誌中執行介質恢複操作（資料庫恢複操作）。如果資料庫的檢查點已經超出了丟失的重做日誌，
則不需要進行介質恢複了，因為重做日誌中記錄的資料寫入到資料檔案中。現在只需要刪除無效的重做日誌組。
如果資料庫還沒有對失效的日誌進行歸檔操作，則執行ALTER DATABASE CLEAR UNARCHIVED LOG禁止歸檔操作，
這樣就可以刪除記錄檔了。
4）當LGWR寫入的時候，所有的組中的成員檔案忽然無法訪問：Oracle返回錯誤，資料庫執行個體被關閉。
此時，需要從有效重做記錄檔中執行介質恢複操作。如果介質沒有被破壞，只是不小心掉線了，則不需要進行介質恢複，
只要將介質恢複線上，然後讓資料庫執行自動執行個體恢複就可以了。
   
可以通過如下參數來設定重做日誌的數量：
MAXLOGFILES：在CREATE DATABASE語句中使用MAXLOGFILES參數可以指定每個資料庫中重做記錄檔組的最大數量。
MAXLOGMEMBERS：在CREATE DATABASE語句中使用MAXLOGMEMBERS參數可以指定每個記錄檔組中包含的記錄檔的最大數量。

--------------------------------------------------------------------------------
歸檔記錄檔和歸檔模式

    歸檔記錄檔是重做記錄檔群組成員的備份，它由重做項目和唯一的記錄序號組成。
當資料庫處于歸檔模式的時候，寫日誌進程（LGWR）不能夠對未歸檔的重做日誌組進行重用和改寫操作。
如果設定了自動歸檔模式，則後台進行ARCn將自動的執行歸檔操作。資料庫會啟動多個進程歸檔，確保一旦記錄檔被填滿馬上會被歸檔。

可以使用歸檔記錄檔達到以下目的：

1）恢複資料庫
2）更新備用資料庫
3）使用LogMiner擷取資料庫的曆史資訊
使用者可以選擇自動歸檔或者手動歸檔，但是自動歸檔模式更加的方便和快捷。
LGWR進程向線上重做記錄檔寫入日誌資訊，一旦重做日誌被寫滿，則由ARC0進程進行歸檔操作。

--------------------------------------------------------------------------------
SCN

SCN是System Change Number的縮寫，它是資料庫的重要機制，可以用來記錄和標識執行資料庫操作的先後順序。
在執行復原事務、備份以及恢複資料庫等操作的時候，資料庫操作的先後順序是非常重要的。
   
SCN是一個只能增大的整數，系統可以保證這個整數是不會越界的。當使用者修改資料庫中的資料（增加，修改，刪除）時，
Oracle資料庫並不是立即將其儲存到資料檔案中，而是將資料儲存在緩衝區（buffer cache）中，當提交事務的時候，
才會將資料寫進資料檔案。

下面是修改資料的簡單過程:
1）開始一個事務，事務是包含一組資料庫操作的邏輯工作單元。
2）在緩衝區中尋找要修改的資料，如果沒有找到，那麼就從資料檔案中找到這個資料，並且將其載入到資料緩衝區中。
3）修改緩衝區中的資料區塊，並將修改的結果儲存到日誌緩衝區中。因為此時緩衝區中的資料與資料檔案中的資料不一致，
因此將這種資料稱之為“髒資料”
4）當使用者提交資料的時候，LGWR進程會將緩衝區中的資料和新產生的SCN寫入到重做記錄檔中，。
但是為了減少I/O操作，Oracle不會立即將髒資料寫入到資料檔案當中。
5）如果發生檢查點（CheckPoint，檢查點是一個事件，當這個事件發生的時候，
DBWn進程將把SGA中所有改變的資料庫緩衝區寫入到資料檔案中）。則CKPT進程喚醒DBWn進程，
更新資料庫中的所有的資料檔案和控制檔案，並且標記最新的檢查點，從而下一次更新從最新的檢查點開始。

--------------------------------------------------------------------------------
通常在執行SHUTDOWN NORMAL和SHUTDOWN IMMEDIATE語句的時候，會觸發Checkpoint事件
當發生Checkpoint事件的時候，Oracle會將SCN寫入到下面的四個地方：

1）系統檢查點SCN（SYSTEM CHECKPOINT SCN）
當一個CheckPoint動作完成之後，Oracle會將系統檢查點的SCN儲存到控制檔案中,可以從視圖V$DATABASE中查看到系統檢查點SCN的值,

代碼如下:
SELECT CHECKPOINT_CHANGE# FROM V$DATABASE;

2）資料檔案檢查點SCN（DATAFILE CHECKPOINT SCN）
當一個CheckPoint動作完成之後，Oracle會將每個資料檔案的SCN儲存到控制檔案中。
可以從系統檢視表V$DATAFILE中看到資料檔案檢查點SCN的值，
代碼如下：
SELECT NAME,CHECKPOINT_CHANGE# FROM V$DATAFILE;

3）啟動SCN(START SCN)
Oracle把每個資料檔案的檢查點SCN儲存在每個資料檔案的檔案頭中，稱為啟動SCN。
因為在資料庫執行個體啟動的時候，Oracle會檢查每個資料庫檔案的啟動SCN與控制檔案檢查點SCN是否一致，
如果不一致的話，則從重做記錄檔中找到丟失的SCN，重新寫入資料檔案中進行恢複。
可以從系統檢視表V$DATAFILE_HEADER中查看到啟動SCN的值，

代碼如下：
SELECT NAME,CHECKPOINT_CHANGE# FROM V$DATAFILE_HEADER;

4）結束SCN（STOP SCN）
每個資料檔案的結束SCN都儲存在控制檔案中。正常情況下，所有處於聯機狀態讀寫入模式下的資料檔案的SCN都為NULL。
可以從系統檢視表V$DATAFILE中查看到結束SCN的值，
代碼如下：
SELECT NAME,LAST_CHANGE# FROM V$DATAFILE；

資料庫正常關閉（執行SHUTDOWN NORMAL和SHUTDOWN IMMEDIATE）的時候，會觸發CHECKPOINT事件，
將重做記錄檔中的資料寫入資料檔案中，並且將上面的4中SCN都更新為最新的值。
當Oracle資料庫正常啟動並且啟動並執行時候，控制檔案檢查點SCN，資料檔案檢查點SCN，每個資料檔案中的啟動SCN都是一致的，
控制檔案中的每個資料檔案的結束SCN都是NULL。

在需要的時候，系統會自動根據時間戳記自動產生新的SCN，可以從DUAL表中查看到當前系統產生的最新的SCN，代碼如下：
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

 

-----------------------------------------------------------------------------------

應用情境99:查看控制檔案的資訊

應用情境100:建立初始控制檔案

應用情境101:建立控制檔案副本

應用情境102：建立新的控制檔案

SELECT MEMBER FROM V$LOGFILE;
SELECT NAME FROM V$DATAFILE;

應用情境103：恢複空進檔案
1.控制檔案被破壞，但儲存控制檔案的目錄仍可以訪問：
使用SHUTDOWN命令關閉資料庫執行個體，然後作業系統命令將控制檔案副本複製到控制檔案的目錄下，最後使用STARTUP命令開啟資料庫執行個體。
2.儲存介質被破壞，導致儲存控制檔案的目錄無法訪問
首先關閉資料庫執行個體，然後使用作業系統命令將控制檔案副本複製到一個新的可以訪問的目錄下。修改contro_files參數，將無效的控制檔案目錄修改為新的目錄。最後，使用STARTUP命令開啟資料庫執行個體。

應用情境104：刪除控制檔案

步驟：
（1）關閉資料庫
（2）編輯CONTROL_FILES參數的值，刪除指定的控制檔案資訊。
（3）將要刪除的控制檔案被分到其他介質，讓後使用作業系統命令將檔案刪除。
（4）重啟資料庫

應用情境105：查看資料檔案資訊

SELECT NAME,STATUS,BYTES FROM V$DATAFILE;

應用情境106：建立資料檔案

建立資料表空間，同時建立一個50MB的資料檔案
CREATE TABLESPACE MYTABS DATAFILE 'C:\Users\Administrator\Desktop\test\mytemfile01.dbf' SIZE 50M

建立暫存資料表空間，建立10MB的臨時檔案
CREATE TEMPORARY TABLESPACE TEMTBS TEMPFILE 'C:\Users\Administrator\Desktop\test\mytemp01.dbf'SIZE 50M ENTENT MANAGEMENT LOCAL;

使用alter tablespace語句修改資料表空間，可以使用add datafile關鍵字向資料表空間店家檔案

ALTER TABLESPACE MYTABS ADD DATAFILE 'C:\Users\Administrator\Desktop\test\mydata.dbf' SIZE 50M;

應用情境108：修改資料檔案的線上狀態

將線上的檔案修改為離線狀態
ALTER DATABASE DATAFILE 'C:\Users\Administrator\Desktop\test\USERS01.DBF' OFFLINE;

指定資料檔案設定成線上狀態
ALTER TABLESPACE HRMAN DATAFILE ONLINE;

應用情境109：刪除資料檔案

刪除資料表空間的mytab，同時刪除資料檔案
DROP TABLESPACE MYTAB INCLUDING CONTENTS CASCADE CONSTRAINTS;

刪除指定的資料檔案
ALTER DATABASE DATAFILE 'C:\Users\Administrator\Desktop\test\MYTAB.DBF' OFFLINE DROP;

應用情境110：查看重做日誌資訊

查看查詢檢視V$LOGFILE
SELECT GROUP#,STATUS,MEMBER FROM V$LOGFILE;

查看視圖V$LOG_HISTORY可以顯示重做日誌的曆史資訊
SELECT RECID, FIRST_CHANGE#, NEXT_CHANGE#, RESETLOGS_CHANGE# FROM V$LOG_HISTORY;

應用情境111：建立重做日誌組合成員
1.建立日誌組
ALTER DATABASE ADD LOGFILE ('log1c.rdo', 'log2c.rdo') SIZE 5000k;

ALTER DATABASE ADD LOGFILE GROUP 10 ('log1a.rdo', 'log2a.rdo') SIZE 5000k;

2.建立重做日誌成員
將重做記錄檔log3a.rdo添加到編號為10的重做日誌組中
ALTER DATABASE ADD LOGFILE MEMBER 'log3a.rdo' TO GROUP 10;

應用情境112：重新命名重做記錄檔

要求的權限：
alter database系統許可權
複製檔案到指定檔案的作業系統許可權
開啟和備份資料庫的許可權。

步驟：
（1）使用shutdown命令關閉資料庫
（2）將重做記錄檔複製到新的位置嗎，然後使用作業系統命名對其進行重新命名。
（3）使用startup mount 命令裝載到資料庫。
（4）使用帶rename file 子句的alter datafile語句重新命名資料庫的重做資料檔案

ALTER DATABASE
RENAME FILE 'log1a.rdo', 'log2a.rdo'
TO 'log1b.rdo', 'log2b.rdo';
（5）使用alter database open命令開啟資料庫

驗證重做記錄檔是否被改名
SELECT GROUP#,STATUS,MEMBER FROM V$LOGFILE;

應用情境113：刪除重做日誌組合成員

刪除重做日誌組：
ALTER DATABASE DROP LOGFILE GROUP 10;

刪除重做日誌成員：
ALTER DATABASE DROP LOGFILE MEMBER 'log2c.rdo';

應用情境114：清空重做記錄檔

ALTER DATABASE CLEAR LOGFILE GROUP 10;

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 10;

應用情境115：指定歸檔目的地

指定多個歸檔目的地的步驟如下：
（1）使用SHUTDOWN命令關閉資料庫
（2）使用SERBICE設定有效網路服務名

LOG_ARCHIVE_DEST_1 = 'LOCATION = C:\app\Administrator\product\11.1.0\db_1\database\archive'
LOG_ARCHIVE_DEST_2 = 'LOCATION = D:\app\Administrator\product\11.1.0\db_1\database\archive'
LOG_ARCHIVE_DEST_3 = 'LOCATION = F:\app\Administrator\product\11.1.0\db_1\database\archive'
（3）可以編輯初始化參數LOG_ARCHIVE_FORMAT設定文檔檔案的初始化模式
LOG_ARCHIVE_DEST_4 = 'SERVICE = standby1'

LOG_ARCHIVE_FORMAT = arch_%t_%s_%r.arc

LOG_ARCHIVE_DEST = 'C:\app\Administrator\product\11.1.0\db_1\database\archive
LOG_ARCHIVE_DUPLEX_DEST = 'D:\app\Administrator\product\11.1.0\db_1\database\archive'

應用情境116：管理歸檔模式
歸檔日誌的操作模式分為兩種：ARCHIVELOG(歸檔模式)和NOARCHIVELOG(非歸檔模式)

切換歸檔模式的步驟
（1）使用SHUTDOWN關閉資料檔案
（2）備份資料庫。
（3）編輯初始化參數檔案，設定歸檔記錄檔的位置
（4）使用STARTUP MOUNT語句歸檔記錄檔的位置
（5）使用ALTER DATABASE ARCHIVELOG語句切換到歸檔模式，或是切換到非歸檔模式。
（6）使用ALTER DATABASE OPEN語句重新開啟資料庫。

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4;

應用情境117：執行手動歸檔

設定資料庫手動歸檔模式：
ALTER DATABASE ARCHIVELOG MANUAL;
手動歸檔所有記錄檔：
ALTER SYSTEM ARCHIVE LOG ALL;

應用情境118：查看當前資料庫執行個體歸檔模式

SELECT LOG_MODE FROM V$DATABASE;

應用情境119：查看日誌資訊

應用情境120：使用V$ARCHIVED_LOG視圖查看曆史歸檔日誌資訊

SELECT NAME, SEQUENCE#, STATUS FROM V$ARCHIVED_LOG;

應用情境121：使用V$ARCHIVE_DEST視圖查看歸檔目的地的資訊

COL DEST_NAME FORMAT A20
COL DESTINATION FORMAT A20
SELECT DEST_NAME, STATUS, DESTINATION FROM V$ARCHIVE_DEST;

應用情境122：使用V$ARCHIVE_PROCESSES視圖查看歸檔進程資訊

SELECT * FROM V$ARCHIVE_PROCESSES;

應用情境123：使用V$TEMPFILE視圖臨時檔案資訊

SELECT RECID, STAMP, THREAD#, SEQUENCE# FROM V$BACKUP_REDOLOG;

應用情境124:使用V$TEMPFILE查看視圖臨時檔案資訊

COL NAME FORMAT A40
SELECT NAME, STATUS, BYTES FROM V$TEMPFILE;

應用情境125：查看警告記錄檔

SELECT VALUE FROM V$PARAMETER
WHERE NAME= 'background_dump_dest';