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

2022-01-13 22:03 abce 阅读(310) 评论(0) 编辑收藏举报

模拟场景：

1.主库，关闭日志传输

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

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

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

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

4.主库，开启日志传输

1	`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 catalog
allocated channel: c1
channel c1: SID=90 device type=DISK
Starting backup at 20-SEP-13
backup will be obsolete on date 27-SEP-13
archived logs will not be kept or backed up
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u03/TEST/oradata/user01.dbf
input datafile file number=00001 name=/u03/TEST/oradata/system.dbf
input datafile file number=00002 name=/u03/TEST/oradata/sysaux.dbf
input datafile file number=00003 name=/u03/TEST/oradata/undotbs1.dbf
channel c1: starting piece 1 at 20-SEP-13
channel c1: finished piece 1 at 20-SEP-13
piece handle=/home/oracle/raj/rman_bkup0aokac2j_1_1.rmb tag=TAG20130920T080539
comment=NONE
channel c1: backup set complete, elapsed time: 00:00:45
backup will be obsolete on date 27-SEP-13
archived logs will not be kept or backed up
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 20-SEP-13
channel c1: finished piece 1 at 20-SEP-13
piece handle=/home/oracle/raj/rman_bkup0bokac40_1_1.rmb tag=TAG20130920T080539
comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-SEP-13
released 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 bytes
Fixed Size 2233336 bytes
Variable Size 813698056 bytes
Database Buffers 247463936 bytes
Redo Buffers 5541888 bytes

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

STANDBY_SQL > show parameter control_files
NAME 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 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/raj';
using target database control file instead of recovery catalog
searching for all files that match the pattern /home/oracle/raj
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/raj/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/raj/control02.ctl

13.备库，恢复standby库

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/raj/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/raj/rman_bkup0aokac2j_1_1.rmb
ORA-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 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/raj/rman_bkup0aokac2j_1_1.rmb
channel ORA_DISK_1: piece handle=/home/oracle/raj/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

这是预料中的错误，因为我们到了最后的归档日志，期待的序列号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 CLOSING
0 ARCH CONNECTED
259 RFS IDLE
259 MRP0 WAIT_FOR_LOG

RFS IDLE

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

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