oracle数据批量入库丢数据,Oracle备库丢失数据文件导致复制进程异常

昨天刚增量恢复完的那套库，发现原来是备库的数据文件丢失了几个，导致复制进程异常，而不是因为主库归档被删除的原因

在备库开启复制：

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

命令执行成功，但是alter日志报错：

Wed Apr 01 17:57:35 2015

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

ORA-16136 signalled during: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL...

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

Attempt to start background Managed Standby Recovery process (db26)

Wed Apr 01 17:57:38 2015

MRP0 started with pid=27, OS id=13877

MRP0: Background Managed Standby Recovery process started (db26)

started logmerger process

Wed Apr 01 17:57:43 2015

Managed Standby Recovery not using Real Time Apply

Wed Apr 01 17:57:49 2015

Errors in file /opt/app/oracle/diag/rdbms/dg_136/db26/trace/db26_dbw0_13751.trc:

ORA-01157: cannot identify/lock data file 551 - see DBWR trace file

ORA-01110: data file 551: '/tol/oradata/db26/newcourse16.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /opt/app/oracle/diag/rdbms/dg_136/db26/trace/db26_dbw0_13751.trc:

ORA-01157: cannot identify/lock data file 552 - see DBWR trace file

ORA-01110: data file 552: '/tol/oradata/db26/sysaux02.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /opt/app/oracle/diag/rdbms/dg_136/db26/trace/db26_dbw0_13751.trc:

ORA-01157: cannot identify/lock data file 553 - see DBWR trace file

ORA-01110: data file 553: '/tol/oradata/db26/sso13.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Wed Apr 01 17:58:02 2015

MRP0: Background Media Recovery terminated with error 1110

Errors in file /opt/app/oracle/diag/rdbms/dg_136/db26/trace/db26_pr00_13879.trc:

ORA-01110: data file 551: '/tol/oradata/db26/newcourse16.dbf'

ORA-01157: cannot identify/lock data file 551 - see DBWR trace file

ORA-01110: data file 551: '/tol/oradata/db26/newcourse16.dbf'

Slave exiting with ORA-1110 exception

Errors in file /opt/app/oracle/diag/rdbms/dg_136/db26/trace/db26_pr00_13879.trc:

ORA-01110: data file 551: '/tol/oradata/db26/newcourse16.dbf'

ORA-01157: cannot identify/lock data file 551 - see DBWR trace file

ORA-01110: data file 551: '/tol/oradata/db26/newcourse16.dbf'

Wed Apr 01 17:58:03 2015

Recovery Slave PR00 previously exited with exception 1110

Errors in file /opt/app/oracle/diag/rdbms/dg_136/db26/trace/db26_mrp0_13877.trc:

ORA-01110: data file 551: '/tol/oradata/db26/newcourse16.dbf'

ORA-01157: cannot identify/lock data file 551 - see DBWR trace file

ORA-01110: data file 551: '/tol/oradata/db26/newcourse16.dbf'

MRP0: Background Media Recovery process shutdown (db26)

Wed Apr 01 17:58:03 2015

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

从上面的信息看出551号数据文件有问题，从操作系统看查看发现没有这个文件，从而导致恢复进程意外终止了，

从主库上备份一下这个文件

RMAN> backup datafile '/tol/oradata/db26/newcourse16.dbf' format '/tol/rmanbak/db26/%d_%I_%s_%p_%T.bkp';

Starting backup at 2015-04-02 10:05:37

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=145 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00551 name=/tol/oradata/db26/newcourse16.dbf

channel ORA_DISK_1: starting piece 1 at 2015-04-02 10:05:39

channel ORA_DISK_1: finished piece 1 at 2015-04-02 10:05:42

piece handle=/tol/rmanbak/db26/DB26_484391126_226_1_20150402.bkp tag=TAG20150402T100539 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 2015-04-02 10:05:42

备库上做恢复：

[oracle@dg_135 db26]$ scp DB26_484391126_227_1_20150402.bkp 192.168.100.136:/tol/rmanbak/db26/

DB26_484391126_227_1_20150402.bkp                                                              100%   36MB  35.5MB/s   00:01

RMAN> catalog start with '/tol/rmanbak/db26';

searching for all files that match the pattern /tol/rmanbak/db26

List of Files Unknown to the Database

=====================================

File Name: /tol/rmanbak/db26/DB26_484391126_226_1_20150402.bkp

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /tol/rmanbak/db26/DB26_484391126_226_1_20150402.bkp

RMAN> restore datafile '/tol/oradata/db26/newcourse16.dbf';

Starting restore at 2015-04-02 10:00:21

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00551 to /tol/oradata/db26/newcourse16.dbf

channel ORA_DISK_1: reading from backup piece /tol/rmanbak/db26/DB26_484391126_226_1_20150402.bkp

channel ORA_DISK_1: piece handle=/tol/rmanbak/db26/DB26_484391126_226_1_20150402.bkp tag=TAG20150402T100539

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:02:45

Finished restore at 2015-04-02 10:03:20

RMAN> recover database;

Starting recover at 2015-04-02 14:16:32

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 04/02/2015 14:17:15

RMAN-06094: datafile 552 must be restored

进而发现552，553号数据文件也没有了，用同样的办法恢复了一下这两个文件；

RMAN> restore datafile 553;

Starting restore at 2015-04-02 15:29:10

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00553 to /tol/oradata/db26/sso13.dbf

channel ORA_DISK_1: reading from backup piece /tol/rmanbak/db26/DB26_484391126_228_1_20150402.bkp

channel ORA_DISK_1: piece handle=/tol/rmanbak/db26/DB26_484391126_228_1_20150402.bkp tag=TAG20150402T153014

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 2015-04-02 15:29:11

RMAN> recover database;

Starting recover at 2015-04-02 15:29:18

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 607268 is already on disk as file /tol/archivelog/1_607268_798641496.arc

archived log for thread 1 with sequence 607269 is already on disk as file /tol/archivelog/1_607269_798641496.arc

archived log for thread 1 with sequence 607270 is already on disk as file /tol/archivelog/1_607270_798641496.arc

archived log for thread 1 with sequence 607271 is already on disk as file /tol/archivelog/1_607271_798641496.arc

archived log for thread 1 with sequence 607272 is already on disk as file /tol/archivelog/1_607272_798641496.arc

。。。。

。。。。

archived log file name=/tol/archivelog/1_607268_798641496.arc thread=1 sequence=607268

archived log file name=/tol/archivelog/1_607269_798641496.arc thread=1 sequence=607269

archived log file name=/tol/archivelog/1_607270_798641496.arc thread=1 sequence=607270

archived log file name=/tol/archivelog/1_607271_798641496.arc thread=1 sequence=607271

开始恢复新产生的归档日志，还差100多个归档，完成后恢复DG的正常复制即可；

archived log file name=/tol/archivelog/1_607365_798641496.arc thread=1 sequence=607365

unable to find archived log

archived log thread=1 sequence=607366

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 04/02/2015 17:24:59

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 607366 and starting SCN of 11866601457

607366为当前current redolog，所以恢复失败，停止recover，启动备库恢复进程：

SQL> select sequence# from v$log where status='CURRENT';

SEQUENCE#

----------

607366

备库：

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

主库切换日志：

SQL> alter system switch logfile;

System altered.

备库查看alter日志信息：

Media Recovery Waiting for thread 1 sequence 607366

Thu Apr 02 17:27:05 2015

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

Thu Apr 02 17:29:53 2015

RFS[2]: Opened log for thread 1 sequence 607366 dbid 484391126 branch 798641496

Archived Log entry 99 added for thread 1 sequence 607366 rlc 798641496 ID 0x1cdf24d6 dest 2:

Thu Apr 02 17:30:00 2015

Media Recovery Log /tol/archivelog/1_607366_798641496.arc

至此，备库恢复正常。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23249684/viewspace-1483476/，如需转载，请注明出处，否则将追究法律责任。