dataguard ORA-16037: user requested cancel of managed recovery operation

原文地址：http://blog.itpub.net/24486203/viewspace-1799124/

今天用脚本检测到主库跟备库未同步，收到邮件如下，立刻登录到备库去查看，发现备库有大量的归档未应用

查询备库日志

tlog
Errors in file /u01/app/oracle/diag/rdbms/xyxdbdg/xyxdb/trace/xyxdb_pr00_13300.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
Errors in file /u01/app/oracle/diag/rdbms/xyxdbdg/xyxdb/trace/xyxdb_pr00_13300.trc:
ORA-16037: user requested cancel of managed recovery operation

Waiting for MRP0 pid 13282 to terminate

查询进程状态mrp

检查发现1319序号日志丢失，其他日志都存在

THREAD#  SEQUENCE# APPLIED
---------- ---------- ---------
     1     1310 YES
     1     1311 YES
     1     1312 YES
     1     1313 YES
     1     1314 YES
     1     1315 YES
     1     1316 YES
     1     1317 YES
     1     1318 YES
     1     1320 NO
     1     1321 NO

   THREAD#  SEQUENCE# APPLIED
---------- ---------- ---------
     1     1322 NO
     1     1324 NO
     1     1323 NO
     1     1325 NO
     1     1326 NO
     1     1327 NO
     1     1328 NO

     1     1329 NO

由于主库下面的归档已经删除，直接在主库里面将1319归档还原．

MAN> restore archivelog from logseq 1319 until logseq 1320;

Starting restore at 14-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=228 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1319
channel ORA_DISK_1: reading from backup piece /backup/oracle/backupsets/arch_e9qguamq_1_1_20150912
channel ORA_DISK_1: piece handle=/backup/oracle/backupsets/arch_e9qguamq_1_1_20150912 tag=ARC_BAK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1320
channel ORA_DISK_1: reading from backup piece /backup/oracle/backupsets/arch_ehqh0v2q_1_1_20150913
channel ORA_DISK_1: piece handle=/backup/oracle/backupsets/arch_ehqh0v2q_1_1_20150913 tag=ARC_BAK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 14-SEP-15

RMAN> quit

将归档拷贝至备库并注册

[oracle@xyxdbmaster arch]$ scp 1_1319_881695127.dbf oracle@192.168.8.7:/backup/oracle/arch/
oracle@192.168.8.7's password:

1_1319_881695127.dbf  

 SQL> alter database register physical logfile '/backup/oracle/arch/1_1319_881695127.dbf';
alter database register physical logfile '/backup/oracle/arch/1_1319_881695127.dbf'
*
ERROR at line 1:

ORA-16089: archive log has already been registered

显示已经注册

重新查看日志

 RFS[20]: Assigned to RFS process 128117
RFS[20]: Identified database type as 'physical standby': Client is ARCH pid 44148
RFS[20]: Opened log for thread 1 sequence 1319 dbid -1821781228 branch 881695127
Archived Log entry 1343 added for thread 1 sequence 1319 rlc 881695127 ID 0x0 dest 2:
Mon Sep 14 14:15:04 2015
Media Recovery Log /backup/oracle/arch/1_1319_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1320_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1321_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1322_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1323_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1324_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1325_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1326_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1327_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1328_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1329_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1330_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1331_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1332_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1333_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1334_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1335_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1336_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1337_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1338_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1339_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1340_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1341_881695127.dbf

Media Recovery Log /backup/oracle/arch/1_1342_881695127.dbf          

Media Recovery Log /backup/oracle/arch/1_1343_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1344_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1345_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1346_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1347_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1348_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1349_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1350_881695127.dbf
Media Recovery Log /backup/oracle/arch/1_1351_881695127.dbf

Media Recovery Waiting for thread 1 sequence 1352 (in transit)

查看进程状态

SQL> select process,status,thread#,sequence# from v$managed_standby
  2  ;

PROCESS   STATUS       THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
ARCH       CLOSING          1     1351
ARCH       CONNECTED          0        0
ARCH       CLOSING          1     1349
ARCH       CLOSING          1     1350
RFS       IDLE               0        0
RFS       IDLE               0        0
MRP0       WAIT_FOR_LOG          1     1352
RFS       IDLE               1     1352
RFS       IDLE               0        0

9 rows selected.

已经正常

主库上查看已经正常

select name,sequence#,applied from v$archived_log a where a.sequence#=(select max(sequence#) from v$archived_log);
col dest_name format A40
select dest_name,status,error from v$archive_dest where rownum<3;

NAME                                  SEQUENCE# APPLIED
-------------------------------------------------- ---------- ---------
xyxdbdg                               1351 YES
/backup/oracle/arch/1_1351_881695127.dbf          1351 NO

SQL> SQL>
DEST_NAME                    STATUS    ERROR
---------------------------------------- --------- --------------------
LOG_ARCHIVE_DEST_1               VALID
LOG_ARCHIVE_DEST_2               VALID

SQL> archive log list;
Database log mode            Archive Mode
Automatic archival            Enabled
Archive destination            /backup/oracle/arch/
Oldest online log sequence     1350
Next log sequence to archive   1352

Current log sequence            1352

总结：

出现此错误原因应该是主库rman策略

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

管理员在配置此策略的时候没有生效

其实出现此错误如果归档过多可以考虑在备库基于scn恢复

参考文档

http://blog.itpub.net/24486203/viewspace-1698671/