ORA-16014: log 18 sequence# 509 not archived, no available destinations

问题描述

数据库有大量锁表，但是不影响生产，因此准备重启。

• 查看实例状态

[oracle@testrac1 ~]$ srvctl status database -d racdb
Instance racdb1 is running on node testrac1
Instance racdb2 is running on node testrac2
[oracle@testrac1 ~]$ srvctl stop database -d racdb
^C[oracle@testrac1 ~]$

stop命令后一直卡着不动，但是另开一个会话，可以看到status已经是not running

所以天真的以为，已经stop了，就没有等，心急的直接start实例，出现了报错：

[oracle@testrac1 ~]$ srvctl start database -d racdb
PRCR-1079 : Failed to start resource ora.racdb.db
CRS-5017: 资源操作 "ora.racdb.db start" 遇到以下错误:
ORA-16014: log 18 sequence# 509 not archived, no available destinations
ORA-00312: online log 18 thread 2: '+RACDB_DATA/racdb/onlinelog/group_18.log'
ORA-00312: online log 18 thread 2: '+FRA/racdb/onlinelog/redo18.log'
。有关详细信息, 请参阅 "(:CLSN00107:)" (位于 "/u01/app/11.2.0/grid/log/testrac1/agent/crsd/oraagent_oracle/oraagent_oracle.log" 中)。

CRS-2674: 未能启动 'ora.racdb.db' (在 'testrac1' 上)
CRS-5017: 资源操作 "ora.racdb.db start" 遇到以下错误:
ORA-03113: end-of-file on communication channel
Process ID: 72055
Session ID: 12208 Serial number: 1
。有关详细信息, 请参阅 "(:CLSN00107:)" (位于 "/u01/app/11.2.0/grid/log/testrac2/agent/crsd/oraagent_oracle/oraagent_oracle.log" 中)。

CRS-2674: 未能启动 'ora.racdb.db' (在 'testrac2' 上)
CRS-2674: 未能启动 'ora.racdb.db' (在 'testrac2' 上)
CRS-2678: 'ora.racdb.db' (在 'testrac2' 上) 遇到了无法恢复的故障
CRS-0267: 要恢复其可用性, 需要人工干预。
CRS-5807: 代理无法处理消息

可能是因为有大量锁表，导致很多日志没有归档就stop数据库了。所以开库的时候就会报错。
于是想尝试做不完全恢复，但是失败了：

RMAN> recover database until cancel;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "cancel": expecting one of: "scn, sequence, time"
RMAN-01007: at line 1 column 24 file: standard input

RMAN> recover database until sequence 509;

Starting recover at 18-11月-2019 13:03:17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=941 instance=racdb1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1880 instance=racdb1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=2193 instance=racdb1 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=2819 instance=racdb1 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=3445 instance=racdb1 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=4072 instance=racdb1 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=1254 instance=racdb1 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=1567 instance=racdb1 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/18/2019 13:03:34
RMAN-06556: datafile 1 must be restored from backup older than SCN 63421488330

RMAN> recover datafile 1 until SCN 63421488330;

Starting recover at 18-11月-2019 13:04:10
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/18/2019 13:04:15
RMAN-06556: datafile 1 must be restored from backup older than SCN 63421488330

RMAN> recover datafile 1 until SCN 63421488329;

Starting recover at 18-11月-2019 13:04:37
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/18/2019 13:04:41
RMAN-06556: datafile 1 must be restored from backup older than SCN 63421488329

并且无法开库：

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS 选项仅在不完全数据库恢复后有效

问题解决

• 查看归档情况

SQL>  select group#,sequence#,archived,status from v$log;

    GROUP#  SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
    14      597 NO  CURRENT
    15      594 NO  INACTIVE
    16      595 NO  INACTIVE
    17      596 YES INACTIVE
    18      509 NO  INACTIVE
    19      510 NO  INACTIVE
    20      511 NO  INACTIVE
    21      512 NO  CURRENT

8 rows selected.

• 清理没归档的日志

SQL> alter database clear unarchived logfile group 18;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16014: 日志 19 sequence# 510 未归档, 没有可用的目的地 ORA-00312:
联机日志 19 线程 2: '+RACDB_DATA/racdb/onlinelog/group_19.log'
ORA-00312: 联机日志 19 线程 2: '+FRA/racdb/onlinelog/redo19.log'


SQL> alter database clear unarchived logfile group 19;

Database altered.

SQL> alter database clear unarchived logfile group 20;

Database altered.

SQL> alter database clear unarchived logfile group 21;

Database altered.

SQL> alter database clear unarchived logfile group 14;

Database altered.

SQL> alter database clear unarchived logfile group 15;

Database altered.

SQL> alter database clear unarchived logfile group 16;

Database altered.

除了已经archived的group，没有archived的group都要clear一遍

• 成功开库

SQL> alter database open;

Database altered.