解決 oracle 11G 出現的ORA-01555 問題

oracle我很不熟, 但在公司使用exp指令備份oracle 11g資料時, 遇到以下錯誤訊息

EXP-00056: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old

爬了文, 解法有三, 我從最簡單的解法開始, 第一種,第二種都失敗, 直到第三種方式才成功.
我建議從第三種方式開始解,該方法不需修改系統設定, 影響較小.

  • 方法一
    修改 undo_retention秒數, 把時間拉大,
    **這鬼才知道怎麼知道有用,我是用備份的方式測試, 發現改了之後還是一樣出錯
SQL>  show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     21600
undo_tablespace                      string      UNDOTBS1
SQL> ALTER SYSTEM SET UNDO_RETENTION = 43200;
SQL>
System altered.

SQL>
  • 方法二
    直接加大undo tablespace ,
    這要小心點, 有很多眉角, 似乎牽涉到小心舊undo tablesapce 是否已呈現"OFFLINE", 且新的undo tablespace 呈現"ONLINE'
# 新增undo tablespace 
create undo tablespace UNDOTBS2 datafile '/.../oradata/undotbs02.dbf' size 30000M autoextend on next 100m maxsize unlimited;
#生效
alter system set undo_tablespace = UNDOTBS2 scope=both;

#查看狀態
select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status; 
select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS1';
select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;
#確認舊的已是offline , 就可以刪掉舊的
Drop tablespace UNDOTBS1 including contents and datafiles;
#再次查看是否已變成新的undo tablespace
show parameter undo
select tablespace_name tablespace, status, sum(bytes)/1024/1024 sum_in_mb, count(*) counts
from dba_undo_extents
group by tablespace_name, status order by 1,2;
  • 方法三
# 先建立一個資料表, 用來儲存有問題的資料ID(ROWID)
SQL> CREATE TABLE CORRUPTED_ROWS (CORRUPTED_ROWID ROWID, ERROR_NUMBER NUMBER);

Table created.

將有錯誤table的table schema , 欄位類型為clob , blob 欄位列出來, 問題一定在這幾個欄位之中

# 以下紅色部分請改為有問題資料表與欄位名稱, 若沒有出現錯誤, 就換下一個欄位(fieldname)試試看
SET TIMING ON
DECLARE 
   ERROR_1578  EXCEPTION;
   ERROR_1555  EXCEPTION;
   ERROR_22922 EXCEPTION;
   PRAGMA EXCEPTION_INIT(ERROR_1578,  -1578);
   PRAGMA EXCEPTION_INIT(ERROR_1555,  -1555);
   PRAGMA EXCEPTION_INIT(ERROR_22922, -22922);
   N NUMBER;
BEGIN
   FOR ROW IN (SELECT ROWID, fieldname FROM user.table)
   LOOP
      BEGIN
         N:=DBMS_LOB.INSTR(ROW.fieldname, HEXTORAW('889911'));
	  EXCEPTION
         WHEN ERROR_1578  THEN 
              INSERT INTO CORRUPTED_ROWS VALUES (ROW.ROWID, 1578);
			  COMMIT;
         WHEN ERROR_1555  THEN 
              INSERT INTO CORRUPTED_ROWS VALUES (ROW.ROWID, 1555);
			  COMMIT;
         WHEN ERROR_22922 THEN 
              INSERT INTO CORRUPTED_ROWS VALUES (ROW.ROWID, 22922);
			  COMMIT;
       END;
   END LOOP;
END;
/

接下來查詢是否真有問題資料

SELECT * FROM CORRUPTED_ROWS;
#執行以下這行指令應該要出錯
SELECT fieldname FROM user.table WHERE ROWID IN (SELECT CORRUPTED_ROWID FROM CORRUPTED_ROWS);

清空有問題的資料欄位

#若欄位型態是clob就用empty_clob(), 若為blob,就改成empty_blob(), 有幾個欄位出問題就清空幾個
update xxx.table set fieldname = empty_clob()where ROWID IN (SELECT CORRUPTED_ROWID FROM CORRUPTED_ROWS);

若不敢清空, 可以跳過有問題的資料備份

exp system@yourinstance BUFFER=81920 file=/tmp/backup.dmp  tables=user.table QUERY=\"WHERE rowid NOT IN \(SELECT CORRUPTED_ROWID FROM CORRUPTED_ROWS\)\"

參考資料

https://www.modb.pro/db/47021

查詢oracle 11g匯出檔案的字元編碼

最近遇到一個搞笑的烏龍, 公司匯出的oracle dmp檔案, 進行還原測試的時候, 無法匯入到測試區, 錯誤訊息為:

IMP-00037: Character set marker unknown

經過爬文,有可能是字元編碼不一致, 網路上提供的解方是:

 cat xxx.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6

這樣可以查出oracle系統的字元編號, 再以字元編號查詢編碼名稱

SQL> select nls_charset_name(to_number('上個指令查出的字元編號','xxxx')) from dual;

這樣的指令應該是可以查出來, 若查不出來就可能是檔案損毀或是我這樣的烏龍,

我匯出備份的時候, 加上tar以及bz2壓縮

所以原檔案變成 xxx.dmp.bz2, 完全看不出來有tar , 因此解壓的時候我忘記了只有解bz2 , 解出來的xxx.dmp 其實不是原本的dmp檔案, 還需要解開tar這個動作, 哈哈

安裝於linux的oracle 11g , 如何設定instance啟用方式

oracle 11g是很久的資料庫, 當時是配合鼎新tiptop 5.1 GP版本安裝, 安裝於centos 5.5 final ,
tiptop系統分成topprod, topstd , 與toptest , 但是到最後很少用到 topstd與toptest,

所以可以預設停用這兩個用不到的instance,

我們可以修改 /etc/oratab 這個檔案, 長得如下:

toptest:/u2/oracle/product/11.2.0/db_1:Y
topstd:/u2/oracle/product/11.2.0/db_1:Y
topprod:/u2/oracle/product/11.2.0/db_1:Y

請停掉database後, 把Y改成N, 以後重啟就不會啟動用不到的instance