Oracle Study之案例--Oracle Sqlplus錯誤

Oracle Study之案例--Oracle Sqlplus錯誤

系統(tǒng)環(huán)境：

操作系統(tǒng)：AIX5.3

數(shù)據(jù)庫：  Oracle 10gR2

    案例分析：

1、查看空間信息
[oracle@aix220 ~]$df -m
Filesystem    MB blocks      Free %Used    Iused %Iused Mounted on
/dev/hd4       17408.00   1238.15   93%    37699    12% /
/dev/hd2        8192.00   6310.39   23%    46534     4% /usr
/dev/hd9var     2048.00   1725.55   16%     1704     1% /var
/dev/hd3        2048.00   1902.58    8%      420     1% /tmp
/dev/fwdump     3072.00   3071.21    1%        4     1% /var/adm/ras/platform
/dev/hd1        2048.00   1821.98   12%       74     1% /home
/proc                 -         -    -         -     -  /proc
/dev/hd10opt    2048.00   1374.77   33%     8934     3% /opt
/dev/lv00       1024.00    991.80    4%       18     1% /var/adm/csd
/dev/arch      10240.00   8347.63   19%  
/dev/lv02      15360.00  10097.80   35%   123693     4% /u01

2、查看文件權(quán)限
[oracle@aix220 ~]$ls -ld /u01/app/oracle/admin/master/
drwxr-x---    8 oracle   dba             512 Feb 17 16:40 /u01/app/oracle/admin/master/
[oracle@aix220 ~]$ls -ld /u01/app/oracle/admin/master/adump/
drwxr-x---    2 oracle   dba            1536 Feb 17 19:48 /u01/app/oracle/admin/master/adump/

3、查看文件系統(tǒng)信息
[oracle@aix220 dbs]$cat /etc/filesystems

/u01:
        dev             = /dev/lv02
        vfs             = jfs
        log             = /dev/loglv00
        mount           = true
        options         = rw
        account         = false

[root@aix220 /]#lsvg -l oraclevg
oraclevg:
LV NAME             TYPE       LPs     PPs     PVs  LV STATE      MOUNT POINT
loglv00             jfslog     1       1       1    closed/syncd  N/A
lv02                jfs        120     120     1    closed/syncd  /u01

[root@aix220 /]#df -m
Filesystem    MB blocks      Free %Used    Iused %Iused Mounted on
/dev/hd4       17408.00   1238.18   93%    37700    12% /
/dev/hd2        8192.00   6310.39   23%    46534     4% /usr
/dev/hd9var     2048.00   1725.56   16%     1704     1% /var
/dev/hd3        2048.00   1902.58    8%      420     1% /tmp
/dev/fwdump     3072.00   3071.21    1%        4     1% /var/adm/ras/platform
/dev/hd1        2048.00   1821.98   12%       74     1% /home
/proc                 -         -    -         -     -  /proc
/dev/hd10opt    2048.00   1374.77   33%     8934     3% /opt
/dev/lv00       1024.00    991.80    4%       18     1% /var/adm/csd
/dev/arch      10240.00   8347.63   19%       52     1% /arch
/dev/lv01       5120.00   4959.25    4%       16     1% /flash
/dev/lv03      25600.00  24796.43    4%       17     1% /home/oracle/arch_master
rhel152:/backup/cuug15/storage30  14111.05  13052.23    8%       31     1% /backup

mount文件系統(tǒng)失?。?[root@aix220 /]#mount /u01
Replaying log for /dev/lv02.
mount: /dev/lv02 on /u01: Unformatted or incompatible media
The superblock on /dev/lv02 is dirty.  Run a full fsck to fix.

修復文件系統(tǒng)：
[root@aix220 /]#fsck -y  /dev/lv02 
......
** Phase 5 - Check Inode Map
** Phase 6 - Check Block Map
Bad Block Map (SALVAGED)
** Phase 6b - Salvage Block Map
Superblock is marked dirty (FIXED)
123665 files 10749336 blocks 20707944 free
***** Filesystem was modified *****

mount文件系統(tǒng)成功：
[root@aix220 /]#mount /u01
[root@aix220 /]#df -m
Filesystem    MB blocks      Free %Used    Iused %Iused Mounted on
/dev/hd4       17408.00   1238.16   93%    37699    12% /
/dev/hd2        8192.00   6310.39   23%    46534     4% /usr
/dev/hd9var     2048.00   1725.55   16%     1704     1% /var
/dev/hd3        2048.00   1902.58    8%      420     1% /tmp
/dev/fwdump     3072.00   3071.21    1%        4     1% /var/adm/ras/platform
/dev/hd1        2048.00   1821.98   12%       74     1% /home
/proc                 -         -    -         -     -  /proc
/dev/hd10opt    2048.00   1374.77   33%     8934     3% /opt
/dev/lv00       1024.00    991.80    4%       18     1% /var/adm/csd
/dev/lv02      15360.00  10111.30   35%   123674     4% /u01

執(zhí)行sqlplus命令成功：
[root@aix220 /]#su - oracle
[oracle@aix220 ~]$sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 17 19:46:03 2015

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>

   數(shù)據(jù)庫庫報ORA-09925: Unable to create audit trail file，當時查 df -h有可用空間，目錄的權(quán)限也正確，未df -i查看Inodes使用情況，審計目錄下有將近24W個文件，初步猜測是因為審計生成文件過多導致目錄所在分區(qū)的Inodes用光了，當時刪除部分審計TRACE文件后正常未查看 Inodes使用情況。

匯總了下：ORA-09925的原因大致有以下三種：--其實可以根據(jù)報錯判斷是權(quán)限問題還是磁盤空間問題

原因一：目錄權(quán)限問題--目錄權(quán)限被改，無權(quán)限向目錄寫審計數(shù)據(jù)
Oracle bin目錄（實際是整個oracle目錄）的屬主被更改: --ls -al bin
解決方法
chown -R oracle.oinstall /opt/orace
原因二：確實磁盤沒空間了 --df -h
原因三：磁盤Inodes用光--df -i 

參考：模擬Linux磁盤分區(qū)有可用空間無可用Inodes時報錯：No space left on device

報錯示例：--部分網(wǎng)絡

示例1：可以根據(jù)報錯信息判斷是目錄權(quán)限問題導致不能寫入

ORA-09925: Unable to create audit trail file
Linux Error: 13: Permission denied
Additional information: 9925
ORA-09925: Unable to create audit trail file
Linux Error: 13: Permission denied
Additional information: 9925

示例2：--只讀文件系統(tǒng)--沒有寫權(quán)限（可能是文件系統(tǒng)出問題）

ORA-09925: Unable to create audit trail file 
Linux-x86_64 Error: 30: Read-only file system 
Additional information: 9925 
ORA-01075: you are currently logged on

示例3：--沒有可用空間

##############################
MOS上的一段描述及解決：
Problem Description ------------------- 
Problem occurs when: $ORACLE_HOME/rdbms/audit directory is full or $ORACLE_HOME/rdbms/audit directory doesn't exist as a side effect of this problem oracle asks for password after connect internal

Solution Description -------------------- 
Make space available in $ORACLE_HOME/rdbms/audit by removing files that are not needed or Make sure the directory exists and is readable by oracle or Change init.ora audit_file_dest to an existing directory. Change init.ora parameter means restart instance as workaround kill pmon. 

Explanation ----------- 
The behavior on Unix is to write a file named ora_<process_id>.aud into the directory specified by audit_file_dest. The default directory is /rdbms/audit. The file will contain an entry for the audited action, which would be internal logins or startup. On ports which support an OS audit trail, these records will be written there. This feature is not affected by the settings of the audit_trail parameter. 

ORA-09925: "Unable to create audit trail file"

Cause: ORACLE was not able to create the file being used to hold audit trail records.

Action: Check the UNIX error number for a possible operating system error. 
If there is no error, contact ORACLE customer support. 

References ----------

[NOTE:1018924.102] ORA-09925 ON DATABASE STARTUP [NOTE:1056988.6] ORA-09925 DURING HOT BACKUPS [NOTE:21073.1]

OERR: ORA-9925 "Unable to create audit trail [BUG:723955] SQLPLUS ALLOWS DB STARTUP (BUT NOT SHUTDOWN)

IF AUDIT_FILE_DEST DOESN'T EXIST

Search Words ------------ ORA-09925 audit trail audit_file_dest