在一個的測試數(shù)據(jù)庫上，創(chuàng)建表空間時出現(xiàn)了這個錯誤。

?

?

由于數(shù)據(jù)庫環(huán)境比較復(fù)雜，簡單描述一下。

這個測試環(huán)境安裝的是Oracle 1106 for Solaris 10 sparc 64bit的RAC環(huán)境，搭建了ASM實例用于存放共享數(shù)據(jù)文件。

在RAC環(huán)境的其中一個節(jié)點上，又建立了一個單實例的數(shù)據(jù)庫，并把這個數(shù)據(jù)庫的數(shù)據(jù)文件也放到了ASM實例上。

結(jié)果嘗試在這個實例上添加新的表空間時報錯：

SQL> select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------------------------------
+DATA/test/datafile/system.533.668281219
+DATA/test/datafile/sysaux.534.668281227
+DATA/test/datafile/undotbs1.535.668281229
+DATA/test/datafile/users.537.668281241

SQL> create tablespace test datafile '+DATA/test/datafile/test01.dbf' size 4096m;
create tablespace test datafile '+DATA/test/datafile/test01.dbf' size 4096m
*
第?1?行出現(xiàn)錯誤:
ORA-01119:?創(chuàng)建數(shù)據(jù)庫文件?'+DATA/test/datafile/test01.dbf'?時出錯
ORA-17502: ksfdcre: 4?未能創(chuàng)建文件?+DATA/test/datafile/test01.dbf
ORA-00569: Failed to acquire global enqueue.
ORA-00569: Failed to acquire global enqueue.

這個錯誤似乎很少見，查看了一下Oracle的官方錯誤文檔描述：

ORA-00569: Failed to acquire global enqueue.
Cause: A prior error occurred on one of the instances in the cluster. Typically errors are caused by shared pool resource contention.
Action: Check for and resolve prior errors on all instances in the cluster. If there is shared pool resource contention, increase the SHARED_POOL_SIZE, DML_ LOCKS, PROCESSES, TRANSACTIONS, CLUSTER_DATABASE_INSTANCES and PARALLEL_MAX_SERVERS initialization parameters.

雖然對問題進行了描述，不過從錯誤看不出導(dǎo)致問題的真正原因。

查詢了一下METALINK，找到了一些錯誤說明，不過沒有和當(dāng)前錯誤相似度很高的，大部分出現(xiàn)這個錯誤的同時，都會伴隨ORA-600錯誤和ORA-4031錯誤。

不過現(xiàn)在有一個簡單的方法來確定到底是數(shù)據(jù)庫產(chǎn)生的問題還是ASM實例導(dǎo)致的問題，現(xiàn)在只需要登陸RAC實例，執(zhí)行類似的添加表空間的操作，檢查是否會出現(xiàn)相同的問題就可以了：

bash-3.00$ export ORACLE_SID=ractest1
bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on?星期三?2月?18 17:12:42 2009

Copyright (c) 1982, 2007, Oracle.? All rights reserved.

已連接到空閑例程。

SQL> startup
ORACLE?例程已經(jīng)啟動。

Total System Global Area 1603887104 bytes
Fixed Size????????????????? 2095208 bytes
Variable Size???????????? 741722008 bytes
Database Buffers????????? 855638016 bytes
Redo Buffers??????????????? 4431872 bytes
數(shù)據(jù)庫裝載完畢。
數(shù)據(jù)庫已經(jīng)打開。
SQL> CREATE TABLESPACE TEST DATAFILE '+DATA/ractest/datafile/test01.dbf' SIZE 4096M;
CREATE TABLESPACE TEST DATAFILE '+DATA/ractest/datafile/test01.dbf' SIZE 4096M
*
第?1?行出現(xiàn)錯誤:
ORA-01119:?創(chuàng)建數(shù)據(jù)庫文件?'+DATA/ractest/datafile/test01.dbf'?時出錯
ORA-17502: ksfdcre: 4?未能創(chuàng)建文件?+DATA/ractest/datafile/test01.dbf
ORA-00569: Failed to acquire global enqueue.
ORA-00569: Failed to acquire global enqueue.

可以看到，相同的錯誤產(chǎn)生了，看來問題可能和ASM實例的狀態(tài)有關(guān)系，登陸ASM實例，進行簡單的檢查：

bash-3.00$ export ORACLE_SID=+ASM1
bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on?星期三?2月?18 17:33:12 2009

Copyright (c) 1982, 2007, Oracle.? All rights reserved.

連接到:?
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> set pages 100 lines 120
SQL> select instance_name, status from v$instance;

INSTANCE_NAME??????????????????? STATUS
-------------------------------- ------------------------
+ASM1??????????????????????????? STARTED

由于ASM實例可以用來檢查的動態(tài)視圖太少，從現(xiàn)有的視圖也看不到特別的地方，看來只能重啟數(shù)據(jù)庫和ASM實例，再次檢查問題：

bash-3.00$ export ORACLE_SID=test
bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on?星期三?2月?18 17:41:40 2009

Copyright (c) 1982, 2007, Oracle.? All rights reserved.

連接到:?
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> shutdown immediate
數(shù)據(jù)庫已經(jīng)關(guān)閉。
已經(jīng)卸載數(shù)據(jù)庫。
ORACLE?例程已經(jīng)關(guān)閉。
SQL> exit
bash-3.00$ export ORACLE_SID=ractest1
bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on?星期三?2月?18 17:43:21 2009

Copyright (c) 1982, 2007, Oracle.? All rights reserved.

連接到:?
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> shutdown immediate
數(shù)據(jù)庫已經(jīng)關(guān)閉。
已經(jīng)卸載數(shù)據(jù)庫。
ORACLE?例程已經(jīng)關(guān)閉。
SQL> exit
從?Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options?斷開
bash-3.00$ export ORACLE_SID=+ASM1???
bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on?星期三?2月?18 17:44:05 2009

Copyright (c) 1982, 2007, Oracle.? All rights reserved.

連接到:?
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> shutdown immediate
^CORA-01013: user requested cancel of current operation

SQL> CONN / AS SYSDBA
已連接。
SQL> shutdown abort
ASM?實例已關(guān)閉
SQL> startup
ASM?實例已啟動

Total System Global Area? 284008448 bytes
Fixed Size????????????????? 2087944 bytes
Variable Size???????????? 256754680 bytes
ASM Cache????????????????? 25165824 bytes
ASM diskgroups mounted
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
bash-3.00$ export ORACLE_SID=test?
bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on?星期三?2月?18 17:47:22 2009

Copyright (c) 1982, 2007, Oracle.? All rights reserved.

已連接到空閑例程。

SQL> startup
ORACLE?例程已經(jīng)啟動。

Total System Global Area? 806133760 bytes
Fixed Size????????????????? 2099064 bytes
Variable Size???????????? 455669896 bytes
Database Buffers????????? 343932928 bytes
Redo Buffers??????????????? 4431872 bytes
數(shù)據(jù)庫裝載完畢。
數(shù)據(jù)庫已經(jīng)打開。
SQL> create tablespace test datafile '+DATA/test/datafile/test01.dbf' size 4096m;
create tablespace test datafile '+DATA/test/datafile/test01.dbf' size 4096m
*
第?1?行出現(xiàn)錯誤:
ORA-01119:?創(chuàng)建數(shù)據(jù)庫文件?'+DATA/test/datafile/test01.dbf'?時出錯
ORA-17502: ksfdcre: 4?未能創(chuàng)建文件?+DATA/test/datafile/test01.dbf
ORA-00569: Failed to acquire global enqueue.
ORA-00569: Failed to acquire global enqueue.

可以看到，重啟ASM實例，問題仍然出現(xiàn)。不過ASM實例也是在兩個節(jié)點上同時運行的，莫非是另一個節(jié)點的ASM實例出現(xiàn)了問題：

bash-3.00$ export ORACLE_SID=+ASM2
bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on?星期四?2月?19 16:38:38 2009

Copyright (c) 1982, 2007, Oracle.? All rights reserved.

連接到:?
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> set pages 100 lines 120
SQL> select instance_name, status from v$instance;

INSTANCE_NAME??????????????????? STATUS
-------------------------------- ------------------------
+ASM2??????????????????????????? STARTED

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

檢查ASM實例未發(fā)現(xiàn)異常，嘗試重啟ASM實例：

bash-3.00$ srvctl stop instance -d ractest -i ractest2
bash-3.00$ srvctl stop asm -n ser2
bash-3.00$ srvctl start asm -n ser2

再次登陸test數(shù)據(jù)庫，執(zhí)行CREATE TABLESPACE語句：

bash-3.00$ export ORACLE_SID=test
bash-3.00$ sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on?星期四?2月?19 16:41:09 2009

Copyright (c) 1982, 2007, Oracle.? All rights reserved.

連接到:?
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> set pages 100 lines 120
SQL> create tablespace test datafile '+DATA/test/datafile/test01.dbf' size 4096m;

表空間已創(chuàng)建。

看來問題果然和ASM實例狀態(tài)不正常有關(guān)。

檢查asm實例2的alert文件，發(fā)現(xiàn)在運行CREATE TABLESPACE語句對應(yīng)的時間點，出現(xiàn)了ORA-4031錯誤：

Wed Feb 18 15:50:04 2009
Errors in file /data/oracle/diag/asm/+asm/+ASM2/trace/+ASM2_lmd0_3099.trc? (incident=2412):
ORA-04031: unable to allocate 3512 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","ges enqueues")
Incident details in: /data/oracle/diag/asm/+asm/+ASM2/incident/incdir_2412/+ASM2_lmd0_3099_i2412.trc
Wed Feb 18 15:50:05 2009
Trace dumping is performing id=[cdmp_20090218155005]
WARNING: ran out of shared pool for GES enqueue object.
Errors in file /data/oracle/diag/asm/+asm/+ASM2/trace/+ASM2_lmd0_3099.trc? (incident=2413):
ORA-04031: unable to allocate 3512 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","ges enqueues")
Incident details in: /data/oracle/diag/asm/+asm/+ASM2/incident/incdir_2413/+ASM2_lmd0_3099_i2413.trc
Trace dumping is performing id=[cdmp_20090218155013]
Wed Feb 18 17:17:23 2009
Errors in file /data/oracle/diag/asm/+asm/+ASM2/trace/+ASM2_lmd0_3099.trc? (incident=2414):
ORA-04031: unable to allocate 3512 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","ges enqueues")
Incident details in: /data/oracle/diag/asm/+asm/+ASM2/incident/incdir_2414/+ASM2_lmd0_3099_i2414.trc
WARNING: ran out of shared pool for GES enqueue object.
Wed Feb 18 17:17:24 2009
Trace dumping is performing id=[cdmp_20090218171724]
Errors in file /data/oracle/diag/asm/+asm/+ASM2/trace/+ASM2_lmd0_3099.trc? (incident=2415):
ORA-04031: unable to allocate 3512 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","ges enqueues")
Incident details in: /data/oracle/diag/asm/+asm/+ASM2/incident/incdir_2415/+ASM2_lmd0_3099_i2415.trc
Trace dumping is performing id=[cdmp_20090218171732]
Wed Feb 18 17:18:02 2009
Errors in file /data/oracle/diag/asm/+asm/+ASM2/trace/+ASM2_lmd0_3099.trc? (incident=2416):
ORA-04031: unable to allocate 3512 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","ges enqueues")
Incident details in: /data/oracle/diag/asm/+asm/+ASM2/incident/incdir_2416/+ASM2_lmd0_3099_i2416.trc
WARNING: ran out of shared pool for GES enqueue object.
Wed Feb 18 17:18:03 2009
Trace dumping is performing id=[cdmp_20090218171803]
Errors in file /data/oracle/diag/asm/+asm/+ASM2/trace/+ASM2_lmd0_3099.trc? (incident=2417):
ORA-04031: unable to allocate 3512 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","ges enqueues")
Incident details in: /data/oracle/diag/asm/+asm/+ASM2/incident/incdir_2417/+ASM2_lmd0_3099_i2417.trc
Trace dumping is performing id=[cdmp_20090218171811]

這個ORA-4031錯誤已經(jīng)和Oracle的metalink文章相符了，和Oracle錯誤文檔上對這個錯誤的描述也是一致的。

而且這個ORA-4031錯誤信息也很明顯，在分配全局對了資源的時候出現(xiàn)的錯誤。

檢查ASM實例的sga，發(fā)現(xiàn)：

SQL> show sga

Total System Global Area? 284008448 bytes
Fixed Size????????????????? 2087944 bytes
Variable Size???????????? 256754680 bytes
ASM Cache????????????????? 25165824 bytes

對于跑了多個RAC環(huán)境的ASM實例而言，200M的SGA顯然太小了，和大部分Oracle默認參數(shù)一樣，默認的ASM實例參數(shù)也是偏小的。

以前也碰到過一次由于ASM實例PROCESS參數(shù)太小，導(dǎo)致ASM實例無法登陸的問題。因此，如果選擇ASM作為產(chǎn)品庫的存儲方式，那么ASM實例要重新設(shè)置，默認的參數(shù)很可能無法滿足需要。

?

總結(jié)

以上是生活随笔為你收集整理的RAC环境在ASM上创建表空间出错ORA-569的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。