Windows 32bit OS上實體記憶體限制以及ORACLE 10G 在32bit OS的SGA記憶體分配限制解決方法

原來由於32位作業系統本身的問題，記憶體只能認到不到4GB，好在微軟加了一個什麼引數PAE，解決了這個記憶體限制

在微軟網站上看到的各個版本記憶體的最大擴充套件情況如下：
Physical Memory Limits: Windows Vista

The following table specifies the limits on physical memory for Windows Vista.

Version Limit in 32-bit Windows/Limit in 64-bit Windows
Windows Vista Ultimate 4 GB/ 128 GB

Windows Vista Enterprise 4 GB/128 GB


Windows Vista Business 4 GB/128 GB


Windows Vista Home Premium 4 GB/16 GB

Windows Vista Home Basic 4 GB/ 8 GB

Windows Vista Starter 1 GB / Not applicable

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

Physical Memory Limits: Windows Home Server
Windows Home Server is available only in a 32-bit edition. The physical memory limit is 4 GB.

--------------------------------------------------------------------------------
Physical Memory Limits: Windows Server 2003
The following table specifies the limits on physical memory for Windows Server 2003. Limits over 4 GB for 32-bit Windows assume that PAE is enabled.

Version Limit in 32-bit Windows /Limit in 64-bit Windows
Windows Server 2003 with Service Pack 2 (SP2), Datacenter Edition 128 GB/2TB
Windows Server 2003 with Service Pack 2 (SP2), Enterprise Edition 64 GB/2 TB
Windows Storage Server 2003, Enterprise Edition 8 GB/ Not applicable
Windows Storage Server 2003 4 GB/ Not applicable
Windows Server 2003 R2 Datacenter Edition
Windows Server 2003 with Service Pack 1 (SP1), Datacenter Edition 128 GB /16 GB with 4GT/ 1 TB
Windows Server 2003 with Service Pack 1 (SP1), Enterprise Edition 64 GB/16 GB with 4GT/1TB
Windows Server 2003 R2 Standard Edition
Windows Server 2003, Standard Edition SP1
Windows Server 2003, Standard Edition SP2 4GB/32GB
Windows Server 2003, Datacenter Edition 128 GB/16 GB with 4GT/512GB

Windows Server 2003, Enterprise Edition 32 GB/16 GB with 4GT/64GB

Windows Small Business Server 2003 4 GB
Not applicable

Windows Compute Cluster Server 2003 Not applicable
32 GB

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

Physical Memory Limits: Windows XP
The following table specifies the limits on physical memory for Windows XP.
Version Limit in 32-bit Windows Limit in 64-bit Windows
Windows XP 4 GB
128 GB
Windows XP Starter Edition 512 MB
Not applicable

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

Physical Memory Limits: Windows 2000
The following table specifies the limits on physical memory for Windows 2000.

Version Limit in 32-bit Windows
Windows 2000 Professional 4 GB

Windows 2000 Server 4 GB

Windows 2000 Advanced Server 8 GB

Windows 2000 Datacenter Server 32 GB

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

OS 是32bit 的，Oracle 資料庫 也只能安裝32bit 的，這樣了就SGA也受到限制，最大不能超過1.7G

看著那大把的記憶體，但資料庫用不上，怪可惜的。。透過下面的方法可以解開記憶體的限制/

- 在作業系統配置AWE,修改boot.ini檔案

右擊"我的電腦"，選擇"屬性"--&gt高階選項卡，找到“啟動與故障恢復”，單擊“設定”，然後單擊“編輯”，

加/PAE選項，修改後的檔案如下：
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /pae

- 透過修改登錄檔，變成oracle可以使用的記憶體，
“開始”- “執行” -“regedit"
HKEY_LOCAL_MACHINESOFTWAREORACLEKEY_oracle_home1，
新增新的strings：AWE_WINDOW_MEMORY ，這個值是準備分配給資料庫用的最大記憶體數(以BYTE為單位),例如你想分配3G,那這個值就是
3*1024*1024*1024
- 透過下面方法，修改引數讓ORACLE DB能使用上剛分配出來的MEM

Sqlplus "/ as sysdba" R
SQLPLUS>create pfile from spfile;
SQLPLUS>Shutdown immediate
SQLPLUS>exit

- 修改ORACLE pfile
註釋掉如下的引數：
sga_max_size
sga_target
DB_CACHE_SIZE
增加如下引數
修改或者新增以下引數
*.db_block_buffers=20000　 #２Ｇ　為例
*.SHARED_POOL_SIZE=400000000 # 400M　為例
*.log_buffer=73000000 # 70M
*.use_indirect_data_buffers=true

sqlplus "/as sysdba"
sql>create spfile from pfile
sql>startup

- 透過以上的方法就可以讓記憶體大大超過１.7GB了

但透過以上的方法修改後，可能在某些平臺上會出現ＢＵＧ，建議到ＭＥＴＡＬＩＮＫ檢視相關ＮＯＴＥ

參考Ｎotes:
Oracle Database and the Windows NT memory architecture, Technical Bulletin
Doc ID: 46001.1
Oracle Database Server and the Operating System Memory Limitations
Doc ID: 269495.1