ORA-1654報錯處理一則

 

今天在客戶那邊巡檢一個資料庫時，在警告日誌檔案發現了一個報錯，大概每隔20秒就有一次記錄；

“ORA-1654: unable to extend index ODS.EI_P1_ATTRSTORE by 256 in tablespace P1TS_IND_STORE”

後來發現是oracle對單個資料檔案大小有限制導致的（Oracle資料檔案的大小存在一個內部限制：db_block_size為2k時,單個資料檔案最大為8G；db_block_size為4k時,單個資料檔案最大為16G；（這裡說的是小檔案表空間）））

客戶這邊的資料庫版本是9.2.0.4，linux作業系統，引數db_block_size為4096
檢視db_block_size：
SQL> show parameter db_block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 4096

表空間P1TS_IND_STORE只有一個資料檔案icatsp1_ora9i.dbf,

用命令du -sh icatsp1_ora9i.dbf檢視該資料檔案大小，17G(奇怪，不是最大16G嗎？）！

檢視錶空間有哪些資料檔案：
SQL> SELECT d.name FROM v$datafile d JOIN v$tablespace t USING(ts#) WHERE t.name='P1TS_IND_STORE';

NAME
-----------------------------------
/***/ora_data/icatsp1_ora9i.dbf

解決方法：
新增一個新的資料檔案，並設定成自動擴充套件
ALTER TABLESPACE P1TS_IND_STORE
ADD DATAFILE '/**/ora_data/icatsp2_ora9i.dbf' SIZE 2048M autoextend on next 512M MAXSIZE UNLIMITED;

觀察警告日誌檔案一段時間，沒有發現繼續報錯，問題解決。

參考文件：

ALERT: RESIZE or AUTOEXTEND can "Over-size" Datafiles and Corrupt the Dictionary [ID 112011.1]

Workaround

~~~~~~~~~~

The workaround for all of the above problems is not to use the commands

described in this alert with sizes above the maximum for your database

DB_BLOCK_SIZE.

As sizes are often specified in "K" or "M" never try to use file sizes

greater than the values given by the following select:

SELECT to_char(4194303*value,'999,999,999,999') MAX_BYTES,

to_char(trunc(4194303*value/1024),'999,999,999')||' Kb' MAX_KB,

to_char(trunc(4194303*value/1024/1024),'999,999')||' Mb' MAX_MB

FROM v$parameter WHERE name='db_block_size';

For convenience the table below shows the maximum sizes for common

DB_BLOCK_SIZES:

DB_BLOCK_SIZE Max Mb value to use in any command

~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

2048 8191 M

4096 16383 M

8192 32767 M

16384 65535 M

Note: For a 2K (2048 byte) DB_BLOCK_SIZE an 8Gb datafile is TOO LARGE.

An 8Gb file would be 8192Mb which is more than 4194303 DB blocks.