【Bug】Bug 11827204: NON-OSDBA USERS WITH LOCAL CONNECTIONS: ORA-15025 ORA-27041

楊奇龍發表於2011-10-11
資料庫版本：Release 11.2.0.2.0
以非 oracle 使用者執行sqlpuls 登入資料庫（ps admin 使用者是oracle 建立的外部使用者，直接執行sqlpus / 便可以登入資料庫）
admin@sf1:/home/admin>sqlplus /
SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 11 10:06:31 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
期間 介面hang住 ，中斷連線

Last login: Sat Oct  8 14:11:44 2011 from 10.1.165.6
重新登入後，再次執行sqlplus / 遇到如下錯誤！
admin@sf1:/opt/11202/oracle/products/11.2.0/bin>sqlplus /
SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 11 10:15:12 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-09945: Unable to initialize the audit trail file
Linux-x86_64 Error: 28: No space left on device
ORA-01013: user requested cancel of current operation

admin@sf1:/opt/11202/oracle/products/11.2.0/bin>df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              19G  6.3G   12G  35% /
/dev/sda6             1.1T  111G  875G  12% /home
/dev/sda3              19G   18G     0 100% /opt --系統空間短時間內爆滿！
原因 觸發oracle 的一個bug：
Bug 11827204: NON-OSDBA USERS WITH LOCAL CONNECTIONS: ORA-15025 ORA-27041
=== ODM Issue Clarification ===
PreProduction: non-RAC - 112020 (asm) - 112020 (db) - Linux x86-64 
(2.6.18-164.15.1.el5) - No asmlib
Problem occurs when making a sqlplus local connection from a unix id other 
than the oracle id, ie with a personal or application unix id.
當使用非oracle 使用者組成員登入資料庫時，會觸發此bug！
------------------------------------------------------------------------
$ . oraenv
ORACLE_SID = [PRLMCD01] ? PRLMCD01
ORACLE_BASE environment variable is not being set since this
information is not available for the current user ID llavoie.
You can set ORACLE_BASE manually if it is required.
$ sqlplus ismdba@PRLMCD01
 SQL*Plus: Release 11.2.0.2.0 Production on Fri Feb 18 15:04:10 2011
 Copyright (c) 1982, 2010, Oracle. All rights reserved.
------------------------------------------------------------------------

At this point it hangs. Checking the alert log show continuous stream of 
error messages:
登入介面hang住，大量如下錯誤寫入alertlog 日誌檔案中和trace 檔案中！
ORA-15025: could not open disk "/dev/mapper/asm-pn02"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
最終會導致系統空間被上述的錯誤資訊使用的空間佔用，導致資料庫hang住！
如何暫時的解決：Problem can be stopped by killing the LOCAL process.
當非oracle 使用者進行操作時可以使用TNS名來訪問資料庫！如：
sqlplus user/pwd@TNSNAME 
時，並不會傳送這樣的錯誤！
The /dev/mapper/asm-* devices are under asmdba group
Additional information:
~~~~~~~~~~~~~~~~~~~~~~~~~
* Job role separation:
  asm=oraasm oinstall,asmdba,asmadmin,asmoper,dbalmdb
  db=oralmdb oinstall,ddba to these users. However the asm disks are also 
partbalmdb,asmdba
* Workaround: Grant asm
  of this group, so they do not feel safe with this WA
* file system is 20Gb .. usually 14Gb can be used by the alert log
* This was never a 112010 and is their first ever ASM implementation.
* Pre-production. Go-live is scheduled for end of March.
DIAGNOSTIC ANALYSIS:
--------------------
* OS job separation is proper.
* Non-OSDBA users should be granted access to the database.
WORKAROUND:
-----------
* Grant asmdba to these users. However the asm disks are also part of this 
  group, so they do not feel safe with this WA
bug 相關屬性：
型別 B - Defect 已在產品版本中修復 -
嚴重性 2 - Severe Loss of Service 產品版本 11.2.0.2.0
狀態 33 - Suspended, Req'd Info not Avail 平臺 226 - Linux x86-64
建立時間 02-Mar-2011 平臺版本 NO DATA
更新時間 22-Jul-2011 基本 Bug -
資料庫版本 11.2.0.2.0
影響平臺 Generic
產品源 Oracle
####敬畏流程，正確做事####

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-708994/，如需轉載，請註明出處，否則將追究法律責任。

相關文章