Sybase數(shù)據(jù)庫維護(hù)篇--高級

1、SYBASE培訓(xùn)教材0培訓(xùn)計劃 培訓(xùn)內(nèi)容 培訓(xùn)目標(biāo) 培訓(xùn)對象SYBASE培訓(xùn)教材1培訓(xùn)內(nèi)容SybaseSybase數(shù)據(jù)庫簡介數(shù)據(jù)庫簡介SybaseSybase產(chǎn)品（產(chǎn)品（ASAASA，ASEASE，IQIQ）ASEASE它是基于客戶它是基于客戶/ /服務(wù)器體系結(jié)構(gòu)的數(shù)據(jù)庫服務(wù)器體系結(jié)構(gòu)的數(shù)據(jù)庫 OLTPOLTP 1:Oracle Server是一個真正的多進(jìn)程數(shù)據(jù)庫引擎，Sybase數(shù)據(jù)庫管理系統(tǒng)采用的是單進(jìn)程多線程的引擎結(jié)構(gòu)。 2:Sybase的數(shù)據(jù)庫引擎還代替操作系統(tǒng)來管理一部分硬件資源，如端口、內(nèi)存、硬盤，繞過了操作系統(tǒng)這一環(huán)節(jié)，提高了性能。SYBASE培訓(xùn)教材2數(shù)據(jù)庫(raid0,1,

2、5)SYBASE培訓(xùn)教材3數(shù)據(jù)庫設(shè)備分配結(jié)構(gòu)SYBASE培訓(xùn)教材4數(shù)據(jù)庫page分配結(jié)構(gòu)SYBASE培訓(xùn)教材5Sybase的多庫結(jié)構(gòu)安裝Sybase數(shù)據(jù)庫時自動生成的下列系統(tǒng)數(shù)據(jù)庫：主數(shù)據(jù)庫master: 數(shù)據(jù)庫全面控制和管理用戶數(shù)據(jù)庫以及SQLSERVER上的一切操作。模型數(shù)據(jù)庫model: 為新建的用戶數(shù)據(jù)庫提供一個原形系統(tǒng)過程數(shù)據(jù)庫sybsystemprocs: SYBASE的系統(tǒng)存儲過程被儲存在這個數(shù)據(jù)庫中臨時數(shù)據(jù)庫tempdb: 作為臨時表和其他的臨時工作空間提供一個存儲區(qū)域。TEMPDB的空間為全體數(shù)據(jù)庫用戶共享也可選擇下列數(shù)據(jù)庫：安全審核數(shù)據(jù)庫sybsecurity: 包含SQL

3、SERVER的審計系統(tǒng)示例數(shù)據(jù)庫pubs2: 這是一個樣本庫 命令語法數(shù)據(jù)庫sybsyntax: 為用戶提供聯(lián)機(jī)幫助用戶數(shù)據(jù)庫 用戶數(shù)據(jù)庫是指用Create database 命令創(chuàng)建的數(shù)據(jù)庫。 使用”use 數(shù)據(jù)庫名”來切換不同的數(shù)據(jù)庫 執(zhí)行sp_changedbowner 改變數(shù)據(jù)庫的屬主關(guān)系 建議事務(wù)日志放在單獨(dú)的設(shè)備上(log)SYBASE培訓(xùn)教材6master庫它是管理和控制用戶數(shù)據(jù)庫以及維護(hù)服務(wù)器正常運(yùn)行的核心數(shù)據(jù)庫，它包含許多系統(tǒng)表和系統(tǒng)過程，從總體上控制用戶數(shù)據(jù)庫和SQL Server的操作，構(gòu)成了SYBASE系統(tǒng)的數(shù)據(jù)字典，它保存了大量的系統(tǒng)信息，如服務(wù)器配置、用戶、設(shè)備等。

4、在master數(shù)據(jù)庫中不允許普通用戶在其中創(chuàng)建數(shù)據(jù)庫對象，否則會使得master數(shù)據(jù)庫的事務(wù)日志很快變滿。如果事務(wù)日志用盡，就無法使用dump transaction命令釋放master數(shù)據(jù)庫中的空間。master數(shù)據(jù)庫主要記錄信息為： 登錄帳號（SQL服務(wù)器用戶名）；syslogins,sysremolelogins 正在執(zhí)行的過程；sysprocesses 可修改的環(huán)境變量；sysconfigures 系統(tǒng)錯誤信息；sysmessages SQL服務(wù)器上每個數(shù)據(jù)庫的信息；sysdatabases 每個數(shù)據(jù)庫占用的存儲空間；sysusages 系統(tǒng)上安裝的設(shè)備及磁帶信息；sysdevices

5、 活動鎖信息；syslocksSYBASE培訓(xùn)教材7TEMPDB庫 它是個臨時數(shù)據(jù)庫，為服務(wù)器運(yùn)行與處理提供一個共享的存儲區(qū)域，如group by和order by的中間結(jié)果就存放在這里。Tempdb的空間為服務(wù)器中所有數(shù)據(jù)庫的所有用戶所共享。 每次重啟SQL Server，服務(wù)器的一個自動進(jìn)程都拷貝model數(shù)據(jù)庫到tempdb數(shù)據(jù)庫，并清除tempdb中原來的內(nèi)容。因此tempdb中的用戶表都是臨時的。臨時表分為兩類：可共享的和不可共享的。不可共享的臨時表在由create table中將符號#置于表名之前創(chuàng)立；可共享的臨時表通過create table中指定表名前綴tempdb.而創(chuàng)立。不

6、可共享的臨時表SQL Server自動為其添加數(shù)字后綴名，且它只存在于當(dāng)前會話中SYBASE培訓(xùn)教材8數(shù)據(jù)庫有關(guān)概念（segment/device)數(shù)據(jù)庫設(shè)備從邏輯上被劃分為數(shù)據(jù)庫段以允許將某一特定的對象放置在指定的段上（創(chuàng)建對象時指定），一數(shù)據(jù)庫設(shè)備可擁有多達(dá)192個段，一段可使用255個邏輯設(shè)備上的存儲空間。當(dāng)用戶創(chuàng)建一個數(shù)據(jù)庫時，SQL SERVER 會自動在該數(shù)據(jù)庫中創(chuàng)建三個段： SYSTEM、LOGSEGMENT 、DEFAULT，這三個段分別用來存儲數(shù)據(jù)庫的系統(tǒng)表、事務(wù)日志和其他數(shù)據(jù)庫對象提高性能：處于不同磁盤設(shè)備上的段可以并行地讀寫；處理大表：利用段，可以將一個大表分段放在獨(dú)立的

7、物理設(shè)備上。所謂數(shù)據(jù)庫設(shè)備，不是指一個可識別的物理設(shè)備，而是指用于存儲數(shù)據(jù)庫和數(shù)據(jù)庫對象的磁盤原始分區(qū)或操作系統(tǒng)文件。初始化數(shù)據(jù)庫設(shè)備使用DISK INIT命令： DISK INITNAME=設(shè)備名，PHYSNAME=物理設(shè)備名，VDEVNO=虛擬設(shè)備號，SIZE=塊數(shù)SYBASE培訓(xùn)教材9數(shù)據(jù)庫有關(guān)概念（segment/device)Sybase segments Table創(chuàng)建于segment上 每個db最多可有32個segments創(chuàng)建段：sp_addsegment seg, db_name, device_name注：創(chuàng)建表需要指定索引和數(shù)據(jù)的存放段名稱Device 和 segment

8、 關(guān)系：單個device上可以創(chuàng)建多個segment，單個segment可以跨越多個deviceTable和segment關(guān)系： 單個segment上創(chuàng)建多個table，單個table可以跨越多個segment（分區(qū)表）。SYBASE培訓(xùn)教材10數(shù)據(jù)庫對象(1) 表(Tables) (2) 視圖(Views) : 視圖是一個虛擬表，其內(nèi)容由查詢定義。同真實(shí)的表一樣，視圖包含一系列帶有名稱的列和行數(shù)據(jù)。但是，視圖并不在數(shù)據(jù)庫中以存儲的數(shù)據(jù)值集形式存在。行和列數(shù)據(jù)來自由定義視圖的查詢所引用的表，并且在引用視圖時動態(tài)生成。對其中所引用的基礎(chǔ)表來說，視圖的作用類似于篩選。 作用: 安全 ; 可使復(fù)雜的

9、查詢易于理解和使用 SYBASE培訓(xùn)教材11Index與性能索引(index): 索引就是加快檢索表中數(shù)據(jù)的方法。在數(shù)據(jù)庫中，索引也允許數(shù)據(jù)庫程序迅速地找到表中的數(shù)據(jù)，而不必Table scan 。 1. 索引可以加快數(shù)據(jù)庫的檢索速度2. 索引降低了數(shù)據(jù)庫插入、修改、刪除等維護(hù)任務(wù)的速度3. 索引創(chuàng)建在表上，不能創(chuàng)建在視圖上 4. 索引既可以直接創(chuàng)建(create index) ，也可以間接創(chuàng)建 ( 主鍵約束或者唯一性鍵約束時)Sybase支持三種不同類型的索引，一是復(fù)合索引，即包含幾個列的索引；二是唯一性索引，即任意兩行中的索引列不能有相同的取值；三是簇類索引（Clustered index

10、es）和非簇類索引(Nonclustered indexs)，SYBASE培訓(xùn)教材12Index與性能-2a) 普通索引(create index) 和唯一性索引(create UNIQUE index)b) 單個索引和組合索引( 最多16 個字段 組成)c) 聚簇索引CLUSTERED 和非聚簇索引UNCLUSTERED :聚簇索引：即物理索引，與基表的物理順序相同(不斷重排表行的物理順序以使其與邏輯索引順序相一致)，數(shù)據(jù)值的順序總是按照順序排列. 即索引樹的葉級頁包含實(shí)際的數(shù)據(jù) . 非聚簇索引: 索引順序與數(shù)據(jù)物理排列順序無關(guān), 即葉級頁指向表中的記錄. , 一個表最多可建249 個非聚簇

11、索引. 可以這么理解聚簇索引：索引的葉節(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)。而非聚簇索引的葉節(jié)點(diǎn)仍然是索引節(jié)點(diǎn)，只不過有一個指針指向?qū)?yīng)的數(shù)據(jù)塊. 每個表最多只能建立一個簇類索引，非簇類索引則可建立多個。與非簇類索引相比，簇類索引的檢索速度要快。應(yīng)當(dāng)在建立任何非簇類索引以前建立簇類索引。SYBASE培訓(xùn)教材13Index與性能-31 1：選擇選擇ClusteredClustered索引索引的建議：的建議：主鍵時常作為主鍵時常作為wherewhere子句的條件子句的條件 某一列經(jīng)常以這樣的格式出現(xiàn)在某一列經(jīng)常以這樣的格式出現(xiàn)在wherewhere表達(dá)式中表達(dá)式中(x=column =y) (x=column use

12、 master 2go 1sp_dboption database_name,trunc,true 2go 1use database_name 2go 1checkpoint 2go Bcp. -b 100 (on unix) -一個批執(zhí)行100行拷貝SYBASE培訓(xùn)教材22Sybase事務(wù)日志-5增加閾值（增加閾值（sp_addthreshold）例如：sp_addthreshold pubs2, logsegment, 200, dump_transaction 其中，存儲過程定義為： create procedure dump_transaction dbname varchar (3

13、0), segmentname varchar (30), space_left int, status int as dump transaction dbname to /dev/rmtx那么，當(dāng)日志段上可用空間小于200頁時，SQL Server運(yùn)行存儲過程dump_transaction,將pubs2 數(shù)據(jù)庫的日志轉(zhuǎn)儲到另一臺設(shè)置上。SYBASE培訓(xùn)教材23數(shù)據(jù)庫鎖數(shù)據(jù)庫行數(shù)據(jù)庫行/ /頁頁/ /表鎖表鎖為了在使用的一個表中改變加鎖類型，可以采用如下的句法格式：alter table lock allpages | datapages | datarows在索引頁面中不會破壞事務(wù)加鎖。

14、相反，而是采用了一種稱之為鎖存的機(jī)制。鎖存是一種類似于旋轉(zhuǎn)鎖（spinlocks）的同步方法sp_configure number of pre-allocated extent設(shè)置為設(shè)置為16也將對系統(tǒng)性能也將對系統(tǒng)性能有明顯的積極的效果有明顯的積極的效果，減小鎖資源的競爭。，減小鎖資源的競爭。共享鎖、排他鎖，死鎖共享鎖、排他鎖，死鎖數(shù)據(jù)庫的鎖存技術(shù)將那些在索引頁中發(fā)生死鎖的情形盡量排除掉。減小死鎖的方法：1：盡量對數(shù)據(jù)多次并發(fā)訪問2：配置“帶有加鎖方式的讀提交”（read committed with lock”），并可用如下的命令加以配置：sp_configure read commit

15、ted with lock,0|1SYBASE培訓(xùn)教材24數(shù)據(jù)庫死鎖可能發(fā)生的死鎖可能發(fā)生的死鎖的原因：的原因：1：插入與非群聚性的索引訪問2：數(shù)據(jù)更新的范圍當(dāng)?shù)谝粋€用戶到達(dá)所確定的頁面邊緣，即該范圍即將終止，下一批所組塊并妨礙的頁面范圍即將開始之時，就可能發(fā)生死鎖。3：按照不同順序訪問表即使訪問行是不同的，對表按照任何不一致的順序進(jìn)行訪問，也會意味著可能發(fā)生死鎖。4：如何查鎖阻塞Sp_who sp_lock , select * from sysprocesses where blocked 05: 如何查鎖的表名 sp_lock-查其中查其中table_id=XXXXXXX對應(yīng)的表對應(yīng)的表

16、select * from sysobjects where id = XXXXXXXSYBASE培訓(xùn)教材25數(shù)據(jù)庫的備份和恢復(fù)數(shù)據(jù)庫的備份：必須啟動backup server由于文件的大小超出了操作系統(tǒng)的用戶最大文件限制。而有些操作系統(tǒng)不支持大于2GB的文件，這時可以使用Backup Server將一個數(shù)據(jù)庫后備到多個文件中。dump database pubs2 to /usr/sybase/pubs2_dump.1stripe on /usr/sybase/pubs2_dump.2stripe on /usr/sybase/pubs2_dump.3go 這種方法還可以提高后備及恢復(fù)的速度

17、，但注意恢復(fù)也必須用相應(yīng)多的設(shè)備。例如：1load database pubs2 from /usr/sybase/pubs2_dump.12stripe on /usr/sybase/pubs2_dump.23stripe on /usr/sybase/pubs2_dump.34go SYBASE培訓(xùn)教材26如何生成bcp命令文件以pubs2為例編輯一個文本文件select.sql，內(nèi)容如下： set nocount on use pubs2 go select bcp pubs2. + name + out + name + .bcp -Usa -P -c from sysobjects

18、where type=U goSYBASE培訓(xùn)教材27檢查修改服務(wù)器基本配置參數(shù)檢查修改服務(wù)器基本配置參數(shù)內(nèi)存配置：內(nèi)存配置：sp_configure “total memory”, new_value (修改內(nèi)存配置，new_value的單位為2K)CPU數(shù)：數(shù)：sp_configure “max online engines”, cpu_number(修改CPU配置，cpu_number小于等于實(shí)際cpu數(shù))并發(fā)用戶連接數(shù)：并發(fā)用戶連接數(shù)：sp_configure “number of user connections” (檢查當(dāng)前的并發(fā)用戶連接數(shù))sp_configure number

19、of locksp_configure procedure cache sizesp_configure number of usersp_configure number of open databasessp_configure number of open objects,150000sp_configure number of open indexes,150000SYBASE培訓(xùn)教材28如何解決數(shù)據(jù)庫被掛起的問題1現(xiàn)象：現(xiàn)象：Error 926 Severity Level 14 Error Message Text Database xx cannot be opened - it

20、 has been marked SUSPECT by recover Explanation (一一)：你使用你使用Transact_SQL命令操作這個數(shù)據(jù)庫的數(shù)據(jù)時命令操作這個數(shù)據(jù)庫的數(shù)據(jù)時, 出現(xiàn)這個信息出現(xiàn)這個信息, 這是一個這是一個嚴(yán)重的錯誤嚴(yán)重的錯誤, 如果你要使用這個數(shù)據(jù)庫的數(shù)據(jù)如果你要使用這個數(shù)據(jù)庫的數(shù)據(jù), 必須改正這個錯誤必須改正這個錯誤. (二二) ：啟動啟動Backup Server, 后備后備master數(shù)據(jù)庫數(shù)據(jù)庫1dump database master to /usr/sybase/master.dup2go（三（三) 用用isql登錄到登錄到SQL Server

21、, 須用須用sa帳號帳號 (本文以本文以pubs2數(shù)據(jù)庫為例數(shù)據(jù)庫為例)1sp_configure “allow updates”, 1 -允許修改允許修改mask庫庫2go 1begin tran 2go 1use master2go1update sysdatabases 2set status = -32768 3Where name=pubs2 4goSYBASE培訓(xùn)教材29如何解決數(shù)據(jù)庫被掛起的問題2如果得到如果得到(1 row affected),則則1commit2go否則否則1rollback2go（四（四)重新啟動重新啟動SQL Server. 注：注：SQL Server重

22、新啟動之后，當(dāng)發(fā)現(xiàn)數(shù)據(jù)庫本身存在重新啟動之后，當(dāng)發(fā)現(xiàn)數(shù)據(jù)庫本身存在不可恢復(fù)的問題不可恢復(fù)的問題時，如數(shù)時，如數(shù)據(jù)頁損壞等，且沒有完好的數(shù)據(jù)庫備份，一定要用據(jù)頁損壞等，且沒有完好的數(shù)據(jù)庫備份，一定要用bcp.out備份用戶數(shù)據(jù)庫數(shù)據(jù)備份用戶數(shù)據(jù)庫數(shù)據(jù)。此時，以下步驟省略，并按照。此時，以下步驟省略，并按照“如何刪除壞的用戶數(shù)據(jù)庫如何刪除壞的用戶數(shù)據(jù)庫”文章刪除此數(shù)據(jù)庫。文章刪除此數(shù)據(jù)庫。之后重建此數(shù)據(jù)庫，恢復(fù)備份。之后重建此數(shù)據(jù)庫，恢復(fù)備份。否則，按以下步驟繼續(xù)操作：否則，按以下步驟繼續(xù)操作：用用sa帳號注冊到帳號注冊到SQL Server. 1begin tran 2go 1use maste

23、r2goSYBASE培訓(xùn)教材30如何解決數(shù)據(jù)庫被掛起的問題31update sysdatabases 2set status=0 3Where name=pubs2 4go 如果得到如果得到(1 row affected),則則1commit 1commit 2go2go否則否則 1rollback 2go2go1sp_configure “allow updates” ,0-不允許修改不允許修改maskmask庫庫2go (五五)重新啟動重新啟動SQL Server.(六六) 如果你的數(shù)據(jù)庫原來有如果你的數(shù)據(jù)庫原來有dboption(例如例如select into,trunc log on

24、chkpt等等), 你你需要重新設(shè)置這些需要重新設(shè)置這些option.(七七) 數(shù)據(jù)庫已經(jīng)恢復(fù)可使用狀態(tài)后，運(yùn)行數(shù)據(jù)庫已經(jīng)恢復(fù)可使用狀態(tài)后，運(yùn)行dbcc命令檢查數(shù)據(jù)庫的一致性命令檢查數(shù)據(jù)庫的一致性(八八) 備份用戶數(shù)據(jù)庫：備份用戶數(shù)據(jù)庫：1dump database pubs2 to /usr/sybase/pubs2.dup2goSYBASE培訓(xùn)教材31 如何終止數(shù)據(jù)庫恢復(fù)過程1當(dāng)某一正常運(yùn)行的大事務(wù)（例如：update、delete操作）被終止，且重新啟動server后，運(yùn)行該事務(wù)的數(shù)據(jù)庫處于恢復(fù)狀態(tài)，通常這種狀態(tài)會持續(xù)很長時間，當(dāng)在此恢復(fù)過程中沒有出現(xiàn)任何異常時，建議耐心等待恢復(fù)過程完成

25、。同時提供以下方法來終止此恢復(fù)過程，但請注意這些操作將帶來數(shù)據(jù)不一致性。必要時，希望用戶用完整、可靠的數(shù)據(jù)庫備份恢復(fù)此數(shù)據(jù)庫。 (1) 啟動Backup Server后，備份master數(shù)據(jù)庫（2）用isql登錄到SQL Server, 須用sa帳號 (本文以pubs2數(shù)據(jù)庫為例)1sp_configure allow updates, 1 2go 1begin tran 2go 1 use master2 go1update sysdatabases 2set status = -32768 3Where name=pubs2 4go如果得到(1 row affected),則commit提

26、交，否則rollbackSYBASE培訓(xùn)教材32 如何終止數(shù)據(jù)庫恢復(fù)過程2（3）這時重新啟動這時重新啟動SQL Server, 再用再用sa帳號登錄到帳號登錄到SQL Server. 1dump tran pubs2 with no_log2go1begin tran 2go 1 use master2 go1update sysdatabases 2set status=0 3Where name=pubs2 4go 如果得到(1 row affected),則commit提交，否則rollback(4) 如果你的數(shù)據(jù)庫原來有如果你的數(shù)據(jù)庫原來有dboption(例如例如select int

27、o,trunc log on chkpt等等), 你需要重新設(shè)置這些你需要重新設(shè)置這些option. (5) 當(dāng)數(shù)據(jù)庫已經(jīng)恢復(fù)可使用狀態(tài)后，運(yùn)行當(dāng)數(shù)據(jù)庫已經(jīng)恢復(fù)可使用狀態(tài)后，運(yùn)行dbcc命令檢查數(shù)據(jù)庫的一致性命令檢查數(shù)據(jù)庫的一致性(參照參照如何檢查數(shù)據(jù)庫中數(shù)據(jù)一致性如何檢查數(shù)據(jù)庫中數(shù)據(jù)一致性文章文章)(6) 備份備份用戶數(shù)據(jù)庫用戶數(shù)據(jù)庫SYBASE培訓(xùn)教材33如何刪除壞的用戶數(shù)據(jù)庫當(dāng)使用當(dāng)使用drop database無法刪除數(shù)據(jù)庫時，使用本文所示方法可以刪除。無法刪除數(shù)據(jù)庫時，使用本文所示方法可以刪除。(1)設(shè)置允許修改系統(tǒng)表設(shè)置允許修改系統(tǒng)表1sp_configure allow upda

28、tes,12go(2)把把 要刪除的用戶數(shù)據(jù)庫置為要刪除的用戶數(shù)據(jù)庫置為suspect狀態(tài)狀態(tài)1use master2go1begin tran2go1update sysdatabases set status=2562where name=pubs23go如果得到如果得到(1 row affected),則則則commit提交，否則rollback(3)重啟重啟server,并用并用isql以以sa注冊。注冊。(4)刪除數(shù)據(jù)庫刪除數(shù)據(jù)庫1dbcc dbrepair(pubs2,dropdb)2go (5)恢復(fù)允許修改系統(tǒng)表恢復(fù)允許修改系統(tǒng)表1sp_configure allow updat

29、es,02goSYBASE培訓(xùn)教材34數(shù)據(jù)一致性檢查1數(shù)據(jù)庫一致性檢查(dbcc)提供了一些命令用于檢查數(shù)據(jù)庫的邏輯和物理一致性。Dbcc主要有兩個功能：使用checkstorage 或 checktable 及 checkdb 在頁一級和行一級檢查頁鏈及數(shù)據(jù)指針。 使用checkstorage, checkalloc, 或 checkverify, tablealloc, 及indexalloc 檢查頁分配。 在下列情況中需要使用 dbcc 命令：作為數(shù)據(jù)庫日常維護(hù)工作的一部分, 數(shù)據(jù)庫內(nèi)部結(jié)構(gòu)的完整性決定于sa 或dbo 定期地運(yùn)行 dbcc 檢查。 在系統(tǒng)報錯以后, 確定數(shù)據(jù)庫是否有損壞。 在備份數(shù)據(jù)庫之前, 確保備份的完整性。 如果懷疑數(shù)據(jù)庫有損壞時, 例如, 使用某個表時報出表損壞的信息, 可以使用 dbcc 確定數(shù)據(jù)庫中其他表是否也有損壞。 SYBASE培訓(xùn)教材35數(shù)據(jù)一致性檢查2下面是dbcc的簡單用法：dbcc checktable (table_name)檢查指定的表, 索引和數(shù)據(jù)頁是否正確鏈接, 索引是否正確排序, 所有指針是否一致, 每頁的數(shù)據(jù)信息是否合理, 頁偏移是否合