oracle資料庫學習入門篇

oracle建立資料表空間及使用者

oracle的學習對於像我這樣的新手，還是要一步一步來，先把基本概念搞清楚。

我覺得oracle學習可以分為幾個部分：
系統基本使用
系統調優
sql語句編寫
sql調優
PL/SQL程式開發

以下討論都是在單主機模式下進行。歡迎討論指正。

問題1：什麼是執行個體？

一個oracle軟體安裝完之後，它有一個預設的執行個體(instance)，同時使用者還可以在oracle中增加執行個體。
執行個體是一個記憶體結構（SGA+後台進程），每個執行個體有一組自己的進程組，用來管理運行執行個體。執行個體中包含了資料表空間、使用者賬戶。

執行個體和執行個體之間是相互獨立的。在邏輯上每個執行個體有自己的資料表空間、自己的使用者。在物理上資料表空間內表的結構、資料和使用者資訊，儲存在磁碟的資料檔案和使用者檔案中。

一個執行個體只能開啟一個資料庫。

問題2：什麼是資料表空間？
資料表空間是oracle中的一種邏輯概念。用來分類使用者、控制使用者表的大小。
資料表空間相當於一個筐，使用者的資料檔案相當於一個個貨物，貨物的大小不能超過筐的大小。

問題3：平時sql操作時說oracle裡的一個資料庫是指什麼，一個執行個體？一個資料表空間？一個使用者？
通俗的理解可以是：在一個執行個體中，某個資料表空間裡的，屬於某個使用者的，所有的表組成的集合。是從使用者的緯度來看的。

比較mysql和oracle建立資料庫的過程。
mysql中：
建立使用者
建立資料庫(create database命令)
授權使用者對資料庫的許可權
建立表...

oracle中：
建立使用者
建立資料表空間
指定使用者的資料表空間
指定使用者空間配額
賦予使用者建立資料庫物件的許可權
建立表...

問題4：在解釋執行個體時說“一個執行個體只能開啟一個資料庫”和問題3中說的資料庫定義有什麼區別？
是兩個不同的概念，執行個體開啟資料庫是指執行個體在啟動時掛載的資料庫mount database。
這個掛載的資料庫是一堆在磁碟上的物理檔案，包括了執行個體啟動時需要的(datafile、redo log file、control file、archive log file、parameter file)等檔案。
問題3中講的資料庫，是指sql語句操作的對象。

總結以上問題：

建立資料表空間及使用者方法如下。

用管理員賬戶登入。

建立使用者：

create user wangyi identified by 123456;

建立資料表空間：

create tablespace users1 datafile '/u01/app/oracle/oradata/XE/users1.dbf' size 200m autoextend on next 32m maxsize 1024m extent management local;

//users1資料表空間初始大小200m，每次增加32m，最大1024m。

指定wangyi使用者的資料表空間為users1：

alter user wangyi default tablespace users1 temporary tablespace temp;

//指定wangyi使用者預設資料表空間users1，暫存資料表空間temp。

指定wangyi使用者使用資料表空間的配額：

alter user wangyi quota unlimited on users1;

賦予使用者權限：

grant create session, create table, create view, create any index to wangyi;

下一篇討論下oracle的檔案系統。

racle檔案系統簡述

在上一篇講資料表空間中說到，在啟動執行個體時，需要載入資料庫，而這個資料庫是由datafile、redo log file、control file、archive log file、parameter file等一堆檔案組成的，那麼這些檔案分別有什麼作用呢？

1、oracle的開機檔案
開機檔案的位置在$ORACLE_HOME/dbs下，有兩個檔案init.ora和spfile<inst_name>.ora，inst_name是當前執行個體名稱。
這兩個檔案定義了啟動的參數。init.ora是文字檔，spfile<inst_name>.ora是二進位檔案。

注意：spfile<inst_name>.ora不能用文字編輯器修改，開啟也不行，可能會造成執行個體無法啟動。需要用alter命令修改。

2、oracle的控制檔案
控制檔案是*.ctl，10g在/u01/oracle/oradata/<inst_name> 目錄下。形如：control01.ctl、control02.ctl、control03.ctl。
11xe版在/u01/app/oracle/oradata/<inst_name> 目錄下，但是檔案名稱變為了control.dbf，本質都是資料檔案（二進位格式），只是檔案尾碼不一樣。

3、oracle的資料檔案
資料檔案是*.dbf，10g也在/u01/oracle/oradata/<inst_name> 目錄下。在建立資料表空間時需要指定的資料檔案就是.dbf。資料庫中的表是儲存在資料檔案中的。
11xe版在/u01/app/oracle/oradata/<inst_name> 目錄下。

4、oracle的其它參數檔案
監聽程式的參數檔案：
10g在
/u01/oracle/product/10.2.0/db_1/network/admin/listener.ora
/u01/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
11xe版在
/u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
/u01/app/oracle/product/11.2.0/xe/network/admin/tnsnames.ora

5、oracle的記錄檔
記錄檔分三大類：
alert log files 警報日誌
trace log files 追蹤記錄檔（使用者和進程）
redo log file 重做日誌（記錄資料庫的更改）

5.1 alert日誌
記錄oracle系統啟動並執行日誌，包括系統參數改變，系統出錯等。
10g在/u01/oracle/admin/orcl/bdump/alert_orcl.log
11xe版在/u01/app/oracle/diag/rdbms/xe/XE/trace/alert_XE.log

5.2 trace日誌
以.trc為尾碼的檔案，記錄了各種sql操作及所消耗的時間等，根據trace檔案我們就可以瞭解哪些sql導致了系統的效能瓶頸，進而採取恰當的方式調優。
10g在/u01/oracle/admin/orcl/udump/目錄下
11xe版在/u01/app/oracle/diag/rdbms/xe/XE/trace/目錄下

5.3 重做記錄檔
重做日誌分為
online redo log files 線上重做日誌
archive redo log files 歸檔重做日誌

1）線上重做日誌
又叫聯機重做日誌，以sql指令碼的形式即時記錄對資料庫修改的資訊，包括使用者對資料修改和資料庫管理員對資料庫結構的修改。

聯機重做記錄檔是迴圈使用的。當第一個記錄檔達到一定數量時，就會停止寫入，而轉向第二個記錄檔，第二個滿轉向第三個記錄檔。第三個滿就向第一個記錄檔寫入。而第一個記錄檔有沒有自動備份就涉及到歸檔或者不歸檔的問題。當資料庫自動對原來的記錄檔進行備份的話就叫歸檔模式，不需要對資料庫進行自動備份就叫非歸檔模式。

在歸檔模式下，當聯機重做日誌寫滿後，則將聯機重做日誌歸檔到磁碟，成為歸檔重做日誌。
在非歸檔模式下，當聯機重做日誌寫滿後，則覆蓋第一個日誌的內容，繼續迴圈寫。

10g聯機重做日誌在/u01/oracle/oradata/orcl/目錄下，形如：redo01.log、redo02.log、redo03.log。

2）歸檔重做日誌
簡稱歸檔日誌，指當條件滿足時，Oracle將線上重做日誌以檔案形式儲存到硬碟（持久化）。

其實，所謂的歸檔，就是指將線上日誌進行歸檔、持久化到成固定的檔案到硬碟，便於以後的恢複和查詢。

當然，前提條件是資料庫要處于歸檔模式。

歸檔日誌的位置由DB_RECOVERY_FILE_DEST參數定義，預設在flash recovery area的路徑。裡面存放有歸檔日誌、閃回日誌等檔案。
10g在/u01/oracle/flash_recovery_area
11xe版在/u01/app/oracle/fast_recovery_area

6、oracle的密碼檔案
密碼檔案作用是對資料庫特殊使用者進行許可權認證。
10g在/u01/oracle/product/10.2.0/db_1/dbs/orapw<inst_name>
11xe版在/u01/app/oracle/product/11.2.0/xe/dbs/orapwXE

 

oracle歸檔模式和非歸檔模式切換
oracle啟動順序簡述

 

上一篇說到oracle的記錄檔有聯機重做日誌和歸檔重做日誌，只有在歸檔模式下，系統才會去寫歸檔重做日誌。

 

使用archive log list查看是否在歸檔模式下：

SQL> archive log list Database log mode       No Archive Mode Automatic archival       Disabled Archive destination       USE_DB_RECOVERY_FILE_DEST Oldest online log sequence     176 Current log sequence       177 

 

一、非歸檔模式改為歸檔模式
1、命令：

shutdown;
startup mount;
alter database archivelog;
alter database open;
archive log list;

2、執行過程：

SQL> shutdown;   Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started.  Total System Global Area  835104768 bytes Fixed Size    2231096 bytes Variable Size  624952520 bytes Database Buffers  205520896 bytes Redo Buffers    2400256 bytes Database mounted. SQL> alter database archivelog;  Database altered.  SQL> alter database open;  Database altered.  SQL> archive log list; Database log mode       Archive Mode Automatic archival       Enabled Archive destination       USE_DB_RECOVERY_FILE_DEST Oldest online log sequence     176 Next log sequence to archive   177 Current log sequence       177 

 

二、歸檔模式改為非歸檔模式
1、命令：

shutdown;
startup mount;
alter database noarchivelog;
alter database open;
archive log list;

2、執行過程：

SQL> shutdown; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started.  Total System Global Area  835104768 bytes Fixed Size    2231096 bytes Variable Size  624952520 bytes Database Buffers  205520896 bytes Redo Buffers    2400256 bytes Database mounted. SQL> alter database noarchivelog;   Database altered.  SQL> alter database open;  Database altered.  SQL> archive log list; Database log mode       No Archive Mode Automatic archival       Disabled Archive destination       USE_DB_RECOVERY_FILE_DEST Oldest online log sequence     176 Current log sequence       177 

 

三、oracle啟動順序
我們會發現平時啟動oracle用startup命令，而在設定歸檔模式時使用的是startup mount命令，所以oracle啟動肯定是分為幾個階段的，在mount這個階段才能修改歸檔模式。

 

可以看到啟動時狀態由 nomount -> mount -> open
1、nomount狀態
nomount的過程就是啟動oracle資料庫執行個體的過程。在這個階段oracle首先會尋找參數檔案（pfile或者spfile），然後根據參數檔案中的設定，建立資料庫執行個體（分配記憶體，建立後台進程）。

 

2、mount狀態
啟動到mount狀態後，oracle就可以從spfile或pfile中擷取了control file的位置資訊，找到控制檔案，並讀取控制檔案。
在這個狀態下可以對資料檔案和記錄檔做管理類操作。

 

3、open狀態
在這個階段，首先會對每個資料檔案做一檢查，檢查資料檔案頭中的檢查點計數（Checkpoint CNT）是否和控制檔案中的檢查點計數一致，然後再開啟資料庫，鎖定資料檔案。

 

4、startup命令等於以下三個命令組合

startup nomount;
alter database mount;
alter database open;