久久国产成人av_抖音国产毛片_a片网站免费观看_A片无码播放手机在线观看,色五月在线观看,亚洲精品m在线观看,女人自慰的免费网址,悠悠在线观看精品视频,一级日本片免费的,亚洲精品久,国产精品成人久久久久久久

分享

與時(shí)俱進(jìn):ASM內(nèi)存管理與創(chuàng)建表空間之ORA-569錯(cuò)誤解決

 數(shù)據(jù)和云 2020-07-01

楊廷琨(yangtingkun)

云和恩墨 CTO

高級(jí)咨詢顧問,,Oracle ACE總監(jiān),,ITPUB Oracle數(shù)據(jù)庫(kù)管理版版主

在一個(gè)測(cè)試數(shù)據(jù)庫(kù)上創(chuàng)建表空間出現(xiàn)了ORA-569錯(cuò)誤。由于環(huán)境比較復(fù)雜,,首先簡(jiǎn)單描述一下數(shù)據(jù)庫(kù)環(huán)境信息,。這個(gè)測(cè)試環(huán)境安裝的是Oracle 11g for Solaris 10 sparc 64bit的RAC環(huán)境,使用ASM作為共享數(shù)據(jù)文件的存儲(chǔ)機(jī)制,。

在RAC環(huán)境的一個(gè)節(jié)點(diǎn)上還建立了一個(gè)單實(shí)例的數(shù)據(jù)庫(kù),,并把這個(gè)數(shù)據(jù)庫(kù)的數(shù)據(jù)文件也放到了ASM實(shí)例上。

在這個(gè)單實(shí)例數(shù)據(jù)庫(kù)上添加新的表空間時(shí)報(bào)錯(cuò),,代碼如下:

SQL> select file_namefrom 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 tablespacetest datafile '+DATA/test/datafile/test01.dbf' size 4096m;

create tablespace testdatafile '+DATA/test/datafile/test01.dbf' size 4096m

*

第 1 行出現(xiàn)錯(cuò)誤:

ORA-01119: 創(chuàng)建數(shù)據(jù)庫(kù)文件'+DATA/test/datafile/test01.dbf' 時(shí)出錯(cuò)

ORA-17502: ksfdcre: 4未能創(chuàng)建文件+DATA/test/datafile/test01.dbf

ORA-00569: Failed to acquire global enqueue.

這個(gè)錯(cuò)誤很少見,,查了一下Oracle的官方報(bào)錯(cuò)文檔的描述:

ORA-00569: Failed toacquire 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.

文檔雖然對(duì)問題進(jìn)行了描述,不過從對(duì)錯(cuò)誤信息和問題描述中看不出導(dǎo)致問題的真正原因,。

查詢了一下MOS,,從中找到了一些關(guān)于ORA-569的錯(cuò)誤說明。不過這些問題都和當(dāng)前錯(cuò)誤有很大的不同:大部分出現(xiàn)這個(gè)錯(cuò)誤的同時(shí)都會(huì)伴隨ORA-600錯(cuò)誤和ORA-4031錯(cuò)誤,。

看來借助Metalink解決這個(gè)問題的希望落空,,只能自己想辦法了。

前面已經(jīng)提到當(dāng)前環(huán)境比較復(fù)雜,,這個(gè)節(jié)點(diǎn)上啟動(dòng)了兩個(gè)實(shí)例,。一個(gè)是單實(shí)例的數(shù)據(jù)庫(kù),另一個(gè)是RAC數(shù)據(jù)庫(kù)中的一個(gè)節(jié)點(diǎn),,而且兩個(gè)數(shù)據(jù)庫(kù)的數(shù)據(jù)文件都存儲(chǔ)在相同的ASM磁盤組中,。

問題都有兩面性,環(huán)境復(fù)雜也有復(fù)雜的好處,,現(xiàn)在有一個(gè)簡(jiǎn)單的方法可以確定到底是數(shù)據(jù)庫(kù)產(chǎn)生的問題還是ASM實(shí)例導(dǎo)致的問題:只需要登錄RAC實(shí)例,,執(zhí)行類似的添加表空間的操作,,就可檢查是否會(huì)出現(xiàn)相同的錯(cuò)誤。

bash-3.00$ export ORACLE_SID=ractest1

bash-3.00$ sqlplus "/as sysdba"

已連接到空閑例程,。

SQL> startup

ORACLE 例程已經(jīng)啟動(dòng),。

Total System Global Area      1603887104   bytes

數(shù)據(jù)庫(kù)裝載完畢。

數(shù)據(jù)庫(kù)已經(jīng)打開,。

SQL> CREATE TABLESPACETEST DATAFILE '+DATA/ractest/datafile/test01.dbf' SIZE 4096M;

CREATE TABLESPACE TESTDATAFILE '+DATA/ractest/datafile/test01.dbf' SIZE 4096M

*

第 1 行出現(xiàn)錯(cuò)誤:

ORA-01119: 創(chuàng)建數(shù)據(jù)庫(kù)文件'+DATA/ractest/datafile/test01.dbf' 時(shí)出錯(cuò)

ORA-17502: ksfdcre: 4未能創(chuàng)建文件+DATA/ractest/datafile/test01.dbf

ORA-00569: Failed to acquire global enqueue.

相同的錯(cuò)誤產(chǎn)生了,說明問題多半與ASM實(shí)例的狀態(tài)有關(guān)系,。登錄ASM實(shí)例進(jìn)行簡(jiǎn)單的檢查:

bash-3.00$ export ORACLE_SID=+ASM1

bash-3.00$ sqlplus "/as sysdba"

SQL> set pages 100 lines 120

SQL> select instance_name,status from v$instance;

INSTANCE_NAME                         STATUS

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

+ASM1                                      STARTED

由于ASM實(shí)例可以用來檢查的動(dòng)態(tài)視圖太少了,,從現(xiàn)有的視圖也看不到什么特別的地方。實(shí)在沒有什么太好的查錯(cuò)辦法,,只能重啟數(shù)據(jù)庫(kù)和ASM實(shí)例,,再次檢查問題:

bash-3.00$ export ORACLE_SID=test

bash-3.00$ sqlplus "/as sysdba"

SQL> shutdown immediate

SQL> exit

bash-3.00$ export ORACLE_SID=ractest1

SQL> shutdown immediate

SQL> exit

bash-3.00$ export ORACLE_SID=+ASM1  

bash-3.00$ sqlplus "/as sysdba"

SQL> shutdown immediate

^CORA-01013: user requestedcancel of current operation

SQL> CONN / AS SYSDBA

已連接。

SQL> shutdown abort

ASM 實(shí)例已關(guān)閉

SQL> startup

ASM 實(shí)例已啟動(dòng)

Total System Global Area       284008448   bytes

ASM diskgroups mounted

bash-3.00$ export ORACLE_SID=test

bash-3.00$ sqlplus "/as sysdba"

SQL> startup

ORACLE 例程已經(jīng)啟動(dòng),。

數(shù)據(jù)庫(kù)裝載完畢,。

數(shù)據(jù)庫(kù)已經(jīng)打開。

SQL> create tablespacetest datafile '+DATA/test/datafile/test01.dbf' size 4096m;

create tablespace testdatafile '+DATA/test/datafile/test01.dbf' size 4096m

*

第 1 行出現(xiàn)錯(cuò)誤:

ORA-01119: 創(chuàng)建數(shù)據(jù)庫(kù)文件'+DATA/test/datafile/test01.dbf' 時(shí)出錯(cuò)

ORA-17502: ksfdcre: 4未能創(chuàng)建文件+DATA/test/datafile/test01.dbf

ORA-00569: Failed toacquire global enqueue.

可以看到重啟ASM實(shí)例后問題仍然出現(xiàn),。不過ASM實(shí)例也是在兩個(gè)節(jié)點(diǎn)上同時(shí)運(yùn)行的,,莫非是在另一個(gè)節(jié)點(diǎn)的ASM實(shí)例出現(xiàn)了問題:

bash-3.00$ export ORACLE_SID=+ASM2

bash-3.00$ sqlplus "/as sysdba"

SQL> set pages 100 lines 120

SQL> select instance_name,status from v$instance;

INSTANCE_NAME                         STATUS

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

+ASM2                                      STARTED

檢查ASM實(shí)例未發(fā)現(xiàn)異常,嘗試重啟ASM實(shí)例:

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ù)庫(kù),,執(zhí)行CREATE TABLESPACE語(yǔ)句:

bash-3.00$ export ORACLE_SID=test
bash-3.00$ sqlplus "/as sysdba"
SQL> set pages 100 lines 120
SQL> create tablespace test datafile '+DATA/test/datafile/test01.dbf' size 4096m;
表空間已創(chuàng)建,。

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

檢查ASM實(shí)例2的alert文件,,發(fā)現(xiàn)在運(yùn)行CREATE TABLESPACE語(yǔ)句對(duì)應(yīng)的時(shí)間點(diǎn)出現(xiàn)了ORA-4031錯(cuò)誤:

Errors in file /data/oracle/diag/asm/+asm/+ASM2/trace/+ASM2_lmd0_3099.trc  (incident=2412):

ORA-04031: unable toallocate 3512 bytes of shared memory ("shared pool","unknown object","sgaheap(1,0)","ges enqueues")

Incident details in:/data/oracle/diag/asm/+asm/+ASM2/incident/incdir_2412/+ASM2_lmd0_3099_i2412.trc

Trace dumping is performingid=[cdmp_20090218155005]

WARNING: ran out of sharedpool for GES enqueue object.

Errors in file /data/oracle/diag/asm/+asm/+ASM2/trace/+ASM2_lmd0_3099.trc  (incident=2413):

ORA-04031: unable toallocate 3512 bytes of shared memory ("shared pool","unknown object","sgaheap(1,0)","ges enqueues")

Incident details in:/data/oracle/diag/asm/+asm/+ASM2/incident/incdir_2413/+ASM2_lmd0_3099_i2413.trc

Trace dumping is performingid=[cdmp_20090218155013]

前面ORA-569報(bào)錯(cuò)再加上這個(gè)ORA-4031報(bào)錯(cuò),,現(xiàn)在已經(jīng)和Metalink里面的問題描述一致了,而且這個(gè)ORA-4031報(bào)錯(cuò)信息也很明顯,,問題在于分配全局隊(duì)列資源時(shí)出現(xiàn)了錯(cuò)誤,。

檢查ASM實(shí)例的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

對(duì)于同時(shí)支持多個(gè)數(shù)據(jù)庫(kù)實(shí)例的ASM實(shí)例而言,,200MB的SGA顯然太小了,,和大部分其他Oracle默認(rèn)參數(shù)一樣,默認(rèn)的ASM實(shí)例參數(shù)也是偏小的,。

前面的文中還描述過:由于ASM實(shí)例的PROCESS參數(shù)太小導(dǎo)致ASM實(shí)例無法登錄的問題,。因此在選擇ASM作為產(chǎn)品數(shù)據(jù)庫(kù)的存儲(chǔ)方式時(shí),就要求ASM實(shí)例在建立時(shí)就要仔細(xì)地設(shè)置,,很多的默認(rèn)參數(shù)須要調(diào)整后才能滿足正式環(huán)境的需要,,使用一項(xiàng)技術(shù),就要尊重一項(xiàng)技術(shù),。

在MOS后來補(bǔ)充的以下文檔上,,明確提出了關(guān)于ASM的SGA配置建議:

Things to Consider Before Upgrading to 11.2.0.3 Grid Infrastructure/ASM [ID 1363369.1]

其中指出:自11.2.0.3開始,,由于初始化參數(shù)Processes的缺省值上升為“CPU Cores * 80 +40”,缺省的MEMORY_TARGET參數(shù)可能不足夠,,尤其是當(dāng)CPU cores較多或者ASM磁盤組較多時(shí),,可能會(huì)導(dǎo)致ORA-4031錯(cuò)誤,推薦增加ASM的MEMORY_TARGET至少到1536M,。原文引用如下:

ASM memory_max_target and memory_target

In 11.2.0.3, init.ora parameter "processes" will be default to "available CPU cores * 80 + 40". As the default value for "memory_target" is based on "processes", it can be insufficient if there's large number of CPU cores or large number of diskgroups which could cause issues (i.e. GI stack fails to stop with ORA-04031 etc), it's recommended to increase the value of memory_max_target and memory_target before upgrading to 11.2.0.3(does not apply to 10g ASM):

Log in to ASM:

SQL> show parameter memory_target

If the value is smaller than 1536m, issue the following:

SQL> alter system set memory_max_target=4096m scope=spfile;
SQL> alter system set memory_target=1536m scope=spfile;

The number 1536m has proven to be sufficient for most environment, the change will not be effective until next restart.

此時(shí)我們?cè)倩仡^看看ORA-00569錯(cuò)誤的提示:

ORA-00569: Failed toacquire 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.

這里已經(jīng)提到的ORA-00569典型的是由于Shared Pool資源競(jìng)爭(zhēng)導(dǎo)致的,,往往山重水復(fù)之后,我們看到柳暗花明的還是最初的起點(diǎn),,很多學(xué)習(xí)的過程,,就是如此。

如何加入"云和恩墨大講堂"微信群

    轉(zhuǎn)藏 分享 獻(xiàn)花(0

    0條評(píng)論

    發(fā)表

    請(qǐng)遵守用戶 評(píng)論公約

    類似文章 更多