以下為在網路上找到的資訊內容,不懂的人可以參考看看^^

ORACLE傻 瓜 手 冊



To be DBA or not to be, that is NOT the question. ---- Arron



作者允許自由散發此文檔,但對其進行的任何修改應通知作者,以便於維護版本。

作者email:zhou_arron@163.com

Oracle8以8.1.5為界分為普通版本和internet版本。普通版版本號8.0.x,接觸較多的是8.0.5;internet版版本號包括8.1.5(Release 1),8.1.6(Release 2),8.1.7(Release 3)。普通版簡稱Oracle 8,internet版簡稱Oracle 8i。如果不作特別說明,文中凡出現Oracle 8i均指8.1.7版。

Oracle9i目前出到第二版,版本號為9.2,簡稱Oracle 9i。如果不作特別說明,文中凡出現Oracle 9i均指9.2版。

本手冊介紹Oracle配置的基本方法,描述的是“所然”而不是“所以然”。全部操作以命令行方式出現,不涉及GUI(只有白刃戰才是真正的戰鬥)。鑒於大家對Windows已經十分熟悉,同時為了避免Windows和Unix兩種截然不同的使用和開發風格給描述帶來的複雜性,所以本手冊不介紹在Windows上的Oracle(上帝的歸上帝,愷撒的歸愷撒)。

文中所有例子以oradb作為資料庫實例名,資料庫用戶dbuser,口令oracle。如果不作特別說明,關於Oracle 8i所有的例子都在Solaris 8 Intel Platform+Oracle 8iR3上通過,關於Oracle 9i所有的例子都在RedHat Linux 7.3+Oracle 9iR2上通過。附錄文件sample.tar包含全部示例,簡稱附錄。

大量使用表emp作為例子(參見附錄08_proc/proc/single/emp.sql):

create table emp

(

no number(12) not null,

name char(20) not null,

age number(6) not null,

duty char(1) not null,

salary number(12) not null,

upd_ts date not null,

primary key (no)

);

開發中對應emp表結構,定義其宿主結構(參見附錄08_proc/proc/single/db.h):

typedef struct

{

double no;

char name[21];

int age;

char duty[2];

double salary;

char upd_ts[15];

} emp_t;

修改歷史:

2000/07 版本1.0

2000/09 版本1.1

增加Linux安裝,export,import使用,資料庫監控及優化(utlbstat,utlestat,分析session),語言時間環境變數設置,Oracle8.0.5手工建庫腳本(wei_dick提供,稍加修改)

2000/10 版本1.2

修改Linux安裝中RedHat 6.x+Oracle 8.1.6、資料庫優化中配置檔和session分析、常用技巧中下載上傳文本資料和訪問他機資料庫;增加創建資料庫實例中資料字典參考、常用技巧中刪除冗余記錄、應用開發,常見錯誤

感謝liu_freeman,jiao_julian,huang_miles等人對開發工具所作的努力

2001/03 版本1.3

修改安裝部分、init.ora配置、常用技巧、應用開發;增加手工建庫、MTS配置;重寫開發工具

感謝li_bo的大力幫助

2001/09 版本1.4

修改資料庫優化,使之較系統化;增加應用開發中多線程下的資料庫連接

2002/04 版本1.5

修改資料庫優化、多線程條件下資料庫編程;分離附錄的程式範例

2002/12 版本2.0

重新安排內容,增加Oracle 9i安裝配置、OCI開發、mysql安裝配置開發,補充資料庫優化、PROC開發





ORACLE傻 瓜 手 冊 1

1 安裝 6

1.1 通用設置 6

1.2 UnixWare7 7

1.2.1 Oracle 8 7

1.3 HP-UX 8

1.3.1 Oracle 8 8

1.4 Linux 9

1.4.1 kernel 2.0 & glibc 2.0 9

1.4.2 kernel 2.2 & glibc 2.1 9

1.4.3 kernel 2.4 & glibc 2.2 10

1.5 Solaris 11

2 創建 13

2.1 Oracle 8 & 8i 13

2.1.1 工具創建 13

2.1.2 手工創建 13

2.1.3 MTS(multi-threaded server) 14

2.1.4 調整臨時表空間 15

2.1.5 調整回滾表空間 15

2.1.6 調整日誌 15

2.1.7 調整用戶表空間 16

2.1.8 創建用戶 17

2.1.9 創建資料物件 17

2.1.10 創建唯讀用戶 18

2.1.11 啟動及關閉資料庫實例 19

2.1.12 網路配置 19

2.2 Oracle 9i 21

2.2.1 手工創建 21

2.2.2 創建用戶表空間 22

3 初始化檔配置 23

3.1 Oracle 8 & 8i 23

3.2 Oracle 9i 25

4 工具 26

4.1 sqlldr 26

4.2 exp 27

4.3 imp 28

4.4 sqlplus 29

4.4.1 命令行參數 29

4.4.2 提示符命令 29

4.4.3 SET選項 30

4.4.4 例子 30

5 備份及恢復 32

5.1 export與import方式 32

5.2 冷備份 32

5.3 聯機全備份+日誌備份 32

5.3.1 設置 32

5.3.2 步驟 33

5.3.3 恢復 33

5.4 注意要點 34

6 資料庫優化 35

6.1 通用設置 35

6.1.1 硬體配置 35

6.1.2 應用配置 35

6.1.3 日常性能監控 36

6.2 實戰分析 36

6.2.1 總體分析 37

6.2.2 詳細分析 37

6.3 專題分析 39

6.3.1 巨表查詢 39

6.3.2 對比測試 41

6.3.3 上下載數據 44

6.3.4 回滾空間快照陳舊(snapshot too old) 46

7 常用技巧 48

7.1 增加、更改和刪除域 48

7.2 刪除冗餘記錄 49

7.3 更改字元集 49

7.4 表數據遷移 50

7.5 成批生成資料 50

7.6 注意要點 51

8 嵌入式SQL(C) 53

8.1 編譯 53

8.2 SQL語句 54

8.2.1 內部類型與宿主類型對應 54

8.2.2 連接和斷開 54

8.2.3 事務 55

8.2.4 標準SQL語句 55

8.2.5 動態SQL語句 55

8.2.6 陣列操作 56

8.3 編程框架 58

8.3.1 總體原則 58

8.3.2 單線程和多線程 59

8.3.3 開發工具 60

9 OCI—Oracle Call Interface 61

9.1 連接和斷開 61

9.1.1 控制碼層次 61

9.1.2 連接流程 61

9.1.3 斷開流程 62

9.2 SQL語句 62

9.2.1 事務 62

9.2.2 無結果集的sql語句 63

9.2.3 有結果集的sql語句 63

9.2.4 LOB 65

9.3 編程框架 67

9.3.1 總體原則 67

9.3.2 sql語句 68

9.3.3 函數 69

10 附錄—MYSQL 72

10.1 安裝配置 72

10.2 管理 72

10.2.1 初始調整 72

10.2.2 建立用戶物件 73

10.3 開發 73

10.3.1 連接和斷開 73

10.3.2 無結果集的sql語句 74

10.3.3 有結果集的sql 74

10.3.4 錯誤處理 75





1 安裝

所有參見內容都在附件01_install_02_create_03_init/下。

1.1 通用設置

檔系統swap

創建檔系統時應考慮Oracle對swap的需要,大約每個oracle服務進程將佔用10-20Mswap空間,通常作業系統建議2倍於記憶體的swap空間,資料庫系統可能要求更多些。

作業系統用戶和環境變數

Oracle文檔要求為資料庫系統的管理和使用建立3個或更多的組,但這個需求是可以忽略的,實踐中並沒有體現其必要性。為簡化操作起見,只建立dba組,即擁有更新軟體和管理最高許可權(SYSDBA)的作業系統用戶組,此組稱為OSDBA,屬於此組的用戶可以SYSDBA身份登錄進任何一個資料庫實例,簡單的,只建立一個用戶,習慣上使用oracle的名稱。

$ groupadd dba

$ useradd –g dba –d /home/oracle –m –s /bin/bash oracle

確定oracle 系統的根目錄ORACLE_BASE,如/opt/oracle,所有的軟體和配置都在這個目錄下展開,雖然並非一定需要如此,但這是一個良好的習慣。同時確定軟體安裝的起始點ORACLE_HOME,通常在ORACLE_BASE下。

修改oracle用戶的.profile,加入以下各行,或者修改/etc/profile,使每一個用戶都獲得環境變數設置

umask 022

ORACLE_BASE=/opt/oracle

ORACLE_HOME=$ORACLE_BASE/product/{版本號}(如8.0.5,8.1.7,9.2.0等)

ORACLE_SID=oradb

ORACLE_TERM=ansi #僅與Oracle8字元介面安裝有關

ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data #字元集支援

NLS_LANG=American_America.{ZHS16CGB231280(Oracle8支持)|ZHS16GBK(Oracle8i支持)|ZHS16GB18030(Oracle9i支持)}

NLS_DATE_FORMAT=YYYYMMDDHH24MISS

LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH #動態連接路徑,Unixware中要確保/usr/ucb/lib在/usr/ccs/lib之後出現

TMPDIR=/tmp #安裝中Oracle會在此目錄下存儲相當數量的檔,所以TMPDIR所在的磁片分區要確保空閒空間的大小,至少在1G左右

PATH=$PATH:$ORACLE_HOME/bin

export ORACLE_BASE ORACLE_HOME ORACLE_SID ORACLE_TERM ORA_NLS33 NLS_LANG NLS_DATE_FORMAT LD_LIBRARY_PATH TMPDIR

參見profile。

注意:

NLS_LANG=American_America.ZHS16CGB231280(ZHS16GBK)

“American”指顯示資訊時所用的語言,竊以為憑大家的英語水平足夠應付,如改為SIMPLIFIED CHINESE,在不帶中文支援的終端上就沒人能看懂了。

“America”指地區

“ZHS16CGB231280”指Client工具使用的字元集,一般使用”ZHS16CGB231280”,Oracle8i已支持到”ZHS16GBK”

NLS_DATE_FORMAT=YYYYMMDDHH24MISS

Oracle的date類型過於靈活,為統一時間格式,利於編程,應將時間的輸入輸出格式限定為14位字串,如”20000101235959”

據oracle文檔,此參數可按照session,作業系統用戶環境,init.ora由高到低的優先順序順序設置,依次覆蓋。

相關系統表:

v$nls_parameters v$nls_valid_values

X-Window

Oracle 8的安裝程式是光碟mount點/bin/orainst,使用字元介面,不用考慮X-Window。

Oracle 8i和9i使用光碟mount點/runInstaller進行安裝,它是用Java編寫的圖形介面,對中文處理有問題,所以應在進入X-Window前確保語言(LANG)和地域(LC_ALL,LC_TYPE,…)環境變數不是中文。

LANG=C

LC_ALL=C

安裝選項

Oracle 8i的主要套裝軟體在安裝選項Enterprise中,但並不包括proc,必須進行第二次安裝,可選擇安裝選項Client中的programmer。

Oracle 9i的主要套裝軟體在安裝選項Enterprise安裝選項中,但並不包括proc,必須進行第二次安裝,一定要選擇安裝選項Client中的Administrator。runInstaller的穩定性欠佳,建議每次安裝結束後,先退出,再進行下一次安裝。

1.2 UnixWare7

1.2.1 Oracle 8

確認作業系統的交換分區swap不少於350M

認為該打的補丁統統打上,甯濫毋缺。UnixWare7.0.1必須打的補丁為ptf7033,ptf7051,ptf7052,ptf7068,ptf7096。

將/etc/default/login中的ulimit設為大於2113674(稍大一點即可,太大會有問題)

將/etc/conf/node.d/async中的600改為666

修改以下核心參數

核心參數 必需值 解釋

SHMMAX 2147483647 共用記憶體段最大尺寸

SHMMNI 100 系統共用記憶體段標識最大數目

SHMSEG 15 每個進程所能使用最大共用記憶體段數目

SEMMNI 100 核心信號量標識最大數目

SEMMSL 150 每個信號量標識包含的信號量個數

SCORLIM 0X7FFFFFFF Core文件最大尺寸

HCORLIM 0X7FFFFFFF

SDATLIM 0X7FFFFFFF 進程堆最大尺寸

HDATLIM 0X7FFFFFFF

SVMMLIM 0X7FFFFFFF 進程最大映射位址

HVMMLIM 0X7FFFFFFF

SFSZLIM 0X7FFFFFFF 進程檔最大偏移量

HFSZLIM 0X7FFFFFFF

SFNOLIM 128 進程能打開的最大檔個數

HFNOLIM 2048

NPROC 20+(8*MAXUSERS) MAX:125000

ARG_MAX 1,048,576

NPBUF 100 I/O緩衝區數目

MAXUP 1000 用戶同時使用的最大進程個數

STRTHRESH 0X500000 流能使用的最大位元組數



為優化應用系統修改以下核心參數

核心參數 參考值 解釋

MSGMAX 8192 消息最大尺寸

MSGMNB 81920 消息佇列尺寸

MSGMNI 2048 系統能並存的最大消息佇列數目

MSGSSZ 16384

MSGTQL 4096 系統能並用的消息頭數目

SEMMNI 1024

SEMMSL 150



也可通過編輯/etc/conf/cf.d/stune達到同樣效果

重新連接內核,重起或運行/etc/conf/bin/idbuild –B

修改核心參數SEMMAP時,注意要同時修改/etc/conf/mtune.d/ipc中相應的MAX值

建立/var/opt/oracle,使oracle成為此目錄屬主

mount oracle光碟,通常mount目錄為/SD-CDROM_1

root用戶,ORACLE_OWNER=oracle,執行光碟上orainst中oratab.sh,建立/var/opt/oracle/oratab

安裝時,選custom方式,安裝時不建立資料庫,字元集可選Simplified Chinese

1.3 HP-UX

1.3.1 Oracle 8

流程大致與unixware相同,調整kernel參數可通過sam,選擇/Kernel Configuration/Actions/Apply Tuned Parameter Set/OLTP Database Server System,另外為提高I/O能力,還需調整以下參數:

核心參數 參考值 解釋

bufpages 61992 緩衝頁

dbc_max_pct 10 動態緩存占記憶體最大百分比

dbc_min_pct 10 動態緩存占記憶體最小百分比

nbuf

設定共用庫目錄SHLIB_PATH,不是LD_LIBRARY_PATH

SHLIB_PATH=$SHLIB_PATH:$ORACLE_HOME/lib;export SHLIB_PATH

1.4 Linux

1.4.1 kernel 2.0 & glibc 2.0

代表產品為Red Hat Linux 5.1。

Oracle 8在RedHat5.1上能成功安裝,安裝套裝軟體為805ship.tgz

一般不會在RedHat5.1上安裝Oracle8i以上的版本

修改共用記憶體最大尺寸限制:

在系統初始化腳本/etc/rc.d/rc.sysinit中加入:

echo 2147483648 >/proc/sys/kernel/shmmax

重啟電腦。這樣做避免了Oracle分配的共用記憶體碎片化,對提高效率有好處。

原$ORACLE_HOME/precomp/admin/pcscfg.cfg中sys_include有誤,使proc預處理pc程式失敗,安裝結束後,應設為:sys_include=(/usr/include,/usr/lib/gcc -lib/i386-redhat-linux/egcs-2.91.66/include)(視gcc版本而定)

1.4.2 kernel 2.2 & glibc 2.1

代表產品為Red Hat Linux 6.2。

修改共用記憶體最大尺寸限制:

在系統初始化腳本/etc/rc.d/rc.sysinit中加入:

echo 2147483648 >/proc/sys/kernel/shmmax

重啟電腦。這樣做避免了Oracle分配的共用記憶體碎片化,對提高效率有好處。

原$ORACLE_HOME/precomp/admin/pcscfg.cfg中sys_include有誤,使proc預處理pc程式失敗,安裝結束後,應設為sys_include=(/usr/include,/usr/lib/gcc -lib/i386-redhat-linux/egcs-2.91.66/include) (視gcc版本而定)

1.4.2.1 Oracle 8

本來已經很少有人在LinuxKernel2.2的系統中安裝Oracle8.0.5,但筆者實在懷念8.0.5純粹的文本介面和與之相處的無數不眠之夜,故收錄如下:

Oracle8在kernel為2.2.x的linux中是無法正常運行的,運行可執行檔如svrmgrl,sqlplus時會導致“Segmentation fault”,原因在於這些linux使用了默認的libc2.1,與Oracle8程式重連接所需的libc2.0不相容。Oracle的補丁程式其實是將Oracle可執行程式的重連接腳本中libc位置重新定位到libc2.0上去,並用舊版的gcc,ld重新連接可執行檔。為此必須先在系統中安裝相容庫和相應工具。這是權宜之計,而且僅對RedHat有效。

root用戶

rpm –ivh tcl-8.0.3-20.i386.rpm Oracle的Intelligent Agent要使用

rpm –ivh compat-binutils-5.2-2.9.1.0.23.1.i386.rpm

rpm –ivh compat-glibc-5.2-2.0.7.1.i386.rpm

rpm –ivh compat-egcs-5.2-1.0.3a.1.i386.rpm

rpm –ivh compat-egcs-c++-5.2-1.0.3a.1.i386.rpm

rpm –ivh compat-libs-5.2-1.i386.rpm

版本號可略有差異

oracle用戶安裝Oracle8.0.5但不創建instance,如選擇安裝文檔,則會產生如下錯誤:

A write error occurred while try to copy ‘/home/oracle/setup_oracle/unixdoc/server.805/install/lnx_server.805.map’ to ‘/oracle/product/8.0.5/doc/server.805/install/lnx_server.805’(No such file or directory).

這是安裝程式的一個bug—不能創建目錄。可進入$ORACLE_HOME/doc,mkdir –p server.805/install,再選擇Retry

從ftp.oracle.com/pub/www/otn/linux下載glibcpatch.tgz,在某一目錄(如~/patch)下展開

cd ~/patch

glibcpatch.sh

經過一段時間後,看到“Applied glibc patch for Oracle 8.0.5.x successfully”,表明補丁成功。此時就能成功創建instance。

1.4.2.2 Oracle 8i

推薦使用典型安裝,否則會產生難以預料的錯誤。

1.4.3 kernel 2.4 & glibc 2.2

代表產品為Red Hat Linux 7.3,SuSE Linux 7.3。

1.4.3.1 Oracle 8i

與Oracle8在RedHat Linux 6.2上安裝所遇到的問題一樣,Oracle 8i使用的glibc 2.1與作業系統自帶的glibc 2.2不能相容,解決的方法也一樣,要安裝glibc 2.1的相容庫,並重新連接Oracle各元件。

除非萬不得已,不建議使用相容方式,因此省略安裝步驟,可參閱網上有關文檔。

1.4.3.2 Oracle 9i

RedHat 7.3

修改共用記憶體最大尺寸限制:

在系統初始化腳本/etc/rc.d/rc.sysinit中加入:

echo 2147483648 >/proc/sys/kernel/shmmax。

修改信號量參數:

在系統初始化腳本/etc/rc.d/rc.sysinit中加入:

echo 250 32000 100 128 >/proc/sys/kernel/sem。

這4個參數依次為SEMMSL(每個用戶擁有信號量最大數量),SEMMNS(系統信號量最大數量),SEMOPM(每次semop系統調用運算元),SEMMNI(系統信號量集最大數量),事實上只有SEMOP是需要調整的。

重啟電腦。

在連接可執行檔過程中,會發生中斷,打開$ORACLE_HOME/ctx/lib/env_ctx.mk,找到INSO_LINK,在-L$(CTXLIB) -L$(LDLIBFLAG)m後加入-L$(LDLIBFLAG)dl,重試。

SuSE 7.3

與RedHat類似,但SuSE沒有/etc/rc.d/rc.sysinit,筆者選擇/etc/rc.d/rc,將核心參數修改添加到最後exit語句之前。

安裝過程中沒有發生任何問題。

1.5 Solaris

Oracle 8i在Solaris 7,8 Intel Platform上均能順利安裝,未測試Solaris Sparc Platform。

Oracle 9i目前無Solaris Intel Platform上的版本,由於條件所限,未測試在Solaris Sparc Platform上的Oracle 9i。

修改下列核心參數:

核心參數 參考值 解釋

shmmax 實體記憶體/2 共用記憶體段最大尺寸

shmmin 1 共用記憶體段最小尺寸

shmmni 100 系統共用記憶體段標識最大數目

shmseg 10 每個進程所能使用最大共用記憶體段數目

semmni 100 系統信號量標識最大數目

semmsl init.ora.processes+10 每個信號量標識包含的信號量數目

semmns sum(init.ora.processes)*10+max(init.ora.processes)+count(init.ora)*10 系統信號量最大數目

semopm 100 每個semop調用最大運算元目

rlim_fd_max 4096 系統檔控制碼最大數目

rlim_fd_cur 1024 每個進程檔控制碼最大數目

修改/etc/system,並重啟使核心參數生效

例:

set shmsys:shminfo_shmmax=2147483648

set shmsys:shminfo_shmmin=1

set shmsys:shminfo_shmmni=100

set shmsys:shminfo_shmseg=10



set semsys:seminfo_semmni=200

set semsys:seminfo_semmsl=200

set semsys:seminfo_semmns=1000

set semsys:seminfo_semopm=100

set semsys:seminfo_semmap=200

set semsys:seminfo_semmnu=250

set semsys:seminfo_semvmx=32767



set msgsys:msginfo_msgmni=200

set msgsys:msginfo_msgmap=200

set msgsys:msginfo_msgmax=65536

set msgsys:msginfo_msgmnb=655360

set msgsys:msginfo_msgssz=64

set msgsys:msginfo_msgtql=1000

set msgsys:msginfo_msgseg=16384



set rlim_fd_max=4096

set rlim_fd_cur=1024

參見solaris_7_8/system

注意:

一定要先重建好kernel後再安裝,因為oracle安裝時根據kernel動態連接程式,如果先安裝oracle,即使隨後正確調整kernel,也會帶來許多問題,如oracle進程不能拉起,instance創建失敗等。

在kernel參數中,對資料庫運行影響最大的主要是SHMMAX,SEMMNS,SEMMNI,SEMMSL,SHMMAX取記憶體一半即可,SEMMNS理論上應等於SEMMNI*SEMMSL,實際取一個較大值即可。

SEMMNS: 信號量最大個數,有些系統可忽略,因為他與SEMMNI,SEMMSL有關。





2 創建

所有參見內容都在附件01_install_02_create_03_init/下。

以oracle用戶進行操作,設定資料庫實例名為oradb(長度建議不要超過8個字元)。

2.1 Oracle 8 & 8i

2.1.1 工具創建

Oracle 8

運行$ORACLE_HOME/bin/orainst(安裝資料庫時必須選中oracle installer),選擇create database object,安裝介面中選Oracle Enterprise Server(RDBMS)

mount point暫為$ORACLE_BASE,字元集為ZHS16CGB231280或ZHS16GBK,調整system,tools,users,rbs,temp,redolog等尺寸。

創建過程中會提示輸入osdba,osoper的UNIX組,這是向instance表明此組的成員享有角色sysdba或sysoper的許可權,從而用connect / as sysdba替換掉connect internal

Oracle 8i

進入X WINDOW,運行dbassist

2.1.2 手工創建

任何工具都有其局限性,熟練的資料庫管理員可採用手工方法創建資料庫,以增加對系統的靈活控制。

對於手工建庫Oracle 8與Oracle 8i的區別主要是建立的資料字典和存儲過程有些不同,Oracle8i的dbassistant可以生成建庫腳本供以後使用。

取得/8i/initoradb.ora,編輯如db_name,control_file,dump_dest等參數,以符合實際情況。如不需要生成remote_login_passwordfile,可在initoradb.ora中設remote_login_passwordfile=none;如需要,在initoradb.ora中設remote_login_passwordfile=exclusive,運行orapwd file= password=

必須創建新生成檔所要用到的目錄,如在配置檔中指定的bdump,cdump,udump等目錄,以及資料檔案存儲目錄。

將initoradb.ora轉移到$ORACLE_BASE/admin/oradb/pfile/,並連接到$ORACLE_HOME/dbs/initoradb.ora。

ln –s $ORACLE_BASE/admin/oradb/pfile/initoradb.ora $ORACLE_HOME/dbs/initoradb.ora

取得8i/createdb.sh,編輯如pfile,資料檔案目錄等參數,以符合實際情況,並轉移到$ORACLE_BASE/admin/oradb/create/下,執行。

相關系統表:

v$database

v$datafile(file#,ts#,name)

v$tablespace(ts#,name)

v$parameter(SQL>show parameter)

v$sga(SQL>show sga)

2.1.3 MTS(multi-threaded server)

Oracle8使用兩種配置模式:dedicated server(專用模式)和shared server(即multi-threaded server共用模式),缺省使用專用模式。在連接數不很大且保持長期連接的情況下,專用模式為每個連接設立一個專用oracle服務進程,以保持較高的性能和穩定性。而當連接數上升到非常高的數目且不保持長期連接時,資料庫管理開銷增大,並且佔用大量系統資源,給作業系統形成帶來極大的壓力。在這種情況下,共用模式更為有利,它通過緩衝池和預先設定數目的server提供服務,每個連接不再有專用的oracle服務進程,每次SQL操作由分配器(dispatcher)確定oracle服務進程。

multi-thread僅表示分配器展開的多個服務流程,並非作業系統意義上的多線程

配置:

 initoradb.ora

加入

mts_dispatchers = “(address=(protocol=TCP))(dispatchers=10)” #初始分配器數量

mts_max_dispatchers = 15 #最大分配器數量

mts_servers = 50 #初始服務進程數量

mts_max_servers = 80 #最大服務進程數量

mts_service = oradb3 #MTS方式下對外提供的資料庫服務,非service_name

表明instance能夠提供MTS服務,不意味著取消dedicated方式

 listener.ora

應刪除所有SID_LIST,SID_LIST的存在決定LISTENER以dedicated還是shared方式啟動oracle連接。如SID_LIST存在,LISTENER不再接受instance的登記,以dedicated方式啟動oracle連接; 如SID_LIST不存在,LISTENER啟動時不為任何instance服務,由instance來登記MTS service,以shared方式啟動oracle連接

 client

MTS在client端配置頗為怪誕,在tnsnames.ora中的host一定要寫資料庫server的名字,而且必須作全名解析,似乎server端接收到client端請求後會將主機字串返回,應此client端必須能夠解析,否則會報出諸如“database service not exist”的錯誤

tnsnames.ora

dbserver.soar.com =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS=(PROTOCOL=TCP)(HOST= dbserver)(PORT = 1521))

)

(CONNECT_DATA =(SERVICE_NAME = oradb))

)

/etc/hosts

10.0.0.1 dbserver.soar.com dbserver

啟動:先起LISTENER,後起instance

以下步驟均在資料庫open狀態下,由system用戶完成

2.1.4 調整臨時表空間

alter tablespace temp temporary; #Oracle8的orainst沒有將temp的缺省值permanent改為temporary,這樣用戶在temp上暫存的資料均為永久物件,很快將temp空間耗完。Oracle8i已修正。

SQL>alter tablespace temp default storage (initial 128k next 128k maxextents 5000 pctincrease 0);

SQL查詢操作如group by,order by,distinct,join等需要在臨時段上展開資料,須充分考慮臨時段的大小。

如果實例啟動參數指定hash_join_enabled=true(缺省為true),當oracle選擇以hash join方式進行表與表的聯接,oracle根據查詢操作的實際情況計算出hash_multiblock_io_count,此參數從屬於session,平時顯示為0,即hash join一次I/O讀寫需要的連續資料空間。這樣當此參數大於臨時段的next擴展塊時,hash join操作會中斷。如果預知聯接表的規模比較巨大,可使用alter tablespace temp default storage(next …)將next值設為較大值,待全部操作完成後,再恢復正常。

2.1.5 調整回滾表空間

先將建庫工具缺省設定的若干個回滾段刪除

SQL>alter rollback segment r01 offline;

SQL>drop rollback segment r01;

根據實際需要創建回滾段(如r01-r10),供聯機處理和批次處理使用

SQL>create rollback segment r01 storage(initial 128k next 128k maxextents 5000 optimal 5M) tablespace rbs;

SQL>alter rollback segment r01 online;

注意修改$ORACLE_HOME/dbs/initoradb.ora中的啟動回滾段段名

另創建一個尺寸無限制的回滾段(r99),供特殊用途

SQL>create rollback segment r99 storage(initial 128k next 128k maxextents 5000) tablespace rbs;

如果在創建回滾段時使用create public rollback segment,則不需要在$ORACLE_HOME/dbs/initoradb.ora中用rollback_segment=(…)選項啟動,推薦使用public方式

相關系統表:

SQL>select segment_name, initial_extent, next_extent, max_extents, extents,bytes from dba_segments where segment_type=’ROLLBACK’; #回滾段佔用空間狀況

SQL>select segment_name, status from dba_rollback_segs; #回滾段狀態

2.1.6 調整日誌

建立日誌組

SQL>alter database add logfile group x(‘log1a’,’log1b’) size 10M;

增加日誌組成員

SQL>alter database add logfile member ‘log1c’ to group x;

刪除日誌

資料庫實例至少需要2個日誌組,只有狀態為inactive的日誌組才能被刪除,而當前日誌組狀態為current,上一個切換的日誌組狀態為active,這就意味著至少存在3個日誌組才能刪除其中的一個,如果要更新全部日誌組,只能刪除一個,再創建一個,直至全部被更新。

SQL>alter database drop logfile group x;

如果要刪除的日誌組是當前日誌組,必須先將其切換至狀態為inactive,再刪除。

SQL>alter system switch logfile;

刪除日誌組成員

SQL>alter database drop logfile member ‘log1c’;

相關系統表

v$log #日誌組狀態、佔用空間、順序號等

v$logfile #日誌組檔

2.1.7 調整用戶表空間

創建表空間

假定表資料在ts_data,索引在ts_index

SQL>create tablespace ts_data default storage(initial 10M next 10M maxextents 5000 pctincrease 0) datafile ‘path/data_01.dbf’ size 500M;

SQL>create tablespace ts_index default storage(initial 5M next 5M maxextents 5000 pctincrease 0) datafile ‘path/index_01.dbf’ size 500M;

參考命令:刪除表空間

SQL>drop tablespace data including contents; #刪除表空間及其包含的所有資料物件

相關系統表:

user(dba)_tablespaces

增加表空間尺寸

假定表空間ts_data由path/data_01.dbf和path/data_02.dbf(500M)組成

增加一個資料檔案:

SQL>alter tablespace ts_data add datafile ‘path/data_03.dbf’ size 500M;

擴大原有檔大小:

SQL>alter database datafile ‘path/data_01.dbf’ resize 1000M;

移動表空間資料檔案

假如要求為:將path1下data_01.dbf移至path2下,並把檔案名改為data01.dbf

實例處於關閉狀態

sqlplus “/ as sysdba”

SQL>startup mount

回到shell環境下

$ mv path1/data_01.dbf path2/data01.dbf

$ mv path1/data_02.dbf path2/data02.dbf

再到sqlplus環境中

SQL>alter database rename file ‘path1/data_01.dbf’ to ‘path2/data01.dbf’;



SQL>alter tablespace tbsdata rename datafile ‘path/data_01.dbf’ to ‘path2/data01.dbf’;

SQL>alter database open;

查看剩餘空間

SQL>select tablespace_name,sum(bytes),max(bytes) from dba_free_space group by tablespace_name;

注意:空閒資料塊總和sum(bytes)夠用並不意味每個空閒塊都滿足分配需要,所以當表空間不夠分配擴展塊的時候,還要查看最大空閒資料塊max(bytes)的大小。

合併空閒塊

如果表空間上的資料物件經常發生類似drop-create的變動,加之未採用統一的擴展塊尺寸,使那些採用較大擴展塊的資料物件不能利用較小的空間碎片,造成空間浪費。可通過將較小的空閒塊合併成較大的空閒塊的方法,減少空間浪費。

SQL>alter tablespace tbsdata coalesce;

2.1.8 創建用戶

SQL>create user dbuser identified by oracle default tablespace data temporary tablespace temp quota unlimited on data quota 0 on system quota 0 on tools quota 0 on users;

SQL>grant connect to dbuser;

SQL>grant create procedure to dbuser; #這些許可權足夠用於開發及生產環境

SQL>grant select on dba_pending_transactions to dbuser; #二階段提交過程中類似Tuxedo的軟體需要檢索掛起交易的狀態,所以必須得到對此視圖的select許可權,以sys用戶身份賦予

修改用戶可使用alter user dbuser ...

參考命令:

drop user dbuser cascade; #刪除用戶及其所有的資料物件

revoke connect from dbuser; #取消用戶角色許可權

相關系統表:

user(dba)_users

user(dba)_role_privs 角色許可權

user(dba)_sys_privs 系統許可權

user(dba)_tab_privs 對其他用戶表操作的許可權

user_ts_quotas 表空間限額

2.1.9 創建資料物件

相關系統表:

user_catalog(cat)

user_objects(obj)

表和索引建立在表空間上,如果不指定表空間,使用本用戶的缺省表空間(default tablespace);如果不指定本物件的存儲參數,使用建於其上的表空間的缺省存儲參數(default storage)。

表(table)

建表腳本通常是以下形式:

create table emp (no number(12), name char(20), …,constraint emp_x00 primary key(no)) storage(initial 100M next 100M pctincrease 0 maxextents 5000) pctused 70 pctfree 10 tablespace tbs_data enable primary key using index tablespace tbs_index;

然而從簡化資料物件配置、減少表空間碎片的角度考慮,不推薦為每張表單獨指定storage選項,存儲參數使用建於其上的表空間的缺省存儲參數。不同表對擴展塊大小的要求,可以通過分析歸類,建立相應具有不同缺省存儲參數的表空間的方法解決。這樣資料庫設計就能變得簡潔明瞭。

命令簡化為:

create table emp (no number(12), name char(20), …, constraint emp_x00 primary key(no))pctused 70 pctfree 10 tablespace tbs_data enable primary key using index tablespace tbs_index;

primary key關鍵字建立同名的primary key constraint和unique index,表的每個域都有自身的constraint。

相關系統表:

user_tables(tabs),dba_tables #表屬性

user_tab_columns(cols),dba_tab_columns #表各列屬性

索引(index)

create index emp_x01 on emp(name) storage(initial 10M next 10M pctincrease 0 maxextents 5000) pctfree 10 tablespace tbs_index;

可參照表對storage的處理方式。

create index emp_x01 on emp(name) pctfree 10 tablespace tbs_index;

相關系統表:

user_indexes(ind),dba_indexes #索引屬性

user_ind_columns,dba_ind_columns #索引各列屬性,以index_position為順序

序列(sequence)

create sequence emp_seq increment by 1 start with 1 nomaxvalue nocycle;

相關系統表:

user(dba)_sequences(seq) 序列屬性

視圖(view)

create emp_depart_view as select emp.name,emp_duty.name from emp,emp_duty where emp.duty=emp_duty.duty;

相關系統表:

user(dba)_views 視圖屬性

Oracle將view,sequence,用戶參數等定義均存放於系統表空間,而用戶創建的表空間僅存放table,index實體,因此可以大膽刪除用戶表空間,再用備份重新恢復,不必擔心view,sequence等會被一併刪去。

2.1.10 創建唯讀用戶

假定資料庫用戶dbbrsr需要對dbuser的表emp擁有select權力

connect dbuser

grant select on emp to dbbrsr

connect dbbrsr

create synonym emp for dbuser.emp;

這樣,dbbrsr就能象使用自己的表一樣對dbuser的表執行select操作

2.1.11 啟動及關閉資料庫實例

oracle用戶,dbstart和dbshut啟動及關閉/var/opt/oracle/oratab或/etc/oratab中設定的資料庫實例,dbstart採用normal方式,dbshut採用immediate方式。

或者使用手工方式

sqlplus “/ as sysdba”

啟動

normal

SQL>startup

mount

SQL>startup mount; #啟動實例進程,載入資料庫檔,允許DBA許可權的某些操作,但禁止對資料庫檔的一般性操作

SQL>完成某些操作

SQL>alter database open;

nomount

SQL>startup nomount; #啟動實例進程,但不允許訪問資料庫,常用於創建資料庫、介質恢復或創建controlfile

SQL>完成某些操作

SQL>alter database open;

關閉

normal

SQL>shutdown或SQL>shutdown transactional; #等待每個連接交易完成後,切斷連接,再關閉資料庫

immediate

SQL>shutdown immediate; #立刻中止每個連接,交易回滾

abort

SQL>shutdown abort; #立刻關閉資料庫,不保證交易完整性,在下一次啟動打開資料庫檔時會進行介質恢復

2.1.12 網路配置

假定某一台機器為client,ORACLE_SID為oraclient,資料庫用戶為dbclient;另一台機器為server,ORACLE_SID為oraserver,資料庫用戶為dbserver在server上$ORACLE_HOME/dbs/initoraserver.ora中有以下設定:

db_name = oraserver

instance_name = oraserver

Oracle 8i

service_names=oraserver

2.1.12.1 TNS

Client端配置

修改$ORACLE_HOME/network/admin/tnsnames.ora,增加一條PROTOCOL=TCP的記錄。

Oracle8

db_server

(DESCRIPTION =

(ADDRESS = (PROTOCOL= TCP)(Host= server)(Port= 1521))

(CONNECT_DATA = (SID = oraserver))

)

Oracle8i

db_server

(DESCRIPTION =

(ADDRESS = (PROTOCOL= TCP)(HOST= server)(PORT= 1521))

(CONNECT_DATA = (SERVICE_NAME=oraserver

)

HOST可在/etc/hosts或DNS中配置,或直接寫上IP地址

sqlplus dbserver/passwd@db_server

Server端配置

修改$ORACLE_HOME/network/admin/listener.ora

在LISTENER中增加ADDRESS的記錄

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

(ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = 1521))

)

)

)

在SID_LIST_LISTENER中增加SID_DESC記錄

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /opt/oracle/app/oracle/product/8.1.5)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = oraserver)

(ORACLE_HOME = /opt/oracle/app/oracle/product/8.1.7)

(SID_NAME = oraserver)

)

)

HOST可在/etc/hosts或DNS中配置,或直接寫上IP地址

注意:LISTENER和SID_LIST_LISTENER是成對出現的,可配置多個監聽服務進程和相應的SID_LIST,如LISTENER_1和SID_LIST_LISTENER_1

2.1.12.2 DB Link

如果client的instance需要在訪問本地資料物件同時訪問server中的資料物件,可在instance中創建對server的資料庫連接,實現間接訪問

在tnsnames.ora中建立“db_server”配置

sqlplus dbclient/passwd1

SQL>create database link server_link connect to dbserver identified by passwd using ‘db_server’;

使用emp@server_link訪問server上的emp,如同訪問本地instance中的資料物件一樣。為了更方便的使用,可建立synonym

2.2 Oracle 9i

Oracle 9i相較於Oracle 8&8i,在相容Oracle 8&8i的基礎上,回滾和臨時表空間配置發生比較大的變化,導致建庫操作出現一些不同。在資料庫配置檔initoradb.ora中有關於回滾表空間的選項,詳細情況在“資料庫配置”中解釋。而且Oracle 9i簡化了表空間的創建。所以此小節主要描述Oracle 9i相對於Oracle 8i的差異,其他相同的操作可參考Oracle 8i。

2.2.1 手工創建

由於在Oracle 9i中工具dbassist的使用方法與在Oracle 8i中類似,因此工具建庫過程省略,只記錄手工建庫過程

Oracle 9i中的建庫過程已經變得極為簡潔,大致如下:

create database ${ORACLE_SID}

user sys identified by sys

user system identified by system

logfile group 1 (‘${ORACLE_BASE}/oradata/${ORACLE_SID}/redo01.log’) size 10M,

group 2 (‘${ORACLE_BASE}/oradata/${ORACLE_SID}/redo02.log’) size 10M,

group 3 (‘${ORACLE_BASE}/oradata/${ORACLE_SID}/redo03.log’) size 10M

maxlogfiles 5

maxlogmembers 5

maxloghistory 1

maxdatafiles 254

maxinstances 1

archivelog

character set ZHS32GB18030

national character set AL16UTF16

datafile ‘${ORACLE_BASE}/oradata/${ORACLE_SID}/system01.dbf’ size 300M

default temporary tablespace tbstemp tempfile ‘${ORACLE_BASE}/oradata/${ORACLE_SID}/temp01.dbf’ size 500M

undo tablespace tbsundo datafile ‘${ORACLE_BASE}/oradata/${ORACLE_SID}/undo01.dbf’ size 500M;

其特點為使用專用的回滾和臨時表空間,而不象Oracle 8i中的那樣,回滾和臨時表空間與普通表空間沒有差異,這樣既簡化了配置也有利於效能提高。要注意臨時表空間的指定檔關鍵字是tempfile而不是通用的datafile,而且臨時表空間的存儲選項必須為uniform,由Oracle系統決定。同樣回滾表空間也是由Oracle系統決定。不必人工干預。

Oracle 9i在$ORACLE_HOME/dbs下可使用二進位配置檔,缺省為spfile{實例名}.ora,如spfileoradb.ora,支援Oracle系統進程在不重啟的情況下動態調整參數,這對要求不間斷運行的系統是有利的。在建庫階段就可將此配置檔創建起來。

create spfile from pfile= ‘${ORACLE_BASE}/admin/${ORACLE_SID}/init${ORACLE_SID}.ora

完整步驟見/9i/createdb.sh,編輯如pfile,資料檔案目錄等參數,以符合實際情況,並轉移到$ORACLE_BASE/admin/oradb/create/下,執行。

2.2.2 創建用戶表空間

Oracle 9i對於表空間管理一個明顯的變化是改資料字典管理(extent management dictionary)為表空間本地管理(extent management local),還可以根據建立的資料物件對空間的要求自動確定擴展塊的大小(autoallocate),最小為64K,這兩項都是創建表空間的缺省選項。

create tablespace tbsdata datafile ‘…’ [ extent management local ] [ autoallocate ];

而對於指定每個擴展塊大小的創建策略,設立了新選項:統一擴展塊大小(uniform [size xxx[K|M]]),可覆蓋autoallocate選項,如果不加上具體的size xxx[K|M],缺省為1M,這樣就不必考慮Oracle 8i中的如initial,next,pctincrease,maxextents等default storage參數應如何組合,事實上Oracle 8i的這些設置原本就沒有什麼意義。

不能夠同時指定extent management local和default storage,換言之,default storage只能和extent management dictionary一起顯式指定。

如果未指定extent management的類型,Oracle 9i缺省使用local方式,如果又同時使用default storage選項,就有以下的判斷:

如果使用minimun extent,Oracle檢查是否minumum extent=initial=next且pctincrease=0,如是,Oracle使用uniform選項,size=initial;如不是,Oracle忽略指定選項,使用autoallocate。

如果未指定minimum extent,Oracle檢查是否initial=next且pctincrease=0,如是Oracle使用uniform選項,size=initial;如不是Oracle忽略指定選項,使用autoallocate。

為了避免與Oracle 8i的習慣做法混淆,建議只使用Oracle 9i較簡潔的方法。

對於存儲少量靜態資料的表空間來說,如配置資訊等,可簡單地寫為:

create tablespace tbsdata datafile ‘…’;

對於必須關心其擴展塊大小的表空間,如大批量的記錄或索引,可簡單地寫為:

create tablespace tbsdata datafile ‘…’ uniform size 10M;







3 初始化檔配置

所有參見內容都在附件01_install_02_create_03_init/下。

描述initoradb.ora中各選項。

3.1 Oracle 8 & 8i

具體參見8i/initoradb.ora。

db_block_size

資料庫基本資料塊尺寸,位元組為單位。

當涉及到大量資料交換時,例如export/import操作時,此參數對資料庫性能有非常大的影響,設定一個較大的值,有利於提高資料吞吐量,但由於db block是檔和記憶體之間交換的基本單位,過大的值反而會交換不需要的記錄,增加額外的I/O。

一般取8k就已能獲得較滿意效果。

db_block_buffers

資料緩衝區,db_block_size為單位,不超過1/4記憶體

計算查詢緩衝命中率:

SELECT name, value FROM v$sysstat WHERE name IN (‘db block gets’, ‘consistent gets’, ‘physical reads’);

Hit Ratio = 1 – ( physical reads / (db block gets + consistent gets) )

SELECT name, phyrds, phywrts FROM v$datafile df, v$filestat fs WHERE df.file# = fs.file#

db block gets:在記憶體buffer中的命中次數

consistent gets:一致性命中次數,指在記憶體buffer中未命中,但從回滾段或資料檔案中獲得命中

physical reads:在資料檔案中的讀次數

注意:一般HitRatio達到90%以上就可以認為已達到優化,這個數值應在系統運行穩定後進行統計。

shared_pool_size

資料字典和SQL操作緩衝區,位元組為單位,不超過1/4記憶體

select (sum(pins – reloads)) / sum(pins) “Lib Cache” from v$librarycache;

select (sum(gets – getmisses – usage – fixed)) / sum(gets) “Row Cache” from v$rowcache;

select * from v$sgastat where name = ‘free memory’

注意:Cache命中率達到95%以上就可以認為已達到優化,這個數值應在系統運行穩定後進行統計

log_checkpoint_interval

日誌提交點數據量間隔

以作業系統block(通常512-byte)為單位,當日誌累計至此參數,會使sga中dirty buffer被同步至資料檔案,日誌切換時也會引起此操作,如設為0,則相當於無限大,此參數失去作用,日誌提交僅依靠日誌檔的切換。

應選擇適當大小的日誌檔,同時使log_checkpoint_interval略大於日誌檔或設為0。原則上應該避免過於頻繁的checkpoint操作,控制在30分鐘以上為好。

推薦此參數設為0。

log_buffer

線上日誌緩衝,位元組為單位,512K或128K*CPU數量,取較大值

processes和sessions

dedicated server模式下每一個連接都有一個Oracle服務進程(process)為之服務,這個連接本身也就是一個會話(session)。

shared server模式下所有連接共用一個Oracle服務進程池,這樣process和session就不再是一一對應,sessions要大於processes。

sort_area_size和sort_area_retained_size

排序緩衝區,位元組為單位。

當排序記錄被全部取走後,緩衝區縮減到sort_area_retained_size,為減少緩衝區縮放的開銷,可使sort_area_size和sort_area_retained_size取相同值。

hash_area_size

hash join緩衝區,位元組為單位,缺省為2*sort_area_size。

db_file_multiblock_read_count

每次讀取的db block數,對大規模查詢性能有提高,特別是表掃描效率。線上系統應避免這種類型的查詢。

db_writer_processes

同步資料進程數,與checkpoint的頻率和資料量有關。

db_block_lru_latches

LRU鎖集,一般設為CPU數目。RedHat Linux 6.x下的Oracle 8.1.6設此參數會導致系統掛起,疑對smp支持有問題

log_archive_start

系統啟動時是否同時啟動歸檔進程(archive)。

log_archive_dest_1

歸檔日誌目錄,最後的標號表明歸檔線程編號,一般只用1。

log_archive_format

歸檔日誌名稱,%t指歸檔線程編號, %s指歸檔日誌序列號

rollback_segments

如果創建回滾段(rollback segment)時不使用public選項,那就是使用私有的回滾段,這樣就必須在系統啟動時啟動。

推薦使用public rollback segment的做法,這個選項可以廢棄。

background_dump_dest

Oracle系統進程記錄log和trc目錄。

alert_{實例名}.log以文本方式記錄系統啟動、關閉、出錯、存儲變化、日誌切換等log資訊。

系統進程以各自名稱和進程號記錄錯誤資訊,檔以trc為尾碼,文本格式。

core_dump_dest

Oracle服務進程的core dump目錄。

user_dump_dest

Oracle服務進程以各自名稱和進程號記錄錯誤資訊,檔以trc為尾碼,文本格式。

3.2 Oracle 9i

參見9i/initoradb.ora。

pga_aggregate_target

以K、M、G為單位

sort, group-by, hash-join, bitmap merge, bitmap create等對記憶體有一定需求的SQL操作,都由此選項統一動態分配記憶體區域大小,因此Oracle 8i中如sort_area_size,sort_area_retained_size,hash_area_size,bitmap_merge_area_size等選項可以廢棄。

db_cache_size

資料緩衝區,以K、M、G為單位,自動對齊到粒度單位。

取代Oracle 8i的db_block_buffers選項。

undo_management

回滾空間管理模式,缺省為manual,使用回滾段(rollback segment),如設為auto,則使用Oracle 9i的回滾表空間。此選項決定了以下關於undo的其他選項。

undo_retention

已提交資料在回滾表空間中保留時間,以秒為單位,缺省900。

當某些較長時間的查詢需要通過回滾資料重建老資料塊的時候,此選項可使新事務盡可能使用空閒的回滾表空間,這樣就減少了查詢過程因snapshot too old而失敗的幾率。

然而當空閒回滾表空間不足以應付新事務時,系統仍然會重用此選項保留的空間,因此不能保證長查詢一定能成功執行完畢。

undo_tablespace

指定系統啟動時的回滾表空間。





4 工具

所有參見內容都在附件04_tool/下。

在《優化》一節中討論以下工具使用的效率。

4.1 sqlldr

參見sqlldr/。

用於將格式化的文本資料上載到表中去

以表emp為例

首先編寫一個控制命令的腳本檔,通常以ctl結尾,內容如下:

emp.ctl

load data

append

into table emp

fields terminated by ‘|’

(

no float external,

name char(20),

age integer external,

duty char(1),

salary float external,

upd_ts date(14) ‘YYYYMMDDHH24MISS’

)

括弧裏對資料檔案裏每個資料欄進行解釋,以此在上載時與目標表進行比對。

除了append外,還有insert、replace、truncate等方式,與append大同小異,不作更多的解釋。

再將上載資料組織成資料檔案,通常以dat結尾,內容如下:

emp.dat

100000000001|Tom|000020|1|000000005000|20020101000000

100000000002|Jerry|000025|2|000000008000|20020101235959

分隔符號要與ctl檔中fields terminated by指定的一致,這個例子中為“|”

ctl和dat檔就緒後可以執行上載,命令為:

sqlldr dbuser/oracle control=emp.ctl data=emp.dat

也可以將dat檔合併在ctl檔中,ctl檔改寫為:

emp2.ctl

load data

infile *

append

into table emp

fields terminated by ‘|’

(

no float external,

name char(20),

age integer external,

duty char(1),

salary float external,

upd_ts date(14) ‘YYYYMMDDHH24MISS’

)

begindata

100000000003|Mulder|000020|1|000000005000|20020101000000

100000000004|Scully|000025|2|000000008000|20020101235959

控制檔中infile選項跟sqlldr命令行中data選項含義相同,如使用infile *則表明資料在本控制檔以begin data開頭的區域內。

這樣命令變成:

sqlldr dbuser/oracle control=emp2.ctl

conventional path

通過常規通道方式上載。

rows:每次提交的記錄數

bindsize:每次提交記錄的緩衝區

readsize:與bindsize成對使用,其中較小者會自動調整到較大者

sqlldr先計算單條記錄長度,乘以rows,如小於bindsize,不會試圖擴張rows以填充bindsize;如超出,則以bindsize為准。

命令為:

sqlldr dbuser/oracle control=emp.ctl log=emp.log rows=10000 bindsize=8192000

direct path

通過直通方式上載,不進行SQL解析。

命令為:

sqlldr dbuser/oracle control=emp.ctl log=emp.log direct=true

4.2 exp

參見dmp/exp_demo.sh。

將資料庫內的各物件以二進位方式下載成dmp檔,方便資料遷移。

buffer:下載資料緩衝區,以位元組為單位,缺省依賴作業系統

consistent:下載期間所涉及的資料保持read only,缺省為n

direct:使用直通方式 ,缺省為n

feeback:顯示處理記錄條數,缺省為0,即不顯示

file:輸出檔,缺省為expdat.dmp

filesize:輸出檔大小,缺省為作業系統最大值

indexes:是否下載索引,缺省為n,這是指索引的定義而非資料,exp不下載索引資料

log:log檔,缺省為無,在標準輸出顯示

owner:指明下載的用戶名

query:選擇記錄的一個子集

rows:是否下載表記錄

tables:輸出的表名列表

下載整個實例

exp dbuser/oracle file=oradb.dmp log=oradb.log full=y consistent=y direct=y

user應具有dba許可權

下載某個用戶所有物件

exp dbuser/oracle file=dbuser.dmp log=dbuser.log owner=dbuser buffer=4096000 feedback=10000

下載一張或幾張表

exp dbuser/oracle file=dbuser.dmp log=dbuser.log tables=table1,table2 buffer=4096000 feedback=10000

下載某張表的部分資料

exp dbuser/oracle file=dbuser.dmp log=dbuser.log tables=table1 buffer=4096000 feedback=10000 query=\”where col1=\’…\’ and col2 \<…\”

不可用於嵌套表

以多個固定大小檔方式下載某張表

exp dbuser/oracle file=1.dmp,2.dmp,3.dmp,… filesize=1000m tables=emp buffer=4096000 feedback=10000

這種做法通常用在:表資料量較大,單個dump檔可能會超出檔系統的限制

直通路徑方式

direct=y,取代buffer選項,query選項不可用

有利於提高下載速度

consistent選項

自export啟動後,consistent=y凍結來自其他會話的對export操作的資料物件的更新,這樣可以保證dump結果的一致性。但這個過程不能太長,以免回滾段和聯機日誌消耗完

4.3 imp

參見dmp/imp_demo.sh。

將exp下載的dmp檔上載到資料庫內。

buffer:上載資料緩衝區,以位元組為單位,缺省依賴作業系統

commit:上載資料緩衝區中的記錄上載後是否執行提交

feeback:顯示處理記錄條數,缺省為0,即不顯示

file:輸入檔,缺省為expdat.dmp

filesize:輸入檔大小,缺省為作業系統最大值

fromuser:指明來源用戶方

ignore:是否忽略物件創建錯誤,缺省為n,在上載前物件已被建立往往是一個正常現象,所以此選項建議設為y

indexes:是否上載索引,缺省為n,這是指索引的定義而非資料,如果上載時索引已建立,此選項即使為n也無效,imp自動更新索引資料

log:log檔,缺省為無,在標準輸出顯示

rows:是否上載表記錄

tables:輸入的表名列表

touser:指明目的用戶方

上載整個實例

imp dbuser/oracle file=oradb.dmp log=oradb.log full=y buffer=4096000 commit=y ignore=y feedback=10000

上載某個用戶所有物件

imp dbuser/oracle file=dbuser.dmp log=dbuser.log fromuser=dbuser touser=dbuser2 buffer=2048000 commit=y ignore=y feedback=10000

上載一張或幾張表

imp dbuser2/oracle file=user.dmp log=user.log tables=table1,table2 fromuser=dbuser touser=dbuser2 buffer=2048000 commit=y ignore=y feedback=10000

以多個固定大小檔方式上載某張表

imp dbuser/oracle file=\(1.dmp,2.dmp,3.dmp,…\) filesize=1000m tables=emp fromuser=dbuser touser=dbuser2 buffer=4096000 commit=y ignore=y feedback=10000

4.4 sqlplus

參見sqlplus/download.sh。

僅列出常用的選項,對複雜的應用不作深究

4.4.1 命令行參數

/ as {sysdba|sysopr}:使用作業系統用戶驗證,以osdba或osopr一員的身份登錄,如驗證通過,被賦予sysdba或sysopr的許可權

使用格式:sqlplus “/ as sysdba”

/nolog:不執行connect操作,直接進入sqlplus操作介面

-s:silent模式,不顯示sqlplus啟動資訊和提示符

<:接受sql腳本從標準輸入重定向

<<:立即文檔

4.4.2 提示符命令

accept variable [number|char|date] [format format] [default default] [prompt text] [hide]:接受輸入變數

例子:accept pwd char format a8 prompt ‘Password:’ hide

column column [format format] [heading heading]:設定對某個域的顯示格式

如果要同時改變某域的輸出長度和標題,必須使用column命令

見emp的定義,name本為char(20),輸出縮為10位,duty本為 char(1),擴張為6位,以便有足夠的空間顯示中文標題。

SQL>column name format a10 heading ‘姓名’;

SQL>column duty format a6 heading ‘職位’;

SQL>column age format 999999 heading ‘年齡’;

SQL>column upd_ts format a14 heading ‘更新時間’;

SQL>select name,duty,age,upd_ts from emp;

show option:顯示SET的選項

spool [filename|off]:輸出重定向檔

timing [start text|show|stop]:計時器

4.4.3 SET選項

autocommit:自動提交insert、update、delete帶來的記錄改變,缺省為off

colsep:域輸出分隔符號

define:識別命令中的變數首碼符,缺省為on,也就是’&’,碰到變數首碼符,後面的字串作為變數處理

如果待更新內容包含’&’(在URL中很常見),而define非設為off,sqlplus會把’&’後面緊跟的字串當成變數,提示輸入,這裏必須重新輸入’&’和那個字串,才能實現正常更新。將define設為off,就不再進行變數判斷。

SQL>set define off;

SQL>update bbs_forum set url=’http://www.xxx.com/bbs/show.php&forum_id=1’ where forum_id=1;

echo:顯示start啟動的腳本中的每個sql命令,缺省為on

feedback:回顯本次sql命令處理的記錄條數,缺省為on

heading:輸出域標題,缺省為on

linesize:輸出一行字元個數,缺省為80

如果一行輸出超過linesize,會回車到第二行,這樣格式就會混亂。

markup html:html格式輸出,缺省為off

通常需要與spool配合,否則html輸出就沒有意義。

numwidth:輸出number類型域長度,缺省為10

長number類型的域常常因為輸出長度的問題,引起誤會。

pagesize:輸出每頁行數,缺省為24

為了避免分頁,可設定為0。

termout:顯示腳本中的命令的執行結果,缺省為on

timing:顯示每條sql命令的耗時,缺省為off

trimout:去除標準輸出每行的拖尾空格,缺省為off

trimspool:去除重定向(spool)輸出每行的拖尾空格,缺省為off

4.4.4 例子

以文本形式下載表資料

oracle缺乏將表中資料輸出至文字檔案的工具,因此只能利用sqlplus和unix工具做變通的處理

sqlplus –s dbuser/oracle </dev/null

set colsep |;

set echo off;

set feedback off;

set heading off;

set pagesize 0;

set linesize 1000;

set numwidth 12;

set termout off;

set trimout on;

set trimspool on;

spool tmp.txt;

select * from emp;

spool off;

exit

EOF

tr –d ‘ ‘ < tmp.txt >emp.txt 刪除空格,可選

注意:一定要用spool,如果在命令行中直接用>tmp.txt可能會造成資料缺失,至少在Unixware7上如此

假定某域是char(n),如中間出現回車\n,則下載出的這條記錄的格式將會錯亂,不宜採用此方法







5 備份及恢復

所有參見內容都在附件05_backup/下。

5.1 export與import方式

參見dmp/backup.sh。

見《工具》對exp和imp的描述

資料庫中的物件是比較多的,但除了表以外佔用的空間不大,所以當表中記錄數量達到一定規模後,以用戶的方式一下子把資料exp出來就顯得不夠靈活。考慮以下的策略,先exp出除表資料以外的所有物件,再分別exp出每張表的資料。

exp dbuser所有的資料物件

exp dbuser/oracle file=dbuser.dmp log=user.log owner=user buffer=2048000 rows=n

exp單張表的數據

sqlplus -s dbuser/oracle </dev/null

set colsep |;

set echo off;

set feedback off;

set heading off;

set pagesize 0;

set linesize 1000;

set termout off;

set trimout on;

set trimspool on;

spool tables.txt;

select table_name from user_tables;

spool off;

exit;

EOF

for table in $(cat tables.txt)

do

exp dbuser/oracle file=${table}_$(date ‘+%Y%m%d’).dmp tables=$table direct=y

done

5.2 冷備份

shutdown資料庫,將所有和本實例有關的檔,包括datafile,controlfile,redolog,archived redolog,initora.ora等全部備份。恢復時只要將這些檔放回從前的目錄,startup資料庫即可。

5.3 聯機全備份+日誌備份

5.3.1 設置

如果資料庫實例原來沒有使用歸檔日誌功能,則必須進行配置修改

initoradb.ora:

log_archive_start = true #實例啟動時同時啟動歸檔進程。

log_archive_dest_1= “location=/appl/oracle/oradata/orafe/arch/arch” #歸檔日誌目錄。

打開歸檔日誌功能:

shutdown資料庫

sqlplus “/ as sysdba”

SQL>startup mount

SQL>alter database archivelog;

SQL>alter database open;

可用archive log list查看狀態,去除歸檔日誌功能的命令為alter database noarchivelog。

5.3.2 步驟

參見online/full.sh、daily.sh,以osdba組的用戶執行

聯機全備份:

資料庫處於open狀態,依次對各個表空間備份

sqlplus “/ as sysdba”

SQL>alter tablespace system begin backup;

複製此tablespace各個datafile

SQL>alter tablespace system end backup;

注意:據推測,begin backup是對tablespace凍結寫入,end backup是解除凍結,因此複製datafile的過程不宜過長

備份controlfile

SQL>alter database backup controlfile to ‘…….’;

日誌備份:

sqlplus “/ as sysdba”

SQL>alter system archive log stop;

移去日誌目錄下的所有archived redolog

SQL>alter system archive log start;

5.3.3 恢復

資料庫處於shutdown狀態

最差情況:磁片全部損壞,僅保存上次聯機全備份和每天日誌備份

解決硬體故障,配置系統軟體及環境

oracle用戶,將全備份和日誌備份轉移至相應目錄,根據initoradb.ora中controlfile的配置,將備份控制檔複製到回應目錄下

sqlplus “/ as sysdba”

SQL>startup mount

SQL>recover database until cancel using backup controlfile;

逐個確認待恢復的archived redolog,待最後一個完成後,鍵入cancel,使恢復結束

SQL>alter database open resetlogs;

注意:由於日誌已經重置,所以應儘快做一次聯機全備份

丟失某資料檔案

只要將此檔從上次聯機全備份中複製至其目錄,並將自上次聯機全備份以來所有日誌備份移至歸檔目錄

sqlplus “/ as sysdba”

SQL>startup mount

SQL>alter database recover datafile ‘path/file’;或者簡單些recover database;

SQL>alter database open;

如果此檔損壞或丟失,又無備份,則只能將此檔脫機,將資料exp出來,重建表空間,再imp進去

sqlplus “/ as sysdba”

SQL>connect internal

SQL>startup mount

SQL>alter database datafile ‘path/file’ offline;

SQL>alter database open;

5.4 注意要點

無論有多少把握,恢復前先做冷備份,此為第一原則

不這樣做,便是無路可退,一旦失誤,後果不必多說。

rollback段損壞

這是非常嚴重的問題,可在initora.ora中寫入_corrupted_rollback_segments=(rxx),啟動時避開損壞的rollback段,這只是權宜之計。如資料庫處於archivelog,應從上一次全備份起利用備份的日誌進行恢復;如資料庫處於noarchivelog,應儘快將全部資料export出來,重建資料庫,再import進去。所有操作之前,應做冷備份。

資料庫異常中止處理

通過手工shutdown abort操作中止資料庫,不會產生大的問題,通常直接startup無需使用介質恢復命令

如果由於機器崩潰引起的中止,則情況嚴重得多,有可能要使用到上面提到的恢復方法,不過這種現象並不多見。一般需要顯式使用介質恢復命令,如下:

sqlplus “/ as s
arrow
arrow
    全站熱搜

    狼翔月影 發表在 痞客邦 留言(0) 人氣()