如何查看oracle數(shù)據(jù)庫中哪些session異常阻塞了系統(tǒng)

Oracle數(shù)據(jù)庫運(yùn)維過程中有時(shí)會(huì)遇到一種異常情況，由于錯(cuò)誤的操作或代碼BUG造成session異常地持有鎖不釋放，并大量阻塞系統(tǒng)對話。這時(shí)候需要找出造成異常阻塞的session并清除。 oracle session通常具有三個(gè)特征： （1）一個(gè)session可能阻塞多個(gè)session； （2）一個(gè)session最多被一個(gè)session阻塞； （3）session阻塞關(guān)系不會(huì)形成環(huán)路。（環(huán)路即死鎖，oracle能自動(dòng)解除） 因此session的阻塞關(guān)系為一棵樹，進(jìn)而DB系統(tǒng)所有session的BLOCK阻塞關(guān)系是一個(gè)由若干session阻塞關(guān)系樹構(gòu)成的森林，而異常session一定會(huì)在故障爆發(fā)時(shí)成為根（root）。因此，找尋異常鎖表session的過程就是找出異常的root。 一般認(rèn)為異常root有兩個(gè)特征：（1）block樹的規(guī)模過大，阻塞樹規(guī)模即被root層層阻塞的session總數(shù)；（2）阻塞的平均等待時(shí)間過長。 查找異常session的方法一： OEM— performance— Blocking Sessions 查找異常session的方法二： select r.root_sid, s.serial#, r.blocked_num, r.avg_wait_seconds, s.username,s.status,s.event,s.MACHINE, s.PROGRAM,s.sql_id,s.prev_sql_id from (select root_sid, avg(seconds_in_wait) as avg_wait_seconds, count(*) - 1 as blocked_num from (select CONNECT_BY_ROOT sid as root_sid, seconds_in_wait from v$session start with blocking_session is null connect by prior sid = blocking_session) group by root_sid having count(*) 1) r, v$session s where r.root_sid = s.sid order by r.blocked_num desc, r.avg_wait_seconds desc; 該SQL語句即是根據(jù)v$session的字段blocking_session統(tǒng)計(jì)阻塞樹根阻塞session的計(jì)數(shù)以及平均阻塞時(shí)間、并進(jìn)行排序，排名最前的往往是異常session。 另外需要注意的是，持有鎖時(shí)間最長、或等待時(shí)間最長的session都不一定是造成阻塞的根源session！

成都網(wǎng)站建設(shè)公司更懂你！成都創(chuàng)新互聯(lián)只做搜索引擎喜歡的網(wǎng)站！成都網(wǎng)站制作前臺(tái)采用搜索引擎認(rèn)可的DIV+CSS架構(gòu),全站HTML靜態(tài),H5高端網(wǎng)站建設(shè)+CSS3網(wǎng)站,提供：網(wǎng)站建設(shè)，微信開發(fā)，成都微信小程序，商城網(wǎng)站建設(shè)，成都app開發(fā)，主機(jī)域名，服務(wù)器租售，網(wǎng)站代托管運(yùn)營，微信公眾號代托管運(yùn)營。

Oracle常見錯(cuò)誤代碼的分析與解決一

在使用ORACLE的過程過 我們會(huì)經(jīng)常遇到一些ORACLE產(chǎn)生的錯(cuò)誤 對于初學(xué)者而言 這些錯(cuò)誤可能有點(diǎn)模糊 而且可能一時(shí)不知怎么去處理產(chǎn)生的這些錯(cuò)誤 本人就使用中出現(xiàn)比較頻繁的錯(cuò)誤代碼一一做出分析 希望能夠幫助你找到一個(gè)合理解決這些錯(cuò)誤的方法 同時(shí)也希望你能夠提出你的不同看法 畢竟作為一種交流的手段 個(gè)人意見難免過于偏頗 而且也必定存在著不足 出錯(cuò)之處在所難免 寫這篇文章的目的就是想通過相互之間的交流共同促進(jìn) 共同進(jìn)步 ORA :unable to extend rollback segment NAME by NUM intablespace NAME 產(chǎn)生原因 上述ORACLE錯(cuò)誤為回滾段表空間不足引起的 這也是ORACLE數(shù)據(jù)管理員最常見的ORACLE錯(cuò)誤信息 當(dāng)用戶在做一個(gè)非常龐大的數(shù)據(jù)操作導(dǎo)致現(xiàn)有回滾段的不足 使可分配用的回滾段表空間已滿 無法再進(jìn)行分配 就會(huì)出現(xiàn)上述的錯(cuò)誤 解決方式 使用 ALTER TABLESPACE tablespace_name ADD DATAFILE filename SIZE size_of_file 命令向指定的數(shù)據(jù)增加表空間 根據(jù)具體的情況可以增加一個(gè)或多個(gè)表空間 當(dāng)然這與還與你主機(jī)上的裸盤設(shè)備有關(guān) 如果你主機(jī)的裸盤設(shè)備已經(jīng)沒有多余的使用空間 建議你不要輕意的增加回滾段表空間的大小 可使用下列的語句先查詢一下剩余的tablespace空間有多少 Select user_name sql_text from V$open_cursor where user_name= ; 如果多余的空間比較多 就可以適當(dāng)追加一個(gè)大的回滾段給表空間使用 從而避免上述的錯(cuò)誤 你也可以用以下語句來檢測一下rollback segment的競爭狀況 Select class count from V$waitstat where calss in( system undo header system undo block undo header undo block );和 Select sum(value) from V$sysstat where name in ( db_block_gets consistents gets );　如果任何一個(gè)class in count/sum(value)大于 % 就應(yīng)該考慮增加rollback segment 相應(yīng)的英文如下 Cause:Failed to allocate extent from the rollback segment in tablespace Action:Use the ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the specified tablespace ORA :unable to extend temp segment by num in tablespace name　產(chǎn)生原因 ORACLE臨時(shí)段表空間不足 因?yàn)镺RACLE總是盡量分配連續(xù)空間 一但沒有足夠的可分配空間或者分配不連續(xù)就會(huì)出現(xiàn)上述的現(xiàn)象 解決方法 我們知道由于ORACLE將表空間作為邏輯結(jié)構(gòu) 單元 而表空間的物理結(jié)構(gòu)是數(shù)據(jù)文件 數(shù)據(jù)文件在磁盤上物理地創(chuàng)建 表空間的所有對象也存在于磁盤上 為了給表空間增加空間 就必須增加數(shù)據(jù)文件 先查看一下指定表空間的可用空間 使用視圖SYS DBA_FREE_SPACE 視圖中每條記錄代表可用空間的碎片大小 SQLSelect file_id block_id blocks bytes from sys dba_free_space where tablespace_name= ; 返回的信息可初步確定可用空間的最大塊 看一下它是否小于錯(cuò)誤信息中提到的尺寸 再查看一下缺省的表空間參數(shù) SQLSELECT INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS PCT_INCREASE FROM SYS DBA_TABLESPACES WHERE TABLESPACE_NAME=name; 通過下面的SQL命令修改臨時(shí)段表空間的缺省存儲(chǔ)值 SQLALTER TABLESPACE name DEFAULT STORAGE (INITIAL XXX NEXT YYY); 適當(dāng)增大缺省值的大小有可能解決出現(xiàn)的錯(cuò)誤問題 也可以通過修改用戶的臨時(shí)表空間大小來解決這個(gè)問題 SQLALTER USER username TEMPORARY TABLESPACE new_tablespace_name; 使用ALTER TABLESPACE命令 一但完成 所增加的空間就可使用 無需退出數(shù)據(jù)庫或使表空間脫機(jī) 但要注意 一旦添加了數(shù)據(jù)文件 就不能再刪除它 若要?jiǎng)h除 就要?jiǎng)h除表空間 一個(gè)報(bào)錯(cuò)例子如下 ORA :unable to extend temp segment by in tablespace TEMPSPACE 相應(yīng)的英文如下 Cause: Failed to allocate extent for temp segment in tablespace Action:Use the ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the specified tablespace or create the object in another tablespace ORA :Oracle data block corrupted(file # num block # num)　產(chǎn)生原因 當(dāng)ORACLE訪問一個(gè)數(shù)據(jù)塊時(shí) 由于 硬件的I/O錯(cuò)誤 操作系統(tǒng)的I/O錯(cuò)誤或緩沖問題 內(nèi)存或paging問題 ORACLE試圖訪問一個(gè)未被格式化的系統(tǒng)塊失敗 數(shù)據(jù)文件部分溢出等上述幾種情況的一種引起了邏輯壞塊或者物理壞塊 這時(shí)就會(huì)報(bào)ORA 的錯(cuò)誤 解決方式 由于ORACLE只有在訪問到有問題的數(shù)據(jù)文件時(shí)才會(huì)報(bào)錯(cuò) 所以報(bào)錯(cuò)的時(shí)間有可能會(huì)比實(shí)際出錯(cuò)的時(shí)間要晚 如果ORA 出錯(cuò)信息提示數(shù)據(jù)壞塊指向的是用戶自己的數(shù)據(jù)文件 則用以下方法來解決 如果通過下面的SQL語句查出的壞塊出現(xiàn)有索引上 則只需重建索引即可 SQLSelect owner segment_name segment_type from dba_extents where file_id= and beeen block_id and block_id+blocks ; (和分別是ORA 報(bào)出的壞塊出現(xiàn)的文件號和塊號) 如果壞塊出現(xiàn)在表上 先用以下語句分析是否為永久性壞塊（建議多執(zhí)行一兩次 有助于鑒別數(shù)據(jù)壞塊是永久性的（硬盤上的物理壞塊）還是隨機(jī)性的（內(nèi)存或硬件錯(cuò)誤引起）） SQLAnalyze table validate structure cascade; 執(zhí)行該命令后 可能會(huì)出現(xiàn)以下的結(jié)果 ORA 與原先錯(cuò)誤信息有相同的參數(shù) 為永久性的物理或邏輯壞塊 與原先錯(cuò)誤信息有不同的參數(shù) 可能與內(nèi)存 page space和I/O設(shè)備有關(guān) 如果用戶有此表的最新備份 那么最好是用此備份來恢復(fù)此表 或者使用event 來取出壞塊以外的數(shù)據(jù) 先關(guān)閉數(shù)據(jù)庫 編輯init ora文件 加入 event= trace name context forever level startup restrict 創(chuàng)建一個(gè)臨時(shí)表 SQLcreate table errortemp as select * from error;(error是壞表的表名) 把event從init ora文件中刪掉并重起數(shù)據(jù)庫 rename壞表 把臨時(shí)表rename成壞表的表名 創(chuàng)建表上的INDEX等 如果ORA 出錯(cuò)信息提示數(shù)據(jù)壞塊指向的是數(shù)據(jù)字典或者是回滾段的話 你應(yīng)該立即與ORACLE公司聯(lián)系 共同商量一個(gè)好的解決辦法 這里所講的解決方法只是比較常見的一種 一些更為具體的解決辦法可以查看一下ORACLE的故障解決手冊 那里面有浞及使用ROWID方法來取出壞塊以外的數(shù)據(jù)的方法 這里就不介紹了 相應(yīng)的英文如下 Cause:The given data block was corrupted probably due to program errors Action:Try to restore the segment containing the given data block This may involve dropping the segment and recreating it If there is a trace file report the messages recorded in it to customer support ORA :max # of extents num reached for rollback segment num　產(chǎn)生原因 這種錯(cuò)誤通常為一個(gè)回滾段和一個(gè)表空間已經(jīng)達(dá)到MAXEXTENTS參數(shù)設(shè)置的極限 要注意的是這個(gè)MAXEXTENTS不是該回滾段或表空間的硬件極限 硬件極限取決于數(shù)據(jù)庫創(chuàng)建時(shí)在init ora文件中指定的DB_BLOCK_SIZE參數(shù)的值 解決方法 使用SQL命令A(yù)LTER TABLESPACE…STORAGE（MAXEXTENTS XXXX）來增加 MAXEXTENTS 其中 XXXX 值必須大于錯(cuò)誤信息中所指的數(shù)值 但不能大于LARGEST MAXEXTENT的值 如果已經(jīng)達(dá)到了LARGEST MAXEXTENT VALUE 解決的辦法就是重新創(chuàng)建較大的范圍尺寸 使用帶有選項(xiàng)PRESS=Y的Export工具導(dǎo)出表 如果表空間有可用空間 先給表做一個(gè)備份 用alter tablespace tablespace_name更改其名字 然后再裝載表回?cái)?shù)據(jù)庫 查看其錯(cuò)誤出現(xiàn)的地方 如果出現(xiàn)在回滾段或索引上 那么必須將其刪除并重建 如果出現(xiàn)在臨時(shí)表空間 修改臨時(shí)表空間的存儲(chǔ)字段 便可解決這個(gè)問題 一個(gè)報(bào)錯(cuò)例子如下 ORA :max # extents reached for rollback segment RBS_ 相應(yīng)的英文如下 Cause: An attempt was made to extend a rollback segment that already has reached its maximum size or space could not be allocated in the data dictionary to contain the definition of the object Action:If possible increase the value of either the MAXEXTENTS or PCTINCREASE initialization parameters or find the data dictionary table lacking space and alter the storage parameters as described in the Oracle Server Administrator s Guide ORA :internal error code arguments:[num] [?] [?] [?] [?]　產(chǎn)生 lishixinzhi/Article/program/Oracle/201311/18838

Oracle重做日志丟失的故障處理

Oracle重做日志

Oracle的重做日志文件(Online redo logfile)循環(huán)記錄了數(shù)據(jù)庫所有的事務(wù) 它的大小 個(gè)數(shù)和存儲(chǔ)位置對數(shù)據(jù)庫性能和恢復(fù)有重要影響 它一般由大小相同的幾組文件構(gòu)成 我們可以查看數(shù)據(jù)庫視圖v$logfile知道redo logfile的個(gè)數(shù)和存儲(chǔ)位置 對每一個(gè)Oracle數(shù)據(jù)庫都要求至少具有兩個(gè)聯(lián)機(jī)重做日志

每一次新的事務(wù)提交時(shí) Oracle將該事務(wù)寫入日志文件 但并非此時(shí)也將修改的數(shù)據(jù)塊寫回原數(shù)據(jù)文件 由于內(nèi)存讀寫和磁盤I/O存在幾個(gè)數(shù)量級的效率差別 Oracle通過減少數(shù)據(jù)文件的物理I/O讀寫來大大提高數(shù)據(jù)庫的性能 同時(shí) 又通過優(yōu)先寫日志文件來保證數(shù)據(jù)的正確性和一致性 基于這種機(jī)制 重做日志文件在數(shù)據(jù)庫的實(shí)例恢復(fù)和介質(zhì)恢復(fù)時(shí)至關(guān)重要 是oracle數(shù)據(jù)庫最重要的物理文件之一

如果數(shù)據(jù)庫在啟動(dòng)時(shí)檢測到重做日志丟失 數(shù)據(jù)庫將無法啟動(dòng) 如果數(shù)據(jù)庫在運(yùn)行時(shí)切換日志文件組 檢測到下一組或者全部的重做日志丟失 數(shù)據(jù)庫將會(huì)崩潰 由于磁盤介質(zhì)損壞或者人為的誤刪除文件 造成嚴(yán)重后果的事件近期時(shí)有發(fā)生 本文列舉了重做日志丟失的數(shù)據(jù)庫恢復(fù) 但如果按照冗余原則合理分布日志文件組的成員 如果工程師了解日志文件的基本原理和使用原則 就完全可以避免出現(xiàn)下列問題

恢復(fù)方法

故障現(xiàn)象

SQL startup mount

Oracle Instance Started

Database mounted

ORA : open failed for members of log group of thread

ORA : online log thread : /ORACLE/ORADATA/H /REDO LOG

ORA : unable to open file

OSD : unable to open file

O/S Error: (OS ) The system cannot find the file specified

恢復(fù)注意事項(xiàng)

以下所列舉的恢復(fù)方法 都屬于不完全恢復(fù)或者強(qiáng)制恢復(fù) 會(huì)丟失當(dāng)前重做日志中的事務(wù)數(shù)據(jù) 一旦操作不當(dāng) 將帶來數(shù)據(jù)丟失等嚴(yán)重后果 請遵循以下幾個(gè)恢復(fù)原則

請勿在生產(chǎn)系統(tǒng)上試用

如果生產(chǎn)系統(tǒng)出現(xiàn)重做日志文件丟失的故障 請勿自行操作破壞現(xiàn)場 應(yīng)該立刻聯(lián)系Oracle工程師

恢復(fù)成功之后 需要馬上做一次數(shù)據(jù)庫的全備份

建議重做日志文件一定要實(shí)現(xiàn)鏡象在不同的磁盤上 避免這種情況的發(fā)生

恢復(fù)方法

首先檢查重做日志文件狀態(tài) 看看報(bào)錯(cuò)的日志文件的狀態(tài)是否為Current

SQL select * from v$log;

SQL select * from v$logfile;

如果重做日志文件狀態(tài)為Inactive 我們可以直接清除該日志文件的內(nèi)容

SQL alter database clear logfile /ORACLE/ORADATA/H /REDO LOG ;

如果重做日志文件狀態(tài)為Current 恢復(fù)工作較為復(fù)雜 有以下四種情況

）通過下面步驟 數(shù)據(jù)庫順利打開

SQL recover database until cancel;

Type Cancel when prompted

SQLalter database open resetlogs;

）第一種情況的 recover database until cancel 操作遇到ORA ORA ORA 錯(cuò)誤 需要整個(gè)數(shù)據(jù)庫的物理備份 并根據(jù)歸檔日志恢復(fù)到錯(cuò)誤時(shí)間點(diǎn) 前提是數(shù)據(jù)庫是歸檔模式

restore old backup

SQL startup mount

SQL recover database until cancel using backup controlfile;

SQL alter database open resetlogs;

）如果數(shù)據(jù)庫是非歸檔模式 只能恢復(fù)整個(gè)物理備份 然后直接打開數(shù)據(jù)庫 這種情況將丟失物理備份至故障發(fā)生前的全部數(shù)據(jù)

）如果數(shù)據(jù)庫是非歸檔模式 且沒有物理備份 只能通過特殊的隱含參數(shù) 允許數(shù)據(jù)庫不一致的狀況下打開數(shù)據(jù)庫 這種恢復(fù)方法是沒有辦法之后的恢復(fù)方法 將導(dǎo)致數(shù)據(jù)庫不一致 一般情況下不要采用 如確有需要 請?jiān)贠racle的技術(shù)人員指導(dǎo)下使用該方法

???????? 關(guān)閉數(shù)據(jù)庫

SQLshutdown immediate

???????? 在initsid ora中加入如下參數(shù)

_allow_resetlogs_corruption=TRUE

???????? 重新啟動(dòng)數(shù)據(jù)庫 利用until cancel恢復(fù)

SQLrecover database until cancel;

Cancel

???????? 打開數(shù)據(jù)庫

SQLalter database open resetlogs;

???????? 數(shù)據(jù)庫被打開后 馬上執(zhí)行一個(gè)全庫導(dǎo)出

關(guān)閉數(shù)據(jù)庫 在initsid ora中去掉_all_resetlogs_corrupt參數(shù)

lishixinzhi/Article/program/Oracle/201311/16743