【Oracle】Oracle 11g主库日志已被删除的情况下如何恢复从库
2022-06-17 08:29 abce 阅读(304) 评论(0) 编辑 收藏 举报模拟
主库的归档日志在没有传输到从库就被删除
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
PRIMARY_SQL > select current_scn from v$database; CURRENT_SCN ----------- 2791422
2. 从库当前的scn
STANDBY_SQL > select current_scn from v$database; CURRENT_SCN ----------- 2791087 -
恢复从库，需要从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/abce/rman_bkup%U.rmb';
backup incremental from scn 2791087 database;
}
6.为从库创建一个新的standby控制文件(在primary端创建)
PRIMARY_SQL > alter database create standby controlfile as '/home/oracle/abce/control02.ctl';
7.将rman备份和standby控制文件拷贝到从库
在从库，拷贝到/home/oracle/abce/
8.将从库启动到nomount状态
STANDBY_SQL > startup nomount;
9.找到standby端的控制文件
STANDBY_SQL > show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /u04/TEST/oradata/control02.ctl
10.使用主库上面创建的standby控制文件替换该控制文件
cp /home/oracle/abce/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 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.恢复从库
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过程停止。
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
STANDBY_SQL > alter database recover managed standby database disconnect from session;
至此，从库已经和主库是同步的了。
可以检查一下：
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
检查从库的进程状态
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
主库切换日志，看从库是否接受和应用
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
