Oracle 如果一个线程被关闭，从活跃数据库复制引发ORA-01194

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 ： 13764045638 QQ号:47079569 邮箱：service@parnassusdata.com

oerr ora 01194
01194, 00000, "file %s needs more recovery to be consistent"
// *Cause:  An incomplete recovery session was started, but an insufficient
//         number of logs were applied to make the file consistent. The
//         reported file was not closed cleanly when it was last opened by
//         the database. It must be recovered to a time when it was not being
//         updated. The most likely cause of this error is forgetting to
//         restore the file from a backup before doing incomplete recovery.
// *Action: Either apply more logs until the file is consistent or restore
//          the file from an older backup and repeat recovery.

适用于:

Oracle Database – Enterprise Edition – 版本 11.2.0.1 及以上
本文信息适用于任何平台。

症状
在以下恢复后，使用FROM ACTIVE DATABASE ，RMAN duplicate 将RAC 数据库复制到单个实例失败：

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
 ORA-01194: file 1 needs more recovery to be consistent
 ORA-01110: data file 1: '/d13dwh/oradata1/d13dwh/datafile/system.2990.703705425'

检查duplicate日志显示：

RMAN-08161: contents of Memory Script:
 {
 set until scn 42092895854;
 recover
 clone database
 delete archivelog
 ;
 }

检查辅助数据库中的文件头显示所有被还原文件的checkpoint scn 先于duplicate计算的 untilscn：

STATUS TO_CHAR(CHECKPOINT_CHANGE#) CHECKPOINT_TIME COUNT(*)
 ------- ---------------------------------------- -------------------- ----------
 ONLINE 42375119996 27-JAN-2010 19:06:03 1
 ONLINE 42375695392 27-JAN-2010 19:24:20 1
 ONLINE 42376080410 27-JAN-2010 19:38:15 1
 ...etc

如果尝试手动完成恢复，生成以下错误：

ORA-00600: internal error code, arguments: [kcvhvdf_1], [], [], [], [], [], [], [], [], [], [], []

生成的跟踪文件显示：

—– Current SQL Statement for this session (sql_id=agzpkm7s74893) —–
ALTER DATABASE RECOVER database using backup controlfile

—– Call Stack Trace —–

Kgeasnmierr Kcvhvdf Krdsmr adbdrv

原因

在duplicate运行时，RAC 线程之一被关闭。
从活跃数据库复制在查询v$archived_log，v$thread 和v$database 来找到所有线程的最高next_change#值。然后，它取最低值并将其作为duplicate untilscn值。如果所有线程被打开，那么从每个线程的最高（next_change#）值的可选性很小，且不论何值，它会与活跃数据文件的当前scn一致。

使用计算的untilscn值查询V$ARCHIVED_LOG：

SQL> alter session set nls_date_format=’dd-moin-rr hh24:mi:ss’;
select recid, stamp, thread#, sequence#, first_time, first_change#,
next_time, next_change#
from v$archived_log
where next_change#=42092895854;

RECID           STAMP    THREAD#  SEQUENCE#         FIRST_TIME FIRST_CHANGE#          NEXT_TIME NEXT_CHANGE#
———- ———- ———- ———- —————— ————- —————— ————
7390  708793125          2       3465 20-jan-10 14:58:44 4.2093E+10    20-jan-10 14:58:44   4.2093E+10

检查线程2的警告日志显示它从1月20日起被关闭，因此为duplicate的计算的untilscn非常旧。

可能与以下相关

BUG 9044053 – RMAN DUPLICATE CAUSES RMAN-6457 WHEN USING ‘UNTIL SCN’ UNTIL STARTUP NODE 2
Fixed Ver:     11.2.0.2

解决方案
当你在一个RAC数据库上运行活跃数据库的duplicate，确保所有线程被打开。
打开线程2 会导致为线程2生成新的checkpoint，它将会成为当前的。

启用线程，使用：

SQL> alter database enable thread <thread#>;

or for new RDBMS versions :

SQL> alter database enable instance ‘<instance name>’;

参考

BUG:9044053 – RMAN DUPLICATE CAUSES RMAN-6457 WHEN USING ‘UNTIL SCN’ UNTIL STARTUP NODE 2