ORA-09925: Unable to create audit trail file

2018. 10. 15. 09:53 내맘대로긍정

프린트 하기

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 12.2.0.1

에러 : -bash: cannot create temp file for here-document: No space left on device

홈경로에서 pud라는 폴더로 이동하기 위해 cd p하고 탭을 눌려서 자동완성되게끔 시도함

[oracle@oracle12 ~]$ cd p-bash: cannot create temp file for here-document: No space left on device

-bash: cd: p: No such file or directory

위와 같은 에러 발생

sqlplus 로 db 접속 시도

[oracle@oracle12 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 15 09:34:25 2018

여기서 행걸려서 들어가지지 않음

컨트롤 C로 빠져 나옴

ERROR:

ORA-09925: Unable to create audit trail file

Linux-x86_64 Error: 28: No space left on device

Additional information: 9925

ORA-01075: you are currently logged on

위 에러 발생

해결 방법 :

alert log 확인

[oracle@oracle12 ~]$ cd /oracle/app/oracle/diag/rdbms/orcl/ORCL/trace

[oracle@oracle12 trace]$ vi alert*

2018-10-15T03:52:21.717053+09:00

Non critical error ORA-48180 caught while writing to trace file "/oracle/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_m000_25416.trc"

Error message: Linux-x86_64 Error: 2: No such file or directory

Additional information: 1

Writing to the above trace file is disabled for now on...

2018-10-15T03:57:21.674460+09:00

krsd_check_stuck_arch: stuck archiver condition cleared

2018-10-15T03:57:21.680237+09:00

Unable to create archive log file '/oracle/app/oracle/fast_recovery_area/ORCL/1_176_979831519.dbf'

2018-10-15T03:57:21.681998+09:00

Errors in file /oracle/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_arc0_1898.trc:

ORA-19504: failed to create file "/oracle/app/oracle/fast_recovery_area/ORCL/1_176_979831519.dbf"

ORA-27040: file create error, unable to create file

Linux-x86_64 Error: 28: No space left on device

Additional information: 1

ARC0: Error 19504 Creating archive log file to '/oracle/app/oracle/fast_recovery_area/ORCL/1_176_979831519.dbf'

krsd_check_stuck_arch: stuck archiver: insufficient local LADs

krsd_check_stuck_arch: stuck archiver condition declared

2018-10-15T03:57:21.725105+09:00

Non critical error ORA-48180 caught while writing to trace file "/oracle/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_m000_25500.trc"

파일을 더이상 생성하지 못해서 발생하는 에러 발견

디스크 용량 체크

[oracle@oracle12 ~]$ df -h

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/ol_oracle12-root 46G 46G 20K 100% /

devtmpfs 2.0G 0 2.0G 0% /dev

tmpfs 2.0G 675M 1.3G 35% /dev/shm

tmpfs 2.0G 202M 1.8G 11% /run

tmpfs 2.0G 0 2.0G 0% /sys/fs/cgroup

/dev/sda1 497M 177M 320M 36% /boot

tmpfs 396M 16K 396M 1% /run/user/42

tmpfs 396M 0 396M 0% /run/user/1000

tmpfs 396M 0 396M 0% /run/user/0

root 영역 (/) 이 100% 인것을 확인

alert log 에 나와있는 fast_recovery_area 경로를 확인

[oracle@oracle12 ~]$ cd /oracle/app/oracle/fast_recovery_area/ORCL

ls 해보니 아카이브 로그들이 많음

[oracle@oracle12 ORCL]$ ls

1_110_979831519.dbf 1_124_979831519.dbf 1_138_979831519.dbf 1_152_979831519.dbf 1_166_979831519.dbf

1_111_979831519.dbf 1_125_979831519.dbf 1_139_979831519.dbf 1_153_979831519.dbf 1_167_979831519.dbf

1_112_979831519.dbf 1_126_979831519.dbf 1_140_979831519.dbf 1_154_979831519.dbf 1_168_979831519.dbf

1_113_979831519.dbf 1_127_979831519.dbf 1_141_979831519.dbf 1_155_979831519.dbf 1_169_979831519.dbf

1_114_979831519.dbf 1_128_979831519.dbf 1_142_979831519.dbf 1_156_979831519.dbf 1_170_979831519.dbf

1_115_979831519.dbf 1_129_979831519.dbf 1_143_979831519.dbf 1_157_979831519.dbf 1_171_979831519.dbf

1_116_979831519.dbf 1_130_979831519.dbf 1_144_979831519.dbf 1_158_979831519.dbf 1_172_979831519.dbf

1_117_979831519.dbf 1_131_979831519.dbf 1_145_979831519.dbf 1_159_979831519.dbf 1_173_979831519.dbf

1_118_979831519.dbf 1_132_979831519.dbf 1_146_979831519.dbf 1_160_979831519.dbf 1_174_979831519.dbf

1_119_979831519.dbf 1_133_979831519.dbf 1_147_979831519.dbf 1_161_979831519.dbf 1_175_979831519.dbf

1_120_979831519.dbf 1_134_979831519.dbf 1_148_979831519.dbf 1_162_979831519.dbf

1_121_979831519.dbf 1_135_979831519.dbf 1_149_979831519.dbf 1_163_979831519.dbf

1_122_979831519.dbf 1_136_979831519.dbf 1_150_979831519.dbf 1_164_979831519.dbf

1_123_979831519.dbf 1_137_979831519.dbf 1_151_979831519.dbf 1_165_979831519.dbf

오늘 날짜 확인

[oracle@oracle12 ORCL]$ date

Mon Oct 15 09:37:54 KST 2018

오늘로부터 5일 이상 지난 파일들 삭제

[oracle@oracle12 ORCL]$ find . -name "*.dbf" -mtime +5 -exec rm {} \;

재 조회

[oracle@oracle12 ORCL]$ ls

1_162_979831519.dbf 1_165_979831519.dbf 1_168_979831519.dbf 1_171_979831519.dbf 1_174_979831519.dbf

1_163_979831519.dbf 1_166_979831519.dbf 1_169_979831519.dbf 1_172_979831519.dbf 1_175_979831519.dbf

1_164_979831519.dbf 1_167_979831519.dbf 1_170_979831519.dbf 1_173_979831519.dbf

디스크 용량 재 확인

[oracle@oracle12 ORCL]$ df -h

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/ol_oracle12-root 46G 41G 5.4G 89% /

devtmpfs 2.0G 0 2.0G 0% /dev

tmpfs 2.0G 675M 1.3G 35% /dev/shm

tmpfs 2.0G 202M 1.8G 11% /run

tmpfs 2.0G 0 2.0G 0% /sys/fs/cgroup

/dev/sda1 497M 177M 320M 36% /boot

tmpfs 396M 16K 396M 1% /run/user/42

tmpfs 396M 0 396M 0% /run/user/1000

tmpfs 396M 0 396M 0% /run/user/0

용량 줄어든것 확인가능

rman에서 crosscheck 후 지워주기

rman 접속

[oracle@oracle12 ORCL]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Oct 15 09:40:09 2018

connected to target database: ORCL (DBID=1501808093)

아카이브 로그 크로스 체크 진행

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=47 device type=DISK