ORA-16014: 日志 1 sequence# 1 未归档, 没有可用的目的地

报错现象：

alert日志：ORA-16014: 日志 1 sequence# 1 未归档, 没有可用的目的地
检查归档路径和权限都没问题，恢复区空间也正常。比较奇怪。

set line 180
select * from v$flash_recovery_area_usage;
复制

千万别一上来就以为日志损坏清理归档：https://blog.csdn.net/weixin_34414650/article/details/92705266

[oracle@ora02 arch]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on 星期日 6月 25 10:54:26 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

已连接到空闲例程。

SQL> startup     
ORACLE 例程已经启动。

Total System Global Area 3154115088 bytes
Fixed Size                  9140752 bytes
Variable Size            1459617792 bytes
Database Buffers         1677721600 bytes
Redo Buffers                7634944 bytes
数据库装载完毕。
ORA-03113: 通信通道的文件结尾 进程 ID:
4470
会话 ID: 19 序列号: 40501

检查日志：
ARC1 (PID:2930): Becoming the 'no SRL' ARCH
2023-06-25T10:26:07.865307+08:00
TMON (PID:2890): ARC3: Archival started
TMON (PID:2890): STARTING ARCH PROCESSES COMPLETE
2023-06-25T10:26:07.885301+08:00
TT00 (PID:2936): Gap Manager starting
2023-06-25T10:40:40.578494+08:00
db_recovery_file_dest_size of 12732 MB is 0.52% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
2023-06-25T10:48:31.911557+08:00
ALTER SYSTEM SET db_recovery_file_dest_size=20G SCOPE=BOTH;
2023-06-25T10:48:42.766329+08:00
alter database open
2023-06-25T10:48:42.773653+08:00
Ping without log force is disabled:
  instance mounted in exclusive mode.
2023-06-25T10:48:42.778151+08:00
Crash Recovery excluding pdb 2 which was cleanly closed.
Endian type of dictionary set to little
2023-06-25T10:48:42.801048+08:00
Errors in file /u01/app/oracle/diag/rdbms/mid/mid/trace/mid_ora_2902.trc:
ORA-16014: 日志 1 sequence# 1 未归档, 没有可用的目的地
ORA-00312: 联机日志 1 线程 1: '/u01/app/oracle/oradata/JYC/redo01.log'
USER (ospid: ): terminating the instance due to ORA error 
2023-06-25T10:48:42.964913+08:00
System state dump requested by (instance=1, osid=2902), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/mid/mid/trace/mid_diag_2841.trc
2023-06-25T10:48:43.300413+08:00
Dumping diagnostic data in directory=[cdmp_20230625104842], requested by (instance=1, osid=2902), summary=[abnormal instance termination].
2023-06-25T10:48:44.469375+08:00
Instance terminated by USER, pid = 2902

复制

处理过程：

当前由于设置了恢复区和log_archive_dest_2所以STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/arch和本地归档都指向了log_archive_dest_2

处理办法是需要取消恢复区，并设置log_archive_dest_1才能确保本地可归档存放。

start mount
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=‘LOCATION=/u01/app/oracle/fast_recovery_area VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)’ scope=both;
alter system reset db_recovery_file_dest;
调整之后：

详细操作日志如下：

[oracle@ora02 trace]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on 星期日 6月 25 10:58:25 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


连接到: 
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enabled_PDBs_on_standby              string      *
standby_db_preserve_states           string      NONE
standby_file_management              string      MANUAL
standby_pdb_source_file_dblink       string
standby_pdb_source_file_directory    string
SQL> archive log list;
数据库日志模式            存档模式
自动存档             启用
存档终点            /u01/app/oracle/arch
最早的联机日志序列     1
下一个存档日志序列   1
当前日志序列           3
SQL> show parameter STANDBY_ARCHIVE_DEST;
SQL> show parameter dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/jyc/adum
                                                 p
background_dump_dest                 string      /u01/app/oracle/product/19.3.0
                                                 /db_1/rdbms/log
core_dump_dest                       string      /u01/app/oracle/diag/rdbms/mid
                                                 /mid/cdump
cursor_bind_capture_destination      string      memory+disk
db_create_file_dest                  string
db_create_online_log_dest_1          string
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_
                                                 area
db_recovery_file_dest_size           big integer 20G
diagnostic_dest                      string      /u01/app/oracle
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string
log_archive_dest_19                  string
log_archive_dest_2                   string      LOCATION=/u01/app/oracle/arch
                                                 VALID_FOR=(STANDBY_LOGFILE,ALL
                                                 _ROLES)
log_archive_dest_20                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string
log_archive_dest_3                   string
log_archive_dest_30                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_31                  string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_11            string      enable
log_archive_dest_state_12            string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_13            string      enable
log_archive_dest_state_14            string      enable
log_archive_dest_state_15            string      enable
log_archive_dest_state_16            string      enable
log_archive_dest_state_17            string      enable
log_archive_dest_state_18            string      enable
log_archive_dest_state_19            string      enable
log_archive_dest_state_2             string      ENABLE
log_archive_dest_state_20            string      enable
log_archive_dest_state_21            string      enable
log_archive_dest_state_22            string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_23            string      enable
log_archive_dest_state_24            string      enable
log_archive_dest_state_25            string      enable
log_archive_dest_state_26            string      enable
log_archive_dest_state_27            string      enable
log_archive_dest_state_28            string      enable
log_archive_dest_state_29            string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_30            string      enable
log_archive_dest_state_31            string      enable
log_archive_dest_state_4             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
log_archive_duplex_dest              string
log_archive_min_succeed_dest         integer     1
remote_recovery_file_dest            string
user_dump_dest                       string      /u01/app/oracle/product/19.3.0
                                                 /db_1/rdbms/log
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/fast_recovery_area VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)' scope=both;

系统已更改。

SQL> alter system reset db_recovery_file_dest;

系统已更改。

SQL> shutdown immediate
ORA-01109: 数据库未打开


已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 3154115088 bytes
Fixed Size                  9140752 bytes
Variable Size            1459617792 bytes
Database Buffers         1677721600 bytes
Redo Buffers                7634944 bytes
数据库装载完毕。
数据库已经打开。
SQL> archive log list;
数据库日志模式            存档模式
自动存档             启用
存档终点            /u01/app/oracle/arch
最早的联机日志序列     2
下一个存档日志序列   4
当前日志序列           4
SQL> 

复制

修改之后归档正常：