資料庫日誌報：ORA-27300: OS 系統相關操作: spcdr:9261:4200 失敗, 狀態為: 997

 作業系統是：window 2003 server
 資料庫版本：10.2.0.1

 警告日誌alter_SID.log出現下面錯誤:

ORA-27300: OS 系統相關操作: spcdr:9261:4200 失敗, 狀態為: 997
ORA-27301: OS 故障訊息: 重疊 I/O 操作在進行中。
ORA-27302: 錯誤發生在: skgpspawn

alter_SID.log日誌:
--------------------------------------------------------------------------------------------------------------------------

Wed Jul 13 14:55:29 2011
Process startup failed, error stack:
Wed Jul 13 14:55:29 2011
Errors in file e:\oracle\product\10.2.0\admin\sxserver\bdump\sxserver_psp0_2984.trc:
ORA-27300: OS system dependent operation:spcdr:9261:4200 failed with status: 997
ORA-27301: OS failure message: 重疊 I/O 操作在進行中。
ORA-27302: failure occurred at: skgpspawn

Wed Jul 13 14:55:30 2011
Process J001 died, see its trace file
Wed Jul 13 14:55:30 2011
kkjcre1p: unable to spawn jobq slave process
Wed Jul 13 14:55:30 2011
Errors in file e:\oracle\product\10.2.0\admin\sxserver\bdump\sxserver_cjq0_3248.trc:

Wed Jul 13 14:55:35 2011
Process startup failed, error stack:
Wed Jul 13 14:55:35 2011
Errors in file e:\oracle\product\10.2.0\admin\sxserver\bdump\sxserver_psp0_2984.trc:
ORA-27300: OS system dependent operation:spcdr:9261:4200 failed with status: 997
ORA-27301: OS failure message: 重疊 I/O 操作在進行中。
ORA-27302: failure occurred at: skgpspawn

Wed Jul 13 14:55:36 2011
Process J001 died, see its trace file
Wed Jul 13 14:55:36 2011
kkjcre1p: unable to spawn jobq slave process
Wed Jul 13 14:55:36 2011
Errors in file e:\oracle\product\10.2.0\admin\sxserver\bdump\sxserver_cjq0_3248.trc:

Wed Jul 13 14:56:36 2011
Errors in file e:\oracle\product\10.2.0\admin\sxserver\bdump\sxserver_j001_1652.trc:
ORA-12012: error on auto execute of job 1
ORA-04030: out of process memory when trying to allocate 8148 bytes (kxs-heap-w,qesaQBInit:buffer)

Wed Jul 13 14:56:36 2011
Errors in file e:\oracle\product\10.2.0\admin\sxserver\bdump\sxserver_j001_1652.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-04030: out of process memory when trying to allocate 44 bytes (kxs-heap-d,allocator state)
ORA-12012: error on auto execute of job 1
ORA-04030: out of process memory when trying to allocate 8148 bytes (kxs-heap-w,qesaQBInit:buffer)

Wed Jul 13 14:59:26 2011
Errors in file e:\oracle\product\10.2.0\admin\sxserver\bdump\sxserver_j000_876.trc:
ORA-12012: 自動執行作業 1 出錯
ORA-04030: 在嘗試分配 16428 位元組 (pga heap,kgh stack) 時進程記憶體不足

Wed Jul 13 14:59:26 2011
Errors in file e:\oracle\product\10.2.0\admin\sxserver\bdump\sxserver_j000_876.trc:
ORA-00604: 遞迴 SQL 層級 1 出現錯誤
ORA-04030: 在嘗試分配 16092 位元組 (pga heap,kgh stack) 時進程記憶體不足
ORA-12012: 自動執行作業 1 出錯
ORA-04030: 在嘗試分配 16428 位元組 (pga heap,kgh stack) 時進程記憶體不足

Wed Jul 13 15:00:45 2011
Process startup failed, error stack:
Wed Jul 13 15:00:45 2011
Errors in file e:\oracle\product\10.2.0\admin\sxserver\bdump\sxserver_psp0_2984.trc:
ORA-27300: OS system dependent operation:spcdr:9261:4200 failed with status: 997
ORA-27301: OS failure message: 重疊 I/O 操作在進行中。
ORA-27302: failure occurred at: skgpspawn

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

據說這是一個oracle的BUG，你可以查下官方，應該可以找到的：

下面是解決辦法：

ORA-27300: OS 系統相關操作: spcdr:9261:4200 失敗, 狀態為: 997
要命的Oracle bug ，耗盡記憶體，導致資料庫掛掉
這個問題用Oracle10.2.0.1.0版本安裝在32位的Windows系統上，非常常見。隨便在google就找到很多。
通常會導致系統耗盡記憶體，導致Oracle資料庫掛掉。
把Oracle資料庫重啟可以臨時解決，但過幾天還會發生ORA-04030記憶體耗盡的情況。最好還是打上補丁。

串連資料庫時出現，
ORA-12518: TNS: 監聽程式無法分發客戶機串連

1、用sysdba串連不是資料庫，資料庫已經掛了

2、alert_sid.log
Sun Apr 19 10:05:56 2009
Process startup failed, error stack:
Sun Apr 19 10:05:56 2009
Errors in file e:\oracle\product\10.2.0\admin\xboms\bdump\xboms_psp0_3124.trc:
ORA-27300: OS 系統相關操作: spcdr:9261:4200 失敗, 狀態為: 997
ORA-27301: OS 故障訊息: 重疊 I/O 操作在進行中。
ORA-27302: 錯誤發生在: skgpspawn

由此導致頻繁的報下面的錯誤：
Sun Apr 19 10:24:08 2009
Errors in file e:\oracle\product\10.2.0\admin\xboms\bdump\xboms_arc0_3032.trc:
ORA-04030: 在嘗試分配 82444 位元組 (pga heap,control file i/o buffer) 時進程記憶體不足

查看：http://**/viewthread.php?tid=129216
and  http://www.itpub.net/thread-1146317-1-1.html

this is a bug ！
oracle承認這是一個bug（10.2.0.2.0之前10g版本），BUG 5607984 - ORACLE DOES NOT CLOSE TCP CONNECTIONS. REMAINS IN CLOSE_WAIT STATE. [On Windows 32-bit].
可打補丁patch number 5639232 ,該補丁有100多MB大。

在作業系統的boot.ini檔案中增加'/3G'，然後降低SGA。

 

1.在命令列開啟c:\boot.ini 檔案，在檔案的末尾加上 “/3GB”

如:c:\boot.ini檔案內容：

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003 Enterprise x64 Edition" /noexecute=optout /fastdetect /3GB

 

metalink上的解釋如下：

ChangesThe '/3GB'  switch was added to the  'boot.ini'  file at the OS level but the errors are eventually encountered when the database is under heavy load. CauseOS resource issue.
The maximum amount of addressable memory by a process ('oracle.exe' for example) running in a
32-Bit Windows environment has been reached.

In a 32-Bit Windows environment, the total addressable memory by a single process is a 4GB. On Windows, the OS reserves half of this memory by default for kernel memory, leaving 2GB of addressable memory for a user process. By placing the '/3GB' switch in the 'boot.ini'  file, this changes the ratio of kernel memory and user memory. When he  '/3GB'  switch is in place, processes (such as 'oracle.exe’) can address 3GB of virtual memory out of the total 4GB of addressable memory. However, once the 3GB of virtual memory is depleted, the process will fail. SolutionTo implement the solution, please execute the following steps:

Tune the application running on the 32-bit environment so that it will not consume greater than 2.7GB of virtual memory when the  '/3GB'  switch is set in the  'boot.ini'  file. In the case of Oracle encountering the OS resource issue, the options are:

1) Reduce the amount of SGA needed to be allocated for the database.
2) Limit the number of dedicated connections to the database and the amount of memory each user process will consume.
3) Change from dedicated connections to multi-threaded server (MTS) connections as MTS only uses a fraction of the amount of memory allocated to each user process when initial connection to the database is established.

See Metalink Note 225349.1 for an in depth discussion regarding memory usage in a 32-bit Windows environment

===========================

ORA-27300 ORA-27301 ORA-27302 in alert log. Cannot connect to database.
  文檔 ID:  注釋:371074.1 類型:  PROBLEM
  上次修訂日期:  30-JAN-2008 狀態:  MODERATED

In this Document
  Symptoms
  Changes
  Cause
  Solution
  References

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

2.降低SGA

SGA：資料緩衝、共用池、大池、Java池...
PGA:堆棧空間、使用者會話資料、遊標狀態...

 

修改SGA必須保持的原則：

1.sga_target不能大於sga_max_size，可以設定為相等。

2.SGA加上PGA等其他進程佔用的記憶體總數必須小於作業系統的實體記憶體。

做如下操作前，必須備份dbs目錄下的所有檔案.

方法一：直接SQL命令列修改：

SQL>alter system set sga_target=1400M scope=spfile;

 

方法二：通過EM修改：

以SYS登陸到EM：管理－>(資料庫配置)所有初始化參數—>SPFile－>
                在此介面可以直接定義sga_target與sga_max_size
然後重啟資料庫即可！