oracle 復原段快照過舊,LOB復原段快照過舊ORA-22924 snapshot too old | 信春哥，系統穩，閉眼上線不復原！...

昨晚接到一個電話，打電話的是開發人員，說資料庫遇到ORA-01555錯誤，應用無法正常使用，當時很納悶，ORA-01555錯誤通常影響的是查詢操作，怎麼會影響業務無法使用呢？

按照正常流程，讓他查了下undo表空間和undo保留時間，發現除了undo保留時間較短外并沒有異常。

SQL> select a.NAME,b.FILE_NAME,b.BYTES/1024/1024/1024 as BYTES,b.MAXBYTES/1024/1024/1024 as MAXBYTES from v$tablespace a,dba_data_files b where a.NAME=b.TABLESPACE_NAME AND A.NAME='UNDOTBS';

NAME FILE_NAME BYTES MAXBYTES

---------- -------------------------------------------------- ---------- ----------

UNDOTBS /u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf .48828125 31.9999847

SQL> show parameter undo_r

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_retention integer 900

雖然undo的保留時間隻有900秒(15分鐘)，可是undo表空間隻使用了500M，而undo表空間最大可以擴充到32G，說明undo表空間還是足夠的，将undo_retention修改為10800秒(3小時)，開發人員說問題依舊，還是報快照過舊，而且SQL剛運作就報錯，懷疑是使用了閃回查詢，開發人員不懂，發過來SQL語句看了下，隻是一條非常簡單的SQL，并沒有使用閃回查詢。

select * from t_restree where c_pid = 'BPM_TREENODE';

難道是遇到了10.2.0.5版本之前的ORA-01555的BUG了？申請遠端看下，遠端後發現特别奇怪，告警日志并沒有記錄這個ORA-01555的任何資訊，無論是SYSTEM UNDO還是UNDO TABLESPACE導緻的ORA-01555錯誤還是BUG導緻的ORA-01555錯誤，告警日志通常都會記錄，而這個卻沒有記錄。檢視資料庫版本，發現是11.2.0.3.0版本。

由于是查詢操作，直接在SQLPLUS裡查詢，發現的确會遇到ORA-01555錯誤。

SQL> select * from jxpg_smartbi.t_restree where c_pid = 'BPM_TREENODE';

ERROR:

ORA-01555: snapshot too old: rollback segment number with name "" too small

ORA-22924: snapshot too old

正常無論是SYSTEM UNDO還是UNDO TABLESPACE導緻的ORA-01555錯誤，在報錯提示中通常都可以看到復原段的number号和名字，而這個報錯number和name都是空的，很奇怪，而且ORA-01555錯誤下面還帶着ORA-22924錯誤，這讓我想到了第三種UNDO，就是LOB的復原段。這裡先說一下ORACLE資料庫的復原段，我知道的有三種：

1.system undo：system復原段，使用system表空間，system undo損壞通常需要借助bbed工具恢複，相對複雜。

2.undo tablespace：這個大家都知道，存一些使用者DML操作的資料塊的前鏡像，保證一緻性讀，大部分的ORA-01555錯誤都與undo tablespace有關。

3.LOB undo：這個可能很多人就不知道了，LOB大對象有自己的undo空間，并不使用undo tablespace，而是使用存放LOB本身的資料塊，使用的是LOB使用的表空間，而且大小受LOB對象占用的資料塊個數與pctversion的影響。

通常system undo出發ORA-01555錯誤都是在資料庫啟動的時候，而且資料庫起不來，而且業務使用者基本不會觸發，第一種情況基本可以排除了。第二種情況也不符合，除了BUG和閃回查詢，如果是第二種情況基本不會在SQL運作時立馬抛出ORA-01555錯誤。那麼，最大的嫌疑就是第三種情況了。

檢視表結構，是否存在LOB字段(應該是CLOB，BLOB通常不會在資料庫中做DML操作)。

SQL> desc jxpg_smartbi.t_restree

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

C_RESID NOT NULL VARCHAR2(255)

C_RESNAME VARCHAR2(255)

C_RESTYPE VARCHAR2(255)

C_PERM CLOB

C_RESDESC VARCHAR2(255)

C_STATUS VARCHAR2(255)

C_LASTMODIFIED DATE

C_RESALIAS VARCHAR2(255)

C_CREATED DATE

C_ORDER NUMBER(38)

C_EXTENDED CLOB

C_PID VARCHAR2(255)

可見，表中包含兩個CLOB字段，和猜測的基本一緻，在看看ORA-22924錯誤，快照過舊不都是ORA-01555嗎，怎麼又出來個22924，檢視22924的錯誤描述。

[[email protected] admin]$ oerr ora 22924

22924, 00000, "snapshot too old"

// *Cause: The version of the LOB value needed for the consistent read was

// already overwritten by another writer.

// *Action: Use a larger version pool.

原來ORA-22924是LOB專有的快照過舊的錯誤代号，到這也就證明我的猜測是對的。檢視下他們的表是如何建的，發現還是存在很多問題。

SQL> select dbms_metadata.get_ddl('TABLE','T_RESTREE','JXPG_SMARTBI') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T_RESTREE','JXPG_SMARTBI')

--------------------------------------------------------------------------------

CREATE TABLE "JXPG_SMARTBI"."T_RESTREE"

( "C_RESID" VARCHAR2(255) NOT NULL ENABLE,

"C_RESNAME" VARCHAR2(255),

"C_RESTYPE" VARCHAR2(255),

"C_PERM" CLOB,

"C_RESDESC" VARCHAR2(255),

"C_STATUS" VARCHAR2(255),

"C_LASTMODIFIED" DATE,

"C_RESALIAS" VARCHAR2(255),

"C_CREATED" DATE,

"C_ORDER" NUMBER(*,0),

"C_EXTENDED" CLOB,

"C_PID" VARCHAR2(255),

PRIMARY KEY ("C_RESID")

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 655360 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "JYJGODS" ENABLE

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE(INITIAL 3145728 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "JYJGODS"

LOB ("C_PERM") STORE AS BASICFILE (

TABLESPACE "JYJGODS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10

NOCACHE LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

LOB ("C_EXTENDED") STORE AS BASICFILE (

TABLESPACE "JYJGODS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10

NOCACHE LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

這裡發現了幾處問題：

ENABLE STORAGE IN ROW：含有LOB字段的表，建議不要講LOB字段放到行内，應該将LOB字段放到行外，并且存儲在獨立的表空間，也就是此處應該是DISABLE STORAGE IN ROW。原因是LOB對象通常都較大，存在行内可能會導緻行連結，影響性能，将LOB存在獨立的表空間，IO性能也會有所提高。

STORE AS BASICFILE：資料庫目前版本11.2.0.3.0，11g推出了securefiles新特性來存儲LOB字段，讀寫性能都比BASICFILE要高很多，出于對性能的考慮，應該使用SECUREFILES存儲模式來存LOB字段。

PCTVERSION 10：這也是導緻這個問題發生的根本原因，PCTVERSION也就是version pool，這部分空間就是預留給LOB使用的復原段，預設是10%，對于BLOB來講(BLOB通常隻是檢視，基本沒有在資料庫中修改的操作)，10%基本足夠了，可是對CLOB來講，10%的復原段就不是很富裕了，也正是這樣，他們才遇到這個問題。

解決方法：

可以增大PCTVERSION，使LOB的復原段增大。

擴充表的EXTENT，擴充表的EXTENT也會間接增大LOB的復原段。

建立BLOCK SIZE為16K的表空間，将表MOVE到新表空間。

——————————–end——————————–