Aix Oracle 10.2.0.4 RAC ORA-00240: control file enqueue held for more than 120 seconds

https://odenysenko.wordpress.com/2009/06/25/asm-performance-with-huge-number-of-files/#more-11

    由於沒太多跡象顯示10g 上有類似的問題 , 從oracle support 看到11g 新增ASM空間有可能因為此BUG 造成DB hang 住 , 考量該bug , 目前先朝 Tuning ASM instance 方向處理.
    調整後 已正常運作一陣子.

後記與分析 :

發現redo log switch 時, archiver arc0 發生了 KSV master wait 事件 , 也意指

在 ASM file metadata operation 過程中 , 遇到該問題 , 這一段相關的記憶體為

ASM 的db_cache_size , 故需依照Oracle 建議將其放大

以下為IBM Tuning ASM 文件

http://www.filibeto.org/~aduritz/truetrue/oracle/aix/impl-oracle-10gRAC-asm-aix.pdf

Oracle Parameters

The following parameters are suggested in the document Oracle Database 10g Release 2

Automatic Storage Management Overview and Technical Best Practices:

Parameters to be included in the spfile for databases using ASM:

1. Increase Processes by 16

2. Increase Large_Pool by 600k

3. Increase Shared Pool by (1M per 100GB of usable space) +

2M (assumes external redundancy is chosen)

Parameters to be included in the spfile of the ASM instances:

1. ASM_POWER_LIMIT=1

The Best Practices guide suggests this value to make ASM rebalance operations a low

priority; however, this can potentially cause rebalance operations to occur during peak

volume times. This parameter can also be set to 0 to prevent accidental rebalance

operations from occurring during peak loads, which may be preferable for many

environments. This parameter can be raised specifically when rebalancing is desired.

2. Processes=25 + 15n, where “n” is the number of databases which will use ASM.

==>

tuning 建議計算如下:

vi /oracle/admin/+ASM/pfile

processes=70 (有三個instances , test , pdsms , RMAN , processes=25+ 15*3 =70 )

large_pool_size=60M

shared_pool_size=64M ( ASM total disk size : 6.2TB ==> shared pool : 62MB + 2MB=64MB)

ASM_POWER_LIMIT=1 ==> default value

以下為Oracle 原廠tuning 建議:

Oracle Database 11g Release 1 and 10g Release 2

Automatic Storage Management Overview and Technical Best Practices (ASM_11gR1_BestPractices_v34.pdf)

Processes = 25 + (10 + [max number of concurrent database file creations, and file extend operations possible] )*n

The number n is the number of databases connecting to ASM

processes= 25 + (10 + 80) * 2 =205 , 80 由datafiles 數量 加總 logfile 數量得來 , 假如你的環境只有10 個datafile 跟 6 個redo log , 可將80 替換為 16。

shared_pool_size=128M

large_pool_size=12M

db_cache_size=64M

[Oracle 10g] 監控ASM空間 , ASM mail scripts by checking its free percentage

Q:為何要設定此監控程式
A:根據監控free space , 才有辦法預先在資料庫空間快不夠時，去新增，否則等到滿了，肯定又是一場災難!

以下為Solaris bash shell 的範例

#content of mail_asm_alert.sh 

#export ORACLE_HOME to ASM_HOME , if installed

export DB=$(ps -ef |grep +ASM |grep -i pmon |awk {'print $9'} |sed -e 's/asm_pmon_//g')

export ORACLE_SID=${DB}

export ORAENV_ASK=NO

export SPOOL_FILE=/backup/rman/scripts/pct_free_asm.log;

ALERT=30

sqlplus / as sysdba <<EOF

spool $SPOOL_FILE

set heading off

--select round(total_mb/1024,2) Total_GB, round(free_mb/1024,2) Free_GB,round(free_mb/total_mb*100,2) pct_Free from v$asm_diskgroup where total_mb > 1;

select ''||NAME||' Pct '||round(free_mb/total_mb*100) pct_Free

from v\$asm_diskgroup where total_mb > 1;

spool off

EOF

#Check DATA diskgroup and REDO diskgroup...

asm_data_pct=`cat $SPOOL_FILE |grep "DATA"|grep -v SQL\> |awk '{print $3}'`

asm_redo_pct=`cat $SPOOL_FILE |grep "REDO"|grep -v SQL\> |awk '{print $3}'`

#Compare operator should be less then by default , ie asm_pct 20% , ALERT 30%

#Test the result by setting -lt to -gt below...

if [ -s $SPOOL_FILE ] && ( [ $asm_data_pct -gt $ALERT ] || [ $asm_redo_pct -gt $ALERT ] ) ; then

  echo "ASM DISKGROUP SHORT ON SPACE - PAGE SENT"

  cat $SPOOL_FILE | mailx -r dbadmin@domain.net -s "`hostname` ASM_DISK spact pct $asm_pct remain! " receiver@domain.net

fi

Mail 的結果如下:

Oracle 10g OBE

從Oracle 官網找了一些不錯的參考資料

Install data miner

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/prod/install/odminst/odminst_otn.htm

Managing Automatic Storage Management Disk Groups

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/prod/manage/asm/asm_otn.htm

Using Materialized Views and Query Rewrite Capabilities

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/prod/bidw/mv/mv_otn.htm

ASMCMD command

http://www.oracle.com/webfolder/technetwork/tutorials/demos/db/10g/r2/asm_xdb2.viewlet/asm_xdb2_viewlet_swf.html

Migrate a database to ASM

http://www.oracle.com/webfolder/technetwork/tutorials/demos/db/10g/r2/asm_migration.viewlet/asm_migration_viewlet_swf.html

Use Transparent Application Failover (TAF) with SELECT statements

http://www.oracle.com/webfolder/technetwork/tutorials/demos/db/10g/r2/rac_taf_select.viewlet/taf_select_viewlet_swf.html

RAC Instance Recovery Concepts

http://www.oracle.com/webfolder/technetwork/tutorials/demos/db/10g/r2/rac_inst_recov_concepts/instance_recovery_concepts_viewlet_swf.html


Improving RAC Database Tuning with ADDM for RAC

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/rac/rac_addm/11gr1_addm_rac4_r2update_viewlet_swf.html

Creating a DG Broker Configuration
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/ha/dataguard/dg_broker/dg_broker.htm

ASM 簡介

In brief , ASM 是Oracle 用來做RAC filesystem 管理用的，可對應到多個raw devices

其實就是一個管理raw device 的interface ，速度可以隨著raw device 數量增加而提升

效能，raw device優點就是io 十分快速，不需透過OS inodes 去對應存取disk blocks

但在檔案方面如果要做存取 或更改，也要在enterprise manager console 或透過oracle

XML DB，以ftp來存取virtual files.

ASM

http://www.oracle.com/technology/products/database/asm/index.html

ASM with EMC storage

http://www.oracle.com/technology/products/database/asm/pdf/asm-on-emc-5_3.pdf

EMC CLARiiON SnapView and MirrorView for Oracle Database 10g & ASM .

http://www.oracle.com/technology/products/database/asm/pdf/EMC%20CLARiiON%20SnapView%20and%20MirrorView%20for%20Oracle%20Database%2010g_6-26.pdf

ASM 10gR2 best practice

http://www.oracle.com/technology/products/database/asm/pdf/asm_10gr2_bestpractices%2009-07.pdf

Oracle 10g ASM tuning

參見附檔 Oracle Database 10g Release 2 Automatic Storage Management Overview and Technical Best Practices (p.7) :

ASM SGA and parameter sizing

將以下參數加到 /opt/oracle/db10g/dbs/init+ASM1.ora

Processes = 25 + (10 + [max number of concurrent database file creations, and file extend operations possible] )*n

            The number n is the number of databases connecting to ASM

For example          = 25 + (10 + 10 ) * 2 =65

i.e :

processes=65

shared_pool_size=128M

large_pool_size=12M

db_cache_size=64M

10g ASM asmcmd 指令

1.透過ASMCMD 存取 ASM disk group

可以透過Oracle asmcmd 此command 去存取ASlM diskgroup，並可刪除檔案

#su – oracle $export ORACLE_SID=+ASM1 $export ORACLE_HOME=/oracle/asm $asmcmd

ASMCMD> help         asmcmd [-p] [command]         The environment variables ORACLE_HOME and ORACLE_SID determine the         instance to which the program connects, and ASMCMD establishes a         bequeath connection to it, in the same manner as a SQLPLUS / AS         SYSDBA.  The user must be a member of the SYSDBA group.         Specifying the -p option allows the current directory to be displayed         in the command prompt, like so:         ASMCMD [+DATAFILE/ORCL/CONTROLFILE] >         [command] specifies one of the following commands, along with its         parameters.         Type "help [command]" to get help on a specific ASMCMD command.         commands:         --------         cd         du         find         help         ls         lsct         lsdg         mkalias         mkdir         pwd         rm         rmalias

ASMCMD> lsct

DB_Name   Status        Software_Version  Compatible_version  Instance_Name db     CONNECTED           10.2.0.4.0          10.2.0.3.0  db1

ASMCMD> lsdg

State    Type    Rebal  Unbal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB Usable_file_MB  Offline_disks  Name MOUNTED  EXTERN  N      N         512   4096  1048576    307185   301947        0          301947              0  DATA/

ASMCMD> mkalias usage: mkalias <filename> <alias>

檢查diskgroup 已佔用空間 ASMCMD> du

Used_MB      Mirror_used_MB    5142                5142

檢視filesystem

ASMCMD> ls DATA/ ASMCMD> cd DATA ASMCMD> ls DB_UNKNOWN/ ORCL/ ASMCMD> cd ORCL ASMCMD> ls ARCHIVELOG/ CONTROLFILE/ DATAFILE/ ONLINELOG/ TEMPFILE/ arch/ spfileorcl.ora

移除某檔案

ASMCMD> rm file_name

2.以ftp 方式連過去asm disk group

以ftp 經由XML DB 連至 diskgroup “+DATA”，port 為7787存取檔案，觀察其檔案結構

帳號 system

密碼 oracle

C:\Documents and Settings\Jui>ftp ftp> open 10.1.149.48 7787 Connected to 10.1.149.48. 220- db1 Unauthorised use of this FTP server is prohibited and may be subject to civil an d criminal prosecution. 220  db1 FTP Server (Oracle XML DB/Oracle Database) ready. User (10.1.149.48:(none)): system 331 pass required for SYSTEM Password: 230 SYSTEM logged in ftp> cd /sys/asm 250 CWD Command successful ftp> ls 200 PORT Command successful 150 ASCII Data Connection DATA 226 ASCII Transfer Complete ftp: 6 bytes received in 0.13Seconds 0.05Kbytes/sec. ftp> cd DATA 250 CWD Command successful ftp> ls 200 PORT Command successful 150 ASCII Data Connection ORCL DB_UNKNOWN 226 ASCII Transfer Complete ftp: 19 bytes received in 0.00Seconds 19000.00Kbytes/sec. ftp>disconnect

PS

另外還有Oracle 所附的兩個工具可以使用，管理方法跟一般oracle database 類似

1. Oracle Enterprise Manager(web)

https://ip_address:1158/em/

啟動enterprise manager:

#su – oracle

$emctl start dbconsole

檢查狀態

#su – oracle

$emctl status dbconsole

停止enterprise manager

#su - oracle

$emctl stop dbconsole

2. Oracle Enterprise Manager console