归档路径没权限导致数据库连接不上故障分析

问题描述

同事反馈业务连不上数据库（单实例），查看磁盘空间、监听、数据库状态、最大会话数和进程数都没问题，最后杀手锏翻告警日志，提示如下报错：

Wed Sep 13 09:18:24 2023
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance ecps - Archival Error
ORA-16038: log 4 sequence# 148 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 4 thread 1: '/home/u01/app/oracle/oradata/ecps/redo04.log'
Archiver process freed from errors. No longer stopped
Wed Sep 13 09:18:24 2023
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance ecps - Archival Error
ORA-16014: log 4 sequence# 148 not archived, no available destinations
ORA-00312: online log 4 thread 1: '/home/u01/app/oracle/oradata/ecps/redo04.log'
Wed Sep 13 09:18:24 2023

问题分析

#磁盘空间   /home目录空间剩余很多
[oracle@zhuti12 ~]$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda3       300G  292G  8.2G  98% /
devtmpfs         63G     0   63G   0% /dev
tmpfs            63G     0   63G   0% /dev/shm
tmpfs            63G   25M   63G   1% /run
tmpfs            63G     0   63G   0% /sys/fs/cgroup
/dev/vda1       197M  113M   85M  58% /boot
/dev/vdb1       985G  409G  526G  44% /home
tmpfs            13G     0   13G   0% /run/user/1000
tmpfs            13G     0   13G   0% /run/user/0

#监听   READY 正常
[oracle@zhuti12 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-SEP-2023 10:09:52

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                10-SEP-2023 18:34:26
Uptime                    2 days 15 hr. 35 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/u01/app/oracle/product/11g/db_1/network/admin/listener.ora
Listener Log File         /home/u01/app/oracle/diag/tnslsnr/zhuti12/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zhuti12)(PORT=1521)))
Services Summary...
Service "ecps" has 1 instance(s).
  Instance "ecps", status READY, has 1 handler(s) for this service...
Service "ecpsXDB" has 1 instance(s).
  Instance "ecps", status READY, has 1 handler(s) for this service...
The command completed successfully

#数据库运行状态  open正常
SQL> select status from v$instance;

STATUS
------------
OPEN

#最大会话数和进程数  参数3000，目前才100
SQL> show parameter process

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes 		     integer	 1
cell_offload_processing 	     boolean	 TRUE
db_writer_processes		     integer	 4
gcs_server_processes		     integer	 0
global_txn_processes		     integer	 1
job_queue_processes		     integer	 1000
log_archive_max_processes	     integer	 4
processes			     integer	 3000
processor_group_name		     string

SQL> show parameter session  #4544 目前才101个会话

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size	   integer	 0
java_soft_sessionspace_limit	 integer	 0
license_max_sessions		       integer	 0
license_sessions_warning	     integer	 0
session_cached_cursors		     integer	 50
session_max_open_files		     integer	 10
sessions			                 integer	 4544
shared_server_sessions		     integer


SQL> select count(*) from v$process;

  COUNT(*)
----------
       100

SQL> select count(*) from v$session;

  COUNT(*)
----------
       101

#查告警日志  找到原因
[oracle@zhuti12 ecps]$ tail -200f  trace/alert_ecps.log 
Wed Sep 13 09:18:24 2023
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance ecps - Archival Error
ORA-16038: log 4 sequence# 148 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 4 thread 1: '/home/u01/app/oracle/oradata/ecps/redo04.log'
Archiver process freed from errors. No longer stopped
Wed Sep 13 09:18:24 2023
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance ecps - Archival Error
ORA-16014: log 4 sequence# 148 not archived, no available destinations
ORA-00312: online log 4 thread 1: '/home/u01/app/oracle/oradata/ecps/redo04.log'
Wed Sep 13 09:18:24 2023

查看归档路径
SQL> show parameter log_archive_dest_1 
NAME				     TYPE	 VALUE
log_archive_dest_1		     string	 location=/home

SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /home/  
Oldest online log sequence     148
Next log sequence to archive   148
Current log sequence	       153

 #ls -ld /home 只有root权限
[root@ora24 mapper]# ls -ld /home
drwxr-xr-x. 2 root root 6 Apr 11  2018 /home

问题解决

创建归档目录

mkdir -p /home/archive/ecps
chown -R oracle:oinstall /home/archive/ecps

更改归档路径

SQL> alter system SET log_archive_dest_1='location=/home/archive/ecps';

查看归档参数

SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /home/archive/ecps
Oldest online log sequence     148
Next log sequence to archive   148
Current log sequence	       153

手动切归档测试

SQL> alter system switch logfile;

System altered.

联系业务测试恢复正常。和现场沟通归档是自行百度开启的，向其说明开启归档注意事项，互相成长。

补充：归档参数

#归档路径  修改后直接生效
alter system SET log_archive_dest_1='location=/home/archive/ecps';  #默认scope=both

#归档格式  修改后需重启
alter system set log_archive_format='%t_%s_%r.arch' scope=spfile; #静态参数

#开启/关闭归档必须在mount下
alter database archivelog;
alter database noarchivelog;