程式員的 Oracle 9i Database 基本管理指南

　程式員時間常不夠用，能熟悉某牌子的 SQL 語法就不錯了，大概很少有人會再花時間和大把銀子去深入學某一套資料庫的建置與管理，尤其像 Oracle 的每一門原廠授權課程都高達六萬多塊。以下是版工收集了一些我在自修 Oracle 9i 時，和程式員稍微比較有關的內容，僅簡要節錄出來。若有謬誤或可補充之內容，也歡迎網友來信告之。 大部份圖形介面的操作，可在操作過程中或最後一個步驟，顯示相對映的 PL/SQL 指令，若網友們忘記相關的 SQL 語法，也可以此方式顯示作為參考。本文範例若您無法執行，有可能是登入的帳號權限不夠，或 以非 SYSDBA 的身份 connect(只能查看不能修改資料)，或未架設 Management Server 而僅以 Standalone (Oracle 8.x 後才有)的方式啟動 Enterprise Manager 圖形介面管理工具。

(1) 透過圖形介面，查詢、修改、刪除「使用者A」建立的「Table B」：
A. Enterprise Manager Console → 資料庫名稱(SID) → Schema → 使用者A → Table B → 右邊分頁有 5 個分頁夾(General、Constraints、…)
B. Enterprise Manager Console → 資料庫名稱(SID) → Schema → 使用者A → Table B → 滑鼠右鍵 → ViewEdit Contents
● Constraints 分頁夾中若該筆記錄的 Type 為 Foreign Key，且在 Cascade On Delete 欄位勾選的話，則當其 Parent table 的資料被刪除時，該 Child table 的資料也會一併刪除。
(2) 讓暫時不用的資料庫，不要隨著 Win 2000 開機時啟動：
Configuration and Migration Tools → Administration Assistant for Windows NT → 資料庫名稱(SID) → 滑鼠右鍵 → Startup/Shutdown options →
取消勾選「Start up instance when service is started」
(3) 設定 Oracle memory structure 的 SGA(System Global Area)、PGA(Program Global Area)：
● 設定 Shared Pool(儲存 Shared cursor、Stored procedure，設定較大可有較好的運作績效)、Buffer Cache(存放最近由資料庫讀出來的資料，可減少 I/O 而提升運算效能，但會消耗系統記憶體)、Max. ConcurrentUsers(可同時連到資料庫的人數。此項必須有架設 Management Server 才會出現)。
Enterprise Manager Console → 資料庫名稱(SID) → Instance → Configuration → 記憶體(分頁夾)
(4) 監督、分配系統資源：
● 透過資源監督器(Resource monitor)、Resource Plans，監視、分配系統資源給不同的用戶、應用程式或 Resource Consumer Group，避免任由作業系統進行處理，導致 Oracle 負擔過重而運作變慢，或因此沒有時間進行預定的排程。
● 即使你分配給某一個 Resource Group 過多的系統資源(如:CPU)，若運作過程中沒用完所分配的資源，Oracle server 也會自動 Tuning 用剩的資源給別的用戶。
A. Enterprise Manager Console → 資料庫名稱(SID) → Instance → Configuration → 資源監督器(分頁夾)
B. Enterprise Manager Console → 資料庫名稱(SID) → Instance → Resource Plans
(5) A.觀察有誰正連上這台 Oracle server？　B.某個 Table 被某人的 Process 給 Lock 住，如何 Kill Session？　C.有誰下過什麼 SQL 指令？
A. Enterprise Manager Console → 資料庫名稱(SID) → Instance → Sessions
B. Enterprise Manager Console → 資料庫名稱(SID) → Instance → Sessions → 任一分頁夾，選左下方的「終止階段作業(kill session)」
C. Enterprise Manager Console → 資料庫名稱(SID) → Instance → Sessions → SQL(分頁夾) → 任一圖示 → 滑鼠右鍵 → View Details
(6) Lock 機制：
● 當有多方的使用者同時存取資料庫，為了避免 Dirty Write、Dirty Read，就必須在對資料做修改時進行某種程度的 Lock，以避免同一張 Table 的同一筆資料，同時被兩個以上的使用者修改，或是正在被修改中的資料也同時被讀取的情況。Oracle 在 Lock 機制上，原則是會自動依 SQL 指令決定 Lock 的方式，並以最低層次的 Lock 來確保最高的資料可用性，但必要時也可以用手動的方式做 Lock。
Enterprise Manager Console → 資料庫名稱(SID) → Instance → Sessions → 鎖定(分頁夾)
(7) 透過圖形介面，新增、移除、設定資料庫：
● 新增資料庫時，有分 General Purpose 和 New Database 等四種。前者包含 Datafiles，建立速度會快些，因其樣板包含了檔案結構、既存資料庫的實體檔案。此外 Datafiles 的功能還包括：A.新增、移除 Control file、Log group　B.改變參數檔案內容、Datafile 的名稱和存放位置。四種新增樣式的細項，亦可參考新增頁面右下方的 Show Details 按鈕。此外樣板亦可由自己建立(管理樣板)。
Configuration and Migration Tools → Database Configuration Assistant(簡稱DBCA)
(8) 透過圖形介面，新增 Table：
● 新建的 Table 其 Schema 屬於原建立的使用者，其他人欲存取須具備一定權限。可於建好 Table 後，在其上按滑鼠右鍵，選 Grant Privileges On 以設定存取權限。
● 新建過程中亦會顯示相對映的 SQL 語法。亦可於建好 Table 後，在其上按滑鼠右鍵，選「顯示物件 DDL」顯示完整語法。
Enterprise Manager Console → 資料庫名稱(SID) → Schema → 任一使用者 → Tables → 滑鼠右鍵 → 建立 or 使用精靈建立
(9) 透過圖形介面，建立 Index(索引)、View(檢視表)、Synonyms(同義字)、Sequence(流水號)、Cluster(叢集)：
● 建立過程中，或過程最後一步，亦可顯示相對映的 SQL 語法。
Enterprise Manager Console → 資料庫名稱(SID) → Schema → 任一使用者 → 打開下拉式選單 → 選要建立的功能 → 滑鼠右鍵 → 建立 or 使用精靈建立
(10) 安全管控 - 設定 User 或 Role(權限的集合，類似 Win NT 的群組)的存取、操作權限：
Enterprise Manager Console → Security → Users、Roles、Profiles
(11) 安全管控 - 登入者的認證由 Win 2000 Server 負責，權限由 Oracle 設定，設定包括可用的 Tablespace、Quota 使用量(此項功能版工未測試成功)：
Configuration and Migration Tools → Administration Assistant for Windows NT → 欲設定的資料庫名稱 → 滑鼠右鍵 → Connect Database → External OS Users →  ViewEdit Contents → Create → 選 Win NT 中的 Users 或 Groups → 設定對應的 Tablespace、Quota

設定完成後，在 Enterprise Manager Console → Security → Users 中，會多出一位名為「OPS$主機名稱\Win NT用戶名稱」的使用者，右邊分頁中的驗證方式顯示為「External」，即表示由 OS 進行身份驗證。接著以此用戶登入 Windows 2000，開啟命令提示字元，輸入「set ORACLE_SID=要登入的資料庫名稱」，再輸入「sqlplus」，即可以此用戶所擁有的權限進入 Oracle 來操作。
(12) 實體空間規劃 - Tablespace、Datafile、Redo log group 的建立與設定，並瞭解其運作情況：
Enterprise Manager Console → 資料庫名稱(SID) → 儲存(Storage Management) → 表格空間 or 資料檔 or 重做日誌群組
(13) 資料備份 - 使用 Export、Import：
● 有關 Oracle 資料備份的方式有很多種，還可分為：離線式、線上式、熱備份、冷備份，本文僅挑重點敘述。欲知詳細步驟及各種備份方式的特點請自行參考相關書籍。
● 此項 EXP 備份工具，較適合備份「部份 Table 或全部 Table」、「部份 Object 或所有 Object」，亦可備份「整個資料庫」。此工具將資料 Dump 出來是以 Binary 的方式，因此無法用記事本開啟檔案。此外，須具備足夠權限才能 Export 別人 Schema 的 Object，且有少數系統內建的 Schema 亦無法以此方式 Export。
Export (備份)：
開啟命令提示字元，輸入 "exp system/用戶密碼@資料庫名稱" → 詢問陣列緩衝區的大小(值的大小僅會影響備份速度) → 匯出檔案名稱(如：backup1.dmp，預設最後會將檔案匯出在 C 槽底下) → 詢問匯出類型(分為：1.整個資料庫　2.使用者　3.表格) → 接下來選項大部份都可選 "yes"(例如是否要壓縮) → 自動開始備份

Import (回復)：
開啟命令提示字元，輸入 "imp system/用戶密碼@資料庫名稱" → 詢問從哪個檔案回復 → 詢問陣列緩衝區的大小 → 詢問「是否僅列出匯入檔案的內容」(若選擇 "no"，會逐步詢問以下的相關資料，否則會跳過一些步驟) → 告知「物件已存在，建立物件的錯誤被略過不予以處理」(預設值為 "no"，則系統碰到錯誤就會顯示錯誤訊息) → 詢問「是否匯入整個匯出檔」(若選 "yes" 則會將原先所有 Export 的資料全部匯入；若選 "no" 則會詢問是要匯入哪位 User 的 Schema、哪一個 Table) → 自動開始回復

● 此項 Export、Import 的備份及回復功能，必須在資料庫啟動時才能操作。版工試過可在有使用者連線時備份和回復，且結束時亦顯示未發生任何錯誤。但建議實務上還是儘量選在使用者離線時做備份，以避免影響運作績效或對用戶作業造成干擾，甚至造成資料的不一致或毀損。
(14) 資料備份 - SQLLDR：
● 支援「純文字檔」的匯入，以進行不同廠牌(異質)資料庫的資料移轉。亦可作為沒有 ODBC 之類介面或其它可用轉換程式時的資料轉換替代方案。有關其詳細步驟請網友自行參閱相關書籍。
(15) 資料備份 - RMAN：
● 一般 Oracle 的熱備份方式較麻煩，不但要一個 Tablespace 、一個 Tablespace 的備份，備份時還得擔心會影響到資料庫的效能。所以 Oracle 提供了一個工具，叫做「Recover Manager」或簡稱「RMAN」，透過這個工具，熱備份變得非常簡單。且 RMAN 可以只備份與上一次備份有變更的部分，而不是整個資料庫，可節省備份的時間和空間。
備份：
step 1. Enterprise Manager Console → 資料庫名稱 → Instance → Configuration → 復原(分頁夾) → 勾選以啟動「存檔日誌(Archive)」→ 接下列的 step 2.
step 2. 開啟命令提示字元，輸入 "rman" → 輸入 "connect TARGET sys/用戶密碼@資料庫名稱" → [若輸入 "report schema;" 可瀏覽目前資料庫中有哪些 Schema] → 若想備份整個資料庫，輸入 "backup database;"；若想備份一個 Tablespace，輸入 "backup tablespace 要備份的表格空間名稱;" → 自動開始備份 → [備份完成後，若輸入 "list backup;" 可看到歷次備份的記錄、時間、備份類型]
● 若想做「增益備份」(Incremental backup)，亦即只想備份有異動的部分，可在上述的最後一個必要步驟中，改為輸入 "backup incremental level 1 database;"。只要曾做過上述的基本備份後，日後再做此增益備份時，就會只備份有異動的部分。

回復 (這部分版工未試成功，若有熟悉此功能的網友懇請來信指教)：
 ● 若有 Datafile 資料毀損的災難發生，可先 shutdown 資料庫，並將原有的 Datafile 暫時 rename，但 Control File 不要 rename，再重新啟動資料庫。此時 Instance 可以啟動，但由於 Datafile 已 rename 因此無法正確 Mount，在這個時間點下，開啟命令提示字元，輸入 "rman" → 輸入 "connect TARGET sys/用戶密碼@資料庫名稱" → 輸入 "restore database;" → 輸入 "recover database;" ，即可進行資料庫的回復，然後再重新啟動資料庫 [1]。
● RMAN 有記憶的功能，不論是使用 Control File 或是另外開一個 Recovery Catalog 空間來存放資料，RMAN 會記得你之前所備份的資料，以及相關的詳細資訊。此時可用 Script 指令，包含三個動作的 run Script：一、告訴 RMAN 你要用 Disk 或 Tape(磁帶)，或是兩者都可以。　二、讓 RMAN 將欲修復的部分從備份中 Restore 回來。　三、讓 RMAN 修復這個部分。底下的指令包含了最簡單，但確實可行的指令，這個 Script 可以把一個幾乎完全損毀的資料庫修復回來 [2],[3]。
RMAN>run {
2>allocate channel dev1 type disk;
3>restore database;
4>recover database;
5>}
(16) 資料備份 - Offline備份：
在資料庫 shutdown(冷備份)，或將相關的 Datafile 暫時鎖住(熱備份)時， 直接拷貝實體檔案進行備份，這些檔案包括：Control File、Parameter File、Datafile、Redo Log File，以及相關的設定檔(如 Remote Password File)。
(17) 不同廠牌(異質)資料庫的轉移工具：
　

參考文件 :
 [1] 林存德，2004，「Oracle 9i 資料庫管理指南」，旗標出版社。
[2] 網路文件、對岸技術文件。
● 艾群科技電子報：Oracle 9i 的冷熱備份 - 1、2：http://www.ever.com.tw/ePaper/ePaper3.html
● Oracle RMAN 快速入門指南：http://www.yesky.com/266/1721266_2.shtml
● ORACLE 備份的分類：http://202.101.18.180:81/gate/big5/www.dev-club.com/club/bbs/showEssence.asp?id=26249
● Oracle Rman/tivoli-TDP/下數據恢復到節點：http://big5.ccidnet.com:89/gate/big5/tech.ccidnet.com/art/985/20030718/55341_1.html
● ORACLE 之常用 FAQ V1.0 (3)：http://big5.webasp.net/article/21/20900.htm
[3] Oracle 9i 教材與相關書籍、Oracle9i User-Managed Backup and Recovery Guide Release 2 (9.2)

文 / 版工 -
Last modified : 2005/10/16  
回本站首頁