【Oracle】Oracle 11g主库日志已被删除的情况下如何恢复从库

模拟

主库的归档日志在没有传输到从库就被删除

?

1 2 3 4 5 6 7 8 9 10

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;

因为主库删除了归档日志，现在从库无法继续同步了。

 

修复

获取主库和从库的SCN

首先获取主库和从库的SCN

1. 主库当前的scn

?

1 2 3 4

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

 

2. 从库当前的scn​

?

1 2 3 4

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

　　

恢复从库，需要从SCN：2791087开始。

 

3.从库关闭日志应用进程

?

1	STANDBY_SQL > alter database recover managed standby database cancel;

　　

4.关闭从库

?

1	STANDBY_SQL > shutdown immediate;

　　

5.主库做增量备份，从上面我们查出的SCN开始备份​

?

1 2 3 4

RMAN> run { allocate channel c1 type disk format '/home/oracle/abce/rman_bkup%U.rmb'; backup incremental from scn 2791087 database; }

　　

6.为从库创建一个新的standby控制文件(在primary端创建)​

?

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

　

7.将rman备份和standby控制文件拷贝到从库

?

1	在从库，拷贝到/home/oracle/abce/

　　

8.将从库启动到nomount状态

?

1	STANDBY_SQL > startup nomount;

　

9.找到standby端的控制文件​

?

1 2 3 4

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

　　

10.使用主库上面创建的standby控制文件替换该控制文件

?

1	cp /home/oracle/abce/control02.ctl /u04/TEST/oradata/control02.ctl

　　

11.将从库启动到mount状态

?

1	STANDBY_SQL > alter database mount standby database;

　　

12.注册rman备份文件

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

STANDBY]$ rman target / Recovery Manager: Release 11.2.0.2.0 - Production on Fri Sep 20 09:14:08 2013 Copyright (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/abce'; using target database control file instead of recovery catalog searching for all files that match the pattern /home/oracle/abce List of Files Unknown to the Database ===================================== File Name: /home/oracle/abce/control02.ctl Do you really want to catalog the above files (enter YES or NO)? yes cataloging files... cataloging done List of Cataloged Files ======================= File Name: /home/oracle/abce/control02.ctl

　　

13.恢复从库​

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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

　　

如果发生如上错误，将recovery过程停止。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

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

　

如果发生这个错误，属于预料内的。因为我们已经到了归档日志的末尾。sequence# 251的日志还没有生成。

启动MRP

?

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

　

至此，从库已经和主库是同步的了。

可以检查一下：​

?

1 2 3 4 5 6 7 8 9 10

1. [Primary] Find current_scn from primary. PRIMARY_SQL > select current_scn from v$database; CURRENT_SCN ----------- 2791422 2. [Standby] Find current_scn from standby. STANDBY_SQL > select current_scn from v$database; CURRENT_SCN ----------- 279142211

　　

检查从库的进程状态​

?

1 2 3 4 5 6 7 8 9

STANDBY_SQL > select sequence#,process,status from v$managed_standby; SEQUENCE# PROCESS STATUS ---------- --------- ------------ 258 ARCH CLOSING 0 ARCH CONNECTED 259 RFS IDLE 259 MRP0 WAIT_FOR_LOG   RFS IDLE

　

主库切换日志，看从库是否接受和应用​

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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

　　

 

 

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