目录

• 前言
• 一、添加redo日志
• 二、报错
• 1、ORA-01623
• 2、ORA-01567

前言

旧系统突然接入新需求，开始500M的redo就小了
在日志中有
thread 3 cannot allocate new log,sequence 161713
checkpoint not complete
那就把redo修改成2g吧。

一、添加redo日志

以前有6组日志，新添加6组日志，把旧日志删除就行了

• 查询日志情况

select * from gv$log;
select * from gv$logfile;

• 添加日志

alter database add logfile thread 1 group 7 ('+DATA','+ARCH') size 2g;
alter database add logfile thread 2 group 8 ('+DATA','+ARCH') size 2g;

alter database add logfile thread 1 group 9 ('+DATA','+ARCH') size 2g;
alter database add logfile thread 2 group 10 ('+DATA','+ARCH') size 2g;

alter database add logfile thread 1 group 11 ('+DATA','+ARCH') size 2g;
alter database add logfile thread 2 group 12 ('+DATA','+ARCH') size 2g;

• 切换日志

alter system switch logfile;
alter system checkpoint;

日志状态
unused（还没有使用过）；
current（正在使用）；
active（Log isactive but is not the current log. It is needed for crash recovery）
inactive（Log is nolonger needed for instance recovery）

对inactive的日志进行删除。

• 删除日志

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;

二、报错

1、ORA-01623

SQL>Alter database drop logfile group 3;
Alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance RAC2 (thread 2) - cannot drop
ORA-00312: online log 3 thread 2:

官方文档地址

Dropping Redo Log Files Failing With ORA-1623 (Doc ID 2341816.1)

• 解决方案

Disable Thread <Thread Number> using below command and drop the Logfiles associated to that Thread.
conn / as sysdba
select * from gv$thread;
alter database disable thread 2;
alter database drop logfile group 3;
alter database enable thread 2;

• 原因：这套rac开始时3个节点，停掉一个节点

2、ORA-01567

ORA-01567 dropping log 6 would leave less than 2 log files for instance rac3 （thread 3）

• 原因
  我只添加thread 1 和thread 2 没添加thread 3日志，当thread 3的日志只剩下2个时，就不让删除了。
• 解决方案
  继续添加thread 3 的日志，然后就能删除了

alter database add logfile thread 3 group 13 ('+DATA','+ARCH') size 2g;
alter database add logfile thread 3 group 14 ('+DATA','+ARCH') size 2g;
alter database add logfile thread 3 group 15 ('+DATA','+ARCH') size 2g;

• ogg extract time since chkpt时间增加，无法停止改进程

kill extrac
start extrac