ORACLE STANDBY 修复ORA-19505问题记录

最新推荐文章于 2024-09-17 21:52:37 发布

原创最新推荐文章于 2024-09-17 21:52:37 发布 · 1.3w 阅读

2 ·

CC 4.0 BY-SA版权

oracle problem 同时被 2 个专栏收录

61 篇文章

订阅专栏

oracle dataguard

27 篇文章

订阅专栏

本文详细记录了一次Oracle11g备库因坏块无法增量修复，采用重新搭建方式修复的过程。在尝试使用RMAN命令进行在线修复时遇到ORA-19505错误，通过关闭备库、覆盖控制文件并从主库拉取最新数据完成恢复。

备库down了，经过检查，坏块无法做增量修复，所以需要重新搭建的方式来修复standby库，oracle11g里面有duplicate的方式远程在线修复命令：

run {
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
duplicate target database for standby from active database nofilenamecheck;
release channel c1;
release channel c2;
}

意外的是，修复过程中报错ORA-19505，如下：

[oracle@odbm1 dbs]$ rlwrap rman target sys/pwd_0978@odbm1 auxiliary sys/pwd_0978@odbs1

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jun 5 19:49:04 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: orcldb (DBID=3391761643)
connected to auxiliary database: orcldb (not mounted)

run {
allocate auxiliary channel c1 device type disk;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "device, type"
RMAN-01008: the bad identifier was: devicetype
RMAN-01007: at line 2 column 31 file: standard input

allocate auxiliary channel c2 device type disk;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "auxiliary": expecting one of: "channel"
RMAN-01007: at line 1 column 10 file: standard input

duplicate target database for standby nofilenamecheck dorecover;

Starting Duplicate Db at 05-JUN-17
release channel c1;
release channel c2;
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=4708 device type=DISK

contents of Memory Script:
{
   set until scn  14430447592;
   restore clone standby controlfile;
}
executing Memory Script

executing command: SET until clause

Starting restore at 05-JUN-17
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /oracle/backup/data/controlfiles/c-3391761643-20170605-00
channel ORA_AUX_DISK_1: ORA-19870: error while restoring backup piece /oracle/backup/data/controlfiles/c-3391761643-20170605-00
ORA-19505: failed to identify file "/oracle/backup/data/controlfiles/c-3391761643-20170605-00"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

failover to previous backup

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /oracle/backup/data/controlfiles/c-3391761643-20170604-00
channel ORA_AUX_DISK_1: ORA-19870: error while restoring backup piece /oracle/backup/data/controlfiles/c-3391761643-20170604-00
ORA-19505: failed to identify file "/oracle/backup/data/controlfiles/c-3391761643-20170604-00"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

failover to previous backup

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /oracle/backup/data/controlfiles/c-3391761643-20170603-00
channel ORA_AUX_DISK_1: ORA-19870: error while restoring backup piece /oracle/backup/data/controlfiles/c-3391761643-20170603-00
ORA-19505: failed to identify file "/oracle/backup/data/controlfiles/c-3391761643-20170603-00"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

failover to previous backup

【解决办法】

（1）关闭备库standby

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>

（2）在备库上覆盖控制文件，启动到nomount

[oracle@pldb02 dbs]$ cp /home/oracle/ctlfile4.bak /oracle/app/oracle/oradata/orcldb/control01.ctl
[oracle@pldb02 dbs]$ rlwrap sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 5 19:57:14 2017

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 4409401344 bytes
Fixed Size		2260408 bytes
Variable Size		 2936013384 bytes
Database Buffers	 1459617792 bytes
Redo Buffers		   11509760 bytes
SQL>

（3）在master主库上重新拉active数据进行恢复操作

[oracle@odbm1 dbs]$ rlwrap rman target sys/pwd_0978@odbm1 auxiliary sys/pwd_0978@odbs1

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jun 5 19:57:27 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: orcldb (DBID=3391761643)
connected to auxiliary database: orcldb (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 05-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=5649 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcldb' auxiliary format 
 '/oracle/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapworcldb'   ;
}
executing Memory Script

Starting backup at 05-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1430 device type=DISK
Finished backup at 05-JUN-17

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/oracle/app/oracle/oradata/orcldb/control01.ctl';
   restore clone controlfile to  '/oracle/app/oracle/fast_recovery_area/orcldb/control02.ctl' from 
 '/oracle/app/oracle/oradata/orcldb/control01.ctl';
}
executing Memory Script

Starting backup at 05-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcldb.f tag=TAG20170605T195936 RECID=30 STAMP=945892777
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 05-JUN-17

Starting restore at 05-JUN-17
using channel ORA_AUX_DISK_1

......

bty，如果执行命令：duplicate target database for standby from active database nofilenamecheck;在线恢复standby库的话，需要特别慎重，因为这会在线锁表的，建议在业务低峰期间做，不然会影响业务使用。