Oracle11g主库日志被删除后如何恢复Standby数据库

模拟场景：

1.主库，关闭日志传输

PRIMARY_SQL> Alter system set log_archive_dest_state_2=defer scope =both;

2.主库，切换日志生成新的归档

PRIMARY_SQL > alter system switch logfile;System altered.PRIMARY_SQL > alter system switch logfile;System altered.

3.主库，移除最近两个日志(还未被传输)

4.主库，开启日志传输

PRIMARY_SQL > Alter system set log_archive_dest_state_2=enable scope=both;

因为已经移除了主库的两个日志，standby库无法再继续进行日志同步。

 

解决方案：

获取主库和备库的scn

1.主库

PRIMARY_SQL > select current_scn from v$database;CURRENT_SCN-----------2791422

2.从库

STANDBY_SQL > select current_scn from v$database;CURRENT_SCN-----------2791087 -

3.从库，停止应用进程

STANDBY_SQL > alter database recover managed standby database cancel;

4.从库，关闭从库

STANDBY_SQL > shutdown immediate;

5.主库，根据scn做一个增量备份

RMAN> run {allocate channel c1 type disk format '/home/oracle/raj/rman_bkup%U.rmb';backup incremental from scn 2791087 database;}2> 3> 4>using target database control file instead of recovery catalogallocated channel: c1channel c1: SID=90 device type=DISKStarting backup at 20-SEP-13backup will be obsolete on date 27-SEP-13archived logs will not be kept or backed upchannel c1: starting full datafile backup setchannel c1: specifying datafile(s) in backup setinput datafile file number=00004 name=/u03/TEST/oradata/user01.dbfinput datafile file number=00001 name=/u03/TEST/oradata/system.dbfinput datafile file number=00002 name=/u03/TEST/oradata/sysaux.dbfinput datafile file number=00003 name=/u03/TEST/oradata/undotbs1.dbfchannel c1: starting piece 1 at 20-SEP-13channel c1: finished piece 1 at 20-SEP-13piece handle=/home/oracle/raj/rman_bkup0aokac2j_1_1.rmb tag=TAG20130920T080539comment=NONEchannel c1: backup set complete, elapsed time: 00:00:45backup will be obsolete on date 27-SEP-13archived logs will not be kept or backed upchannel c1: starting full datafile backup setchannel c1: specifying datafile(s) in backup setincluding current control file in backup setchannel c1: starting piece 1 at 20-SEP-13channel c1: finished piece 1 at 20-SEP-13piece handle=/home/oracle/raj/rman_bkup0bokac40_1_1.rmb tag=TAG20130920T080539comment=NONEchannel c1: backup set complete, elapsed time: 00:00:01Finished backup at 20-SEP-13released channel: c1

6.主库，创建一个新的standby控制文件

PRIMARY_SQL > alter database create standby controlfile as'/home/oracle/raj/control02.ctl';

7.拷贝rman备份文件和新创建的standby控制文件到备库

拷贝到目录/home/oracle/raj/​

8.备库，启动standby，以nomount的方式

STANDBY_SQL > startup nomount;ORACLE instance started.Total System Global Area 1068937216 bytesFixed Size 2233336 bytesVariable Size 813698056 bytesDatabase Buffers 247463936 bytesRedo Buffers 5541888 bytes

9.备库.找出standby控制文件的位置

STANDBY_SQL > show parameter control_filesNAME TYPE VALUE------------------------------------ ----------- ------------------------------control_files string /u04/TEST/oradata/control02.ctl

10.备库，使用新创建的控制文件替换老的standby控制文件

cp /home/oracle/raj/control02.ctl /u04/TEST/oradata/control02.ctl

11.备库，mount备库

STANDBY_SQL > alter database mount standby database;

12.备库，注册rman备份

STANDBY]$ rman target /Recovery Manager: Release 11.2.0.2.0 - Production on Fri Sep 20 09:14:08 2013Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.connected to target database: TESTER (DBID=3502897675, not open)RMAN> catalog start with '/home/oracle/raj';using target database control file instead of recovery catalogsearching for all files that match the pattern /home/oracle/rajList of Files Unknown to the Database=====================================File Name: /home/oracle/raj/control02.ctlDo you really want to catalog the above files (enter YES or NO)? yescataloging files...cataloging doneList of Cataloged Files=======================File Name: /home/oracle/raj/control02.ctl

13.备库，恢复standby库

RMAN> recover database;Starting recover at 20-SEP-13allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=76 device type=DISKchannel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00001: /u04/TEST/oradata/system.dbfdestination for restore of datafile 00002: /u04/TEST/oradata/sysaux.dbf9destination for restore of datafile 00003: /u04/TEST/oradata/undotbs1.dbfdestination for restore of datafile 00004: /u04/TEST/oradata/user01.dbfchannel ORA_DISK_1: reading from backup piece /home/oracle/raj/rman_bkup0aokac2j_1_1.rmbRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 09/20/2013 09:16:41ORA-19870: error while restoring backup piece /home/oracle/raj/rman_bkup0aokac2j_1_1.rmbORA-19573: cannot obtain exclusive enqueue for datafile 4

如果发生上面的错误，取消恢复操作

STANDBY_SQL > alter database recover managed standby database cancel;Database altered.Again follow the same process i.e recovering the databaseRMAN> recover database;Starting recover at 20-SEP-13using channel ORA_DISK_1channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00001: /u04/TEST/oradata/system.dbfdestination for restore of datafile 00002: /u04/TEST/oradata/sysaux.dbfdestination for restore of datafile 00003: /u04/TEST/oradata/undotbs1.dbfdestination for restore of datafile 00004: /u04/TEST/oradata/user01.dbfchannel ORA_DISK_1: reading from backup piece /home/oracle/raj/rman_bkup0aokac2j_1_1.rmbchannel ORA_DISK_1: piece handle=/home/oracle/raj/rman_bkup0aokac2j_1_1.rmb tag=TAG20130920T080539channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01starting media recoveryarchived log for thread 1 with sequence 250 is already on disk as file/u04/TEST/oradata/REDO_STDBY/1_250_824551947.arcarchived log file name=/u04/TEST/oradata/REDO_STDBY/1_250_824551947.arc thread=1 sequence=250unable to find archived logarchived log thread=1 sequence=251RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================10RMAN-03002: failure of recover command at 09/20/2013 09:28:04RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 251 and starting SCN of2796410

这是预料中的错误，因为我们到了最后的归档日志，期待的序列号8008的归档日志还没有生成。

 

然后启动日志应用

STANDBY_SQL > alter database recover managed standby database disconnect from session;

现在备库就和主库同步了。

14.检查

(1)检查主库

PRIMARY_SQL > select current_scn from v$database;CURRENT_SCN-----------2791422

(2)检查从库

STANDBY_SQL > select current_scn from v$database;CURRENT_SCN-----------279142211

(3)检查从库的进程

STANDBY_SQL > select sequence#,process,status from v$managed_standby;SEQUENCE# PROCESS STATUS---------- --------- ------------258 ARCH CLOSING0 ARCH CONNECTED259 RFS IDLE259 MRP0 WAIT_FOR_LOG​RFS IDLE

(4)主库切换日志，看看新的归档是否被应用了

PRIMARY_SQL > alter system switch logfile;System altered.PRIMARY_SQL > archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /u03/TEST/oradata/REDO_STDBY/Oldest online log sequence 257Next log sequence to archive 259Current log sequence 259STANDBY_SQL > archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /u04/TEST/oradata/REDO_STDBY/Oldest online log sequence 258Next log sequence to archive 0Current log sequence 259

 

 

参考：

https://dbaclass.com/article/how-to-recover-standby-database-when-archive-logs-are-missing-in-primary/