Oracle Study之案例--Oracle Sqlplus错误
Oracle Study之案例--Oracle Sqlplus错误
系统环境：
操作系统：AIX5.3
数据库： Oracle 10gR2
案例分析：
1、查看空间信息[oracle@aix220 ~]$df -mFilesystem 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% /u012、查看文件权限[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、查看文件系统信息[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 oraclevgoraclevg:LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINTloglv00 jfslog 1 1 1 closed/syncd N/Alv02 jfs 120 120 1 closed/syncd /u01[root@aix220 /]#df -mFilesystem 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_masterrhel152:/backup/cuug15/storage30 14111.05 13052.23 8% 31 1% /backupmount文件系统失败：[root@aix220 /]#mount /u01Replaying log for /dev/lv02.mount: /dev/lv02 on /u01: Unformatted or incompatible mediaThe superblock on /dev/lv02 is dirty. Run a full fsck to fix.修复文件系统：[root@aix220 /]#fsck -y /dev/lv02 ......** Phase 5 - Check Inode Map** Phase 6 - Check Block MapBad Block Map (SALVAGED)** Phase 6b - Salvage Block MapSuperblock is marked dirty (FIXED)123665 files 10749336 blocks 20707944 free***** Filesystem was modified *****mount文件系统成功：[root@aix220 /]#mount /u01[root@aix220 /]#df -mFilesystem 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执行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 2015Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to an idle instance.SQL>
数据库库报ORA-09925: Unable to create audit trail file，当时查 df -h有可用空间，目录的权限也正确，未df -i查看Inodes使用情况，审计目录下有将近24W个文件，初步猜测是因为审计生成文件过多导致目录所在分区的Inodes用光了，当时删除部分审计TRACE文件后正常未查看 Inodes使用情况。
汇总了下：ORA-09925的原因大致有以下三种：--其实可以根据报错判断是权限问题还是磁盘空间问题
原因一：目录权限问题--目录权限被改，无权限向目录写审计数据
Oracle bin目录（实际是整个oracle目录）的属主被更改: --ls -al bin
解决方法
chown -R oracle.oinstall /opt/orace
原因二：确实磁盘没空间了 --df -h
原因三：磁盘Inodes用光--df -i
参考：模拟Linux磁盘分区有可用空间无可用Inodes时报错：No space left on device
报错示例：--部分网络
示例1：可以根据报错信息判断是目录权限问题导致不能写入
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：--只读文件系统--没有写权限（可能是文件系统出问题）
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_
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
