DataGuard添加臨時資料檔案的bug

DataGuard添加臨時資料檔案的bug

有一個環境是Oracle 10gR2，一主兩備，因為10g的備庫還不是active,所以有一些查詢的需求的時候，我們還是會開啟相應的視窗時間。

開發的同學需要做一個大查詢，資料只能全表，而且還有order by，勢必會消耗大量的temp空間，這個時候充分利用備庫就是好一些，有一個備庫平時也沒有用過，今天就用這個備庫來完成查詢需求。

但是過了一會，開發同事說，查詢失敗了。讓我看看什麼原因。

開發同學提供的日誌為：

2015-11-20 10:48:05,---exception: ---- StatementCallback; uncategorized SQLException for SQL [select c.cn as cn,c.uin as uin from test_bind c where enabled='Y' group by c.cn,c.uin having count(c.cn) >1]; SQL state [99999]; error code [25153]; ORA-25153: Temporary Tablespace is Empty

; nested exception is java.sql.SQLException: ORA-25153: Temporary Tablespace is Empty

看來這個問題還挺不好意思的，原來暫存資料表空間為空白了。

SQL> select file_name,bytes from dba_temp_files;

no rows

那麼備庫中的暫存資料表空間怎麼沒了呢？

查看記錄發現是在前幾天的一次日誌應用後，暫存資料表空間清空了。

Tue Nov 17 17:48:23 CST 2015

Media Recovery Log /U01/app/oracle/admin/acctest/arch/1_21281_782846320.dbf

Recovery deleting tempfile #3:'/U03/app/oracle/oradata/acctest/temp03.dbf'

Recovery deleting tempfile #2:'/U03/app/oracle/oradata/acctest/temp02.dbf'

Recovery deleting tempfile #1:'/U03/app/oracle/oradata/acctest/temp01.dbf'

Recovery dropped temporary tablespace 'TEMP'

Media Recovery Waiting for thread 1 sequence 21282

因為暫存資料表空間對於資料庫來說還是一個輔助的部分，主備庫可以不同。所以簡單的分析之後決定還是手工添加臨時資料檔案。

這個時候查看暫存資料表空間，發現已經是TEMP2了。

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

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

SYSTEM

UNDOTBS1

SYSAUX

USERS

...

TEMP2

9 rows selected.

既然是空的，那就添加一個臨時資料檔案吧。結果基本功不紮實，錯誤提示還是有些誤導。

SQL> alter tablespace temp2 add datafile /U03/app/oracle/oradata/acctest/temp01.dbf' size 32G;

alter tablespace temp2 add datafile /U03/app/oracle/oradata/acctest/temp01.dbf' size 32G

*

ERROR at line 1:

ORA-16000: database open for read-only access

簡單修改，把datafile改為tempfile繼續

SQL> alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp01.dbf' size 30G;

alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp01.dbf' size 30G

*

ERROR at line 1:

ORA-01119: error in creating database file '/U03/app/oracle/oradata/acctest/temp01.dbf'

ORA-27038: created file already exists

Additional information: 1

這個時候提示檔案已經存在，好吧，確實是檔案存在，那我就reuse吧。

使用resue的方式，結果報出了ORA-27086的錯誤。

SQL> alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp01.dbf' size 30G reuse;

alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp01.dbf' size 30G reuse

*

ERROR at line 1:

ORA-01119: error in creating database file '/U03/app/oracle/oradata/acctest/temp01.dbf'

ORA-27086: unable to lock file - already in use

Linux-x86_64 Error: 11: Resource temporarily unavailable

Additional information: 8

Additional information: 19076

這個錯誤還是讓人有些摸不著頭腦。是本身就有臨時資料檔案嗎？

SQL> select file_name,bytes from dba_temp_files;

no rows

這個時候查看檔案系統中檔案的情況。發現貌似時間戳記確實是更新了，但是這個檔案就是不在資料字典裡。

$ ll temp*.dbf

total 432600976

-rw-r----- 1 oracle oinstall 32212262912 Nov 20 10:58 temp01.dbf

-rw-r----- 1 oracle oinstall 21109940224 Jan  5  2015 temp02.dbf

-rw-r----- 1 oracle oinstall 21109940224 Jan  5  2015 temp03.dbf

那麼這個問題還是很奇怪的，只能聯想到是bug了，結果一查還真有這麼一個bug,版本都完全符合。

Bug 15944809 - add tempfile at physical standby fails with ORA-1119, ORA-27086 (Doc ID 15944809.8)

這個問題的workaround 有兩個，一個就是重啟備庫

Workaround

A shutdown/startup of the standby databse will clear the DBW0's

stale file lock state, and then the new tempfile can be created.

那我先試試添加一個新的資料檔案，先不停庫。

SQL> alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp04.dbf' size 10G;

Tablespace altered.

可以建立了，那就開始resize一下。

SQL> alter database tempfile '/U03/app/oracle/oradata/acctest/temp04.dbf' resize 30G;

Database altered.

這個時候，先讓開發同學去完成這個查詢任務。

然後查詢完成之後，收迴環境之後，就可以嘗試重啟了。

重啟之後，再次添加臨時資料檔案，就沒有問題了。

SQL> alter tablespace temp2 add tempfile '/U03/app/oracle/oradata/acctest/temp01.dbf' size 30G reuse;

Tablespace altered.

SQL> select name,bytes from v$tempfile;

NAME                                                    BYTES

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

/U03/app/oracle/oradata/acctest/temp04.dbf        3.2212E+10

/U03/app/oracle/oradata/acctest/temp01.dbf        3.2212E+10

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2

Oracle Data Guard 重要配置參數

基於同一主機配置 Oracle 11g Data Guard

探索Oracle之11g DataGuard

Oracle Data Guard （RAC+DG) 歸檔刪除策略及指令碼

Oracle Data Guard 的角色轉換

Oracle Data Guard的日誌FAL gap問題

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 處理方法