oracle数据库alert 日志和trace日志文件过大清理

前言

使用sys用户登录时，提示ORA-09945: Unable to initialize the audit trail file系统空间已满，遂排查后发现alert 日志和trace文件过大。

---

提示：以下是本篇文章正文内容，下面案例可供参考

操作步骤

1.查询监听及文件大小并关闭

代码如下（示例）：

[root@db ~]# su - oracle
[oracle@db ~]$ lsnrctl status # 下面是监听信息里的目录部分
Listener Parameter File   /usr/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
Listener Log File         /usr/app/oracle/diag/tnslsnr/db/listener/alert/log.xml
# 进入目录下查看各个文件大小
[oracle@db ~]$ cd  /usr/app/oracle/diag/tnslsnr/db/listener
[oracle@db listener]$ du -sh *
# 关闭监听
[oracle@db ~]$ lsnrctl stop

 2.查找并清理过期alert日志文件

代码如下（示例）：

# 查找alter目录下3天前的xml日志
[oracle@db ~]$ find /usr/app/oracle/diag/tnslsnr/db/listener/alert -mtime +3 -name "*.xml"
# 检查无误后删除3天前的日志文件
[oracle@db ~]$ find /usr/app/oracle/diag/tnslsnr/db/listener/alert -mtime +3 -name "*.xml" | xargs rm -f

3.清理trace目录下的文件

代码如下（示例）： 

# 进入监听
[oracle@db trace]$ lsnrctl

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 31-DEC-2024 13:18:48

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> show log_status # 查看监听日志文件状态 "on"是写入状态
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=15210)))
LISTENER parameter "log_status" set to ON
The command completed successfully
LSNRCTL> set log_status off #关闭写入状态
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=15210)))
LISTENER parameter "log_status" set to OFF
The command completed successfully
LSNRCTL> exit # 退出监听程序
[oracle@db trace]$ mv listener.log listener.log.bak # 重命名log文件
[oracle@db trace]$ lsnrctl # 再次进入监听（可以同时开两个控制台，就不用来回进出）

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 31-DEC-2024 13:22:24

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set log_status on # 开启写入状态
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=15210)))
LISTENER parameter "log_status" set to ON
The command completed successfully
LSNRCTL> reload # 重启监听
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=15210)))
The command completed successfully
LSNRCTL> status # 查看监听状态
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=15210)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                31-DEC-2024 11:06:08
Uptime                    0 days 2 hr. 17 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /usr/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
Listener Log File         /usr/app/oracle/diag/tnslsnr/db/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db)(PORT=15210)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=db)(PORT=5500))(Security=(my_wallet_directory=/usr/app/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> exit # 退出监听程序
[oracle@db trace]$ ls
listener.log  listener.log.bak  #可以看到已经产生了新的log文件，bak文件可以根据情况移动或者删除

---

总结

以上就是今天记录的内容，本文仅仅简单介绍了在sys登陆oracle时，提示：
ERROR:ORA-09945: Unable to initialize the audit trail file
Linux Error: 28: No space left on device。
错误的解决方式，可供参考，同样的提示可能原因不尽相同请仔细分析。