Oracle DataGuard之--手工解决日志GAP（日志间隙）

     在Oracle DG 中当standby db 失联后，会照成主备库，日志不同步，照成日志的GAP；在配置DG参数（FAL_SERVER)，备库可以主动向主库request log，但有时候不能自动获取时，可以通过手工来解决日志GAP.

系统环境：

操作系统： RedHat EL55

Oracle：   Oracle 11gR2

Primary DB:     BJ

Standby DB:   GZ


一、查看物理备库GAP 信息

要确定在你的物理备数据库上是否有归档中断，查询V$ARCHIVE_GAP 视图，如下面例子所示：

 SQL> SELECT* FROMV$ARCHIVE_GAP
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD #=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN X AND X；

二、Oracle DG 解决日志 gap（间隙）问题，手工在备库上注册archive log


1、在主库上将日志传送到备库

[oracle@bj admin]$ cd /dsk4/arch_bj/

[oracle@bj arch_bj]$ ls

arch_1_10_830282966.log  arch_1_23_830282966.log  arch_1_36_830282966.log

arch_1_11_830282966.log  arch_1_24_830282966.log  arch_1_37_830282966.log

arch_1_12_830282966.log  arch_1_25_830282966.log  arch_1_38_830282966.log

arch_1_13_830282966.log  arch_1_26_830282966.log  arch_1_39_830282966.log

arch_1_14_830282966.log  arch_1_27_830282966.log  arch_1_40_830282966.log

arch_1_15_830282966.log  arch_1_28_830282966.log  arch_1_41_830282966.log

arch_1_16_830282966.log  arch_1_29_830282966.log  arch_1_42_830282966.log

arch_1_17_830282966.log  arch_1_30_830282966.log  arch_1_43_830282966.log

arch_1_18_830282966.log  arch_1_31_830282966.log  arch_1_44_830282966.log

arch_1_19_830282966.log  arch_1_32_830282966.log  arch_1_45_830282966.log

arch_1_20_830282966.log  arch_1_33_830282966.log  arch_1_46_830282966.log

arch_1_21_830282966.log  arch_1_34_830282966.log  arch_1_47_830282966.log

arch_1_22_830282966.log  arch_1_35_830282966.log  arch_1_9_830282966.log

[oracle@bj arch_bj]$ scp *4* sh:/dsk4/arch_gz

arch_1_41_830282966.log                                     100% 4650KB   4.5MB/s   00:00    

arch_1_42_830282966.log                                     100%   21KB  20.5KB/s   00:00    

arch_1_43_830282966.log                                     100%  166KB 166.0KB/s   00:00    

arch_1_44_830282966.log                                     100%   35KB  34.5KB/s   00:00    

arch_1_45_830282966.log                                     100% 4096     4.0KB/s   00:00    

arch_1_46_830282966.log                                     100%   33KB  33.0KB/s   00:00    

arch_1_47_830282966.log                                     100%   34KB  33.5KB/s   00:00    

[oracle@bj arch_bj]$

2、备库日志、根据日志信息，可以了解备库缺少sequence 41以后的日志


Media Recovery Waiting for thread 1 sequence 41

Mon Nov 04 15:55:01 2013

3、在备库上注册archive log

15:55:21 SYS@ gz>alter database register logfile '/dsk4/arch_gz/arch_1_41_830282966.log';

Database altered.

Elapsed: 00:00:00.03

15:55:33 SYS@ gz>alter database register logfile '/dsk4/arch_gz/arch_1_42_830282966.log';

Database altered.

Elapsed: 00:00:00.01

15:55:43 SYS@ gz>alter database register logfile '/dsk4/arch_gz/arch_1_43_830282966.log';

Database altered.

Elapsed: 00:00:00.02

15:56:01 SYS@ gz>


4、备库日志

-----备库会主动解决日志gap

alter database register logfile '/dsk4/arch_gz/arch_1_41_830282966.log'

There are 1 logfiles specified.

ALTER DATABASE REGISTER [PHYSICAL] LOGFILE

Completed: alter database register logfile '/dsk4/arch_gz/arch_1_41_830282966.log'

Mon Nov 04 15:55:35 2013

Media Recovery Log /dsk4/arch_gz/arch_1_41_830282966.log

Media Recovery Waiting for thread 1 sequence 42

Mon Nov 04 15:55:43 2013

alter database register logfile '/dsk4/arch_gz/arch_1_42_830282966.log'

There are 1 logfiles specified.

ALTER DATABASE REGISTER [PHYSICAL] LOGFILE

Resynchronizing thread 1 from sequence 41 to 42

Completed: alter database register logfile '/dsk4/arch_gz/arch_1_42_830282966.log'

Mon Nov 04 15:55:45 2013

Media Recovery Log /dsk4/arch_gz/arch_1_42_830282966.log

Media Recovery Waiting for thread 1 sequence 43

Mon Nov 04 15:56:01 2013

alter database register logfile '/dsk4/arch_gz/arch_1_43_830282966.log'

There are 1 logfiles specified.

ALTER DATABASE REGISTER [PHYSICAL] LOGFILE

Resynchronizing thread 1 from sequence 42 to 43

Completed: alter database register logfile '/dsk4/arch_gz/arch_1_43_830282966.log'

Mon Nov 04 15:56:06 2013

Media Recovery Log /dsk4/arch_gz/arch_1_43_830282966.log

Media Recovery Waiting for thread 1 sequence 44

Mon Nov 04 15:56:08 2013

Using STANDBY_ARCHIVE_DEST parameter default value as /dsk4/arch_gz

RFS[1]: Assigned to RFS process 3854

RFS[1]: Identified database type as 'physical standby': Client is ARCH pid 4244

RFS[1]: Opened log for thread 1 sequence 44 dbid 242121299 branch 830282966

Mon Nov 04 15:56:08 2013

RFS[2]: Assigned to RFS process 3856

RFS[2]: Identified database type as 'physical standby': Client is ARCH pid 4248

RFS[2]: Opened log for thread 1 sequence 45 dbid 242121299 branch 830282966

Archived Log entry 4 added for thread 1 sequence 45 rlc 830282966 ID 0xe741ed4 dest 2:

RFS[2]: Opened log for thread 1 sequence 46 dbid 242121299 branch 830282966

Archived Log entry 5 added for thread 1 sequence 44 rlc 830282966 ID 0xe741ed4 dest 2:

Archived Log entry 6 added for thread 1 sequence 46 rlc 830282966 ID 0xe741ed4 dest 2:

RFS[1]: Opened log for thread 1 sequence 47 dbid 242121299 branch 830282966

Archived Log entry 7 added for thread 1 sequence 47 rlc 830282966 ID 0xe741ed4 dest 2:

Media Recovery Log /dsk4/arch_gz/arch_1_44_830282966.log

Media Recovery Log /dsk4/arch_gz/arch_1_45_830282966.log

Media Recovery Log /dsk4/arch_gz/arch_1_46_830282966.log

Media Recovery Log /dsk4/arch_gz/arch_1_47_830282966.log

Media Recovery Waiting for thread 1 sequence 48

Mon Nov 04 15:56:14 2013

RFS[3]: Assigned to RFS process 3859

RFS[3]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 4250

Primary database is in MAXIMUM PERFORMANCE mode

RFS[3]: Opened log for thread 1 sequence 48 dbid 242121299 branch 830282966

Archived Log entry 8 added for thread 1 sequence 48 rlc 830282966 ID 0xe741ed4 dest 2:

RFS[3]: Opened log for thread 1 sequence 49 dbid 242121299 branch 830282966

Mon Nov 04 15:56:21 2013

Media Recovery Log /dsk4/arch_gz/arch_1_48_830282966.log

Media Recovery Waiting for thread 1 sequence 49 (in transit)


5、问题解决


主库：

16:02:06 SYS@ prod>select max(sequence#) from v$archived_log;


MAX(SEQUENCE#)

--------------

           48

Elapsed: 00:00:00.01


备库：

15:56:01 SYS@ gz>select max(sequence#) from v$archived_log;


MAX(SEQUENCE#)

--------------

           48