Oracle Study之案例--Oracle Sqlplus錯誤

系統環境：

作業系統：AIX5.3

資料庫：  Oracle 10gR2

<a href="http://s3.51cto.com/wyfs02/M02/58/AD/wKiom1S3elTR8RPtAAIs2ex0EMc791.jpg" target="_blank"></a>

    案例分析：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

<code>1</code><code>、檢視空間資訊</code>

<code>[oracle@aix220 ~]$df -m</code>

<code>Filesystem    MB blocks      Free %Used    Iused %Iused Mounted </code><code>on</code>

<code>/dev/hd4       </code><code>17408.00</code>   <code>1238.15</code>   <code>93</code><code>%    </code><code>37699</code>    <code>12</code><code>% /</code>

<code>/dev/hd2        </code><code>8192.00</code>   <code>6310.39</code>   <code>23</code><code>%    </code><code>46534</code>     <code>4</code><code>% /usr</code>

<code>/dev/hd9var     </code><code>2048.00</code>   <code>1725.55</code>   <code>16</code><code>%     </code><code>1704</code>     <code>1</code><code>% /</code><code>var</code>

<code>/dev/hd3        </code><code>2048.00</code>   <code>1902.58</code>    <code>8</code><code>%      </code><code>420</code>     <code>1</code><code>% /tmp</code>

<code>/dev/fwdump     </code><code>3072.00</code>   <code>3071.21</code>    <code>1</code><code>%        </code><code>4</code>     <code>1</code><code>% /</code><code>var</code><code>/adm/ras/platform</code>

<code>/dev/hd1        </code><code>2048.00</code>   <code>1821.98</code>   <code>12</code><code>%       </code><code>74</code>     <code>1</code><code>% /home</code>

<code>/proc                 -         -    -         -     -  /proc</code>

<code>/dev/hd10opt    </code><code>2048.00</code>   <code>1374.77</code>   <code>33</code><code>%     </code><code>8934</code>     <code>3</code><code>% /opt</code>

<code>/dev/lv00       </code><code>1024.00</code>    <code>991.80</code>    <code>4</code><code>%       </code><code>18</code>     <code>1</code><code>% /</code><code>var</code><code>/adm/csd</code>

<code>/dev/arch      </code><code>10240.00</code>   <code>8347.63</code>   <code>19</code><code>%  </code>

<code>/dev/lv02      </code><code>15360.00</code>  <code>10097.80</code>   <code>35</code><code>%   </code><code>123693</code>     <code>4</code><code>% /u01</code>

<code>2</code><code>、檢視檔案權限</code>

<code>[oracle@aix220 ~]$ls -ld /u01/app/oracle/admin/master/</code>

<code>drwxr-x---    </code><code>8</code> <code>oracle   dba             </code><code>512</code> <code>Feb </code><code>17</code> <code>16</code><code>:</code><code>40</code> <code>/u01/app/oracle/admin/master/</code>

<code>[oracle@aix220 ~]$ls -ld /u01/app/oracle/admin/master/adump/</code>

<code>drwxr-x---    </code><code>2</code> <code>oracle   dba            </code><code>1536</code> <code>Feb </code><code>17</code> <code>19</code><code>:</code><code>48</code> <code>/u01/app/oracle/admin/master/adump/</code>

<code>3</code><code>、檢視檔案系統資訊</code>

<code>[oracle@aix220 dbs]$cat /etc/filesystems</code>

<code>/u01:</code>

<code>        </code><code>dev             = /dev/lv02</code>

<code>        </code><code>vfs             = jfs</code>

<code>        </code><code>log             = /dev/loglv00</code>

<code>        </code><code>mount           = </code><code>true</code>

<code>        </code><code>options         = rw</code>

<code>        </code><code>account         = </code><code>false</code>

<code>[root@aix220 /]#lsvg -l oraclevg</code>

<code>oraclevg:</code>

<code>LV NAME             TYPE       LPs     PPs     PVs  LV STATE      MOUNT POINT</code>

<code>loglv00             jfslog     </code><code>1</code>       <code>1</code>       <code>1</code>    <code>closed/syncd  N/A</code>

<code>lv02                jfs        </code><code>120</code>     <code>120</code>     <code>1</code>    <code>closed/syncd  /u01</code>

<code>[root@aix220 /]#df -m</code>

<code>/dev/hd4       </code><code>17408.00</code>   <code>1238.18</code>   <code>93</code><code>%    </code><code>37700</code>    <code>12</code><code>% /</code>

<code>/dev/hd9var     </code><code>2048.00</code>   <code>1725.56</code>   <code>16</code><code>%     </code><code>1704</code>     <code>1</code><code>% /</code><code>var</code>

<code>/dev/arch      </code><code>10240.00</code>   <code>8347.63</code>   <code>19</code><code>%       </code><code>52</code>     <code>1</code><code>% /arch</code>

<code>/dev/lv01       </code><code>5120.00</code>   <code>4959.25</code>    <code>4</code><code>%       </code><code>16</code>     <code>1</code><code>% /flash</code>

<code>/dev/lv03      </code><code>25600.00</code>  <code>24796.43</code>    <code>4</code><code>%       </code><code>17</code>     <code>1</code><code>% /home/oracle/arch_master</code>

<code>rhel152:/backup/cuug15/storage30  </code><code>14111.05</code>  <code>13052.23</code>    <code>8</code><code>%       </code><code>31</code>     <code>1</code><code>% /backup</code>

<code>mount檔案系統失敗：</code>

<code>[root@aix220 /]#mount /u01</code>

<code>Replaying log </code><code>for</code> <code>/dev/lv02.</code>

<code>mount: /dev/lv02 </code><code>on</code> <code>/u01: Unformatted </code><code>or</code> <code>incompatible media</code>

<code>The superblock </code><code>on</code> <code>/dev/lv02 is dirty.  Run a full fsck to fix.</code>

<code>修複檔案系統：</code>

<code>[root@aix220 /]#fsck -y  /dev/lv02 </code>

<code>......</code>

<code>** Phase </code><code>5</code> <code>- Check Inode Map</code>

<code>** Phase </code><code>6</code> <code>- Check Block Map</code>

<code>Bad Block Map (SALVAGED)</code>

<code>** Phase 6b - Salvage Block Map</code>

<code>Superblock is marked dirty (FIXED)</code>

<code>123665</code> <code>files </code><code>10749336</code> <code>blocks </code><code>20707944</code> <code>free</code>

<code>***** Filesystem was modified *****</code>

<code>mount檔案系統成功：</code>

<code>/dev/hd4       </code><code>17408.00</code>   <code>1238.16</code>   <code>93</code><code>%    </code><code>37699</code>    <code>12</code><code>% /</code>

<code>/dev/lv02      </code><code>15360.00</code>  <code>10111.30</code>   <code>35</code><code>%   </code><code>123674</code>     <code>4</code><code>% /u01</code>

<code>執行sqlplus指令成功：</code>

<code>[root@aix220 /]#su - oracle</code>

<code>[oracle@aix220 ~]$sqlplus </code><code>'/as sysdba'</code>

<code>SQL*Plus: Release </code><code>10.2.</code><code>0.1.</code><code>0</code> <code>- Production </code><code>on</code> <code>Tue Feb </code><code>17</code> <code>19</code><code>:</code><code>46</code><code>:</code><code>03</code> <code>2015</code>

<code>Copyright (c) </code><code>1982</code><code>, </code><code>2005</code><code>, Oracle.  All rights reserved.</code>

<code>Connected to an idle instance.</code>

<code>SQL&gt;</code>

   資料庫庫報ORA-09925: Unable to create audit trail file，當時查 df -h有可用空間，目錄的權限也正确，未df -i檢視Inodes使用情況，審計目錄下有将近24W個檔案，初步猜測是因為審計生成檔案過多導緻目錄所在分區的Inodes用光了，當時删除部分審計TRACE檔案後正常未檢視 Inodes使用情況。

原因一：目錄權限問題--目錄權限被改，無權限向目錄寫審計資料

解決方法

chown -R oracle.oinstall /opt/orace

原因二：确實磁盤沒空間了 --df -h

原因三：磁盤Inodes用光--df -i 

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-x86_64 Error: 30: Read-only file system 

Additional information: 9925 

ORA-01075: you are currently logged on

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_&lt;process_id&gt;.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

本文轉自 客居天涯 51CTO部落格，原文連結：http://blog.51cto.com/tiany/1604445，如需轉載請自行聯系原作者