Oracle存在gap,Oracle DataGuard之--手工解决日志GAP（日志间隙）-CSDN博客

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

二、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#)

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

Elapsed: 00:00:00.01

备库：

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

MAX(SEQUENCE#)

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